SQL入门25题

记录一下入门SQL SERVER的25题

创建数据库

1. 使用SQL语句和SSMS界面两种方式创建符合如下条件的数据库：

   • 数据库的名字为“教师授课管理数据库”。
   • 数据文件的逻辑文件名为“Teachers_dat”，存放在D:\Test目录下(若D：中无此子目录，可先建立此目录，然后再创建数据库)。
   • 文件的初始大小为5MB。
   • 增长方式为自动增长，每次增加lMB。
   • 日志文件的逻辑文件名字为“Teachers_log”，也存放在D:\Test目录下。
   • 日志文件的初始大小为2MB。
   • 日志文件的增长方式为自动增长，每次增加15%。

   CREATE DATABASE 教师授课管理数据库
   ON       
     (NAME=Teachers_dat,
      FILENAME='E:\SQLTest\Teachers_dat.mdf',
      SIZE=5,
      MAXSIZE=30,
      FILEGROWTH=1) 
   LOG ON
     (NAME=Teachers_log,
      FILENAME='E:\SQLTest\Teachers_log.ldf',
      SIZE=2,
      MAXSIZE=12,
      FILEGROWTH=15%)
   

2. 在已创建的“教师授课管理数据库”中创建满足如下条件的三张表：（SQL语句创建表不写列说明）

   教师表（T）

   列名	说明	数据类型	约束
   Tno	教师号	字符串，长度为7	主码
   Tname	姓名	字符串，长度为10	非空
   Tsex	性别	字符串，长度为2	取值为“男” “女”
   Birthday	出生日期	小日期时间型（smalldatetime）	允许空
   Dept	所在部门	字符串，长度为12	允许空
   Sid	身份证号	字符串，长度为18	不重

   课程表（C）

   列名	说明	数据类型	约束
   Cno	课程号	字符串，长度为10	主码
   Cname	课程名	字符串，长度为20	非空
   Credit	学分	短整型（smallint）	大于0且小于20
   Property	课程性质	字符串，长度为10	默认值为“必修”

   授课表（S）

   列名	说明	数据类型	约束
   Tno	教师号	字符串，长度为7	主码，引用教师表的外码
   Cno	课程号	字符串，长度为10	主码，引用课程表的外码
   Hours	授课时数	整数（int）	大于0

   CREATE TABLE 教师表(
       Tno CHAR(7) PRIMARY KEY,
       Tname CHAR(10) NOT NULL,
       Tsex CHAR(2) CHECK (Tsex = '男' OR Tsex = '女'),
       Birthday SMALLDATETIME NULL,
       Dept CHAR(12) NULL,
       Sid CHAR(18) UNIQUE
   );
   
   CREATE TABLE 课程表(
       Cno CHAR(10) PRIMARY KEY,
       Cname CHAR(20) NOT NULL,
       Credit SMALLINT CHECK (Credit > 0 AND Credit < 20),
       Property CHAR(10) DEFAULT '必修'
   );
   
   CREATE TABLE 授课表(
       Tno CHAR(7) NOT NULL REFERENCES 教师表 (Tno),
       Cno CHAR(10) NOT NULL REFERENCES 课程表 (Cno),
       Hours INT CHECK (Hours > 0)
       -- PRIMARY KEY(Tno,Cno),
    -- FOREIGN KEY(Tno) REFERENCES 教师表(Tno),
    -- FOREIGN KEY(Cno) REFERENCES 课程表(Cno)
   
   );
   ALTER TABLE 授课表
   ADD CONSTRAINT PK_NAME PRIMARY KEY (Tno, Cno);
   

3. 在授课表中添加一个授课类别列，列名为Type，类型为nchar(4)

   ALTER TABLE 授课表
   ADD Type NCHAR(4);
   

4. 删除教师表、课程表、授课表三张表。（使用SQL语句实现）

   DROP TABLE 授课表;
   DROP TABLE 教师表;
   DROP TABLE 课程表;
   

数据基本查询

