WRITING BASIC SQL SELECT STATEMENTS CREATE THE CLIENT_MASTER TABLE DESCRIBED BELOW:

Practical 1: Writing Basic SQL SELECT Statements 

CREATE THE CLIENT_MASTER TABLE DESCRIBED BELOW:

Column Name	Datatype	Size	Constraints
Client_No	Char	6	Primary Key
Name	Varchar2	20	Not Null
Address	Varchar2	100
City	Varchar2	15
Pincode	Number	6
State	Varchar2	20
Bal_Due	Number	8,2

Solution:

CREATE TABLE CLIENT_MASTER(

CLIENT_NO CHAR(6) PRIMARY KEY,

NAME VARCHAR2(20) NOT NULL,

ADDRESS VARCHAR2(100),

CITY VARCHAR2(15),

PINCODE NUMBER(6),

STATE VARCHAR2(20),

BAL_DUE NUMBER(8,2));

PERFORM THE FOLLOWING QUERIES ON THE TABLE:

Q1: Find out the names of all clients.

Solution:

SELECT NAME FROM CLIENT_MASTER;

Output:

NAME

Ivan Bayross

Vandana

Praveen

Basu

Ravi

Rukhmani

Q2: Retrieve the entire contents of Client_Master table.

Solution:

SELECT * FROM CLIENT_MASTER;

Output:

CLIENT_NO	NAME	ADDRESS	CITY	PINCODE	STATE	BAL_DUE
C00001	Ivan Bayross	F-111	Bombay	400054	Maharashtra	15000
C00002	Vandana	F-112	Madras	780001	Tamil Nadu	0
C00003	Praveen	F-113	Bombay	400057	Maharashtra	5000
C00004	Basu	F-114	Bangalore	560001	Karnataka
C00005	Ravi	F-115	Delhi	110005	Delhi	2000
C00006	Rukhmani	F-116	Bombay	400060	Maharashtra

Q3: Retrieve the names, city and state of all the clients.

Solution:

SELECT NAME, CITY, STATE FROM CLIENT_MASTER;

Output:

NAME	CITY	STATE
Ivan Bayross	Bombay	Maharashtra
Vandana	Madras	Tamil Nadu
Praveen	Bombay	Maharashtra
Basu	Bangalore	Karnataka
Ravi	Delhi	Delhi
Rukhmani	Bombay	Maharashtra

Q4: Print the structure of Client_Master table.

Solution:

DESC CLIENT_MASTER;

Output:

Name Null Type

------------------------------ -------- ----------------------------------

CLIENT_NO NOT NULL CHAR(6)

NAME NOT NULL VARCHAR2(20)

ADDRESS VARCHAR2(100)

CITY VARCHAR2(15)

PINCODE NUMBER(6)

STATE VARCHAR2(20)

BAL_DUE NUMBER(8,2)

Q5: List the names of all clients having ‘a’ as the second letter in their names.

Solution:

SELECT NAME FROM CLIENT_MASTER WHERE NAME LIKE ‘_a%’;

Output:

NAME

Vandana

Basu

Ravi

Q6: List the names of all clients having ‘a’ as the second letter in their city name.

Solution:

SELECT NAME FROM CLIENT_MASTER WHERE CITY LIKE ‘_a%’;

Output:

NAME

Vandana

Basu

Q7: List all the clients who stay in ‘Bombay’ or ‘Delhi’.

Solution:

SELECT NAME FROM CLIENT_MASTER WHERE CITY = ‘Bombay’ OR ‘Delhi’;

Output:

NAME

Ivan Bayross

Praveen

Ravi

Rukhmani

Q8: List all the clients who are located in Madras.

Solution:

SELECT NAME FROM CLIENT_MASTER WHERE CITY = ‘Madras’;

Output:

NAME

Vandana

Q9: List all clients whose Bal_Due is greater than the value 1000.

Solution:

SELECT NAME FROM CLIENT_MASTER WHERE BAL_DUE > 1000;

Output:

NAME

Ivan Bayross

Praveen

Ravi

Q10: List the names, city and state of clients who are not in the state of ‘Maharashtra’.

Solution:

SELECT NAME, CITY, STATE FROM CLIENT_MASTER WHERE NOT STATE = ‘Maharashtra’;

Output:

NAME	CITY	STATE
Vandana	Madras	Tamil Nadu
Basu	Bangalore	Karnataka
Ravi	Delhi	Delhi