CREATE TABLE `score` (
`sid` int(10) NOT NULL,
`username` varchar(10) NOT NULL,
`subject` varchar(10) NOT NULL,
`score` int(3) DEFAULT NULL,
PRIMARY KEY (`sid`,`subject`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
**
编写的sql考虑到score为空的情况
**
计算每个人的总成绩并排名(要求显示字段:姓名,总成绩)
SELECT a.username,a.totalScore FROM
(SELECT username,SUM(score) as totalScore FROM score GROUP BY sid) a
ORDER BY totalScore DESC;
计算每个人单科的最高成绩(要求显示字段: 学号,姓名,课程,最高成绩)
SELECT a.sid,a.score,a.username,a.subject FROM score a
JOIN (SELECT sid,MAX(score) as maxScore FROM score GROUP BY sid) b
ON a.sid = b.sid AND a.score = b.maxScore;
计算每个人的平均成绩(要求显示字段: 学号,姓名,平均成绩)并查出平均成绩大于80的同学 (提示:having相当于过滤)
SELECT a.sid,a.sumScore/b.num AS avgScore,a.username FROM
(SELECT sid,SUM(score) as sumScore,username FROM score GROUP BY sid,username) a
JOIN (SELECT COUNT(*) AS num,sid FROM score GROUP BY sid) b
ON a.sid = b.sid HAVING avgScore > 80;
SELECT sid,AVG(score),username FROM score GROUP BY sid,username; (当成绩为空时gg)
列出各门课程成绩最好的学生(要求显示字段: 学号,姓名,科目,成绩)
SELECT a.sid,a.username,a.subject,a.score FROM score a
JOIN (SELECT max(score) as maxScore,subject FROM score GROUP BY subject) b
ON a.score=b.maxScore AND a.subject=b.`subject`;
列出各门课程成绩最好的两位学生(要求显示字段: 学号,姓名,科目,成绩)
SELECT a.* FROM score a WHERE (SELECT COUNT(*) FROM score b
WHERE b.subject = a.subject AND b.score > a.score) < 2 AND a.score IS NOT NULL
ORDER BY a.score DESC;
查看所有科目均超过60分的同学
SELECT username,sid FROM
(SELECT username,sid FROM score where score > 60) a
GROUP BY sid HAVING COUNT(*) = 3;
统计如下 学号 姓名 语文 数学 英语 总分 平均分
SELECT * FROM (SELECT sid AS 学号,username AS 姓名,
SUM(CASE WHEN subject = '语文' THEN score ELSE 0 END) as 语文,
SUM(CASE WHEN subject = '数学' THEN score ELSE 0 END) as 数学,
SUM(CASE WHEN subject = '英语' THEN score ELSE 0 END) AS 英语,
SUM(score) AS 总分,SUM(score)/3 AS 平均分 FROM score
GROUP BY sid) a ORDER BY 总分 DESC;
列出各门课程的平均成绩(要求显示字段:课程,平均成绩)
SELECT sum(score)/(SELECT COUNT(*) FROM
(SELECT DISTINCT sid FROM score GROUP BY sid) a) as avgScore, subject FROM score
GROUP BY subject;
列出数学成绩的排名(要求显示字段:学号,姓名,成绩,排名)排名前2名
select sid,username,score,
(select count(*) from score a where subject ='数学' and a.score > b.score)+1 as orders from score b
where subject='数学' order by score desc limit 0,2;
如果存在重复的数学成绩 则需要去数学成绩进行去重比较(上述假设排名为 1 1 3 4 下述排名 1 1 2 3)
select sid,username,score,
(select count(distinct a.score) from score a where subject ='数学' and a.score > b.score)+1 as orders from score b
where subject='数学' order by score desc limit 0,2;
统计如下: 课程 不及格 良 优 个数
SELECT subject,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS 不及格,
SUM(CASE WHEN score BETWEEN 60 AND 80 THEN 1 ELSE 0 END) AS 良,
SUM(CASE WHEN score > 80 THEN 1 ELSE 0 END) AS 优 FROM score
GROUP BY subject;
某家SQL笔试题
CREATE TABLE branch
(
branch_name
varchar(20) NOT NULL,
branch_city
varchar(20) DEFAULT NULL,
PRIMARY KEY (branch_name
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE account
(
account_name
varchar(20) NOT NULL,
branch_name
varchar(20) DEFAULT NULL,
balance
double(20,2) DEFAULT NULL,
PRIMARY KEY (account_name
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE customer
(
id
int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增主键’,
customer_name
varchar(50) NOT NULL COMMENT ‘名称’,
customer_street
varchar(20) NOT NULL COMMENT ‘街道’,
customer_city
varchar(20) NOT NULL COMMENT ‘城市’,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
CREATE TABLE depasitor
(
account_name
varchar(20) NOT NULL,
customer_name
varchar(20) DEFAULT NULL,
PRIMARY KEY (account_name
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
上海所有支行存款最多的支行信息
SELECT b.branch_name,b.branch_city,MAX(balance) FROM
(SELECT branch_name,branch_city FROM branch WHERE branch_city = 'shanghai') as b
JOIN (SELECT branch_name,SUM(balance) as balance FROM account
GROUP BY branch_name) as a ON a.branch_name = b.branch_name;
用sql查询在上海所有支行都开过户的用户信息?(branch_city=shanghai)
SELECT * FROM customer WHERE customer_name in (
SELECT z.customer_name FROM
(SELECT b.branch_name,d.customer_name FROM
(SELECT branch_name FROM branch WHERE branch_city = 'shanghai') b
JOIN account a ON b.branch_name = a.branch_name
JOIN depasitor d ON d.account_name = a.account_name
GROUP BY b.branch_name,d.customer_name) z
GROUP BY customer_name HAVING COUNT(*) = (SELECT COUNT(*) FROM branch WHERE branch_city = 'shanghai'));
用sql查询在杨浦支行只开过一个账户的用户信息?(branch_name=yangpu)
SELECT * FROM customer WHERE customer_name in (
SELECT d.customer_name FROM
(SELECT branch_name FROM branch WHERE branch_name = 'yangpu') b
JOIN account a ON a.branch_name = b.branch_name
JOIN depasitor d ON d.account_name = a.account_name
GROUP BY customer_name HAVING COUNT(*) = 1);