sdnu-dboj-Student-Movie

题目入口：http://db.itoi.sd.cn/problem/set

成绩管理相关题目

Student表

Course表

SC表

初始化SQL语句

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
/*学生表*/
CREATE TABLE Student
(
    Sno  INT(9) PRIMARY KEY,
    Sname VARCHAR(10),
    Ssex  CHAR(2),
    Sage  TINYINT(3),
    Sdept VARCHAR(20)
);
INSERT INTO Student
VALUES (201215121, '李勇', '男', 20, 'CS'),
     (201215122, '刘晨', '女', 19, 'CS'),
    (201215123, '王敏', '女', 18, 'MA'),
    (201215125, '张立', '男', 19, 'IS'),
    (201215133, '张三', '男', 21, 'TE'),
     (201215137, '赵四', '男', 23, 'TE'),
     (201215139, '田二', '女', 24, 'CS'),
     (201215140, '李四', '男', 21, 'CS'),
     (201215141, '郑五', '女', 22, 'IS');
/*课程表*/
CREATE TABLE Course
(
    Cno      INT(4) PRIMARY KEY,
    Cname     VARCHAR(40),
    Cpno      INT(4),
    Ccredit     TINYINT(3),
    FOREIGN     KEY (Cpno) REFERENCES Course (Cno)
);
INSERT INTO Course
VALUES    (1, '数据库', 5, 4),
        (2, '数学', NULL, 2),
    (3, '信息系统', 1, 4),
     (4, '操作系统', 6, 3),
     (5, '数据结构', 7, 4),
     (6, '数据处理', NULL, 2),
     (7, 'PASCAL语言', 6, 4);
/*选课表*/
CREATE TABLE SC
(
    Sno  INT(9),
    Cno  INT(4),
    Grade SMALLINT(3),
    PRIMARY KEY (Sno, Cno),
/* 主码由两个属性构成，必须作为表级完整性进行定义*/
    FOREIGN KEY (Sno) REFERENCES Student (Sno),
/* 表级完整性约束条件，Sno是外码，被参照表是Student */
    FOREIGN KEY (Cno) REFERENCES Course (Cno)
/* 表级完整性约束条件， Cno是外码，被参照表是Course*/
);
INSERT INTO SC
VALUES (201215121, 1, 92),
            (201215121, 2, 85),
            (201215121, 3, 88),
            (201215122, 2, 90),
      (201215122, 3, 80),
            (201215122, 6, 59),
         (201215123, 1, 84),
         (201215125, 1, 60),
         (201215125, 3, 90),
         (201215133, 4, 87),
         (201215137, 2, 79),
         (201215139, 2, 80),
         (201215140, 2, 81);
SET FOREIGN_KEY_CHECKS = 1;

问题代码

0
select Sno, Sname
        from Student
1
select Sname, Sno, Sdept
        from Student
2
select Sname, Sno, Sdept
        from Student
3
select distinct Sno
        from SC
4
select distinct Sname
        from Student
        where Sdept='CS'
5
select Sname, Sage
    from Student
    where Sage<20
6
select Sno
    from SC
    where Grade<60
7
select Sname, Sdept, Sage
    from Student
    where Sage>=20 and Sage<=23
8
select Sname, Sdept, Sage
    from Student
    where Sage<20 or Sage>23
9
select Sname, Ssex
    from Student
    where Sdept='CS' or Sdept='MA' or Sdept='IS'
10
select Sname, Ssex
    from Student
    where Sdept not in ('CS', 'MA', 'IS')
11
select Sname, Sno, Ssex
    from Student
    where Sname like '刘_'
12
select Sname, Sno
    from Student
    where Sname like '_立'
13
select Sname, Sno, Ssex
    from Student
    where Sname not like '刘_'
14
select Sno, Cno
    from SC
    where Grade is not null
15
select Sname
    from Student
    where Sdept='CS' and Sage<20
16
select Sno, Grade
    from SC
    where Cno='3'
    order by Grade desc
17
select *
    from Student
    order by Sdept asc, Sage desc
18
select count(Sno) as 'COUNT'
    from Student
19
select count(Sno) as COUNT
        from (
        select Sno
                from SC
                group by sno) as test
20
select avg(Grade) as AVG
    from SC
    where Cno='1'
21
select max(Grade) as MAX
    from SC
    where Cno='1'
22
select SUM(Ccredit) as SUM
        from Course, SC
        where Course.Cno=SC.Cno 
        and Sno='201215121'
23
select Cno, count(Grade) as COUNT
        from SC
        group by Cno
24
select Sno
        from(
        select Sno, count(Grade) as COUNT
                from SC
                group by Sno
            )as test
        where COUNT>2
25
select Sno, AVG(Grade) as AVG
        from SC
        group by Sno
        having AVG(Grade)>=88
26
select Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
    from Student inner join SC
    on Student.Sno=SC.Sno
27
SELECT x.Cno, y.Cpno
        FROM Course x, Course y
        WHERE x.Cpno = y.Cno
28
select Student.Sno, Student.Sname
        from Student, SC
        where Student.Sno=SC.Sno
                and SC.Cno='2'
                and SC.Grade>80
29
select test.Sno, test.Sname, Course.Cname, test.Grade
    from Course join
        (select SC.Sno, SC.Cno, Student.Sname, SC.Grade
            from Student join SC 
                on Student.Sno=SC.Sno)as test
        on Course.Cno=test.Cno

Movie相关题目

30
select title
        from movie
        where director='Steven Spielberg'
31
select year
        from movie
        where mID in (
                select mID
                from rating
                where stars='4' or stars='5'
                )
        order by year asc
32
select title
        from movie
        where mID not in (
                select mID
                    from rating)
33
select name
        from reviewer
        where rID in (
                select rID
                        from rating
                        where ratingDate is null)
34
select reviewer.name, test.title, test.stars, test.ratingDate
from reviewer join (
    select movie.title, rating.rID, rating.stars, rating.ratingDate
    from movie join rating
    on movie.mID=rating.mID
    ) as test
on reviewer.rID=test.rID
order by reviewer.name, test.title, test.stars
35
37
select movie.title, rating_2.stars_avg as stars
from movie join (
    select mID, AVG(stars) as stars_avg
    from rating
    group by mID) as rating_2
on movie.mID=rating_2.mID
order by stars desc, title
38
select name
from reviewer
where rID in(
    select rID
    from rating
    group by rID
    having COUNT(rID)>=3)
39
select name 
from reviewer
where rID in (
select distinct rID
from rating
where mID in (
    select mID
    from movie
    where title='Gone with the Wind'))
 40
 select temp.name as name, movie.title as title, temp.stars as stars
    from movie join (
        select rating.stars, reviewer.name, rating.mID
        from rating join reviewer
        on rating.rID=reviewer.rID) as temp
    on movie.mID=temp.mID
    where movie.director=temp.name