MySQL Database Exercises

I. A database comprising four tables: Student table (Student), course Schedule (Course), score table (score) and teacher Information sheet (Teacher). The structure of the four tables, as shown in table 1-1 (i) to table (iv), is shown in table 1-2 table (i) ~ table (iv). Create four tables with SQL statements and complete related topics.

Table 1-1 table Structure of the database

Table (i) Student (student table)

Property name	Data type	Can be empty	Meaning
Sno	Char (3)	Whether	Study number (main code)
Sname	Char (8)	Whether	Student Name
Ssex	Char (2)	Whether	Student Sex
Sbirthday	Datetime	Can	Student's date of birth
Class	Char (5)	Can	Students in the same class

Table (ii) Course (curriculum)

Property name	Data type	Can be empty	Meaning
Cno	Char (5)	Whether	Course Number (main code)
Cname	Varchar (10)	Whether	Course Name
Tno	Char (3)	Whether	Faculty Number (external code)

Table (iii) score (score table)

Property name	Data type	can be empty	meaning
Sno	Char (3)	No	School Number (outside code)
Cno	Char (5)	No	Course Number (outside code)
degree	Decimal)	can	Results
Main code: sno+ Cno

Table (iv) Teacher (Teacher's table)

Property name	Data type	Can be empty	Meaning
Tno	Char (3)	Whether	Faculty Number (main code)
Tname	Char (4)	Whether	Faculty Name
Tsex	Char (2)	Whether	Faculty Sex
Tbirthday	Datetime	Can	Faculty Birth date
Prof	Char (6)	Can	Title
Depart	Varchar (10)	Whether	Faculty Department

Table 1-2 data in the database

Table (i) Student

Sno	Sname	Ssex	Sbirthday	Class
108	Zenghua	Man	1977-09-01	95033
105	Kuanming	Man	1975-10-02	95031
107	Wang Li	Woman	1976-01-23	95033
101	Li June	Man	1976-02-20	95033
109	Wang fang	Woman	1975-02-10	95031
103	Contacts	Man	1974-06-03	95031

Table (ii) Course

Cno	Cname	Tno
3-105	Introduction to Computers	825
3-245	Operating system	804
6-166	Digital circuit	856
9-888	Advanced mathematics	831

Table (iii) score

Sno	Cno	Degree
103	3-245	86
105	3-245	75
109	3-245	68
103	3-105	92
105	3-105	88
109	3-105	76
101	3-105	64
107	3-105	91
108	3-105	78
101	6-166	85
107	6-166	79
108	6-166	81

Table (iv) Teacher

Tno	Tname	Tsex	Tbirthday	Prof	Depart
804	Sung	Man	1958-12-02	Associate professor	Computer Department
856	Zhang Xu	Man	1969-03-12	Lecturer	Department of Electronic Engineering
825	Wang ping	Woman	1972-05-05	Ta	Computer Department
831	Liu Bing	Woman	1977-08-14	Ta	Department of Electronic Engineering

1. Query the sname, Ssex, and class columns of all records in the student table.

Select from Student

2, the inquiry teacher all units namely does not duplicate depart column.

Select distinct depart from teacher

3. Query all records of student table.

4. Query all records from 60 to 80 of the scores in the score table.

Between and

5. Check the record of 85, 86 or 88 in the score table.

Degree in (85,86,88) not in

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.

Select * FROM Student order BY class Desc

8, in CNO Ascending, Degree descending query score all records of the table.

Con Asc,degree desc

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. (sub-query or sort)

Sort: Select sno con from score order BY degree DESC limit 0,1

Sub-Query method: Select Sno,cno from Score where degree= (select Max (degree) from score)

Subqueries: Query statements query one or a list of results, which can be used as parameters of other query statements, which is a subquery, that is, the nesting of queries.

11. Check the average score of each course. Group BY course, then ask for the average of each course.

Select avg (degree), CNO from score group by CNO

12. Check the average score of the course with at least 5 students enrolled in the score table and begin with 3.

Select avg (degree), CNO from score where CNO in (

Select CNO from score where CNO like ' 3% ' GROUP by CNO have Count (*) >=5

) Group BY CNO

Query a fuzzy query for course information beginning with 3

Select * FROM score where CNO like ' 3% '

Number of course numbers with an elective course greater than or equal to 5

Select CNO from score GROUP by CNO have Count (*) >=5

13, the query score is greater than 70, less than 90 of the SNO column.

14. Sname, CNO and degree columns for all students are queried.

Select (select Sname from student where Student.sno=score.sno), Sno,cno,degree from score

Select Sname,cno,degree from Score,student where Score,sno=student Sno

SELECT * from a, b flute kardzhiy

Select Sname,cno,degree from score join student on Score.sno=student.sno

15. Check the SNO, CNAME and degree columns of all students.

Select Sno,cname,degree from score join course on SCORE.CNO=COURSE.CNO

16. Check the sname, CNAME and degree columns of all students.

Select Sname,cname,degree from score join course on SCORE.CNO=COURSE.CNO join student on Score.sno=student.sno

17. Check the average score of "95033" class students.

Select AVG (degree) from score join student on Score.sno=student.sno where class= ' 95033 '

Select AVG (degree) from score where Sno in (select Sno from student where class= ' 95033 ')

18. Assume that a grade table is created using the following command:

CREATE table grade (low int (3), Upp int (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 ')

The SNO, CNO and rank columns of all students are now queried.

Select Sno,cno,rank from score join grade on Score.drgree between Grade.low and Grade.upp

19, the query elective "3-105" course performance is higher than the "109" student scores of all the students record.

SELECT * FROM score where cno= ' 3-105 ' &&degree> (select degree from score where cno= ' 3-105 ' &&sno= ' 109 ')

20, the inquiry score to choose to learn many courses of the students score is the record of non-highest scores.

SELECT * FROM score a where Sno in (select Sno to score GROUP by SNO have Count (*) >1) and degree < (select Max (degree) from score b where b.cno=a.cno)

21, the results of the inquiry is higher than the number of "109", the course number is "3-105" of all records.

SELECT * from score where degree > (select degree from score where sno= ' 109 ' && cno= ' 3-105 ')

22. The SNO, sname and Sbirthday of all students who were born in the same year were queried and studied for 108.

Select Sno,sname,sbirthday from student where year (Birthday) = (select year (sbirthday) from student where sno= ' 108 ')

23, inquires "Zhang Xu" the Teacher classroom student achievement.

SELECT * FROM score joins course on Score.cno=course.cno joins teacher on Course.tno=teacher.tno where Tname= ' Zhang Xu '

SELECT * FROM Score whre cno in (select CNO from Course where tno= (select TNO from teacher where Tname = ' Zhang Xu '))

24. The name of the teacher who has more than 5 students who have enrolled in a course.

Select Tname CNO from score join course on SCORE.CNO=COURSE.CNO join teacher on Course.tno=teacher.tno GROUP by CNO Havi ng Count (*) >=5

Select Tname from teacher where TNO in (select Tno from Course where CNO in (select CNO from Score GROUP by CNO have CO UNT (*) >5))

25. Check the records of all students in class 95033 and 95031.

Select * FROM student where class = ' 95033 ' or class= ' 95031 '

26, the inquiry existence has 85 points above the course CNO.

Select distinct CNO from score where degree>=85

27. Find out the results table of the "computer Department" teacher teaching course.

SELECT * FROM score joins course on Score.cno=course.cno joins teacher on Teacher.tno=course.tno where depart= ' computer system '

SELECT * FROM score where CNO in (select CNO from Course where TNO on (select TNO from teacher where depart = ' computer system '))

28, query "computer department" and "Electronic Engineering department" different titles of teachers Tname and Prof.

SELECT * FROM teacher Whre prof not in (select Prof from teacher where depart= ' computer system ' and Prof in (select Prof from teacher Where depart= ' electronic Engineering Department '))

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.

SELECT * FROM score where cno= ' 3-105 ' and degree >any (Select degree from score where cno = ' 3-105 ') Order by degree D Esc

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.

SELECT * FROM score where cno= ' 3-105 ' and degree >all (select degree from score where cno = ' 3-105 ')

31. Check the name, sex and birthday of all teachers and classmates.

Select Sname,ssex,sbirthday from Student

Union

Select Tname,tsex,tbirthday from teacher

32, query All "female" teacher and "female" classmate's name, Sex and birthday.

Select Sname,ssex,sbirthday from student where ssex= ' woman '

Union

Select Tname,tsex,tbirthday from teacher where tsex= ' woman '

33. Check the scores of students who have a lower average score than the course.

SELECT * FROM Score a WHERE degree< (select AVG (degree) from score b where a.cno=b.cno)

34. Check the Tname and depart of all classroom teachers.

Select Tname,depart from teacher where TNO in (select Tno from Course where CNO in (select CNO from Score))

35. Inquire about the Tname and depart of all teachers who have not lectured.

Select Tname,depart from teacher where TNO not in (select TNO from Course where CNO in (select distinct CNO from score ) )

36. Check the class number of at least 2 boys.

Select class from student where ssex= ' Man ' GROUP by class has count (*) >=2

37, inquires the student table the surname "the king" the classmate record.

SELECT * FROM student where sname don't like ' King% '

38. Check the name and age of each student in the student table.

Select Sname,year (now ())-year (Birthday) from student

39. Query the maximum and minimum sbirthday date values in the student table.

Select Max (sbirthday), min (birthday) from student

40. Check all records in the student table in order of class number and age from large to small.

Select * FROM Student Order BY class DESC, Sbirthday

41. Query "male" teachers and their courses.

Select Tname,cname from teacher joins course on course. Tno=teacher. Tno where tsex= ' man '

SELECT *

42. Check the SNO, CNO and degree columns of the students with the highest score.

SELECT * FROM score where degree= (select Max (degree) from score)

SELECT * FROM score ORDER BY degree DESC limit 0,1

43, inquiries and "Li June" with the gender of all students sname.

Select sname from student where ssex= (select Ssex from student where Sname= ' Li June ')

44, inquiries and "Li June" with the same sex and classmates sname.

Select sname from student where ssex= (select Ssex from student where Sname= ' Li June ') && class= (select class from Studen t where Sname= ' Li June ')

45. Check the scores of all the "male" students who have enrolled in the "Introduction to Computer" course.

SELECT * FROM score where cno= (select CNO from course where cname= ' Introduction to Computers ') && sno in (select Sno from student wher E ssex= ' male ')

MySQL Database Exercises