Summary of SQL statements

CREATE TABLE Student (
Sno VARCHAR2 (Ten) primary key,
Sname varchar2 (20),
Sage Number (2),
Ssex VARCHAR2 (5)
);
CREATE TABLE Teacher (
TNO VARCHAR2 (Ten) primary key,
Tname VARCHAR2 (20)
);
CREATE TABLE Course (
CNO VARCHAR2 (10),
CNAME varchar2 (20),
TNO Varchar2 (20),
Constraint Pk_course primary KEY (Cno,tno)
);
CREATE TABLE SC (
Sno VARCHAR2 (10),
CNO VARCHAR2 (10),
Score Number (4,2),
Constraint PK_SC primary KEY (SNO,CNO)
);
/******* initializing data ******/for student tables
INSERT into student values (' s001 ', ' Zhang San ', 23, ' Male ');
INSERT into student values (' s002 ', ' John Doe ', 23, ' Male ');
INSERT into student values (' s003 ', ' Peng Wu ', 25, ' Male ');
INSERT into student values (' s004 ', ' Qin Qin ', 20, ' female ');
INSERT into student values (' s005 ', ' Wang Li ', 20, ' female ');
INSERT into student values (' s006 ', ' Li Bo ', 21, ' Male ');
INSERT into student values (' s007 ', ' Christina ', 21, ' Male ');
INSERT into student values (' s008 ', ' Shang ', 21, ' female ');
INSERT into student values (' s009 ', ' Chen Yixiao ', 23, ' female ');
INSERT into student values (' s010 ', ' Vanessa Mae ', 22, ' female ');
Commit
/****************** Initializing teacher Table ***********************/
Insert into teacher values (' t001 ', ' Liu Yang ');
Insert into teacher values (' t002 ', ' Shanyan ');
Insert into teacher values (' t003 ', ' Humingshing ');
Commit
/*************** Initialize Curriculum ****************************/
Insert into course values (' c001 ', ' j2se ', ' t002 ');
Insert into course values (' c002 ', ' Java Web ', ' t002 ');
Insert into course values (' c003 ', ' SSH ', ' t001 ');
Insert into course values (' c004 ', ' Oracle ', ' t001 ');
Insert into course values (' c005 ', ' SQL SERVER 2005 ', ' t003 ');
Insert into course values (' c006 ', ' C # ', ' t003 ');
Insert into course values (' c007 ', ' JavaScript ', ' t002 ');
Insert into course values (' c008 ', ' div+css ', ' t001 ');
Insert into course values (' c009 ', ' PHP ', ' t003 ');
Insert into course values (' c010 ', ' EJB3.0 ', ' t002 ');
Commit
/*************** Initialization score Table ***********************/
INSERT into SC values (' s001 ', ' c001 ', 78.9);
INSERT into SC values (' s002 ', ' c001 ', 80.9);
INSERT into SC values (' s003 ', ' c001 ', 81.9);
INSERT into SC values (' s004 ', ' c001 ', 60.9);
INSERT into SC values (' s001 ', ' c002 ', 82.9);
INSERT into SC values (' s002 ', ' c002 ', 72.9);
INSERT into SC values (' s003 ', ' c002 ', 81.9);
INSERT into SC values (' s001 ', ' c003 ', ' 59 ');
Commit

Practice:
Note: It is important to note that the data in the following exercise is based on SQL statements that are written according to the data that is initialized to the database.

SELECT * from student;
SELECT * from teacher;
SELECT * from course;
SELECT * FROM SC;
1, inquiry "c001" course than "c002" course performance of all students of the school number;
Select A.ssno1 from
(select S.sno Ssno1,s.score sscore1 from SC s where s.cno= ' c001 ') a LEFT join
(select S.sno Ssno2,s.score sscore2 from SC s where s.cno= ' c002 ') b on A.ssno1=b.ssno2
where A.sscore1 > B.sscore2

2, the query average score is more than 60 points of the student's number and average score;

Select S.sno as study number, AVG (S.score) average score from SC s GROUP by S.SNO have avg (s.score) >60

