Classic SQL Exercises

Today on the Internet to find a few classic SQL exercises do a bit, although it is not difficult, but the foundation is very good, on the basis of understanding can be further analysis, to study the merits of various solutions, or even simple optimization.
Now share the questions and answers. I'm using MySQL 5.0, but most of them are standard SQL.
Table structure:
CREATE TABLE STUDENT
(SNO VARCHAR (3) Not NULL,
SNAME VARCHAR (4) Not NULL,
SSEX VARCHAR (2) Not NULL,
Sbirthday DATETIME,
CLASS VARCHAR (5))
Go
CREATE TABLE COURSE
(CNO VARCHAR (5) Not NULL,
CNAME VARCHAR (Ten) is not NULL,
TNO VARCHAR (Ten) not NULL)
Go
CREATE TABLE Score
(SNO VARCHAR (3) Not NULL,
CNO VARCHAR (5) Not NULL,
Degree NUMERIC (ten, 1) not NULL)
Go
CREATE TABLE TEACHER
(TNO VARCHAR (3) Not NULL,
Tname varchar (4) NOT NULL, Tsex varchar (2) is not NULL,
Tbirthday DATETIME not NULL, PROF VARCHAR (6),
Depart VARCHAR (Ten) not NULL)

INSERT into STUDENT (sno,sname,ssex,sbirthday,class) VALUES (108, ' Zeng Hua '
, ' Male ', 1977-09-01,95033);
INSERT into STUDENT (Sno,sname,ssex,sbirthday,class) VALUES (105, ' Kuanming '
, ' Male ', 1975-10-02,95031);
INSERT into STUDENT (sno,sname,ssex,sbirthday,class) VALUES (107, ' Wang Li '
, ' female ', 1976-01-23,95033);
INSERT into STUDENT (Sno,sname,ssex,sbirthday,class) VALUES (101, ' Li June '
, ' Male ', 1976-02-20,95033);
INSERT into STUDENT (Sno,sname,ssex,sbirthday,class) VALUES (109, ' Wang Fang '
, ' female ', 1975-02-10,95031);
INSERT into STUDENT (Sno,sname,ssex,sbirthday,class) VALUES (103, ' contacts '
, ' Male ', 1974-06-03,95031);
GO
INSERT into COURSE (cno,cname,tno) VALUES (' 3-105 ', ' Introduction to Computers ', 825)
INSERT into COURSE (cno,cname,tno) VALUES (' 3-245 ', ' operating system ', 804);
INSERT into COURSE (cno,cname,tno) VALUES (' 6-166 ', ' data circuit ', 856);
INSERT into COURSE (cno,cname,tno) VALUES (' 9-888 ', ' advanced math ', 100);
GO
INSERT into Score (Sno,cno,degree) VALUES (103, ' 3-245 ', 86);
INSERT into Score (Sno,cno,degree) VALUES (105, ' 3-245 ', 75);
INSERT into Score (Sno,cno,degree) VALUES (109, ' 3-245 ', 68);
INSERT into Score (Sno,cno,degree) VALUES (103, ' 3-105 ', 92);
INSERT into Score (Sno,cno,degree) VALUES (105, ' 3-105 ', 88);
INSERT into Score (Sno,cno,degree) VALUES (109, ' 3-105 ', 76);
INSERT into Score (Sno,cno,degree) VALUES (101, ' 3-105 ', 64);
INSERT into Score (sno,cno,degree) VALUES (107, ' 3-105 ', 91);
INSERT into Score (sno,cno,degree) VALUES (108, ' 3-105 ', 78);
INSERT into Score (Sno,cno,degree) VALUES (101, ' 6-166 ', 85);
INSERT into Score (sno,cno,degree) VALUES (107, ' 6-106 ', 79);
INSERT into Score (sno,cno,degree) VALUES (108, ' 6-166 ', 81);
GO
INSERT into TEACHER (Tno,tname,tsex,tbirthday,prof,depart)
VALUES (804, ' sung ', ' Male ', ' 1958-12-02 ', ' associate Professor ', ' computer Department ');
INSERT into TEACHER (Tno,tname,tsex,tbirthday,prof,depart)
VALUES (856, ' Zhang Xu ', ' Male ', ' 1969-03-12 ', ' lecturer ', ' Electronic Engineering Department ');
INSERT into TEACHER (Tno,tname,tsex,tbirthday,prof,depart)
VALUES (825, ' Wang Ping ', ' female ', ' 1972-05-05 ', ' ta ', ' computer Department ');
INSERT into TEACHER (Tno,tname,tsex,tbirthday,prof,depart)
VALUES (831, ' Liu Bing ', ' female ', ' 1977-08-14 ', ' ta ', ' Electronic Engineering Department ');

