學生桌
學生(s_id,s_name,s_birth,s_sex)
學生證、學生姓名、出生日期、學生性別
課表
課程(c_id, c_name, t_id)
課程 ID、課程名稱、教師 ID
老師桌
老師(t_id, t_name)
教師 ID、教師姓名
分數表
分數(s_id, c_id, s_score)
學生ID、課程ID、分數
CREATE TABLE `Student`( `s_id` VARCHAR(20), `s_name` VARCHAR(20) NOT NULL DEFAULT '', `s_birth` VARCHAR(20) NOT NULL DEFAULT '', `s_sex` VARCHAR(10) NOT NULL DEFAULT '', PRIMARY KEY(`s_id`) );
CREATE TABLE `Course`( `c_id` VARCHAR(20), `c_name` VARCHAR(20) NOT NULL DEFAULT '', `t_id` VARCHAR(20) NOT NULL, PRIMARY KEY(`c_id`) );
CREATE TABLE `Teacher`( `t_id` VARCHAR(20), `t_name` VARCHAR(20) NOT NULL DEFAULT '', PRIMARY KEY(`t_id`) );
CREATE TABLE `Score`( `s_id` VARCHAR(20), `c_id` VARCHAR(20), `s_score` INT(3), PRIMARY KEY(`s_id`,`c_id`) );
INSERT INTO Student VALUES('01', 'John Doe', '1990-01-01', 'Male'); INSERT INTO Student VALUES('02', 'Jane Smith', '1990-12-21', 'Male'); INSERT INTO Student VALUES('03', 'Michael Brown', '1990-05-20', 'Male'); INSERT INTO Student VALUES('04', 'Emily Davis', '1990-08-06', 'Male'); INSERT INTO Student VALUES('05', 'Lucy Johnson', '1991-12-01', 'Female'); INSERT INTO Student VALUES('06', 'Sophia Williams', '1992-03-01', 'Female'); INSERT INTO Student VALUES('07', 'Olivia Taylor', '1989-07-01', 'Female'); INSERT INTO Student VALUES('08', 'Victoria King', '1990-01-20', 'Female');
INSERT INTO Course VALUES('01', 'Literature', '02'); INSERT INTO Course VALUES('02', 'Mathematics', '01'); INSERT INTO Course VALUES('03', 'English', '03');
INSERT INTO Teacher VALUES('01', 'Andrew'); INSERT INTO Teacher VALUES('02', 'Bethany'); INSERT INTO Teacher VALUES('03', 'Charlie');
insert into Score values('01' , '01' , 80); insert into Score values('01' , '02' , 90); insert into Score values('01' , '03' , 99); insert into Score values('02' , '01' , 70); insert into Score values('02' , '02' , 60); insert into Score values('02' , '03' , 80); insert into Score values('03' , '01' , 80); insert into Score values('03' , '02' , 80); insert into Score values('03' , '03' , 80); insert into Score values('04' , '01' , 50); insert into Score values('04' , '02' , 30); insert into Score values('04' , '03' , 20); insert into Score values('05' , '01' , 76); insert into Score values('05' , '02' , 87); insert into Score values('06' , '01' , 31); insert into Score values('06' , '03' , 34); insert into Score values('07' , '02' , 89); insert into Score values('07' , '03' , 98);
SELECT a.*, b.s_score AS '01_score', c.s_score AS '02_score' FROM student a JOIN score b ON a.s_id = b.s_id AND b.c_id = '01' LEFT JOIN score c ON a.s_id = c.s_id AND c.c_id = '02' WHERE b.s_score > COALESCE(c.s_score, 0); -- Using COALESCE instead of OR c.c_id = NULL -- Alternatively SELECT a.*, b.s_score AS '01_score', c.s_score AS '02_score' FROM student a, score b, score c WHERE a.s_id = b.s_id AND a.s_id = c.s_id AND b.c_id = '01' AND c.c_id = '02' AND b.s_score > c.s_score;
SELECT a.*, b.s_score AS '01_score', c.s_score AS '02_score' FROM student a LEFT JOIN score b ON a.s_id = b.s_id AND b.c_id = '01' JOIN score c ON a.s_id = c.s_id AND c.c_id = '02' WHERE COALESCE(b.s_score, 0) < c.s_score; -- Using COALESCE for clarity
SELECT b.s_id, b.s_name, ROUND(AVG(a.s_score), 2) AS avg_score FROM student b JOIN score a ON b.s_id = a.s_id GROUP BY b.s_id, b.s_name HAVING AVG(a.s_score) >= 60;
SELECT b.s_id, b.s_name, ROUND(AVG(a.s_score), 2) AS avg_score FROM student b LEFT JOIN score a ON b.s_id = a.s_id GROUP BY b.s_id, b.s_name HAVING AVG(a.s_score) < 60 UNION SELECT a.s_id, a.s_name, 0 AS avg_score FROM student a WHERE a.s_id NOT IN (SELECT DISTINCT s_id FROM score);
SELECT a.s_id, a.s_name, COUNT(b.c_id) AS sum_course, SUM(b.s_score) AS sum_score FROM student a LEFT JOIN score b ON a.s_id = b.s_id GROUP BY a.s_id, a.s_name;
SELECT COUNT(t_id) FROM teacher WHERE t_name LIKE 'Smith%';
SELECT a.* FROM student a JOIN score b ON a.s_id = b.s_id WHERE b.c_id IN ( SELECT c_id FROM course WHERE t_id = ( SELECT t_id FROM teacher WHERE t_name = 'John Doe' ) );
SELECT * FROM student c WHERE c.s_id NOT IN ( SELECT a.s_id FROM student a JOIN score b ON a.s_id = b.s_id WHERE b.c_id IN ( SELECT a.c_id FROM course a JOIN teacher b ON a.t_id = b.t_id WHERE t_name = 'John Doe' ) );
SELECT a.* FROM student a, score b, score c WHERE a.s_id = b.s_id AND a.s_id = c.s_id AND b.c_id = 'Math101' AND c.c_id = 'Science101';
SELECT a.* FROM student a WHERE a.s_id IN (SELECT s_id FROM score WHERE c_id = 'Math101') AND a.s_id NOT IN (SELECT s_id FROM score WHERE c_id = 'Science101');
-- @wendiepei's approach SELECT s.* FROM student s LEFT JOIN Score s1 ON s1.s_id = s.s_id GROUP BY s.s_id HAVING COUNT(s1.c_id) < (SELECT COUNT(*) FROM course); -- @k1051785839's approach SELECT * FROM student WHERE s_id NOT IN ( SELECT s_id FROM score t1 GROUP BY s_id HAVING COUNT(*) = (SELECT COUNT(DISTINCT c_id) FROM course) );
SELECT * FROM student WHERE s_id IN ( SELECT DISTINCT a.s_id FROM score a WHERE a.c_id IN ( SELECT c_id FROM score WHERE s_id = '01' ) );
SELECT t3.* FROM ( SELECT s_id, group_concat(c_id ORDER BY c_id) group1 FROM score WHERE s_id <> '01' GROUP BY s_id ) t1 INNER JOIN ( SELECT group_concat(c_id ORDER BY c_id) group2 FROM score WHERE s_id = '01' GROUP BY s_id ) t2 ON t1.group1 = t2.group2 INNER JOIN student t3 ON t1.s_id = t3.s_id
select a.s_name from student a where a.s_id not in ( select s_id from score where c_id = (select c_id from course where t_id =( select t_id from teacher where t_name = 'Tom')));
SELECT a.s_id, a.s_name, ROUND(AVG(b.s_score), 2) AS average_score FROM student a LEFT JOIN score b ON a.s_id = b.s_id WHERE a.s_id IN ( SELECT s_id FROM score WHERE s_score < 60 GROUP BY s_id HAVING COUNT(*) >= 2 ) GROUP BY a.s_id, a.s_name;
SELECT a.*, b.c_id, b.s_score FROM student a JOIN score b ON a.s_id = b.s_id WHERE b.c_id = '01' AND b.s_score < 60 ORDER BY b.s_score DESC;
SELECT a.s_id, MAX(CASE WHEN c_id = '01' THEN s_score END) AS Chinese, MAX(CASE WHEN c_id = '02' THEN s_score END) AS Math, MAX(CASE WHEN c_id = '03' THEN s_score END) AS English, ROUND(AVG(s_score), 2) AS average_score FROM score a GROUP BY a.s_id ORDER BY average_score DESC;
SELECT a.c_id, b.c_name, MAX(s_score) AS HighestScore, MIN(s_score) AS LowestScore, ROUND(AVG(s_score), 2) AS AverageScore, ROUND(100 * (SUM(CASE WHEN s_score >= 60 THEN 1 ELSE 0 END) / COUNT(s_score)), 2) AS PassRate, ROUND(100 * (SUM(CASE WHEN s_score BETWEEN 70 AND 80 THEN 1 ELSE 0 END) / COUNT(s_score)), 2) AS MediumRate, ROUND(100 * (SUM(CASE WHEN s_score BETWEEN 80 AND 90 THEN 1 ELSE 0 END) / COUNT(s_score)), 2) AS GoodRate, ROUND(100 * (SUM(CASE WHEN s_score >= 90 THEN 1 ELSE 0 END) / COUNT(s_score)), 2) AS ExcellentRate FROM score a LEFT JOIN course b ON a.c_id = b.c_id GROUP BY a.c_id, b.c_name;
SELECT a.s_id, a.c_id, @rank := IF(@prev_score = a.s_score, @rank, @rank + 1) AS rank_without_ties, @prev_score := a.s_score AS score FROM (SELECT s_id, c_id, s_score FROM score ORDER BY c_id, s_score DESC) a, (SELECT @rank := 0, @prev_score := NULL) r ORDER BY a.c_id, a.rank_without_ties;
SELECT a.s_id, @rank := IF(@prev_score = a.sum_score, @rank, @rank + 1) AS rank, @prev_score := a.sum_score AS total_score FROM (SELECT s_id, SUM(s_score) AS sum_score FROM score GROUP BY s_id ORDER BY sum_score DESC) a, (SELECT @rank := 0, @prev_score := NULL) r ORDER BY total_score DESC;
SELECT a.t_id, c.t_name, a.c_id, ROUND(AVG(s_score), 2) AS avg_score FROM course a LEFT JOIN score b ON a.c_id = b.c_id LEFT JOIN teacher c ON a.t_id = c.t_id GROUP BY a.c_id, a.t_id, c.t_name ORDER BY avg_score DESC;
(SELECT d.*, c.ranking, c.s_score, c.c_id FROM (SELECT s_id, s_score, c_id, @rank := IF(@prev_cid = c_id, @rank + 1, 1) AS ranking, @prev_cid := c_id FROM score, (SELECT @rank := 0, @prev_cid := NULL) AS var_init WHERE c_id = '01' ORDER BY c_id, s_score DESC ) c LEFT JOIN student d ON c.s_id = d.s_id WHERE c.ranking BETWEEN 2 AND 3 ) UNION (SELECT d.*, c.ranking, c.s_score, c.c_id FROM (SELECT similar structure as above but with c_id = '02' in the WHERE clause) c LEFT JOIN student d ON c.s_id = d.s_id WHERE c.ranking BETWEEN 2 AND 3 ) UNION (SELECT similar structure as above but with c_id = '03' in the WHERE clause);
select distinct f.c_name, a.c_id, b.`85-100`, b.Percentage as `[85-100] Percentage`, c.`70-85`, c.Percentage as `[70-85] Percentage`, d.`60-70`, d.Percentage as `[60-70] Percentage`, e.`0-60`, e.Percentage as `[0-60] Percentage` from score a left join ( select c_id, SUM(case when s_score > 85 and s_score <= 100 then 1 else 0 end) as `85-100`, ROUND(100*(SUM(case when s_score > 85 and s_score <= 100 then 1 else 0 end)/count(*)),2) as Percentage from score GROUP BY c_id ) b on a.c_id = b.c_id left join ( select c_id, SUM(case when s_score > 70 and s_score <= 85 then 1 else 0 end) as `70-85`, ROUND(100*(SUM(case when s_score > 70 and s_score <= 85 then 1 else 0 end)/count(*)),2) as Percentage from score GROUP BY c_id ) c on a.c_id = c.c_id left join ( select c_id, SUM(case when s_score > 60 and s_score <= 70 then 1 else 0 end) as `60-70`, ROUND(100*(SUM(case when s_score > 60 and s_score <= 70 then 1 else 0 end)/count(*)),2) as Percentage from score GROUP BY c_id ) d on a.c_id = d.c_id left join ( select c_id, SUM(case when s_score >= 0 and s_score <= 60 then 1 else 0 end) as `0-60`, ROUND(100*(SUM(case when s_score >= 0 and s_score <= 60 then 1 else 0 end)/count(*)),2) as Percentage from score GROUP BY c_id ) e on a.c_id = e.c_id left join course f on a.c_id = f.c_id;
select a.s_id, @i:=@i+1 as 'No Gaps in Ranking', @k:=(case when @avg_score=a.avg_s then @k else @i end) as 'With Gaps in Ranking', @avg_score:=avg_s as 'Average Score' from (select s_id, ROUND(AVG(s_score),2) as avg_s from score GROUP BY s_id ORDER BY avg_s DESC) a, (select @avg_score:=0, @i:=0, @k:=0) b;
select a.s_id, a.c_id, a.s_score from score a left join score b on a.c_id = b.c_id and a.s_score < b.s_score group by a.s_id, a.c_id, a.s_score having count(b.s_id) < 3 order by a.c_id, a.s_score desc;
select c_id, count(s_id) from score group by c_id;
select s_id, s_name from student where s_id in (select s_id from score group by s_id having count(c_id) = 2);
select s_sex, count(s_sex) as Count from student group by s_sex;
select * from student where s_name like '%Tom%';
select a.s_name, a.s_sex, count(*) as Count from student a join student b on a.s_id != b.s_id and a.s_name = b.s_name and a.s_sex = b.s_sex group by a.s_name, a.s_sex;
select s_name from student where s_birth like '1990%';
select c_id, round(avg(s_score), 2) as avg_score from score group by c_id order by avg_score desc, c_id asc;
select a.s_id, b.s_name, round(avg(a.s_score), 2) as avg_score from score a left join student b on a.s_id = b.s_id group by s_id having avg_score >= 85;
select a.s_name, b.s_score from student a join score b on a.s_id = b.s_id where b.c_id = (select c_id from course where c_name = 'mathematics') and b.s_score < 60;
select a.s_id, a.s_name, sum(case c.c_name when 'history' then b.s_score else 0 end) as 'history', sum(case c.c_name when 'mathematics' then b.s_score else 0 end) as 'mathematics', sum(case c.c_name when 'Politics' then b.s_score else 0 end) as 'Politics', sum(b.s_score) as 'Total score' from student a left join score b on a.s_id = b.s_id left join course c on b.c_id = c.c_id group by a.s_id, a.s_name;
select a.s_name, b.c_name, c.s_score from student a left join score c on a.s_id = c.s_id left join course b on c.c_id = b.c_id where c.s_score >= 70;
select a.s_id, a.c_id, b.c_name, a.s_score from score a left join course b on a.c_id = b.c_id where a.s_score < 60;
select a.s_id, b.s_name from score a left join student b on a.s_id = b.s_id where a.c_id = '01' and a.s_score > 80;
select count(*) from score group by c_id;
select c_id from course c, teacher d where c.t_id = d.t_id and d.t_name = 'Tom';
-- Get maximum score (could have ties)
select max(s_score) from score where c_id = '02';
-- Get information
select a.*, b.s_score, b.c_id, c.c_name from student a left join score b on a.s_id = b.s_id left join course c on b.c_id = c.c_id where b.c_id = (select c_id from course c, teacher d where c.t_id = d.t_id and d.t_name = 'Tom') and b.s_score in (select max(s_score) from score where c_id = '02');
select distinct b.s_id, b.c_id, b.s_score from score a, score b where a.c_id != b.c_id and a.s_score = b.s_score;
select a.s_id, a.c_id, a.s_score from score a where (select count(1) from score b where b.c_id = a.c_id and b.s_score >= a.s_score) <= 2 order by a.c_id;
select c_id, count(*) as total from score group by c_id having total > 5 order by total, c_id asc;
select s_id, count(*) as sel from score group by s_id having sel >= 2;
select * from student where s_id in (select s_id from score group by s_id having count(*) = (select count(*) from course));
select s_birth, (date_format(now(), '%Y') - date_format(s_birth, '%Y') - (case when date_format(now(), '%m%d') > date_format(s_birth, '%m%d') then 0 else 1 end)) as age from student;
select * from student where week(date_format(now(), '%Y%m%d')) = week(s_birth);
select * from student where week(date_format(now(), '%Y%m%d')) + 1 = week(s_birth);
select * from student where month(date_format(now(), '%Y%m%d')) = month(s_birth);
select * from student where month(date_format(now(), '%Y%m%d')) + 1 = month(s_birth);
OK,If you find this article helpful, feel free to share it with more people.
If you want to find a SQL tool to practice, you can try our sqlynx, which has a simple interface and is easy to use. https://www.sqlynx.com/download/ Free download
以上是MySQL 基本精選練習題及答案的詳細內容。更多資訊請關注PHP中文網其他相關文章!