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

成绩管理相关题目

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