笔记整理2

--A1 创建一个名字为testdb的数据库，该数据库包含一个数据文件和一个日志文件，逻辑文件名为testdb_data,磁盘文件名为testdb_data.mdf，
--文件初始容量为5MB，最大容量为15MB，文件增长量为20%，而事务日志文件的逻辑文件名testdb_log，磁盘文件名为testdb.ldf，文件初始容量为5MB，
--最大容量为10MB，文件递增量为1MB（数据库创建在C:\server目录下）。
CREATE DATABASE testdb --DATABASE 数据库--
ON PRIMARY
(
	NAME=testdb_data,
	FILENAME='E:\SJD\testdb_data.mdf',
	SIZE=5MB,
	MAXSIZE=15MB,
	FILEGROWTH=20%
)
LOG ON 
(
	NAME=testdb_log,
	FILENAME='E:\SJD\testdb_data.idf',
	SIZE=5MB,
	MAXSIZE=15MB,
	FILEGROWTH=1MB
)
 --A2用T-SQL语句创建一个如下所示的数据表，表名为stu，建在名为testdb的数据库中。
USE testdb
GO
CREATE TABLE stu    --CREATE 创建 ALTER 修改 DEKETE 删除 --
(
	Sno	CHAR(10)	NOT NULL,
	Sname	VARCHAR(30)	NOT NULL,
	Ssex	CHAR(20)	NOT NULL
)
--A3将studentdb数据库里的表teacher、professional、department导入到testdb数据库中

 
--A4查询年纪最大的3位副教授的信息、
select top(3) Tno,Tname 
from Teacher
order by Tbirthday
--A5各类职称的教师人数的统计
select Ttitle ,count(*) as '人数'
from Teacher
group by Ttitle
--A6查询姓“王”且全名为三个汉字的教师的工号和姓名；
select Tname
from Teacher 
where Tname like '王%'and LEN(tname)=3
--A7查询选修“现代职业人”的学生的学号，姓名，课程名；
SELECT student.Sno 学号, sname 姓名 , cname 课程名
FROM Result  INNER JOIN Course
	ON result.cno=course.cno
		INNER  JOIN student
		ON student.sno=result.sno
--A8查询与工号“0201”相同职称的教师的工号和姓名。
SELECT Tno 工号,Tname 姓名
FROM Teacher
WHERE Ttitle=(SELECT Ttitle
	FROM Teacher
	WHERE Tno='0201') AND Tno='0201'  
--A9创建一个带参数的存储过程kcgc，要求根据输入的课程名称（如“3dsmax”），可以查询选修了该课程的学生学号和姓名。
create procedure kcgc
@DEPT NCHAR(10)
AS
SELECT SNO,SNAME,DEPT
FROM 数据表
where DEPT=@DEPT


exec kcgc @DEPT='3dsmax'


--B3 利用UPDATE语句,将student表中入学成绩提高15分。（7.0分）
UPDATE student
SET sscore=sscore+15


--B4查询student表中姓陈或姓王的学生信息
select * from student where student.sname like '王_'

--B5用IN子查询查出入学成绩500分以上的学生的课程成绩，显示课程号、学号和成绩


SELECT sno 学号,cno 课程号,grade 成绩
FROM choice
WHERE sno IN (SELECT sno
				FROM student
				WHERE sscore>=500)


--B6使用T-SQL语句创建视图v_07，要求查询07级学生的学号，姓名和入学分数



--B7查询年龄在30岁至40岁（包含30岁和40岁）之间的男教师的工号、姓名和年龄
SELECT tno,tname,tbirthday,
(YEAR(GETDATE())-YEAR(tbirthday))
FROM teacher
WHERE tsex='男' AND (YEAR(GETDATE())-YEAR(tbirthday))>=30 AND (YEAR(GETDATE())-YEAR(tbirthday))<=40


--B8统计各课程的学生的平均成绩（结果显示为课程名称、平均成绩）
SELECT AVG(grade) 平均分,cname 课程名称
FROM choice INNER JOIN course
ON course.cno=choice.cno
GROUP BY cname




--B9用IN子查询查出入学成绩500分以上的学生的课程成绩，显示课程号、学号和成绩

SELECT sno 学号,cno 课程号,grade 成绩
FROM choice
WHERE sno IN (SELECT sno
FROM student
WHERE sscore>=500)

SELECT student.sno 学号,cno 课程号,grade 成绩
FROM choice INNER JOIN student
ON choice.sno=student.sno
WHERE sscore>=500



