SQL and DBMS

www.android.universityupdates.in | www.universityupdates.in | https://telegram.me/jntuh




UNIT – III
SQL: QUERIES, CONSTRAINTS, TRIGGERS: form of basic SQL query, UNION, INTERSECT,
and EXCEPT, Nested Queries, aggregation operators, NULL values, complex integrity constraints in
SQL, triggers and active databases. Schema Refinement: Problems caused by redundancy,
decompositions, problems related to decomposition, reasoning about functional dependencies, FIRST,
SECOND, THIRD normal forms, BCNF, lossless join decomposition, multi-valued dependencies,
FOURTH normal form, FIFTH normal form.


1. SQL COMMANDS
Structured Query Language (SQL) is the database language used to create a database and
to perform operations on the existing database. SQL commands are instructions used to
communicate with the database to perform specific tasks and queries with data. These SQL
commands are categorized into five categories as:

i. DDL: Data Definition Language
ii. DML: Data Manipulation Language
iii. DQL: Data Query Language
iv. DCL : Data Control Language
v. TCL : Transaction Control Language.

SQL commands



DDL DML DQL DCL TCL
Data Definition Data Manipulation Data Query Data Control Transaction
Language Language Language Language Control Language



CREATE INSERT GRANT COMMIT
SELECT

ALTER DELETE REVOKE ROLLBACK


DROP SAVEPOINT
UPDATE

TRUNCATE


i. DDL(Data Definition Language) : DDL or Data Definition Language consists of the
SQL commands that can be used to define the database schema. It simply deals with
descriptions of the database schema and is used to create and modify the structure of
database objects in the database. The DQL commands are:



Ravindar.M, Asso.Prof, CSE Dept, JITS-KNR


www.android.previousquestionpapers.com | www.previousquestionpapers.com | https://telegram.me/jntuh

, www.android.universityupdates.in | www.universityupdates.in | https://telegram.me/jntuh




 CREATE: It is used to create the database or its objects (like table, index, function,
views, store procedure and triggers).
 DROP: It is used to delete objects from the database.
 ALTER: It is used to alter the structure of the database.
 TRUNCATE: It is used to remove all records from a table, including all spaces
allocated for the records are removed.
ii. DQL (Data Query Language): DML statements are used for performing queries on the
data within schema objects. The purpose of DQL Command is to get data from some schema
relation based on the query passed to it. The DQL commands are:
 SELECT – is used to retrieve data from the database.

iii. DML (Data Manipulation Language): The SQL commands that deals with the
manipulation of data present in the database belong to DML or Data Manipulation Language
and this includes most of the SQL statements. The DML commands are:

 INSERT – is used to insert data into a table.
 UPDATE – is used to update existing data within a table.
 DELETE – is used to delete records from a database table.
iv. DCL (Data Control Language): DCL includes commands which mainly deal with the
rights, permissions and other controls of the database system. The DCL commands are:
 GRANT-gives user’s access privileges to database.
 REVOKE-withdraw user’s access privileges given by using the GRANT command.
v. TCL (transaction Control Language): TCL commands deals with the transaction
within the database. The TCL commands are:
 COMMIT– commits a Transaction.
 ROLLBACK– rollbacks a transaction in case of any error occurs.
 SAVEPOINT–sets a save point within a transaction.


2. DDL COMMANDS
DDL or Data Definition Language consists of the SQL commands that can be used to define
the database schema. It simply deals with descriptions of the database schema and is used to
create and modify the structure of database objects in the database. The DQL commands are:



Ravindar.M, Asso.Prof, CSE Dept, JITS-KNR


www.android.previousquestionpapers.com | www.previousquestionpapers.com | https://telegram.me/jntuh

, www.android.universityupdates.in | www.universityupdates.in | https://telegram.me/jntuh




i. CREATE: It is used to create the database or its objects like table, index, function, views,
store procedure and triggers.

a) The ‘CREATE DATABASE’ Statement: This statement is used to create a database.

Syntax: CREATE DATABASE Database_Name;

Example: CREATE DATABASE Employee;

It creates Employee database.

b) The ‘CREATE TABLE’ Statement: This statement is used to create a table.
Syntax:
CREATE TABLE TableName (
Column1 datatype(size)[column_constraint],
Column2 datatype(size)[column_constraint],
....
ColumnN datatype(size)[column_constraint],
[table_constraint]
[,table_constraint]
);

Note: The content in the square brackets indicates it is optional. If not required, you can skip it.

Column constraints

o PRIMARY KEY // Use only, If one column name as primary key.
o NOT NULL // It does not accept NULL value in that column.
o DEFAULT value // It store default value in that column, if no value is inserted
o UNIQUE // It allows to store only unique values in the column


Table constraints

o PRIMARY KEY(column_name1, column_name2, …)
Use it, If one column name or multiple column names acts as primary key.
o UNIQUE(column_name1, column_name2, …)
Use it, if one column name or multiple column names should contain unique values.
If multiple column names are used, then for each row, it consider values from all the columns
mentioned to decide the uniqueness, but not column wise.
o FOREIGN KEY (column_name1) REFERENCES other_table_name (column_name2)
It is used to link data from one table to other table.
o CHECK(condition)
It does not allow inserting value(s), if the condition is not satisfied. The condition may also
contain multiple column names.




Ravindar.M, Asso.Prof, CSE Dept, JITS-KNR


www.android.previousquestionpapers.com | www.previousquestionpapers.com | https://telegram.me/jntuh

, www.android.universityupdates.in | www.universityupdates.in | https://telegram.me/jntuh




Example 1: Creating table without any constraints
CREATE TABLE Employee_Info
(
EmployeeID int,
EmployeeName varchar(20),
PhoneNumber numeric(10),
City varchar(20),
Country varchar(20)
);
Example 2: Using PRIMARY KEY and NOT NULL as column constraints
CREATE TABLE Departments
(
DeptID int PRIMARY KEY,
DeptName varchar(20)NOT NULL,
Hod varchar(20),
Location varchar(20)
);
Example 3: Using PRIMARY KEY, NOT NULL, UNIQUE and DEFAULT as column constraints and FOREIGN
KEY as table constraint.
CREATE TABLE Students_Info
(
HallTicketNo int PRIMARY KEY,
Name varchar(20)NOT NULL,
Mobile numeric(10)NOT NULL UNIQUE,
DepartmentID int,
City varchar(20)DEFAULT ‘Hyderabad’,
FOREIGN KEY(DepartmentID) REFERENCES Departments (DeptID)
);

Example 4: Using NOT NULL, UNIQUE as column constraints and PRIMARY KEY and CHECK as table
constraints.
CREATE TABLE Voter_list
(
VoterID numeric(10),
AdhaarNo numeric(12)NOT NULL UNIQUE,
Name varchar(20)NOT NULL,
Age int,
Mobile numeric(10) UNIQUE,
City varchar(20),
PRIMARY KEY(VoterID),
CHECK(AGE>18)
);

c) The ‘CREATE TABLE AS’ Statement: You can also create a table from another
existing table. The newly created table also contains data of existing table.
Syntax: CREATE TABLE NewTableName AS(SELECT Column1, column2, ..., ColumnN
FROM ExistingTableName
WHERE [condition]);

Example: CREATE TABLE ExampleTable AS ( SELECT EmployeeName, PhoneNumber
FROM Employee_Info );




Ravindar.M, Asso.Prof, CSE Dept, JITS-KNR


www.android.previousquestionpapers.com | www.previousquestionpapers.com | https://telegram.me/jntuh