1. 查询已授课程的课程号，要求显示的课程号不重复。（消除取值重复的行）

   SELECT DISTINCT Cno
   FROM 授课表;
   

2. 查询全体老师的姓名及其年龄，查询结果的列名为姓名、年龄。（提示：使用系统函数getdate()获取当前日期，使用系统函数year()获取日期中的年份）（目标列表达式、列别名）

   SELECT Tname AS 姓名, YEAR(GETDATE()) - YEAR(Birthday) AS 年龄
   FROM 教师表;
   

3. 查询全体教师的姓名及其年龄，并按教师的年龄降序排列。（排序）

   SELECT Tname AS 姓名, YEAR(GETDATE()) - YEAR(Birthday) AS 年龄
   FROM 教师表
   ORDER BY 年龄 DESC;
   

4. 检索名字中第二个字是“力”或“历”的教师的详细信息。（通配符）

   SELECT *
   FROM 教师表
   WHERE Tname LIKE '_力%' OR Tname LIKE '_历%';
   -- select * from 教师表 where Tname like '_[力,历]%'
   

5. 查询未录入出生日期的教师姓名。（空值）

   SELECT Tname
   FROM 教师表
   WHERE Birthday IS NULL;
   

6. 查询教师总人数。（聚集函数）

   SELECT COUNT(Tno) AS 总人数
   FROM 教师表;
   

7. 汇总各位授课教师的教师号及总学时。（分组统计）

   SELECT 教师表.Tno, SUM(授课表.Hours) AS 总学时
   FROM 教师表
   GROUP BY 教师表.Tno;
   

8. 统计每个部门男女教师的人数。（分组统计）

   select Dept 系,Tsex 性别,count(distinct Tno) 人数 from 教师表 group by Dept,Tsex
   -- 或者
   select distinct 教师表.Dept 系,男教师人数,女教师人数 from (select 教师表.Dept,count(Tno) as 男教师人数 from 教师表 where Tsex='男' group by Dept)as 男教师 right join 教师表 on 男教师.Dept=教师表.Dept left join (select 教师表.Dept,count(Tno) as 女教师人数 from 教师表 where Tsex='女' group by Dept )as 女教师 on 教师表.Dept=女教师.Dept
   
   

9. 计算所教授的每门课程均在40学时以上的教师的教师号和平均学时。（分组筛选）

   SELECT 授课表.Tno, AVG(授课表.Hours) AS 平均学时
   FROM 授课表
   WHERE 授课表.Tno NOT IN (
       SELECT DISTINCT 授课表.Tno 
   	FROM 授课表 
   	WHERE 授课表.Hours <= 40)
   GROUP BY 授课表.Tno;
   -- select Tno,avg(Hours) AVGX from 授课表 group by Tno	having  min(Hours) >= 40
   

10. 统计女教师各个年龄的人数，求出超过1人的年龄和该年龄的人数。（条件筛选、分组筛选）

    SELECT YEAR(GETDATE()) - YEAR(Birthday) AS 年龄, COUNT(YEAR(Birthday)) AS 人数
    FROM 教师表 
    WHERE Tsex = '女'
    GROUP BY YEAR(Birthday)
    HAVING COUNT(YEAR(Birthday)) > 1;
    

    select  year(getdate())-year(Birthday) 年龄,count(Tno) 人数
       from  教师表  where  Tsex ='女' group by year(getdate())-year(Birthday) having count(Tno)>1
    

数据高级查询与更新

1. 查询教授“英语”课的教师的姓名。（嵌套或连接）

   SELECT Tname FROM 教师表
   WHERE Tno IN (
   	SELECT Tno FROM 授课表
   	WHERE Cno IN (
   		SELECT Cno FROM 课程表
   		WHERE Cname = '英语'
   		) 
   	);
   

