SQL Server-select Statement Exercises

Creating tables and entering data

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 ');

Practice Topics

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. [Cartesian product]
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. [The question does not match the answer]
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. Inquire about 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

SQL Statement Exercises Reference answers
1, select Sname,ssex,class from Student;
2, select distinct depart from teacher;
3, select Sno as ' study number ', Sname as ' name ', Ssex as ' sex ', sbirthday as ' Birth date ', class as ' shift ' from student;
Or
Select Sno as study number, Sname as name, Ssex as sex, sbirthday as date of birth, class as shift from student;
4. Select * from score where degree between and 80;
or select * from score where degree>=60 and degree<=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;
or select * FROM Score order by CNO, degree desc;
9, select COUNT (*) as CNT from student where class= ' 95031 ';
10, select Sno as ' study number ', CNO as ' Course number ', degree as ' highest score ' from score
Where degree= (select Max (degree) from score)
11. Select AVG (degree) As course average from score where cno= ' 3-105 ';
12. Select Cno,avg (degree) from score where CNO like ' 3% ' GROUP by CNO have Count (*) >5;
13. Select Sno from Score Group by Sno have min (degree) >70 and Max (degree) <90;
14, select Student. Sname,score. Cno,score.degree from Student,score where student. Sno=score. Sno;
15, select X.sno,y.cname,x.degree from score X,course y where x.cno=y.cno;
16. Select X.sname,y.cname,z.degree from student X,course Y,score Z where X.sno=z.sno and z.cno=y.cno;
17, select Y.cno,avg (y.degree) from student X,score y where X.sno=y.sno and x.class= ' 95033 ' GROUP by Y.CNO;
18. Select Sno,cno,rank from Score,grade where degree between low and UPP order by rank;
19. Select X.cno,x.sno,x.degree from score X,score y
where x.cno= ' 3-105 ' and X.degree>y.degree and y.sno= ' 109 ' and y.cno= ' 3-105 ';
20.
1, query results non-undergraduate highest select * from score B where degree < (select Max (degree) from score a where a.cno=b.cno);
2, the results of the students who are not the highest in the undergraduate course and 2 above are selected:
21, 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 ')
22. Select Sno,sname,sbirthday from student where To_char (Sbirthday, ' yyyy ') = (select To_char (sbirthday, ' yyyy ') from Student where sno= ' 108 ');
23. 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 ');
24. 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);
25. Select * from student where class in (' 95033 ', ' 95031 ');
26. Select distinct CNO from score where degree in (select degree from score where degree>85);
27. Select * FROM score where CNO in (select X.cno from course X,teacher y where Y.tno=x.tno and y.depart= ' computer system ');
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 cno= ' 3-105 ' and Degree>any (select degree from score where cno= ' 3-245 ') Order by degree des C
30. Select * FROM score where cno= ' 3-105 ' and Degree>all (select degree from score where cno= ' 3-245 ');
31. Select Tname,tsex,tbirthday from teacher
Union select Sname,ssex,sbirthday from student;
32. Select Tname,tsex,tbirthday from teacher where tsex= ' female '
Union select Sname,ssex,sbirthday from student where ssex= ' female ';
33. Select * FROM score a WHERE degree< (select AVG (degree)
From score b where a.cno=b.cno);
34. Select Tname,depart from teacher a where exists
(SELECT * from course b where a.tno=b.tno);
35. Select Tname,depart from teacher a where not exists
(SELECT * from course b where a.tno=b.tno);
36. Select class from student where ssex= ' Man ' GROUP by class has count (*) >=2;
37. Select * FROM student where sname don't like ' Wang _ ';
38. Select Sname as Name, (To_char (sysdate, ' yyyy ')-to_char (sbirthday, ' yyyy ')) as age from student
39, select Sname,sbirthday as maximum from student where Sbirthday = (select min (sbirthday) from student)
Union select Sname,sbirthday as min from student where Sbirthday = (select Max (sbirthday) from student)
40, select Class,sname,sbirthday from Student order by class Desc,sbirthday;
41, select X.tname,y.cname from teacher X,course y where x.tno=y.tno and x.tsex= ' male ';
42. Select * FROM score where degree= (select Max (degree) from score);
43. Select Sname from student where ssex= (select Ssex from student where Sname= ' Li June ');
44. Select Sname from student where ssex= (select Ssex from student where Sname= ' Li June ') and class= (select class from Student W Here Sname= ' Li June ');
45. Select * FROM score where Sno in (select Sno from student where ssex= ' man ') and cno= (select CNO from Course
Where cname= ' Introduction to computers ');

SQL Server-select Statement Exercises