--B10为student数据库中的student表创建一个名为update_sname的UPDATE触发器
--，该触发器的功能是禁止更新student表中的sname字段的内容。如修改“张劲”的学生姓名为“zhangjin”，触发成功后显示“不能修改学生的姓名！”。（创建触发器代码执行成功后截取界面；触发该触发器执行成功后的代码与结
-- update果界面截取）
CREATE TRIGGER  update_sname
ON student		--对哪张表起作用
FOR UPDATE      --触发器条件 ，INSERT  ，DELECT
AS 
	IF UPDATE(sname)
		BEGIN
			PRINT'不能修改学生姓名！'
			ROLLBACK TRAN   --回滚事务
		END
--测试代码，验证触发器是否有效
UPDATE student
SET sname='asdasd'
WHERE sno='0601011101'




--D1、创建一个名字为ProductSales的数据库，该数据库包含一个数据文件和一个日志文件，
--数据文件的逻辑文件名为ProductSales_data,磁盘文件名为ProductSales_data.mdf，
--文件初始容量为5MB，最大容量为15MB，文件增长量为20%；而事务日志文件的逻辑文件名ProductSales_log，
--磁盘文件名为ProductSales_log.ldf，文件初始容量为5MB，
--最大容量为10MB，文件递增量为1MB（数据库创建在C盘根目录下）。
--（5.0分）
CREATE DATABASE ProductSales --DATABASE 数据库--
ON PRIMARY
(
	NAME=ProductSales_data,
	FILENAME='E:\SJD\ProductSales_data.mdf',
	SIZE=5MB,
	MAXSIZE=15MB,
	FILEGROWTH=20%
)
LOG ON 
(
	NAME=ProductSales_log,
	FILENAME='E:\SJD\ProductSales_data.idf',
	SIZE=5MB
)
--D2.
--使用T-SQL语句操作创建产品表，表名为Product，设置ProductID字段为主键。
--ProductID	ProductName	Price
--1	HP1200 打印机	2000
--2	LX360 兼容机	4800
--3	IBM350 笔记本	11000
--4	IBM360 笔记本	12000
--（10.0分）
USE ProductSales
GO
CREATE TABLE Product    --CREATE 创建 ALTER 修改 DEKETE 删除 --
(
	ProductID    INT PRIMARY KEY ,
	ProductName  VARCHAR(50),
	Price        INT
)
--向表中加入数数据
INSERT INTO Product
VALUES
(1,'HP1200打印机','2000'),
(2,'LX360兼容机','4800'),
(3,'IBM350笔记本','11000'),
(4,'IBM360笔记本','12000')

--D3.
--使用操作创建销售情况表，表名为Sales，设置ProductID字段与产品表中ProductID外键关联。
--ProductID	ClientName	ProductNumber	SalesPrice
--2	北大青鸟	10	4500
--1	北大青鸟	25	1800
--3	联想集团	10	11000
--2	联想集团	30	4500
--1	联想集团	20	1800
--3	北大方正	40	10000
--3	诺基亚	20	10500
--（10.0分）
USE ProductSales
GO
CREATE TABLE Sales    --CREATE 创建 ALTER 修改 DEKETE 删除 --
(
	ProductID INT foreign key (ProductID) references Product(ProductID) ,
	ClientName VARCHAR(50),
	ProductNumber INT,
	SalesPrice INT
)
INSERT INTO Sales
VALUES
(2,'北大青鸟','10','4500'),
(1,'北大青鸟','25','1800'),
(3,'联想集团','10','11000'),
(2,'联想集团','30','4500'),
(1,'联想集团','20','1800'),
(3,'北大方正','40','10000'),
(3,'诺基亚','20','10500')
--D4
--查询出单笔售数量大于15 的客户名称、购买数量、销售价格信息；（7.0分）
USE ProductSales
GO
SELECT ClientName 客户名称,ProductNumber 购买数量,SalesPrice 销售价格
FROM Sales
WHERE ProductNumber>15

--D5.
--查询出所有商品的全部销售金额；

USE ProductSales
GO
SELECT SUM(ProductNumber*SalesPrice)
FROM Sales

--D6. 
--查询客户姓名、对应客户的销售总金额；
SELECT ClientName 客户姓名,SUM(ProductNumber*SalesPrice) 销售总金额 
FROM Sales
GROUP BY ClientName
--D7. 
--查询购买过商品“IBM 350 笔记本”的商品名称、客户名称、购买数量；
SELECT  Product.ProductName,  Sales.ClientName,  Sales.ProductNumber
FROM    Product INNER JOIN
        Sales ON  Product.ProductID =  Sales.ProductID
WHERE  Product.ProductName = 'IBM350笔记本'
--D8.
--将客户“北大青鸟”名称更新为“北大青鸟APTECH”。

--UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
UPDATE Sales 
SET ClientName='北大青鸟APTECH'
WHERE  ClientName='北大青鸟'

