Group A
Assignment No: 2
Title: Design and Develop SQL DDL statements which demonstrate the use of SQL
objects such as
Table , View ,Index ,Synonym.
Learning Objective:
To learn all type Data Definition Language commands and their uses.
Introduction to SQL:
a. SQL stands for Structured Query Language
b. SQL lets you access and manipulate databases
c. SQL is an ANSI (American National Standards Institute) standard
Commands of SQL are grouped
into four languages. 1>DDL
DDL is abbreviation of Data Definition Language. It is used to create and
modify the structure of database objects in database.
Examples: CREATE, ALTER, DROP,RENAME, TRUNCATE statements
2>DML
DML is abbreviation of Data Manipulation Language. It is used to retrieve,
store, modify, delete, insert and update data in database.
Examples: SELECT, UPDATE, INSERT, DELETE statements
3>DCL
DCL is abbreviation of Data Control Language. It is used to create roles, permissions,
and referential integrity as well it is used to control access to database by securing it.
Examples: GRANT, REVOKE statements
4>TCL
TCL is abbreviation of Transactional Control Language. It is used to manage different
transactions occurring within a database.
Examples: COMMIT, ROLLBACK statements
Data Definition Language (DDL)
1. Data definition Language (DDL) is used to create, rename, alter, modify, drop,
replace, and delete tables, Indexes, Views, and comment on database objects; and
establish a default database.
2. The DDL part of SQL permits database tables to be created or deleted. It also
define indexes (keys), specify links between tables, and impose constraints between
tables. The most important DDL statements in SQL are:
1. CREATE TABLE- Creates a new table
2. ALTER TABLE- Modifies a table
3. DROP TABLE- Deletes a table
4. TRUNCATE -Use to truncate (delete all rows) a table.
5. CREATE INDEX- Creates an index (search key)
6. DROP INDEX- Deletes an index
• The CREATE TABLE Statement
The CREATE TABLE statement is used to create a table in a database.
Syntax
CREATE TABLE tablename
(attr1_name attr1_datatype(size) attr1_constraint,
attr2_name attr2_datatype(size) attr2_constraint,….);
SQL Constraints
Constraints are used to limit the type of data that can go into a table.
Constraints can be specified when a table is created (with the CREATE TABLE
statement) or after the table is created (with the ALTER TABLE statement).
We will focus on the following constraints:
1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY
5. CHECK
6. DEFAULT
Add constraint after table creation using alter table option
Syntax - Alter table add constraint constraint_name constraint_type(Attr_name)
Example - Alter table stud add constraint
prk1 primary key(rollno);
Drop constraint:
Syntax
Drop Constraint Constraint_name; Example - Drop constraint prk1;
The Drop TABLE Statement Removes the table from the database
Syntax
DROP TABLE table_name;
The ALTER TABLE Statement
The ALTER TABLE statement is used to add, delete, or modify columns in an
existing table.
Syntax
To add a column in a table, use the following syntax:
ALTER TABLE
table_name ADD
column_name
datatype;
To delete a column in a table, use the following syntax (notice that some database
systems don't allow deleting a column):
ALTER TABLE table_name DROP COLUMN column_name;
To change the data type of a column in a table, use
the following syntax: ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
The RENAME TABLE Statement
Rename the old table to new table;
Syntax
Rename old_tabname to new_tabname;
The TRUNCATE TABLE Statement
The ALTER TABLE Statement is used to truncate (delete all rows) a table.
Syntax
To truncate a table, use following syntax : TRUNCATE TABLE table_name;
CREATE VIEW Statement
In SQL, a view is a virtual table based on the result-set of an SQL statement.A view
contains rows and columns, just like a real table. The fields in a view are fields
from one or more real tables in the database.
Syntax
CREATE VIEW
view_name AS
SELECT
column_name(s)
FROM table_name
WHERE condition;
SQL Dropping a View
You can delete a view with the DROP VIEW command.
Syntax
DROP VIEW view_name;
Create Index Statement
1. Index in SQL is created on existing tables to retrieve the rows quickly. When
there are thousands of records in a table, retrieving information will take a
long time.
2. Therefore indexes are created on columns which are accessed frequently, so
that the information can be retrieved quickly.
3. Indexes can be created on a single column or a group of columns. When a
index is
4. created, it first sorts the data and then it assigns a ROWID for each row.
Syntax
CREATE INDEX index_name
ON table_name (column_name1, column_name2...);
index_name is the name of the INDEX.
table_name is the name of the table to which the indexed column belongs.
column_name1, column_name2.. is the list of columns which make up the INDEX.
Drop Index Statement
Syntax
DROP INDEX index_name;
Create Synonym statement
1. Use the CREATE SYNONYM statement to create a synonym, which is an
alternative name for a table, view, sequence, procedure, stored function, package,
materialized view.
2. Synonyms provide both data independence and location transparency. Synonyms
permit applications to function without modification regardless of which user owns
the table or view and regardless of which database holds the table or view.
3. You can refer to synonyms in the following DML statements: SELECT, INSERT,
UPDATE, DELETE
Syntax - Create synonym synonym-name for object-name;
Example-Create synonym synonym_name for table_name
Create synonym t for test
1. SQL Dropping a View
You can delete a view with the DROP VIEW command.
Syntax
DROP VIEW view_name;
2. Create Index Statement
1. Index in SQL is created on existing tables to retrieve the rows quickly. When there are thousands of records in a
table, retrieving information will take a long time.
2. Therefore indexes are created on columns which are accessed frequently, so that the information can be
retrieved quickly.
3. Indexes can be created on a single column or a group of columns. When a index is created, it first sorts the
data and then it assigns a ROWID for each row.
Syntax
CREATE INDEX index_name
ON table_name (column_name1,column_name2...);
index_name is the name of the INDEX.
table_name is the name of the table to which the indexed column belongs.
column_name1, column_name2.. is the list of columns which make up the INDEX.
3. Drop Index Statement
Syntax
DROP INDEX index_name;
4. Create Synonym statement
1. Use the CREATE SYNONYM statement to create a synonym, which is an alternative name for a table, view,
sequence, procedure, stored function, package, materialized view.
2. Synonyms provide both data independence and location transparency. Synonyms permit applications to
function without modification regardless of which user owns the table or view and regardless of which
database holds the table or view.
3. You can refer to synonyms in the following DML statements: SELECT, INSERT, UPDATE, DELETE
Syntax - Create synonym synonym-name for object-name;
Example-Create synonym synonym_name for table_name
Create synonym t for test
Conclusion:
