SQL tutorial in detail with exercises and it's solutions

I have taken MySQL to explain queries:

MySQL Introduction

Note: “MySQL” it third party (“sun micro system”)

C:\mysql  –u  root

Types of Table (Engine)

MyISAM: Foreign key constraint does not support

InnoDB: used to support foreign key constraint

BDB: support for UNIX environment

Heap: it is temporary or virtual table, which is created only in memory not in hard disk

Merge: it is used, if we want to merge more than one table (it is also temporary or virtual table)

Syntax:

Create table list ( — , — , — )engine=InnoDB;

MySQL Commands:

Show databases;

Create database db_name;

Use dbname;

Show tables;

Create table tb_name(id int, name varchar(20));

Desc tb_name;

Insert into tb_name values(101 , ‘lokesh kakkar’);

Insert into tb_name (id) values(102);

Update tb_name set name=’luck key’ where id=102;

Select * from tb_name;

Delete from tb_name where id=102;

How to Import data from any file:

Mysql –u root <db.sql  (for database and tables)

Mysql –u root <data.sql (for data into tables)

Create these tables and enter their values:

1. Customer (customer_id, customer_name, address)
2. Employee (employee_id, employee_name)
3. Product (product_id, product_name, product_rate)
4. Master (order_id, description)
5. Order_detail (order_id, product_id, quantity)
6. Order_master (order_id, order_date, customer_id, employee_id)

Data of Customer Table:

Customer_id	Customer_name	Address
C0001	‘Ricky’	‘Calofornia’
C0002	‘Thompson’	null
C0003	‘Adams’	null
C0004	‘Smith’	‘Texas’
C0005	‘John’	‘Florida’

Data of Employee Table:

Employee_id	Employee_name
E0001	‘Bob’
E0002	‘Maria’
E0003	‘Peter’
E0004	‘Kelvin’
E0005	‘Herbert’
E0005	‘Michael’

Data of Product Table:

Product_id	Product_name	Product_rate
P0001	‘Pepsi’	5.00
P0002	‘Coca Cola’	10.00
P0003	‘Thums Up’	11.00
P0004	‘Limca’	13.00
P0005	‘Vanilla Shake’	15.00
P0006	‘Strawberry Shake’	17.00
P0007	‘Chocolate Shake’	20.00
P0008	‘Pineapple Juice’	16.00
P0009	‘Orange Juice’	12.00
P0010	‘Mango Juice’	15.00
P0011	‘Apple Juice’	25.00
P0012	‘Lime Water’	5.00
P0013	‘Fanta’	7.00

Data of Master Table:

Order_id (int)	Description
101	‘Total Quantity 55’
102	‘Total Quantity 35’

 

Data of Order_detail Table:

