After finishing these 33 questions, we don't have to worry about the written sql test any more. We don't talk much nonsense. Let's go straight to the dry goods!!!
1: Title
1. Query the Sname, Ssex, and Class columns of all records in the Student table.
2. Query all the companies of teachers, that is, the non duplicate Depart ment column.
3. Query all records in the Score table with scores between 60 and 80.
4. Query the Score table for records with scores of 85, 86 or 88.
5. Query the records of class "95031" or students with gender "female" in the Student table.
6. Query all records of the Student table in descending Class order.
7. Query all records of the Score table in ascending order of Cno and descending order of Degree.
8. Query the number of students in class "95031".
9. Query the student number and course number with the highest Score in the Score table. (subquery or sorting)
10. Query the average score of each course.
11. Query the average Score of courses starting with 3 for at least 5 students in the Score table.
12. Query the Sname, Cno, and Degree columns for all students.
13. Query the Sname, Cname, and Degree columns for all students.
14. Query the average score of students in class "95033".
15. Query the Sno, Cno and rank columns of all students.
16. Query the records of all students whose grades of "3-105" course are higher than those of "109".
17. Query the record of non highest scores among students who have selected multiple courses in score.
18. Query all records with scores higher than those with student number "109" and course number "3-105".
19. Query the Sno, Sname and Sbirthday columns of all students born in the same year with student number 101.
20. Query the student scores of "Zhang Xu" teachers.
21. Query the names of teachers with more than 5 students taking a course.
22. Find out the grade table of the courses taught by the teachers of the "computer department".
23. Query the Cno, Sno and Degree of the students whose elective number is "3-105" and whose grades are at least higher than those of the students whose elective number is "3-245", and sort them from high to low.
24. Query the students whose elective number is "3-105" and whose grades are higher than those of the students whose elective number is "3-245"
25. Query the name, sex and birthday of all teachers and students
26. Query the name, sex and birthday of all "female" teachers and "female" students
27. Query the grade sheet of students whose scores are lower than the average score of the course.
28. Inquire the class number of at least 2 boys.
29. Query the records of students whose surname is not "Wang" in the Student table.
30. Query the name and age of each Student in the Student table.
31. Query all records in the Student table in the order of class number and age.
32. Query Sname, a classmate of the same sex as "Li Jun"
33. Query the scores of all "male" students who take the course of "Introduction to computer".
2: answer
--1. Query the Sname, Ssex and Class columns of all records in the Student table.
select st.sname,st.ssex,st.class from student st;
--2. Query all companies of teachers, i.e. non duplicate Depart ment column.
select distinct depart from teacher;
--3. Query all records with scores between 60 and 80 in the Score table.
select * from score sc where sc.degree between 60 and 80;
--4. Query the Score of 85, 86 or 88 in the Score table.
select * from score where degree in (85,86,88);
--5. Query the records of class "" or female students in the Student table.
select * from student st where st.class=95031 or st.ssex = 'female'-- routine
select * from student st where st.class=95031 union select * from student s where s.ssex = 'female'-- After optimization
--6. Query all records in the Student table in descending Class order.
select * from student order by class desc;
--7. Query all records in Score table in ascending order of Cno and descending order of Degree.
select * from score order by cno asc,degree desc;
--8. Query the number of students in class "95031".
select count(1) from student where class = 95031;
--9. Query the student number and course number with the highest Score in the Score table.
select sno,cno from score order by degree desc limit 1;
--10. Query the average score of each course.
select cno,AVG(degree) as' average score 'from score group by cno;
--11. Query the average Score of courses with at least 5 students taking elective courses and beginning with 3 in the Score table.
select cno,AVG(degree) from score group by cno having count(sno)>=5 and cno like '3%';
--12. Query the Sname, Cno and Degree columns of all students.
select sname,cno,degree from student st join score sc on st.sno = sc.sno;
--13. Query the Sname, Cname and Degree columns of all students.
select sname,cname,degree from student st join score sc on st.sno = sc.sno join course co on co.cno = sc.cno;
--14. Query the average score of students in class "95033".
select AVG(degree) from score sc join student st on sc.sno = st.sno and st.class = 95033;
--15. Query the Sno, Cno and rank columns of all students:
select sc.sno,sc.cno,gr.rank from score sc join grade gr on sc.degree between gr.low and gr.upp;
--16. Query the records of all students whose grades of "3-105" course are higher than those of "109".
SELECT * FROM student WHERE
sno IN ( SELECT sno FROM score WHERE cno = '3-105' AND degree > ( SELECT degree FROM score WHERE sno = 109 AND cno = '3-105' ) );
--17. Query the record of non highest scores among students who have selected multiple courses in score.
select * from score where sno in(select sno from score group by sno having count(cno)>1 ) and degree != (select MAX(degree) from score);
--18. Query all records with scores higher than those with student number "109" and course number "3-105".
select * from score where degree > (select degree from score where sno = 109 and cno = '3-105');
--19. Query the Sno, Sname and Sbirthday columns of all students born in the same year with student number 101.
select sno,sname,sbirthday from student where YEAR(sbirthday) like (select YEAR(sbirthday) from student where sno = 101) and sno != 101;
--20. Query the student scores of "Zhang Xu" teachers.
select sc.degree from score sc where sc.cno in (select co.cno from teacher te join course co on te.tno = co.tno and te.tname = 'Zhang Xu');
--21. Query the names of teachers with more than 5 students taking a course.
select tname from teacher where tno in
( select co.tno from score sc join course co on sc.cno = co.cno group by sc.cno having count(sc.sno)>5 );
--22. Find out the grade table of the courses taught by the teachers of the "computer department".
Select * from score where CNO in (select co.cno from course co join teacher Te on te.department = 'computer department' and te.tno = co.tno);
--23. Query the Cno, Sno and Degree of students whose elective number is "- 105" and whose grades are at least higher than those of students whose elective number is "- 245", and sort them from high to low.
select * from score where cno='3-105' and degree >
(select max(degree) from score where cno='3-245' )
order by degree desc;
--24. Query the students whose elective number is "3-105" and whose grades are higher than those of the students whose elective number is "3-245"
select cno,sno,degree from score where cno='3-105' and degree >(select max(degree) from score where cno='3-245' );
--25. Query the name, sex and birthday of all teachers and students
select tname,tsex,tbirthday from teacher union select sname ,ssex ,sbirthday from student;
--26. Query the name, sex and birthday of all "female" teachers and "female" students
select tname,tsex,tbirthday ， from teacher where tsex = 'female' union select sname, ssex, sbirthday ， from student where ssex = 'female';
--27. Query the grade sheet of students whose scores are lower than the average score of the course.
select * from score where degree < any(select AVG(degree) from score group by cno);
--28. Inquire the class number of at least 2 boys.
select class from student group by class having count(class)>=2;
--29. Query the records of students whose surname is not "Wang" in the Student table.
select * from student where sname not like 'Wang%';
--30. Query the name and age of each Student in the Student table.
select sname, YEAR(now())-YEAR(sbirthday) as' age 'from student;
--31. Query all records in the Student table in the order of class number and age.
select * from student order by class desc, YEAR(now())-YEAR(sbirthday) desc ;
--32. Query Sname, a classmate of the same sex as "Li Jun"
select sname from student where ssex=(select ssex from student where sname = 'Li Jun') and
class=(select class from student where sname = 'Li Jun');
--33. Query the scores of all "male" students who take the course of "Introduction to computer".
select sc.sno,sc.cno,sc.degree from score sc join course co on sc.cno = co.cno and co.cname = 'introduction to computer' and sno in (select sno from student where ssex = 'male');
3: Script
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cno` char(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`cname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`tno` int(3) DEFAULT NULL,
PRIMARY KEY (`cno`) USING BTREE,
INDEX `tno`(`tno`) USING BTREE,
CONSTRAINT `course_ibfk_1` FOREIGN KEY (`tno`) REFERENCES `teacher` (`tno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'Class Schedule Card' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('3-105', 'Introduction to computer', 825);
INSERT INTO `course` VALUES ('3-245', 'operating system', 804);
INSERT INTO `course` VALUES ('6-166', 'digital circuit ', 856);
INSERT INTO `course` VALUES ('9-888', 'Advanced mathematics', 831);
-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
`low` int(11) DEFAULT NULL,
`upp` int(11) DEFAULT NULL,
`rank` char(1) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci COMMENT = 'Grade table' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of grade
-- ----------------------------
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');
-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sno` int(3) DEFAULT NULL,
`cno` char(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`degree` decimal(4, 1) DEFAULT NULL,
INDEX `sno`(`sno`) USING BTREE,
INDEX `cno`(`cno`) USING BTREE,
CONSTRAINT `score_ibfk_1` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `score_ibfk_2` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'Transcript' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (103, '3-245', 86.0);
INSERT INTO `score` VALUES (105, '3-245', 75.0);
INSERT INTO `score` VALUES (109, '3-245', 68.0);
INSERT INTO `score` VALUES (103, '3-105', 92.0);
INSERT INTO `score` VALUES (105, '3-105', 88.0);
INSERT INTO `score` VALUES (109, '3-105', 76.0);
INSERT INTO `score` VALUES (101, '3-105', 64.0);
INSERT INTO `score` VALUES (107, '3-105', 91.0);
INSERT INTO `score` VALUES (108, '3-105', 78.0);
INSERT INTO `score` VALUES (101, '6-166', 85.0);
INSERT INTO `score` VALUES (107, '6-166', 79.0);
INSERT INTO `score` VALUES (108, '6-166', 81.0);
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sno` int(3) NOT NULL,
`sname` char(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`ssex` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`sbirthday` datetime(0) DEFAULT NULL,
`class` int(5) DEFAULT NULL,
PRIMARY KEY (`sno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'Student list' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (101, 'Li Jun', 'male', '1976-02-20 00:00:00', 95033);
INSERT INTO `student` VALUES (103, 'Lu Jun', 'male', '1974-06-03 00:00:00', 95031);
INSERT INTO `student` VALUES (105, 'Kuang Ming', 'male', '1975-10-02 00:00:00', 95031);
INSERT INTO `student` VALUES (107, 'Wang Li', 'female', '1976-01-23 00:00:00', 95033);
INSERT INTO `student` VALUES (108, 'Zeng Hua', 'male', '1977-09-01 00:00:00', 95033);
INSERT INTO `student` VALUES (109, 'Wang Fang', 'female', '1975-02-10 00:00:00', 95031);
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tno` int(3) NOT NULL,
`tname` char(4) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`tsex` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`tbirthday` datetime(0) DEFAULT NULL,
`prof` char(6) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`depart` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`tno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'Teacher table' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (804, 'Li Cheng', 'male', '1958-12-02 00:00:00', 'associate professor', 'Computer Department');
INSERT INTO `teacher` VALUES (825, 'Wang Ping', 'female', '1972-05-05 00:00:00', 'assistant', 'Computer Department');
INSERT INTO `teacher` VALUES (831, 'Bing Liu', 'female', '1977-08-14 00:00:00', 'assistant', 'Department of Electronic Engineering');
INSERT INTO `teacher` VALUES (856, 'Xu Zhang', 'male', '1969-03-12 00:00:00', 'lecturer', 'Department of Electronic Engineering');
SET FOREIGN_KEY_CHECKS = 1;
If you think the article is helpful, you are welcome to like the collection and pay attention. Three hits in a row, thank you!! ☺ ☻