DBMS Module IV

SQL
SQL Concepts: Basics of SQL, DDL, DML, DCL, Tables – Create,
Modify and Delete table structures, Rename and Drop tables,
Defining constraints – Primary key, foreign key, unique, not
null, check, IN operator Select Command, Logical Operators,
Functions - aggregate functions, Built-in functions –numeric,
date, string functions, set operations, sub-queries, correlated
sub-queries, Use of group by, having, order by, join and its
types, Exist, Any, All. View - Creation, Renaming the column of
a view, destroys view.
SQL
• SQL stands for Structured Query Language
• SQL lets you access and manipulate databases
• 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
SQL
• Data Definition Language (DDL)
• Create/alter/delete tables and their attributes
• Data Manipulation Language (DML)
• Query one or more tables
• Insert/delete/modify tuples in tables
• Transact-SQL
• Idea: package a sequence of SQL statements → server

4
Data Integrity
Entity Integrity − There are no duplicate rows in a table.
Domain Integrity − Enforces valid entries for a given column by
restricting the type, the format, or the range of values.
Referential integrity − Rows cannot be deleted, which are used by
other records.
User-Defined Integrity − Enforces some specific business rules that do
not fall into entity, domain or referential integrity
SQL Data Types
SQL Data Type is an attribute that specifies the type of data of any
object. Each column, variable and expression has a related data type in
SQL. You can use these data types while creating your tables. You can
choose a data type for a table column based on your requirement
SQL Operators
An operator is a reserved word or a character used primarily in an SQL
statement's WHERE clause to perform operation(s), such as
comparisons and arithmetic operations.
These Operators are used to specify conditions in an SQL statement
and to serve as conjunctions for multiple conditions in a statement.

Arithmetic operators
Comparison operators
Logical operators
Operators used to negate conditions
SQL Commands
• SELECT - extracts data from a database
• UPDATE - updates data in a database
• DELETE - deletes data from a database
• INSERT INTO - inserts new data into a database
• CREATE DATABASE - creates a new database
• ALTER DATABASE - modifies a database
• CREATE TABLE - creates a new table
• ALTER TABLE - modifies a table
• DROP TABLE - deletes a table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index
 DATABASE Creation
The SQL CREATE DATABASE statement is used to create a new SQL database.
Syntax
The basic syntax of this CREATE DATABASE statement is as follows −
CREATE DATABASE DatabaseName;
Always the database name should be unique within the RDBMS.
CREATE DATABASE IF NOT EXISTS my_db;

Example
If you want to create a new database <testDB>, then the CREATE DATABASE statement
would
be as shown below −
SQL> CREATE DATABASE testDB;
Show all DBs
Once a database is created,
you can check it in the list of databases as follows −
SQL> SHOW DATABASES; (\l- postgres)
Drop a DB
The SQL DROP DATABASE statement is used to drop an existing
database in SQL schema.
Syntax
The basic syntax of DROP DATABASE statement is as follows −
DROP DATABASE DatabaseName;
Always the database name should be unique within the RDBMS.
Example
If you want to delete an existing database <testDB>, then the DROP DATABASE
statement would be as shown below −
SQL> DROP DATABASE testDB;
Use a particular DB
When you have multiple databases in your SQL Schema, then before starting
your operation, you would need to select a database where all the
operations would be performed.
The SQL USE statement is used to select any existing database in the SQL
schema.
Syntax
The basic syntax of the USE statement is as shown below −
USE DatabaseName; (\c DatabaseName;- Postgres)
Always the database name should be unique within the RDBMS.
Example
You can check the available databases as shown below −
SQL> SHOW DATABASES;
 CREATE TABLE
The SQL CREATE TABLE statement is used to create a new table.
Syntax
The basic syntax of the CREATE TABLE statement is as follows −
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
)
Eg:- CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
 DROP TABLE
The SQL DROP TABLE statement removes a table definition and all the
data, indexes, triggers, constraints, and permission specifications for
that table.
Syntax
The basic syntax of this DROP TABLE statement is as follows −
DROP TABLE table_name;
Eg:_ DROP TABLE CUSTOMERS;
 INSERT INTO
The SQL INSERT INTO Statement is used to add new rows of data to a table in
the database.
Syntax
There are two basic syntaxes of the INSERT INTO statement
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2,
value3,...valueN);
Here, column1, column2, column3,...columnN are the names of the columns
in the table into which you want to insert the data.
Example:- To insert a records in the CUSTOMERS table.
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
 SELECT
