Unit 7 Structured Query Language (SQL)

DATABASE MANAGEMENT SYSTEM

 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

Unit 7 Structured Query Language (SQL)

Content
• WHERE Clause
• SQL Conditions/ Operators
1. AND Operator
2. OR Operator
3. NOT Operator
4. IN and NOT IN Operator
5. BETWEEN and NOT BETWEEN Operator
6. LIKE Operator
• ORDER BY Clause
• GROUP BY Clause
• HAVING Clause
• JOIN
1. INNER JOIN
2. LEFT JOIN/ LEFT OUTER JOIN
3. RIGHT JOIN/ RIGHT OUTER JOIN
4. FULL JOIN/ FULL OUTER JOIN
• Sub Query
• Correlated Sub Query
• EXISTS and NOT EXISTS Clause
• ANY Clause
• ALL Clause
• Correlated Sub Query with SELECT Statement
• VIEWS
1. CREATING VIEW
2. UPDATING VIEWS
3. DELETING VIEW
• Transaction Control Command
1. ROLLBACK
2. COMMIT
3. SAVEPOINT

Page | 1
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

WHERE Clause
• The WHERE condition is used to describe a condition while we get data from a table.
• The WHERE clause appears right after the FROM clause of the SELECT statement.
The WHERE clause uses the condition to filter the rows returned from the SELECT
[Link] returns the exact result only when the condition is fulfilled.
• Syntax:
SELECT column1, column2,...columnN
FROM table_name
WHERE conditions;
• The WHERE condition can be used with logical and comparison operators, as shown
in the below table:
Different Operators Description
AND Logical operator AND
OR Logical operator OR
= Equal
> Greater than
< Less than
<> Not equal
>= Greater than or equal
<= Less than or equal
IN The IN operator will return true if a value matches any
value in a list
LIKE The LIKE operator is used to return true if a value matches
a pattern
BETWEEN The BETWEEN operator is used to return true if a value is
between a range of values
NOT Negate the result of other operators

➢ Example 1 Display record of customer whose first name is Lisa from Customer
table.
SELECT * FROM customer WHERE first_name='Lisa';

➢ Example 2 Display records of films with rental rate more than 4 from film table.
SELECT * FROM film WHERE rental_rate >4;
Page | 2
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

SQL Conditions/ Operators

• In this section, we are going to understand the different types of PostgreSQL
Conditions/Operators, which are used to get more specific results to form a database.
They are generally used with the WHERE clause.
1. AND Operator
• The AND operator is a logical operator used to combine multiple conditions in a
PostgreSQL query.
• It returns true if all the conditions joined by AND are true; otherwise, it returns false.
• For example, the condition condition1 AND condition2 will be true only if both
condition1 and condition2 evaluate to true.
• Syntax:
SELECT column1, column2,...columnN FROM table_name
WHERE condition1 AND condition2;
➢ Example 1 Display records of films whose rental rate is more than 4 and
replacement cost is 19.99 and above from film table.
SELECT * FROM film WHERE rental_rate >4 AND
replacement_cost>=19.99;

➢ Example 2 Display title of films from film table, whose rental rate is more than
4, replacement cost is 19.99 and above and rating is R.
SELECT * FROM film
WHERE rental_rate >4 AND replacement_cost>=19.99 AND rating
='R';

Page | 3
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

➢ Example 3 Find out the title of films whose rental rate is more than 4,
replacement cost is 19.99 and above and rating is R, from film table.
SELECT title FROM film
WHERE rental_rate >4 AND replacement_cost>=19.99 AND rating
='R';

➢ Example 4 Find out the number of films whose rental rate is more than 4,
replacement cost is 19.99 and above and rating is R, from film table.
SELECT Count (title) FROM film
WHERE rental_rate >4 AND replacement_cost>=19.99 AND rating
='R';

2. OR Operator
• The OR operator is a logical operator used to combine multiple conditions in a
PostgreSQL query.
• It returns true if at least one of the conditions joined by OR is true; otherwise, it returns
false.
• For example, the condition condition1 OR condition2 will be true if either condition1
or condition2 (or both) evaluate to true.
Page | 4
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

• Syntax:
SELECT column1, column2,...columnN FROM table_name
WHERE condition1 OR condition2;
➢ Example 1 Display records of those films whose rating is either R or PG-13 from
film table.
SELECT * FROM film WHERE rating ='R' OR rating= 'PG-13';

➢ Example 2 Display records of those films whose rating is either R or PG-13 or

whose length is 50 or more, from film table.
SELECT * FROM film WHERE rating ='R' OR rating= 'PG-13' OR
length >50;

3. NOT Operator
• The NOT operator is a logical operator used to negate a condition in a PostgreSQL
query.
• It returns true if the condition following NOT is false; otherwise, it returns false.
• NOT condition is used to get those rows where a condition is not true. And we can
combine the NOT condition with the WHERE Clause.
• For example, the condition NOT condition will be true if condition is false.
• Syntax:
SELECT column1, column2,...columnN FROM table_name
WHERE NOT condition;
Page | 5
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

➢ Example 1 Display records of those films whose rating is not R from film table
/ Display records of those films which are not rated as R from film table.
SELECT * FROM film WHERE NOT rating ='R';

➢ Example 2 Display records of all customers from customer table other than
customers of store 1 and also address_id more than 50. / except customers of
store 1 and also address_id more than 50.
SELECT * FROM customer WHERE NOT store_id =1 AND NOT
address_id > 50;

➢ The NOT Operator with IN Condition is used to fetch those rows whose values
do not match the list's values.
➢ We can also combine the NOT Operator with Like, Between Condition.

4. IN and NOT IN Operator

• The IN condition is used within the WHERE clause to get those data that matches any
data in a list.
• The IN condition is used to reduce multiple OR conditions.
• The PostgreSQL IN condition will return true if the value matches any value in the
given list, which is value1, value2 ,....valueN,, and these lists of value can be a list of
literal values.
• For example, string, numbers, or an output of a SELECT command.
• Syntax:
SELECT column1, column2,.. FROM table
WHERE column IN (value1, value2, value3,...);
Page | 6
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

➢ Example 1 Display Id, first name, last name and salary of employee 1,3,5 and 6
from employee table.
SELECT EmpId, FirstName, LastName, Salary FROM Employee
WHERE EmpId IN (1, 3, 5, 6);
• The above query will return records where EmpId is 1 or 3 or 5 or 6.
➢ Example 2 Display payment records for amount 0.99, 1.99 or 2.99 from payment
table.
SELECT * FROM payment WHERE amount IN (0.99,1.99,2.99);