Topic:
1. Query the sname, Ssex, and class columns of all records in the student table.
2, the inquiry teacher all units namely does not duplicate depart column.
3. Query all records of student table.
4. Query all records from 60 to 80 of the scores in the score table.
5. Check the record of 85, 86 or 88 in the score table.
6, inquires the student table "95031" class or the sex is "the female" the classmate record.
7. Query the student table for all records in descending order of class.
8, in CNO Ascending, Degree descending query score all records of the table.
9. Check the number of students in "95031" class.
10. Check the student number and course number of the highest score in the score table.
11. Check the average of the ' 3-105 ' course.
12.Check the average score for a course that has at least 5 students enrolled in the score table and starts with 3.
13, the query minimum score is greater than 70, the highest score is less than 90 sno column.
14. Sname, CNO and degree columns for all students are queried.
15. Check the SNO, CNAME and degree columns of all students.
16. Check the sname, CNAME and degree columns of all students.
17. Check the average of the courses selected in the "95033" class.
18. Assume that a grade table is created using the following command:
CREATE table Grade (low number (3,0), UPP number (3), rank char (1));
Insert into grade values (90,100, ' A ');
Insert into grade values (80,89, ' B ');
Insert into grade values (70,79, ' C ');
Insert into grade values (60,69, ' D ');
Insert into grade values (0,59, ' E ');
Commit
The SNO, CNO and rank columns of all students are now queried.
19, the query elective "3-105" course performance is higher than the "109" student scores of all the students record.
20, inquires score to choose to learn more than one of the students in the score is a record of non-highest scores.
21, the results of the inquiry is higher than the number of "109", the course number is "3-105" of all records.
22. The SNO, sname and Sbirthday of all students who were born in the same year were queried and studied for 108.
23, inquires "Zhang Xu" the Teacher classroom student achievement.
24. The name of the teacher who has more than 5 students who have enrolled in a course.
25. Check the records of all students in class 95033 and 95031.
26, the inquiry existence has 85 points above the course CNO.
27. Find out the results table of the "computer Department" teacher teaching course.
28, query "computer department" and "Electronic Engineering department" different titles of teachers Tname and Prof.
29, the inquiry elective number is "3-105" course and the result is at least higher than the elective number "3-245" classmate's CNO, Sno and degree, and according to degree from high to low order.
30, inquires the elective number is "3-105" and the result is higher than the elective number is "3-245" the course schoolmate's CNO, Sno and degree.
31. Check the name, sex and birthday of all teachers and classmates.
32, query All "female" teacher and "female" classmate's name, Sex and birthday.
33. Check the scores of students who have a lower average score than the course.
34. Check the Tname and depart of all classroom teachers.
35 Check the Tname and depart of all teachers who have not lectured.
36. Check the class number of at least 2 boys.
37, inquires the student table the surname "the king" the classmate record.
38. Check the name and age of each student in the student table.
39. Query the maximum and minimum sbirthday date values in the student table.
40. Check all records in the student table in order of class number and age from large to small.
41. Query "male" teachers and their courses.
42. Check the SNO, CNO and degree columns of the students with the highest score.
43, inquiries and "Li June" with the gender of all students sname.
44, inquiries and "Li June" with the same sex and classmates sname.
45. Check the scores of all "male" students who took the "Introduction to Computer" course
Reference Answer:
1. SELECT Sname,ssex,class from STUDENT;

