Q1. List all details from the client_master table for clients whose Bal_due = 0.
SQL> select * from client_master where bal_due=0;
CLIENT NAME CITY PINCODE BAL_DUE
------ ------------------------------ -------------------- ---------- ----------
C00002 VANDANA RAO Chennai 780001 0
C00004 BASU NATH Mumbai 400056 0
C00006 ROOPA Mumbai 400050 0
Q2. Update table client_master , Change city of Client_no C00004 to Jaipur.
SQL> update client_master set city='Jaipur' where client_no='C00004';
1 row updated.
SQL> select * from client_master;
CLIENT NAME CITY PINCODE BAL_DUE
------ ------------------------------ -------------------- ---------- ----------
C00001 SMITHA JAIN Mumbai 400054 15000
C00002 VANDANA RAO Chennai 780001 0
C00003 PREM PUJARI Mumbai 400057 5000
C00004 BASU NATH Jaipur 400056 0
C00005 RAVI SHANKAR Delhi 100001 2000
C00006 ROOPA Mumbai 400050 0
6 rows selected.
Q3. Retrieve records of clients residing in Mumbai.
SQL> select * from client_master where city='Mumbai';
CLIENT NAME CITY PINCODE BAL_DUE
------ ------------------------------ -------------------- ---------- ----------
C00001 SMITHA JAIN Mumbai 400054 15000
C00003 PREM PUJARI Mumbai 400057 5000
C00006 ROOPA Mumbai 400050 0
Q4. Find the name and address of customer who has placed Order_no ‘O19003’and O19002 respectively.
SQL> select client_master.client_no, name, city, pincode from client_master, sale_order
2 where client_master.client_no = sale_order.client_no and (order_no = 'O19003' or
3 order_no = 'O19002');
CLIENT NAME CITY PINCODE
------ ------------------------------ -------------------- ----------
C00002 VANDANA RAO Chennai 780001
C00001 SMITHA JAIN Mumbai 400054
Q5. Construct English like sentence from the table client_master ({Customer name} live in the city of {city}).
SQL> select trim(name) || ' live in the city of '|| city as address from client_master;
ADDRESS
-----------------------------------------------------------------------
SMITHA JAIN live in the city of Mumbai
VANDANA RAO live in the city of Chennai
PREM PUJARI live in the city of Mumbai
BASU NATH live in the city of Jaipur
RAVI SHANKAR live in the city of Delhi
ROOPA live in the city of Mumbai
6 rows selected.
Q6. List the client_no, name, city and pincode of clients whose Order_status is “In process”.
SQL> select client_master.client_no, name, city, pincode from client_master, sale_order
2 where client_master.client_no = sale_order.client_no and order_status = 'In process';
CLIENT NAME CITY PINCODE
------ ------------------------------ -------------------- ----------
C00001 SMITHA JAIN Mumbai 400054
C00005 RAVI SHANKAR Delhi 100001
|