➢ Example 3 Display records of all customers with first name John, Jake or Julie
from customer table.
SELECT * FROM customer
WHERE first_name IN ('John', 'Jake', 'Julie');
➢ Use the NOT operator with the IN operator to filter records that do not fall in
the specified values.
• Syntax:
SELECT column1, column2,.. FROM table WHERE column
NOT IN (value1, value2, value3,...);
➢ Example 4 Display Id, first name, last name and salary of all employees except
1,3 and 5 from employee table.
SELECT EmpId, FirstName, LastName, Salary FROM Employee
WHERE EmpId NOT IN (1, 3, 5);
• The above query will return records where EmpId is other than 1 or 3 or 5 or 6.
➢ Example 5 Display payment records of all amounts except 0.99,1.99 and 2.99
from payment table.
SELECT * FROM payment WHERE amount NOT IN (0.99,1.99,2.99);

Page | 7
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

5. BETWEEN and NOT BETWEEN Operator

• The BETWEEN operator is used in the WHERE conditions to filter records within
the specified range.
• The range of values can be strings, numbers, or dates. The range of values must be
specified with the AND operator, as shown below.
• Syntax:
SELECT column1, column2,.. FROM table WHERE column
BETWEEN begin_value AND end_value;
➢ Example 1 Display Id, first name, last name and salary of all employees whose
salary is 10000 or more upto 20000 / whose salary ranges from 10000 to 20000.
SELECT EmpId, FirstName, LastName, Salary FROM Employee
WHERE Salary BETWEEN 10000 AND 20000;
• In Above Query , the Salary column is used with the BETWEEN operator to filter
records. The Salary BETWEEN 10000 AND 20000; specifies that the values in the
Salary column should be between 10000 and 20000 (inclusive of both values).
➢ Example 2 Display customer id, amount and payment date of all payment
amounts ranging from 3.99 to 5.99 from payment table.
SELECT customer_id,amount,payment_date FROM payment
WHERE amount BETWEEN 3.99 AND 5.99;

➢ Example 3 (1) Display customer id, amount and payment date of all payments
received on 1st feb 2007 to 15th feb 2007 from payment table.
SELECT customer_id,amount,payment_date FROM payment
WHERE payment_date BETWEEN '2007-02-01' AND '2007-02-15';
Page | 8
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

➢ Example 3 (2) Display customer id, amount and payment date of all payments
received on 1st feb 2007 to 14th feb 2007 from payment table.
SELECT customer_id,amount,payment_date FROM payment
WHERE payment_date BETWEEN '2007-02-01' AND '2007-02-14';

➢ Use the NOT operator with the BETWEEN operator to filter records that do not
fall in the specified range.
• Syntax:
SELECT column1, column2,.. FROM table
WHERE column NOT BETWEEN begin_value AND end_value;
➢ Example 4 Find out the Id, first name, last name and salary of all employees
with salary less than 10000 or more than 20000.
SELECT EmpId, FirstName, LastName, Salary FROM Employee
WHERE Salary NOT BETWEEN 10000 AND 20000;
• In Above Query , the Salary column is used with the NOT BETWEEN operator to
filter records. The Salary BETWEEN 10000 AND 20000; specifies that the values in
the Salary column should not be between 10000 and 20000 (inclusive of both
values).
• We are using the greater than (>) and less than (<) operators with OR operator
instead of using NOT BETWEEN/ AND operators.
SELECT EmpId, FirstName, LastName, Salary FROM Employee
WHERE Salary < 10000 OR Salary > 20000;

Page | 9
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

➢ Example 5 Find out the customer id, amount and payment date of all amounts
less than 2.99 or more than 7.99, from payment table.
SELECT customer_id,amount,payment_date from payment
WHERE amount NOT BETWEEN 2.99 AND 7.99;

6. LIKE Operator
• The LIKE operator is used in the WHERE condition to filter data based on some
specific pattern. It can be used with numbers, string, or date values. However, it is
recommended to use the string values.
• The result contains strings, which are case-sensitive and follow the specified pattern.
• Like pattern is case sensitive
• Syntax:
SELECT column1, column2,...columnN FROM table_name
WHERE column_name LIKE 'pattern';
• PostgreSQL provides with two wildcards:
1. Percent sign ( %):
• The % matches zero, one, or multiple characters (capital or small) or numbers.
• E.g. 'A%' will match all string starting with 'A' and followed by any number
of characters or numbers.
2. Underscore sign ( _):
• The underscore _ sign matches any single character or number.
• E.g. 'A_' will match all strings with two chars where the first character must
be 'A' and second character can be anything.
• Here Pattern can be any of the following shown in table
Pattern Description
FirstName LIKE 'john' Returns records whose FirstName value is 'john'
Returns records whose FirstName value starts with 'j'
FirstName LIKE 'j%'
followed by any number of characters or numbers.

Page | 10
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

Returns records whose FirstName value contains 'a' at

FirstName LIKE '%a%'
any position.
Returns records whose FirstName value should start
FirstName LIKE 'a%b'
with 'a' and last character should be 'b'.
Returns records whose FirstName value contains two
FirstName LIKE '_a'
characters and the second character must be 'a'.
Returns records whose FirstName value contains the
FirstName LIKE '_a%'
second characters 'a'.
Returns records whose FirstName value has the second
FirstName LIKE '%a_'
last character is either 'a'.
Returns records whose FirstName value must be three
FirstName LIKE '_ _ _' characters long. (Space is to know the 3-underscore
sign.)
Returns records whose FirstName value must contain
FirstName LIKE '_ _ _%' at least three characters or more. (Space is to know the
3-underscore sign.)

➢ Example 1 Display record of all customers whose first name starts with ‘J’, from
customer table.
SELECT * FROM customer WHERE first_name LIKE 'J%';

➢ Example 2 Find out the number of customers from customer table, whose first
name starts with ‘J’.
SELECT Count (*) FROM customer WHERE first_name LIKE 'J%';
or
SELECT Count (first_name) FROM customer WHERE first_name
LIKE 'J%';

➢ Example 3 Display records of all customers from customer table, whose first
name starts with ‘J’ and last name starts with ‘S’.

