--創建學生基本情況表
CREATE TABLE XS
( 學號     char(6)  PRIMARY KEY,
  姓名     char(8)  NOT NULL,
  身份證   char(20) CONSTRAINT sh_uk UNIQUE,
  專業名   char(10) NULL,
  性別     bit NOT NULL DEFAULT 1,
  出生時間 smalldatetime NULL,
  總學分   tinyint  NULL CHECK(總學分<=50),
  備注     text  NULL,
  CONSTRAINT chk CHECK
         (學號 like '[0-9][0-9][0-9][0-9][0-9][0-9]')
 )

--插入測試數據
INSERT xs VALUES('010001','王林'  ,'140104198510121234','計算機',1,'1984.10.12',40,'本市')
INSERT xs VALUES('010002','程明'  ,'','通信工程',0,'1985.1.1',20,NULL)
SELECT * FROM XS

--創建課程情況表
CREATE TABLE KC
    (
       課程號   char(3)  PRIMARY KEY ,
       課程名   char(16) NOT NULL ,
       開課學期 tinyint  NOT NULL ,
       學時     tinyint  NOT NULL ,
       學分     tinyint  NULL 
     ) 

--插入測試數據
INSERT KC VALUES('101','計算機基礎',   1, 80, 5)
INSERT KC VALUES('102','程序設計語言', 2, 68, 4)
INSERT KC VALUES('206','離散數學',     4, 68, 4)

--創建學生選課情況表
CREATE TABLE XS_KC
   (  學號    char(6) NOT NULL REFERENCES xs(學號),
      課程號  char(3) NOT NULL REFERENCES KC(課程號),
      成績    tinyint  NULL,
      學分    tinyint  NULL,
      CONSTRAINT pk PRIMARY KEY(學號,課程號)	
   )

--插入測試數據
INSERT XS_KC(學號,課程號,成績) VALUES('010001','101',80)
INSERT XS_KC(學號,課程號,成績) VALUES('010001','102',78)
INSERT XS_KC(學號,課程號,成績) VALUES('010002','101',62)


--檢查DEFAULT約束
INSERT xs(學號,姓名,身份證) VALUES('010003','王燕' ,'不祥')

--檢查NOT NULL約束
INSERT xs(學號,身份證) VALUES('010003','14010119700506133')

--檢查PRIMARY KEY約束
INSERT xs VALUES('010002','王玉民'  ,'','計算機',0,'1985.1.1',20,NULL)
INSERT XS_KC(學號,課程號,成績) VALUES('010001','101',100)

--檢查UNIQUE約束
INSERT xs(學號,姓名,身份證) VALUES('010004','王玉民' ,'140104198510121234')
INSERT xs(學號,姓名,身份證) VALUES('010004','王玉民' ,NULL)
INSERT xs(學號,姓名,身份證) VALUES('010005','張平' ,NULL)

--檢查CHECK約束
INSERT xs(學號,姓名,身份證) VALUES('01006','趙高' ,'1234')
UPDATE xs SET 總學分=60 WHERE 學號 = '010003'

--檢查FOREGIN KEY約束
INSERT XS_KC(學號,課程號,成績) VALUES('010006','102',78)
UPDATE xs SET 學號='010007' WHERE 學號 = '010003'
UPDATE xs SET 學號='010008' WHERE 學號 = '010002'
DELETE xs WHERE 學號 = '010002'


SELECT * FROM XS
SELECT * FROM XS_KC
SELECT * FROM KC