SQL Tutorial for Beginners in 3.5 hours
Follow the below 18 steps to learn SQL from scratch.
Download all the scripts used from here
1) Install the PostgreSQL database & PgAdmin IDE tool from here
2) Connect to the Postgres server and create a new database named “demo”.
select * from information_schema.tables; -- Understand what is a relational database? Create database demo;
3) Login to the demo database through the PgAdmin tool.
4) Create a simple table
create table products ( id int, name varchar(50), price float, release_date date ); -- What is a table, column and rows. -- What are data types?
5) Load data
insert into products (id,name,price,release_date)
values(1,'iPhone 15',900,'22-08-2023');
insert into products (id,name,price,release_date)
values(1,'Macbook Pro',2000,'10-02-2021');
insert into products (id,name,price,release_date)
values(1,'AirPods',400,'15-04-2022');
insert into products (id,name,price,release_date)
values(1,'iPhone 15',900,to_date('22-08-2023','dd-mm-yyyy'));
insert into products (id,name,price,release_date)
values(1,'Macbook Pro',2000,to_date('10-02-2021','dd-mm-yyyy'));
insert into products (id,name,price,release_date)
values(1,'AirPods',400,to_date('15-04-2022','dd-mm-yyyy'));6) Read data
-- fetch all columns and rows from table select * from products; -- fetch selected rows but all columns select * from products where price > 500; -- fetch only the product names select name from products; -- fetch name and display column name as Product_name select name as Product_name from products; -- give an alias to table select name as Product_name from products p; select p.name as Product_name from products p; -- fetch name of products which were released in 2023 select name from products where to_char(release_date, 'yyyy') = '2023' select name from products where extract(year from release_date) = '2023' -- get total no of products select count(*) from products -- get the average and total price of all products select avg(price) from products; select avg(price) as avg_cost, sum(price) as total_sum from products;
7) Modify data
-- change the price of iPhone to 1000 update products set price = 1000 where id = 1; -- this will update wrong records update products set price = 1000 where name = 'iPhone 15'; update products set price = 1000 where name like 'iPhone%'; -- this would update any name with "iPhone"
8) Remove data
-- remove products costing over 1000 delete from products where price > 1000; -- remove all products other than iPhone delete from products where name not like 'iPhone%'; -- remove all products delete from products; truncate table products; -- this is a DDL command
9) Create a backup table
-- create a backup table of products with same structure and data create table products_bkp as select * from products; -- create a empty backup table with only the structure from products table create table products_bkp as select * from products where 1=2;
10) Remove table
drop table products; drop table if exists products;
11) Modify table
alter table products_bkp rename to products; alter table products rename column name to product_name; -- modify the data type of price to integer alter table products alter column price type int;
12) Primary key constraint
create table products ( id int primary key, name varchar(50), price float, release_date date ); create table products ( id int, name varchar(50), price float, release_date date, constraint pk_prd primary key(id) ); create table products ( id int, name varchar(50), price float, release_date date, constraint pk_prd primary key(id,name) /* allows to create a primary key with multiple columns (also referred to as composite primary key) */ );
13) Load data with constraints & Identity column
insert into products (id,name,price,release_date)
values(1,'iPhone 15',900,to_date('22-08-2023','dd-mm-yyyy'));
insert into products (id,name,price,release_date)
values(1,'Macbook Pro',2000,to_date('10-02-2021','dd-mm-yyyy'));
insert into products (id,name,price,release_date)
values(1,'AirPods',400,to_date('15-04-2022','dd-mm-yyyy'));
insert into products (id,name,price,release_date)
values(1,'iPhone 15',900,to_date('22-08-2023','dd-mm-yyyy'));
insert into products (id,name,price,release_date)
values(2,'Macbook Pro',2000,to_date('10-02-2021','dd-mm-yyyy'));
insert into products (id,name,price,release_date)
values(3,'AirPods',400,to_date('15-04-2022','dd-mm-yyyy'));
drop table products;
create table products
(
id int generated always as identity primary key,
name varchar(50) ,
price float,
release_date date
);
insert into products (id,name,price,release_date)
values(default,'iPhone 15',900,to_date('22-08-2023','dd-mm-yyyy'));
insert into products (id,name,price,release_date)
values(default,'Macbook Pro',2000,to_date('10-02-2021','dd-mm-yyyy'));
insert into products (id,name,price,release_date)
values(default,'AirPods',400,to_date('15-04-2022','dd-mm-yyyy'));
14) Create a Sales Order dataset
drop table if exists products;
create table products
(
id int generated always as identity primary key,
name varchar(100),
price float,
release_date date
);
insert into products
values(default,'iPhone 15', 800, to_date('22-08-2023','dd-mm-yyyy'));
insert into products
values(default,'Macbook Pro', 2100, to_date('12-10-2022','dd-mm-yyyy'));
insert into products
values(default,'Apple Watch 9', 550, to_date('04-09-2022','dd-mm-yyyy'));
insert into products
values(default,'iPad', 400, to_date('25-08-2020','dd-mm-yyyy'));
insert into products
values(default,'AirPods', 420, to_date('30-03-2024','dd-mm-yyyy'));
drop table if exists customers;
create table customers
(
id int generated always as identity primary key,
name varchar(100),
email varchar(30)
);
insert into customers values(default,'Meghan Harley', 'mharley@demo.com');
insert into customers values(default,'Rosa Chan', 'rchan@demo.com');
insert into customers values(default,'Logan Short', 'lshort@demo.com');
insert into customers values(default,'Zaria Duke', 'zduke@demo.com');
drop table if exists employees;
create table employees
(
id int generated always as identity primary key,
name varchar(100)
);
insert into employees values(default,'Nina Kumari');
insert into employees values(default,'Abrar Khan');
insert into employees values(default,'Irene Costa');
drop table if exists sales_order;
create table sales_order
(
order_id int generated always as identity primary key,
order_date date,
quantity int,
prod_id int references products(id),
status varchar(20),
customer_id int references customers(id),
emp_id int,
constraint fk_so_emp foreign key (emp_id) references employees(id)
);
insert into sales_order
values(default,to_date('01-01-2024','dd-mm-yyyy'),2,1,'Completed',1,1);
insert into sales_order
values(default,to_date('01-01-2024','dd-mm-yyyy'),3,1,'Pending',2,2);
insert into sales_order
values(default,to_date('02-01-2024','dd-mm-yyyy'),3,2,'Completed',3,2);
insert into sales_order
values(default,to_date('03-01-2024','dd-mm-yyyy'),3,3,'Completed',3,2);
insert into sales_order
values(default,to_date('04-01-2024','dd-mm-yyyy'),1,1,'Completed',3,2);
insert into sales_order
values(default,to_date('04-01-2024','dd-mm-yyyy'),1,3,'completed',2,1);
insert into sales_order
values(default,to_date('04-01-2024','dd-mm-yyyy'),1,2,'On Hold',2,1);
insert into sales_order
values(default,to_date('05-01-2024','dd-mm-yyyy'),4,2,'Rejected',1,2);
insert into sales_order
values(default,to_date('06-01-2024','dd-mm-yyyy'),5,5,'Completed',1,2);
insert into sales_order
values(default,to_date('06-01-2024','dd-mm-yyyy'),1,1,'Cancelled',1,1);
SELECT * FROM products;
SELECT * FROM customers;
SELECT * FROM employees;
SELECT * FROM sales_order;15) Foreign key constraint
-- Before foreign key constraint
drop table if exists sales_order;
create table sales_order
(
order_id int generated always as identity primary key,
order_date date,
quantity int,
prod_id int ,
status varchar(20),
customer_id int ,
emp_id int
);
insert into sales_order
values(default,to_date('01-01-2024','dd-mm-yyyy'),2,1,'Completed',1,1);
insert into sales_order
values(default,to_date('01-01-2024','dd-mm-yyyy'),1,10,'Completed',11,100);
insert into sales_order
values(default,to_date('01-01-2024','dd-mm-yyyy'),1,15,'Completed',11,100);
-- After applying foreign key
drop table if exists sales_order;
create table sales_order
(
order_id int generated always as identity primary key,
order_date date,
quantity int,
prod_id int references products(id),
status varchar(20),
customer_id int ,
emp_id int,
constraint fk_so_cust foreign key(customer_id) references customers(id),
constraint fk_so_emp foreign key(emp_id) references employees(id)
);
insert into sales_order
values(default,to_date('01-01-2024','dd-mm-yyyy'),2,1,'Completed',1,1);
insert into sales_order
values(default,to_date('01-01-2024','dd-mm-yyyy'),1,5,'Completed',4,3);
insert into sales_order
values(default,to_date('01-01-2024','dd-mm-yyyy'),1,3,'Completed',2,1);
16) Dropping table with constraints
-- cannot drop a table which is being referred(used) by other table. drop table if exists products; drop table if exists customers; drop table if exists employees; drop table if exists sales_order; -- First drop the child table which refers other tables drop table if exists sales_order; drop table if exists products; drop table if exists customers; drop table if exists employees;
17) Case Study - 20 SQL Queries
-- 1. Identify the total no of products sold
select sum(quantity) as total_products
from sales_order;
-- 2. Other than Completed, display the available delivery status's
select distinct status from sales_order
where status <> 'Completed';
select distinct status from sales_order
where status not in ('Completed', 'completed');
select distinct status from sales_order
where lower(status) <> 'completed';
select distinct status from sales_order
where upper(status) <> 'COMPLETED';
-- 3. Display the order id, order_date and product_name for all the completed orders.
select so.order_id, so.order_date, p.name
from sales_order so
join products p on p.id=so.prod_id
where lower(so.status) = 'completed';
-- 4. Sort the above query to show the earliest orders at the top. Also, display the customer who purchased these orders.
select so.order_id, so.order_date, p.name as product, c.name as customer
from sales_order so
join products p on p.id=so.prod_id
join customers c on c.id = so.customer_id
where lower(so.status) = 'completed'
order by so.order_date;
-- 5. Display the total no of orders corresponding to each delivery status
select status, count(*) as tot_orders
from sales_order so
group by status;
-- 6. How many orders are still not completed for orders purchasing more than 1 item?
select count(status) as not_completed_orders
from sales_order so
where quantity > 1
and lower(status) <> 'completed';
-- 7. Find the total number of orders corresponding to each delivery status by ignoring the case in the delivery status. The status with highest no of orders should be at the top.
select status, count(*) as tot_orders
from (select case when lower(status) = 'completed'
then 'Completed' else status
end as status
from sales_order) sq
group by status
order by tot_orders desc;
select upper(status) as status, count(*) as tot_orders
from sales_order so
group by upper(status)
order by tot_orders desc;
-- 8. Write a query to identify the total products purchased by each customer
select c.name as customer, sum(quantity) as total_products
from sales_order so
join customers c on c.id = so.customer_id
group by c.name;
-- 9. Display the total sales and average sales done for each day.
select order_date, sum(quantity*price) as total_sales
, avg(quantity*p.price) as avg_sales
from sales_order so
join products p on p.id = so.prod_id
group by order_date
order by order_date;
-- 10. Display the customer name, employee name, and total sale amount of all orders which are either on hold or pending.
select c.name as customer, e.name as employee
, sum(quantity*p.price) as total_sales
from sales_order so
join employees e on e.id = so.emp_id
join customers c on c.id = so.customer_id
join products p on p.id = so.prod_id
where status in ('On Hold', 'Pending')
group by c.name, e.name;
-- 11. Fetch all the orders which were neither completed/pending or were handled by the employee Abrar. Display employee name and all details of order.
select e.name as employee, so.*
from sales_order so
join employees e on e.id = so.emp_id
where lower(e.name) like '%abrar%'
or lower(status) not in ('completed', 'pending');
-- 12. Fetch the orders which cost more than 2000 but did not include the MacBook Pro. Print the total sale amount as well.
select (so.quantity * p.price) as total_sale, so.*
from sales_order so
join products p on p.id = so.prod_id
where prod_id not in (select id from products
where name = 'Macbook Pro')
and (so.quantity * p.price) > 2000;
-- 13. Identify the customers who have not purchased any product yet.
select * from customers
where id not in (select distinct customer_id
from sales_order);
select c.*
from customers c
left join sales_order so on so.customer_id = c.id
where so.order_id is null;
select c.*
from sales_order so
right join customers c on so.customer_id = c.id
where so.order_id is null;
-- 14. Write a query to identify the total products purchased by each customer. Return all customers irrespective of whether they have made a purchase or not. Sort the result with the highest no of orders at the top.
select c.name , coalesce(sum(quantity), 0) as tot_prod_purchased
from sales_order so
right join customers c on c.id = so.customer_id
group by c.name
order by tot_prod_purchased desc;
-- 15. Corresponding to each employee, display the total sales they made of all the completed orders. Display total sales as 0 if an employee made no sales yet.
select e.name as employee, coalesce(sum(p.price * so.quantity),0) as total_sale
from sales_order so
join products p on p.id = so.prod_id
right join employees e on e.id = so.emp_id and lower(so.status) = 'completed'
group by e.name
order by total_sale desc;
-- 16. Re-write the above query to display the total sales made by each employee corresponding to each customer. If an employee has not served a customer yet then display "-" under the customer.
select e.name as employee, coalesce(c.name, '-') as customer
, coalesce(sum(p.price * so.quantity),0) as total_sale
from sales_order so
join products p on p.id = so.prod_id
join customers c on c.id = so.customer_id
right join employees e on e.id = so.emp_id
and lower(so.status) = 'completed'
group by e.name, c.name
order by total_sale desc;
-- 17. Re-write the above query to display only those records where the total sales are above 1000
select e.name as employee, coalesce(c.name, '-') as customer
, coalesce(sum(p.price * so.quantity),0) as total_sale
from sales_order so
join products p on p.id = so.prod_id
join customers c on c.id = so.customer_id
right join employees e on e.id = so.emp_id
and lower(so.status) = 'completed'
group by e.name, c.name
having sum(p.price * so.quantity) > 1000
order by total_sale desc;
-- 18. Identify employees who have served more than 2 customers.
select e.name, count(distinct c.name) as total_customers
from sales_order so
join employees e on e.id = so.emp_id
join customers c on c.id = so.customer_id
group by e.name
having count(distinct c.name) > 2;
-- 19. Identify the customers who have purchased more than 5 products
select c.name as customer, sum(quantity) as total_products_purchased
from sales_order so
join customers c on c.id = so.customer_id
group by c.name
having sum(quantity) > 5;
-- 20. Identify customers whose average purchase cost exceeds the average sale of all the orders.
select c.name as customer, avg(quantity * p.price)
from sales_order so
join customers c on c.id = so.customer_id
join products p on p.id = so.prod_id
group by c.name
having avg(quantity * p.price) > (select avg(quantity * p.price)
from sales_order so
join products p on p.id = so.prod_id);18) Next steps
a) Go to LeetCode (or any other SQL practice platforms) and practice solving all EASY SQL problems.
b) Learn the following concepts
i) Subqueries from here
ii) Window functions from here and here
iii) All types of JOINS from here and here
iv) CTE table (WITH clause) from here
c) After learning the above 4 concepts, you can start solving Medium level problems on LeetCode or other SQL practice platforms.
