数据库查询语句练习

Sweep-

已于 2022-07-11 08:26:26 修改

阅读量1.3k

点赞数

分类专栏：数据库文章标签：数据库 database

于 2022-06-20 23:32:22 首次发布

本文链接：https://blog.csdn.net/x20020402/article/details/123927028

版权

数据库专栏收录该内容

5 篇文章 4 订阅

订阅专栏

一、关系模式

--department(dNo,dName,officeRoom,homepage)
--student(Sno,Sname,Ssex,Sage,dNo)
--course(Cno,Cname,Cpno,Ccredit,dNo)
--sc(Sno,Cno,grade,recordDate)

二、创建表



CREATE TABLE department(
  dNo           CHAR(2)        NOT NULL UNIQUE,
  dName         VARCHAR(20),
  officeRoom    VARCHAR(40),
  homePage      VARCHAR(80),
  PRIMARY KEY(dNo)
);

CREATE TABLE student(
  Sno 		CHAR(10)        NOT NULL UNIQUE,
  Sname		VARCHAR(40)    NOT NULL,
  Ssex		CHAR(2),       
  Sage		INT,
  dNo		CHAR(2),
  PRIMARY KEY(Sno),
  FOREIGN KEY (dNo) REFERENCES  department(dNo)
);


CREATE TABLE course(
  Cno		CHAR(8)	       NOT NULL UNIQUE,
  Cname		CHAR(40)    NOT NULL,
  Cpno		CHAR(8),
  Ccredit	INT,
  dNo           CHAR(2),
  PRIMARY KEY(Cno),
  FOREIGN KEY (Cpno) REFERENCES  course(Cno),
  FOREIGN KEY (dNo)  REFERENCES  department(dNo)
);

CREATE TABLE sc(
  Sno		CHAR(10)	       NOT NULL,
  Cno		CHAR(8)	       NOT NULL,
  grade		INT,
  recordDate    date,
  PRIMARY KEY(Sno,Cno),
  FOREIGN KEY (Sno) REFERENCES  student(Sno),
  FOREIGN KEY (Cno) REFERENCES  course(Cno)
);

三、插入表



INSERT INTO department VALUES('01','信息学院','行政楼409','www.xxx.edu.cn');
INSERT INTO department VALUES('02','软件学院',null,null);
INSERT INTO department VALUES('03','理学院',null,null);
INSERT INTO department VALUES('04','文学院',null,null);
INSERT INTO department VALUES('05','外国语学院',null,null);

INSERT INTO student VALUES('170101','宁灿',  '女',19,'01');
INSERT INTO student VALUES('170102','尹江月','女',19,'01');
INSERT INTO student VALUES('170103','杨佳伟','男',null,null);
INSERT INTO student VALUES('170104','杨何宇','男',19,'01');
INSERT INTO student VALUES('170105','胡耀斌','男',19,null);
INSERT INTO student VALUES('170106','李杨阳','女',20,'01');
INSERT INTO student VALUES('170107','杜利俊','女',18,'01');
INSERT INTO student VALUES('170108','钱多多','女',17,'01');
INSERT INTO student VALUES('170109','李佳伟','女',null,'01');
INSERT INTO student VALUES('170110','吴莫愁','女',21,'01');

INSERT INTO student VALUES('170201','安相成','男',19,'02');
INSERT INTO student VALUES('170202','曹师好','男',null,'02');
INSERT INTO student VALUES('170203','雷霆',  '男',18,'02');
INSERT INTO student VALUES('170204','刘书敏','男',20,'02');
INSERT INTO student VALUES('170205','王兵',  '男',21,'02');
INSERT INTO student VALUES('170206','李佳成','男',19,null);
INSERT INTO student VALUES('170207','唐玉迎','女',17,'02');
INSERT INTO student VALUES('170208','杨曼婷','女',19,'02');

INSERT INTO student VALUES('170301','张望',  '男',21,'03');
INSERT INTO student VALUES('170302','王芳',  '女',18,'03');
INSERT INTO student VALUES('170303','赵四海','男',19,'03');