The SQL SELECT statement is used to fetch the data from a database
table which returns this data in the form of a result table. These result
tables are called result-sets.
Syntax
The basic syntax of the SELECT statement is as follows −
SELECT column1, column2, columnN FROM table_name;
SELECT * FROM table_name;
Eg:- > SELECT ID, NAME, SALARY FROM CUSTOMERS;
SELECT * FROM CUSTOMERS;
 DELETE
DELETE command is used to delete rows that are no longer required
from the database tables. It deletes the whole row from the table.
Delete command comes in handy to delete temporary or obsolete data
from your database. The DELETE command can delete more than one
row from a table in a single query
Synatax:
DELETE FROM table_name [WHERE condition];
Eg:-
DELETE FROM movies WHERE movie_id = 18;
 UPADATE
Used to modify the existing data in a MySQL table
Syntax:
UPDATE table_name SET field1 = new-value1, field2 = new-value2 [WHERE Clause]

Eg:-
UPDATE Customers SET Name = 'Alfred Schmidt’, aDDRESS= 'Frankfurt’ WHERE ID = 1;
 ALTER TABLE
• The ALTER TABLE statement is used to add, delete, or modify columns in an
existing table.
• Also used to add and drop various constraints on an existing table.
1. Add Column
Syntax: ALTER TABLE table_name ADD column_name datatype;
Eg:- ALTER TABLE Customers ADD Email varchar(255);
2. To change the data type of a column in a table.

syntax: ALTER TABLE table_name ALTER COLUMN column_name datatype;

Or
ALTER TABLE table_name MODIFY COLUMN column_name datatype;
Or
ALTER TABLE table_name MODIFY column_name datatype Ex:-
ALTER TABLE Persons ALTER COLUMN DateOfBirth year;
 ALTER Table
3. Drop Column
To delete the column named "DateOfBirth" in the "Persons" table.
Syntax:
ALTER TABLE table_name DROP COLUMN COLUMNNAME
Example:
ALTER TABLE Persons DROP COLUMN DateOfBirth;
4. Rename Column In Table
Syntax: ALTER TABLE table_name RENAME COLUMN old_name TO new_name
column_definition
Example: To change the column name "cus_surname" to "cus_title".
: ALTER TABLE cus_tbl RENAME COLUMN cus_surname cus_title;
 ALTER TABLE
5. RENAME TABLE
Syntax:
ALTER TABLE table_name RENAME TO new_table_name;
Example:
To rename table cus_tbl is renamed as cus_table.
ALTER TABLE cus_tbl RENAME TO cus_table;
 SQL Constraints
NOT NULL Constraint − Ensures that a column cannot have a NULL value.
DEFAULT Constraint − Provides a default value for a column when none is specified.
UNIQUE Constraint − Ensures that all the values in a column are different.
PRIMARY Key − Uniquely identifies each row/record in a database table.
FOREIGN Key − Uniquely identifies a row/record in any another database table.
CHECK Constraint − The CHECK constraint ensures that all values in a column satisfy
certain conditions.
INDEX − Used to create and retrieve data from the database very quickly.
 NOT NULL Constraint
The NOT NULL constraint enforces a column to NOT accept NULL values. This
enforces a field to always contain a value, which means that you cannot insert a
new record, or update a record without adding a value to this field.
Example
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
 UNIQUE Constraint
The UNIQUE constraint ensures that all values in a column are different. Both the
UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a
column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE
constraint. We can have many UNIQUE constraints per table, but only one PRIMARY
KEY constraint per table.
Eg:-
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);
 PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a table.
Primary keys must contain UNIQUE values, and cannot contain NULL
values.
Example:-
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL,
FirstName varchar(255), Age int, PRIMARY KEY (ID) );
 FOREIGN KEY Constraint
A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY is a field (or collection of
fields) in one table that refers to the PRIMARY KEY in another table.
The table containing the foreign key is called the child table, and the table containing the candidate
key is called the referenced or parent table.
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
 CHECK Constraint
The CHECK constraint is used to limit the value range that can be placed
in a column.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);
 DEFAULT Constraint
The DEFAULT constraint is used to provide a default value for a column.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);
 ADD/DROP Constraint
Constraint can be added to a column in a table using ALTER command
Syntax:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);
Eg:-
ALTER TABLE supplier ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_id);

