How SQL Server uses SQL statements to create tables

--Using Master
Use master
Go

--Determine if database HR exists
if exists (select 1 from sysdatabases where name= ' HR ')
Begin
--delete Database hr if it exists
Drop Database HR
End
Go

--Create DATABASE HR
Create DATABASE HR
Go

--Using Database hr
Use HR
Go

-------------------------------------------
-------------------------------------------
--Create jobs table in database HR information about positions in the company
--Judging whether the jobs table exists
if exists (select 1 from sysobjects where name= ' JOBS ')
Begin
--Delete the Jobs table if the table exists
drop table JOBS
End
Go

--Create a jobs table
CREATE TABLE JOBS
(
job_id varchar (TEN) NOT null primary key,--position number
Job_title nvarchar () not NULL,--Job name
Min_salary money is not null,--Job minimum salary, not less than 1000 yuan
Max_salary money is not null-the position is the most well-paid, not less than the minimum wage
)
Go

---------add constraints in the Jobs table-----------
--check min_salary not less than 1000 yuan
ALTER TABLE JOBS
Add constraint ch_min_salary Check (min_salary>=1000)
--check max_salary is not less than minimum salary
ALTER TABLE JOBS
Add constraint ch_max_salary Check (max_salary>=min_salary)
Go

--Add test data to table jobs
INSERT into JOBS values (1001, ' manager ', 1200,1300)
--insert into jobs values (1002, ' manager 1 ', 200,1300) error, position minimum salary less than 1000 yuan
Go

--Show all the information in jobs
SELECT * FROM JOBS
Go
-------------------------------------------
-------------------------------------------

-------------------------------------------
-------------------------------------------
--Create a departments table in database HR stores information about departments in the company
--Determine if the table departments exists
if exists (select 1 from sysobjects where name= ' departments ')
Begin
--delete if it exists
DROP TABLE Departments
End
Go

--Create TABLE departments
CREATE TABLE Departments
(
department_id int Identity (1001,1) primary key NOT NULL,--department number, primary key, self-increment
Department_name nvarchar (+) not NULL,--department name
manager_id INT--Department supervisor number, foreign key, reference Employees table employee_id
)
Go

--Add test data to the table
INSERT into departments values (' administrative department ', null)
Go

--Show all the information in the table departments
SELECT * FROM Departments
Go
-------------------------------------------
-------------------------------------------

-------------------------------------------
-------------------------------------------
--Create a Employees table in database HR stores information about employees in the company
--Determine if the table employees exists
if exists (select 1 from sysobjects where name= ' EMPLOYEES ')
Begin
--Delete the table if it exists in the Employees table
drop table EMPLOYEES
End
Go

--Create TABLE EMPLOYEES
CREATE table EMPLOYEES
(
employee_id int primary key identity (100,1) not NULL,--employee number, primary key, Increment (starting at 100, 1 per increment)
first_name nvarchar (4) NOT NULL,--employee name
last_name nvarchar (4) NOT NULL,--employee last name
EMAIL varchar ( NOT NULL,--clerk message
Phone_number varchar () NOT NULL,--staff phone
hire_date datetime default (GETDATE ()),--Staff entry Time
job_id varchar (0) NOT NULL,--clerk position number, foreign key, reference to Jobs table job_id
SALARY Money,--staff salary, greater than a/manager_id int,--Employee supervisor number, foreign key, Reference Employees table employee_id
department_id int,--Employee department number, foreign key, departments table department_id
)
Go

----------add constraints to the table---------------
--Employee position number, foreign key, reference to Jobs table job_id
ALTER TABLE EMPLOYEES
Add constraint fk_job_id foreign key (job_id) references JOBS (job_id)
--staff salary, more than 0
ALTER TABLE EMPLOYEES
Add constraint ch_salary Check (salary>0)
--Employee Supervisor number, foreign key, reference Employees table employee_id
ALTER TABLE EMPLOYEES
Add constraint fk_employee_id foreign key (employee_id) references EMPLOYEES (employee_id)
--Employee department number, foreign key, reference departments table department_id
ALTER TABLE EMPLOYEES
Add constraint fk_department_id foreign key (DEPARTMENT_ID) references departments (DEPARTMENT_ID)
Go

--Add test data to table employees
INSERT into EMPLOYEES values (' John Doe ', ' Lee ', ' [email protected] ', ' 180123266 ', default, ' 1001 ', 2000,100,1001)
Go

--Show all the information in the table employees
SELECT * FROM EMPLOYEES
Go
-------------------------------------------
-------------------------------------------

-------------Extension Exercises---------------------
-------------------------------------------
-------------------------------------------
--Create a Job history table in database hr Distory
--Determine if the table Distory in HR exists
if exists (select 1 from sysobjects where name= ' distory ')
Begin
--Delete the table if the table exists
drop table Distory
End
Go

--Create TABLE Distory
CREATE TABLE Distory
(
employee_id int NOT NULL,--employee number, primary key
start_date datetime NOT NULL,--start time, primary key
end_date datetime NOT NULL,--end time defaults to GETDATE ()
job_id varchar (+) NOT NULL,--clerk position number, foreign key, reference to the job_id of the jobs table
department_id INT--employee department number, foreign key, reference departments table department_id

)
Go

----------add constraints to the table---------------
--employee number, primary key
ALTER TABLE Distory
Add constraint pk_employee_id primary key (EMPLOYEE_ID)
--Start time, primary key
ALTER TABLE Distory
Add constraint pk_start_date unique (start_date)
--End time defaults to GETDATE ()
ALTER TABLE Distory
Add constraint df_end_date Default (GETDATE ()) for end_date
--Employee position number, foreign key, reference to Jobs table job_id
ALTER TABLE Distory
Add constraint fk_job_id1 foreign key (job_id) references JOBS (job_id)
--Employee department number, foreign key, reference departments table department_id
ALTER TABLE Distory
Add constraint fk_department_id1 foreign key (DEPARTMENT_ID) references departments (DEPARTMENT_ID)
Go

--Add test data to table Distory
INSERT into distory values (100001, ' 2012.12.12 ', default, ' 1001 ', 1001)
INSERT into distory values (100002, ' 2015. 2.2 ', default, ' 1001 ', 1001)
Go

--Query all the information in the table Distory
SELECT * FROM Distory
Go
-------------------------------------------
-------------------------------------------

How SQL Server uses SQL statements to create tables