--D9. 
--为ProductSales数据库中的产品表创建一个名为update_pno的UPDATE触发器，该触发器的作用是禁止更新产品表中的“ProductName”字段的内容。并用UPDATE语句修改产品表中第一条记录为：
--1 	HP1500 打印机	2000
--要求显示“不能修改产品名称”的警告信息。
--（10.0分）
--创建触发器
CREATE TRIGGER update_pno
ON product
FOR UPDATE
AS
	IF UPDATE(ProductName)
		BEGIN
			PRINT '不能修改产品名称'
			ROLLBACK TRANSACTION
		END
--测试触发器
UPDATE product
SET ProductName='HP1000打印机'
WHERE ProductID='1'

--E1
--创建一个名字为Factory的数据库，该数据库包含一个数据文件和一个日志文件，数据文件的逻辑文件名为Factory_data,磁盘文件名为Factory_data.mdf
--文件初始容量为5MB，最大容量为15MB，文件增长量为20%；而事务日志文件的逻辑文件名Factory_log，磁盘文件名为Factory_log.ldf，
--文件初始容量为5MB，最大容量为10MB，文件递增量为1MB（数据库创建在C盘根目录下）。
CREATE DATABASE Factory --DATABASE 数据库--
ON PRIMARY
(
	NAME=Factory_data,
	FILENAME='E:\SJD\Factory_data.mdf',
	SIZE=5MB,
	MAXSIZE=15MB,
	FILEGROWTH=20%
)
LOG ON 
(
	NAME=ProductSales_log,
	FILENAME='E:\SJD\Factory_data.idf',
	SIZE=5MB,
	MAXSIZE=10MB,
	FILEGROWTH=1MB
)
--E2.
--Wno表示职工工号；Wname表示职工姓名；Wsex表示职工性别；Wbirthday表示职工生日；DepartNo表示部门号；DName表示部门名称。
--使用T-SQL语句操作创建员工表，表名为Worker，设置Wno字段为主键，并为Wsex字段创建检查约束，让字段的内容非男即女。

USE Factory
GO
CREATE TABLE Product    --CREATE 创建 ALTER 修改 DEKETE 删除 --
(
	Wname VARCHAR(20)  PRIMARY KEY ,
	Wsex  VARCHAR(4) CHECK(Wsex='男'or Wsex='女'),
	Wbirthday DATETIME,
	DepartNo INT 
)
INSERT INTO Product
VALUES
('孙华','男','1952-1-3','1'),
('孙天奇','女','1965-3-10','2'),
('陈明','男	','1945-5-8','2'),
('李华','男','1956-8-7','3'),
('余慧','男','1980-12-4','3')

--E3
--使用操作创建部门表，表名为Depart。设置Dno字段为主键。
USE Factory
GO
CREATE TABLE Depart    --CREATE 创建 ALTER 修改 DEKETE 删除 --
(
	Dno INT  PRIMARY KEY ,
	DName VARCHAR(20)
)

INSERT INTO Depart
VALUES
('1','财务处'),
('2','人事处'),
('3','市场部')
--E4查询在1970年前出生的职工工号和姓名。
SELECT DepartNo 职工工号,Wname 姓名
FROM Product
WHERE YEAR(Wbirthday)>1970
--E5查询姓“孙”的职工的姓名和部门名称。
SELECT   Depart.DName,  Product.Wname
FROM     Depart INNER JOIN
                 Product ON  Depart.Dno =  Product.DepartNo
WHERE   Product.Wname LIKE '孙%'
--E6查询年龄最大的3位男职工的工号和姓名, 并按照工号进行升序排序。
SELECT   TOP (50) PERCENT Depart.Dno, Product.Wname, Depart.DName, Product.Wbirthday
FROM      Depart INNER JOIN
                Product ON Depart.Dno = Product.DepartNo
ORDER BY Product.Wbirthday   --ORDER 升序    ORDER BY dbo.Product.Wbirthday DESC 降序
--E7查询和“孙天奇”在同一部门的职工的工号和姓名
SELECT    Depart.Dno, Product.DepartNo, Product.Wname, Depart.DName
FROM      Depart INNER JOIN
          Product ON Depart.Dno = Product.DepartNo
WHERE DepartNo=2

--E8删除所有男职工的信息
DELETE FROM Product WHERE Wsex='男'		-- DELETE FROM  表名  WHERE 条件 

--E9为Factory数据库中的职工表创建一个名为update_name的UPDATE触发器，该触发器的作用是禁止更新职工表中的“姓名”字段的内容。
--并用UPDATE语句修改职工表中工号为‘2’的职工姓名为“陈晨”，要求显示“不能修改职工姓名”的警告信息。
--创建触发器
CREATE TRIGGER update_name
ON product
FOR UPDATE
AS
	IF UPDATE(Wname)
		BEGIN
			PRINT '不能修改产员工名称'
			ROLLBACK TRANSACTION
		END