The syntax to drop a primary key using the ALTER TABLE statement in
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
Eg:- ALTER TABLE supplier DROP CONSTRAINT supplier_pk;
 SQL Functions
Aggregate Functions:
Aggregates the values and return a single value, below is the list of
some aggregate values in sql server.
 SQL Functions
SQL numeric functions are used primarily for numeric manipulation
and/or mathematical calculations.
 SQL Functions
String Functions
String functions are used to perform an operation on input string and return an
output string.Following are the string functions defined in SQL
1. ASCII(): This function is used to find the ASCII value of a character.
Syntax: SELECT ascii('t’);
Output: 116
2. CHAR_LENGTH(): This function is used to find the length of a word.
Syntax: SELECT char_length('Hello!');
Output: 6
3. CHARACTER_LENGTH(): This function is used to find the length of a line.
Syntax: SELECT CHARACTER_LENGTH('geeks for geeks');
Output: 15
 SQL Functions
4. CONCAT(): This function is used to add two words or strings.
Syntax: SELECT 'Geeks' || ' ' || 'forGeeks' FROM dual;
Output: ‘GeeksforGeeks’
5. INSERT(): This function is used to insert the data into a database.
Syntax: INSERT INTO database (geek_id, geek_name) VALUES (5000, 'abc');
Output: successfully updated
6. LCASE(): This function is used to convert the given string into lower case.
Syntax: LCASE ("GeeksFor Geeks To Learn");
Output: geeksforgeeks to learn
7. LEFT(): This function is used to SELECT a sub string from the left of given
size or
characters.
Syntax: SELECT LEFT('geeksforgeeks.org', 5);
Output: geeks
 SQL Functions
8. LENGTH(): This function is used to find the length of a word.
Syntax: LENGTH('GeeksForGeeks');
Output: 13
9. LOWER(): This function is used to convert the upper case string into
lower case.
Syntax: SELECT LOWER('GEEKSFORGEEKS.ORG');
Output: geeksforgeeks.org
10. LTRIM(): This function is used to cut the given sub string from the
original string.
Syntax: LTRIM('123123geeks', '123');
Output: geeks
 SQL Functions
11. MID(): This function is to find a word from the given position and of the given size.
Syntax: Mid ("geeksforgeeks", 6, 2);
Output: for
12. REPLACE(): This function is used to cut the given string by removing the given sub string.
Syntax: REPLACE('123geeks123', '123');
Output: geeks
13. REVERSE(): This function is used to reverse a string.
Syntax: SELECT REVERSE('geeksforgeeks.org');
Output: ‘gro.skeegrofskeeg’
14. RIGHT(): This function is used to SELECT a sub string from the right end of the given size.
Syntax: SELECT RIGHT('geeksforgeeks.org', 4);
Output: ‘.org’
15. RTRIM(): This function is used to cut the given sub string from the original string.
Syntax: RTRIM('geeksxyxzyyy', 'xyz');
Output: ‘geeks’
 SQL Functions
16. STRCMP(): This function is used to compare 2 strings.
• If string1 and string2 are the same, the STRCMP function will return 0.
• If string1 is smaller than string2, the STRCMP function will return -1.
• If string1 is larger than string2, the STRCMP function will return 1.
Syntax: SELECT STRCMP('google.com', 'geeksforgeeks.com');
Output: -1
17. TRIM(): This function is used to cut the given symbol from the string.
Syntax: TRIM(LEADING '0' FROM '000123');
Output: 123
18. UCASE(): This function is used to make the string in upper case.
Syntax: UCASE ("GeeksForGeeks");
Output: GEEKSFORGEEKS
 SQL Functions
SQL Date Functions
• NOW(): Returns the current date and time. Example:
SELECT NOW();
Output: 2017-01-13 08:03:52
• CURDATE(): Returns the current date. Example:
SELECT CURDATE();
Output:2017-01-13
• CURTIME(): Returns the current time. Example:
SELECT CURTIME();
Output: 08:05:15
 SQL SET Operations
The SQL Set operation is used to combine the two or more SQL SELECT
statements.
Types of Set Operation
1. Union
2. UnionAll
3. Intersect
4. Minus
 1. Union
The SQL Union operation is used to combine the result of two or more
SQL SELECT queries.
In the union operation, all the number of datatype and columns must
be same in both the tables on which UNION operation is being applied.
The union operation eliminates the duplicate rows from its resultset.
Syntax
SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;
 First

SELECT * FROM First

UNION
SELECT * FROM Second;
 2.Union All
Union All operation is equal to the Union operation. It returns the set
without removing duplication and sorting the data.
Syntax:
SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2;
Eg:-
SELECT * FROM First
UNION ALL
SELECT * FROM Second;
 3. Intersect
• The Intersect operation returns the common rows from both the SELECT
statements.
• In the Intersect operation, the number of datatype and columns must be the
same.
• It has no duplicates and it arranges the data in ascending order by default.
• Syntax
SELECT column_name FROM table1
INTERSECT
SELECT column_name FROM table2;
Eg:-
SELECT * FROM First
INTERSECT
SELECT * FROM Second;
 4. Minus
• It combines the result of two SELECT statements. Minus operator is used to
display the rows which are present in the first query but absent in the second
query.
• It has no duplicates and data arranged in ascending order by default.
SELECT column_name FROM table1
MINUS
SELECT column_name FROM table2;

Eg:-
SELECT * FROM First
MINUS
SELECT * FROM Second;
Aliases, * and DISTINCT, Empty WHERE-clause
• In SQL, we can use the same name for two (or more) attributes as
long as the attributes are in different relations
• A query that refers to two or more attributes with the same name
must qualify the attribute name with the relation name by prefixing
the relation name to the attribute name
• Example:

• EMPLOYEE.LNAME, DEPARTMENT.DNAME

Slide 8- 55
ALIASES
• Some queries need to refer to the same relation twice
• In this case, aliases are given to the relation name
• For each employee, retrieve the employee's name, and the name of his or her immediate
supervisor.

SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME

FROM EMPLOYEE E S
WHERE E.SUPERSSN=S.SSN

• In Query, the alternate relation names E and S are called aliases or tuple variables for the
EMPLOYEE relation
• We can think of E and S as two different copies of EMPLOYEE; E represents employees in role
of supervisees and S represents employees in role of supervisors

Slide 8- 56
 SQL Clauses
SQL clause helps us to retrieve a set or bundles of records from the
table.
SQL clause helps us to specify a condition on the columns or the
records of a table.
Different clauses available in the Structured Query Language are as
follows:
WHERE CLAUSE
GROUP BY CLAUSE
HAVING CLAUSE
ORDER BY CLAUSE
UNSPECIFIED
WHERE-clause
• A missing WHERE-clause indicates no condition; hence, all tuples of the relations
in the FROM-clause are selected
• This is equivalent to the condition WHERE TRUE
• Retrieve the SSN values for all employees.

• SELECT SSN
FROM EMPLOYEE

• If more than one relation is specified in the FROM-clause and there is no join
condition, then the CARTESIAN PRODUCT of tuples is selected

Slide 8- 58
 SQL - Where
A WHERE clause in SQL is used with the SELECT query, which is one of the data
manipulation language commands.
WHERE clauses can be used to limit the number of rows to be displayed in the
result set, it generally helps in filtering the records.
It returns only those queries which fulfill the specific conditions of the WHERE
clause.
WHERE clause is used in SELECT, UPDATE, DELETE statement, etc.
Syntax:
SELECT * FROM TABLENAME WHERE CONDITION;
SELECT COLUMNNAME1, COLUMNNAME2 FROM TABLENAME WHERE CONDITION;
USE OF *
• To retrieve all the attribute values of the selected tuples, a * is used, which stands
for all the attributes
Examples:

Q1C: SELECT *
FROM EMPLOYEE
WHERE DNO=5

Q1D: SELECT *
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME='Research' AND
DNO=DNUMBER

Slide 8- 60
USE OF DISTINCT
• SQL does not treat a relation as a set; duplicate tuples can appear
• To eliminate duplicate tuples in a query result, the keyword DISTINCT is used
• For example, the result of Q1 may have duplicate SALARY values whereas Q2
does not have any duplicate values

Q1: SELECT SALARY

FROM EMPLOYEE
Q2: SELECT DISTINCT SALARY
FROM EMPLOYEE

Slide 8- 61
EXPLICIT SETS
• It is also possible to use an explicit (enumerated) set of values in the
WHERE-clause rather than a nested query
• Retrieve the social security numbers of all employees who work on
project number 1, 2, or 3.
Q: SELECT DISTINCT ESSN
FROM WORKS_ON
WHERE PNO IN (1, 2, 3)

Slide 8- 62
NULLS IN SQL QUERIES
• SQL allows queries that check if a value is NULL (missing or undefined or not
applicable)
• SQL uses IS or IS NOT to compare NULLs because it considers each NULL value
distinct from other NULL values, so equality comparison is not appropriate.
• Query : Retrieve the names of all employees who do not have supervisors.
Q: SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE SUPERSSN IS NULL
• Note: If a join condition is specified, tuples with NULL values for the join attributes
are not included in the result

Slide 8- 63
AGGREGATE FUNCTIONS
• Include COUNT, SUM, MAX, MIN, and AVG
• Query : Find the maximum salary, the minimum salary, and the
average salary among all employees.
Q: SELECT MAX(SALARY),
MIN(SALARY), AVG(SALARY)
FROM EMPLOYEE

• Some SQL implementations may not allow more than one function in
the SELECT-clause

Slide 8- 64
 GROUP BY
The GROUP BY Statement in SQL is used to arrange identical data into
groups with the help of some functions. i.e. if a particular column has
the same values in different rows then it will arrange these rows in a
group.
Features
• GROUP BY clause is used with the SELECT statement.
• In the query, the GROUP BY clause is placed after the WHERE clause.
• In the query, the GROUP BY clause is placed before the ORDER BY clause if
used.
• In the query, the Group BY clause is placed before the Having clause.
 GROUP BY
Syntax:-
SELECT column1, function_name(column2)
FROM table_name
WHERE condition
GROUP BY column1, column2
ORDER BY column1, column2;
• function_name: Name of the function used for example, SUM() , AVG().
• table_name: Name of the table.
• condition: Condition used.
 GROUP BY
Group By single column means, placing all the rows with the same value of only that particular column in one
group.

INSERT INTO emp (emp_no, name, sal, age)

CREATE TABLE emp ( VALUES
emp_no INT PRIMARY KEY, (1, 'Aarav', 50000.00, 25),
name VARCHAR(50), (2, 'Aditi', 60000.50, 30),
(3, 'Amit', 75000.75, 35),
sal DECIMAL(10,2),
(4, 'Anjali', 45000.25, 28),
age INT (5, 'Chetan', 80000.00, 32),
); (6, 'Divya', 65000.00, 27),
(7, 'Gaurav', 55000.50, 29),
SELECT NAME, SUM(SAL) FROM emp GROUP BY Name;
(8, 'Isha', 72000.75, 31),
(9, 'Kavita', 48000.25, 26),
(10, 'Mohan', 83000.00, 33),
(11, 'Aarav', 50000.00, 25);
 HAVING
• To Specify a condition on GROUP.
• HAVING clause to place conditions to decide which group will be part of the final
result set. Also, we can not use aggregate functions like SUM(), COUNT(), etc.
with the WHERE clause. So we have to use the HAVING clause if we want to use
any of these functions in the conditions.
Syntax:
SELECT column1, function_name(column2)
FROM table_name
WHERE condition SELECT NAME, SUM(sal) FROM Emp
GROUP BY column1, column2 GROUP BY name
HAVING SUM(sal)>8000;
HAVING condition
ORDER BY column1, column2;
 ORDER BY
• SQL ORDER BY clause sorts the result of the SELECT statement either in ascending
or descending order.
• Used to sort the fetched data in either ascending or descending according to one
or more columns. It is very useful to present data in a structured manner.

• SQL ORDER BY default mode is sorting data into ascending order. To sort data in
descending order use the DESC keyword with ORDER BY clause.
• Syntax:-
SELECT * FROM table_name ORDER BY column_name ASC | DESC;
Eg:- SELECT * FROM emp ORDER BY SAL DESC;
 EXISTS
• The EXISTS operator is used to test for the existence of any record in a subquery.
• The EXISTS operator returns TRUE if the subquery returns one or more records.
• Syntax:-
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
THE EXISTS FUNCTION
• EXISTS is used to check whether the result of a correlated nested query is empty
(contains no tuples) or not
• Retrieve the name of each employee who has a dependent with the same first name as
the employee.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE EXISTS (SELECT *
FROM DEPENDENT
WHERE SSN=ESSN AND
FNAME=DEPENDENT_NAME)
• Retrieve the names of employees who have no dependents.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE NOT EXISTS (SELECT *
FROM DEPENDENT
WHERE SSN=ESSN)

Slide 8- 71
 SQL ANY and ALL Operators
The ANY operator:
returns a boolean value as a result
returns TRUE if ANY of the subquery values meet the condition
ANY means that the condition will be true if the operation is true for any of the
values in the range.
Syntax:-
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY Eg:- SELECT Cus_Name FROM CUSTOMERS WHERE ID = ANY(SELECT
(SELECT column_name emp_NO FROM emp where age = 28);
FROM table_name
WHERE condition);
 The ALL operator:
• returns a boolean value as a result
• returns TRUE if ALL of the subquery values meet the condition is used with SELECT, WHERE and
HAVING statements
ALL means that the condition will be true only if the operation is true for all values in the range.
Syntax:-
SELECT ALL column_name(s) SELECT column_name(s)
FROM table_name FROM table_name
WHERE condition; WHERE column_name operator ALL
(SELECT column_name
FROM table_name
WHERE condition);
Eg:- select all name,emp_no from emp where sal>70000;
SUBSTRING COMPARISON
• The LIKE comparison operator is used to compare partial strings
• Two reserved characters are used: '%' (or '*' in some implementations) replaces
an arbitrary number of characters, and '_' replaces a single arbitrary character
• Retrieve all employees whose Name must contain the substring RAV‘ in it.

Eg:- SELECT EMP_NO,NAME FROM EMP WHERE NAME LIKE "%Rav%";

Slide 8- 74
ARITHMETIC OPERATIONS
• The standard arithmetic operators '+', '-'. '*', and '/' (for addition, subtraction,
multiplication, and division, respectively) can be applied to numeric values in an
SQL query result
• Query 27: Show the effect of giving all employees who work on the 'ProductX'
project a 10% raise.
Q27: SELECT FNAME, LNAME, 1.1*SALARY
FROM EMPLOYEE, WORKS_ON,
PROJECT
WHERE SSN=ESSN AND PNO=PNUMBER
AND PNAME='ProductX’

Slide 8- 75
Summary of SQL Queries
• A query in SQL can consist of up to six clauses, but only the first two,
SELECT and FROM, are mandatory. The clauses are specified in the
following order:

SELECT <attribute list>

FROM <table list>
[WHERE <condition>]
[GROUP BY <grouping attribute(s)>]
[HAVING <group condition>]
[ORDER BY <attribute list>]

Slide 8- 76
 SQL Sub Query
• Subquery - query within another query. A
• Subquery is a query that is embedded in WHERE clause of another SQL query.
• The outer query is called as main query and inner query is called as subquery.
• The subquery generally executes first when the subquery doesn’t have any co-relation with the
main query, when there is a co-relation the parser takes the decision on the fly on which query to
execute on precedence and uses the output of the subquery accordingly.
• Syntax:-
SELECT column_name FROM table_name WHERE column_name expression
operator(SELECT COLUMN_NAME from TABLE_NAME WHERE ... );
Eg:-
Select NAME, LOCATION, PHONE_NUMBER from DATABASE WHERE ROLL_NO IN (SELECT ROLL_NO
from STUDENT where SECTION=’A’);
INSERT INTO Student1 SELECT * FROM Student2;
 DATABASE STUDENT

Select NAME, ROLL NO,LOCATION, PHONE_NUMBER from DATABASE WHERE ROLL_NO IN (SELECT ROLL_NO from
STUDENT where SECTION=’A’);
INSERT INTO Student1 SELECT * FROM Student2;
 Correlated Subquery
Correlated subqueries are used for row-by-row processing. Each
subquery is executed once for every row of the outer query.
A correlated subquery is evaluated once for each row processed by the
parent statement. The parent statement can be a SELECT, UPDATE, or
DELETE statement.
Syntax:- SELECT column1, column2, ....
FROM table1 outer
WHERE column1 operator
(SELECT column1, column2
FROM table2 WHERE expr1 = outer.expr2);
 Correlated Subquery

SELECT last_name, first_name

FROM employee e1
WHERE NOT EXISTS (SELECT ph.last_name
FROM payment_history ph
WHERE ph.employee_id = e1.employee_id
AND ph.payment_type = ‘bonus’
)
 SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related
column between them.
TYPES
•(INNER) JOIN: Returns records that have matching values in both tables
•LEFT (OUTER) JOIN: Returns all records from the left table, and the matched
records from the right table
•RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched
records from the left table
•FULL (OUTER) JOIN: Returns all records when there is a match in either left or
right table
 INNER JOIN
The INNER JOIN keyword selects records that have matching values in both tables.

SELECT Customers.customer_id,
Customers.first_name, Orders.amount
FROM Customers
INNER JOIN Orders
ON Customers.customer_id = Orders.customer;
 SQL LEFT JOIN
The SQL LEFT JOIN combines two tables based on a common column. It then
selects records having matching values in these columns and the remaining rows
from the left.
SELECT Customers.customer_id, Customers.first_name,
Orders.amount FROM Customers
LEFT JOIN Orders ON
Customers.customer_id = Orders.customer;table.
 SQL Right Join
The SQL RIGHT JOIN statement joins two tables based on a common column. It
selects records that have matching values in these columns and the remaining rows
from the right table.
Syntax:
SELECT columns_from_both_tables
FROM table1
RIGHT JOIN table2
ON table1.column1 = table2.column2
Eg:-
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
RIGHT JOIN Orders
ON Customers.customer_id = Orders.customer;
 SQL FULL OUTER JOIN
The SQL FULL OUTER JOIN statement joins two tables based on a common column.
It selects records that have matching values in these columns and the remaining
rows from both of the tables.
Syntax:
SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column1 = table2.column2;
Eg:-
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
FULL OUTER JOIN Orders
ON Customers.customer_id = Orders.customer;
 VIEWS
SQL provides the concept of VIEW, which hides the complexity of the data and
restricts unnecessary access to the database. It permits the users to access only a
particular column rather than the whole data of the table.

The View in the Structured Query Language is considered as the virtual table, which
depends on the result-set of the predefined SQL statement.

Like the SQL tables, Views also store data in rows and columns, but the rows do not
have any physical existence in the database.

Any database administrator and user can easily create the View by selecting the
columns from one or more database tables. They can also delete and update the
views according to their needs.
 VIEWS - Create
Syntax:

Single Table
CREATE VIEW View_Name AS
SELECT Column_Name1, Column_Name2, ....., Column_NameN
FROM Table_Name
WHERE condition;

Multiple Table
CREATE VIEW View_Name AS
SELECT Table_Name1.Column_Name1, Table_Name1.Column_Name2,
Table_Name2.Column_Name2, ....., Table_NameN.Column_NameN
FROM Table_Name1, Table_Name2, ....., Table_NameN
WHERE condition;
 VIEW - Update
We can also modify existing data and insert the new record into the view in the Structured Query
Language. A view in SQL can only be modified if the view follows the following conditions:
• You can update that view which depends on only one table. SQL will not allow updating the view
which is created more than one table.
• The fields of view should not contain NULL values.
• The view does not contain any subquery and DISTINCT keyword in its definition.
• The views cannot be updatable if the SELECT statement used to create a View contains JOIN or
HAVING or GROUP BY clause.
• If any field of view contains any SQL aggregate function, you cannot modify the view.

• Syntax:-
CREATE OR REPLACE VIEW View_Name AS
SELECT Column_Name1, Column_Name2, ....., Column_NameN
FROM Table_Name
WHERE condition;
 VIEW - Insert
Just like the insertion process of database tables, we can also insert the record in
the views. The following SQL INSERT statement is used to insert the new row or
record in the view:
Syntax:-
INSERT INTO View_Name(Column_Name1, Column_Name2 , Column_Name3, ....., Column_NameN)
VALUES(value1, value2,….,Valuen);
 VIEW- Delete & Drop
Just like the deletion process of database tables, we can also delete the record from the
views. The following SQL DELETE statement is used to delete the existing row or record
from the view:
Syntax:-
DELETE FROM View_Name WHERE Condition;

• Drop a View
Suppose, you want to delete the above Student_View, then you have to type the following
query in the Structured Query Language:
Syntax:-
DROP VIEW View_Name;
Example
CREATE VIEW Student_View AS
SELECT Student_ID, Stu_Subject, Stu_Marks
FROM Student_Details
WHERE Stu_Marks > 85;

Select * FROM Student_View;

CREATE OR REPLACE VIEW Student_View AS
SELECT Student_ID, Stu_Name, Stu_Subject, Stu_Marks
FROM Student_Details
WHERE Stu_Subject = 'Math’;

SELECT * FROM Student_View;

INSERT INTO Student_View (Student_ID, Stu_Subject, Stu_Marks) VALUES (1007,
Hindi, 89);

SELECT * FROM Student_View;

DELETE FROM Student_View WHERE Stu_Subject = 'Math’ ;

SELECT * FROM Student_View;

DROP VIEW Student_View;