There were four tables, class table class (every, the className) students table student (stuId, every, stuName), score table score (courseid, stuId, score), the curriculum course (courseid, courseName),
(1) distributed query the lowest scores in all the subjects students student id, name, class name, course title and score
(2) query each class average in all the subjects, shows the average, the class name, course name
(3) query every is' 1 ', the className as' class 1 'of all students in the class all course grades, if a student lack of examination, scores, there is no record in the table, the students also to query information and course information, eventually show students student id, name, course name and scores,
CodePudding user response:
For bosses to solve thank you, general train of thought a bit but what is almost always
CodePudding user response:
Suggest the original poster to write out the thinking of every problem, and then describe the problem, it can help you more, these questions are common the application of the aggregation function, simple, hope that the original poster can oneself think much,CodePudding user response:
- question 1
The select B.s tuid, B.s tuname, Arthur c. lassname,, dc oursename, A.s core
The from score A
Join student B on A.s tuid=B.s tuid
Join a class C on A.c lassid=Arthur c. lassid
The join course D on A.c ourseid=, dc ourse
Where not the exists (select 1 from the from score
Join student on score. Stuid=student. Stuid
Where biggest lassid=student. Every and score. Courseid=A.c ourseid and score. Score
- question 2
The select Max (Arthur c. lassname) as the classname, Max (, dc oursename) as coursename, avg (A.s core) as avg_score
The from score A
Join student B on A.s tuid=B.s tuid
Join a class C on A.c lassid=Arthur c. lassid
The join course D on A.c ourseid=, dc ourse
Group by A.c ourse, biggest lassid
- question 3
The select stuid, stuname, coursename, score
The from student A
Left the join score on B A.s tuid=B.s tuid
Left the join course C on biggest ourseid=Arthur c. ourseid
Left the join class D on A.c lassid=, dc lassid
Where every='1' and the classname='1'
CodePudding user response:
Question 1:The select st. stuId, st. stuName, Arthur c. lassName, co., courseName, min (s.s core) from score s
Inner join student st on st. stuId=s.s tuid
Inner join class c on Arthur c. lassId=st. every
Inner join course co on Co. courseid=s.c ourseid
Group by s.c ourseid, Arthur c. lassId
The order by the score, Arthur c. lassName
Question 2:
The select Arthur c. lassName, co., courseName, AVG (s.s core) from score s
Inner join student st on st. stuId=s.s tuid
Inner join class c on Arthur c. lassId=st. every
Inner join course co on Co. courseid=s.c ourseid
Group by s.c ourseid, Arthur c. lassId
The order by the score, Arthur c. lassName
Question 3:
The select st. stuId, st. stuName, co., courseName, s.s core from student st
Inner join class c on Arthur c. lassId=st. every
Left the join score s on s.s tuid=st. stuId
Left the join course co on co courseid=s.c ourseid
Where Arthur c. lassId=1
CodePudding user response:
The CREATE TABLE ` class ` (` every ` int (11) NOT NULL,
` className ` varchar (255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
PRIMARY KEY (` every `) USING BTREE
) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=Dynamic;
INSERT INTO ` class ` VALUES (1, 'class 1'), (2, 'class 2), (3,' class 3), (4, 'class 4');
The CREATE TABLE ` course ` (
` courseid ` int (11), NOT NULL AUTO_INCREMENT,
` courseName ` varchar (32) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
PRIMARY KEY (` courseid `) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 CHARACTER SET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=Dynamic;
INSERT INTO ` course ` VALUES (1, 'yuwen), (2,' shuxue ');
The CREATE TABLE ` score ` (
` courseid ` int (11) NULL DEFAULT NULL,
` stuid ` int (11) NULL DEFAULT NULL,
` score ` int (11) NULL DEFAULT NULL
) ENGINE=InnoDB CHARACTER SET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=Dynamic;
INSERT INTO ` score ` VALUES (1, 1, 45), (1, 2, 12), (1, 3, 34), (1, 4, 44), (1, 5, 54), (1, 6, 64), (1, 7, 74), (1, 8, 84), (1, 9, 94), (1, 10, 104), (1, 11, 114), (1, 12, 124), (1, 13, 134), (1, 14, 144), (1, 15, 154), (1, 16, 164), (2, 1, 15), (2, 2, 25), (2, 3, 35), (2, 4, 45), (2, 5, 55), (2, 6, 65), (2, 7, 75), (2, 8, 85), (2, 9, 95), (2, 10, 81), (2, 11, 11), (2, 12, 21), (2, 13, 31), (2, 14, 41), (2, 15, 51), (2, 16, 61);
The CREATE TABLE ` student ` (
` stuId ` int (11), NOT NULL AUTO_INCREMENT,
` every ` int (11) NULL DEFAULT NULL,
` stuName ` varchar (32) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
PRIMARY KEY (` stuId `) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=17 CHARACTER SET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=Dynamic;
INSERT INTO ` student ` VALUES (1, 1, '1 xiaoming), (2, 1,' 1 xiaohong '), (3, 1, '1 kunjia'), (4, 1, '1 shewang), (5, 2, 2' poo '), (6, 2, '2 lk), (7, 2,' 2 quanse), (8, 2, '2 yuan), (9, 3, 3' poo '), (10, 3 '3 lk), (11, 3, 3' quanse '), (12, 3, '3 yuan), (13, 4, 4' poo '), (14, 4, 4 'lk'), (15, 4, 4 'quanse'), (16, 4, 4 'yuan');