Database Management System
UNIT 2
Relational data model and language
SQL
LEC-4
Today’s Target
Some Operators
By PRAGYA RAJVANSHI
AKTU PYQs
[Link], [Link]( C.S.E.)
SQL UNION Operator Syntax
SELECT column_names FROM table1
The UNION operator in SQL is used to UNION
combine the result set of multiple SELECT SELECT column_names FROM table2;
statements and return one result set. EMP1
Rules for SQL UNION
Each table used within UNION must
have the same number of columns.
The columns must have the same data
types.
The columns in each table must be in
the same order.
Emp2
SQL UNION ALL Example
find the cities (only unique values) from both the
we will find the cities (duplicate values also)
“Table1” and the “Table2” tables: from both the “Emp1” and the “Emp2” tables:
. Except Clause
EXCEPT returns those tuples that are returned by the first SELECT operation,
and not returned by the second SELECT operation.
Students and TA (Teaching Assistant). We want to return all those students
who are not teaching assistants.
To retain duplicates, we must explicitly EXISTS
write EXCEPT ALL instead of EXCEPT. The EXISTS condition in SQL is used to check
whether the result of a correlated nested
query is empty (contains no tuples) or not.
The result of EXISTS is a Boolean value True or
False.
It can be used in a SELECT, UPDATE, INSERT or
DELETE statement
Difference between EXCEPT and NOT IN Clause
EXCEPT automatically removes all duplicates in the
final result, whereas NOT IN retains duplicate
tuples. It is also important to note that EXCEPT is
not supported by MySQL.
SYNTAX
SELECT column_name(s) FROM table_name
WHERE EXISTS (SELECT column_name(s)
FROM table_name
WHERE condition);
To fetch the first and last name of the Using NOT with EXISTS Fetch last and first name
customers who placed at least one order. of the customers who has not placed any order.
Q
Q.1 Retrieve the birth date and address of
the employee(s) whose name is ‘John B.
Smith’ Q.3. For every project located in ‘Stafford’, list
the project number, the controlling department
number, and the department manager’s last
name, address, and birth date.
NOTE “,” IS USED FOR CROSS JOIN and cross
product
Q.2 Retrieve the name and address of all
employees who work for the ‘Research’
department.
Q Q.4 For each employee, retrieve the Q.6 Retrieve all employees in department 5
employee’s first and last name and the first and whose salary is between $30,000 and
last name of his or her immediate supervisor. $40,000 and belong to dno=5
Q.7Retrieve the salary of every employee and
all distinct salary values
Q.5 Retrieve all employees whose address is
in Houston, Texas.
Salesman(salesman_id,city,name,commission)
1. Find the details of those salespeople who
come from the Paris City or 'Rome' City. Customer (Customer_id | cust_name | city |
grade | salesman_id )
3. Retrieve the details of all customers whose ID
belongs to any of the values 3007, 3008 or 3009
2. write a SQL query to find the details of those
salespeople who live in cities other than Paris
and Rome.
Order(ord_no purch_amt ord_date Salesman(salesman_id,city,name,commissio
customer_id salesman_id) n)
4. select orders between 500 and 4000 (begin and 5. To find the details of all salespeople except
end values are included). Exclude orders amount those whose names begin with any letter
948.50 and 1983.43. between 'A' and 'M' .
Customer (Customer_id | cust_name | city | Salesman (salesman_id | name | city |
grade | salesman_id ) commission)
6. retrieve the details of the customers whose 8. write a SQL query to find the details of those
names begins with the letter 'B'. salespeople whose names begin with ‘N’ and the
fourth character is 'l'. Rests may be any character.
7. write a SQL query to find the details of the
customers whose names end with the letter 'n'.
Customer (Customer_id | cust_name | city | orders
ord_no, purch_amt, ord_date, customer_id ,
grade | salesman_id ) salesman_id
9. find all those customers who does not have any 12. to calculate total purchase amount of all
grade. orders.
10. count the number of customers.
13. calculate the average purchase amount of all
11. write a SQL query to determine the number of orders.
customers who received at least one grade for
their activity. 14. write a SQL query that counts the number of
unique salespeople.
orders 18. write a SQL query to find the highest purchase
(ord_no ,purch_amt ,ord_date, customer_id amount ordered by each customer on a particular
,salesman_id) date
15. write a SQL query to find the maximum
purchase amount.
19. To determine the highest purchase amount
16. write a SQL query to find the minimum made by each salesperson on '2012-08-17‘
purchase amount.
17. To find the highest grade of the customers in 20. To count all the orders generated on '2012-08-
each city 17'.
orders
(ord_no ,purch_amt ,ord_date, customer_id
,salesman_id)
21. To count the number of orders based on the
combination of each order date and salesperson.
Return order date, salesperson id.