create database EDUC
on
(name = EDUC_data,
filename = 'd:\jxgl\EDUC.mdf',
size = 5,
maxsize = 10,
filegrowth = 1)
log on
(name = EDUC_log,
 filename ='d:\jxgl\EDUC.ldf',
 size = 2,
 maxsize = 5,
 filegrowth = 1)

use EDUC
go

create table Class (
clsNO char(6) PRIMARY KEY,
clsName varchar(16) NOT NULL,
Director varchar(10),
Specialty varchar(30)
)

create table Student (
sno char(8) PRIMARY KEY,
sname varchar(10) NOT NULL,
ssex char(2) CHECK(ssex IN('男','女')),
clsNO char(6),
saddr varchar(20),
sage tinyint CHECK(sage>=10 AND sage <= 30),
height numeric(4,2)
FOREIGN KEY(clsNO) REFERENCES Class(clsNO)
)

create table Course (
cno char(4) PRIMARY KEY,
cname varchar(16) NOT NULL,
cpno char(4),
Ccredit numeric(2,1)
)

create table sc(
  sno char(8),
  cno char(4),
  grade numeric(3,1),
  PRIMARY KEY (sno,cno),
  FOREIGN KEY(sno) REFERENCES Student(sno),
  FOREIGN KEY(cno) REFERENCES Course(cno) 
)

create table student1 (
sno char(8) PRIMARY KEY,
sname varchar(10) NOT NULL,
ssex char(2) CHECK(ssex IN('男','女')),
clsNO char(6),
saddr varchar(20),
sage tinyint CHECK(sage>=10 AND sage <= 30),
height numeric(4,2)
FOREIGN KEY(clsNO) REFERENCES Class(clsNO)
)

ALTER Table Student1 
 ADD s_entrance datetime

 ALTER TABLE Student1
  ALTER COLUMN saddr varchar(40)

  DROP TABLE student1


  INSERT INTO Class VALUES(
  'CS01','计算机一班','张宁','计算机应用'
  )

  INSERT INTO Class VALUES(
  'CS02','计算机二班','王宁','计算机应用'
  )

  INSERT INTO Class VALUES(
  'MT04','数学四班','陈晨','数学'
  )

  INSERT INTO Class VALUES(
  'PH08','物理八班','葛格','物理'
  )

  INSERT INTO Class VALUES(
  'GL01','地理一班','张四','应用地理'
  )

  Select * from Class

  INSERT INTO Student VALUES(
  '20130101','王军','男','CS01','下关40#','20','1.76'
  )

  INSERT INTO Student VALUES(
  '20130102','李杰','男','CS01','江边路96#','22','1.72'
  )

  INSERT INTO Student VALUES(
  '20130306','王彤','女','MT04','中央路94#','19','1.65'
  )

  INSERT INTO Student VALUES(
  '20130107','吴杪','女','PH08','莲化小区74#','18','1.60'
  )

  SELECT * FROM Student

  INSERT INTO Course VALUES (
  '0001','高等数学',NULL,'6'
  )

   INSERT INTO Course VALUES (
  '0003','计算机基础','0001','3'
  )

   INSERT INTO Course VALUES (
  '0007','物理','0001','4'
  )

  SELECT * FROM Course



  INSERT INTO sc VALUES(
  '20130101','0001','90'
  )

  INSERT INTO sc VALUES(
  '20130101','0007','86'
  )

  INSERT INTO sc VALUES(
  '20130102','0001','87'
  )

  INSERT INTO sc VALUES(
  '20130102','0003','76'
  )

  INSERT INTO sc VALUES(
  '20130306','0001','87'
  )

  INSERT INTO sc VALUES(
  '20130306','0003','93'
  )

  INSERT INTO sc VALUES(
  '20130107','0007','85'
  )

  INSERT INTO sc VALUES(
  '20130306','0007','90'
  )
  SELECT * FROM sc

  --首先在student表中插入一条新记录：学号：20131101、姓名：张三、性别：男、年龄：19、班级编号：‘CS01’。
  INSERT INTO Student VALUES(
  '20131101','张三','男','CS01',NULL,'19',NULL
  )
  SELECT * FROM Student
  
  --1)    对于student表，将所有班级号为‘CS01’的，并且年龄小于20岁的学生的班级号改为‘CS02’。
  UPDATE Student
  SET clsNO='CS02'
  WHERE clsNO='CS01' AND sage<20

  SELECT * FROM Student

  --2)    对于student表，删掉所有年龄小于20岁，并且专业号为‘CS02’的学生的记录
  DELETE FROM Student
  WHERE sage<20 AND clsNO='CS02'

  SELECT * FROM Student

  --用SQL语句分别建立以下索引
  --(1) 在student表的Sname列上建立普通降序索引Stusname。
  CREATE INDEX Stusname ON Student(sno DESC)

  --(2) 在course表的Cname列上建立唯一索引Coucname。
  CREATE UNIQUE INDEX Coucname ON Course(cname)

  --(3) 在sc表的Sno(升序), Cno(升序)和grade (降序)三列上建立一个普通索引SCno。
  CREATE INDEX SCno ON sc(sno ASC, cno ASC, grade DESC)


    DROP INDEX Stusname ON Student

