Step-5- in SQl-server2000

Page 1 of 1

[ 1 post ]

Author

Question

msi_fci

Question subject: Step-5- in SQl-server2000

Posted: Sat Apr 07, 2007 7:21 pm

Joined: Thu Apr 05, 2007 9:57 pm
Posts: 17
Has thanked: 0 time
Have thanks: 0 time

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

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 :

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

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

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

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

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

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

Code:

DROP VIEW SUPP_FOR_PROD;

Page 1 of 1

[ 1 post ]

Similar topics
Topic title	Forum	Author	Comments
unit step and impulse function L.T	General Discussion	loomy	0
(MS Press) Microsoft Visual CSharp 2008 - Step By Step	offers	Casper	0
Cisco - Press - Configuring CallManager and Unity A Step-by-	offers	Casper	0
TCP IP First Step	offers	Casper	0
Cisco Press - Routing First-Step 2004	offers	Casper	0

Codemiles.com » Database Forums » SQL Server

All times are UTC [ DST ]

Users browsing similar posts

Users browsing this forum: No registered users and 1 guest

Jump to:

Previous Question | Next Question