--测试触发器
UPDATE Product
SET Wname='陈晨'
WHERE DepartNo='3'


--F1创建一个名字为MyQQ的数据库，该数据库包含一个数据文件和一个日志文件，数据文件的逻辑文件名为MyQQ_data,磁盘文件名为MyQQ_data.mdf，
--文件初始容量为5MB，最大容量为15MB，文件增长量为20%；而事务日志文件的逻辑文件名MyQQ_log，磁盘文件名为MyQQ_log.ldf，文件初始容量为5MB，最大容量为10MB，
--文件递增量为1MB（数据库创建在C盘根目录下）。
CREATE DATABASE MyQQ
ON  PRIMARY
(NAME=MyQQ_data,
   FILENAME='E:\SJD\MyQQ_data.mdf',
   SIZE=5MB,
   MAXSIZE=15MB,
   FILEGROWTH=20%)
 LOG ON
  (NAME=MyQQ_log,
   FILENAME='E:\SJD\MyQQ_log.ldf',
   SIZE=5MB,
   MAXSIZE=10MB,
FILEGROWTH=1MB)
--F2 LoginPWD表示用户密码；NickName表示用户名；Sex表示用户性别；Star表示星座；BloodTypeId表示血型编号；BloodType表示血型。
--使用T-SQL语句操作创建用户信息表，表名为Users，将Id列设置为主键。使用操作创建血型信息表，表名为Blood，将Id列设置为主键。
CREATE TABLE Users
(
Id          int constraint pk_users Primary key,
LoginPWD    char(6) not null,
NickName    char(20) ,
Sex         char(2),
Star        char(6),
BloodTypeId int not null, 
)
INSERT INTO Users
(Id,LoginPWD,NickName,Sex,Star,BloodTypeId)
VALUES
(1,'0000','豆豆','男','白羊座',1)
INSERT INTO Users
(Id,LoginPWD,NickName,Sex,Star,BloodTypeId)
VALUES
(2,'00A0','小强','男','双子座',4)
INSERT INTO Users
(Id,LoginPWD,NickName,Sex,Star,BloodTypeId)
VALUES
(3,'0000','静静','女','双子座',2)
INSERT INTO Users
(Id,LoginPWD,NickName,Sex,Star,BloodTypeId)
VALUES
(4,'00B0','.NET','男','巨蟹座',3)
INSERT INTO Users
(Id,LoginPWD,NickName,Sex,Star,BloodTypeId)
VALUES
(5,'0CC0','bobo','女','狮子座',1)


--F3 使用操作创建血型信息表，表名为Blood
CREATE TABLE Blood
(
id int,
BloodType char(4),
)
insert into Blood (id,BloodType)
values
(1,'A型')
insert into Blood (id,BloodType)
values
(1,'B型')
insert into Blood (id,BloodType)
values
(1,'O型')
insert into Blood (id,BloodType)
values
(1,'AB型')

--F4  查询出血型为“A 型”的用户姓名、性别。
select Nickname 用户姓名 ,Sex 性别 
from users
 where BloodTypeId=(select id from blood where BloodType='A型' )
--F5  查询用户的密码中包含字符“A”的用户姓名、性别。
SELECT NickName,Sex
FROM Users
WHERE LoginPWD LIKE '%A%'
--F6查询出血型为“A 型”并且星座为“狮子座”的女用户姓名、密码
SELECT NickName,LoginPWD
FROM Users INNER JOIN Blood
ON Users.Id=Blood.id
WHERE Blood.BloodType='A型' AND Users.Star='狮子座' AND Sex='女'
--F7统计各星座的用户人数。
SELECT Star 星座,COUNT(*) 人数
FROM Users
GROUP BY Star
--F8将用户“.NET”的名字更新为“天外飞仙”。
update users 
set nickname='天外飞仙'
where nickname='.NET'
--F9为MyQQ数据库编写一个带参数的存储过程proc_Star，
--它可以查询和某用户同一星座的用户的姓名和性别，并调用该存储过程验证其功能
CREATE PROC proc_Star
@star VARCHAR(50)
AS
		SELECT Users.NickName,Sex   
		FROM    dbo.Users INNER JOIN
                dbo.Blood ON dbo.Users.Id = dbo.Blood.id
		WHERE Star=@star
EXEC proc_Star '双子座'

--G1 
--创建了一个名为Department数据库，该数据库的主数据文件逻辑名称为“Department_data”，物理文件名为“Department.mdf”，路径为c:\sql,初始大小为10MB，
--最大尺寸为100M，增长速度为10%；数据库的日志文件逻辑名称为“Department_log”，物理文件名为“Department.ldf”，路径为c:\sql,初始大小为5MB，最大尺寸为10MB，
--增长速度为1MB。
CREATE DATABASE Department
ON  PRIMARY
(
NAME=Department_data,
   FILENAME='E:\SJD\Department_data.mdf',
   SIZE=10MB,
   MAXSIZE=100MB,
   FILEGROWTH=10%
   )
 LOG ON