Page | 11
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

SELECT * FROM customer WHERE first_name LIKE 'J%' AND

last_name LIKE 'S%' ;

➢ Example 4 Display records of all customers from customer table, whose first
name starts with ‘j’ and last name starts with ‘s’
SELECT * FROM customer WHERE first_name LIKE 'j%' AND
last_name LIKE 's%';

➢ Example 5 Display customer id, first name and last name of customers whose
first name ends with ‘er’, from customer table.
SELECT customer_id,first_name,last_name FROM customer WHERE
first_name LIKE '%er';

➢ Example 6 Display customer id, first name and last name of customers who has
‘her’ in their first name starting from second character.
SELECT customer_id,first_name,last_name FROM customer WHERE
first_name LIKE '_her%';

Page | 12
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

➢ Example 7 Display customer id, first name and last name of customers with first
name which should not have ‘her’ starting from second character.
SELECT customer_id,first_name,last_name FROM customer WHERE
first_name NOT LIKE '_her%';

➢ Example 8 Display customer id, first name and last name of customers with first
name starting with ‘A’ and last name arranged in alphabetical order, from
customer table.
SELECT customer_id,first_name,last_name FROM customer
WHERE first_name LIKE 'A%' ORDER BY last_name;

➢ Example 9 List the records of customers whose first names start with ‘A’ but
last names do not start with ‘B’ and last name arranged in ascending order,
from customer table.
SELECT * FROM customer WHERE first_name LIKE 'A%'
AND last_name NOT LIKE 'B%' ORDER BY last_name;

Page | 13
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

ORDER BY Clause
• The ORDER BY clause can be used in the SELECT query to sort the result in
ascending or descending order of one or more columns.
ORDER BY Characteristics:
• The ORDER BY clause is used to get the sorted records on one or more columns in
ascending or descending order.
• IT is written at last and executed at last in Postgre SQL.
• The ORDER BY clause must come after the WHERE, GROUP BY, and HAVING
clause if present in the query.
• Use ASC or DESC to specify the sorting order after the column name. Use ASC to
sort the records in ascending order or use DESC for descending order. By default, the
ORDER BY clause sort the records in ascending order if the order is not specified.
• Syntax:
SELECT column1, column2,...columnN
FROM table_name
[WHERE condition]
ORDER BY column1, column2, .. columnN [ASC | DESC];
➢ Example 1 List the records of all customers in ascending order of their first
names from customer table.
SELECT * FROM customer ORDER BY first_name ASC;

➢ Example 2 List the records of all customers in descending order of their first
names from customer table.
SELECT * FROM customer ORDER BY first_name DESC;

Page | 14
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

➢ Example 3 Display store id, first name and last name of all customers from
customer table with first name and last name arranged in ascending order.
SELECT store_id,first_name,last_name FROM customer
ORDER BY first_name, last_name;

➢ Example 4 Display store id, first name and last name of all customers from
customer table with store id arranged in descending order and first name in
ascending order.
SELECT store_id,first_name,last_name FROM customer
ORDER BY store_id DESC, first_name ASC;

➢ Example 5 Display title and length of all films arranged from shortest to longest
film, from film table.
Select title, length FROM film ORDER BY length;

Page | 15
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

➢ Example 6 Display title and length of first 5 shortest films from film table.
Select title, length FROM film ORDER BY length LIMIT 5;

Group By Clause
• The GROUP BY clause is used to get the summary data based on one or more groups.
The groups can be formed on one or more columns.
GROUP BY Characteristics:
• The GROUP BY clause is used to form the groups of records.
• The GROUP BY clause must come after the WHERE clause if present and before the
HAVING clause.
• The GROUP BY clause can include one or more columns to form one or more groups
based on that columns.
• Only the GROUP BY columns can be included in the SELECT clause.
• Syntax:
SELECT column1, column2 FROM table_name
WHERE condition
GROUP BY column1, column2;
➢ Example 1 Display all unique customer ids from payment table / List the
customer ids from payment table.
SELECT customer_id FROM payment GROUP BY customer_id;

Note: It is similar to distinct (customer_id)

SELECT distinct (customer_id) FROM payment;
Page | 16
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

➢ Example 2 Display all unique customer ids from customer table arranged in
ascending order.
SELECT customer_id FROM payment GROUP BY customer_id ORDER
BY customer_id ASC;

➢ Example 3 (1) – Display each customer id and the total amount paid by each one
of them in ascending order from payment table.
SELECT customer_id, SUM(amount) FROM payment
GROUP BY customer_id ORDER BY SUM(amount);

➢ Example 3 (2) Display each customer id of all customers arranged in ascending

order and the total amount paid by each one of them from payment table.
SELECT customer_id, SUM(amount) FROM payment
GROUP BY customer_id ORDER BY customer_id;

Page | 17
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

➢ Example 4- Display customer ids and the total number of transactions done by
each customer in descending order from payment table
SELECT customer_id, COUNT(payment_id) FROM payment
GROUP BY customer_id ORDER BY COUNT(payment_id) DESC;

➢ Example 5 Find out total amount that each staff received from each customer
and display the staff_id, customer_id and total amount arranged in ascending
order of staff_id and customer_id from payment table.
SELECT staff_id, customer_id, SUM(amount) FROM payment
GROUP BY staff_id,customer_id
ORDER BY staff_id, customer_id;

➢ Example 6 Find out the total amount collected on each date and display the date
and total amount arranged in ascending order of total amount, from payment
table.
SELECT DATE (payment_date),SUM(amount) FROM payment
GROUP BY DATE (payment_date) ORDER BY SUM(amount);

Page | 18
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

HAVING Clause
• The HAVING clause includes one or more conditions that should be TRUE for groups
of records.
• The HAVING clause was added to SQL because the WHERE clause cannot be used
with aggregate functions.
HAVING Characteristics:
• The HAVING clause is used to filter out grouping records.
• The HAVING clause must come after the GROUP BY clause and before the ORDER
BY clause.
• The HAVING clause can include one or more conditions.
• The HAVING condition can only include columns that are used with the GROUP BY
clause. To use other columns in the HAVING condition, use the aggregate functions
with them.
• Difference between having and where clauses
Having clause Where clause
The HAVING clause allows us to filter The WHERE clause permits us to filter
groups of rows as per the defined rows according to a defined condition.
condition.
The HAVING clause is useful to groups The WHERE clause is applied to rows
of rows. only.
• Syntax:
SELECT column1, column2, Aggregate_function(Column3)
FROM table_name
WHERE condition
GROUP BY column1, column2
HAVING Aggregate_function(Column3)conditions;

