B.E.
(E&TC) – 2019 Pattern Database Management Lab
Group: A
Experiment No.2
Design and develop at SQL DDL statements which demonstrate the user of SQL objects
such as Table, View, Index, Sequence and Synonym.
Roll Number
Date of Performance
AIM: Design and develop at SQL DDL statements which demonstrate the user of SQL objects
such as Table, View, Index, Sequence and Synonym.
OBJECTIVES: To understand and demonstrate DDL statements on various SQL objects.
SOFTWARE/TOOLS: MySQL Server, MySQL Workbench, and Command Line Client.
THEORY:
The MySQL language is subdivided according to their functions as follows
DDL - Data Definition Language
DML - Data Manipulation Language
DCL - Data Control Language
TCL - Transaction Control
5
B.E. (E&TC) – 2019 Pattern Database Management Lab
A. Data Definition Language (DDL): Data Definition Language (DDL) or Schema Definition
Language, statements are used to define the database structure or schema.
1. CREATE - to create objects in the database
2. ALTER - alters the structure of the database
3. DROP - delete objects from the database
4. TRUNCATE - remove all records from a table, including all spaces allocated for the records are
Removed.
5. COMMENT - add comments to the data dictionary
6. RENAME - rename an object
B. Data Manipulation Language (DML): Data Manipulation Language (DML) statements are used
for managing data within schema objects.
1. INSERT - insert data into a table
2. UPDATE - updates existing data within a table
3. DELETE - deletes all records from a table, the space for the records remain
4. MERGE UPSET operation (insert or update) CALL - call a PL/SQL or Java subprogram
5. EXPLAIN PLAN - explain access path to data
6. LOCK TABLE - control concurrency Data Retrieval Language / Data Query Language
(DRL/DQL): SELECT - retrieve data from a database
C. Data Control Language (DCL): Data Control Language (DCL) statements.
Some examples:
1. GRANT - gives user's access privileges to database
2. REVOKE - withdraw access privileges given with the GRANT command
D. Transaction Control (TCL): Transaction Control (TCL) statements are used to manage the
changes made by DML statements. It allows statements to be grouped together into logical
transactions.
1. COMMIT save work done
2. SAVEPOINT identify a point in a transaction to which you can later roll back
3. ROLLBACK - restore database to original since the last
4. COMMIT SET TRANSACTION - Change transaction options like isolation level and what rollback
segment to use
View
View is a data object which does not contain any data. Contents of the view are the resultant of
6
B.E. (E&TC) – 2019 Pattern Database Management Lab
base table. They are operated just like base table but they don't contain any data of their own. The
difference between a view and a table is that views are definitions built on top of other tables (or
views). If data is changed in the underlying table, the same change is reflected in the view. A view
can be built on
top of a single or multiple table.
Advantages of database view
The following are advantages of using database views.
A database view allows you to simplify complex queries
A database view helps limit data access to specific users.
A database view provides extra security layer.
A database view enables computed columns
Syntax for create table
CREATE TABLE table_name (column_namecolumn_type);
Syntax for create view
CREATE [OR REPLACE] VIEW view_name [(column_list)] AS select_statement;
Example
CREATE VIEW my_v1 AS SELECT * FROM user_details;
Indexes:
A database index is a data structure that improves the speed of operations in a table. Indexes can
be created using one or more columns, providing the basis for both rapid random lookups and
efficient ordering of access to records. While creating index, it should be considered that what the
columns are? This will be used to make SQL queries and create one or more indexes on those
columns. Practically, indexes are also type of tables, which keep primary key or index field and a
pointer to each record into the actual table. The users cannot see the indexes;
they are just used to speed up queries and will be used by Database Search Engine to locate
records very fast.
INSERT and UPDATE statements take more time on tables having indexes whereas SELECT
statements become fast on those tables. The reason is that while doing insert or update. database
need to insert or update index values as well.
Simple and Unique Index: You can create a unique index on a table. A unique index means that two
rows cannot have the same index value. Here is the syntax to create an Index on a table
7
B.E. (E&TC) – 2019 Pattern Database Management Lab
Syntax
CREATE UNIQUE INDEX index_name ON table_name(column1, column2,...);
SQL Sequence
Sequence is a feature supported by some database systems to produce unique values on demand.
Some DBMS like MySQL supports AUTO_INCREMENT in place of Sequence. AUTO_INCREMENT is
applied on columns; it automatically increments the column value by 1 each time a new record is
entered into the table. Sequence is also somewhat similar to AUTO_INCREMENT but it has some
extra features.
Creating Sequence
Syntax to create sequences is,
CREATE Sequence sequence-name start with initial-value increment by increment-
Value max value maximum -value cycle|nocycle;
initial-value specifies the starting value of the Sequence, increment-value is the value by which
sequence will be incremented and maxvalue specifies the maximum value until which sequence
will increment itself. cycle specifies that if the maximum value exceeds the set limit, sequence will
restart its cycle from the beginning. No cycle specifies that if sequence exceed maxvalue an error
will be thrown.
Example to create Sequence
Create table student(roll_no int auto_increment,primary key(roll_no),name varchar(10),branch
varchar(10));
Insert values in table:
Insert into student (name,branch)values('pooja', 'computer');
CONCLUSION:
QUESTIONS:
1. What are the DDL commands?
2. What is the differences between drop and truncate?
3. What is the need of view?
8
B.E. (E&TC) – 2019 Pattern Database Management Lab
REFERENCES:
[1] Database System Concepts 7th Edition Avi Silberschartz, Henry F. Korth, S Sudarshan Mc Graw
Hill Publication. Chapter- 03, Page No-65-111
Marks (Out of 20) Signature of Faculty with Date
MR (6) MP (6) MU (8) Total (20)
MR – Marks for Regularity, MP – Marks for Presentation, MU – Marks for
Understanding
