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 the basic SQL queries.
Create the tables described below:
Table Name: Client_Master
Description: used to store information about clients.
+-------------+-----------+------+ | Column_Name | Data_Type | Size | +-------------+-----------+------+ | ClientNo | varchar2 | 6 | | Name | varchar2 | 20 | | Address1 | varchar2 | 30 | | Address2 | varchar2 | 30 | | City | varchar2 | 15 | | PinCode | Number | 8 | | State | varchar2 | 15 | | BalDue | Number | 10 | +-------------+-----------+------+
Command:
create table client_master (ClientNo varchar(6), Name varchar(20), Address1 varchar(30), Address2 varchar(30), City varchar(20), PinCode int, State varchar(20), BailDue int);
Table Name: Product_Master
Description: Used to store information about products.
+---------------+-----------+------+ | Column_Name | Data_Type | Size | +---------------+-----------+------+ | ProductNo | varchar2 | 6 | | Description | varchar2 | 15 | | ProfitPercent | varchar2 | 4 | | UnitMeasure | varchar2 | 10 | | QtyOnHand | Number | 8 | | RecorderLvi | Number | 8 | | SellPrice | Number | 8 | | CostPrice | Number | 8 | +---------------+-----------+------+
Command:
create table product_master (ProductNo varchar(6), ProductName varchar(15), Quantity float(4,2), Piece varchar(10), QtyOnHand int, Tax int, CostPrice int, SalePrice int);
Table Name: Salesman_Master
Description: Used to store information about salesman working in the company.
+--------------+-----------+------+ | Column_Name | Data_Type | Size | +--------------+-----------+------+ | SalesmanNo | varchar2 | 6 | | SalesmanName | varchar2 | 20 | | Address1 | varchar2 | 30 | | Address2 | varchar2 | 30 | | City | varchar2 | 20 | | PinCode | Number | 8 | | State | varchar2 | 20 | | SalAmt | Number | 8 | | TgttoGet | Number | 6 | | YtdSales | Number | 6 | | Remarks | varchar2 | 60 | +--------------+-----------+------+
Command:
create table salesman_master (SalesmanNo varchar(6), SalesmanName varchar(20), Address1 varchar(30), Address2 varchar( 30), City varchar(20), PinCode int, State varchar(20), SalAmt int, TgtToGet int, YtdSales int, Remarks varchar(60));
Insert the following data into the tables:
a) Data for Client_Master Table.
+----------+----------------+----------+----------+-----------+---------+-------------+---------+ | ClientNo | Name | Address1 | Address2 | City | PinCode | State | BailDue | +----------+----------------+----------+----------+-----------+---------+-------------+---------+ | C00001 | Ivan Bayross | Address1 | Address2 | Mumbai | 400001 | Maharashtra | 15000 | | C00002 | Mamta Muzumdar | Address1 | Address2 | Madras | 780001 | Tamil Nadu | 0 | | C00003 | Chhaya Bankar | Address1 | Address2 | Mumbai | 400057 | Maharashtra | 5000 | | C00004 | Ashwini Joshi | Address1 | Address2 | Bangalore | 560001 | Karnataka | 0 | | C00005 | Hansei Colaco | Address1 | Address2 | Mumbai | 400060 | Maharashtra | 2000 | | C00006 | Deepak Sharma | Address1 | Address2 | Mangalore | 560050 | Karnataka | 0 | +----------+----------------+----------+----------+-----------+---------+-------------+---------+
Command:
insert into client_master values('C00001','Ivan Bayross','Address1','Address2','Mumbai',400001,'Maharashtra',15000);
insert into client_master values('C00002','Mamta Muzumdar','Address1','Address2','Madras',780001,'Tamil Nadu',0);
insert into client_master values('C00003','Chhaya Bankar','Address1','Address2','Mumbai',400057,'Maharashtra',5000);
insert into client_master values('C00004','Ashwini Joshi','Address1','Address2','Bangalore',560001,'Karnataka',0);
insert into client_master values('C00005','Hansei Colaco','Address1','Address2','Mumbai',400060,'Maharashtra',2000);
insert into client_master values('C00006','Deepak Sharma','Address1','Address2','Mangalore',560050,'Karnataka',0);b) Data for Product_Master Table
+-----------+--------------+----------+-------+-----------+------+-----------+-----------+ | ProductNo | ProductName | Quantity | Piece | QtyOnHand | Tax | CostPrice | SalePrice | +-----------+--------------+----------+-------+-----------+------+-----------+-----------+ | P00001 | T-Shirts | 5.00 | Piece | 200 | 50 | 350 | 250 | | P0345 | Shirts | 6.00 | Piece | 150 | 50 | 500 | 350 | | P06734 | Cotton Jeans | 5.00 | Piece | 100 | 20 | 600 | 450 | | P07865 | Jeans | 5.00 | Piece | 100 | 20 | 750 | 500 | | P07868 | Trousers | 2.00 | Piece | 150 | 50 | 850 | 550 | | P07885 | Pull Overs | 2.50 | Piece | 150 | 50 | 850 | 550 | | P07868 | Denim Shirts | 4.00 | Piece | 100 | 40 | 350 | 250 | | P07868 | Lycra Tops | 5.00 | Piece | 70 | 30 | 300 | 175 | | P07868 | Skirts | 5.00 | Piece | 75 | 30 | 450 | 300 | +-----------+--------------+----------+-------+-----------+------+-----------+-----------+
Command:
insert into product_master values('P00001','T-Shirts', 5, 'Piece', 200, 50, 350, 250);
insert into product_master values('P0345','Shirts', 6, 'Piece', 150, 50, 500, 350);
insert into product_master values('P06734','Cotton Jeans', 5, 'Piece', 100, 20, 600, 450);
insert into product_master values('P07865','Jeans', 5, 'Piece', 100, 20, 750, 500);
insert into product_master values('P07868','Trousers', 2, 'Piece', 150, 50, 850, 550);
insert into product_master values('P07885','Pull Overs', 2.5, 'Piece', 150, 50, 850, 550);
insert into product_master values('P07868','Denim Shirts', 4, 'Piece', 100, 40, 350, 250);
insert into product_master values('P07868','Lycra Tops', 5, 'Piece', 70, 30, 300, 175);
insert into product_master values('P07868','Skirts', 5, 'Piece', 75, 30, 450, 300);c) Data for Salesman_Master Table
+------------+--------------+----------+----------+--------+---------+-------------+--------+----------+----------+---------+ | SalesmanNo | SalesmanName | Address1 | Address2 | City | PinCode | State | SalAmt | TgtToGet | YtdSales | Remarks | +------------+--------------+----------+----------+--------+---------+-------------+--------+----------+----------+---------+ | S00001 | Aman | A/14 | Worli | Mumbai | 400002 | Maharashtra | 3000 | 100 | 50 | Good | | S00002 | Omkar | 65 | Nariman | Mumbai | 400001 | Maharashtra | 3000 | 200 | 100 | Good | | S00003 | Raj | P-7 | Bandra | Mumbai | 400032 | Maharashtra | 3000 | 200 | 100 | Good | | S00004 | Ashish | A/5 | Juhu | Mumbai | 400044 | Maharashtra | 3500 | 200 | 150 | Good | +------------+--------------+----------+----------+--------+---------+-------------+--------+----------+----------+---------+
Command:
insert into salesman_master values('S00001','Aman','A/14','Worli','Mumbai',400002,'Maharashtra',3000,100,50,'Good');
insert into salesman_master values('S00002','Omkar','65','Nariman','Mumbai',400001,'Maharashtra',3000,200,100,'Good'
);
insert into salesman_master values('S00003','Raj','P-7','Bandra','Mumbai',400032,'Maharashtra',3000,200,100,'Good');
insert into salesman_master values('S00004','Ashish','A/5','Juhu','Mumbai',400044,'Maharashtra',3500,200,150,'Good');Exercise on retrieving the data.
a) Find out the name of all the clients
Command: select distinct Name from client_master; Output: +----------------+ | Name | +----------------+ | Ivan Bayross | | Mamta Muzumdar | | Chhaya Bankar | | Ashwini Joshi | | Hansei Colaco | | Deepak Sharma | +----------------+
b) Retrieve the entire contents of the Client_Master table.
Command: select * from client_master; Output: +----------+----------------+----------+----------+-----------+---------+-------------+---------+ | ClientNo | Name | Address1 | Address2 | City | PinCode | State | BailDue | +----------+----------------+----------+----------+-----------+---------+-------------+---------+ | C00001 | Ivan Bayross | Address1 | Address2 | Mumbai | 400001 | Maharashtra | 15000 | | C00002 | Mamta Muzumdar | Address1 | Address2 | Madras | 780001 | Tamil Nadu | 0 | | C00003 | Chhaya Bankar | Address1 | Address2 | Mumbai | 400057 | Maharashtra | 5000 | | C00004 | Ashwini Joshi | Address1 | Address2 | Bangalore | 560001 | Karnataka | 0 | | C00005 | Hansei Colaco | Address1 | Address2 | Mumbai | 400060 | Maharashtra | 2000 | | C00006 | Deepak Sharma | Address1 | Address2 | Mangalore | 560050 | Karnataka | 0 | +----------+----------------+----------+----------+-----------+---------+-------------+---------+
c) Retrieve the list of names, city and the state of al the clients
Command: select Name, City, State from client_master; Output: +----------------+-----------+-------------+ | Name | City | State | +----------------+-----------+-------------+ | Ivan Bayross | Mumbai | Maharashtra | | Mamta Muzumdar | Madras | Tamil Nadu | | Chhaya Bankar | Mumbai | Maharashtra | | Ashwini Joshi | Bangalore | Karnataka | | Hansei Colaco | Mumbai | Maharashtra | | Deepak Sharma | Mangalore | Karnataka | +----------------+-----------+-------------+
d) List the various products available from the Product_Master table
Command: select distinct ProductName from product_master; Output: +--------------+ | ProductName | +--------------+ | T-Shirts | | Shirts | | Cotton Jeans | | Jeans | | Trousers | | Pull Overs | | Denim Shirts | | Lycra Tops | | Skirts | +--------------+
e) List all the clients who live in Mumbai
Command: select * from client_master where City = 'Mumbai'; Output: +----------+---------------+----------+----------+--------+---------+-------------+---------+ | ClientNo | Name | Address1 | Address2 | City | PinCode | State | BailDue | +----------+---------------+----------+----------+--------+---------+-------------+---------+ | C00001 | Ivan Bayross | Address1 | Address2 | Mumbai | 400001 | Maharashtra | 15000 | | C00003 | Chhaya Bankar | Address1 | Address2 | Mumbai | 400057 | Maharashtra | 5000 | | C00005 | Hansei Colaco | Address1 | Address2 | Mumbai | 400060 | Maharashtra | 2000 | +----------+---------------+----------+----------+--------+---------+-------------+---------+
f) Find the names of the salesman who have a salary equal to Rs.3000
Command: select SalesmanName from salesman_master where SalAmt = 3000; Output: +--------------+ | SalesmanName | +--------------+ | Aman | | Omkar | | Raj | +--------------+
Exercise on updating the records in the table
a) Change the city of ClientNo ‘C00005’ to ‘Bangalore’
Command:
update client_master set City = 'Bangalore' where ClientNo = 'C00005';b) Change the BalDue of ClientNo ‘C00001’ to Rs.1000
Command:
update client_master set BailDue = 1000 where ClientNo = 'C00001';c) Change the cost price of ‘Trousers’ to Rs.950.00.
Command:
update product_master set CostPrice = 950 where ProductName = 'Trousers';d) Change the city of the salesman to Pune.
Command:
update salesman_master set City = 'Pune';Exercise on deleting the records in the table
a) Delete all the salesmen from the Salesman_Master whose salaries are equal to 3500.
Command:
delete from salesman_master where SalAmt = 3500;b) Delete all products from Product_Master where the QtyOnHand is equal to 100.
Command:
delete from product_master where QtyOnHand = 100;c) Delete from Client where the column state =’Maharashtra’.
Command:
delete from client_master where State = 'Tamil Nadu';Exercise on altering the table structure
d) Add a column called ‘Telephone’ of data type ‘number’ and size ‘10’ to the Client_Master table
Command:
alter table client_master add Telephone int;e) Change the size ODF SellPrice column Product_Master to 10,2
Command:
alter table product_master modify SellPrice float(10,2);Exercise on deleting the table structure
f) Destroy table Client_Master along with its data.
Command:
drop table client_master;Exercise on renaming the table
g) Change the name of the Salesman_Master to sman_mast
Command:
alter table salesman_master rename to sman_mast;