Give the needs first, then write a little every day as a practice and consolidation, every day will be updated; insist, refuel!
I. Original data
create database practice;
USE PRACTICE;
CREATE TABLE STUDENT
(SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATE,
CLASS VARCHAR(5));
CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL,
CNAME VARCHAR(10) NOT NULL,
TNO VARCHAR(10) NOT NULL);
CREATE TABLE SCORE
(SNO VARCHAR(3) NOT NULL,
CNO VARCHAR(5) NOT NULL,
DEGREE NUMERIC(10, 1) NOT NULL) ;
CREATE TABLE TEACHER
(TNO VARCHAR(3) NOT NULL,
TNAME VARCHAR(4) NOT NULL, TSEX VARCHAR(2) NOT NULL,
TBIRTHDAY DATETIME NOT NULL, PROF VARCHAR(6),
DEPART VARCHAR(10) NOT NULL);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108,'Hua Zeng'
,'male' ,'1977-09-01',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105,'Kuang Ming'
,'male' ,'1975-10-02',95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107,'Wang Li'
,'female' ,'1976-01-23',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101,'Li Jun'
,'male' ,'1976-02-20',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109,'Wang Fang'
,'female' ,'1975-02-10',95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103,'Lu Jun'
,'male' ,'1974-06-03',95031);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105' ,'Introduction to Computer Science',825);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'operating system' ,804);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'Data circuit' ,856);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'Advanced mathematics' ,100);
INSERTINTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERTINTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERTINTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERTINTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERTINTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERTINTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERTINTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERTINTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERTINTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERTINTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERTINTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERTINTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);
INSERTINTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (804,'Li Cheng','male','1958-12-02','associate professor','Department of Computer Science');
INSERTINTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (856,'Zhang Xu','male','1969-03-12','lecturer','Department of Electronic Engineering');
INSERTINTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (825,'Wang Ping','female','1972-05-05','Assistant','Department of Computer Science');
INSERTINTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (831,'Liu Bing','female','1977-08-14','Assistant','Department of Electronic Engineering');
select*from STUDENT;
select*from COURSE;
select*from SCORE;
select*from TEACHER;|
|
|
|
|
|
Two, demand
1. Query the Sname, Ssex, and Class es columns of all records in the Student table.
2. Query all units of teachers, that is, the unperduplicated Depart column.
3. Query all records of Student table.
4. Query all records in the Score table with scores between 60 and 80.
5. Query the Score table for records of 85, 86 or 88.
6. Query the records of the students in "95031" class or those whose gender is "female" in the Student table.
7. Query all records of Student table in descending order of Class.
8. Query all records of Score table in ascending order of Cno and descending order of Degree.
9. Query the number of students in class 95031.
10. Query the student number and course number with the highest score in the Score table.
11. Search for the average score of `3-105'course.
12. Query the Score table for the average scores of at least five students taking courses that begin with three.
13. The lowest query score is more than 70 and the highest score is less than 90 for Sno columns.
14. Query the Sname, Cno and Degree columns of all students.
15. Query the Sno, Cname and Degree columns of all students.
16. Query the Sname, Cname and Degree columns of all students.
17. Search for the average score of the selected courses in Class 95033.
18. Assume that a grade table is created using the following commands:
create table grade(low number(3,0),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');
commit;
Now we query the Sno, Cno and rank columns of all the students.
19. Query the records of all the students whose grades in the elective "3-105" course are higher than those in the "109" course.
20. Inquire about the record of the non-highest score among the students who have chosen more than one course in score.
21. The inquiry results are higher than all the records of the grades with the student number "109" and the course number "3-105".
22. Query the Sno, Sname and Birrthday columns of all students born in the same year with the number 108.
23. Query the students'achievements of "Zhang Xu" teachers.
24. Find out the names of teachers who have more than 5 students taking a certain course.
25. Query the records of all the students in Class 95033 and Class 95031.
26. Query the existence of courses Cno with 85 or more marks.
27. Query out the results of the courses taught by the teachers in the Computer Department.
28. Search for Teachers with Different Prof essional Titles in Computer Department and Electronic Engineering Department.
29. Query the students whose elective number is "3-105" and their grades are at least higher than those of students whose elective number is "3-245" in Cno, Sno and Degree, and rank them in order of Degree from high to low.
30. Query the students whose elective number is "3-105" and their scores are higher than those of the students whose elective number is "3-245" in terms of No, Sno and Degree.
31. Query the name s, sex and birthday of all teachers and classmates.
32. Query the name s, sex and birthday of all "female" teachers and classmates.
33. Look up the scores of students whose grades are lower than the average of the course.
34. Inquire all teachers'Tname and Depart.
35. Query all teachers who have not given lectures about Tname and Depart.
36. Query the class number of at least two boys.
37. Query the records of students who are not surnamed "Wang" in the Student table.
38. Query 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. Query all records in the Student table in the order of class number and age from older to younger.
41. Query "male" teachers and their courses.
42. Query the No, Cno and Degree columns of the highest score students.
43. Query and "Li Jun" the name of all students of the same sex.
44. Query and "Li Jun" classmate Sname.
45. Search the results sheets of all male students taking the course of Introduction to Computer
2. Query all units of teachers, that is, the unperduplicated Depart column.
3. Query all records of Student table.
4. Query all records in the Score table with scores between 60 and 80.
5. Query the Score table for records of 85, 86 or 88.
6. Query the records of the students in "95031" class or those whose gender is "female" in the Student table.
7. Query all records of Student table in descending order of Class.
8. Query all records of Score table in ascending order of Cno and descending order of Degree.
9. Query the number of students in class 95031.
10. Query the student number and course number with the highest score in the Score table.
11. Search for the average score of `3-105'course.
12. Query the Score table for the average scores of at least five students taking courses that begin with three.
13. The lowest query score is more than 70 and the highest score is less than 90 for Sno columns.
14. Query the Sname, Cno and Degree columns of all students.
15. Query the Sno, Cname and Degree columns of all students.
16. Query the Sname, Cname and Degree columns of all students.
17. Search for the average score of the selected courses in Class 95033.
18. Assume that a grade table is created using the following commands:
create table grade(low number(3,0),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');
commit;
Now we query the Sno, Cno and rank columns of all the students.
19. Query the records of all the students whose grades in the elective "3-105" course are higher than those in the "109" course.
20. Inquire about the record of the non-highest score among the students who have chosen more than one course in score.
21. The inquiry results are higher than all the records of the grades with the student number "109" and the course number "3-105".
22. Query the Sno, Sname and Birrthday columns of all students born in the same year with the number 108.
23. Query the students'achievements of "Zhang Xu" teachers.
24. Find out the names of teachers who have more than 5 students taking a certain course.
25. Query the records of all the students in Class 95033 and Class 95031.
26. Query the existence of courses Cno with 85 or more marks.
27. Query out the results of the courses taught by the teachers in the Computer Department.
28. Search for Teachers with Different Prof essional Titles in Computer Department and Electronic Engineering Department.
29. Query the students whose elective number is "3-105" and their grades are at least higher than those of students whose elective number is "3-245" in Cno, Sno and Degree, and rank them in order of Degree from high to low.
30. Query the students whose elective number is "3-105" and their scores are higher than those of the students whose elective number is "3-245" in terms of No, Sno and Degree.
31. Query the name s, sex and birthday of all teachers and classmates.
32. Query the name s, sex and birthday of all "female" teachers and classmates.
33. Look up the scores of students whose grades are lower than the average of the course.
34. Inquire all teachers'Tname and Depart.
35. Query all teachers who have not given lectures about Tname and Depart.
36. Query the class number of at least two boys.
37. Query the records of students who are not surnamed "Wang" in the Student table.
38. Query 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. Query all records in the Student table in the order of class number and age from older to younger.
41. Query "male" teachers and their courses.
42. Query the No, Cno and Degree columns of the highest score students.
43. Query and "Li Jun" the name of all students of the same sex.
44. Query and "Li Jun" classmate Sname.
45. Search the results sheets of all male students taking the course of Introduction to Computer