(
  NAME=Department_log,
   FILENAME='E:\SJD\Department_log.ldf',
   SIZE=5MB,
   MAXSIZE=10MB,
FILEGROWTH=1MB
)
--G2 在Department数据库中，使用操作创建教师信息表,表名：teacher。
use department 
go
CREATE TABLE Teacher
(
	Teacher_id int not null primary key,
	Name  char(20) not null,
	Sex char(2) not null,
	age int not null,
	nation varchar(50),
	title varchar(20)
)
--在Department数据库中，使用操作创建课程表,表名：course
use department 
go
Create table course
(
 Course_id char(7),
 Course_name varchar(50)
)
--在Department数据库中,使用操作创建任课表,表名：teach
use department 
go
Create table teach
(
 id char(7) NOT NULL ,
 Teacher_id char(7) NOT NULL,
 Course_id char(7) NOT NULL,
 Numberoflesson int NULL
)
--G3在Department数据库中,使用T-SQL语句将下列课程信息添加到表
insert into course
(course_id, course_name)
values
('100001','SQL Server数据库')
insert into course
(course_id, course_name)
values
('100002','数据结构')
insert into course
(course_id, course_name)
values
('100003',' VB程序设计')
--在Department数据库中,使用T-SQL语句将下列信息添加到表teach。
insert into teach
(id,teacher_id,course_id,numberoflesson)
values
('50001','1010105', '100001',76)
insert into teach
(id,teacher_id,course_id,numberoflesson)
values
('50002','1010105', '100001',98)
insert into teach
(id,teacher_id,course_id,numberoflesson)
values
('50003','1010107', '100003',56)
insert into teach
(id,teacher_id,course_id,numberoflesson)
values
('50004','1010108', '100001',76)
insert into teach
(id,teacher_id,course_id,numberoflesson)
values
('50005','1010109', '100002',108)
--使用T-SQL语句将下列教师信息添加到表teacher。

insert into teacher
(Teacher_id, Name,sex,age,nation,title)
values
(1010105,'韦海忠', '男',55 ,'汉族', '教授')
insert into teacher
(Teacher_id, Name,sex,age,nation,title)
values
(1010106,'赵国富', '男',46,'汉族', '副教授')
insert into teacher
(Teacher_id, Name,sex,age,nation,title)
values
(1010107,'金啸胜', '男',31,'汉族', '讲师')
insert into teacher
(Teacher_id, Name,sex,age,nation,title)
values
(1010108,'郭伯法', '男',34,'汉族', '副教授')
insert into teacher
(Teacher_id, Name,sex,age,nation,title)
values
(1010109,'郭浩', '女',26,'苗族', '助教')
--G4 查询教师的平均年龄。
SELECT AVG(Age) 平均年龄            
FROM Teacher
--G5 查询姓“郭”的教师的信息。
SELECT *
FROM teacher
WHERE Name LIKE '郭%'
--G6 查询职称是副教授的人数。
Select title 职称, count(*)人数
From teacher
Where  title='副教授'
Group By  title
--G7 查询教师编号为“1010105”的教师上课的课程编号、课时数
SELECT Teacher_id 课程号,NumberofLesson 课时数                
FROM teach
WHERE id='1010105' 

--G8 查询所上课程的课时数大于90的所有教师的信息，包括编号、姓名、职称。
SELECT teacher.Teacher_id 编号,Name 姓名,title 职称            
FROM teach INNER JOIN teacher
ON teach.id=teacher.Teacher_id
WHERE Numberoflesson>90  
--G9 创建一个带参数的存储过程proc_keshi：计算某教师授课总课时，并将值返回的存储过程以及执行代码。执行存储过程：计算“郭浩”的总课时。
USE department
GO
IF EXISTS (SELECT* FROM SYSOBJECTS WHERE name='proc_keshi')
  DROP PROC proc_keshi
  GO
CREATE PROC proc_keshi
@Name VARCHAR(20)
AS
SELECT sum(Numberoflesson) 总课时
FROM Teacher INNER JOIN Teach
  ON Teacher.Teacher_id=Teach.Teacher_id
  WHERE Name=@Name
GO
EXEC proc_keshi  '郭浩'









