SQL CREATE TABLE, database, constraint, folder syntax

Use master
Go

--------------------------------------------------------------------------------------------------------------- ---------------------------
---------------------------------------------------------Create a database storage location (folder)-------------------------------------------------- ---
--------------------------------------------------------------------------------------------------------------- ---------------------------
--exec sp_configure ' show advanced options ', 1
--go
--reconfigure
--go
--exec sp_configure ' xp_cmdshell ', 1
--go
--reconfigure
--go
--exec xp_cmdshell ' mkdir e:\ '
--go

------------------------------------------------------------------------------------------------------------ Create a database------------------------------the
---------------------------------------------------------------------------------- ----------------------------------------------
------------------------------------------------------------- -----------------------------------------------------------------------------
If exists (SELECT * FROM sysdatabases WHERE name = ' guest ')
drop database,
Go
Create database Guest,
on primary
(
name = ' ',
filename = ' e:\ beida Jade Bird-shenzhen Lion \s2\1_ optimization myschool database design \chapter_08\room\room.mdf ',
size = 5,
filegrowth = 15%
)
Log on
(
name = ' Room_log ',
filename = ' e:\ beida Jade Bird-shenzhen Lions \s2\1_ optimization myschool Database design \chapter_08\room\room_log.ldf ',
size = 1,
FileGrowth = 15%
)
Go

------------------------------------------------------------------------------------------------------------ Create a table------------------------------the
------------------------------------------------------------------------------------ ------------------------------------------------
----------------------------------------------------------- -------------------------------------------------------------------------------
Use a.
Go
if exists ( SELECT * from sys.tables where name = ' Roomtype ')
drop table Roomtype
Go
if exists (SELECT * from Sys.tables wher E name = ' roomstate ')
drop table Roomstate
Go
if exists (SELECT * from sys.tables where name = ' guest ')
Drop t Able the
Go
if exists (SELECT * from sys.tables where name = ' residestate ')
drop table Residestate
Go
If E Xists (SELECT * from sys.tables where name = ' Guestrecord ')
drop table Guestrecord
Go

CREATE TABLE Roomtype--room type table
(
TypeID int identity (first) not NULL,--room type number, primary key, identity column starting from 1, increment value of 1
TypeName varchar () NOT NULL,--room type name
Typeprice Decimal NOT null--room price, greater than or equal to 0
)
Go

CREATE TABLE Roomstate--room status table
(
Roomstateid int identity (first) not NULL,--room status number, primary key, identity column starting from 1, increment value of 1
Roomstatename varchar (NOT NULL)--room status name
)
Go

CREATE TABLE Room-rooms Information sheet
(
Roomid int identity (first) not NULL,--room number, primary key, identity column starting from 1, increment value of 1
Bednum int,--Number of beds, default value is 2
Guestnum int,--Number of guests, default value is 0, must be greater than or equal to 0
[Description] varchar (NOT NULL),--room description, required
Roomstateid int,--room status number, foreign key
Roomtypeid INT--room type number, foreign key
)
Go

CREATE TABLE Residestate--Checkout status table
(
Resideid int identity (first) not NULL,--checkout status number, primary key, identity column starting from 1, increment value of 1
Residename varchar () NOT null--checkout status name, required
)
Go

CREATE TABLE Guestrecord--Guest Information Form
(
Gusetid int identity (first) not NULL,--guest check-in serial number, primary key, identity column starting from 1, with a value increment of 1
Gusetname varchar () NOT NULL,--guest name
Identityid varchar NOT NULL,--ID number, number of characters must be equal to 18
Roomid int,--room number, foreign key
Resideid int,--Checkout status number, foreign key, default value "No Checkout" number
Residedate datetime,--check-in date
Leavedate datetime,--checkout date must be greater than or equal to check-in date
Deposit Decimal,--Deposit
Totalmoney decimal--Total amount
)
Go