Order_id (int)	Product_id	Quantity (int / decimal (10,2)
101	‘P0003’	20
101	‘P0006’	35
102	‘P0003’	Null
102	‘P0010’	25
102	‘P0006’	10
103	‘P0011’	50
103	‘P0001’	18
104	‘P0008’	5
105	‘P0003’	25
105	‘P0001’	30
106	‘P0001’	10
107	‘P0002’	5
108	‘P0007’	32
109	‘P0002’	21
110	‘P0009’	28
111	‘P0012’	42
112	‘P0003’	40
113	‘P0004’	Null
114	‘P0005’	28

Data of Order_master Table:

Order_id (int)	Order_date	Customer_id	Employee_id
101	‘13-04-2009 03:42:00’	‘C0001’	‘E0001’
102	‘13-04-2009 03:42:00’	‘C0002’	‘E0004’
103	‘13-04-2009 03:42:00’	‘C0002’	‘E0005’
104	‘13-04-2009 03:42:00’	‘C0004’	‘E0002’
105	‘13-04-2009 03:42:00’	‘C0005’	‘E0003’
106	‘13-04-2009 03:42:00’	‘C0003’	‘E0005’
107	‘13-04-2009 03:42:00’	‘C0001’	‘E0002’
108	‘13-04-2009 03:42:00’	‘C0002’	‘E0001’
109	‘13-04-2009 03:42:00’	‘C0003’	‘E0004’
110	‘13-04-2009 03:42:00’	‘C0005’	‘E0005’
111	‘13-04-2009 03:42:00’	‘C0004’	‘E0005’
112	‘13-04-2009 03:42:00’	‘C0001’	‘E0003’
113	‘13-04-2009 03:42:00’	‘C0002’	‘E0002’
114	‘13-04-2009 03:42:00’	‘C0004’	‘E0001’

EXERCISE on SQL operations or general queries:

1. Display the name of all products with their rate and increased rate by 20%
2. Display the name of all products with their rate and discount by 20%
3. Display the name of those products with their rate, whose rates are more than Rs.12
4. Display the name of those products, with their rate (including Rs.4 service charges), whose rates are more than Rs.20
5. Display the name of those customers, whose address is null
6. Display the information (name and rates) of all shakes
7. Display the name of those products, having second character ‘i’
8. Display those transactions, whose order quantity with 30 to 50 inclusive
9. Display the name of those products whose rates are 5, 15, and 25
10. Display those transactions, whose order quantity with 30 to 50 exclusive
11. Display the name of those products, which starts with ‘P’ or ‘L’
12. Display the name of those customers, whose address is not null
13. Display the name of those products, which starts neither with ‘P’ or ‘L’

EXERCISE on SQL functions:

1. Display the name of all customers in lowercase, uppercase and length of name
2. Display the name of those products, whose sub-string starting from 2 having length 3
3. Display current date, current time, current date and time
4. Display system date
5. Display name of products with their rate, rate + 20% and rounded value of rate + 20%
6. Display name of products with their rate, square of rate and square root of rate

EXERCISE on Group by and Order by clause:

1. Display the information in ascending order, of those products, whose rates are more than Rs.15
2. Display the information in Descending order, of those products, whose rates are more than Rs.15
3. Display all transactions in order of order id and quantity ordered. Display the result only for those orders for which quantity ordered is more than 20 units
4. Display name of products in order of product rate
5. Display all transactions in order of quantity
6. Display name of products in order of product rate. Also display product rate as RATE
7. Display that how many number of products available
8. Display the sum of rate of products, and average rate of product
9. Display the minimum rate of product and maximum rate of product
10. Display id of those employees who attended the orders, along with number of orders they attended 
11. Display all order id which having id less than 106, along with total quantity ordered by those ids
12. Display id of all order along with total quantity ordered by those ids. The restriction is that display only those orders, whose total quantity ordered is more than 50
13. Display id of all order along with total quantity ordered by those ids. The restriction is that display only those orders, whose total quantity ordered is more than 50. And result should be displayed in descending order of sum of quantity ordered

EXERCISE on SQL sub-queries:

1. Display the name of those products, whose rates are more than rates of Strawberry Shake
2. Display name of product, whose rate is maximum
3. Display the name of those products, whose rates are less than average rate of products
4. Display name of customers, who are attended by Bob
5. Display name of customers, who bought Pepsi or Lime Water
6. Display id of those orders whose order quantity is more than the maximum quantity ordered by id ‘101’. Also display the ids of product and quantity ordered
7. Display id of those orders whose order quantity is more than the minimum quantity ordered by id ‘101’. Also display the ids of product and quantity ordered

EXERCISE on JOINS (Table Alias, Equi-joins/Inner-joins, Outer-joins):

1. Table Alias

Display the name of those products, whose rates are in range 13 and 15 inclusive

Select p.product_name, p.product_rate

From product p

Where p.product_rate

Between 13 and 15;

2. Equi-joins or Inner-joins

Display the name of customers along with the name of those employees who attended their orders

Select c.customer_name, e.employee_name

From customer c, employee e, order_master o

Where c.customer_id=o.customer_id

and e.employee_id=o.employee_id;

or

select customer_name, employee_name

from order_master o INNER JOIN

customer c on

o.customer_id=c.customer_id

INNER JOIN on

o.employee_id=e.employee_id;

Display ids of those orders whose demand for quantity is available along with ids of corresponding products, quantity ordered, rate of item ordered and bill payable (rate * quantity) of those order

Select order_id, p.product_id, quantity, product_rate, (quantity * product_rate) as total

From product p INNER JOIN order_deatail o

On p.product_id=o.product_id

And quantity is not null order by order_id;

3. Outer-joins

Display the name and id of all employees along with the id of orders. Name and id must be displayed weather the employee has attended order or not

Select e.employee_id, e.employee_name, order_id

from employee e LEFT OUTER JOIN

order_master o on

o.emplyee_id=e.employee_id;

EXERCISE on SETS (union, union all, exists):

1. UNION

Display the unique product_id available in product & order_detail

Select product_id from product

UNION

Select product_id from order_detail;

2. UNION ALL

Display the product_id available in both tables product & order_detail

Select product_id from product

UNION ALL

Select product_id from order_detail;

3. EXISTS

Display the id of those employees, who have attended at least one order

Select distinct e.employee_id from employee e

Where exists

( select * from order_master o where o.employee_id=e.employee_id);

or

select distinct employee_id from order_master;