DDL Statements-Adding Constraints-CREATE-ALTER-TABLE

DDL Statements(Advanced)


1. Adding Constraints

Example 1:
( Adding Constraint(s) in Table Creation Time )

Create a table called DEPT with DEPT_ID as primary key and DEPT_NAME must be a value from IS , CS , IT or DS.

sql code

CREATE TABLE DEPT
(
  DEPT_ID int Primary Key,
  DEPT_NAME varchar(2) Unique,
  DESCRIPTION varchar(100) Not Null,
  Constraint DEPT_NAME_CK Check (DEPT_NAME in ('CS','IT','IS','DS')) 
);

BETTER STATEMENT THAT DOES THE SAME JOB OF THE PREVIOUS ONE :

sql code

CREATE TABLE DEPT
(
  DEPT_ID int,
  DEPT_NAME varchar(2),
  DESCRIPTION varchar(100) Constraint DESCRIPTION_NN Not Null,    
  Constraint DEPT_ID_PK Primary Key (DEPT_ID),  			       
  Constraint DEPT_NAME_UK Unique (DEPT_NAME),		           
  Constraint DEPT_NAME_CK Check (DEPT_NAME in ('CS','IT','IS','DS'))        
);

Example 2:
( Adding Foreign Key Constraint(s) in Table Creation Time )

Create a table called STUDENT with STUD_ID as primary key and STUD_NAME that can't accept null values and a DEPT_ID column as a forign key to DEPT_ID in DEPT table.

sql code

CREATE TABLE STUDENT
(
  STUD_ID int Primary Key,
  STUD_NAME varchar(30) Not Null,
  DEPT_ID INT,
  Constraint STUDENT_DEPT_ID_FK Foreign Key (DEPT_ID) References
  DEPT (DEPT_ID)        		 
);

Example 3:
( Adding A Constraint After Table Creation Time )

Add a unique constraint on STUD_NAME column after creating table STUDENT as in the previous example


sql code

ALTER TABLE STUDENT
ADD CONSTRAINT STUD_NAME_UK UNIQUE ( STUD_NAME);

2.Dropping Constraints

Example :( Removing a constraint by its name )

Remove the constraint called STUD_NAME_UK from STUDENT table

Code:

ALTER TABLE STUDENT
DROP CONSTRAINT STUD_NAME_UK;

3.Adding Indexes

Example :

Add an index on column STUD_NAME IN STUDENT table.

sql code

CREATE INDEX STUD_NAME_INDEX
ON STUDENT(STUD_NAME);

4.Dropping Indexes

Example :
( Removing an index by its name )

Remove the index called STUD_NAME_INDEX from STUDENT table

Code:

DROP INDEX STUDENT.STUD_NAME_INDEX;

5.Creating Views

Example 1:

Create a view called SALES_RER that conatins all information of employees working as sales representatives.

sql code

CREATE VIEW SALES_REP AS
SELECT *
FROM employees
WHERE Title  = 'Sales Representative'

Example 2:

Create a view called SUPP_FOR_PROD that contains the names, prices and supplier names for each product


sql code

CREATE VIEW SUPP_FOR_PROD AS
SELECT  Products.ProductName,Suppliers.ContactName,Products.UnitPrice
FROM Products, Suppliers
WHERE Products.SupplierID = Suppliers.SupplierID

6.Dropping Views

Example :
( Removing a view by its name )

Remove the view called SUPP_FOR_PROD

sql code

DROP VIEW SUPP_FOR_PROD;