--------------------------------------------------------------------------------------------------------------- ---------------------------
-----------------------------------------------------------Add constraint Information-------------------------------------------------------- -----------
--------------------------------------------------------------------------------------------------------------- ---------------------------
--Add primary key
ALTER TABLE Roomtype
Add constraint Pk_roomtype_typeid primary key (TypeID)
Go
ALTER TABLE Roomstate
Add constraint Pk_roomstate_roomstateid primary key (Roomstateid)
Go
ALTER TABLE
Add constraint Pk_room_typeid primary key (Roomid)
Go
ALTER TABLE Residestate
Add constraint Pk_residestate_typeid primary key (Resideid)
Go
ALTER TABLE Guestrecord
Add constraint Pk_guestrecord_typeid primary key (Gusetid)
Go
--Add a FOREIGN KEY constraint
ALTER TABLE
Add Constraint Fk_room_roomstateid
Foreign KEY (Roomstateid) references Roomstate (Roomstateid)
Go
ALTER TABLE
Add Constraint Fk_room_roomtypeid
Foreign KEY (Roomtypeid) references Roomtype (TypeID)
Go
ALTER TABLE Guestrecord
Add Constraint Fk_guestrecord_roomid
Foreign KEY (Roomid) references (roomid)
Go
ALTER TABLE Guestrecord
Add Constraint Fk_guestrecord_resideid
Foreign KEY (Resideid) references Residestate (Resideid)
Go
--Adding CHECK constraints
ALTER TABLE Roomtype
Add constraint ck_roomtype_typeprice check (typeprice >= 0)
Go
ALTER TABLE
Add constraint ck_room_guestnum check (guestnum >= 0)
Go
ALTER TABLE Guestrecord
Add constraint Ck_guestrecord_identityid check (len (identityid) = 18)
Go
ALTER TABLE Guestrecord
Add constraint ck_guestrecord_leavedate Check (DateDiff (day,leavedate,residedate) <= 0)
Go
--Add a DEFAULT constraint
ALTER TABLE
Add constraint Df_room_bednum Default (2) for Bednum
Go
ALTER TABLE
Add constraint Df_room_guestnum Default (0) for Guestnum
Go
ALTER TABLE Guestrecord
Add constraint Df_guestrecord_resideid Default (0) for Resideid
Go

