DBMS LABORATORY
Group A
Assignment No: 2
Class: T.E.Computer RollNo:
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:
SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL is an ANSI (American National Standards Institute) standard
Commands of SQL are grouped into four languages.
1>DDL
DDL is abbreviation ofData 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
JES’ITMR
DBMS LABORATORY
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:
CREATE TABLE- Creates a new table
ALTER TABLE- Modifies a table
DROP TABLE- Deletes a table
TRUNCATE -Use to truncate (delete all rows) a table.
CREATE INDEX- Creates an index (search key)
DROP INDEX- Deletes an index
1.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:
JES’ITMR
DBMS LABORATORY
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
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;
2.The Drop TABLE Statement
Removes the table from the database
Syntax
DROP TABLE table_name;
3. 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;
4. The RENAME TABLE Statement
JES’ITMR
DBMS LABORATORY
Rename the old table to new table;
Syntax
Rename old_tabname to new_tabname;
5. 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;
6. 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;
7. SQL Dropping a View
You can delete a view with the DROP VIEW command.
Syntax
DROP VIEW view_name;
8 . 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.
JES’ITMR
DBMS LABORATORY
column_name1, column_name2.. is the list of columns which make up the INDEX.
9. Drop Index Statement
Syntax
DROP INDEX index_name;
10. 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:
JES’ITMR
