SQL-sever-2000各种查询语句和建表语句.docxVIP

SQL sever 2000各种查询语句和建表语句 CREATE TABLE Company ( CompanyID CHAR(3) NOT NULL CONSTRAINT PK_Company PRIMARY KEY, CompanyName NVARCHAR(10) NOT NULL, CompanyAddress NVARCHAR(50) NULL ) CREATE TABLE Worker ( WorkerID CHAR(5) NOT NULL CONSTRAINT PK_Worker PRIMARY KEY, WorkerName NVARCHAR(5) NOT NULL, WorkerSex NCHAR(1) NOT NULL CONSTRAINT CK_Worker_WorkerSex CHECK (WorkerSex IN (男, 女)), WorkerAge TINYINT NULL, WorkerJob NVARCHAR(10) NULL, Salary INT NULL, CompanyID CHAR(3) NULL CONSTRAINT FK_Worker_Company FOREIGN KEY REFERENCES Company(CompanyID) ) CREATE TABLE Project ( ProjectID CHAR(3) NOT NULL CONSTRAINT PK_Project PRIMARY KEY, ProjectName NVARCHAR(20) NOT NULL, ProjectPlace NVARCHAR(10) NULL ) CREATE TABLE Enroll ( WorkerID CHAR(5) NOT NULL, ProjectID CHAR(3) NOT NULL, Job NVARCHAR(10) NULL, MonthCount INT NULL, MonthSalary INT NULL, CONSTRAINT PK_Enroll PRIMARY KEY (WorkerID, ProjectID), CONSTRAINT FK_Enroll_Worker FOREIGN KEY (WorkerID) REFERENCES Worker(WorkerID), CONSTRAINT FK_Enroll_Project FOREIGN KEY (ProjectID) REFERENCES Project(ProjectID) ) INSERT INTO Company (CompanyID, CompanyName, CompanyAddress) VALUES (A01, 北京公司, 北京海淀区) INSERT INTO Company (CompanyID, CompanyName, CompanyAddress) VALUES (B24, 上海公司, 上海闵行区) INSERT INTO Company (CompanyID, CompanyName, CompanyAddress) VALUES (C13, 福建公司, 福建福州鼓楼区) INSERT INTO Company (CompanyID, CompanyName, CompanyAddress) VALUES (D00, 台湾%公司, 台北县台北市) INSERT INTO Worker (WorkerID, WorkerName, WorkerSex, WorkerAge, WorkerJob, Salary, CompanyID) VALUES (A0101, 黄伟强, 男, 33, 工程师, 2100, A01) INSERT INTO Worker (WorkerID, WorkerName, WorkerSex, WorkerAge, WorkerJob, Salary, CompanyID) VALUES (A0102, 陈至, 男, 36, 高级工程师, 4300, A01) INSERT INTO Worker (WorkerID, WorkerName, WorkerSex, WorkerAge, WorkerJob, Salary, CompanyID) VALUES (B2424, 林发清, 女, 43, 高级工程师, 5000, B24) INSERT INTO Worker (WorkerID, WorkerName, WorkerSex,