1. Create the table clients with given columns:
CREATE TABLE CLIENTS(
Client_no VARCHAR2(5) PRIMARY KEY,
Name VARCHAR2(10) NOT NULL,
City VARCHAR2(10),
State VARCHAR2(10),
Bal_due Number(8,2)
);
2. Alter table clients add new column Mobile-no
ALTER TABLE CLIENTS ADD Mobile_No Number(10);
3. Insert the given details in the table
INSERT INTO CLIENTS(Client_no, Name, City, Pincode, State, Bal_due, Mobile_No)
VALUES('01', 'Neha Sharma', 'Pune', '100001', 'Maharashtra', 20000, '7755221144');
INSERT INTO CLIENTS(Client_no, Name, City, Pincode, State, Bal_due, Mobile_No)
VALUES('02', 'Raju Verma', 'Mumbai', '100002', 'Maharashtra', 12000, '9988556633');
INSERT INTO CLIENTS(Client_no, Name, City, Pincode, State, Bal_due, Mobile_No)
VALUES('03', 'Ajay Sehani', 'Delhi', '100003', 'Delhi', 30000, '7896541234');
INSERT INTO CLIENTS(Client_no, Name, City, Pincode, State, Bal_due, Mobile_No)
VALUES('04', 'Prince Mehta', 'Pune', '100004', 'Maharashtra', 45000, '9598715879');
INSERT INTO CLIENTS(Client_no, Name, City, Pincode, State, Bal_due, Mobile_No)
VALUES('05', 'Kiran Mehra', 'Kota', '100005', 'Rajasthan', 40000, '9589647168');
INSERT INTO CLIENTS(Client_no, Name, City, Pincode, State, Bal_due, Mobile_No)
VALUES('06', 'Sunil Kumar', 'Indore', '100006', 'MP', 15000, '9876543215');
SELECT* FROM CLIENTS
4. Describe table structure
DESC CLIENTS;
5. Display records of clients table
SELECT* FROM CLIENTS;
6. Display names of all clients
SELECT Name from CLIENTS;
7. List all clients from Mumbai
SELECT * FROM CLIENTS WHERE City = 'Mumbai';
8. List all different states
SELECT State FROM CLIENTS;
9. list clients in ascending order of balance due
SELECT* FROM CLIENTS ORDER BY Bal_due;
10. print clients whose balance is Bal-due is greater than 10000
SELECT *
FROM clients
WHERE `Bal-due` > 10000;
11. List names, city and states of clients not from Maharashtra
SELECT name, city, state
FROM clients
WHERE state NOT = 'Maharashtra'
12. List clients in descending order in their names
SELECT* FROM CLIENTS ORDER BY Name DESC;
13. Rename the table to new clients
ALTER TABLE CLIENTS RENAME TO NewClients;14. create table client-details with c-no, name and Bal-due using create table from table
CREATE TABLE Client_details AS
SELECT Client_no, Name, Bal_due FROM NewClients;;15. change city of client 3 to Pune
UPDATE CLIENTS SET CITY = 'Pune' WHERE Client_no = 03;
16. drop column pin code
ALTER TABLE CLIENTS DROP COLUMN 'Pincode'