--H1 
--创建一个名为teacher数据库，该数据库的主数据文件逻辑名称为“teacher_data”，物理文件名为“teacher.mdf”，
--路径为c:\sql,初始大小为5MB，最大尺寸为100M，增长速度为10%；数据库的日志文件逻辑名称为“teacher_log”，
--物理文件名为“teacher.ldf”，路径为c:\sql,初始大小为1MB，最大尺寸为5MB，增长速度为1MB。
CREATE DATABASE teacher --DATABASE 数据库--
ON PRIMARY
(
	NAME=teacher_data,
	FILENAME='E:\SJD\teacher_data.mdf',
	SIZE=5MB,
	MAXSIZE=100MB,
	FILEGROWTH=10%
)
LOG ON 
(
	NAME=teacher_log,
	FILENAME='E:\SJD\teacher_data.idf',
	SIZE=1MB,
	MAXSIZE=5MB,
	FILEGROWTH=1MB
)
--H2 使用操作在teacher数据库中创建教师表,表名：teacher。
CREATE TABLE teacher
(
teacher_id	char(8)		NOT  NULL,		--	设为主键	职工号
Dept_no		char(2)		NOT  NULL,			--	系号
tname		varchar(16)	NULL,				--姓名
salary		int			NULL,					--工资
course		varchar(20)	NULL,				--主讲课程
age			int			NULL						--年龄
)
--使用操作创建系部表，表名为department
CREATE TABLE department
(
	id	char(2)	NOT  NULL,			--系部编号
	Dept_name	char(12)	NULL	--系部名称
)


--H3  使用T-SQL语句创建教师表中的数据，数据如下：

('11020001','1','肖海','3408','数据结构','45'),
('11020002','2','王岩盐','4390','数据结构','36'),
('11020003','1','刘星魂','2450','Ｃ语言','43'),
('11020004','3','钱青名','2987','数据库','37'),
('11020005','4','呼延军','3220','编译原理','29'),
('11020006','4','欧阳秀','3345','编译原理','55')
--使用T-SQL语句创建系部表中的数据，数据如下
INSERT INTO department
VALUES
('1','计算机系'),
('2','通信系'),
('3','信息管理系'),
('4','数学系')
	

--H4 查询教授“编译原理”这门课的教师人数。
SELECT COUNT(*)教师人数
FROM teacher
WHERE course='编译原理'
--H5、查询比“欧阳秀”工资高的教师的姓名
SELECT tname 姓名
FROM teacher
WHERE  salary>(SELECT salary 
					FROM teacher 
						WHERE tname='欧阳秀')

--H6、查各个系教师的人数
SELECT COUNT(*) 人数
FROM department,teacher
WHERE department.id=teacher.Dept_no  
GROUP BY Dept_name

--H7、查询教师40-49这个年龄组的教师人数
SELECT COUNT(*) 教师人数
FROM teacher
WHERE age BETWEEN 40 AND 50
--H8、查询计算机系的所有教师信息。
SELECT department.id 系部编号,teacher.teacher_id 教师编号,tname 姓名,salary 工资,course 课程,age 年龄
FROM department INNER JOIN teacher
  ON department.id=teacher.Dept_no
WHERE Dept_name='计算机系'
--H9、为teacher数据库的teacher表中创建一个INSERT 触发器T_insert，当向teacher表
--中插入数据时，要求学号必须以“1102”开头，否则取消插入操作，并提示错误
--“输入的教师工号错误，请确认后重新录入！”的警告信息。
CREATE TRIGGER T_insert
ON teacher
FOR INSERT
AS
DECLARE @TNO char(8)
SELECT @TNO=teacher_id
FROM INSERTED
PRINT @TNO

  IF  LEFT(@TNO,4)!='1102'
        BEGIN
		   PRINT'输入的教师工号错误，请确认后重新录入！'
		   ROLLBACK TRAN
		END
INSERT INTO teacher
(teacher_id,Dept_no,tname)
VALUES
('008','03','BHJ')


--I1
--创建一个名字为“学生选课数据库”的数据库，该数据库包含一个数据文件和一个日志文件，数据文件的逻辑文件名为xuanke_data,磁盘文件名为xuanke_data.mdf，
--文件初始容量为5MB，最大容量为15MB，文件增长量为20%；而事务日志文件的逻辑文件名xuanke_log，磁盘文件名为xuanke_log.ldf，文件初始容量为5MB，最大容量为10MB，
--文件递增量为1MB（数据库创建在C盘根目录下）。
--（10.0分）
CREATE DATABASE 学生选课数据库
ON PRIMARY
(
       NAME=xuanke_data,
       FILENAME='E:\SJD\xuanke_data.mdf',
      SIZE=5MB,
      MAXSIZE=15MB,
      FILEGROWTH=20%
)
LOG ON
(
      NAME=xuanke_log,
      FILENAME='E:\SJD\xuanke_log.ldf',
      SIZE=5MB,
      MAXSIZE=10MB,
      FILEGROWTH=1MB
)
--I2
--使用T-SQL语句操作创建学生表，表名为“学生”
--设置“学号”字段为主键，字段类型为nchar,字段长度为10；
--设置“姓名”字段，字段类型为nchar,字段长度为10；
--设置“性别”字段，字段类型为nchar,字段长度为2，并为“性别”字段创建检查约束，让字段的内容非男即女；设置“出生日期”字段，字段类型为datetime。
--使用T-SQL语句操作为学生表插入如下数据：
CREATE TABLE 学生
(
   学号        nchar(10)          NOT NULL PRIMARY KEY ,
   姓名        nchar(10)          NOT NULL,
   性别        nchar(2)           CHECK(性别='男' or 性别='女'),
   出生日期    datetime            NULL
)
insert into 学生 values('090101','汪涵','男','1990-1-1')
insert into 学生 values('090102','刘洁','女','1991-3-4')
insert into 学生 values('090103','刘莉莉','女','1992-5-6')
--I3
--使用T-SQL语句操作创建课程表，表名为“课程”，
--设置“课程编号”字段为主键，字段类型为nchar,字段长度为10；
--设置“课程名”字段，字段类型为nchar,字段长度为10；
--设置“学分”字段，字段类型为real；设置“先行课程号”字段，字段类型为nchar,字段长度为10。
--使用T-SQL语句操作为学生表插入如下数据：