INSERT INTO student VALUES('170401','孙敏',  '女',null,null);
INSERT INTO student VALUES('170402','李忠国','男',null,'04');
INSERT INTO student VALUES('170403','钱紧',  '男',17,'04');
INSERT INTO student VALUES('170404','钱多多','女',20,'04');
INSERT INTO student VALUES('170405','管八方','男',21,'04');
INSERT INTO student VALUES('170406','王兵',  '男',19,'04');
INSERT INTO student VALUES('170407','张三丰','男',null,null);

INSERT INTO course VALUES('030101','高等数学',null,2,'03');
INSERT INTO course VALUES('030102','线性代数',null,2,'03');
INSERT INTO course VALUES('030201','矩阵论','030102',3,'03');
INSERT INTO course VALUES('030202','概率论','030101',2,'03');
INSERT INTO course VALUES('030301','数理统计','030202',3,'03');

INSERT INTO course VALUES('010101','信号与系统',null,2,'01');
INSERT INTO course VALUES('010102','数字电路','010101',2,'01');
INSERT INTO course VALUES('010103','数字信号处理','030301',3,'01');
INSERT INTO course VALUES('010201','模式识别','010103',3,'01');

INSERT INTO course VALUES('020101','离散数学',null,2,'02');
INSERT INTO course VALUES('020102','程序设计基础',null,2,'02');
INSERT INTO course VALUES('020201','计算机组成原理','020101',3,'02');
INSERT INTO course VALUES('020202','面向对象程序设计','020102',2,'02');
INSERT INTO course VALUES('020203','编译原理','020101',2,'02');
INSERT INTO course VALUES('020301','数据结构','020202',3,'02');
INSERT INTO course VALUES('020302','操作系统','020201',3,'02');
INSERT INTO course VALUES('020401','数据库系统','020301',3,'02');
INSERT INTO course VALUES('020402','算法设计与分析','020301',3,'02');
INSERT INTO course VALUES('020403','形式语言与自动机','020203',3,'02');
INSERT INTO course VALUES('020501','移动计算','020102',2,'02');

INSERT INTO course VALUES('040101','中国传统文化',null,1,'04');
INSERT INTO course VALUES('040102','近代世界史',null,1,'04');
INSERT INTO course VALUES('040103','现代文学',null,1,'04');
INSERT INTO course VALUES('040201','古典文学欣赏','040101',2,'04');
INSERT INTO course VALUES('040202','世界文学','040103',2,'04');
INSERT INTO course VALUES('040301','中国诗词欣赏','040201',2,'04');

INSERT INTO sc VALUES ('170101','030101',91,null);
INSERT INTO sc VALUES ('170101','030102',83,null);
INSERT INTO sc VALUES ('170101','020101',88,null);
INSERT INTO sc VALUES ('170101','020102',92,null);
INSERT INTO sc VALUES ('170101','020201',70,null);
INSERT INTO sc VALUES ('170101','020202',80,null);
INSERT INTO sc VALUES ('170101','020203',null,null);
INSERT INTO sc VALUES ('170101','020301',null,null);
INSERT INTO sc VALUES ('170101','020302',null,null);
INSERT INTO sc VALUES ('170101','020401',null,null);
INSERT INTO sc VALUES ('170101','020402',null,null);

INSERT INTO sc VALUES ('170102','030101',88,null);
INSERT INTO sc VALUES ('170102','030102',86,null);
INSERT INTO sc VALUES ('170102','030201',58,null);
INSERT INTO sc VALUES ('170102','030202',90,null);
INSERT INTO sc VALUES ('170102','030301',70,null);
INSERT INTO sc VALUES ('170102','010101',85,null);
INSERT INTO sc VALUES ('170102','010102',68,null);
INSERT INTO sc VALUES ('170102','010103',80,null);
INSERT INTO sc VALUES ('170102','010201',78,null);

INSERT INTO sc VALUES ('170103','030101',88,null);
INSERT INTO sc VALUES ('170103','030102',80,null);
INSERT INTO sc VALUES ('170103','030202',76,null);
INSERT INTO sc VALUES ('170103','010101',90,null);
INSERT INTO sc VALUES ('170103','010102',83,null);
INSERT INTO sc VALUES ('170103','010103',55,null);
INSERT INTO sc VALUES ('170103','010201',77,null);

INSERT INTO sc VALUES ('170104','030101',null,null);