Page | 19
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

➢ Example 1 Find out the customer ids of all customers who made total payment
of more than $ 100.
SELECT customer_id, SUM (amount) FROM payment
GROUP BY customer_id HAVING SUM(amount)>100;

➢ Example 2 Display the store id and total number of customers of each store
which has more than 300 customers, from customer table.
Step 1. Display each distinct store id and total number of customers of each store from
customer table. (for reference –just to know how it works)
SELECT store_id, COUNT(*) FROM customer GROUP BY store_id;

Step 2
SELECT store_id, COUNT(*) FROM customer
GROUP BY store_id HAVING COUNT(*)>300;
Or
SELECT store_id, COUNT(customer_id) FROM customer
GROUP BY store_id HAVING COUNT(customer_id)>300;

➢ Example 3 What are the customer ids of customers who have spent more than
$100 in payment transactions with our staff_id member 2?
SELECT customer_id,SUM (amount) FROM payment
WHERE staff_id=2 GROUP BY customer_id
HAVING SUM (amount)>100;

Page | 20
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

Just for reference

SELECT staff_id,customer_id, SUM (amount) FROM payment
WHERE staff_id=2 GROUP BY staff_id ,customer_id
HAVING SUM (amount)>100;

JOIN
• Join clause is used to combine records from two or more tables in a database. A JOIN
is a means for combining fields from two tables by using values common to each.
• It is used to merge columns from one or more tables according to the data of the
standard columns between connected tables. Usually, the standard columns of the first
table are primary key columns and the second table columns are foreign key columns.
Type Description
INNER JOIN Match records in both tables
LEFT JOIN Match left (first) table records with right table records
RIGHT JOIN Match right (last) table records with left table records
FULL JOIN Include all left and right records whether they match or not
INNER JOIN
• The INNER JOIN query is used to retrieve the
matching records from two or more tables based
on the specified condition.
• Syntax:
SELECT
table1.column_name(s),
table2.column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Page | 21
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

➢ Display records of all persons who have registered as well as created their login
ids from the following schemas.
1. Logins (log_id int, name varchar(20)) ;
2. Registrations (registration_id int, name varchar(20));
Step 1: CREATE TABLE Logins (log_id int, name varchar(20));
Step 2: INSERT into Logins VALUES (1,'Xavier'), (2,'Andrew'),
(3, 'Yolanda'),(4,'Bob');
Step 3: CREATE TABLE Registrations (reg_id int, name
varchar(20));
Step 4: INSERT into Registrations VALUES(1,'Andrew'),
(2,'Bob'),(3, 'Charlie'),(4,'David');
Step 5: SELECT * FROM Registrations INNER JOIN Logins ON
[Link]=[Link];

➢ Example 1 Display records of all customers who have done payment along with
all payment details from customer and payment tables.
SELECT * FROM payment INNER JOIN customer ON
payment.customer_id=customer.customer_id;

SELECT payment_id,payment.customer_id,first_name FROM

payment INNER JOIN customer ON
payment.customer_id=customer.customer_id;

Page | 22
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

LEFT JOIN/ LEFT OUTER JOIN

• The LEFT JOIN is a type of inner join where it
returns all the records from the left table and
matching records from the right table.
• Here, the left table is a table that comes to the left
side or before the "LEFT JOIN" phrase in the query,
and the right table refers to a table that comes at the
right side or after the "LEFT JOIN" phrase. It returns
NULL for all non-matching records from the right table.
• Syntax:
SELECT column_name(s) FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
➢ Example 1 Display records all persons who have registration ids and also those
persons who have login ids as well as registration ids from "Registrations" and
"Logins" tables.
SELECT * from Registrations LEFT OUTER JOIN Logins ON
[Link]=[Link];
OR
SELECT * from Registrations LEFT JOIN Logins ON
[Link]=[Link];

➢ Display records of all persons who have registered but do not have login id from
"Registrations" and "Logins" tables.
SELECT * from Registrations LEFT OUTER JOIN Logins ON
[Link]=[Link] WHERE Logins IS NULL;

Page | 23
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

➢ Example 2 Display film id and title of all films which do not have an inventory
id, using film and inventory tables.
• Consider Film and inventory relations
SELECT film.film_id,title,inventory_id,store_id FROM film
LEFT JOIN inventory ON inventory.film_id=film.film_id;

➢ To find rows in Table A and not found in Table B

SELECT film.film_id,title,inventory_id FROM film LEFT JOIN
inventory ON inventory.film_id=film.film_id WHERE
inventory.film_id IS NULL;

RIGHT JOIN/ RIGHT OUTER JOIN

• The RIGHT JOIN is the reverse of LEFT JOIN.
The RIGHT JOIN query returns all the records
from the right table and matching records from the
left table.
• Here, the right side table is a table that comes to the
right side or after the "RIGHT JOIN" phrase in the
query, and the left table is a table that comes at the
left side or before the "RIGHT JOIN" phrase.
• The RIGHT JOIN returns NULL for all non-matching records from the left table. In
some databases, it is called RIGHT OUTER JOIN.
• Syntax:
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
Page | 24
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

➢ Example 3 Display records of all persons who have login ids and also reg id,
name, login id of all persons who have login ids as well as registration ids using
"Registrations " and "Logins" tables.
SELECT * from Registrations RIGHT OUTER JOIN Logins ON
[Link]=[Link];
OR
SELECT * from Registrations RIGHT JOIN Logins ON
[Link]=[Link];

➢ Example 4 Display records of all persons who have login id but not registration
id.
• To find rows in Table B and not found in Table A
SELECT * from Registrations RIGHT OUTER JOIN Logins ON
[Link]=[Link] WHERE Registrations.reg_id
IS NULL;

➢ Example 5 Display film id, title, inventory id and store id of all films using film
and inventory tables.
• Consider Film and inventory relations
SELECT film.film_id,title, inventory_id, store_id FROM film
RIGHT JOIN inventory ON inventory.film_id=film.film_id;

Page | 25
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

FULL JOIN/ FULL OUTER JOIN

