The study of the writing of SQL statements accumulated in one semester

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