SQL语句练习题(萨师煊数据库概论课后习题)

标签: 数据库

在这里插入图片描述

----------------------  创建数据库 school 脚本 ---------------------------  

--drop database school
create database school    --创建数据库
go

/*CREATE DATABASE school
ON 
( NAME = school_dat,
   FILENAME = 'd:\school1.mdf',
   SIZE = 2,
   MAXSIZE = 4,
   FILEGROWTH = 1 )
LOG ON
( NAME =school_log,
   FILENAME = 'd:\school1.ldf',
   SIZE = 2MB,
   MAXSIZE = 3MB,
   FILEGROWTH = 1MB ) */

use school   --进入 school 数据库
go

EXEC sp_addtype ud_sno,'char(6)'   
go

--创建表
create table Student ( 
   Sno ud_sno , 
   Sname char(10) not null unique ,
   Ssex char(2) check (ssex='男' or ssex='女') ,
   Sage smallint check(sage>16) ,
   Sdept char(10)  not null default 'JSJ' ,
   
   primary key (sno)
 ) 
create index ix_student_sname ON student(sname)

create table course( 
   Cno char(4) ,
   Cname char(16) ,
   Cpno  char(4) ,
   Ccredit int check (Ccredit >=0 and Ccredit<=5),

   check( cno<>cpno) , --约束
   primary key (cno)
 )
create index ix_course_cpno ON course(cpno)
 
create table SC( 
  Sno ud_sno ,
  Cno char(4) ,
  Grade int check(grade<=100) ,

  constraint pk_sc primary key (sno,cno),
  foreign key (sno) references student(sno) ,
  foreign key (cno) references course(cno) ,
 )
create index ix_sc_cno ON sc(cno)
 
--添加数据

insert into Student (Sno,Sname,Ssex,Sage,Sdept)  values ('0003','陈小明','男',20,'SX')
insert into Student (Sno,Sname,Ssex,Sage,Sdept)  values ('0004','杨秀红','女',21,'JSJ')
insert into Student (Sno,Sname,Ssex,Sage,Sdept)  values ('0001','周志林','男',20,'SX')
insert into Student (Sno,Sname,Ssex,Sage,Sdept)  values ('0009','钱明明','男',20,'SX')
insert into Student (Sno,Sname,Ssex,Sage,Sdept)  values ('0002','李文庆','男',23,'JSJ')
insert into Student (Sno,Sname,Ssex,Sage,Sdept)  values ('0081','刘亭','女',  22,'SX')
insert into Student (Sno,Sname,Ssex,Sage,Sdept)  values ('0091','贺秋雪','女',20,'SX')
insert into Student (Sno,Sname,Ssex,Sage,Sdept)  values ('0078','王振','男',  21,'JSJ')
insert into Student (Sno,Sname,Ssex,Sage,Sdept)  values ('0092','赵三','男',  22,'SX')
insert into student (sno,sname,ssex,sage,sdept) values ('8001','张华','男',23,'SX')
insert into student (sno,sname,ssex,sage,sdept) values ('8002','赵颖','女',21,'SX')
insert into student (sno,sname,ssex,sage,sdept) values ('8003','钱凯','男',22,'JSJ')
insert into student (sno,sname,ssex,sage,sdept) values ('8004','王华','男',21,'SX')
insert into student (sno,sname,ssex,sage) values ('8005','张英','女',21)
insert into student (sno,sname,ssex,sage) values ('8006','赵章','女',22)
insert into student (sno,sname,ssex,sage) values ('8007','钱利','男',23)
insert into student (sno,sname,ssex,sage) values ('8008','王铁','男',21)
insert into student (sno,sname,ssex,sage) values ('8009','张明','男',22)