• The FULL JOIN returns all the records all the specified
tables. It includes NULL for any non-matching records.
• In some databases, FULL JOIN is called FULL OUTER
JOIN. It can return a very large result set because it
returns all the rows from all the tables.
• Syntax:
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;
➢ Example 6 Display records of all persons who have registered and also all
persons who have logged in from Registrations and Logins.
• Taking the same data as above
SELECT * from Registrations FULL OUTER JOIN Logins ON
Registration [Link]=[Link];

➢ Example 7 Display reg id, name of all persons who do not have login ids and also
display login id, name of all persons who do not have registration id from
Registrations and Logins.
• Full outer join with where condition
SELECT * from Registrations FULL OUTER JOIN Logins ON
[Link]=[Link] WHERE Registrations.reg_id
IS NULL OR Logins.log_id IS NULL;

Page | 26
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

Sub Query
• A subquery is a command used within another query. In contrast, the INNER
SELECT or the INNER statement is called a SUBQUERY, and OUTER SELECT or
the OUTER statement is called the MAIN command. The PostgreSQL subquery is
enclosed in parentheses.

• The subqueries are used when we want to fetch a calculation with the help of an
aggregate function like Average, Count, Sum, Max, and Min function, but we do not
want the aggregate function to use into the MAIN query.
• There are a few rules that subqueries must follow −
• Subqueries must be enclosed within parentheses.
• A subquery can have only one column in the SELECT clause, unless multiple
columns are in the main query for the subquery to compare its selected
columns.
• When using certain comparison operators in a WHERE clause, if a subquery
(inner query) returns a null value to the parent query (outer query), then the
outer query won’t return any rows.
• Syntax:
SELECT column_name(s)FROM <table1>
WHERE <column> <OPERATOR> (SELECT <column> FROM
<table1> WHERE condition);
• Syntax with WHERE IN:
SELECT column1, column2
FROM table1
WHERE column1 IN (SELECT column1 FROM table2 WHERE
condition);
➢ Example 1 Find the films whose rental rate is higher than the average rental rate.
We can do it in two steps:
1. Find the average rental rate by using the SELECT statement and average function
( AVG).
2. Use the result of the first query in the second SELECT statement to find the films
that we want
Select AVG (rental_rate) FROM film;
• The average rental rate is 2.98
Page | 27
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

• Now, we can get films whose rental rate is higher than the average rental rate:
SELECT title, rental_rate FROM film WHERE rental_rate >
2.98;
• To construct a subquery, we put the second query in brackets and use it in the WHERE
clause as an expression:
Select title, rental_rate FROM film
WHERE rental_rate >(Select AVG (rental_rate) FROM film);

➢ Example 2 Find the customer id and customer name whose payment is more
than $ 8.99 also arrange the customer_id in ascending order.
SELECT customer_id,first_name,last_name FROM customer WHERE
customer_id IN (SELECT customer_id FROM payment WHERE
amount > 8.99) ORDER BY customer_id ASC;

Correlated Sub Query

• In subquery the inner subquery has been completely independent of the outer query.
• In a correlated subquery, the inner query uses information from the outer query and
executes once for every row in the outer query. This correlation is accomplished by
using a reference to the outside query within the subquery.
• The way a correlated subquery works is when a reference to the outer query is found
in the subquery, the outer query will be executed and the results returned to the
subquery. The subquery is executed for every row that is selected by the outer query.
• Due to the fact that the subquery in a correlated subquery can be executed for every
row returned in the outer query, performance can be degraded. With a sub-query,

Page | 28
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

performance is totally dependent upon the query and the data involved. However, if
written efficiently, a correlated subquery will outperform applications that use several
joins and temporary tables.
• Subqueries that return more than one row can only be used with multiple value
operators, such as the IN, EXISTS, NOT IN, ANY, ALL operator.
EXISTS and NOT EXISTS
• The "EXISTS" keyword in PostgreSQL is used in a subquery to check the existence
of rows in a table.
• The subquery should return at least one row if the condition is true, otherwise, it
returns no rows.
• The EXISTS operator is often used with the correlated subquery.
• EXISTS can be useful for conditions like checking if a record exists before
performing an action or for complex queries involving multiple tables.
• Syntax:
SELECT column_name(s) FROM table_name
WHERE EXISTS(subquery);
➢ Example 1 Find customers who have at least one payment whose amount is
greater than 11 also arrange the customer name in ascending order .
SELECT first_name, last_name FROM customer WHERE EXISTS
(SELECT customer_id FROM payment
WHERE customer.customer_id = payment.customer_id AND
amount > 11 ) ORDER BY first_name, last_name;

NOT EXISTS
➢ The NOT operator negates the result of the EXISTS operator. The NOT EXISTS
is opposite to EXISTS. It means that if the subquery returns no row, the NOT
EXISTS returns true. If the subquery returns one or more rows, the NOT
EXISTS returns false.
• Syntax:
SELECT column_name(s) FROM table_name
WHERE NOT EXISTS(subquery);

Page | 29
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

➢ Example 2 Find the customers who do not have even a single payment whose
amount is greater than 11
SELECT first_name, last_name FROM customer WHERE NOT EXISTS
(SELECT customer_id FROM payment WHERE
customer.customer_id = payment.customer_id AND amount > 11
) ORDER BY first_name, last_name;

ANY
• The "ANY" operator in PostgreSQL is used to compare a value to a set of values
returned by a subquery.
• The subquery should return a single column of values that can be compared to the
expression.
• ANY is commonly used with comparison operators like =, >, <, etc., to perform
comparisons against a set of values.
• Syntax:
SELECT column_name(s) FROM table_name
WHERE column_name <operator> ANY(subquery);
➢ Example 1 Find film title and length where length is greater than or equal to
any film category’s maximum length.
SELECT title,length FROM film
WHERE length >= ANY(SELECT MAX( length) FROM film INNER JOIN
film_category ON film.film_id = film_category.film_id
GROUP BY category_id );

Page | 30
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

ALL
• The "ALL" operator in PostgreSQL is used to compare a value to a set of values
returned by a subquery.
• It returns true if all of the values in the set match the given value, otherwise, it returns
false.
• The syntax for using ALL is: expression operator ALL (subquery);
• The subquery should return a single column of values that can be compared to the
expression.
• ALL is commonly used with comparison operators like =, >, <, etc., to perform
comparisons against a set of values.
• Syntax:
SELECT column_name(s)FROM table_name
WHERE column_name <operator> ALL(subquery);
➢ Example 1 Find all films with its title whose lengths are greater than the average
length by its ratings. Or
➢ Find film id, title and length of all films whose length is greater than the average
length (rounded upto 2 digits after decimal) of each rating category and display
in ascending order of length.
SELECT film_id, title, length FROM film
WHERE length > ALL (SELECT ROUND (AVG (length),2) FROM film
GROUP BY rating) ORDER BY length;

