SQL Query Language Exercise

Use master GO IF EXISTS (SELECT* FROM sysdatabases WHERE name='Mystudentinfomanage') DROP DATABASE mystudentinfomanage--Delete a database go to CREATE DB mystudentinfomanage--Create a database go use Mystudentinfomanagego--Student Table if EXISTS (SELECT* FROM sysobjects WHERE name='Student') DROP TABLE Student--Delete table Go CREATE table Student (--Student (s#,sname,sage,ssex) Student table s# INT not NULL PRIMARY KEY,--Study number Sname NVARCHAR ( -) Not NULL,--name Sage INT not NULL,--Age Ssex CHAR (2) Not NULL--gender);--Teacher Table if EXISTS (SELECT* FROM sysobjects WHERE name='Teacher') DROP table Teachergo CREATE table Teacher (--Teacher (t#,tname) teacher table t# INT not NULL PRIMARY KEY, Tname NVARCHAR ( -) not NULL);--Curriculum if EXISTS (SELECT* FROM sysobjects WHERE name='Course') DROP table Coursego CREATE table Course (--Course (c#,cname,t#) Curriculum C # INT not NULL PRIMARY KEY, Cname NVARCHAR ( -) NOT NULL, t# INT is not null REFERENCES dbo. Teacher (t#));--score Table if EXISTS (SELECT* FROM sysobjects WHERE name='SC') DROP TABLE scgo CREATE table SC (--SC (s#,c#,score) score table s# INT not NULL REFERENCES dbo. Student (s#), C # INT not NULL REFERENCES dbo. Course (C #), score INT not NULL, CONSTRAINT fk_student_course PRIMARY KEY (s#,c#)--School Number and course number of the same seat);----------------------------------------------------------------------inserting test data into a data table (Student) Medium--------------------------------------------DELETE from dbo. Student;insert into dbo. Student (s#, Sname, Sage, Ssex) VALUES (001,--s#-intN'Zhang San',--Sname-nvarchar ( -)           -,--Sage-int          'male'--Ssex-Char(2) ) INSERT into dbo. Student (s#, Sname, Sage, Ssex) VALUES (002,--s#-intN'John Doe',--Sname-nvarchar ( -)           +,--Sage-int          'male'--Ssex-Char(2) ) INSERT into dbo. Student (s#, Sname, Sage, Ssex) VALUES (003,--s#-intN'Harry',--Sname-nvarchar ( -)           A,--Sage-int          'female'--Ssex-Char(2) ) INSERT into dbo. Student (s#, Sname, Sage, Ssex) VALUES (004,--s#-intN'Saturday',--Sname-nvarchar ( -)           -,--Sage-int          'female'--Ssex-Char(2)          )                          ------------------------------------------------------------inserting data into the Teacher's table (Teacher) Medium----------------------------------------------------------DELETE from dbo.          Teacher; INSERT into dbo. Teacher (t#, Tname) VALUES ( on,--t#-intN'cotyledons'--Tname-nvarchar ( -)          )                ------------------------------------------------------------------insert data into the curriculum---------------------------------------------- ---------------DELETE from dbo. Course;insert into dbo. Course (C #, Cname, t#) VALUES (001,--C #-intN'language',--Cname-nvarchar ( -)           on--t#-intINSERT into dbo. Course (C #, Cname, t#) VALUES (002,--C #-intN'Mathematics',--Cname-nvarchar ( -)           on--t#-intINSERT into dbo. Course (C #, Cname, t#) VALUES (0003,--C #-intN'English',--Cname-nvarchar ( -)           on--t#-int          )                                            --------------------------insert data into the score table (SC)---------------------------------------------------------------------------------- -------------DELETE from dbo.              SC; INSERT into dbo. SC (s#, C #, score) VALUES (001,--s#-int          001,--C #-int           ---Score-int          )             

SQL Query Language Exercise