INSERT INTO sc VALUES ('170105','030101',null,null);

INSERT INTO sc VALUES ('170108','030101',90,null);
INSERT INTO sc VALUES ('170108','030102',87,null);
INSERT INTO sc VALUES ('170108','030202',78,null);
INSERT INTO sc VALUES ('170108','010101',57,null);
INSERT INTO sc VALUES ('170108','010102',56,null);
INSERT INTO sc VALUES ('170108','010103',54,null);
INSERT INTO sc VALUES ('170108','010201',50,null);

INSERT INTO sc VALUES ('170110','030101',90,null);
INSERT INTO sc VALUES ('170110','030102',90,null);
INSERT INTO sc VALUES ('170110','010101',88,null);
INSERT INTO sc VALUES ('170110','010102',88,null);
INSERT INTO sc VALUES ('170110','010103',77,null);

INSERT INTO sc VALUES ('170201','030101',88,null);
INSERT INTO sc VALUES ('170201','030102',83,null);
INSERT INTO sc VALUES ('170201','020101',79,null);
INSERT INTO sc VALUES ('170201','020102',84,null);
INSERT INTO sc VALUES ('170201','020201',75,null);
INSERT INTO sc VALUES ('170201','020202',80,null);
INSERT INTO sc VALUES ('170201','020301',82,null);
INSERT INTO sc VALUES ('170201','020302',53,null);
INSERT INTO sc VALUES ('170201','020401',78,null);
INSERT INTO sc VALUES ('170201','020402',58,null);


INSERT INTO sc VALUES ('170202','030101',86,null);
INSERT INTO sc VALUES ('170202','030102',82,null);
INSERT INTO sc VALUES ('170202','020101',90,null);
INSERT INTO sc VALUES ('170202','020102',85,null);
INSERT INTO sc VALUES ('170202','020201',80,null);
INSERT INTO sc VALUES ('170202','020202',78,null);
INSERT INTO sc VALUES ('170202','020203',75,null);
INSERT INTO sc VALUES ('170202','020301',70,null);
INSERT INTO sc VALUES ('170202','020302',68,null);
INSERT INTO sc VALUES ('170202','020401',80,null);
INSERT INTO sc VALUES ('170202','020402',56,null);

INSERT INTO sc VALUES ('170203','030101',null,null);
INSERT INTO sc VALUES ('170203','030102',null,null);

INSERT INTO sc VALUES ('170205','030101',80,null);
INSERT INTO sc VALUES ('170205','030102',55,null);
INSERT INTO sc VALUES ('170205','020101',50,null);
INSERT INTO sc VALUES ('170205','020102',96,null);
INSERT INTO sc VALUES ('170205','020201',80,null);
INSERT INTO sc VALUES ('170205','020202',85,null);
INSERT INTO sc VALUES ('170205','020203',60,null);
INSERT INTO sc VALUES ('170205','020301',88,null);
INSERT INTO sc VALUES ('170205','020302',92,null);
INSERT INTO sc VALUES ('170205','020401',65,null);
INSERT INTO sc VALUES ('170205','020402',70,null);
INSERT INTO sc VALUES ('170205','020501',80,null);

INSERT INTO sc VALUES ('170207','030101',null,null);
INSERT INTO sc VALUES ('170207','030102',null,null);

INSERT INTO sc VALUES ('170208','030101',60,null);
INSERT INTO sc VALUES ('170208','030102',null,null);
INSERT INTO sc VALUES ('170208','020101',null,null);
INSERT INTO sc VALUES ('170208','020102',null,null);
INSERT INTO sc VALUES ('170208','020201',null,null);
INSERT INTO sc VALUES ('170208','020202',null,null);
INSERT INTO sc VALUES ('170208','020301',null,null);
INSERT INTO sc VALUES ('170208','020302',null,null);
INSERT INTO sc VALUES ('170208','020401',null,null);
INSERT INTO sc VALUES ('170208','020402',null,null);
INSERT INTO sc VALUES ('170208','020501',null,null);


INSERT INTO sc VALUES ('170303','030101',90,null);
INSERT INTO sc VALUES ('170303','030102',88,null);
INSERT INTO sc VALUES ('170303','030201',92,null);
INSERT INTO sc VALUES ('170303','030202',93,null);
INSERT INTO sc VALUES ('170303','030301',94,null);