--1)    查询“计算机应用”专业的学生学号和姓名；
  SELECT Student.sno,Student.sname FROM Student,Class 
  WHERE Student.clsNO = Class.clsNO AND Class.Specialty = '计算机应用'

--2)    查询选修课程“0001”且成绩在80～90 之间的学生学号和成绩，并将成绩乘以系数0.75 输出；
  SELECT sno,grade*0.75 grade FROM sc 
  WHERE grade BETWEEN 80 AND 90 AND cno = '0001'

--3)    查询“0001”课程的成绩高于“李杰”的学生学号和成绩；
SELECT DISTINCT sc.sno,sc.grade FROM Student,sc
WHERE sc.cno = '0001' AND sc.grade > (
SELECT sc.grade FROM sc,Student
    WHERE Student.sno = sc.sno AND Student.sname = '李杰' AND sc.cno = '0001'
) 
-- 李杰 0001 课程成绩为87.0 只有20130101 王军0001 成绩为90 高于
--4)    查询没有选修“0002”课程的学生姓名。
SELECT Student.sname FROM Student,sc
WHERE Student.sno = sc.sno AND sc.sno NOT IN (
    SELECT sc.sno FROM sc
    WHERE sc.cno = '0002'
)

SELECT Student.sname,Student.sno FROM Student,sc
WHERE Student.sno = sc.sno AND SC.cno != '0002'

SELECT Student.sname FROM Student
WHERE NOT EXISTS (
 SELECT * FROM Student,sc
 WHERE Student.sno = sc.sno AND sc.cno = '0002'
)
-- 没有编号为0002的课程，则所有的学生应该被选到



--实验7

--1) 查询学生的总人数。
 SELECT COUNT(*) FROM Student

--2) 查询选修了课程的学生人数。
 SELECT COUNT(DISTINCT sno) FROM sc

--3) 查询课程的课程号和选修该课程的人数。
 SELECT cno,COUNT(sno) FROM sc 
 GROUP BY cno

--4) 查询选修课程超过2 门课的学生学号和姓名。
 SELECT DISTINCT Student.sno,Student.sname FROM Student,sc
 WHERE Student.sno IN (
    SELECT sc.sno FROM sc
     GROUP BY sc.sno
     HAVING COUNT(*) > 2
 )


 --实验8
-- 1.定义视图
  --  在EDUC数据库中，已Student、Course 和SC表为基础完成一下视图定义:
    --1)    定义一个反映学生出生年份的视图V_YEAR，该视图要求使用系统函数（YEAR(),GETDATE()）来获取当前日期及转换；
    CREATE VIEW V_YEAR (sno, sname, sbirth)
    AS 
    SELECT sno,sname,YEAR(GETDATE())-sage
    from Student



    --2)    定义视图V_AVG_S_G：该视图将反映学生选修课程的门数及平均成绩；
    CREATE VIEW V_AVG_S_G (sno,ccount,grade_ave)
    AS 
    SELECT sno,COUNT(*) ,AVG(grade) FROM sc GROUP BY sc.sno


--2.使用视图
    --1)    查询平均成绩为90分及以上的学生学号、姓名和成绩；
    SELECT V_AVG_S_G.sno,V_YEAR.sname,V_AVG_S_G.grade_ave FROM V_AVG_S_G,V_YEAR
    WHERE V_AVG_S_G.sno=V_YEAR.sno AND V_AVG_S_G.grade_ave >= 90


    --2)    查询1995年出生的学生学号和姓名。
    SELECT sno,sname FROM V_YEAR
    WHERE sbirth='1996'


