[Go] Classic SQL exercises

Original topic from QAZ13177_58_CSDN blog http://blog.csdn.net/qaz13177_58_/article/details/5575711/#sql

Just update your personal answers for reference

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 of the course with at least 5 students enrolled in the score table and begin 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
Individual answers (no validation, no errors please note that only for review grammar, regardless of performance and rationality):

SELECT * FROM dbo. Courseselect * FROM dbo. Scoreselect * FROM dbo. Studentselect * FROM dbo. Teacherselect * FROM grade--the answer is mostly done with the fly correlation subquery (because of the high efficiency), the related subquery is replaced by the join statement, but there is also a place to write-related subqueries, only to exercise more than 1 of the wording. SELECT Sname,ssex,class from STUDENT
2.SELECT Count (*) as the number of teachers, depart from TEACHER GROUP by DEPART3. SELECT * from STUDENT4. SELECT * from score where degree between and 805.SELECT * from score WHERE degree in (85,86,88) 6.SELECT * from STUDENT WHERE ssex= ' female ' OR class= ' 95031 ' 7.SELECT * from the STUDENT ORDER by CLASS DESC8. SELECT * FROM score ORDER by CNO Asc,degree DESC9. Select COUNT (*) as number of STUDENT where class= ' 95031 ' 10.SELECT sno,cno from score where degree= (SELECT MAX (degree) from S CORE) SELECT TOP 1 sno,cno from score ORDER by degree DESC11.  Select CONVERT (DECIMAL (10,2), AVG (degree)) from score where cno= ' 3-105 '/12.with t_c as (SELECT CNO from score WHERE CNO like ' 3% ' GROUP by CNO have COUNT (CNO) >5) SELECT AVG (degree) as ' more than five students elective and 3 all scores of all courses beginning with the average ' from score WHERE CNO in (sele CT t_c.cno from T_c) 13.SELECT SNO to score GROUP by SNO have MIN (degree) >70 and MAX (degree) <90 ORDER by SNO14. SELECT *, (select SNAME from STUDENT S WHERE S.sno=score. SNO) as student name from score ORDER by SNO15. SELECT *, (select CNAME FROM COURSE S WHERE s.cno=score. CNO) As course from score ORDER by SNO16. Select Degree as Score, (select SNAME from STUDENT S WHERE S.sno=score. SNO) as student name, (SELECT CNAME from COURSE T WHERE T.cno=score. CNO) As course from score ORDER by student name 17. Select AVG (degree) from score where SNO in (select SNO from STUDENT where class= ' 95033 ') 18.SELECT sno,cno, (select RANK FRO M grade G WHERE degree between G.low and G.upp) as RANK from SCORE19. SELECT * FROM score where cno= ' 3-105 ' and degree> (select TOP 1 degree from score where cno= ' 3-105 ' and sno=109) 20.SELEC T * FROM Score WHERE SNO in (select SNO from score GROUP by SNO have COUNT (SNO) >1) and degree<> (select MAX (degre E) from score) ORDER by SNO21. SELECT * FROM score where cno= ' 3-105 ' and degree> (select TOP 1 degree from score where cno= ' 3-105 ' and sno=109) 22.SELEC T sno,sname,sbirthday from STUDENT WHERE DATEPART (yyyy,sbirthday) = (SELECT TOP 1 DATEPART (yyyy,sbirthday) from STUDENT WHE RE sno=108) 23.SELECT * from score WHERE cno= (SELECT CNO from COURSE where tno= (SELECT TOP 1 TNO from TEACHER WHERE tname= ' Zhang Xu ')) 24. 
With TEMP as (select Cno,count (CNO) as Stucount-score GROUP by CNO have COUNT (CNO) >5) SELECT tname as teacher name, C.CNA ME as course, TEMP. Stucount as repair number from TEACHER T left join COURSE C on T.tno=c.tno INNER JOIN TEMP on C.cno=temp. CNO25.  SELECT * from STUDENT where CLASS in (' 95033 ', ' 95031 ') 26.SELECT DISTINCT CNO from score where Degree>8527.select * from Score S WHERE EXISTS ((select CNO from COURSE where TNO on (select TNO from TEACHER where depart= ' computer system ') and CNO=S.CNO)-                   -only exercise using the EXISTS keyword exists and in is the difference between the--in first executes the inner layer exists first executes the outer layers--so according to the data of the parent-child query two keywords will have different performance --Therefore, it is not possible to accurately describe the efficiency of in and ex 28.
SELECT * from TEACHER WHERE PROF not in (select T1. PROF from (select PROF from TEACHER where depart= ' computer system ') T1 INNER JOIN (select PROF from TEACHER where depart= ' electronic Engineering Department ') T 2 on T1. Prof=t2. PROF)--This question I understand whether there is a problem 29. SELECT * FROM score where cno= ' 3-105 ' and degree> (select MAX (degree) from score where cno= ' 3-245 ') ORDER by degree DESC3 0.SELECT * from score where cno= ' 3-105 ' and degree> (SELECT MAX (degree) from score where cno= ' 3-245 ') 31.
SELECT ' student ' as name, ' as gender, null as birthday union Allselect Sname,ssex,sbirthday from STUDENT Union allselect ' teacher ', ', ', NULL union Allselect Tname,tsex,tbirthday from TEACHER
32.SELECT ' student ' as name, ' as gender, NULL as birthday Union Allselect sname,ssex,sbirthday from STUDENT WHERE ssex= ' Women ' Union allselect ' Teacher ', ', NULL UNION allselect tname,tsex,tbirthday from TEACHER WHERE tsex= ' female ' 33.SELECT * from score T WHERE degree> (SEL ECT AVG (degree) as AVGs from score where Cno=t.cno GROUP by CNO) 34.SELECT Tname,depart from TEACHER T WHERE EXISTS (SELECT * from COURSE where Tno=t.tno) 35.SELECT Tname,depart from TEACHER T where is EXISTS (SELECT * from COURSE WHERE tno=t.tno) 36.SELECT CLASS from STUDENT where ssex= ' man ' GROUP by CLASS has COUNT (SSEX) >=237.select * from STUDENT WHERE LTRIM (SN AME) Not like ' King% ' 38.SELECT Sname,datediff (Year,sbirthday,getdate ()) as age, sbirthday from STUDENT
39.SELECT ' Max: ' +convert (NVARCHAR ($), MAX (Sbirthday), all) from STUDENT unionselect ' min: ' +convert (NVARCHAR (+), min (Sbirthday), from STUDENT
40.SELECT * from STUDENT ORDER by Class,sbirthday-Class number Priority SELECT * from STUDENT ORDER by Sbirthday,class-age priority 41. SELECT * from TEACHER T INNER joins COURSE C on T.tno=c.tno and t.tsex= ' man ' 42.
Select TOP 1 * from score ORDER by degree DESC-this is wrong, if 2 students are tied together select * from score where degree= (select MAX (degree) from Score)
43.DECLARE @SEX NVARCHAR (2) SET @SEX = (select TOP 1 SSEX from STUDENT where Sname= ' Li June ') SELECT * from STUDENT WHERE [email PR Otected]select * from STUDENT where ssex= (SELECT TOP 1 SSEX from STUDENT where Sname= ' Li June ')
44.SELECT * from STUDENT where ssex= (select top 1 SSEX from STUDENT where Sname= ' Li June ') and class= (select top 1 CLASS from ST Udent WHERE sname= ' li June ')
45.SELECT * from STUDENT where ssex= ' man ' and SNO in (select DISTINCT SNO from score where cno= (select TOP 1 CNO from COURSE W Here cname= ' Introduction to Computers ')

[go] Classic SQL exercises