【知识碎片】3：SELECT查询练习,硬件与并行

最新推荐文章于 2024-08-17 06:16:01 发布

大桔骑士v

最新推荐文章于 2024-08-17 06:16:01 发布

阅读量857

点赞数 2

分类专栏：知识碎片文章标签： select 硬件数据库并行

本文链接：https://blog.csdn.net/shu15121856/article/details/78940167

版权

知识碎片专栏收录该内容

4 篇文章 0 订阅

订阅专栏

数据库

SELECT查询练习

--检索有学生重修的教师编号和姓名
USE school;
SELECT DISTINCT T1.gh AS '教师工号',T1.xm AS '教师姓名'
FROM
    dbo.T AS T1
        RIGHT JOIN
    dbo.E AS E1
    ON T1.gh=E1.gh
WHERE EXISTS
    (
    SELECT E2.xh
    FROM dbo.E AS E2
    WHERE
        --学号一样
        E2.xh=E1.xh AND
        --课号也一样
        E2.kh=E1.kh AND
        --学期存在更小的
        (
            SUBSTRING(E2.xq,0,10)<SUBSTRING(E1.xq,0,10)
            OR
            (
                SUBSTRING(E2.xq,0,10)=SUBSTRING(E1.xq,0,10)
                AND
                SUBSTRING(E2.xq,10,15)>SUBSTRING(E1.xq,10,15)
            )
        )
    );

--检索有学生重修的教师编号和姓名
USE school;
SELECT DISTINCT E1.gh AS '教师工号',T1.xm AS '教师姓名'
FROM
    (
    dbo.T AS T1
        RIGHT JOIN
    dbo.E AS E1
    ON T1.gh=E1.gh
        INNER JOIN
    dbo.E AS E2
    ON
        E1.xh=E2.xh AND
        E1.kh=E2.kh AND
        (
            SUBSTRING(E2.xq,0,10)<SUBSTRING(E1.xq,0,10)
            OR
            (
                SUBSTRING(E2.xq,0,10)=SUBSTRING(E1.xq,0,10)
                AND
                SUBSTRING(E2.xq,10,15)>SUBSTRING(E1.xq,10,15)
            )
        )
    );

--查询选修了两门及以上课程的学生学号
USE school;
SELECT DISTINCT xh AS '学号'
FROM dbo.E
GROUP BY xh
HAVING COUNT(*)>1; --组过滤条件(组满足条件就输出,必须用聚合)

--查询选修了两门及以上课程的学生学号及选修门数,按门数降序,学号升序
USE school;
SELECT DISTINCT xh AS '学号',COUNT(*) AS '门数'
FROM dbo.E
GROUP BY xh
HAVING COUNT(*)>1
ORDER BY
    2 DESC,
    xh ASC;

--查询有三门以上课程90分以上的学生的学号以及90分以上的课程数
USE school;
SELECT xh AS '学号',COUNT(*) AS '90分以上课程数'
FROM dbo.E
WHERE zpcj>=90
GROUP BY xh
HAVING COUNT(*)>=3;

--输出有成绩在90分以上的学生的学号和选课门数
USE school;
SELECT xh AS '学号',COUNT(*) AS '选课门数'
FROM dbo.E
GROUP BY xh
HAVING MAX(zpcj)>=90; --分组以后,组内最高分>=90分

--查询所有成绩在80分以上的学生学号
USE school;
SELECT xh AS '学号'
FROM dbo.E
GROUP BY xh
HAVING MIN(zpcj)>=80; --分组以后,组内最低分>=80分

--查询有成绩在90分以上的学生学号
--①
USE school;
SELECT DISTINCT xh AS '学号'
FROM E
WHERE zpcj>=90;

--②
USE school;
SELECT xh AS '学号'
FROM E
WHERE zpcj>=90
GROUP BY xh;

--③
USE school;
SELECT xh AS '学号'
FROM dbo.E
GROUP BY xh
HAVING MAX(zpcj)>=90;

--MOV(mid,...)电影表
--CST(cid,...)演员表
--MC(mid,cid,...)电影-演员的关联表
--求参演演员超过10人,且这些演员中至少有两人还合作过其它电影的影片
USE movie;
SELECT *
FROM MOV
WHERE mid IN
    (
    SELECT mid
    FROM MC AS MC0
    GROUP BY mid
    HAVING COUNT(*)>10
    )
    AND
    EXISTS
    (
        SELECT *
        FROM MC AS MC1, MC AS MC2
        WHERE 
            MC1.cid!=MC2.cid
                AND
            MC1.mid=MC2.mid
                AND
            MC1.mid!=MOV.mid
                AND
            MC1.cid IN
            (
                SELECT MC3.cid
                FROM MC AS MC3
                WHERE MC3.mid=MOV.mid
            )
                AND
            MC2.cid IN
            (
                SELECT MC3.cid
                FROM MC AS MC3
                WHERE MC3.mid=MOV.mid
            )
    );

