SQL Server CREATE TABLE examples

-- DROP DATABASE testDB

CREATE DATABASE testDB
GO

USE testDB
GO

--------------------------
-- DDL
--------------------------

-------------------------
-- CREATE TABLE EX1
-------------------------

CREATE TABLE employees
(employeeID int,
 lastName varchar(25))

 DROP TABLE employees 

-------------------------
-- CREATE TABLE EX2 (IDENTITY / DEFAULT)
-------------------------

CREATE TABLE employees
(employeeID int IDENTITY(1,1) ,
 lastName varchar(25),
 salary money DEFAULT 4000 )

 INSERT INTO employees
 VALUES (1, 'yossi' , 6000)

 INSERT INTO employees
 VALUES ( 'yossi' , 6000)

 SELECT * FROM employees 

 -- Implicit Insert
 INSERT INTO employees
 VALUES ('moshe' , DEFAULT)
 -- Explicit Insert
 INSERT INTO employees (lastName)
 VALUES ('haim') 

 SELECT * FROM employees 

 DROP TABLE employees 

 -------------------------
-- CREATE TABLE EX3 (CONSTRAINTS)
-------------------------

-- NOT NULL 

DROP TABLE employees 

CREATE TABLE employees
(employeeID int  ,
 lastName varchar(25) NOT NULL ,
 salary money DEFAULT 4000 )

 INSERT INTO employees
 VALUES (1, NULL , 6000)

 -- UNIQUE 

 DROP TABLE employees 

CREATE TABLE employees
(employeeID int  ,
 lastName varchar(25) NOT NULL ,
 salary money DEFAULT 4000 ,
 phoneNumber varchar(25) UNIQUE)

 INSERT INTO employees
 VALUES (1, 'Moshe' , 6000 , '03-5555555') 

 INSERT INTO employees
 VALUES (2, 'Yossi' , 7000 , '03-5555555')

 -- PRIMARY KEY 

 DROP TABLE employees 

CREATE TABLE employees
(employeeID int PRIMARY KEY  ,
 lastName varchar(25) NOT NULL ,
 salary money DEFAULT 4000 ,
 phoneNumber varchar(25) UNIQUE)

 INSERT INTO employees
 VALUES (1, 'Moshe' , 6000 , '03-5555555')

 INSERT INTO employees
 VALUES (1, 'Yossi' , 7000 , '03-7777777')

 INSERT INTO employees
 VALUES (NULL, 'Yossi' , 7000 , '03-7777777')

 -- CHECK 1

DROP TABLE employees 

CREATE TABLE employees
(employeeID int PRIMARY KEY  ,
 lastName varchar(25) NOT NULL ,
 salary money DEFAULT 4000 CHECK (salary BETWEEN 0 AND 10000),
 phoneNumber varchar(25) UNIQUE)

  INSERT INTO employees
 VALUES (1, 'Moshe' , 10001 , '03-5555555')

  INSERT INTO employees
 VALUES (1, 'Moshe' , 9999 , '03-5555555')

 -- CHECK 2

DROP TABLE employees 

CREATE TABLE employees
(employeeID int PRIMARY KEY  ,
 lastName varchar(25) NOT NULL ,
 salary money DEFAULT 4000 CHECK (salary BETWEEN 0 AND 10000),
 phoneNumber varchar(25) UNIQUE ,
 email varchar(25) CHECK (email LIKE '%@gmail.com'))

 INSERT INTO employees
 VALUES (1, 'Moshe' , 9999 , '03-5555555' , 'moshe@walla.co.il')

 INSERT INTO employees
 VALUES (1, 'Moshe' , 9999 , '03-5555555' , 'moshe@gmail.com')

 -- FOREIGN KEY 

 DROP TABLE employees 

 CREATE TABLE departments
 (department_id int PRIMARY KEY,
  department_name varchar(25))

  INSERT INTO departments
  VALUES (50 , 'IT')

  INSERT INTO departments
  VALUES (60 , 'HR')

  INSERT INTO departments
  VALUES (70 , 'SA')

 CREATE TABLE employees
(employeeID int PRIMARY KEY  ,
 lastName varchar(25) NOT NULL ,
 salary money DEFAULT 4000 CHECK (salary BETWEEN 0 AND 10000),
 phoneNumber varchar(25) UNIQUE ,
 email varchar(25) CHECK (email LIKE '%@gmail.com'),
 department_id int REFERENCES departments (department_id))

 INSERT INTO employees
 VALUES (1, 'Moshe' , 9999 , '03-5555555' , 'moshe@gmail.com' , 50 )

 INSERT INTO employees
 VALUES (2, 'Yossi' , 9999 , '03-7777777' , 'yossi@gmail.com' , 999 )

 DELETE FROM departments
 WHERE department_id = 50 

 DELETE FROM departments
 WHERE department_id = 60

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…