INSERT INTO sc VALUES ('170401','040101',82,null);
INSERT INTO sc VALUES ('170401','040102',80,null);
INSERT INTO sc VALUES ('170401','040103',83,null);
INSERT INTO sc VALUES ('170401','040201',89,null);
INSERT INTO sc VALUES ('170401','040202',70,null);
INSERT INTO sc VALUES ('170401','040301',null,null);

INSERT INTO sc VALUES ('170403','040201',95,null);
INSERT INTO sc VALUES ('170403','040202',88,null);
INSERT INTO sc VALUES ('170403','040301',80,null);

INSERT INTO sc VALUES ('170404','040101',95,null);
INSERT INTO sc VALUES ('170404','040102',91,null);
INSERT INTO sc VALUES ('170404','040103',89,null);
INSERT INTO sc VALUES ('170404','040201',93,null);
INSERT INTO sc VALUES ('170404','040202',92,null);
INSERT INTO sc VALUES ('170404','040301',88,null);

INSERT INTO sc VALUES ('170405','040102',70,null);
INSERT INTO sc VALUES ('170405','040201',58,null);
INSERT INTO sc VALUES ('170405','040202',66,null);
INSERT INTO sc VALUES ('170405','040301',77,null);

INSERT INTO sc VALUES ('170406','040101',55,null);
INSERT INTO sc VALUES ('170406','040102',65,null);
INSERT INTO sc VALUES ('170406','040301',75,null);

四、练习语句

一、基本查询
1、查询所有年龄小于18岁的男生姓名

select sNo,sName from student where sage<18

2、查询所有学分大于3的课程名称

select cName from Course where Ccredit>3
3、查询所有没有先修课的课程名称

select cName from Course where cPNo is null

二、连接查询
1、查询“信息学院”所有学生姓名

select sName
from student
where dNo=(select dNo
from department
where dName='信息学院'
)
2、查询所有具有不及格记录的学生姓名

select sName
from Student
where sNo in(select sNo
from SC
       where grade<60
       )
3、查询“信息学院”所有年龄小于18岁的女生姓名

select sName
from Student
where dNo=(select dNo
from Department
       where dName='信息学院'
       )
       and Ssex='女'
4、查询所有选修了“数据库”课程的学生姓名

select sName
from Student
where sNo in(select sNo
from SC
       where cNo=(select cNo
       from Course
                   where cName='数据库系统'
                   )
           )
5、查询“信息学院”所有选修了“科学技术简史”课程的学生姓名
6、查询选修了“数据结构”课程并且不及格的学生姓名
7、查询“计算机学院”选修了“概率论”课程且成绩大于等于80分的学生姓名
8、查询“文学院”开设的所有学分大于等于3的课程名称
9、查询“信息学院”所有选修了“数据库”课程并且不及格的学生姓名及分数
10、查询“王强”同学所选修的全部课程名称、学分以及开课学院。

三、综合查询
1、查询选修了“数据库”或者选修了“算法”课程的学生姓名
2、查询选修了“数据库”并且选修了“算法”课程的学生姓名
3、查询选修了“数据库”但没有选修“算法”课程的学生姓名
4、查询没有选修“算法”课程的学生姓名
5、查询仅仅选修了“算法”一门课程的学生姓名
6、查询选修了“信息学院”开设的所有课程的学生姓名
7、查询选修了所有学分大于3课程的学生姓名
8、查询“数据库”课程所有不及格学生姓名及所在学院
9、查询“数据库”先修课的课程名称

select Cname
from course
where Cno in (select Cpno
from course
           where Cname='数据库系统');
10、查询先修课为“数据库”的课程名称

Sweep-

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
打赏
0
评论
数据库查询语句练习

一、关系模式--department(dNo,dName,officeRoom,homepage)--student(Sno,Sname,Ssex,Sage,dNo)--course(Cno,Cname,Cpno,Ccredit,dNo)--sc(Sno,Cno,grade,recordDate)二、创建表CREATE TABLE department( dNo CHAR(2) NOT NULL UNIQUE, dName .
复制链接

扫一扫