20_ students ' Choosing Course Database SQL statement 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)

Field name	Data type	Can be empty	Meaning
Sno	VARCHAR2 (3)	Whether	Study number (primary key)
Sname	VARCHAR2 (8)	Whether	Student Name
Ssex	VARCHAR2 (2)	Whether	Student Sex
Sbirthday	Date	Can	Student's date of birth
Sclass	VARCHAR2 (5)	Can	Students in the same class

Table (ii) Course (curriculum)

Property name	Data type	Can be empty	Meaning
Cno	VARCHAR2 (5)	Whether	Course number (primary key)
Cname	Varchar (10)	Whether	Course Name
Tno	VARCHAR2 (3)	Whether	Faculty Number (foreign key)

Table (iii) score (score table)

Property name	Data type	Can be empty	Meaning
Sno	VARCHAR2 (3)	Whether	School Number (foreign key)
Cno	VARCHAR2 (5)	Whether	Course number (foreign key)
Degree	Number (4,1)	Can	Results
Primary key: sno+ Cno

Table (iv) Teacher (Teacher's table)

Property name	Data type	Can be empty	Meaning
Tno	VARCHAR2 (3)	Whether	Faculty Number (primary key)
Tname	VARCHAR2 (4)	Whether	Faculty Name
Tsex	VARCHAR2 (2)	Whether	Faculty Sex
Tbirthday	Date	Can	Faculty Birth date
Prof	VARCHAR2 (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 T. from STUDENT T.sname,t.ssex,t.class; --t is alias, alias is also OK, no need to do

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

3. Query all records of student table.

Selext * from STUDENT

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

Select t.* from score t where t.degree>60 and t.degree<80;

SELECT * FROM score where degree>60 and degree<80; Both types are available

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

SELECT * FROM score where degree= ' or degree= ' or degree= ' 88 ';

6, inquires the student table "95031" class or the sex is "the female" the classmate record.

SELECT * from STUDENT a where a.sclass= ' 95031 ';

Where the post-write conditional expression (logical judgment), write the query efficiency is high.

SELECT * from STUDENT where sclass= ' 95031 ' or ssex= ' female '; --or meet one can

SELECT * from STUDENT where sclass= ' 95031 ' and ssex= ' women '; --and two simultaneous gratification

7. Query all records of the student table in descending order of class.

Select T.*, t.rowid from STUDENT t ORDER by DESC;

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

Select T.*, t.rowid from score T order by CNO 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)

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.

Select t.* from score t where sno>70 and sno<90;

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

Select Sname,cno,degree from student;

Select Tno,tname,prof,depart from student; --student have select Sname,cno,degree from student; say degree code is invalid

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 number (3), 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 ')

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 result of the inquiry is higher than the record of "109" and the grade of the course number "3-105".

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.

20_ students ' Choosing Course Database SQL statement exercises