SQL SESSION 2

Session 2 : Client Master

Client_no	Name	City	Pincode	State	Bal_Due
C00001	Ivan Bayross	Bombay	400054	Maharashtra	15000
C00002	Vandana saitwal	Madras	600001	Tamil Nadu	0
C00003	Pramada Jaguse	Bombay	400057	Maharashtra	5000
C00004	Basu Navindgi	Bombay	400056	Maharashtra	0
C00005	Avi Sreedharan	Delhi	100001	New elhi	2000

Problems:

 1. Clients.

 2. Print the entire client_master table.

 3. Retrieve the list of names and the cities of all the clients.

 4. Find the names of all the clients having ‘a’ as the second letter in their name.

 5. Find out the clients who stay in city ‘Bombay’ or city ‘Delhi’ or city ‘Madras’.

 6. List all the clients who are located in Bombay.

 7. Print the list of clients whose balance due are greater than 10000.

 8. List the names, city and state of clients not in the state of Maharashtra.

Solution:

1. Clients

SELECT Client_No, Names

FROM Client_Master;

2. Print the entire Client_Master table.

SELECT *

FROM Client_Master;

3. Retrieve the list of names and the cities of all the clients.

                SELECT Names, City

                FROM Client_Master;

4. Find the names of all the clients having ‘a’ as the second letter in their name.

SELECT Names

FROM Client_Master

WHERE ((Names) like '[a-z]a*');

5. Find out the clients who stay in city ‘Bombay’ or city ‘Delhi’ or city ‘Madras’.

                SELECT Names

FROM Client_Master

WHERE City IN ('Mumbai', 'Chennai', 'Delhi');

6. List all the clients who are located in Bombay.

                SELECT Names

                FROM Client_Master

                WHERE City IN (‘Mumbai’);

7. Print the list of clients whose balance due are greater than 10000.

SELECT Names

FROM Client_Master

WHERE Bal_Due>10000;

8. List the names, city and state of clients not in the state of Maharashtra.

SELECT Names, City, State

From Client_Master

WHERE State NOT IN (‘Maharashtra’);