CREATE TABLE 课程
(
   课程编号       nchar(10)           NOT NULL PRIMARY KEY,
   课程名         nchar(10)           NOT NULL,
   学分           real                NOT NULL,
   先行课程号     nchar(10)           NULL
)

insert into 课程 values('C001','C语言','2','NULL')
insert into 课程 values('C002','数据结构','2','C001')
insert into 课程 values('C003','数据库原理','2','NULL')

--I4
--使用T-SQL语句创建“选课”表，表名为“选课”，并且设置“学号”和“课程编号”字段为双主键。
CREATE TABLE 选课
(
       学号               nchar(6)             NOT NULL,
       课程编号           nchar(4)             NOT NULL,
       分数               int                  NOT NULL
)

insert into 选课 values('090101','C001','78')
insert into 选课 values('090101','C002','87')
insert into 选课 values('090102','C002','97')
insert into 选课 values('090102','C001','79')
insert into 选课 values('090102','C003','56')
insert into 选课 values('090103','C001','55')

ALTER TABLE 选课 ADD PRIMARY KEY(学号,课程编号)

--I5 查询各课程的平均分
SELECT AVG(分数)
FROM 选课
GROUP BY 课程编号

--I6查询不及格学生的学号、姓名、课程名和分数
SELECT 选课.学号,姓名,课程名,分数
FROM  学生 INNER JOIN 选课
          ON  学生.学号=选课.学号
           INNER JOIN 课程
		     ON 选课.课程编号=课程.课程编号
WHERE 分数<60
--I7查询选修“C语言”课程的学生的学号和分数
SELECT 选课.学号,分数
FROM 课程 INNER JOIN 选课
   ON 课程.课程编号=选课.课程编号
WHERE 课程名='C语言'
--I8查询出所有学生的选课情况，包括学号，姓名、课程编号、课程名、学分、分数
SELECT 学生.学号,姓名,选课.课程编号,课程名,学分,分数
FROM 学生 INNER JOIN 选课
      ON 学生.学号=选课.学号
       INNER JOIN 课程
          ON 选课.课程编号=课程.课程编号
--I9
--为“学生选课数据库”数据库中的选课表创建一个名为update_fenshu的UPDATE触发器，该触发器的作用是禁止更新选课表中的“分数”字段的内容。并演示用UPDATE语句修改“选课”表中第一条记录为分数改为80如图：
--090101
--100002

--80

--要求显示“不能修改分数”的警告信息。


CREATE TRIGGER update_fenshu
ON 选课
FOR UPDATE
AS 
    IF UPDATE(分数) 
        BEGIN
             PRINT'不能修改分数'
             ROLLBACK TRAN
        END
UPDATE 选课
SET 分数=80
WHERE 学号='090101'


--J1.	
--创建一个名字为“jieyue”的数据库，该数据库包含一个数据文件和一个日志文件，数据文件的逻辑文件名为jieyue_data,磁盘文件名为jieyue_data.mdf，
--文件初始容量为5MB，最大容量为15MB，文件增长量为20%；而事务日志文件的逻辑文件名jieyue_log，磁盘文件名为jieyue_log.ldf，文件初始容量为5MB，
--最大容量为10MB，文件递增量为1MB（数据库创建在C盘根目录下）。
CREATE DATABASE jieyue
ON  PRIMARY 
( NAME = N'jieyue', 
  FILENAME = 'E:\SJD\jieyue.mdf' , 
  SIZE = 8960KB , 
  MAXSIZE = 15360KB , 
  FILEGROWTH = 20%)
  LOG ON 
