Total members 11870 |It is currently Wed May 25, 2022 8:24 am Login / Join Codemiles

Java

C/C++

PHP

C#

HTML

CSS

ASP

Javascript

JQuery

AJAX

XSD

Python

Matlab

R Scripts

Weka





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;




_________________
Recommend my post if you found it helpful.


Author:
Newbie
User avatar Posts: 15
Have thanks: 0 time
Post new topic Reply to topic  [ 1 post ] 

  Related Posts  to : DDL Statements-Adding Constraints-CREATE-ALTER-TABLE
 Types of Databases and statements, Create, Alter, delete     -  
 adding caption to table in html     -  
 SQL ALTER Command     -  
 For statement without all three statements     -  
 java statements     -  
 translate XQuery to SQL statements     -  
 DML Statements, insert row, delete row     -  
 Need automatic adding to catalogs     -  
 Adding a New Node to contact.xml     -  
 adding padding to your text     -  









Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
All copyrights reserved to codemiles.com 2007-2011
mileX v1.0 designed by codemiles team
Codemiles.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to Amazon.com