Correlated Sub Query with Select statement

• Syntax:
SELECT column_name
(SELECT column_name FROM table2
WHERE table2.foreign_key = table1.primary_key)
FROM table1;
➢ Example 1 Find the minimum amount and id of customer with their email.
SELECT customer_id, email,(SELECT MIN (amount)FROM PAYMENT
WHERE customer.customer_id=payment.customer_id)
FROM customer;

Page | 31
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

VIEWS
• A view is a stored query. A view can be accessed as a virtual table in PostgreSQL. In
other words, a PostgreSQL view is a logical table that represents data of one or more
underlying tables through a SELECT statement.
CREATING VIEW
• The PostgreSQL views are created using the CREATE VIEW statement. The
PostgreSQL views can be created from a single table, multiple tables, or another view.
• Syntax:
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];
➢ Example 1
CREATE VIEW customer_info AS
SELECT customer_id, first_name,last_nameFROM customer;

UPDATING VIEWS
• To change the definition of a view, you use the ALTER VIEW statement.
• Syntax:
ALTER VIEW View_name RENAME TO View_newname;
➢ Example 2
ALTER VIEW customer_info RENAME TO customer_detail;

Page | 32
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

DELETING VIEW
• To drop a view, simply use the DROP VIEW statement with the view_name.
• Syntax:
DROP VIEW view_name;
➢ Example 3
DROP VIEW customer_detail;
Transaction Control Command

ROLLBACK
• The ROLLBACK command is the transactional command used to undo transactions
that have not already been saved to the database.
• For suppose, the employee of the bank incremented the balance record of the wrong
person mistakenly then he can simply rollback and can go to the previous state.
• Syntax:
ROLLBACK TRANSACTION; (or) ROLLBACK;
➢ Example 1
CREATE TABLE BankStatements
(customer_id INT PRIMARY KEY,
full_name VARCHAR NOT NULL,
balance INT);
• Now we will insert data of some customers
INSERT INTO
BankStatements (customer_id, full_name, balance ) VALUES
(1, 'Sekhar rao', 1000),
(2, 'Abishek Yadav', 500),
(3, 'Srinivas Goud', 1000);
• Output
BEGIN;
DELETE from bankstatements WHERE customer_id=3;
SELECT * FROM bankstatements;

Page | 33
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

Rollback;
SELECT * FROM bankstatements; (TO CHECK THE TABLE)

COMMIT
• COMMIT command is used to save changes and reflect it in the database whenever
we display the required data.
• For suppose we updated data in the database but we didn’t give COMMIT then the
changes are not reflected in the database.
• To save the changes done in a transaction, we should COMMIT that transaction for
sure.
• The COMMIT command saves all transactions to the database since the last
COMMIT or ROLLBACK command.
• Syntax:
COMMIT TRANSACTION;(or) COMMIT;
➢ Example 2
CREATE TABLE BankStatements
(customer_id INT PRIMARY KEY,
full_name VARCHAR NOT NULL,
balance INT);
• Now we will insert data of some customers
INSERT INTO
BankStatements (customer_id, full_name, balance ) VALUES
(1, 'Sekhar rao', 1000),
(2, 'Abishek Yadav', 500),
(3, 'Srinivas Goud', 1000);
• Output
BEGIN;
DELETE from bankstatements WHERE customer_id=3;
SELECT * FROM bankstatements; (TO CHECK THE TABLE)

Commit;

Page | 34
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

Rollback;

SELECT * FROM bankstatements; (TO CHECK THE TABLE)

SAVEPOINT
• In the database systems, savepoints are helpful for performing complicated error
recovery.
• Using multiple lined statement, in a transaction, we are able to rollback to a savepoint
in the event, an error occurs, allowing the application to recover without having to
cancel the entire transaction.
• Syntax:
SAVEPOINT is declared inside the transaction with its name
SAVEPOINT savepoint_name;
To ROLLBACK to the specific SAVEPOINT use the following command,
ROLLBACK TO SAVEPOINT savepoint_name;
To destroy a SAVEPOINT at any stage inside the transaction use the
following statement,
RELEASE SAVEPOINT savepoint_name;
RELEASE SAVEPOINT destroys a previously defined specific
SAVEPOINT at any stage in the current transaction.
➢ Example 3
CREATE TABLE BankStatements
(customer_id INT PRIMARY KEY,
full_name VARCHAR NOT NULL,
balance INT);
• Now we will insert data of some customers
INSERT INTO
BankStatements (customer_id, full_name, balance ) VALUES
(1, 'Sekhar rao', 1000),
(2, 'Abishek Yadav', 500),
(3, 'Srinivas Goud', 1000);
• Output
Page | 35
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

• For SAVEPOINT t1
BEGIN;
INSERT INTO
BankStatements (customer_id, full_name, balance ) VALUES
(4, 'Priya Rathod', 200),
(5, 'Hiren Patel',800);
SAVEPOINT t1;

SELECT * FROM bankstatements; (TO CHECK THE TABLE)

• For SAVEPOINT t2 and ROLLBACK to SAVEPOINT t1

DELETE from bankstatements WHERE customer_id = 5;
SELECT * FROM bankstatements;

SAVEPOINT t2;

ROLLBACK to SAVEPOINT t1;

SELECT * FROM bankstatements; (TO CHECK THE TABLE)

Page | 36
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

BEGIN;
INSERT INTO
BankStatements (customer_id, full_name, balance ) VALUES
(4, 'Priya Rathod', 200),
(5, 'Hiren Patel',800);
SAVEPOINT t1;
SELECT * FROM bankstatements; (TO CHECK THE TABLE)
DELETE from bankstatements WHERE customer_id=5;
SELECT * FROM bankstatements; (TO CHECK THE TABLE)
SAVEPOINT t2;
ROLLBACK to SAVEPOINT t1;
SELECT * FROM bankstatements; (TO CHECK THE TABLE)

