数据库基础查询题

心已赠人 2021-09-22 20:00 334阅读 0赞

> 力扣数据库题：[https://leetcode-cn.com/problemset/database/][https_leetcode-cn.com_problemset_database]

基本表结构：  
student(sno,sname,sage,ssex)学生表  
course(cno,cname,tno) 课程表  
sc(sno,cno,score) 成绩表  
teacher(tno,tname) 教师表

**1，查询课程1的成绩比课程2的成绩高的所有学生的学号**

select a.sno from
    (select sno,score from sc where cno=1) a,
    (select sno,score from sc where cno=2) b
    where a.score>b.score and a.sno=b.sno

**2，查询平均成绩大于60分的同学的学号和平均成绩**

select a.sno as "学号", avg(a.score) as "平均成绩" 
    from
    (select sno,score from sc) a 
    group by sno having avg(a.score)>60

**3，查询所有同学的学号、姓名、选课数、总成绩**

select a.sno as 学号, b.sname as 姓名,
    count(a.cno) as 选课数, sum(a.score) as 总成绩
    from sc a, student b
    where a.sno = b.sno
    group by a.sno, b.sname

或者：

select student.sno as 学号, student.sname as 姓名,
     count(sc.cno) as 选课数, sum(score) as 总成绩
    from student left Outer join sc on student.sno = sc.sno
    group by student.sno, sname

**4，查询姓“张”的老师的个数**

select count(distinct(tname)) from teacher where tname like '张%‘

或者：

select tname as "姓名", count(distinct(tname)) as "人数" 
    from teacher 
    where tname like'张%'
    group by tname

**5，查询没学过“张三”老师课的同学的学号、姓名**

select student.sno,student.sname from student
    where sno not in (select distinct(sc.sno) from sc,course,teacher
    where sc.cno=course.cno and teacher.tno=course.tno and teacher.tname='张三')

**6，查询同时学过课程1和课程2的同学的学号、姓名**

select sno, sname from student
    where sno in (select sno from sc where sc.cno = 1)
    and sno in (select sno from sc where sc.cno = 2)

或者：

selectc.sno, c.sname from
    (select sno from sc where sc.cno = 1) a,
    (select sno from sc where sc.cno = 2) b,
    student c
    where a.sno = b.sno and a.sno = c.sno

或者：

select student.sno,student.sname from student,sc where
    student.sno=sc.sno and sc.cno=1
    and exists( select * from sc as sc_2 where sc_2.sno=sc.sno and sc_2.cno=2)

**7，查询学过“李四”老师所教所有课程的所有同学的学号、姓名**

select a.sno, a.sname from student a, sc b
    where a.sno = b.sno and b.cno in
    (select c.cno from course c, teacher d where c.tno = d.tno and d.tname = '李四')

或者：

select a.sno, a.sname from student a, sc b,
    (select c.cno from course c, teacher d where c.tno = d.tno and d.tname = '李四') e
    where a.sno = b.sno and b.cno = e.cno

**8，查询课程编号1的成绩比课程编号2的成绩高的所有同学的学号、姓名**

select a.sno, a.sname from student a,
    (select sno, score from sc where cno = 1) b,
    (select sno, score from sc where cno = 2) c
    where b.score > c.score and b.sno = c.sno and a.sno = b.sno

**9，查询所有课程成绩小于60分的同学的学号、姓名**

select sno,sname from student
    where sno not in (select distinct sno from sc where score > 60)

**10，查询至少有一门课程与学号为1的同学所学课程相同的同学的学号和姓名**

select distinct a.sno, a.sname
    from student a, sc b
    where a.sno <> 1 and a.sno=b.sno and
    b.cno in (select cno from sc where sno = 1)

或者：

select s.sno,s.sname 
    from student s,
    (select sc.sno 
    from sc
    where sc.cno in (select sc1.cno from sc sc1 where sc1.sno=1)and sc.sno<>1
    group by sc.sno)r1
    where r1.sno=s.sno

**11. 请列出所有超过或等于5名学生的课**

有一个courses 表 ，有: student (学生) 和 class (课程)。

请列出所有超过或等于5名学生的课。

例如,表:

±--------±-----------+  
| student | class |  
±--------±-----------+  
| A | Math |  
| B | English |  
| C | Math |  
| D | Biology |  
| E | Math |  
| F | Computer |  
| G | Math |  
| H | Math |  
| I | Math |  
±--------±-----------+

应该输出:

±--------+  
| class |  
±--------+  
| Math |  
±--------+

Note:  
学生在每个课中不应被重复计算。

SELECT class FROM courses GROUP BY class having count(DISTINCT student)>=5

**12. 获取 Employee 表中第 n 高的薪水（Salary）**

编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。

±—±-------+  
| Id | Salary |  
±—±-------+  
| 1 | 100 |  
| 2 | 200 |  
| 3 | 300 |  
±—±-------+

例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

±-----------------------+  
| getNthHighestSalary(2) |  
±-----------------------+  
| 200 |  
±-----------------------+

CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS
    BEGIN
            Declare M INT;
            Set M = N-1;
        RETURN (
            /* Write your T-SQL query statement below. */
           select DISTINCT Salary from Employee order by Salary DESC limit M, 1
        );
    END

**13. 编写一个 SQL 查询来实现分数排名**  
编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

±—±------+  
| Id | Score |  
±—±------+  
| 1 | 3.50 |  
| 2 | 3.65 |  
| 3 | 4.00 |  
| 4 | 3.85 |  
| 5 | 4.00 |  
| 6 | 3.65 |  
±—±------+

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

±------±-----+  
| Score | Rank |  
±------±-----+  
| 4.00 | 1 |  
| 4.00 | 1 |  
| 3.85 | 2 |  
| 3.65 | 3 |  
| 3.65 | 3 |  
| 3.50 | 4 |  
±------±-----+

SELECT Score, 
    (SELECT count(DISTINCT score) FROM Scores WHERE score >= s.score) AS Rank 
    FROM Scores s ORDER BY Score DESC ;

[https_leetcode-cn.com_problemset_database]: https://leetcode-cn.com/problemset/database/