SQL query language exercise topic:
First, please build the following four tables first:
Student table
NO
Name
SEX
Birthday
Class
108
Zenghua
male
09/01/77
95033
105
Clear
male
10/02/75
95031
107
Wang Li
Female
01/23/76
95033
101
Li Jun
male
02/20/76
95033
109
Wang Fang
Female
02/10/75
95031
103
army
male
06/03/74
95031
Teacher Table
NO
Name
SEX
Birthday
PROF
Depart
804
Li Cheng
male
12/02/58
Associate Professor
computer science
856
Zhang Xu
male
03/12/69
lecturer
Electronic Engineering
825
Wang Ping
Female
05/05/72
Assistant
computer science
831
Liu Bing
Female
08/14/77
Assistant
Electronic Engineering
Course table
Cno
CNAME
TNO
3-105
Introduction to Computer
825
3-245
operating system
804
6-166
Digital circuits
856
9-888
advanced mathematics
100
SCORE table
NO
Cno
Degree
103
3-245
84
105
3-245
67
109
3-245
98
103
3-105
78
105
3-105
91
109
3-105
68
101
3-105
93
107
3-105
72
108
3-105
66
101
6-166
80
107
6-166
77
108
6-166
62
1. Query the Name, SEX, Class column recorded by the Student table
Select Name, SEX, CLASS
From student;
2. Show the unit of teacher work (not repeated)
Select Distinct Depart
From tea;
3. Display the Student Table All Records in Chinese Character Attributes
Select no as' student number ', name as' Name, SEX AS' Gender ', Birthday As' Birthday', Class AS 'Class'
From student;
4. The name of the students between 60 and 80
Select Distinct Name
From student, score
Where student.no = score.no and degree BetWeen 60 and 80;
5. Record of query results is 80, 85, or 88
SELECT *
From score
WHERE Degree in ('80', '85', '88');
6. Inquire'
95031
'
Class or gender is the classmate record of 'female'
SELECT *
From student
WHERE CLASS = '95031' or sex = 'female';
7. Display the record in Student in descending order in "class"
SELECT *
From student
ORDER by class desc;
8. Single order in CNO, Degree descended records display records in score
SELECT *
From score
Order by CNO, Degree DESC;
9. Display '
95031
'
The number of students in the class
SELECT Count (*)
From student
WHERE CLASS = '95031';
10. Display the number of students in the score table in the score, SELECT TOP 1 NO, CNO
From score
Order by degree desc;
11. Display '3
-105
'
Number of courses average score
SELECT AVG (Degree)
From score
WHERE CNO = '3-105';
12. Show at least 5 students elective and use '
3
'
The average score of the beginning of the course number
SELECT AVG (Degree)
From score
WHERE CNO LIKE '3%'
Having count (no)> = 5;
13. Show the lowest score greater than 70, the highest score below 90 NO columns
Select no
From score
GROUP BY NO
Having min> 70 and max (degree) <90;
14. Query students' name, the course number and the results of the course
SELECT NAME, CNO, Degree
From student, score
Where student.no = score.no;
15. Query the average score of the course selected by the 95033 class
SELECT AVG (Degree)
From score
WHERE NO IN
(
Select no
From student
WHERE CLASS = '95033'
);
16. Record of all classmates that have a higher score of 3-105 courses above 109 students
SELECT *
From score
Where no! = '109' and cno = '3-105' and degree>
(
SELECT DEGREE
From score
WHERE CNO = '3-105' and no = '109'
);
17. Query the score of the classmates of the elective multi-dochi course as the record of non-highest grades
18. The results of the query are higher than the study number '
109
'
No., the course number is' 3
-105
'
All records of grades
SELECT *
From score
Where degree>
(
SELECT DEGREE
From score
WHERE NO = '109' and cno = '3-105'
);
19. Query and learn from 108 students from all students of all students of all students born in the same year
20. Show "Zhang Xu" teacher name and grade
Select Name, Degree
From student, score
Where student.no = score.no and cn in
(
SELECT CNO
From court
WHERE TNO IN
(
Select no
From tea
Where name = 'Zhang Xu'
)
);
twenty one. The number of students who have a classmate who have been elective a course is more than 5 people
Select Name
From tea
WHERE NO IN
(
SELECT TNO
From court
WHERE CNO IN
(
SELECT CNO
From score
GROUP BY CNO
Having Count (CNO)> 5
)
);
twenty two. Show the record of 95033 class and 95031 class
SELECT *
From student
WHERE CLASS IN ('95033', '95031');
twenty three. Check the course number with more than 85 points of grades
Select Distinct CNO
From score
WHERE Degree> 85;
twenty four. Query the transcripts of the "Computer System" teachers
SELECT DEGREE
From score
WHERE CNO IN
(
SELECT CNO
From court
WHERE TNO IN
(
Select no
From tea
Where depart = 'computer system
)
);
25. Query the Name and PROF of the "Computer System" and "Electronics Engineering" different titles
26. Query Elective Courses Yes "3
-105
"
Courses and at least higher than the elective course number "3
-245
"
Corps's CNO, NO, and Degree, and Degree is arranged from high to low order
SELECT *
From score
WHERE CNO = '3-105' and degree>
(
SELECT TOP 1 Degree
From score
WHERE CNO = '3-245'
Order by Degree DESC
)
Order by degree desc;
27. Listed the number of elective numbers on the screen "3
-105
"
And the grades are higher than the elective course number "3
-245
"
Course classmates CNO, NO, Degree
SELECT *
From score
WHERE CNO = '3-105' and degree>
(
SELECT TOP 1 Degree
From score
WHERE CNO = '3-245'
Order by Degree
);
28. The results of the results of classmates with low average scores than this course
29. Query all unknown teachers' Name and depart
Select Name, Depart
From tea
WHERE NO =
(
SELECT TNO
From court
Where CNO Not in IN
(
SELECT CNO
From score
GROUP BY CNO
)
);
30. Check at least 2 boys' class numbers
31. I don't surname the "Wang" classmate record
SELECT *
From student
WHERE NAME NOT LIKE 'King%'
32. Query each student's name and age
33. Query the maximum and minimum birthday value and its name
34. Query all information in Student with the class number and age from the big to small order
SELECT *
From student
Order by class desc, birthday ASC;
35. Query male teachers and their names
Select Name, CNAME
From Teacher, Course
Where teacher.no = course.tno;
36. Query the name of the students with the lowest average and their average results
37. Query and "Li Jun" name
Select Name
From student
WHERE sex =
(
SELECT SEX
From student
WHERE Name = 'Li Jun'
);
38. Query and "Li Jun" name of all students of the same gender but does not work
Select Name
From student
WHERE sex =
(
SELECT SEX
From student
WHERE Name = 'Li Jun'
)
AND
Class! =
(
Select Class
From student
WHERE Name = 'Li Jun');
39. Check all the male student transcripts of all elective "computer introduction"
Select Student.no, Name, Degree
From score, student
Where student.no = score.no and cno =
(
SELECT CNO
From court
WHERE CNAME = 'Computer Inspiration'
);
Second, first build the table of the "Introduction to the Database System" on page 80, then complete the following exercises.
1. Query all projects in Shanghai for Shanghai
SELECT *
From j
WHERE city = 'Shanghai';
2. Remove the lightweight part code
SELECT TOP 1 PNO
From p
ORDER by weight;
3. Isolated from the supplier document for the part of the part
Select Distinct Sno
From SPJ
WHERE JNO = 'J1';
4. Isolated to the supplier of Part P1 for engineering J1
SELECT SNO
From SPJ
WHERE JNO = 'J1' and PNO = 'P1';
5. Query the project name of the supplier S1
Select Jname
From j
WHERE JNO IN
(
Select JNO
From SPJ
WHERE SNO = 'S1'
);
6. Query the colors of the parts provided by the supplier S1
Select Distinct Color
From p
WHERE PNO IN
(
SELECT PNO
From SPJ
WHERE SNO = 'S1'
);
7. Query the supplier of suppliers for engineering J1 or J2
Select Distinct Sno
From SPJ
WHERE JNO = 'J1' or JNO = 'J2';
8. Query the supplier of red parts for engineering J1
Select Distinct spj.sno
From SPJ, P
WHERE JNO = 'J1' and spj.pno = p.Pno and color = 'red';
9. The query is the supplier of the supplier for the city's project for Shanghai.
Select Distinct spj.sno
From SPJ, J
Where spj.jno = j.jno and city in ('Shanghai');
10. Query the supplier of red parts for the city of Shanghai or Beijing
Select Distinct spj.sno
From SPJ, J, P
WHERE spj.pno = p.Pno and color = 'red' and spj.jno = j.jno and city in ('Shanghai', 'Beijing');
11. Search for the supplier and project of the same supplier provided by the project
Select Distinct PNO
From SPJ, S, J
WHERE spj.sno = s.sno and s.city = j.city;
12. Query Shanghai's supplier to provide part of any of the projects of Shanghai
SELECT PNO
From SPJ, S, J
WHERE spj.sno = s.sno and s.city = 'Shanghai' and spj.jno = j.jno and j.city = 'Shanghai';
13. The query is at least one and the project does not provide part of the supplier of the same city. Check the code from Shanghai supplier does not provide any part of the project
Select Distinct JNO
From SPJ
WHERE JNO NOT IN
(
Select JNO
From S, SPJ
Where spj.sno = s.sno and city = 'Shanghai'
);
15. Query such suppliers, they can provide at least one part provided by the supplier of red parts
16. The query is supplied by the supplier S1.
Select Distinct JNO
From SPJ
WHERE SNO = 'S1';
