SQL is a standard language for storing, manipulating and retrieving data in databases.
In this series of posts, we will discuss the SQL queries from very Basic to Advanced.
It is recommended to view the post in the following order:
1. SQL Basic Queries Example – I
2. SQL Basic Queries Example – II
3. SQL Queries Using Aggregate Functions – I
4. SQL Queries Using Aggregate Functions – II
5. SQL Intermediate Queries Example – I
6. SQL Intermediate Queries Example – II
7. SQL Advanced Queries Example – I
8. SQL Advanced Queries Example – II
In this post, we will look at some intermediate SQL queries.
Create the tables described below:
Table Name: sales
+---------+------------+------------+---------------+--------------+ | OrderId | OrderDate | OrderPrice | OrderQuantity | CustomerName | +---------+------------+------------+---------------+--------------+ | 1 | 2005-12-22 | 160 | 2 | Smith | | 2 | 2005-08-10 | 190 | 2 | Johnson | | 3 | 2005-07-13 | 500 | 5 | Baldwin | | 4 | 2005-07-15 | 420 | 2 | Smith | | 5 | 2005-12-22 | 1000 | 4 | Wood | | 6 | 2005-10-02 | 820 | 4 | Smith | | 7 | 2005-11-03 | 2000 | 2 | Baldwin | | 8 | 2002-12-22 | 1000 | 4 | Wood | | 9 | 2004-12-29 | 5000 | 4 | Smith | +---------+------------+------------+---------------+--------------+
Command:
create table sales(OrderId int primary key, OrderDate date, OrderPrice int, OrderQuantity int, CustomerName varchar(20)); insert into sales values(1,'2005-12-22',160,2,'Smith'); insert into sales values(2,'2005-08-10',190,2,'Johnson'); insert into sales values(3,'2005-07-13',500,5,'Baldwin'); insert into sales values(4,'2005-07-15',420,2,'Smith'); insert into sales values(5,'2005-12-22',1000,4,'Wood'); insert into sales values(6,'2005-10-02',820,4,'Smith'); insert into sales values(7,'2005-11-03',2000,2,'Baldwin'); insert into sales values(8,'2002-12-22',1000,4,'Wood'); insert into sales values(9,'2004-12-29',5000,4,'Smith');
Table Name: products
+------------+---------+------------------+--------------+-----------+ | Product_id | OrderId | Manufacture_Date | Raw_Material | Vender_id | +------------+---------+------------------+--------------+-----------+ | AZ145 | 2 | 2005-12-23 | Steel | 1 | | AZ147 | 6 | 2002-08-15 | Steel | 3 | | CS435 | 5 | 2001-11-04 | Steel | 1 | | CS783 | 1 | 2004-11-03 | Plastic | 2 | | CS784 | 4 | 2005-11-28 | Plastic | 2 | | FD123 | 2 | 2005-10-03 | Milk | 2 | | FD267 | 5 | 2002-21-03 | Bread | 4 | | FD333 | 9 | 2001-12-12 | Milk | 1 | | FD344 | 3 | 2005-11-03 | Milk | 1 | | GR233 | 3 | 2005-11-30 | Pulses | 2 | | GR567 | 6 | 2005-09-03 | Pulses | 2 | +------------+---------+------------------+--------------+-----------+
Command:
create table products(Product_id varchar(20) primary key,
OrderId int,
Manufacture_Date varchar(20),
Raw_Material varchar(20),
Vender_id int);
insert into products values('AZ145',2,'2005-12-23','Steel',1);
insert into products values('CS784',4,'2005-11-28','Plastic',2);
insert into products values('AZ147',6,'2002-08-15','Steel',3);
insert into products values('FD344',3,'2005-11-03','Milk',1);
insert into products values('GR233',3,'2005-11-30','Pulses',2);
insert into products values('FD123',2,'2005-10-03','Milk',2);
insert into products values('CS783',1,'2004-11-03','Plastic',2);
insert into products values('CS435',5,'2001-11-04','Steel',1);
insert into products values('GR567',6,'2005-09-03','Pulses',2);
insert into products values('FD267',5,'2002-21-03','Bread',4);
insert into products values('FD333',9,'2001-12-12','Milk',1);
Table name: vender_info
+-----------+-------------+ | Vender_id | Vender_name | +-----------+-------------+ | 1 | Smith | | 2 | Wills | | 3 | Johnson | | 4 | Roger | +-----------+-------------+
Command:
create table vender_info(Vender_id int primary key, Vender_name varchar(20)); insert into vender_info values(1,'Smith'); insert into vender_info values(2,'Wills'); insert into vender_info values(3,'Johnson'); insert into vender_info values(4,'Roger');
Table name: venders
+--------------+---------+-----------+ | Raw_Material | Venders | Vender_id | +--------------+---------+-----------+ | Steel | Smith | 1 | | Plastic | Wills | 2 | | Steel | Johnson | 3 | | Milk | Smith | 1 | | Pulses | Wills | 2 | | Bread | Roger | 4 | | Bread | Wills | 2 | | Milk | Wills | 3 | +--------------+---------+-----------+
Command:
create table venders(Raw_Material varchar(20), Venders varchar(20), Vender_id int);
insert into venders values('Steel','Smith',1);
insert into venders values('Plastic','Wills',2);
insert into venders values('Steel','Johnson',3);
insert into venders values('Milk','Smith',1);
insert into venders values('Pulses','Wills',2);
insert into venders values('Bread','Roger',4);
insert into venders values('Bread','Wills',2);
insert into venders values('Milk','Wills',3);Exercise on Intermediate Queries
a) Display product information which is ordered in the same year of its manufacturing year.
Command: select p.* from products p, sales s where year(p.Manufacture_date) = year(s.OrderDate) and p.OrderId = s.OrderId; Output: +------------+---------+------------------+--------------+-----------+ | Product_id | OrderId | Manufacture_Date | Raw_Material | Vender_id | +------------+---------+------------------+--------------+-----------+ | AZ145 | 2 | 2005-12-23 | Steel | 1 | | CS784 | 4 | 2005-11-28 | Plastic | 2 | | FD123 | 2 | 2005-10-03 | Milk | 2 | | FD344 | 3 | 2005-11-03 | Milk | 1 | | GR233 | 3 | 2005-11-30 | Pulses | 2 | | GR567 | 6 | 2005-09-03 | Pulses | 2 | +------------+---------+------------------+--------------+-----------+
b) Display product information which is ordered in the same year of its manufacturing year where the vender is ‘smith’.
Command: select p.* from products p, sales s where year(p.Manufacture_date) = year(s.OrderDate) and p.OrderId = s.OrderId and p.Vender_id = (select Vender_id from vender_info where Vender_name = 'Smith'); Output: +------------+---------+------------------+--------------+-----------+ | Product_id | OrderId | Manufacture_Date | Raw_Material | Vender_id | +------------+---------+------------------+--------------+-----------+ | AZ145 | 2 | 2005-12-23 | Steel | 1 | | FD344 | 3 | 2005-11-03 | Milk | 1 | +------------+---------+------------------+--------------+-----------+
c) Display the total number of orders placed in each year.
Command: select sum(OrderQuantity) as sum, year(OrderDate) from sales group by year(OrderDate); Output: +------+-----------------+ | sum | year(OrderDate) | +------+-----------------+ | 4 | 2002 | | 4 | 2004 | | 21 | 2005 | +------+-----------------+
d) Display the total number of orders placed in each year by vender Wills.
Command: select count(*),a.OrderDate,c.Vender_name from sales a natural join products b natural join vender_info c where c.Vender_name = 'Wills' group by year(a.OrderDate); Output: +----------+------------+-------------+ | count(*) | OrderDate | Vender_name | +----------+------------+-------------+ | 5 | 2005-12-22 | Wills | +----------+------------+-------------+
e) Display the name of all those persons who are vendors and customers both.
Command: select Vender_name from vender_info where Vender_name in (select CustomerName from sales); Output: +---------+ | Venders | +---------+ | Smith | | Johnson | +---------+
f) Display the total number of food items ordered every year.
Command: select year(OrderDate),sum(OrderQuantity) from sales group by year(OrderDate); Output: +-----------------+--------------------+ | year(OrderDate) | sum(OrderQuantity) | +-----------------+--------------------+ | 2002 | 4 | | 2004 | 4 | | 2005 | 21 | +-----------------+--------------------+
g) Display the total number of food items ordered every year made from bread.
Command: select year(OrderDate),sum(OrderQuantity) from sales where OrderId in (select OrderId from products where Raw_Material = 'Bread') group by year(OrderDate); Output: +-----------------+--------------------+ | year(OrderDate) | sum(OrderQuantity) | +-----------------+--------------------+ | 2005 | 4 | +-----------------+--------------------+
h) Display list of product_id whose vendor and customer is different.
Command: select a.Product_id from products a natural join vender_info b natural join sales c where b.Vender_name != c.CustomerName; Output: +------------+ | Product_id | +------------+ | AZ145 | | CS435 | | FD344 | | CS783 | | CS784 | | FD123 | | GR233 | | GR567 | | AZ147 | | FD267 | +------------+
i) Display all those customers who are ordering products of milk by smith.
Command: select c.CustomerName from products a natural join vender_info b natural join sales c where b.Vender_name = 'Smith' and a.Raw_Material = 'Milk'; Output: +--------------+ | CustomerName | +--------------+ | Smith | | Baldwin | +--------------+
j) Display the total number of orders by each vender every year.
Command: select sum(c.OrderQuantity), b.Vender_name, year(c.OrderDate) from products a natural join vender_info b natural join sales c group by Vender_name, year(OrderDate); Output: +----------------------+-------------+-------------------+ | sum(c.OrderQuantity) | Vender_name | year(c.OrderDate) | +----------------------+-------------+-------------------+ | 4 | Johnson | 2005 | | 4 | Roger | 2005 | | 4 | Smith | 2004 | | 11 | Smith | 2005 | | 15 | Wills | 2005 | +----------------------+-------------+-------------------+
k) Display name of those vendors whose products are sold more than 2000 Rs. Every year.
Command: select Vender_name,year(OrderDate), sum(OrderPrice*OrderQuantity) as TotalAmount from products a natural join vender_info b natural join sales c group by b.Vender_name,year(OrderDate) having sum(c.OrderPrice*c.OrderQuantity) > 2000; Output: +-------------+-----------------+-------------+ | Vender_name | year(OrderDate) | TotalAmount | +-------------+-----------------+-------------+ | Johnson | 2005 | 3280 | | Roger | 2005 | 4000 | | Smith | 2004 | 20000 | | Smith | 2005 | 6880 | | Wills | 2005 | 7320 | +-------------+-----------------+-------------+
