Department of Computer Science and Engineering
Database Management System
Code: 21CS53
5th SEM
By,
Dr. Deepak
N. A.
Dept of CSE,
RVITM, Bengaluru
SQL, Advances
Queries, Application
Development
Module - 3
Structured Query
Language
SQL Data Definition
SQL is an database language by which we can
perform certain operations on the existing
database. To create a database, SQL uses
certain commands like CREATE, DROP, INSERT,
etc.
DDL – Data Definition Language
DDL consists of the SQL commands that can be used to
define the database schema.
It deals with descriptions of the database schema and
is used to create and modify the structure of database
objects in the database.
DDL is a set of SQL commands used to create, modify,
and delete database structures but not data.
List of DDL commands:
CREATE: This command is used to create the database or its
objects (like table, index, function, views, store procedure,
and triggers).
DROP: This command is used to delete objects from the
database.
ALTER: This is used to alter the structure of the database.
TRUNCATE: This is used to remove all records from a table,
including all spaces allocated for the records are removed.
COMMENT: This is used to add comments to the data
dictionary.
DQL (Data Query Language)
DQL statements are used for performing queries on
the data within schema objects. The purpose of the
DQL Command is to get some schema relation based
on the query passed to it.
List of DQL:
SELECT: It is used to retrieve data from the database.
DML(Data Manipulation Language)
The SQL commands that deal with the manipulation
of data present in the database belong to DML or
Data Manipulation Language.
This includes most of the SQL statements. It is the
component of the SQL statement that controls
access to data and the database.
List of DML commands:
INSERT: It is used to insert data
into a table.
UPDATE: It is used to update
existing data within a table.
DELETE: It is used to delete
records from a database table.
DCL (Data Control Language)
DCL- Consists of statements that
control security and concurrent
access to table data.
List of DCL commands:
GRANT: This command gives users
access privileges to the database.
Syntax:
GRANT SELECT, UPDATE ON MY_TABLE TO SO
ME_USER, ANOTHER_USER;
REVOKE: This command withdraws
the user’s access privileges given
by using the GRANT command.
Syntax:
REVOKE SELECT, UPDATE ON MY_TABLE FRO
M USER1, USER2;
TCL (Transaction Control Language)
TCL instructions are used to
handle database transactions.
These are used to keep track of
the modifications that DML
statements make.
List of TCL commands:
COMMIT: Commits a Transaction.
ROLLBACK: Rollbacks a transaction
in case of any error occurs.
SAVEPOINT: Sets a save point within
a transaction.
More Complex SQL Retrieval Queries
Additional features allow users to specify more
complex retrievals from database:
Nested queries,
Joining tables,
Aggregate functions, and Grouping
1. Nested queries: A nested
query is a complete query
embedded within another
operation. A nested query can
have all the elements used in a
regular query, and any valid
query can be embedded within
another operation to become a
nested query.
Nested queries: - Example
Nested queries: - Example
Nested Queries (cont’d.)
Nested Queries (cont’d.)
Joining tables
A JOIN clause is used to combine rows from two
or more tables, based on a related column
between them.
Inner join
Inner Join - Example
Inner Join - Example
Aggregate functions in SQL
Grouping functions in SQL
Grouping functions in SQL
Grouping functions in SQL
Comparisons Involving NULL
Meanings of NULL
Unknown value
Unavailable or withheld value
Not applicable attribute
Specifying constraints as
assertions and action triggers
Assertions
When a constraint involves Two (or) more tables, the
table constraint mechanism is sometimes hard and
results may not come as expected.
To cover such situation SQL supports the creation of
assertions that are constraints not associated with
only one table.
Assertion statement should ensure a certain
condition will always exist in the database.
DBMS always checks the assertion whenever
modifications are done in the corresponding table
Assertions Contd…
Assertions Contd…
Triggers
A trigger is a database object that is associated with
the table, it will be activated when a defined action is
executed for the table. The trigger can be executed
when we run the following statements:
INSERT
UPDATE
DELETE
Triggers
Contd..
Views (Virtual Tables) in SQL
A view is a virtual table whose
contents are defined by a query.
Like a table, a view consists of a
set of named columns and rows of
data.
Simple View and Complex View: Simple
views can only contain a single base table.
Complex views can be constructed on more
than one base table.
Views (Virtual Tables) in SQL
Schema change
statements in SQL
Schema change statements are used to make the
alteration to the existing database. The schema
change statements are defined using the structured
query language i.e. SQL.
Alter Command in SQL
The ALTER TABLE command adds, deletes, or
modifies columns in a table. The ALTER TABLE
command also adds and deletes various
constraints in a table.
Alter Command in SQL
End
Module - 3