SQL基础题目

来源：互联网发布：酷狗m1 知乎编辑：程序博客网时间：2024/06/17 13:31

题一，

S（学号，性别，年龄，姓名，系别，省区） SC(学号，课程号，学分) C（课程号，课程名，学分）
用SQL语句实现：
1查询选修了课程的人数

2查询选修了1号课程的学生姓名

3查询每个学生的学号，姓名，选修课程名，成绩

4查询选修课程“人工智能”的学生的学号，姓名

5 查询选修1号课程的最高分

6查询姓名中第二个子为“月”的学生

7 求各课程号及相应的选课人数

8查询选修2号课程且成绩在80分以上的学生

9查询“物理系“的学生来自那些省区

10查询全体学生情况，结果按所在系升序排列，同一系在按年龄降序排列

11查询选修了2号课程的平均成绩

12查询姓名中第二个子为”阳“的学生姓名和学号

1.SELECT COUNT(DISTINCT(学号）） FROM SC
2.SELECT 姓名 FROM S,SC WHERE S.学号=SC.学号 AND S.学号=1号
3.SELECT S.学号，姓名，课程名，成绩 FROM S,SC,C WHERE S.学号=SC.学号 ANDSC.课程号=C.课程 AND 课程号=1号
4.SELECT S.学号，姓名 FROM S,SC WHERE S.学号=SC.学号 AND SC.课程=‘人工智能’
5.SELECT MAX(成绩） FROM S,SC WHERE S.学号=SC.学号 AND S.学号=1号
6.SELECT 学号，姓名 FROM S WHERE SUBSTRING(姓名，3,2）=’月’
7.SELECT 课程号，COUNT(*） FROM C,SC WHERE C.课程号=SC.课程号
8.SELECT S.学号，姓名 FROM S,SC,C WHERE S.学号=SC.学号 AND SC.课程号=C.课程号 ANDC.成绩>80
9.SELECT DISTINCT（省区） FROM S WHERE S.系别=‘物理’
10.SELECT * FROM S ORDER BY 系别 ASC，年龄 DSC
11.SELECT AVG(成绩） FROM C WHERE 课程号=2号
12.SELECT 学号，姓名 FROM S WHERE SUBSTRING(姓名，3,2）=’阳’

题二，

S(SNO,SNAME）学生关系。SNO 为学号，SNAME 为姓名
C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME为课程名，CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩

建表语句：

create table S

(

SNO VARCHAR2(3),

SNAME VARCHAR2(10)

);

insert into S (SNO, SNAME) values ('1', '赵一');

insert into S (SNO, SNAME) values ('2', '钱二');

insert into S (SNO, SNAME) values ('3', '孙三');

insert into S (SNO, SNAME) values ('4', '李四');

insert into S (SNO, SNAME) values ('5', '王五');

insert into S (SNO, SNAME) values ('6', '张六');

create table C

(

CNO VARCHAR2(3),

CNAME VARCHAR2(30),

CTEACHER VARCHAR2(10)

);

insert into C (CNO, CNAME, CTEACHER) values ('1', '语文','语文老师');

insert into C (CNO, CNAME, CTEACHER) values ('2', '数学','数学老师');

insert into C (CNO, CNAME, CTEACHER) values ('3', '英语','英语老师');

insert into C (CNO, CNAME, CTEACHER) values ('4', '历史','历史老师');

insert into C (CNO, CNAME, CTEACHER) values ('5', '地理','地理老师');

insert into C (CNO, CNAME, CTEACHER) values ('6', '生物','生物老师');

create table SC

(

SNO VARCHAR2(3),

CNO VARCHAR2(3),

SCGRADE NUMBER

);

insert into SC (SNO, CNO, SCGRADE) values ('1', '1', 11);

insert into SC (SNO, CNO, SCGRADE) values ('1', '2', 12);

insert into SC (SNO, CNO, SCGRADE) values ('1', '3', 13);

insert into SC (SNO, CNO, SCGRADE) values ('1', '4', 14);

insert into SC (SNO, CNO, SCGRADE) values ('1', '5', 15);

insert into SC (SNO, CNO, SCGRADE) values ('1', '6', 16);

insert into SC (SNO, CNO, SCGRADE) values ('2', '1', 21);

insert into SC (SNO, CNO, SCGRADE) values ('2', '2', 22);

insert into SC (SNO, CNO, SCGRADE) values ('2', '3', 23);

insert into SC (SNO, CNO, SCGRADE) values ('3', '4', 34);

insert into SC (SNO, CNO, SCGRADE) values ('3', '5', 35);

insert into SC (SNO, CNO, SCGRAD

E) values ('3', '6', 36);

insert into SC (SNO, CNO, SCGRADE) values ('4', '1', 41);

insert into SC (SNO, CNO, SCGRADE) values ('4', '2', 42);

insert into SC (SNO, CNO, SCGRADE) values ('5', '3', 53);

insert into SC (SNO, CNO, SCGRADE) values ('5', '4', 54);

1. 找出没有选修过“语文老师”老师讲授课程的所有学生姓名

正确写法一：
SELECT S.SNAME
FROM S
WHERE NOT EXISTS
     (SELECT * FROM SC,C
      WHERE SC.CNO=C.CNO
      AND SC.SNO=S.SNO
      AND C.CTEACHER='语文老师')

正确写法二：
SELECT S.SNAME
FROM S
WHERE SNO NOT IN
     (SELECT SNO
      FROM SC,C
      WHERE SC.CNO=C.CNO
      AND C.CTEACHER='语文老师')

不推荐，如果子查询中有null值，结果将是错误的。

错误写法一：
SELECT S.SNO,S.SNAME
FROM S,C,SC
WHERE SC.CNO=C.CNO
AND SC.SNO=S.SNO
AND C.CTEACHER<>'语文老师'
ORDER BY S.SNO

错误分析：这条语句是错误的，错在将sc表与c表做连接，这样做的结果就是会将sc表的所有记录都取出，即这个学生选了所有的课程，仍然会被选出，因为数学老师也不是语文老师,历史老师也不是语文老师....

错误写法二：
SELECT S.SNAME
FROM S
WHERE SNO IN
     (SELECT SNO FROM SC,C
      WHERE SC.CNO=C.CNO
      AND C.CTEACHER<>'语文老师')

错误分析同上

2. 列出有二门以上（含两门）课程低于40分的学生姓名及其平均成绩(低于40分以下课程的平均成绩)

正确写法一：
SELECT S.SNAME,A.SCORE
FROM
(SELECT SNO,AVG(SC.SCGRADE) AS SCORE FROM SC
WHERE SCGRADE<40
GROUP BY SNO
HAVING COUNT(*)>=2)A,S
WHERE A.SNO=S.SNO

使用子查询

正确写法二：
SELECT S.SNAME,AVG(SC.SCGRADE) FROM SC, S
WHERE SC.SCGRADE<40
AND S.SNO = SC.SNO
GROUP BY S.SNAME
HAVING COUNT(*)>=2

所有课程的平均成绩的SQL
1. SELECT S.SNO,S.SNAME,AVG(SC.SCGRADE)
FROM S,SC,(
    SELECTSNO
    FROMSC
    WHERESCGRADE<40
    GROUP BYSNO
    HAVINGCOUNT(DISTINCT CNO)>=2
)A WHERE S.SNO=A.SNO AND SC.SNO=A.SNO
GROUP BY S.SNO,S.SNAME

2.select s.sname,res2.avg_all_course
from
(select sc.sno,avg(sc.scgrade) avg_Low_40
from sc
where sc.scgrade<40
group by sno
having count(*)>=2) res1,
(select sno,avg(sc.scgrade) avg_all_course
from sc
group by sno) res2,
s
where s.sno=res1.sno
and s.sno=res2.sno

3. 列出既学过语文课，又学过数学课的所有学生姓名

正确写法一：
SELECT SNAME
FROM
(SELECT SC.SNO
FROM C,SC
WHERE SC.CNO=C.CNO
AND C.CNAME IN('语文,'数学’)
GROUP BY SC.SNO
HAVING COUNT(*)=2)A,S
WHERE A.SNO=S.SNO

使用子查询

正确写法二：
SELECT S.SNAME
FROM C,SC, S
WHERE SC.CNO=C.CNO AND SC.SNO = S.SNO
AND C.CNAME IN('语文’,'数学')
GROUP BY S.SNAME
HAVING COUNT( * ) =2

3,我个人的写法

select s.sname
from s
where sno in
(select distinct(sno)
from sc
where sc.cno in
(select c.cno
from c
where c.cname in
('语文','数学'))

4. 列出1号课比2号课成绩低的所有学生的姓名及1号课和2号课的成绩

正确写法一：

使用自连接及子查询
SELECT SNAME,T.SCORE1,T.SCORE2
FROM
(SELECT A.SNO,A.SCGRADE AS SCORE1,B.SCGRADE AS SCORE2
FROM SC A,SC B
WHERE A.SNO=B.SNO
AND A.CNO='1'
AND B.CNO='2'
AND A.SCGRADE<B.SCGRADE)T,S
WHERE T.SNO=S.SNO

正确写法二：

不使用子查询
SELECT S.SNAME,A.SCGRADE,B.SCGRADE
FROM S,SC A,SC B
WHERE S.SNO=A.SNO
AND S.SNO=B.SNO
AND A.CNO='1'
AND B.CNO='2'
AND A.SCGRADE<B.SCGRADE

5. 列出选修了所有课程的学生的姓名

正确写法一：

不使用子查询
SELECT S.SNAME
FROM SC,S
WHERE S.SNO=SC.SNO
GROUP BY S.SNAME
HAVING COUNT(*)=(SELECT COUNT(*) FROM C)

正确写法二：

使用子查询

select s.sname
from
(select sc.sno,count(cno)
from sc
group by sc.sno
having count(cno)=
(select count(cno)
from c)
) s1,s
where s.sno=s1.sno

0 0