SQL DATABASE Statement Practice topics

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 s

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.

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

11. Check the average score of each 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 score is greater than 70, less than 90 of the 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 score of "95033" class students.

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

CREATE table grade (Low Int,upp Int,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.

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

20, the inquiry score to choose to learn many courses of the students score is the 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. 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 the "male" students who have enrolled in the "Introduction to Computer" course.

SQL DATABASE Statement Practice topics