Download SQL: Queries, Constraints, Triggers - A Comprehensive Guide - Prof. Babu and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity!
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.
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:
SQL commands
DDL
Data Definition Language
DML
Data Manipulation Language
DQL
Data Query Language
DCL
Data Control Language
TCL
Transaction Control Language
CREATE
ALTER
DROP
TRUNCATE
INSERT
DELETE
UPDATE
SELECT GRANT
REVOKE
COMMIT
ROLLBACK
SAVEPOINT
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:
Example 1: Creating table without any constraints
CREATE TABLE Employee_Info ( EmployeeID int, EmployeeName varchar(2 0 ), PhoneNumber numeric(10), City varchar(2 0 ), Country varchar(2 0 ) );
Example 2: Using PRIMARY KEY and NOT NULL as column constraints
CREATE TABLE Departments ( DeptID int PRIMARY KEY, DeptName varchar(2 0 )NOT NULL, Hod varchar(2 0 ), Location varchar(2 0 ) );
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(2 0 )NOT NULL, Mobile numeric( 10 )NOT NULL UNIQUE, DepartmentID int, City varchar(2 0 )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(2 0 )NOT NULL, Age int, Mobile numeric( 10 ) UNIQUE, City varchar(2 0 ), 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 );
ii. DROP: This statement is used to drop an existing table or a database.
a) The ‘DROP DATABASE’ Statement: This statement is used to drop an existing database. When you use this statement, complete information present in the database will be lost. Syntax: DROP DATABASE DatabaseName;.
Example: DROP DATABASE Employee;
b) The ‘DROP TABLE’ Statement: This statement is used to drop an existing table. When
you use this statement, complete information present in the table will be lost.
Syntax: DROP TABLE TableName;.
Example: DROP TABLE Employee;
iii. TRUNCATE: This command is used to delete the information present in the table but
does not delete the table. So, once you use this command, your information will be lost, but
not the table.
Syntax: TRUNCATE TABLE TableName;.
Example: TRUNCATE TABLE Employee_Info;
iv. ALTER: This command is used to add, delete or modify column(s) in an existing table. It
can also be used to rename the existing table and also to rename the existing column name.
a) The ‘ALTER TABLE’ with ADD column: You can use this command to add a new
column to the existing table.
Syntax: ALTER TABLE TableName ADD ColumnName Datatype;
Example: Adding Blood Group column to the Employee_Info table
ALTER TABLE Employee_Info ADD BloodGroup varchar( 10 );
b) The ‘ALTER TABLE’ with DROP column: You can use this command to remove a
column from the existing table.
Syntax: ALTER TABLE TableName DROP ColumnName; Example: Removing Blood Group column from the Employee_Info table
ALTER TABLE Employee_Info DROP BloodGroup;
Syntax: INSERT INTO TableName[(Column1, Column2,..., ColumnN)] VALUES (value1, value2,..., valueN);
Example1 :
INSERT INTO Employee_Info( EmployeeID, EmployeeName, PhoneNumber, City,Country)
VALUES ('06', 'Sanjana', '9921321141', 'Chennai', 'India');
Example2 : When inserting all column values as per their order in the table, you can omit column names.
INSERT INTO Employee_Info
VALUES ('07', 'Sayantini','9934567654', 'Pune', 'India');
ii. DELETE: This statement is used to delete the existing records in a table.
Syntax: DELETE FROM TableName WHERE Condition;
Example: DELETE FROM Employee_Info WHERE EmployeeName='Preeti';
Note: If where condition is not used in DELETE command, then all the rows data will be deleted. If used only rows which satisfies the condition are deleted.
iii. UPDATE: This statement is used to modify the record values already present in the table.
Syntax: UPDATE TableName SET Column1 = Value1, Column2 = Value2, ... [WHERE Condition];
Example:
UPDATE Employee_Info SET EmployeeName = 'Jhon', City= 'Ahmedabad' WHERE EmployeeID = 1;
Note: If where condition is not used in UPDATE command, then in all the rows Employee Name changes to 'Jhon' and City name changes to 'Ahmedabad'. If used only rows which satisfies the condition are updated.
4. DQL COMMAND: The purpose of DQL Command is to get data from one or more
tables based on the query passed to it.
i. SELECT: This statement is used to select data from a database and the data returned is stored in a result table, called the result-set.
Syntax: SELECT [DISTINCT] ***** / Column1,Column2,...ColumN FROM TableName [WHERE search_condition] [GROUP BY column_names [HAVING search_condition_for_GROUP_BY] [ORDER BY column_name ASC/DESC] ;
Example 1: SELECT * FROM table_name;
Example 2: SELECT EmployeeID, EmployeeName FROM Employee_Info;
The ‘SELECT with DISTINCT’ Statement: This statement is used to display only
different unique values. It mean it will not display duplicate values.
Example : SELECT DISTINCT PhoneNumber FROM Employee_Info;
The ‘ORDER BY’ Statement: The ‘ORDER BY’ statement is used to sort the required
results in ascending or descending order. The results are sorted in ascending order by
default. Yet, if you wish to get the required results in descending order, you have to use
the DESC keyword.
Example
/* Select all employees from the 'Employee_Info' table sorted by City */
SELECT * FROM Employee_Info ORDER BY City;
/*Select all employees from the 'Employee_Info' table sorted by City in Descending order */
SELECT * FROM Employee_Info ORDER BY City DESC;
/* Select all employees from the 'Employee_Info' table sorted by City and EmployeeName. First it sort the rows as per city, then sort by employee name */
SELECT * FROM Employee_Info ORDER BY City, EmployeeName;
/* Select all employees from the 'Employee_Info' table sorted by City in Descending order and EmployeeName in Ascending order: */
SELECT * FROM Employee_Info ORDER BY City ASC, EmployeeName DESC;
AVG( ) Function: The AVG function returns the average value of a numeric column that you choose.
Syntax: SELECT AVG(ColumnName) FROM TableName WHERE Condition; Example: SELECT AVG(Salary) FROM Employee_Salary;
The ‘GROUP BY’ Statement: This ‘GROUP BY’ statement is used with the aggregate functions to group the result-set by one or more columns.
Example:
-- To list the number of employees from each city.
SELECT COUNT(EmployeeID), City FROM Employee_Info GROUP BY City;
The ‘HAVING’ Clause: The ‘HAVING’ clause must be used SQL along with GROUP BY
clause only. It is similar to the WHERE clause.
Example
/ To list the number of employees in each city. The employees should be sorted high to low and only those cities must be included who have more than 5 employees:/
SELECT COUNT(EmployeeID), City FROM Employee_Info GROUP BY City HAVING COUNT(EmployeeID) > 2 ;
Operators in SQL:
The different set of operators available in SQL are as follows:
Arithmetic operators
Bitwise operators
Comparison operator
Compound operator
Logical operator
Let us look into each one of them, one by one.
Arithmetic Operators:
Operator Description
% Modulus [A % B]
/ Division [A / B]
Bitwise Operators:
Operator Description
^ Bitwise Exclusive OR (XOR) [A ^ B]
| Bitwise OR [A | B]
& Bitwise AND [A & B]
Comparison Operators:
Operator Description
< > Not Equal to [A < > B]
<= Less than or equal to [A <= B]
= Greater than or equal to [A >= B]
< Less than [A < B]
Greater than [A > B]
= Equal to [A = B]
Compound Operators:
Operator Description
|= Bitwise OR equals [A |= B]
^-= Bitwise Exclusive equals [A ^-= B]
&= Bitwise AND equals [A &= B]
%= Modulo equals [A %= B]
/= Divide equals [A /= B]
= Multiply equals [A= B]
- = Subtract equals [A-= B]
+= Add equals [A+= B]
Logical Operators: The Logical operators present in SQL are as follows: AND, OR, NOT,
BETWEEN, LIKE, IN, EXISTS, ALL, ANY.
SELECT * FROM Employee_Salary WHERE Salary BETWEEN 40000 AND 50000;
LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column of a
table. There are mainly two wildcards that are used in conjunction with the LIKE operator:
% : It is used to matches 0 or more character. _ : It is used to matches exactly one character.
Syntax
SELECT ColumnName(s) FROM TableName WHERE ColumnName LIKE pattern;
Refer to the following table for the various patterns that you can mention with the LIKE
operator.
Like Operator Condition Description
WHERE CustomerName LIKE ‘v% Finds any values that start with “v”
WHERE CustomerName LIKE ‘%v’ Finds any values that end with “v”
WHERE CustomerName LIKE ‘%and%’ Finds any values that have “and” in any position
WHERE CustomerName LIKE ‘_q%’
Finds any values that have “q” in the second position.
WHERE CustomerName LIKE ‘u_%_%’
Finds any values that start with “u” and are at least 3 characters in length
WHERE ContactName LIKE ‘m%a’
Finds any values that start with “m” and end with “a”
Example: SELECT * FROM Employee_Info WHERE EmployeeName LIKE 'S%';
IN Operator: This operator is used for multiple OR conditions. This allows you to specify
multiple values in a WHERE clause.
Syntax: SELECT ColumnName(s) FROM TableName WHERE ColumnName IN (Value1,Value2...);
Example: SELECT * FROM Employee_Info WHERE City IN ('Mumbai', 'Bangalore', 'Hyderabad');
NOTE: You can also use IN while writing Nested Queries.
EXISTS Operator: The EXISTS operator is used to test if a record exists or not.
Syntax: SELECT ColumnName(s) FROM TableName WHERE EXISTS (SELECT ColumnName FROM TableName WHERE condition);
Example: SELECT City FROM Employee_Info WHERE EXISTS (SELECT City FROM Employee_Info WHERE EmployeeId = 05 AND City = 'Kolkata');
ALL Operator: The ALL operator is used with a WHERE or HAVING clause and returns TRUE
if all of the subquery values meet the condition.
Syntax: SELECT ColumnName(s) FROM TableName WHERE ColumnName operator ALL (SELECT ColumnName FROM TableName WHERE condition);
Example: SELECT EmployeeName FROM Employee_Info WHERE EmployeeID = ALL ( SELECT EmployeeID FROM Employee_Info WHERE City = 'Hyderabad');
ANY Operator: Similar to the ALL operator, the ANY operator is also used with a WHERE or
HAVING clause and returns true if any of the subquery values meet the condition.
Syntax: SELECT ColumnName(s) FROM TableName WHERE ColumnName operator ANY (SELECT ColumnName FROM TableName WHERE condition);
Example: SELECT EmployeeName FROM Employee_Info WHERE EmployeeID = ANY (SELECT EmployeeID FROM Employee_Info WHERE City = 'Hyderabad' OR City = 'Kolkata');
Aliases Statement: Aliases are used to give a column / table a temporary name and only
exists for duration of the query.
Syntax: /* Alias Column Syntax. Instead of displaying the column name
used in the table, it display alias name. */
SELECT ColumnName AS AliasName FROM TableName;
Syntax: SELECT ColumnName(s) FROM Table1 WHERE condition INTERSECT SELECT ColumnName(s) FROM Table2 WHERE condition;
iii. EXCEPT: This operator returns those tuples that are returned by the first SELECT
operation, and are not returned by the second SELECT operation.
Syntax: SELECT ColumnName(s) FROM Table1 WHERE condition EXCEPT SELECT ColumnName(s) FROM Table2 WHERE condition;
Note: UNION, INTERSECT or EXCEPT operations are possible if and only if first SELECT query and second SELECT query produces same no of columns in same order, same column names and data type. Otherwise it gives an error.
7. JOINS
JOINS are used to combine rows from two or more tables, based on a related column between those tables. The following are the types of joins:
INNER JOIN: This join returns those records which have matching values in both the tables. FULL JOIN: This join returns all those records which either have a match in the left or the right table. LEFT JOIN: This join returns records from the left table, and also those records which satisfy the condition from the right table. RIGHT JOIN: This join returns records from the right table, and also those records which satisfy the condition from the left table.
Refer to the image below.
Let’s consider the below Technologies and the Employee_Info table, to understand the syntax
of joins.
Employee_Info
EmployeeID EmployeeName PhoneNumber City Country
01 Shravya 9898765612 Mumbai India
02 Vijay 9432156783 Delhi India
03 Preeti 9764234519 Bangalore India
04 Vijay 9966442211 Hyderabad India
05 Manasa 9543176246 Kolkata India
Technologies
TechID EmpID TechName ProjectStartDate
1 01 DevOps 04-01-
2 03 Blockchain 06-07-
3 04 Python 01-03-
4 06 Java 10-10-
INNER JOIN or EQUI JOIN: This is a simple JOIN in which the result is based on matched
data as per the equality condition specified in the SQL query. This join is used mostly.
NATURAL JOIN is a type INNER JOIN. We can also use it. It also gives same result.
Syntax
SELECT ColumnName(s) FROM Table INNER JOIN Table2 ON Table1.ColumnName = Table2.ColumnName;
Example
SELECT T.TechID, E.EmployeeID, E.EmployeeName FROM Technologies T INNER JOIN Employee_Info E ON T.EmpID = E.EmpID;
TechID EmployeeID EmployeeName
1 01 Shravya
2 03 Preeti
3 04 Vijay
RIGHT JOIN: The right outer join returns a result-set table with the matched data from the
two tables being joined, then the remaining rows of the right table and null for the remaining left
table's columns.
Syntax: SELECT ColumnName(s) FROM Table RIGHT JOIN Table2 ON Table1.ColumnName = Table2.ColumnName;
Example: SELECT E.EmployeeId, E.EmployeeName, T.TechID FROM Employee_Info E RIGHT JOIN Technologies T ON E.EmployeeID = T.EmpIDID ;
EmployeeID (^) EmployeeName TechID
01 Shravya 1
03 Preeti 2
04 Vijay 3
NULL NULL 4
8. TRIGGERS
A trigger is a stored procedure in database which automatically invokes whenever a special event
in the database occurs. For example, a trigger can be invoked when a row is inserted into a
specified table or when certain table columns are being updated. So, a trigger can be invoked
either BEFORE or AFTER the data is changed by INSERT , UPDATE or DELETE statement.
Refer to the image below.
Syntax:
CREATE TRIGGER [TriggerName] [BEFORE | AFTER] {INSERT | UPDATE | DELETE} on [TableName] [FOR EACH ROW] [TriggerBody]
Explanation of syntax:
create trigger [trigger_name]: Creates or replaces an existing trigger with the trigger_name. [before | after]: This specifies when the trigger will be executed. {insert | update | delete}: This specifies the DML operation. on [table_name]: This specifies the name of the table associated with the trigger. [for each row]: This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected. [trigger_body]: This provides the operation to be performed as trigger is fired.
BEFORE and AFTER of Trigger:
BEFORE triggers run the trigger action before the triggering statement is run.
AFTER triggers run the trigger action after the triggering statement is run.
EXAMPLE:
CREATE TRIGGER nb BEFORE INSERT ON accounts FOR EACH ROW /* Event / Begin IF :NEW.bal < 0 THEN /Condition/ DBMS_OUTPUT.PUT_LINE('BALANCE IS NAGATIVE..'); /Action*/ END IF; End;
A trigger called ‘ nb ’ is created to alert the user when inserting account details with negative
balance value in to accounts table. Before inserting, the trigger is activated if the condition is
true. When a trigger activated, the action part of the trigger is get executed.
9. NORMALIZATION
Normalization is the process of minimizing the redundancy from a relation or set of relations. It is used to eliminate the Insertion, Update and Deletion Anomalies. Normalization divides the larger table into the smaller table and links them using relationship. Normalization is done with the help of different normal form.