3, inquiry all students of the school number, name, number of courses selected, total;
--Method 1
Select S.sno,s.sname,count (C.CNO), sum (c.score) from student s INNER join SC c in S.sno = C.sno GROUP BY s.sno,s.sname
--Method 2
Select S.sno,s.sname,a.countcno,a.sumcscore from student s inner join
(Select C.sno csno,count (c.cno) countcno,sum (c.score) Sumcscore from SC C GROUP by C.sno) a
On s.sno = A.csno
4, inquires the surname "Liu" the number of teachers;
Select COUNT (te.tname) from teacher Te where te.tname like ' Liu ';
5, the inquiry did not learn "Shanyan" teacher class students of the school number, name;
Select Stu.sno,stu.sname from student Stu where isn't stu.sname in (
Select distinct st.sname from Student St left Join
(SELECT * FROM SC s LEFT join
(SELECT * FROM teacher te inner join course co-te.tno = Co.tno) A on s.cno =a.cno) b
On st.sno = b.sno where b.tname = ' Shanyan '
)
6, the inquiry learned "c001" and also learned the number "c002" course of the students of the school number, name;

Select St.sno,st.sname from Student St join SC AC in St.sno=ac.sno join SC BC on BC.SNO=AC.SNO
where ac.cno= ' c001 ' and bc.cno= ' c002 '

Select St.sno,st.sname from student St INNER Join
(Select A.sno from
(SELECT * from SC s where s.cno= ' c001 ') a INNER join
(SELECT * from SC s where s.cno= ' c002 ')
B on a.sno = B.sno) C on st.sno= C.sno
7, the inquiry has learned "Shanyan" the teacher teaches all classes The student's school number, the name;
Select distinct st.sno,st.sname from Student St join SC ac in St.sno = Ac.sno Join course Co on ac.cno=co.cno join teacher
Te on Co.tno=te.tno where te.tname= ' Shanyan '

Select distinct st.sno,st.sname from Student St left Join
(SELECT * FROM SC s LEFT join
(SELECT * FROM teacher te inner join course co-te.tno = Co.tno) A on s.cno =a.cno) b
On st.sno = b.sno where b.tname = ' Shanyan '
8, inquires the course number "c002" The result than the course number "c001" The curriculum low all schoolmate's student number, the name;

Select St.sno,st.sname from Student St where St.sno in
(Select A.sno from
(SELECT * from sc s1 where s1.cno = ' c002 ') A,
(SELECT * from SC s2 where s2.cno = ' c001 ') b where A.sno = B.sno and A.score < B.score)

--9, check all the course scores of less than 60 points of the student's school number, name;
Select distinct st.sno,st.sname from Student St left join SC AC on St.sno=ac.sno
where Ac.score >= 60

Select Sno,sname from student where Sno not in (select Sno from SC where score>=60)

--10, the inquiry did not learn all classes of the students of the school number, name;
Select St.sno,st.sname from Student St left join SC ac on st.sno = Ac.sno
GROUP BY St.sno,st.sname have count (AC.CNO) < (select count (Distinct CO.CNO) from course CO)

SELECT * from student;
SELECT * from teacher;
SELECT * from course;
SELECT * FROM SC;

11, inquires at least one course and the student number is "s001" the classmate learns the same student's school number and the name;
--1
Select distinct st.sno,st.sname from student St joins SC BC on St.sno=bc.sno where isn't bc.sno= ' s001 ' and Bc.cno in (
Select Ac.cno from SC ac where ac.sno= ' s001 ')
--2
Select Su.sno,su.sname from student su where Su.sno in (
Select Sc.sno from SC where sc.cno in (select S.cno from SC s where s.sno= ' s001 ')
and sc.sno<> ' s001 ')
12, inquires at least to learn the number for "s001" classmate all a class of other students study number and name;

SELECT * FROM SC
Left join student St in St.sno=sc.sno where not sc.sno= ' s001 '
and Sc.cno in (select CNO from SC where sno= ' s001 ')

13, the "SC" table in the "Shanyan" teacher taught the results of the course are changed to the average performance of the curriculum;

