MySQL Advanced Querying

Queries involving multiple tables

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)
);

insert into CUSTOMERS values (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
insert into CUSTOMERS values (2, 'Khilan', 25, 'Delhi', 1500.00 );
insert into CUSTOMERS values (3, 'Kaushik', 23, 'Kota', 2000.00 );
insert into CUSTOMERS values (4, 'Chaitali', 25, 'Mumbai', 6500.00 );
insert into CUSTOMERS values (5, 'Hardik', 27, 'Bhopal', 8500.00 );
insert into CUSTOMERS values (6, 'Komal', 22, 'MP', 4500.00 );
insert into CUSTOMERS values (7, 'Muffy', 24, 'Indore', 10000.00 );

create table ORDERS(
   oid		 int		  not null,
   date          datetime,
   customer_id   int              not null,
   amount        int		  not null,
   primary key(oid),
   foreign key (customer_id) references CUSTOMERS(id) on delete cascade
);

insert into ORDERS values (100,'2009-10-08',3,1500);
insert into ORDERS values (101,'2009-11-20',2,1560);
insert into ORDERS values (102,'2009-10-08',3,3000);
insert into ORDERS values (103,'2008-05-20',4,2060);
insert into ORDERS values (104,'2010-10-07',3,500);
insert into ORDERS values (105,'2010-10-10',6,6560);
insert into ORDERS values (106,'2011-10-18',3,3200);
insert into ORDERS values (107,'2011-05-22',5,2960);
insert into ORDERS values (108,'2011-05-12',5,2960);
insert into ORDERS values (109,'2011-06-22',5,2960);
insert into ORDERS values (110,'2012-10-08',4,500);
insert into ORDERS values (111,'2012-11-20',2,560);
insert into ORDERS values (112,'2012-10-08',3,1000);
insert into ORDERS values (113,'2012-05-20',4,4060);
insert into ORDERS values (114,'2012-10-07',4,3500);
insert into ORDERS values (115,'2012-10-10',3,7560);
insert into ORDERS values (116,'2012-10-18',3,200);
insert into ORDERS values (117,'2012-05-22',5,960);
insert into ORDERS values (118,'2012-05-12',5,1960);
insert into ORDERS values (119,'2012-06-22',5,4960);

Join Queries

--Retrieve ids and names of customers who have placed orders

select distinct id, name
from CUSTOMERS, ORDERS
where  CUSTOMERS.id = ORDERS.customer_id;

-- Retrieve ids and names of customers who have placed an order
-- with amount more than $5000

select id, name
from CUSTOMERS, ORDERS
where  id = customer_id and
       amount > 5000;

-- Retrieve ids and names of customers from 'Kota' who have placed an order
-- with amount more than $5000

select id, name
from CUSTOMERS, ORDERS
where  id = customer_id and
       address = 'Kota' and
       amount > 5000;

-- Retrieve ids and names of customers who have placed orders
-- in 2011

select distinct id, name
from CUSTOMERS, ORDERS
where  id = customer_id and
       year(date) = 2011;

Union Queries

-- Retrieve ids and names of customers who are older than 30 or
-- have a salary more than 8000
(select distinct id, name
from CUSTOMERS
where age > 30)
union
(select distinct id, name
from CUSTOMERS
where salary > 8000);

AGGREGATE Operations (count, sum, min, max, avg)

-- Retrieve the number of customers
select count(id)
from CUSTOMERS;

-- Retrieve the number of orders placed by customer named 'Kaushik'
select count(oid)
from CUSTOMERS, ORDERS
where id = customer_id and
   name = 'Kaushik';

-- Retrieve the total amount of all orders placed by customer named 'Kaushik'
select sum(amount)
from CUSTOMERS, ORDERS
where id = customer_id and
   name = 'Kaushik';

GROUP BY and HAVING clauses

-- Retrieve ids and names of customers along with the count of the number
-- of orders each has placed
select id, name, count(oid) NUMBER_OF_ORDERS
from CUSTOMERS, ORDERS
where id = customer_id
group by id, name;

-- Retrieve ids and names of customers along with the count of the number
-- of orders each has placed, only if the the number of orders is more than 5
select id, name, count(oid) NUMBER_OF_ORDERS
from CUSTOMERS, ORDERS
where id = customer_id
group by id, name
having count(oid) > 5;

-- Retrieve ids and names of customers along with the total amount
-- of all orders each has placed
select id, name, sum(amount) TOTAL_SALES
from CUSTOMERS, ORDERS
where id = customer_id
group by id, name;

NESTED QUERIES

-- Get number of rooms for each building, include buildings with 0 rooms
(select bcode, sum(cap)
 from   room
 group by bcode
)
union
(select bcode, 0 
 from building 
 where bcode not in 
   (select distinct bcode 
    from room)
)