2. SELECT DISTINCT depart from TEACHER;

3. SELECT * from STUDENT;

4. SELECT * FROM score WHERE degree between and 80;

5.SELECT * FROM score WHERE degree in (85,86,88);

6. SELECT * from STUDENT WHERE class= ' 95031 ' OR ssex= ' female ';

7.SELECT * from STUDENT ORDER by CLASS DESC;

8.SELECT * FROM score ORDER by CNO Asc,degree DESC;

9.SELECT COUNT (*) from STUDENT WHERE class= ' 95031 ';

10.SELECT Sno,cno from Score WHERE degree= (SELECT MAX (degree) from score);

SELECT Sno,cno from score ORDER by degree DESC LIMIT 1;

11.SELECT AVG (degree) from score WHERE cno= ' 3-105 ';

12.select avg (degree), CNO
From score
Where CNO like ' 3% '
GROUP BY CNO
Having count (Sno) >= 5;

13.SELECT SNO from score GROUP by SNO have MIN (degree) >70 and MAX (degree) <90;

14.SELECT A.sname,b.cno,b.degree from STUDENT as A joins score as B on A.sno=b.sno;

15.SELECT A.cname, B.sno,b.degree from COURSE as A joins score as B on A.CNO=B.CNO;

16.SELECT a.sname,b.cname,c.degree from STUDENT A JOIN (COURSE b,score C)
On A.sno=c.sno and B.cno =c.cno;

17.SELECT AVG (A.degree) from score A joins STUDENT B on a.sno = B.sno WHERE b.class= ' 95033 ';

18.SELECT A.sno,a.cno,b.rank from score a,grade B WHERE a.degree between B.low and B.upp

ORDER by RANK;

19.SELECT a.* from score A JOIN score B WHERE a.cno= ' 3-105 ' and A.degree>b.degree and

B.sno= ' 109 ' and b.cno= ' 3-105 ';
Another solution: Select a.* from score A WHERE a.cno= ' 3-105 ' and A.degree>all (select degree from

Score B WHERE b.sno= ' 109 ' and b.cno= ' 3-105 ');

20.SELECT * FROM score S WHERE degree< (SELECT MAX (degree) from score) GROUP by SNO have

COUNT (SNO) >1 ORDER by degree;

21. See the second solution of 19

22. Select Sno,sname,sbirthday from STUDENT WHERE year (sbirthday) = (select year (sbirthday)

From STUDENT WHERE sno= ' 108 ');
Oracle:select X.cno,x.sno,x.degree from score X,score y where X.degree>y.degree and

Y.sno= ' 109 ' and y.cno= ' 3-105 ';
Select Cno,sno,degree from score where degree > (select degree from score where sno= ' 109 '

and cno= ' 3-105 ')

23.SELECT A.sno,a.degree from Score A JOIN (TEACHER b,course C)
On A.cno=c.cno and B.tno=c.tno
WHERE b.tname= ' Zhang Xu ';
Another solution: Select Cno,sno,degree from Score where cno= (select X.cno from course x,teacher y

where X.tno=y.tno and Y.tname= ' Zhang Xu ');
According to the actual explain this SELECT statement, the first scan count is less than the second

24.SELECT a.tname from TEACHER A joins (COURSE B, score C) on (A.tno=b.tno and B.CNO=C.CNO)

GROUP by C.cno have COUNT (C.CNO) >5;
Another solution: Select Tname from teacher where TNO in (select X.tno from course X,score y where

X.CNO=Y.CNO GROUP BY X.tno have Count (X.tno) >5);
Actual test 1 is significantly better than 2


25. Select Cno,sno,degree from score where cno= (select X.cno from course X,teacher y where

X.tno=y.tno and Y.tname= ' Zhang Xu ');

26. SELECT CNO from score GROUP by CNO have MAX (degree) >85;
Another solution: SELECT distinct CNO from score where degree in (select degree from score where

DEGREE&GT;85);