Update SC Set score= (select AVG (score) from SC where CNO in (
Select CNO from Course where tno= (select TNO from teacher where tname= ' Shanyan ')))
where CNO in (select CNO from Course where tno= (select TNO from teacher where tname= ' Shanyan '));

14, inquires and "s001" number of students to study the course of the same class of other students and the name of the school number;
Select Bc.cno from SC BC where Bc.sno <> ' s001 ' minus
(select Sc.cno from SC where sno= ' s001 ')

15, delete learning "Shanyan" the SC table record of the teacher class;
Delete from SC
where CNO in (select CNO from Course
where TNO in (select TNO from teacher
where tname= ' Shanyan '))

16, insert some records into the SC table, these records require the following conditions: No number "c002" class number,
The average grade of the "c002" class;
Insert INTO SC (sno,cno,score)
SELECT DISTINCT St.sno,sc.cno,
(select AVG (score) from SC where cno= ' c002 ')
From student ST,SC
Where NOT EXISTS
(SELECT * from SC where cno= ' c002 ' and Sc.sno=st.sno) and sc.cno= ' c002 ';

17. Check the highest and lowest score of each section: show the following form: Course ID, highest score, lowest score
Select CNO, Max (score), Min (score) from SC Group by CNO;
Select Ac.cno,max (Ac.score), Min (ac.score) from SC ac GROUP by AC.CNO
18, according to the average grades from low to high and the percentage of passing rate from high to low order
Select Cno,avg (Score), sum (case if score>=60 then 1 else 0 end)/count (*)
As pass rate
From SC GROUP by CNO
ORDER by AVG (score), pass rate desc
19, query different teachers teach different courses average from high to low display

Select Co.tno,ac.cno,avg (ac.score) from SC AC join course CO on AC.CNO=CO.CNO
Group BY Co.tno,ac.cno ORDER by AVG (Ac.score) desc

Select Max (T.tno), Max (T.tname), Max (C.CNO), Max (C.cname), C.cno,avg (score) from SC, course C,teacher t
where Sc.cno=c.cno and C.tno=t.tno
GROUP BY C.cno
ORDER by AVG (SCORE) desc

20, the statistics print each section result, each score segment number: Course ID, course name, [100-85],[85-70],[70-60],[<60]

Select Sc.cno,c.cname,
SUM (case when score between and 1 else 0 end) as "[100-85]",
SUM (case when score between and 1 else 0 end) as "[85-70]",
SUM (case when score between 1 else 0 end) as "[70-60]",
SUM (case if score <60 then 1 else 0 end) as "[<60]"
From SC, course C where Sc.cno=c.cno GROUP by SC.CNO, C.cname;

21. Check the records of the top three grades of each section: (regardless of the performance of the situation)
SELECT * FROM
(select S.sno,s.cno,s.score,row_number () over (partition by CNO ORDER BY s.score Desc)
RN from SC s) where rn<4

SELECT * FROM
(select Sno,cno,score,row_number () over (partition by CNO ORDER BY score Desc) RN from SC)
where rn<4

22. Check the number of students who have been enrolled in each course
Select S.cno,count (*) from SC s GROUP by S.cno
23. Find out the number and name of all students who have only one course of study
Select Bc.sno from SC BC join
(Select S.cno scno,count (*) A from SC s GROUP by S.CNO) SCC on BC.CNO=SCC.SCNO where a=1
24. Query the number of boys and girls
SELECT * FROM Student St
Select St.ssex,count (1) From Student St Group by St.ssex have st.ssex= ' men ' or st.ssex= ' women '
25. Check the list of students with the surname "Zhang"
SELECT * FROM Student St where st.sname like ' sheet% '
26, check the same-name list of same-sex students, and statistics of the same number
Select St.sname,st.ssex,count (1) From Student St Group by St.sname,st.ssex have count (1) >1
27.1981-Born Student list (note: The type of sage column in the Student table is number)

SELECT * FROM Student St where To_char (sysdate, ' yyyy ')-st.sage=1994
28, the average score of each course is queried, the results are arranged in ascending order of average grade, and the average result is the same, descending by the course number.

