sql应用

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;