insert into course(Cno,Cname,Cpno,Ccredit) values ('1001','高等数学','',5)
insert into course(Cno,Cname,Cpno,Ccredit) values ('1002','离散数学','1001',3)
insert into course(Cno,Cname,Cpno,Ccredit) values ('1003','程序设计','',5)
insert into course(Cno,Cname,Cpno,Ccredit) values ('1004','数据结构','1003',4)
insert into course(Cno,Cname,Cpno,Ccredit) values ('1005','数据库原理','1004',4)
insert into course(Cno,Cname,Cpno,Ccredit) values ('1006','操作系统','1004',5)

insert into SC(Sno,Cno,Grade) values ('0002','1002',90)
insert into SC(Sno,Cno,Grade) values ('0002','1001',91)
insert into SC(Sno,Cno,Grade) values ('0002','1003',67)
insert into SC(Sno,Cno,Grade) values ('0002','1004',98)
insert into SC(Sno,Cno,Grade) values ('0001','1002',92)
insert into SC(Sno,Cno,Grade) values ('0001','1001',92)
insert into SC(Sno,Cno,Grade) values ('0001','1003',76)
insert into SC(Sno,Cno,Grade) values ('0003','1001',77)
insert into SC(Sno,Cno,Grade) values ('0001','1004',     91)
insert into SC(Sno,Cno,Grade) values ('0004','1001',     97)
insert into SC(Sno,Cno,Grade) values ('0004','1002',     78)
insert into SC(Sno,Cno,Grade) values ('0004','1003',     65)
insert into SC(Sno,Cno,Grade) values ('0004','1004',     89)
insert into SC(Sno,Cno,Grade) values ('0091','1001',     93)
insert into SC(Sno,Cno,Grade) values ('0091','1002',     87)
insert into SC(Sno,Cno,Grade) values ('0091','1003',     99)
insert into SC(Sno,Cno,Grade) values ('0091','1004',     95)
insert into SC(Sno,Cno,Grade) values ('0009','1001',     93)
insert into SC(Sno,Cno,Grade) values ('0009','1002',     88)
insert into SC(Sno,Cno,Grade) values ('0009','1003',     60)
insert into SC(Sno,Cno,Grade) values ('0009','1004',     83)
insert into SC(Sno,Cno,Grade) values ('0092','1001',     98)
insert into SC(Sno,Cno,Grade) values ('0092','1002',     86)
insert into SC(Sno,Cno) values ('0092','1003')

insert into SC(Sno,Cno,Grade) values ('0081','1001',     90)
insert into SC(Sno,Cno) values ('0081','1002')
insert into SC(Sno,Cno) values ('0081','1003')

insert into SC(Sno,Cno,Grade) values ('8001','1002',90)
insert into SC(Sno,Cno,Grade) values ('8001','1001',91)
insert into SC(Sno,Cno,Grade) values ('8001','1003',67)
insert into SC(Sno,Cno,Grade) values ('8001','1005',98)
insert into SC(Sno,Cno,Grade) values ('8002','1002',87)
insert into SC(Sno,Cno,Grade) values ('8002','1001',96)
insert into SC(Sno,Cno,Grade) values ('8002','1003',68)
insert into SC(Sno,Cno,Grade) values ('8002','1005',92)

insert into SC(Sno,Cno,Grade) values ('8003','1001',91)
insert into SC(Sno,Cno,Grade) values ('8003','1003',92)
insert into SC(Sno,Cno,Grade) values ('8003','1004',63)
insert into SC(Sno,Cno,Grade) values ('8003','1005',94)

insert into SC(Sno,Cno,Grade) values ('8004','1001',92)
insert into SC(Sno,Cno,Grade) values ('8004','1003',93)
insert into SC(Sno,Cno,Grade) values ('8004','1004',64)
insert into SC(Sno,Cno) values ('8004','1005')

insert into SC(Sno,Cno) values ('8006','1001')
insert into SC(Sno,Cno,Grade) values ('8006','1002',92)
insert into SC(Sno,Cno,Grade) values ('8006','1003',20)
insert into SC(Sno,Cno) values ('8006','1004')

