1.查询每门课都大于80分的学生姓名
方式一:
SELECT name
FROM test
GROUP BY name
HAVING MIN(score) > 80
方式二:
SELECT DISTINCT name
FROM test
WHERE name NOT IN(SELECT DISTINCT name
FROM test
WHERE score <= 80);
2.删除自动编号不同,其他都相同的学生冗余信息
DELETE test WHERE id NOT IN(SELECT MIN(id)
FROM test
GROUP BY student_id,name,course_id,score);
3.一个叫team的表,里面只有一个字段name,一共有4条记录,分别是a,b,c,d,对应四个球队,现在四个球队进行比赛,用一条sql语句显示所有可能的比赛组合。
SELECT a.name,b.name
FROM team a,team b
WHERE a.name < b.name;
4.请用sql语句实现:从testDB数据表汇总查询出所有月份的发生额都比101科目相应月份的发生高的科目。请注意:TestDB中有很多科目,都有1-12月份的发生额。ACCID:科目代码,Occmonth:发生额月份,DebitOccur:发生额
SELECT a.*
FROM testDB a,
(SELECT occmonth,max(debitoccur)
FROM testDB
WHERE accid = '101'
GROUP BY occmonth) b
WHERE a.occmomth = b.occmonth
AND a.debitoccur > b.debit101occur;
5.根据表给出如下的查询结果。
SELECT year,
(SELECT amount FROM test t1 WHERE month=1 AND t1.year = t2.year) as m1,
(SELECT amount FROM test t1 WHERE month=2 AND t1.year = t2.year) as m2,
(SELECT amount FROM test t1 WHERE month=3 AND t1.year = t2.year) as m3,
(SELECT amount FROM test t1 WHERE month=4 AND t1.year = t2.year) as m4
FROM test
GROUP BY year;
6.日程安排前5分钟提醒
SELECT *
FROM test
WHERE DATEDIFF('minute',startTime,getdate()) > 5;
7.说明:有两张关联表,删除主表中已经在副表中没有的信息
DELETE FROM test1
WHERE NOT EXISTS(SELECT *
FORM test2
WHERE test1.id = test2.id);
8.有两个表A和B,均有key和value两个字段,如果B的key在A中也有,就把B的value换为A中对应的value
UPDATE b
SET b.value = (SELECT a.value
FROM a
WHERE a.key = b.key)
WHERE b.id IN(SELECT b.id
FROM b,a
WHERE b,key = a.key);
9.根据表按如下方式显示
方式一:
SELECT courseid,
coursename,
score,
decode(sign(socre-60),-1,'fail','pass') AS mark
FROM test;
方式二:
//有错
SELECT courseid,coursename,score,(CASE score
WHEN score >= 60
THEN 'pass'
ELSE 'fail') AS mark
FROM test;