Practical No. # 1
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(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 |
