Integration of the last semester to learn the accumulation of hope can help beginners!
It may be useful in the future!
The use of a basic statement
Operation based on EMP table
1, according to the salary from high to low arrangement
Sql> Select RowNum as Order, ename,sal
2 from (select Ename,sal
3 from EMP
4 ORDER BY Sal Desc)
5 Where rownum<=5
6;
2, do the embedded diagram from large to small after the first five to find the wrong wording
Sql> Select Ename,sal
2 from EMP
3 where rownum<=5
4 ORDER BY Sal Desc;
First five records are sorted by salary (not asked, this is the wrong way)
3. Change the job bonus named Scott
Update emp Set job= ' MANAGER ', comm= ' 4000 '
where ename= ' SCOTT ';
4. Looking for a salary greater than Scott or HireDate earlier than Scott's
Select Ename,empno from emp
where sal> (select Sal from emp where ename= ' SCOTT ')
Or hiredate> (select HireDate from emp where ename= ' SCOTT ');
5. Find the largest
Select Ename,sal from emp
Where sal= (select Max (SAL) from EMP);
6. Find departments with less than 4 department personnel
Select AVG (SAL) from EMP
where deptno=
(Select Deptno from emp
GROUP BY DEPTNO have Count (*) <4);
7. Some Settings reference
Set Linesize 180 Sets the total number of characters displayed per line
Set PageSize 100 Sets the number of rows to display per page
Set feedback on/off setting whether the selected XX line is displayed
Set heading on/off setting whether to display column names
Set time on/off setting whether to display current system times
Set timing on/off setting shows how long each SQL execution consumes
Set Termout on/off sets whether the information is printed in the console when the SQL file is executed
Set trimout on/off setting removes trailing spaces per line of standard output
Set Trimspool on/off setting removes trailing spaces for each line in the spool output file
8, according to different dimensions grouped multidimensional data statistics
Select AVG (grade), STU_NO,CNO
From MARK2
GROUP BY rollup (CNO,STU_NO);
Group first with CNO, then sort by stu_no
Calculates the average of a set of CNO
9. Cube combines the data dimensions of group by
Select AVG (grade), STU_NO,CNO
From MARK2
Group by Cube (STU_NO,CNO);
AVG (GRADE) stu_no CNO
---------- ---------- ----------
78.8888889
90 1
83.3333333 2
63.3333333 3
73.3333333 1404010525
90 1404010525 1
80 1404010525 2
50 1404010525 3
83.3333333 1404010526
100 1404010526 1
80 1404010526 2
70 1404010526 3
80 1404010527
80 1404010527 1
90 1404010527 2
70 1404010527 3
Calculate the average value grouped by CNO first
Then calculate the average by grouping (STU_NO,CNO)
10, query a table in the course 1 than the course 2 score High student number
Select A.num,a.score
As Score1,b.score
As Score2 from (
SELECT * from SC where cno= ' 001 ') a
Left Join
(SELECT * from SC where cno= ' 002 ') b
On A.num=b.num
where a.score<b.score;
11, Inquiry table in the course 1 than the course 2 High student information: Return multi-value? With in!
Select Num,name
From Stu
where Num in
(Select A.num from (
SELECT * from SC where cno= ' 001 ') a
Left Join
(SELECT * from SC where cno= ' 002 ') b
On A.num=b.num
where A.score<b.score);
12, the query average score is greater than 60 students study number, average performance, name
1\ query average score greater than 90 student number: Select NUM,AVG (Score) from SC GROUP by NUM have avg (score) >90
2\ Select Num,name
From Stu
where Num in (
Select num from SC GROUP by NUM has avg (score) >90); (What about the average score)?
3\select Num,avg (Score)
From SC
GROUP BY NUM has avg (score) >90; (Tanchako and GPA)
Final wording:
Select Stu.num,stu.name,avg (Sc.score)
From STU,SC
where Stu.num in (
Select num from SC GROUP BY (Sc.num,name) has AVG (score) >90)
and Stu.num=sc.num;
13, check all students of the student number, name, number of courses selected, total
Select Stu.num,stu.name,count (SC.CNO), sum (sc.score)
From STU,COURSE,SC
where Stu.num=sc.num and Course.cno=sc.cno
Group BY stu.num,stu.name ORDER by Num;
14. Inquire all the teacher surnamed Li
SELECT * FROM teacher
Where teacher.name like ' li% ';
15. Check the number and name of the students who have not selected the Chinese course
Select Stu.num,stu.name
From Stu
where Stu.num not in
(SELECT DISTINCT Stu.num
From STU,COURSE,SC
where Stu.num=sc.num and Course.cno=sc.cno
and Course.name= ' Chinese ');
16. Check all student numbers and names of selected maths courses
Select Stu.num,stu.name
From Stu
where Stu.num in
(SELECT DISTINCT Stu.num
From STU,COURSE,SC
where Stu.num=sc.num and Course.cno=sc.cno
and course.name= ' maths ');
17. Check all student numbers and names of maths and Chinese courses at the same time
SELECT * FROM Stu
where Stu.num in
(select num from Sc,course where sc.cno=course.cno and course.name= ' maths ')
and Stu.num in
(select num from Sc,course where sc.cno=course.cno and Course.name= ' Chinese ');
18. Check the name and student number of all courses less than 90 points.
SELECT * FROM Stu
where Stu.num not in
(select num from SC where score>=90);
B PL/SQL statements
1. Output Some statements with table type
Declare
Type My_table_type is table of varchar (20)
Index by Binary_integer;
My_table My_table_type;
Begin
My_table (1): = ' What the fuck ';
My_table (2): = ' hehe is a gay ';
My_table (3): = ' what the hell ';
My_table ( -100): = ' Is it a fky ';
Dbms_output.put_line (my_table (1));
Dbms_output.put_line (My_table (2));
Dbms_output.put_line (My_table (3));
Dbms_output.put_line (my_table (-100));
Dbms_output.put_line (My_table.count);
End
/
2. Select statement Application: Note that the query results can only be a single record
Sql> Declare
2 V_dname Student.name%type;
3 V_dept_rec Student%rowtype;
4 begin
5 select name into V_dname from student where num= ' 02 ';
6 Dbms_output.put_line (' Name of student "is: ' | | V_dname);
7 End;
8/
Name of student Is:wangwu
3, cursor: is a buffer zone, in memory temporary storage of multiple records.
Open cursor FETCH data close cursor
4. Stored Procedures
Query the name of the specified number:
Create or Replace procedure P1
As
V_ename Emp.ename%type;
Begin
Select Ename into V_ename
from EMP where empno=7788;
Dbms_output.put_line (' 7788 employees ' name is ' | | V_ename);
End
/
Query the name of the specified number of employees
Create or Replace procedure P1 (P_no in Emp.empno%type)
As
V_ename Emp.ename%type;
Begin
Select Ename into V_ename
from EMP where empno=p_no;
Dbms_output.put_line (p_no| | ' Employee's name is ' | | V_ename);
exception
When No_data_found Then
Dbms_output.put_line (' No this number ');
When others then
Dbms_output.put_line (' system error ');
End
/
EMPNO ename JOB MGR hiredate SAL COMM DEPTNO
C SQL statement Exercises
1. List the names of all employees and their direct superiors;
Select A.ename empname,b.ename mgrname
From EMP A,emp b
where A.mgr=b.empno;
2. List the names of employees who work in the "IT" department;
Select ename from emp
where deptno=
(Select Deptno from dept
where dname= ' IT ');
3. Name, number and department number of all employees whose wages are higher than the average wage of the company;
Select Ename,deptno,empno from emp
Where sal> (select AVG (SAL) from EMP);
4, list the number of employees in each department, the average salary;
Select AVG (SAL), COUNT (*)
From EMP
Group BY Deptno;
5. List the detailed information and number of departments in all sectors;
SELECT * FROM dept a LEFT join
(SELECT COUNT (*), deptno from EMP
Group BY Deptno)
B
On
A.deptno=b.deptno;
6. List the minimum wage for various positions;
Select Job,min (SAL)
From EMP
Group by Job;
7. List the name, salary and department number of the manager with the lowest wage in the department manager;
Select Ename,sal,deptno
From EMP
Where Sal<=all (
Select Sal from emp where job= ' MANAGER '
) and job= ' MANAGER ';
8. List the basic information of the five employees who have the highest salary in the company.
SELECT * FROM emp
where Ename in (
Select Ename from (
Select ename from emp
ORDER BY Sal Desc) A
where RowNum <6);
The study of the writing of SQL statements accumulated in one semester