Data Definition Language – DDL

Create table שם טבלה
(
Column_name data_type [DEFAULT] [IDENTITY] [CONSTRAINT]
[,column_name….]
)

CREATE TABLE [Order Details] 
( 
    OrderID INT ,
    ProductID INT ,
    UnitPrice MONEY, 
    Quantity SMALLINT (1),
    Discount REAL 
)

Create table שם טבלה
SELECT */column_name
INTO new_table_name
FROM table_name
[WHERE condition]

…Quantity SMALLINT DEFAULT (1)

…Id_num int identity(1,1)

ALTER TABLE customers ADD phone nvarchar(10)

ALTER TABLE customers DROP COLUMN phone

Drop table שם טבלה

CREATE TABLE [Order Details] 
( 
    OrderID int CONSTRAINT ord_det_ord_id_nn NOT NULL,
    ProductID int NOT NULL,
    UnitPrice money NOT NULL DEFAULT (0), 
    Quantity smallint NOT NULL DEFAULT (1),
    Discount real NOT NULL DEFAULT (0), 
    PRIMARY KEY (OrderID,ProductID), 
    FOREIGN KEY (OrderID) REFERENCES Orders,
    FOREIGN KEY(ProductID) REFERENCES Products,
    CHECK (Discount >= 0 and Discount <= 1),
    CHECK (Quantity > 0), 
    CHECK (UnitPrice >= 0) 
)

ALTER TABLE [schema.]table
[ADD / DROP / RENAME]

-- הוספת אילוץ
ADD [CONSTRAINT constraint_nick_name] constraint_type (column)

-- מחיקת אילוץ
DROP PRIMARY KEY [CASCADE] 
DROP UNIQUE (column,...) 
DROP CONSTRAINT constraint_name [CASCADE]

-- ex2
Create table employees
(
    Employee_id int PRIMARY KEY identity(1,1),
    Last_name VARCHAR(20) NOT NULL,
    Hire_date DATETIME DEFAULT(GETDATE()) NOT NULL,
    Salary MONEY DEFAULT(5000),
    Email VARCHAR(30) UNIQUE NULL
)

Create table departments
(
    department_id int PRIMARY KEY identity(10,10),
    department_name VARCHAR(20) NOT NULL,
    location VARCHAR(30) NOT NULL
)

-- ex3
ALTER TABLE employees 
ADD department_id int FOREIGN KEY (department_id) REFERENCES departments

-- ex4
SELECT  *
INTO emp_history
FROM employees

-- ex5
Drop table emp_history