数据库——SQL-SERVER练习（1）连接与子查询

佚名 6年前 (2019-05-09) 随笔 2005人围观抢沙发百度已收录

一.实验准备

1.复制实验要求文件及“CREATE-TABLES.SQL”文件，粘贴到本地机桌面。

2．启动SQL-SERVER服务。

SRE实战互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

3. 运行查询分析器，点击菜单《文件》/《打开》，打开CREATE-TABLES.SQL， 并运行，建立学生数据库STUDB及表STUDENT, COURSE, SC。

4. 点击菜单《文件》/《新建》，建立一个空查询窗口。开始实验。

5.本次实验环境是xp虚拟机，SQL-SERVER迷你版。

//CREATE-TABLES.SQL
use master
go

if exists (select * from dbo.sysdatabases where name = 'STUDB') 
drop database STUDB
GO

create database STUDB
go
use STUDB
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SC]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SC]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[STUDENT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[STUDENT]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[COURSE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[COURSE]
GO





CREATE TABLE STUDENT
(
    SNO  NUMERIC(5)  CONSTRAINT P_STUDENT PRIMARY KEY,
    SNAME CHAR(6) NOT NULL,
        SSEX  CHAR(2)  DEFAULT '男'
                CONSTRAINT C_SSEX CHECK( SSEX IN ('男','女')),
    SAGE  NUMERIC(2) DEFAULT 20,
        SDEPT CHAR(10)
      
);


CREATE TABLE COURSE
(
    CNO  NUMERIC(2)  CONSTRAINT P_COURSE PRIMARY KEY,
    CNAME CHAR(10) NOT NULL  CONSTRAINT U_CNAME UNIQUE,
    CPNO NUMERIC(2)   CONSTRAINT F_CPNO REFERENCES COURSE(CNO),
    CCREDIT NUMERIC(2)
);

CREATE TABLE SC
(
    SNO  NUMERIC(5)  REFERENCES STUDENT,
    CNO NUMERIC(2)  REFERENCES COURSE(CNO),
    GRADE  NUMERIC(6,2),
        PRIMARY KEY(SNO,CNO) 
);



insert INTO STUDENT  values(  95001,'李勇','男',20,'CS');
insert INTO  STUDENT  values( 95002,'刘晨','女',19,'IS');
insert  INTO STUDENT  values( 95003,'王敏','女',18,'MA');
insert  INTO STUDENT  values( 95004,'张立','男',21,'IS');
insert  INTO STUDENT  values( 95005,'周斌','男',18,'CS');
insert  INTO STUDENT  values( 95006,'孙兵','男',19,'CS');


insert  INTO COURSE  values( 2,'数学',NULL,2);
insert  INTO COURSE  values( 6,'数据处理',2,2);
insert  INTO COURSE  values( 4,'操作系统',6,3);

insert  INTO COURSE  values( 7,'PASCAL',6,4);

insert  INTO COURSE  values( 5,'数据结构',7,4);

insert  INTO COURSE  values( 1,'数据库原理',5,4);

insert  INTO COURSE  values( 3,'信息系统',1,4);

insert  INTO SC  values( 95001,1,92);
insert  INTO SC  values( 95001,2,85);
insert  INTO SC  values( 95001,3,88);
insert  INTO SC  values( 95002,2,90);
insert  INTO SC  values( 95002,3,80);
insert  INTO SC  values( 95003,1,80);
insert  INTO SC  values( 95004,1,75);
insert  INTO SC  values( 95005,1,96);
insert  INTO SC  values( 95003,2,NULL);
insert  INTO SC  values( 95003,4,NULL);

二.实验内容

1.查询各个课程号及相应的选课人数（SC表，按课号分组）

SELECT CNO,COUNT(SNO)
FROM SC
GROUP BY CNO

2.查询每个学生的学号和选课门数, 最高成绩，最低成绩，平均成绩

SELECT SNO,COUNT(CNO),MAX(GRADE),MIN(GRADE),AVG(GRADE)
FROM SC
GROUP BY SNO

3.查询各系的系名, 性别，人数, 平均年龄(按系名, 性别分组)

SELECT SDEPT,SSEX,COUNT(*),AVG(SAGE)
FROM STUDENT
GROUP BY SDEPT,SSEX

4. 查询选修了>=3门课的学生号( HAVING )

SELECT SNO
FROM SC
GROUP BY SNO
HAVING COUNT(*)>=3

5. 查询选修学生人数>=2的课号

SELECT CNO
FROM SC
GROUP BY CNO
HAVING COUNT(*)>=2

6 .查询成绩在75以上超过两门课的学生号 ( WHERE+HAVING )

SELECT SNO
FROM SC
WHERE GRADE>75
GROUP BY SNO
HAVING COUNT(*)>=2

7. 查询每个学生及其选课情况 ( 连接STUDENT和SC )

SELECT *
FROM STUDENT,SC
WHERE STUDENT.SNO=SC.SNO

8. 查询CS系学生的学生号，学生名，课号，成绩（用连接）

SELECT STUDENT.SNO,SNAME,CNO,GRADE
FROM STUDENT,SC
WHERE STUDENT.SNO=SC.SNO AND SDEPT='CS'

9.查询选1号课的学生号，学生名，成绩（用连接）

SELECT STUDENT.SNO,SNAME,GRADE
FROM STUDENT,SC
WHERE STUDENT.SNO=SC.SNO AND CNO='1'

10.查询每个学号的平均成绩，总学分（连接SC和COURSE，分组）

SELECT SNO,AVG(GRADE),SUM(CCREDIT)
FROM COURSE,SC
WHERE SC.CNO=COURSE.CNO AND GRADE>=60
GROUP BY SNO

11.查询CS系学生的学生号，选课课号，课名，成绩（三表连接）

SELECT STUDENT.SNO,COURSE.CNO,CNAME,GRADE
FROM COURSE,SC,STUDENT
WHERE STUDENT.SNO=SC.SNO AND SC.CNO=COURSE.CNO AND SDEPT='CS'

12. 查询与“刘晨”在同一个系学习的学生号，姓名，系名。

SELECT SNO,SNAME,SDEPT
FROM STUDENT
WHERE SDEPT =
(
SELECT SDEPT
FROM STUDENT
WHERE SNAME='刘晨'
)

13. 查询选1号课的学生号，学生名（用IN 子查询）

SELECT SNO,SNAME
FROM STUDENT
WHERE SNO IN
(
SELECT SNO
FROM SC
WHERE CNO='1'
)

14. 查询未选1号课的学生号，学生名（用NOT IN 子查询）

SELECT SNO,SNAME
FROM STUDENT
WHERE SNO NOT IN
(
SELECT SNO
FROM SC
WHERE CNO='1'
)

15. 查询没有不及格的学生号，学生名（用NOT IN 子查询）

SELECT SNO,SNAME
FROM STUDENT
WHERE SNO NOT IN
(
SELECT SNO
FROM SC
WHERE GRADE<60
)

16. 查询学生号，姓名，课号，成绩, 要包括未选修课的学生（用外连接）

SELECT STUDENT.SNO,SNAME,CNO,GRADE
FROM STUDENT,SC
WHERE STUDENT.SNO*=SC.SNO

17. 查询课号, 课名, 学号, 成绩 , 包括未被选修的课.（用外连接）

SELECT COURSE.CNO,COURSE.CNAME,SNO,GRADE
FROM COURSE,SC
WHERE COURSE.CNO*=SC.CNO

18．查询比刘晨年龄大的学生号,姓名。（>子查询）

SELECT SNO,SNAME
FROM STUDENT
WHERE SAGE >
(
 SELECT SAGE
 FROM STUDENT
 WHERE SNAME='刘晨'
)

19. 查询比信息系任意一个学生年龄小的学生姓名和年龄。（<ANY子查询）

SELECT SNAME,SAGE
FROM STUDENT
WHERE SAGE <ANY
(
 SELECT SAGE
 FROM STUDENT
 WHERE SDEPT='IS'
)

20.查询选’数据库原理’课的学号(用 IN子查询)

SELECT SNO
FROM STUDENT
WHERE SNO IN
( 
  SELECT SNO
  FROM SC
  WHERE CNO IN
  (
     SELECT CNO
     FROM COURSE
     WHERE CNAME='数据库原理'
   )
)

21. 查询选’数据库原理’课的学号,姓名(用 IN子查询)

SELECT SNO,SNAME
FROM STUDENT
WHERE SNO IN
( 
  SELECT SNO
  FROM SC
  WHERE CNO IN
  (
     SELECT CNO
     FROM COURSE
     WHERE CNAME='数据库原理'
   )
)