SQL 2 for interview preparation -- Implementation of Database (CREATE TABLE FOR T-SQL database)

1. database files include:

Primary data file: *. MDF

Secondary data file: *. NDF

Log File: *. LDF (L is L in lower case)

 

2. Use the T-SQL to create a database

 

Code

Use Master
Go
-- --------- Create a database ------------

If   Exists ( Select   *   From Sysdatabases Where Name = ' Studb ' )
Drop   Database Studb
Create   Database Studb
On   Primary
(
Name = ' Studb_data ' ,
Filename = ' D: \ studb_data.mdf ' ,
Size = 3 MB,
Maxsize = 10 MB,
Filegrowth = 1 MB
)
Log   On
(
Name = ' Studb_log ' ,
Filename = ' D: \ studb_data.ldf ' ,
Size = 1 MB,
Filegrowth = 1 MB
)

 

 

3. Create a database table with T-SQL

 

Code

-- --------- Create a database table ------------
Use Studb
Go
If   Exists ( Select   *   From Sysobjects Where Name = ' Stuinfo ' )
Drop   Table Stuinfo
Create   Table Stuinfo
(
Stuname Varchar ( 20 ) Not   Null ,
Stuno Char ( 6 ) Not   Null ,
Stuage Int   Not   Null ,
Stuid numeric ( 18 , 0 ), -- ID card
Stuseat Smallint   Identity ( 1 , 1 ),
Stuaddress Text
)
Go

If   Exists ( Select   *   From Sysobjects Where Name = ' Stumarks ' )
Drop   Table Stumarks
Create   Table Stumarks
(
Exmano Char ( 7 ) Not   Null , -- Exam No.
Stuno Char ( 6 ) Not   Null , -- Student ID
Writtenexam Int Not   Null , -- Test Score
Labexam Int   Not   Null -- Test result
)
Go

 

 

4. Add Constraints

 

Code

-- ------------ Add constraints -----------------

Alter   Table Stuinfo -- Modify the stuinfo table
Add   Constraint Pk_stuno Primary   Key (Stuno) -- The primary key pk_stuno is a custom primary key name and can be omitted.

Alter   Table Stuinfo
Add   Constraint Uq_stuid Unique (Stuid) -- Add unique constraint

Alter   Table Stuinfo
Add   Constraint Df_stuaddress Default ( ' Address Unknown ' ) For Stuaddress -- Add default blank default 'address unknown'

Alter   Table Stuinfo
Add   Constraint Ck_stuage Check (Stuage Between   18   And   60 ) -- Add a check constraint between 18 and 60 years old

Alter   Table Stumarks
Add   Constraint Fk_stuno Foreign   Key (Stuno) References Stuinfo (stuno)
Go  

 

 

5. Delete Constraints

 

-- ----------- Delete constraint --------------
Alter   Table Stuinfo
Drop   Constraint Constraint name -- For example, fk_stuno ck_stuage df_stuaddress uq_stuid pk_stuno