27. SELECT a.* from Score A joins (TEACHER b,course C) on A.cno=c.cno and B.tno=c.tno
WHERE b.depart= ' computer system ';
Another solution: SELECT * from score where CNO in (select A.cno from Course a joins teacher B on

A.tno=b.tno and b.depart= ' Computer Department ');
At this point 2 is slightly better than 1, and the performance will drop rapidly in multi-connection situations.

28. Select Tname,prof from teacher where depart= ' computer system ' and Prof not in (select Prof from

Teacher where depart= ' electronic Engineering Department ');

29. SELECT * FROM score where Degree>any (select degree from score where cno= ' 3-245 ') ORDER

by degree DESC;

30. SELECT * FROM score where Degree>all (select degree from score where cno= ' 3-245 ') ORDER

by degree DESC;

31.SELECT SNAME as NAME, SSEX as SEX, Sbirthday as BIRTHDAY from STUDENT
UNION
SELECT Tname as NAME, tsex as SEX, Tbirthday as BIRTHDAY from TEACHER;

32.SELECT SNAME as NAME, SSEX as SEX, Sbirthday as BIRTHDAY from STUDENT WHERE ssex= ' women '
UNION
SELECT Tname as NAME, tsex as SEX, Tbirthday as BIRTHDAY from TEACHER WHERE tsex= ' women ';

33.SELECT a.* from score A where degree< (SELECT AVG (degree) from score B where a.cno=b.cno);
Must pay attention to ******** this question

34. Solution One: SELECT a.tname,a.depart from TEACHER A joins COURSE B on A.tno=b.tno;
Solution Two: Select Tname,depart from teacher a where exists
(SELECT * from course b where a.tno=b.tno);
Solution Three: Select Tname,depart from TEACHER WHERE TNO in (select TNO from COURSE);

The actual analysis, the first kind of disclosure looks better, at least scan the least.

35. Solution One: SELECT tname,depart from TEACHER A left JOIN COURSE B USING (TNO) WHERE Isnul

(B.TNO);
Solution Two: Select Tname,depart from teacher a where not exists
(SELECT * from course b where a.tno=b.tno);
Solution Three: Select Tname,depart from TEACHER WHERE TNO not in (select TNO from COURSE);
The less in method is the least efficient and the other two are almost

36.SELECT CLASS from STUDENT A WHERE ssex= ' Men ' GROUP by CLASS have COUNT (SSEX) >1;

37.SELECT * from STUDENT A WHERE SNAME isn't like ' King% ';

38.SELECT SNAME, (Year (now ())-year (Sbirthday)) as the age from STUDENT;

39.select Sname,sbirthday as Themax from student where Sbirthday = (select min (sbirthday)

From student)
Union
Select Sname,sbirthday as themin from student where Sbirthday = (select Max (Sbirthday) from

Student);

40.SELECT CLASS, (Year (now ())-year (Sbirthday)) as-an-STUDENT ORDER by CLASS Desc,age

DESC;

41.SELECT a.tname,b.cname from TEACHER A JOIN COURSE B USING (TNO) WHERE a.tsex= ' male ';

42.SELECT a.* from score A WHERE degree= (SELECT MAX (degree) from score B);

43.SELECT SNAME from STUDENT A where ssex= (SELECT SSEX from STUDENT B where B.sname= ' Li June ');

44.SELECT SNAME from STUDENT A where ssex= (SELECT SSEX from STUDENT B where B.sname= ' Li June ')
and class= (SELECT CLASS from STUDENT C WHERE c.sname= ' Li June ');

45. Solution One: SELECT a.* from score A JOIN (STUDENT b,course C) USING (sno,cno) WHERE b.ssex= ' male

' and c.cname= ' computer Introduction ';
Solution Two: SELECT * from score where Sno in (select Sno from student where
ssex= ' Male ') and cno= (select CNO from Course
Where cname= ' Introduction to computers ');

(go) Classic SQL exercises