--查询每个学生选课情况(包括没有选修课程的学生)
USE school;
SELECT S.xh AS '学号',S.xm AS '姓名',E.kh AS '课号',C.km AS '课名',E.xq AS '学期'
FROM
    dbo.S
        LEFT JOIN
    dbo.E
    ON S.xh=E.xh
        LEFT JOIN
    dbo.C
    ON E.kh=C.kh;

--检索选了2次课的学生的学号与姓名
USE school;
SELECT S.xh AS '学号',S.xm AS '姓名',COUNT(*)
FROM
    dbo.S
        LEFT JOIN
    dbo.E
        ON S.xh=E.xh
GROUP BY S.xh,S.xm
HAVING
    COUNT(*)=2;

--检索所有课程都选修的的学生的学号与姓名
--即查询SxE表中这样的学生:不存在[C表里的这样的课:本学生未选]
--即查询SxE表中这样的学生:不存在[C表中的这样的课:不存在[E表里的这个课,学号与本学生相同]]
USE school;
SELECT DISTINCT S0.xh AS '学号',S0.xm AS '姓名'
FROM
    dbo.S AS S0
WHERE
    NOT EXISTS
    (
    SELECT *
    FROM dbo.C AS C0
    WHERE
        NOT EXISTS
        (
        SELECT *
        FROM dbo.E AS E0
        WHERE
            E0.kh=C0.kh AND
            E0.xh=S0.xh
        )
    );

--检索选修课程包含1106同学所学全部课程的学生学号和姓名
--即选择这样的学生:[1106号学生的所有课号集-选修的所有课号集]为空集
USE school;
SELECT S0.xh AS '学号',S0.xm AS '姓名'
FROM dbo.S AS S0
WHERE
    NOT EXISTS
    (
        (
        SELECT DISTINCT E1.kh
        FROM dbo.E AS E1
        WHERE E1.xh=1106
        )
        EXCEPT
        (
        SELECT DISTINCT E0.kh
        FROM dbo.E AS E0
        WHERE E0.xh=S0.xh
        ) 
    );


--查询每门课程中分数最高的学生学号和学生姓名,总评成绩
USE school;
SELECT MaxGradeTab.课程号,SETab.学号,SETab.姓名,SETab.总评成绩
FROM
    (
    SELECT S0.xh AS '学号',S0.xm AS '姓名',E0.zpcj AS '总评成绩'
    FROM
        dbo.S AS S0
            INNER JOIN
        dbo.E AS E0
        ON S0.xh=E0.xh
    ) AS SETab
    INNER JOIN
    (
    SELECT kh AS '课程号',MAX(zpcj) AS '最大总评'
    FROM dbo.E
    GROUP BY E.kh
    ) AS MaxGradeTab
    ON SETab.总评成绩=MaxGradeTab.最大总评
ORDER BY
    MaxGradeTab.课程号;

--查询年龄小于本学院平均年龄
--所有课程总评成绩都高于所选课程平均总评成绩
--(不存在[该学生这样的课程:不高于平均总评成绩])
--的学生学号、姓名和平均总评成绩
--按年龄排序
USE school;
SELECT
    E0.xh AS '学号',
    S0.xm AS '姓名',
    (
    SELECT AVG(E3.zpcj)
    FROM dbo.E AS E3
    WHERE E3.xh=S0.xh
    ) AS '平均总评成绩'
FROM
    dbo.E AS E0
        LEFT JOIN
    dbo.S AS S0
    ON E0.xh=S0.xh
WHERE
    DATEDIFF(DAY,S0.csrq,'2018-1-1')<   
                (
                SELECT AVG(DATEDIFF(DAY,S1.csrq,'2018-1-1'))
                FROM dbo.S AS S1
                WHERE S1.yxh=S0.yxh
                )
    AND
    NOT EXISTS
    (
        (
        SELECT *
        FROM dbo.E AS E1
        WHERE
            E1.xh=S0.xh
            AND
            E1.zpcj<=
                (
                SELECT AVG(zpcj)
                FROM dbo.E AS E2
                GROUP BY E2.kh
                HAVING E2.kh=E1.kh
                )
        )
    )
ORDER BY S0.csrq;