19Z411 DATABASE MANAGEMENT
SYSTEMS LABORATORY
LIST OF EXPERIMENTS:
1. Practice Data Definition Language (DDL) and Data Manipulation
Language (DML) commands.
2. Database design using E-R model – case study.
3. Implement Views and stored procedures.
4. Implement functions, Cursors and Triggers.
5. Study assignment on performance monitoring and tuning Tools.
6. Mini project- Application development.
Couse Outcome
Upon completion of this course, the students will
be able to
CO1: Use the fundamental concepts of SQL, PL/SQL
to design solutions for experiments and study
performance monitoring and tuning Tools.
CO2: Develop, debug, test and document the
designed solutions.
SQL
Topics to be Covered
• Overview of SQL Query Language
• DDL
• DML
• TCL
• Basic Structure of SQL Queries
• Basic Operations built in function
• Aggregate Functions
• Group by and having
• Joins
• Nested Subqueries
What is SQL?
• SQL stands for Structured Query Language
• SQL lets you access and manipulate databases
• SQL is an ANSI (American National Standards
Institute) standard
• SQL works with database programs like MS
Access, DB2, Informix, MS SQL Server, Oracle,
Sybase, etc.
What Can SQL do?
• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and
views
Datatypes
• char
• varchar(size)
• int
• float
• number(precision,scale)
• date – ‘13-NOV-92’
Topics to be Covered
• Overview of SQL Query Language
• DDL
• DML
• TCL
• Basic Structure of SQL Queries
• Basic Operations built in function
• Aggregate Functions
• Group by and having
• Joins
• Nested Subqueries
DDL Commands
• Data Definition Language
– database schemas and descriptions.
– how the data should reside in the database.
• Various DDL Commands available
– Drop
– Rename Remember:
– Create Dr.Cat
– Alter
– Truncate
Create Table
• Collection of related
data Eg:
– Rows – Tuples(data) create table
– Columns – Attributes student
• Syntax: (
create table t_name Id int primary key,
( Name varchar(50),
Column_name1 datatype, Age int
Column_name2 datatype );
);
To view the schema of a table
• Syntax:
describe t_name; or
desc t_name
• Eg:
describe student;
desc student;
Alter Command
• alteration of table structures.
• There are various uses of alter command
– to add a column to existing table
– to rename any existing column
– to change datatype of any column or to
modify its size.
– alter is also used to drop a column.
Add a single Column to existing table
Syntax:
alter table t_name add(column-name datatype);
Eg:
alter table student add(address varchar(50));
Add Multiple Columns to the existing
table
Syntax: • Eg:
alter table table-name alter table student add
add (father_name varchar(60),
(column-name1 mother_name varchar(60),
datatype1, dob date
column-name2 );
datatype2,
column-name3 datatype3
);
Modify an existing column
• Syntax:
alter table t_name modify(column-name
datatype);
Eg:
alter table student modify(address
varchar(30));
Rename a Column
Syntax:
alter table t_name rename column
old-column-name to column-name;
Eg:
alter table student rename column address
to Location;
Drop a Column
Syntax:
alter table table-name drop(column-name);
Eg:
alter table student drop(Location);
Rename Command
• Rename a table
Syntax:
rename old-table-name to new-table-name;
Eg:
rename student to student_record;
Truncate Command
• Removes all records from a table.
• But this command will not destroy the table's
structure.
• When we apply truncate command on a table its
Primary key is initialized.(1)
• Implicitly preceded and followed by a commit
operation.
• Records removed this way cannot be restored in a
rollback operation.
Syntax: truncate table table-name
Eg: truncate table student;
drop Command
• Removes a table from database.
– drop table table-name;
– Eg: drop table student;
Exercise 1
1. Write a SQLstatement to create a simple table
countries including columns country_id,country_name and
region_id.
2. Add a column population to the country table.
3. Write a SQL statement to create a simple table students including
columns student_id, student_name and dept.
4. Add a column date of join to the students table(use data type as
date)
5. Write a SQLstatement to create a simple table
employees including columns emp_id, emp_name and
designation.
6. Add a column salary to the employees table (use datatype as
decimal).
7. Drop all three tables.