( NAME = N'jieyue_log', 
  FILENAME = 'E:\SJD\jieyue_log.ldf' , 
  SIZE = 5120KB , 
  MAXSIZE = 10240KB , 
  FILEGROWTH = 1024KB )
  --J2.	使用操作创建“读者”表。
CREATE TABLE 读者
(
	卡号 nvarchar(255) primary key,
	姓名 nvarchar(255) ,
	性别 nvarchar(255) ,
	系部 nvarchar(255) 
)
ALTER TABLE 读者
ADD CONSTRAINT CK_sex CHECK(性别='男' OR 性别='女' )
--用INSERT语句输入数据
INSERT INTO 读者
(卡号,姓名,	性别,系部)
VALUES
('0001','洪玮','男','计算机系')
INSERT INTO 读者
(卡号,姓名,	性别,系部)
VALUES
('0002','袁骏','男','计算机系')
INSERT INTO 读者
(卡号,姓名,	性别,系部)
VALUES
('0003','曾晔','男','计算机系')
INSERT INTO 读者
(卡号,姓名,	性别,系部)
VALUES
('0004','平怀军','男','计算机系')
INSERT INTO 读者
(卡号,姓名,	性别,系部)
VALUES
('0005','史伟建','男','计算机系')
INSERT INTO 读者
(卡号,姓名,	性别,系部)
VALUES
('0006','徐波','男','计算机系')
INSERT INTO 读者
(卡号,姓名,	性别,系部)
VALUES
('0007','范美芳','女','计算机系')
INSERT INTO 读者
(卡号,姓名,	性别,系部)
VALUES
('0008','高燕燕','女','计算机系')

--J3	利用导入功能，导入“图书”表、“借书记录” 表、“还书记录” 表
--能成功导入即可

--J4 查询卡号为‘0002’的读者的借书和还书情况，显示出卡号、图
--书编号、借书日期、应还日期、还书日期
SELECT 读者.卡号,还书记录.图书编号,借书日期,应还日期,还书日期
FROM 读者 INNER JOIN 借书记录
	ON 读者.卡号 =借书记录.卡号
	INNER JOIN 还书记录
	ON 读者.卡号=还书记录.卡号
WHERE 读者.卡号='0002'

--J5 查询图书编号为“bsn001”的图书的借还情况，显示出卡号、图
--书编号、借书日期、应还日期、还书日期

SELECT 还书记录.卡号,还书记录.图书编号,借书日期,应还日期,还书日期
FROM 图书 INNER JOIN 借书记录
	ON 图书.图书编号 =借书记录.图书编号
	INNER JOIN 还书记录
	ON 图书.图书编号=还书记录.图书编号
WHERE 图书.图书编号='bsn001'


--J6 查询读者“袁骏”所借阅过的图书，显示卡号、姓名、图书名称

SELECT 读者.卡号,读者.姓名,图书.图书名称
FROM 读者 INNER JOIN 借书记录
	ON 读者.卡号 =借书记录.卡号
	INNER JOIN 图书
	ON 借书记录.图书编号=图书.图书编号
WHERE 读者.姓名='袁骏'


--J7 查询图书“JAVA程序设计”借阅信息，显示读者的卡号、姓名和图书名称


SELECT 读者.卡号,读者.姓名,图书.图书名称
FROM 图书 INNER JOIN 借书记录
	ON 图书.图书编号 =借书记录.图书编号
	INNER JOIN 读者
	ON 读者.卡号=借书记录.卡号
WHERE 图书.图书名称=  'JAVA程序设计'

--J8 为jieyue数据库中的“借书记录” 表创建一个名为insert_jie的insert触发器，该触发器的作用是每当借书表插入
--一条记录，相应的“图书”表应该减少1本该书的库存数量。并用insert语句触发事件，截图相应结果。
CREATE TRIGGER  insert_jie
ON 借书记录		--对哪张表起作用
FOR INSERT      --触发器条件 ，INSERT  ，DELECT
AS 
	UPDATE 图书
	SET 库存数量=库存数量-1
	WHERE 图书编号=(SELECT 图书编号 FROM inserted)


INSERT INTO 借书记录
VALUES
('0001','bsn001','2020-6-12','2020-7-12')

--J9 为jieyue数据库中的“还书记录” 表创建一个名为insert_huan的insert触发器，该触发器的作用是每当还书表插入一条记
--录，相应的“图书”表应该增加1本该书的库存数量。并用insert语句触发事件，截图相应结果。
CREATE TRIGGER  insert_huan
ON 还书记录		--对哪张表起作用
FOR INSERT      --触发器条件 ，INSERT  ，DELECT
AS 
	UPDATE 图书
	SET 库存数量=库存数量+1
	WHERE 图书编号=(SELECT 图书编号 FROM inserted)


INSERT INTO 还书记录
VALUES
('0001','bsn001','2020-6-12')