• For SAVEPOINT t3 and RELEASE SAVEPOINT t2

(Continue with previous command as we rollback to SAVEPOINT t1 )
Begin;
DELETE from bankstatements WHERE customer_id=5;
SELECT * FROM bankstatements; (TO CHECK THE TABLE)
SAVEPOINT t2;
UPDATE bankstatements SET balance=1000 WHERE customer_id=4;
SAVEPOINT t3;
SELECT * FROM bankstatements; (TO CHECK THE TABLE)

RELEASE SAVEPOINT t2;

Page | 37
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

• For ROLLBACK to SAVEPOINT t1

ROLLBACK to SAVEPOINT t1;
SELECT * FROM bankstatements; (TO CHECK THE TABLE)

BEGIN;
INSERT INTO
BankStatements (customer_id, full_name, balance ) VALUES
(4, 'Priya Rathod', 200),
(5, 'Hiren Patel',800);
SAVEPOINT t1;
SELECT * FROM bankstatements; (TO CHECK THE TABLE)
DELETE from bankstatements WHERE customer_id=5;
SELECT * FROM bankstatements; (TO CHECK THE TABLE)
SAVEPOINT t2;
UPDATE bankstatements SET balance=1000 WHERE customer_id=4;
SAVEPOINT t3;
RELEASE SAVEPOINT t2;
ROLLBACK to SAVEPOINT t1; (TO CHECK THE TABLE)

Page | 38
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

Question Bank Solution

We have following relations:

employees(emp_id,name,dept_id) salaries (emp_id,salary)
1) Find out the names of employees who belong to the same department as Mark.
Ans. SELECT name FROM employees WHERE dept_id
IN (SELECT dept_id FROM employees WHERE name= ‘Mark’);
2) Find out names of employees whose salaries are greater than 50,000.
Ans. select name from employees where emp_id
IN(select emp_id from salaries where salary>50000);
3) Retrieve the names of employees who have corresponding entries of the salary.
Ans. SELECT name FROM employees
WHERE EXISTS (SELECT * FROM salaries
WHERE employees.emp_id=salaries.emp_id);
We have following relation
orders(order_id, customer_id, order_date, amount)
1) Find out the number of orders for each customer by customer_id.
Ans. SELECT customer_id, COUNT(*) FROM orders
GROUP BY customer_id;
2) Find out the total amount by order_id and order_date.
Ans. SELECT order_id, order_date, SUM(amount)
FROM orders GROUP BY order_id,order_date;
3) Find out the number of orders for each customer by customer_id. Show only
customer_id with number of orders above 5.
Ans. SELECT customer_id, COUNT(*) FROM orders
GROUP BY customer_id HAVING COUNT(*)>5;
We have following relation
movie_streaming (id, date, movie, director, number_of_streams)
1) Find the total number of streams by date.
[Link] date, SUM(number_of_streams)
FROM movie_streaming GROUP BY date;
2) Find the total number of streams by date and director.
Ans. SELECT date, director, SUM (number_of_streams)
FROM movie_streaming GROUP BY date, director;
Page | 39
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

3) Find the total number of streams by date and director. Show only dates with a
total number of streams above 740.
Ans. SELECT date, director, SUM(number_of_streams)
FROM movie_streaming
GROUP BY date, director
HAVING SUM(number_of_streams) >740;
We have following relation
EMP (empno, ename, jobtitle, manager_no, hiredate, salary, deptno)
DEPT (deptno, dname,location)
1) Find employees whose name start with letter A or letter a.
Ans. SELECT ename FROM emp
WHERE ename LIKE 'a%' OR ename LIKE 'A%';
2) Find the employees who are working in Smith's department.
Ans. SELECT ename FROM EMP WHERE deptno
IN(SELECT deptno FROM EMP WHERE ename = ‘Smith’);
3) Display employees who are getting maximum salary in each department.
Ans. SELECT ename FROM EMP WHERE salary IN
(SELECT MAX(salary) FROM EMP GROUP BY deptno);
Consider following relations
Instructor (id, name, dept_name, salary)
Teaches (id, course_id, sec_id, sem(even/odd), year)
1) Find the number of instructors who teach a course in even semester of 2016.
Ans. SELECT COUNT(id) FROM Teaches
WHERE sem = ‘even’ AND year = 2016;
2) List the instructors who are not teaching in CE Department.
Ans. SELECT id FROM Instructor WHERE dept_name <> ‘CE’;
We have following relations
EMP (EmpID, Empname, DepID, salary) DEP (DepID, DepName)
1) List all details of Employees whose salary is same as FORD or SMITH in desc
order of Salary.
Ans. SELECT * FROM EMP WHERE salary
IN (SELECT salary FROM EMP
WHERE Empname = ‘SMITH’ OR Empname = ‘FORD’)
ORDER BY salary desc;
2) Find out the empid, empname and depname having same depid.
Page | 40
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

Ans. SELECT EmpID, Empname, DepName

FROM EMP INNER JOIN DEP
ON [Link] = [Link];
We have following relations
EMP (empid, ename, depid, job, salary, deptno) DEP (depid, depname)
1) List the employees details whose jobs are same as ALLEN.
Ans. SELECT * FROM EMP
WHERE job = (SELECT job FROM EMP
WHERE ename = ‘ALLEN’);
2) Find the employees details who are not working in sales Department.
Ans. SELECT * FROM EMP WHERE deptid
NOT IN(SELECT deptid FROM DEP
WHERE depname = ‘sales’);
We have following relation
worker(id, name, depid, salary, deptno)
1) List the worker details in dept 20 whose salary is greater than the average
salary of dept 10 employees.
Ans. SELECT * FROM worker
WHERE deptno=20 AND salary > (SELECT AVG(salary)
FROM worker WHERE deptno=10);
Write query for the following.
employee (id, name, salary, address) department(d_id, d_name, id)
1) Create a view department_details of department table.
Ans. CREATE VIEW department_details AS
SELECT * FROM department;
2) Join two existing tables using inner join.
Ans. SELECT * FROM employee INNER JOIN department
ON [Link] = [Link];
3) To drop a view.
Ans. DROP VIEW department_details;

We have following relations:

Employees (eid, first_name, last_name, email, salary, department_id)
Departments (eid, did, department_name, location_id)
Page | 41
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

1) Find the details of employees who have the highest salary.

