Create Database MyFirstDatabase;

USE MyFirstDatabase;

Drop Table student;

Create Table student(
s_no char(8) 			PRIMARY KEY,
s_name char(8) 			NOT NULL,
s_sex char(2) 			CHECK(s_sex = '' OR s_sex = 'Ů'),
s_birthday smalldatetime	CHECK(s_birthday>='1970-1-1' AND s_birthday<='2000-1-1'),
s_speciality varchar(50) 	DEFAULT  '',
s_avgrade numeric(3,1) 	CHECK(s_avgrade >= 0 AND s_avgrade <= 100),
s_dept varchar(50) 		DEFAULT  'ѧϵ'
);


Delete From student;

Insert Into student Values('20060201','','', '1987-1-1', 'Ӧü', 94.5, 'ϵ')
Insert Into student Values('20060202','Ѿ','Ů', '1987-2-23', '', 88.8, 'ϵ')
Insert Into student Values('20060203','Ǹ','', '1986-12-25', '繤', 85.8, 'Ϣϵ')
Insert Into student Values('20060204','Ը','', '1988-7-1', '繤', 77.8, 'Ϣϵ')
Insert Into student Values('20060205','־','', '1985-9-18', 'Ӧü', 45.0, 'ϵ')
Insert Into student Values('20060206','˼','Ů', '1984-8-1', 'Ӧü', 61.3, 'ϵ')
Insert Into student Values('20060207','Զ','', '1983-11-2', '', 72.8, 'ϵ')
Insert Into student Values('20060208','','Ů', '1985-5-28', '', 55.7, 'ϵ')

Select * From student;


--=============================================================================================================

Drop Table SC;

Create Table SC(
s_no 	char(8),
c_name 	varchar(20),
c_grade	numeric(3,1) 	CHECK(c_grade >= 0 AND c_grade <= 100),
PRIMARY KEY(s_no, c_name)  --(s_no, c_name)Ϊ
);

Insert Into SC Values('20060201','Ӣ', 80.2)
Insert Into SC Values('20060201','ݿԭ', 70.0)
Insert Into SC Values('20060201','㷨', 92.4)
Insert Into SC Values('20060202','Ӣ', 81.9)
Insert Into SC Values('20060202','㷨', 85.2)
Insert Into SC Values('20060203','ý弼', 68.1)


Select * From SC;

--========== ʮ¼ ====================================================================
USE MyFirstDatabase
delete from student;
DECLARE @I INT, @s_no char(8); 
SET @I=0;
while @I<100000
BEGIN
  
  SET @s_no = STR(@I,8);
  --PRINT STR(@I);
  PRINT @s_no
  Insert Into student Values(@s_no,'','Ů', '1985-5-28', '', 55.7, 'ϵ')
  SET @I=@I+1
END

--SELECT * FROM student;
--=============================================================================================










