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

Post Top Ad

Responsive Ads Here

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

Share This


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

No comments:

Post a Comment

Post Bottom Ad

Responsive Ads Here

Pages