insert into SC(Sno,Cno,Grade) values ('8007','1001',99)
insert into SC(Sno,Cno,Grade) values ('8007','1003',92)
insert into SC(Sno,Cno,Grade) values ('8007','1004',86)
insert into SC(Sno,Cno,Grade) values ('8007','1005',98)
insert into SC(Sno,Cno,Grade) values ('8007','1002',95)

insert into SC(Sno,Cno,Grade) values ('8008','1001',91)
insert into SC(Sno,Cno,Grade) values ('8008','1003',92)
insert into SC(Sno,Cno,Grade) values ('8008','1004',83)
insert into SC(Sno,Cno,Grade) values ('8008','1005',94)
insert into SC(Sno,Cno,Grade) values ('8008','1002',99)

insert into SC(Sno,Cno,Grade) values ('8009','1001',65)
insert into SC(Sno,Cno,Grade) values ('8009','1002',71)
insert into SC(Sno,Cno,Grade) values ('8009','1003',32)
insert into SC(Sno,Cno,Grade) values ('8009','1004',25)


--一 单表
--1查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。

Select Sno ,Sname ,Sage 
From Student
Where ssex='女' and Sage between 19 and 21
Order by Sage DesC
--2查询姓名中第2个字为“明”字的学生学号、性别。
select Sno ,sname,ssex
from Student
where Sname like '%明%'
--3查询 1001课程没有成绩的学生学号、课程号
select sno, cno   
from  sc    
where grade is null and cno='1001'

--4查询JSJ 、SX、WL 系的年龄大于25岁的学生学号,姓名,结果按系及学号排列           X
select Sno,Sname from student

where (Sdept='JSJ' OR Sdept='SX' OR Sdept='WL')and Sage>25

--5按10分制查询学生的sno,cno,10分制成绩 
select sno,cno,grade*0.1 as A
from SC 
  -- (1-10分 为1 ,11-20分为2 ,30-39分为3,。。。90-100为10) 
--6查询 student 表中的学生共分布在那几个系中。(distinct) 
select distinct Sdept from student
--7查询0001号学生1001,1002课程的成绩。
SELECT grade
from sc
where Sno='0001' and Cno in('1001','1002')














---二 统计
--1查询姓名中有“明”字的学生人数。
SELECT count(*) 
from student
where Sname like '%明%'
--2计算‘JSJ’系的平均年龄及最大年龄。
SELECT avg(Sage) AS A,max(Sage) AS B
from student
where sdept='JSJ'
--3查询学生中姓名为张明、赵英的人数                 ⭐X
SELECT count(*) 
from student
where Sname in('张明','赵英' ) 
--4计算每一门课的总分、平均分,最高分、最低分,按平均分由高到低排列                          
SELECT Cno, SUM(grade) as 'sum',avg(grade)as 'avg',max(grade)as 'max',min(grade)as 'min' 
from sc 

GROUP BY Cno  ORDER BY avg(grade) DESC

--5 计算 1001,1002 课程的平均分。               X
SELECT cno,avg(grade)
from sc 
where Cno in ('1001','1002') ORDER BY Cno 
--6 查询平均分大于80分的学生学号及平均分 
select Sno , avg(grade)
from sc GROUP BY Sno HAVING avg(grade) >'80'
--7 统计选修课程超过 2 门的学生学号
SELECT Sno 
from sc GROUP BY Sno HAVING count(*) >'2'
--8 统计有10位成绩大于85分以上的课程号。
SELECT Cno 
FROM sc 
WHERE grade>'85' GROUP BY Cno HAVING count(*)='10'
--9 统计平均分不及格的学生学号
SELECT Sno
from sc GROUP BY Sno HAVING avg(grade) <'60'
--10 统计有大于两门课不及格的学生学号                 X
SELECT Sno 
from sc
WHERE grade<'60' GROUP BY Sno HAVING count(*)>'2'
版权声明:本文为ABV09876543210原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/ABV09876543210/article/details/105565589