/*

CREATE TABLE stuinfo
   (stu#       varchar(10) NOT NULL UNIQUE,
    stu_name   varchar(10),
    sex        varchar(2),
    province   varchar(10),
    area       varchar(10),
    birthday   datetime,
    nation     varchar(10) DEFAULT '漢',
    member     bit,
  PRIMARY KEY(stu#))


CREATE TABLE course
   (course#       varchar(10) NOT NULL UNIQUE,
    course_name   varchar(40),
    credit_hour   numeric(5,1),
    period        integer,
  PRIMARY KEY(course#),
  CHECK(credit_hour>=0),
  CHECK(period>=0))


CREATE TABLE student_grade
   (stu#        varchar(10) NOT NULL,
    course#     varchar(10) NOT NULL,
    grade       numeric(5,2),
  PRIMARY KEY(stu#,course#),
  FOREIGN KEY(stu#) REFERENCES stuinfo(stu#),
  FOREIGN KEY(course#) REFERENCES course(course#),
  CHECK(grade BETWEEN 0 AND 100))


ALTER TABLE stuinfo
    ADD  department  varchar(20)


INSERT INTO stuinfo  
  VALUES ('98111001', '蔡堯強', '男', '浙江', '金華', '1980-1-20', '漢', 1, NULL)

INSERT INTO stuinfo
  VALUES ('98111002', '馮正亞', '女', '江蘇', '江陰', '1979-1-9', '漢', 0, NULL)

INSERT INTO stuinfo
  VALUES ('98111003', '傅建玲', '女', '浙江', '紹興', '1980-5-22', '漢', 1, NULL)

INSERT INTO stuinfo
  VALUES ('98111004', '韓家雄', '男', '江蘇', '南通', '1979-4-30', '漢', 0, NULL)

INSERT INTO stuinfo  
  VALUES ('98111006', '江海山', '男', '安徽', '合肥', '1981-2-3', '漢', 0, NULL)

INSERT INTO stuinfo  
  VALUES ('98111007', '金明', '男', '安徽', '馬鞍山', '1980-1-1', '漢', 0, NULL)

INSERT INTO stuinfo  
  VALUES ('98111008', '李翠', '女', '陜西', '延安', '1981-3-1', '漢', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111009', '李維華', '女', '青海', '西寧', '1979-11-6', '漢', 1, NULL)

INSERT INTO "stuinfo"  
  VALUES ('98111010', '梁小麟', '男', '廣東', '茂名', '1980-3-5', '漢', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111011', '林水寶', '男', '福建', '泉州', '1981-2-10', '漢', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111012', '劉洪星', '男', '山東', '日照', '1979-10-1', '漢', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111013', '劉紹軍', '男', '湖南', '常德', '1980-2-3', '漢', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111014', '呂修福', '男', '江蘇', '連云港', '1981-11-1', '漢', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111015', '祁晶', '男', '甘肅', '白銀', '1981-4-2', '漢', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111016', '欽海蓉', '男', '江蘇', '江陰', '1980-1-1', '漢', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111017', '邵斯林', '男', '廣西', '梧州', '1980-1-1', '漢', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111018', '沈瀚斐', '男', '江蘇', '無錫', '1980-1-1', '漢', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111019', '盛梅蘭', '女', '浙江', '金華', '1980-1-1', '漢', 1, NULL)

INSERT INTO stuinfo 
  VALUES ('98111020', '石健', '男', '貴州', '畢節', '1980-1-1', '漢', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111021', '王雄川', '男', '浙江', '金華', '1980-1-1', '漢', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111022', '魏曉宇', '男', '江蘇', '揚州', '1980-1-1', '漢', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111023', '肖穎', '女', '福建', '南平', '1980-1-1', '漢', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111024', '謝吉吉', '男', '廣東', '韶關', '1980-1-1', '漢', 0, NULL)

INSERT INTO stuinfo  
  VALUES ('98111025', '徐丹', '女', '江蘇', '南通', '1980-1-1', '漢', 0, NULL)

INSERT INTO stuinfo  
  VALUES ('98111026', '徐遠哲', '男', '安徽', '宿縣', '1980-1-1', '漢', 0, NULL)

INSERT INTO stuinfo  
  VALUES ('98111027', '許明', '男', '山東', '日照', '1980-1-1', '漢', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111028', '嚴偉', '男', '浙江', '衢州', '1981-2-1', '漢', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111029', '楊旭', '男', '貴州', '銅仁', '1980-10-9', '土家', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111030', '楊訓', '男', '安徽', '宿縣', '1979-3-2', '漢', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111031', '姚遠秀', '男', '陜西', '商洛', '1980-1-1', '漢', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111032', '易蟬鳴', '女', '廣東', '湛江', '1980-1-1', '漢', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111033', '張建', '男', '江蘇', '淮陰', '1980-1-1', '漢', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111034', '張蘇榮', '男', '安徽', '宣城', '1980-1-1', '漢', 1, NULL)

INSERT INTO stuinfo 
  VALUES ('98111037', '周雷', '男', '江蘇', '徐州', '1980-1-1', '漢', 1, NULL)

INSERT INTO stuinfo
  VALUES ('98111038', '朱強光', '男', '江蘇', '泗陽', '1980-1-1', '漢', 1, NULL)

INSERT INTO stuinfo
  VALUES ('98111040', '鄒志敏', '男', '江西', '九江', '1980-1-1', '漢', 1, NULL)

INSERT INTO stuinfo (stu#, stu_name)
  VALUES ('98111041', '李偉')


UPDATE  stuinfo  
  SET   sex =  '男',
        province = '江蘇',   
        area = '南京',   
        birthday = '1981-1-1',
        member = 1  
  WHERE stu# = '98111041'

UPDATE stuinfo  
  SET department = '計算機系'


DELETE FROM stuinfo  
  WHERE stu# = '98111041'


SELECT stu_name, sex, province, area
  FROM stuinfo  
  WHERE ( sex = '女' ) AND ( province = '江蘇' )


SELECT stu_name, sex, province, area
  FROM stuinfo  
  WHERE ( sex = '女' ) AND ( province = '江蘇' ) OR (province like '廣%' )
  ORDER BY province

SELECT province, COUNT(stu_name)
  FROM stuinfo  
  GROUP BY province HAVING COUNT(stu_name)>4


INSERT INTO stuinfo (stu#)
  VALUES ('98111041')

SELECT COUNT(province)
  FROM stuinfo  

SELECT COUNT(*)
  FROM stuinfo  

SELECT COUNT(DISTINCT province)
  FROM stuinfo  


*/

/*
下列語句錯，不能刪除列
ALTER TABLE stuinfo
  DROP  department

下列語句執行未成功
修改已有列的數據類型？？
？？ALTER TABLE stuinfo
    MODIFY member integer

刪除列的約束
??ALTER TABLE course
    DROP CHECK(period>0)

增加列的寬度
??ALTER TABLE stuinfo
  MODIFY  stu_name nchar(20)

*/