Ans. SELECT * FROM Employees
WHERE salary = (SELECT MAX(salary) FROM Employees);
2) Display all the employee name along with department name who are working
neither in ‘HR’ Department nor earns more than 50000.
Ans. SELECT first_name, last_name, department_name
FROM Employees INNER JOIN Departments
ON [Link] = [Link]
AND department_name <> ‘HR’ AND salary <50000;
We have following relations:
Supplier (S#, sname, status, city)
Parts (P#, pname, color, weight, city)
SP (S#, P#, quantity)
Answer the following queries.
1) Find S# of supplier who supplies ‘red’ part.
Ans. SELECT DISTINCT Supplier. S#
FROM Supplier INNER JOIN SP
ON Supplier.S# = SP.S# INNER JOIN Parts
ON SP. P# = Parts. P# AND color='red';
2) Count number of supplier who supplies ‘red’ part.
Ans. SELECT COUNT(*)
FROM Supplier where S# IN (SELECT DISTINCT Supplier.S#
FROM Supplier INNER JOIN SP
ON Supplier.S# = SP.S#
INNER JOIN Parts ON SP.P# = Parts.P# AND color='red');
3) Sort the supplier table by sname?
Ans. SELECT * FROM Supplier ORDER BY sname;
We have following relations
EMP (empno, ename, jobtitle, manager, hiredate, salary, deptno)
DEPT (deptno, dname,location)
Answer the following queries.
1) Find the Employees who get salary more than Chris salary.
Ans. SELECT ename FROM EMP
WHERE salary > (SELECT salary
Page | 42
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

FROM EMP WHERE ename= ‘Chris’);

2) Display department number along with the number of employees which
belongs to that department number.
Ans. SELECT deptno, COUNT (empno)FROM EMP GROUP BY deptno;
Write queries for the following tables:
T1 ( Empno, Ename , Salary, Designation) T2 (Empno, Deptno)
(1) Display the Deptno in which Employee Seeta is working.
Ans. SELECT Deptno
FROM T2 INNER JOIN T1
ON [Link] = [Link] AND Ename = ‘Seeta’;
(2) Display Empno, Ename, Deptno.
Ans. SELECT [Link], Ename, Deptno
FROM T1 INNER JOIN T2 ON [Link] = [Link];
Consider following schema and write SQL for given statements.
Student (RollNo, Name, DeptCode, City)
Department (DeptCode, DeptName)
Result (RollNo, Semester, SPI)
1. List out the RollNo, Name along with SPI of Student.
Ans. SELECT [Link], Name, SPI
FROM Student INNER JOIN Result
ON [Link]=[Link];
2. Display student name who got highest SPI in semester 1.
Ans. SELECT Name FROM Student INNER JOIN Result
ON [Link]=[Link] WHERE Semester=1 AND
SPI=(SELECT MAX (SPI) FROM Result);
3. Display the list of students whose DeptCode is 5, 6,7,10.
Ans. SELECT Name FROM Student WHERE Deptcode IN (5,6,7,10);
Consider the relation Database.
Person (SSN, name, city) Car (license_no, year, model, SSN)
Accident (drive_no, SSN, license_no, accidentyear, damage_amt)
1) Find the name of driver who did not have an accident in 'Delhi'.
Ans. SELECT name FROM Person INNER JOIN Accident
ON [Link] = [Link] AND city<>‘delhi’;
Page | 43
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

2) Find the cars sold in 2006 and whose owner are from vadodara.
Ans. SELECT license_no
FROM Car INNER JOIN Person
ON [Link]=[Link] AND year=2006 AND city=’vadodara’;
3) How many different models of car are used by [Link].
Ans. SELECT COUNT (model)
FROM Car INNER JOIN Person
ON [Link]=[Link] AND name = ‘[Link]’;
Consider following schema and write SQL for given statements.
worker (id, firstname, lastname, salary, joining_date, dept)
bonus (id, bonus_date, amount)
1) Find firstname and lastname of worker whose amount is greater than 2400.
Ans. SELECT firstname, lastname
FROM worker INNER JOIN bonus
ON [Link] = [Link] AND amount >2400 ;
2) List out salary of worker with id who got bonus.
Ans. SELECT salary,[Link]
FROM worker INNER JOIN bonus
ON [Link] = [Link];
Consider following schema and write SQL for given statements.
title (id, designation, DOJ)
bonus (id, bonus_date, amount)
1) List out bonus id whose designation is MANAGER .
Ans. SELECT [Link] FROM title INNER JOIN bonus
ON [Link] = [Link] AND designation = ‘MANAGER’;
2) List out id's whose bonus amount at most 4000 and designation is admin
Ans. SELECT [Link] FROM title INNER JOIN bonus
ON [Link]=[Link] AND amount <= 4000
AND designation = ‘admin’;

For given relation:

Employee (eid, ename, address, deptname , salary)
Project (eid, pid, pname, location)
Page | 44
 L.J Institutes of Engineering and Technology
Semester: II Subject Database Management System
Unit-7 Structured Query Language (SQL)

1) Display name and salary of employee who is taking maximum salary.

Ans. SELECT ename, salary FROM Employee
WHERE salary = (SELECT MAX (salary) FROM Employee);
2) Display highest salary department wise and name of employee who is taking that
salary.
Ans. SELECT ename, salary, deptname FROM Employee
WHERE salary IN (SELECT MAX (salary) FROM Employee
GROUP BY deptname);
3) Find details of employee who works on a pid equal to 10.
Ans. SELECT * FROM Employee INNER JOIN Project
ON [Link]=[Link] AND pid=10;
Consider the following student relation:
Student(name, rollno, marks, percentage, address, dob)
Create an view from relation Student with fields name,rollno,percentage
Ans. CREATE VIEW Student_details AS
SELECT name, rollno, percentage FROM Student;
Write queries for the following.
Employee (EID, Name, Age, Salary)
Department (DID, D_Name, EID, Country)
1) Create a view Emp_India which contains the name, age and salary of Indian
employees.
Ans. CREATE view Emp_India AS
SELECT Name, Age, Salary
FROM Employee INNER JOIN Department
ON [Link] = [Link]
WHERE Country = ‘India’;
2) Display the name of employee in descending order whose Country starts with ‘I’.
Ans. SELECT Name FROM Employee
INNER JOIN Department
ON [Link] = [Link]
WHERE Country like ‘I%’ ORDER BY Name DESC;

Page | 45