2. 查询教授”0001”课程比”0002”课程学时高的所有教师的教师号。（嵌套）

   SELECT S1.Tno
   FROM 授课表 AS S1
   WHERE Cno = '0001' AND Hours > (
   	SELECT S2.Hours FROM 授课表 AS S2
   	WHERE S2.Tno = S1.Tno AND Cno = '0002'
   	);
   

   select a.Tno from (select Tno,Hours from 授课表 where Cno='0001') a, (select Tno,Hours from 授课表 where Cno='0002') b where a.Hours>b.Hours and a.Tno=b.Tno
   

3. 查询教师的教师号、平均授课学时及根据平均授课学时排的名次。（嵌套）

   SELECT Tno, AVG(Hours) AS 平均学时, RANK() OVER (
   	ORDER BY AVG(Hours) DESC
   	) AS 排名
   FROM 授课表
   GROUP BY Tno;
   

4. 查询教授过所有课程的教师的教师号。（EXISTS谓词）

   即：查询没有课程没教过的教师

   SELECT Tno
   FROM 教师表
   WHERE NOT EXISTS(
   	SELECT *
   	FROM 课程表
   	WHERE NOT EXISTS(
   		SELECT *
   		FROM 授课表
   		WHERE 授课表.Tno = 教师表.Tno AND 授课表.Cno = 课程表.Cno
   		)
   	);
   

5. 查询至少教授了 “2004003” 号教师教授的全部课程的教师的教师号。（EXISTS谓词）

   即：不存在这样的课程y，教授了 “2004003” 号教师教了，而教师x没教

   SELECT DISTINCT S1.Tno
   FROM 授课表 AS S1
   WHERE NOT EXISTS (
   	SELECT *
   	FROM 授课表 AS S2
   	WHERE S2.Tno = '2004003' AND NOT EXISTS (
   		SELECT *
   		FROM 授课表 AS S3
   		WHERE S3.Cno = S2.Cno AND S3.Tno = S1.Tno 
   		)
   	);
   

6. 查询既上“英语”课又上“经济学”课的教师的教师号。（集合）

   SELECT Tno
   FROM 课程表, 授课表
   WHERE 课程表.Cno = 授课表.Cno AND 课程表.Cname = '英语'
   INTERSECT
   SELECT Tno
   FROM 课程表, 授课表
   WHERE 课程表.Cno = 授课表.Cno AND 课程表.Cname = '经济学';
   

   并集（或者）：UNION；差集：EXCEPT；

   select Tno from 授课表 where Cno in(select Cno from 课程表 where Cname='英语')
   intersect
   select Tno from 授课表 where Cno in(select Cno from 课程表 where Cname='经济学')
   

7. 在教师表中插入2条记录，分别是张元、李光，其余属性值自拟。（添加）

   INSERT INTO 教师表(
   	Tno,
   	Tname,
   	Tsex,
   	Birthday,
   	Dept,
   	Sid
   	)
   VALUES(
   	'2001003',
   	'张元',
   	'男',
   	'1991-05-03',
   	'计算机',
   	'244501198002032324'
   	);
   	
   INSERT INTO 教师表
   VALUES(
   	'2005004',
   	'李光',
   	'男',
   	'1991-04-03',
   	'计算机',
   	'756401198002032324'
   	);
   
   

8. 删除教师“刘元”的教师信息。（删除）

   DELETE FROM 授课表
   WHERE Tno = (
   	SELECT Tno
   	FROM 教师表
   	WHERE Tname = '刘元'
   	);
   DELETE FROM 教师表
   WHERE Tname = '刘元';
   

9. 如果他曾授过课，则相应的授课信息是否还存在？（删除）

   若想删除刘元的教师信息，要先删除和教师表有引用联系的授课表中刘元的授课信息，
   delete from 授课表 from 教师表 join 授课表 on 教师表.Tno=授课表.Tno where Tname='刘元'
   或
   delete from 授课表 where Tno in (select Tno from 教师表 where Tname='刘元')
   然后再删除刘元的教师信息。
   delete from 教师表 where Tname='刘元'

10. 将计算机系的系名改为计算机科学系。（更新）

    UPDATE 教师表
    SET Dept = '计算机科学'
    WHERE Dept = '计算机';