First, create a database with a login form. Student Table Curriculum Schedule table
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.
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 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 ');
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.
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 Check 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
Improve your exercise:
The database realizes the function of adding and removing and checking and modifying
Main functions:
(1) First to have a login interface (you can do the appropriate restrictions)
(2) Students can choose their favorite subjects according to their own conditions.
(3) Students can modify their chosen subjects according to their own conditions.
(4) Students can delete their favorite subjects according to their own conditions.
(5) Students can view their selection results and achievements
Exercises on SQL Server