Select C.cno,avg (c.score) from SC C GROUP by C.cno ORDER by AVG (C.score), C.cno
29. The number, name and average scores of all students who have average scores greater than 85
SELECT * FROM Student St Join
(Select C.sno c,avg (c.score) from SC C GROUP by C.SNO have avg (c.score) >85) AA on ST.SNO=AA.C
30. Check the name and score of the student whose class is "database" with a score below 60
SELECT * from student;
SELECT * from teacher;
SELECT * from course;
SELECT * FROM SC;
SELECT * FROM Student St join SC AC in St.sno=ac.sno join course Co on AC.CNO=CO.CNO
where Co.cname= ' Oracle ' and ac.score<60
31. Check the course selection of all students;
Select Ac.sno,count (1) from Student St join SC AC in St.sno=ac.sno GROUP by Ac.sno
32. Inquire about the name, course name and score of any course score above 70 points;
Select St.sname,co.cname,bc.score from student St join SC BC on ST.SNO=BC.SNO join course Co on BC.CNO=CO.CNO
where Bc.sno in
(select Ac.sno from SC ac
where ac.score>70 Group by Ac.sno) and bc.score>70
33, the examination of the course of failure, and according to the course number from large to small arrangement
Select Ac.cno from SC ac where ac.score<60 GROUP by ac.cno ORDER BY ac.cno Desc
34. To inquire the student's number and name of the course number is c001 and the course result is above 80 points;
SELECT * FROM SC ac where ac.cno= ' c001 ' and ac.score>80
35. Number of students selected for the course
Select COUNT (1) from SC
36. The name of the student with the highest achievement and the results of the students who have enrolled in the course of the "Shanyan" teacher
Select St.sname,c.score from Student St joins SC c on St.sno=c.sno where C.score in (
Select Max (c.score) from Student St join SC C on St.sno=c.sno join course Co
On C.cno=co.cno join teacher Te on Co.tno=te.tno
where te.tname= ' Shanyan ')

37. Check each course and the corresponding number of elective
Select Co.cno,count (CO.CNO) from SC C left join course Co in C.CNO=CO.CNO Group by Co.cno
38. Check the student's number, course number and student achievement of the same students with different course grades.
SELECT * FROM SC AC GROUP by AC.CNO
39. Check the top two of the best results for each course
SELECT * FROM
(select Ac.sno,ac.score,ac.cno,row_number () over (partition by Ac.cno ORDER BY ac.score Desc)
RN from SC ac) where rn<=2

40. The number of students enrolled in each course (more than 10 participants are counted). Required to output the course number and number of elective
Query results are sorted in descending order of number, in ascending order by course number if the number is the same
Select Ac.cno,count (ac.sno) from SC AC GROUP by AC.CNO have Count (Ac.sno) >2
Order by Count (Ac.sno) desc,ac.cno

41. Retrieve the student number of at least two elective courses
SELECT * FROM SC
Select Ac.sno,count (AC.CNO) from SC AC GROUP by Ac.sno have Count (ac.cno) >=2
42. Check the course number and course name of all the students who have enrolled in the course
Select St.sno,co.cno,co.cname from Student St
INNER JOIN SC AC on St.sno=ac.sno left join course Co on AC.CNO=CO.CNO

43, inquiry did not learn the "Shanyan" teacher taught the name of any course students

Select St.sname from Student St join SC AC in St.sno=ac.sno join Course Co on ac.cno=co.cno join teacher Te
On Co.tno=te.tno where not te.tname= ' Shanyan '
44. Check the number of students with two or more failed courses and their average scores
Select Bc.sno,avg (bc.score) from SC BC where Bc.sno in
(select Ac.sno from SC ac where ac.score<60
GROUP BY Ac.sno have Count (ac.score) >2) GROUP by Bc.sno

45, search "c004" course score is less than 60, in descending order by the number of students
Select Ac.sno from SC ac where ac.cno= ' c004 ' and ac.score<60 order by ac.score Desc
46, delete "s002" students "c001" The results of the course
Delete sc ac where ac.sno= ' s002 ' and ac.cno= ' c001 '

Summary of SQL statements