--实验9
--1. 在班级表class中增加班级人数（c_total）字段。
  ALTER TABLE Class
  ADD c_total int 

  UPDATE Class
  SET c_total = 2
  WHERE Class.clsNO='CS01'

  UPDATE Class
  SET c_total = 0
  WHERE Class.clsNO='CS02'

  UPDATE Class
  SET c_total = 0
  WHERE Class.clsNO='GL01'

 UPDATE Class
  SET c_total = 1
  WHERE Class.clsNO='MT04'

  UPDATE Class
  SET c_total = 1
  WHERE Class.clsNO='PH08'
  
  SELECT * FROM Class

--2. 为学生表(student)创建INSERT触发器t_inst_stu：新增一名学生时，若其班级编号非空，则将班级表（class）中相应班级的人数（c_total）自动加1。
   CREATE TRIGGER t_inst_stu ON Student
   FOR INSERT
   AS
   BEGIN
     IF EXISTS (SELECT * FROM inserted WHERE clsNO IS NOT NULL)
     BEGIN
      UPDATE Class SET c_total += 1
      WHERE clsNO = (SELECT clsNO FROM inserted)
     END
   END                                    
--3.为学生表(student)创建DELETE触发器t_dele_stu：删除一名学生时，若其班级编号非空，则将班级表（class）中相应班级的人数（c_total）自动减1。
   CREATE TRIGGER t_dele_stu ON Student
   FOR DELETE
   AS
   BEGIN
     IF EXISTS (SELECT * FROM deleted WHERE clsNO IS NOT NULL)
     BEGIN
       UPDATE Class SET c_total -= 1
       WHERE clsNO = (SELECT clsNO FROM deleted)
     END
   END
--4. 为学生表(student)创建UPDATE触发器t_update_stu:当某学生所在班号发生变化时（即调到另一班级后），将其原先所在班级的人数（c_total）减1，将新调入的班级班级的人数（c_total）加1。
  CREATE TRIGGER t_update_stu ON Student
  FOR UPDATE
  AS
  BEGIN
    IF UPDATE(clsNO)
    BEGIN
        UPDATE Class SET c_total -= 1 
        WHERE clsNO = (SELECT clsNO FROM deleted) 

        UPDATE Class SET c_total += 1
        WHERE clsNO = (SELECT clsNO FROM inserted)
    END
  END

  --体会触发器的作用
  --1) 查看班级表（class）
  SELECT * FROM Class

  --2）对学生表(student)分别插入（INSERT）、删除（DELETE）和修改（UPDATE）元组
  INSERT INTO Student VALUES(
  '20162037','路人甲','男','GL01','安徽合肥','21','1.70'
  )
  SELECT * FROM Class

  DELETE FROM Student
  WHERE sno = '20162037'
  SELECT * FROM Class

  --把学号为20130101的学生调到CS02班中
  UPDATE Student
  SET clsNO = 'CS02'
  WHERE sno = '20130101'
  SELECT * FROM Class

  --3）再次查看班级表（class），检查其数据的变化情况，体会触发器的作用。

  --实验10
--1创建一个不带参数的存储过程p_stu_info1，实现对满足要求的学生基本信息的查询。
--要求：所有年龄<21岁的男同学
  CREATE PROCEDURE p_stu_info1
  AS
    BEGIN
     SELECT * FROM Student WHERE sage < 21 AND ssex = '男'
    END
  
--2、创建一个带有参数的存储过程p_stu_info2，实现对满足要求的学生基本信息的查询。
--要求：输入参数为学号，与指定学号的学生同龄的所有同学。
  CREATE PROCEDURE p_stu_info2 @inputsno char(8) 
  AS
    BEGIN
      SELECT * FROM Student 
      WHERE sage = (SELECT sage FROM Student WHERE sno = @inputsno)
    END
 

--3、创建一个存储过程p_stu_info3,根据输入的学号，查询某学生的基本信息。
--要求：输入参数为学号。
  CREATE PROCEDURE p_stu_info3 @inputsno char(8)
  AS
    BEGIN
      SELECT * FROM Student WHERE sno = @inputsno;
    END

--4、创建一个存储过程p_stu_grade,根据输入的学号，返回其选课及其成绩。
--要求：输入参数为学号。
  CREATE PROCEDURE p_stu_grade @inputsno char(8)
  AS
   BEGIN
    SELECT cname,grade FROM Course INNER JOIN sc ON sc.cno = Course.cno
    WHERE sno = @inputsno ;
   END
  
--5.使用SQL语句分别执行p_stu_info1、p_stu_info2、p_stu_info3和p_stu_grade，并查看显示结果。
  
  EXEC p_stu_info1
  
  EXEC p_stu_info2 '20130101'

  EXEC p_stu_info3 '20130102'

  EXEC p_stu_grade '20130101'