--------------------------------------------------------------------------------------------------------------- ---------------------------
-----------------------------------------------------------Add test Data-------------------------------------------------------- -----------
--------------------------------------------------------------------------------------------------------------- ---------------------------
INSERT into Roomtype values (' Standard rooms ', 180.00)
INSERT into Roomtype values (' Single suite ', 360.00)
INSERT into Roomtype values (' Deluxe Standard Room ', 260.00)
INSERT into roomstate values (' Clean in ')
INSERT into roomstate values (' No check in ')
INSERT into roomstate values (' already checked in ')
INSERT into room values (Default,default, ' twin Standard Rooms ', 1, 1)
INSERT into room values (Default,default, ' twin Standard Rooms ', 2, 1)
INSERT into room values (Default,default, ' double standard rooms ')
INSERT into room values (Default,default, ' twin Standard Rooms ', 3, 1)
INSERT into room values (Default,default, ' single rooms ', 1, 1)
INSERT into room values (Default,default, ' single Rooms ', 2, 1)
INSERT into rooms values (Default,default, ' single Room ', up)
INSERT into room values (Default,default, ' single Rooms ', 3, 1)
INSERT into room values (Default,default, ' single rooms ', 1, 3)
INSERT into room values (Default,default, ' single Rooms ', 2, 3)
INSERT into rooms values (Default,default, ' single Room ', 3,2)
INSERT into room values (Default,default, ' Single Suite ', 2, 1)
INSERT into room values (Default,default, ' Single Suite ', 1, 3)
INSERT into room values (Default,default, ' Single Suite ', 3, 1)
INSERT into room values (Default,default, ' Single Suite ')
INSERT into room values (Default,default, ' Single Suite ', 2, 3)
INSERT into room values (Default,default, ' Single Suite ', 1, 3)
INSERT into room values (Default,default, ' Single Suite ')
INSERT into room values (Default,default, ' Single Suite ', 3,2)
INSERT into room values (Default,default, ' Single Suite ')
INSERT into room values (Default,default, ' Single Suite ', 3, 1)
INSERT into room values (Default,default, ' Deluxe Standard rooms ', 2, 3)
INSERT into room values (Default,default, ' Deluxe Standard rooms ', 1, 3)
INSERT into room values (Default,default, ' Deluxe Standard rooms ', 2, 1)
INSERT into room values (Default,default, ' Deluxe Standard rooms ', +)
INSERT into room values (Default,default, ' Deluxe Standard rooms ', 2, 1)
INSERT into room values (Default,default, ' Deluxe Standard Rooms ', 3, 1)
INSERT into residestate values (' No checkout ')
INSERT into residestate values (' checked out ')
INSERT into Guestrecord values (' Gao Fei ', ' 555555555555555555 ', 1,1,getdate (), DATEADD (Dd,1,getdate ()), 100.00,280.00)
INSERT into Guestrecord values (' Gao Fei 1 ', ' 444444444444444444 ', 2,1,getdate (), DATEADD (Dd,2,getdate ()), 100.00,380.00)
INSERT into Guestrecord values (' Gao Fei 2 ', ' 333333333333333222 ', 1,1,getdate (), DATEADD (Dd,3,getdate ()), 100.00,380.00)
INSERT into Guestrecord values (' Gao Fei 3 ', ' 333333333333333333 ', 1,2,getdate (), DATEADD (Dd,4,getdate ()), 100.00,280.00)
INSERT into Guestrecord values (' Gao Fei 4 ', ' 410787855123179810 ', 2,1,getdate (), DATEADD (Dd,5,getdate ()), 100.00,280.00)
INSERT into Guestrecord values (' Gao Fei 5 ', ' 123321321312321321 ', 1,2,getdate (), DATEADD (Dd,7,getdate ()), 100.00,380.00)
INSERT into Guestrecord values (' Gao Fei 6 ', ' 410787855102579810 ', 3,1,getdate (), DATEADD (Dd,8,getdate ()), 100.00,380.00)
INSERT into Guestrecord values (' Gao Fei 7 ', ' 410787855102579810 ', 1,1,getdate (), DATEADD (Dd,1,getdate ()), 100.00,280.00)
INSERT into Guestrecord values (' Gao Fei 8 ', ' 410787855102579810 ', 2,1,getdate (), DATEADD (Dd,3,getdate ()), 100.00,280.00)
INSERT into Guestrecord values (' Gao Fei 9 ', ' 410787855102579810 ', 1,1,getdate (), DATEADD (Dd,1,getdate ()), 100.00,380.00)
INSERT into Guestrecord values (' Gao Fei ', ' 410787855102579810 ', 3,1,getdate (), DATEADD (Dd,5,getdate ()), 100.00,280.00)
INSERT into Guestrecord values (' Gao Fei ', ' 123787855102579810 ', 1,2,getdate (), DATEADD (Dd,4,getdate ()), 100.00,380.00)
INSERT into Guestrecord values (' Gao Fei ', ' 210787855102579810 ', 1,1,getdate (), DATEADD (Dd,6,getdate ()), 100.00,280.00)
INSERT into Guestrecord values (' Gao Fei ', ' 440787855102579810 ', 1,2,getdate (), DATEADD (Dd,2,getdate ()), 100.00,380.00)
INSERT into Guestrecord values (' Gao Fei ', ' 410787855102579810 ', 1,1,getdate (), DATEADD (Dd,1,getdate ()), 100.00,280.00)
INSERT into Guestrecord values (' Gao Fei ', ' 410787855102576789 ', 1,1,getdate (), DATEADD (Dd,1,getdate ()), 100.00,380.00)
INSERT into Guestrecord values (' Gao Fei ', ' 410787855102579810 ', 1,1,getdate (), DATEADD (Dd,1,getdate ()), 100.00,280.00)
INSERT into Guestrecord values (' Gao Fei ', ' 456465464546546467 ', 2,1,getdate (), DATEADD (Dd,1,getdate ()), 100.00,280.00)
INSERT into Guestrecord values (' Gao Fei ', ' 000000000000000000 ', 1,2,getdate (), DATEADD (Dd,1,getdate ()), 100.00,280.00)
INSERT into Guestrecord values (' Gao Fei ', ' 111111111111111111 ', 1,1,getdate (), DATEADD (Dd,1,getdate ()), 100.00,280.00)
Go

SQL CREATE TABLE, database, constraint, folder syntax