[go: up one dir, main page]
Scribd
Upload
169 views18 pages

DML - 7

The document contains SQL commands that create tables, insert data, and perform queries on the tables. The tables created are EmployeeD to store employee name and salary, TBorrow to store customer name and loan number, and TLoan to store loan number, customer name, and amount. Data is inserted into the tables and various SELECT queries are run to retrieve, filter, and perform calculations on the data.

Uploaded by

Tharun Sankar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
169 views18 pages

DML - 7

The document contains SQL commands that create tables, insert data, and perform queries on the tables. The tables created are EmployeeD to store employee name and salary, TBorrow to store customer name and loan number, and TLoan to store loan number, customer name, and amount. Data is inserted into the tables and various SELECT queries are run to retrieve, filter, and perform calculations on the data.

Uploaded by

Tharun Sankar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 18

SQL> create table EmployeeD(E_Name varchar(10), E_Salary number(10));

Table created.

SQL> commit;

Commit complete.

SQL> insert into EmployeeD values('&n', '&s');


Enter value for n: Abdullah
Enter value for s: 35000
old 1: insert into EmployeeD values('&n', '&s')
new 1: insert into EmployeeD values('Abdullah', '35000')

1 row created.

SQL> /
Enter value for n: Anu
Enter value for s: 38000
old 1: insert into EmployeeD values('&n', '&s')
new 1: insert into EmployeeD values('Anu', '38000')

1 row created.

SQL> /
Enter value for n: Abdul
Enter value for s: 25000
old 1: insert into EmployeeD values('&n', '&s')
new 1: insert into EmployeeD values('Abdul', '25000')

1 row created.

SQL> /
Enter value for n: Balaji
Enter value for s: 42000
old 1: insert into EmployeeD values('&n', '&s')
new 1: insert into EmployeeD values('Balaji', '42000')

1 row created.

SQL> /
Enter value for n: Dinesh
Enter value for s: 45000
old 1: insert into EmployeeD values('&n', '&s')
new 1: insert into EmployeeD values('Dinesh', '45000')

1 row created.

SQL> /
Enter value for n: Faizal
Enter value for s: 33000
old 1: insert into EmployeeD values('&n', '&s')
new 1: insert into EmployeeD values('Faizal', '33000')

1 row created.

SQL> /
Enter value for n: Ifthikar
Enter value for s: 38000
old 1: insert into EmployeeD values('&n', '&s')
new 1: insert into EmployeeD values('Ifthikar', '38000')

1 row created.

SQL> /
Enter value for n: Ijas
Enter value for s: 36000
old 1: insert into EmployeeD values('&n', '&s')
new 1: insert into EmployeeD values('Ijas', '36000')

1 row created.

SQL> /
Enter value for n: Shameem
Enter value for s: 47000
old 1: insert into EmployeeD values('&n', '&s')
new 1: insert into EmployeeD values('Shameem', '47000')

1 row created.

SQL> /
Enter value for n: Raj
Enter value for s: 28000
old 1: insert into EmployeeD values('&n', '&s')
new 1: insert into EmployeeD values('Raj', '28000')

1 row created.

SQL> commit;

Commit complete.

SQL> desc EmployeeD;


Name Null? Type
------------------------------- -------- ----
E_NAME VARCHAR2(10)
E_SALARY NUMBER(10)

SQL> select * from EmployeeD;

E_NAME E_SALARY
---------- ----------
Abdullah 35000
Anu 38000
Abdul 25000
Balaji 42000
Dinesh 45000
Faizal 33000
Ifthikar 38000
Ijas 36000
Shameem 47000
Raj 28000

10 rows selected.

SQL> select E_Salary + 500 from EmployeeD;


E_SALARY+500
------------
35500
38500
25500
42500
45500
33500
38500
36500
47500
28500

10 rows selected.

SQL> select E_Salary - 500 from EmployeeD;

E_SALARY-500
------------
34500
37500
24500
41500
44500
32500
37500
35500
46500
27500

10 rows selected.

SQL> select E_Salary * 2 from EmployeeD;

E_SALARY*2
----------
70000
76000
50000
84000
90000
66000
76000
72000
94000
56000

10 rows selected.

SQL> select E_Salary / 2 from EmployeeD;

E_SALARY/2
----------
17500
19000
12500
21000
22500
16500
19000
18000
23500
14000

10 rows selected.

SQL> select * from EmployeeD where E_Salary between 33000 and 45000;

E_NAME E_SALARY
---------- ----------
Abdullah 35000
Anu 38000
Balaji 42000
Dinesh 45000
Faizal 33000
Ifthikar 38000
Ijas 36000

7 rows selected.

SQL> select * from EmployeeD where E_Salary = 38000 or E_Salary = 47000;

E_NAME E_SALARY
---------- ----------
Anu 38000
Ifthikar 38000
Shameem 47000

SQL> select * from EmployeeD where E_Salary not like 38000;

E_NAME E_SALARY
---------- ----------
Abdullah 35000
Abdul 25000
Balaji 42000
Dinesh 45000
Faizal 33000
Ijas 36000
Shameem 47000
Raj 28000

8 rows selected.

SQL> select * from EmployeeD where E_Salary = 45000;

E_NAME E_SALARY
---------- ----------
Dinesh 45000

SQL> select * from EmployeeD where E_Salary != 36000;

E_NAME E_SALARY
---------- ----------
Abdullah 35000
Anu 38000
Abdul 25000
Balaji 42000
Dinesh 45000
Faizal 33000
Ifthikar 38000
Shameem 47000
Raj 28000

9 rows selected.

SQL> select * from EmployeeD where E_Salary < 45000;

E_NAME E_SALARY
---------- ----------
Abdullah 35000
Anu 38000
Abdul 25000
Balaji 42000
Faizal 33000
Ifthikar 38000
Ijas 36000
Raj 28000

8 rows selected.

SQL> select * from EmployeeD where E_Salary > 36000;

E_NAME E_SALARY
---------- ----------
Anu 38000
Balaji 42000
Dinesh 45000
Ifthikar 38000
Shameem 47000

SQL> select * from EmployeeD where E_Salary <= 33000;

E_NAME E_SALARY
---------- ----------
Abdul 25000
Faizal 33000
Raj 28000

SQL> select * from EmployeeD where E_Salary >= 42000;

E_NAME E_SALARY
---------- ----------
Balaji 42000
Dinesh 45000
Shameem 47000

SQL> select E_Name from EmployeeD where E_Name like 'I%';

E_NAME
----------
Ifthikar
Ijas

SQL> select E_Name from EmployeeD where E_Name like '%l';

E_NAME
----------
Abdul
Faizal

SQL> select E_Name from EmployeeD where E_Name like '%aj%';

E_NAME
----------
Balaji
Raj

SQL> select E_Name from EmployeeD where E_Name like '_b%';

E_NAME
----------
Abdullah
Abdul

SQL> select E_Name from EmployeeD where E_Name like 'B_____%';

E_NAME
----------
Balaji

SQL> select E_Name from EmployeeD where E_Name like 'A__%';

E_NAME
----------
Abdullah
Anu
Abdul

SQL> select E_Name from EmployeeD where E_Name like 'R__%'

E_NAME
----------
Raj

SQL> select E_Name from EmployeeD where E_Name like 'I___%'

E_NAME
----------
Ifthikar
Ijas

SQL> select E_Name from EmployeeD where E_Name like 'A%h';

E_NAME
----------
Abdullah
SQL> select E_Name from EmployeeD where E_Name not like 'A%';

E_NAME
----------
Balaji
Dinesh
Faizal
Ifthikar
Ijas
Shameem
Raj

7 rows selected.

SQL> create table TBorrow(C_Name varchar(10), L_No varchar(6));

Table created.

SQL> insert into TBorrow values('&n', '&l');


Enter value for n: Ajay
Enter value for l: LN01
old 1: insert into TBorrow values('&n', '&l')
new 1: insert into TBorrow values('Ajay', 'LN01')

1 row created.

SQL> /
Enter value for n: Bharath
Enter value for l: LN02
old 1: insert into TBorrow values('&n', '&l')
new 1: insert into TBorrow values('Bharath', 'LN02')

1 row created.

SQL> /
Enter value for n: Chandru
Enter value for l: LN01
old 1: insert into TBorrow values('&n', '&l')
new 1: insert into TBorrow values('Chandru', 'LN01')

1 row created.

SQL> /
Enter value for n: Dharsan
Enter value for l: LN03
old 1: insert into TBorrow values('&n', '&l')
new 1: insert into TBorrow values('Dharsan', 'LN03')

1 row created.

SQL> /
Enter value for n: Elwin
Enter value for l: LN04
old 1: insert into TBorrow values('&n', '&l')
new 1: insert into TBorrow values('Elwin', 'LN04')
1 row created.

SQL> /
Enter value for n: Faizal
Enter value for l: LN03
old 1: insert into TBorrow values('&n', '&l')
new 1: insert into TBorrow values('Faizal', 'LN03')

1 row created.

SQL> /
Enter value for n: Giri
Enter value for l: LN05
old 1: insert into TBorrow values('&n', '&l')
new 1: insert into TBorrow values('Giri', 'LN05')

1 row created.

SQL> /
Enter value for n: Harish
Enter value for l: LN06
old 1: insert into TBorrow values('&n', '&l')
new 1: insert into TBorrow values('Harish', 'LN06')

1 row created.

SQL> /
Enter value for n: Idris
Enter value for l: LN04
old 1: insert into TBorrow values('&n', '&l')
new 1: insert into TBorrow values('Idris ', 'LN04')

1 row created.

SQL> /
Enter value for n: Jack
Enter value for l: LN07
old 1: insert into TBorrow values('&n', '&l')
new 1: insert into TBorrow values('Jack', 'LN07')

1 row created.

SQL> select * from TBorrow;

C_NAME L_NO
---------- ------
Ajay LN01
Bharath LN02
Chandru LN01
Dharsan LN03
Elwin LN04
Faizal LN03
Giri LN05
Harish LN06
Idris LN04
Jack LN07

10 rows selected.
SQL> create table TLoan(L_No varchar(6), B_Name varchar(15), Amount number(10));

Table created.

SQL> insert into TLoan values('&l', '&b', '&a');


Enter value for l: LN01
Enter value for b: Jamal
Enter value for a: 50000
old 1: insert into TLoan values('&l', '&b', '&a')
new 1: insert into TLoan values('LN01', 'Jamal', '50000')

1 row created.

SQL> /
Enter value for l: LN04
Enter value for b: Mathur
Enter value for a: 30000
old 1: insert into TLoan values('&l', '&b', '&a')
new 1: insert into TLoan values('LN04', 'Mathur', '30000')

1 row created.

SQL> /
Enter value for l: LN03
Enter value for b: Jamal
Enter value for a: 45000
old 1: insert into TLoan values('&l', '&b', '&a')
new 1: insert into TLoan values('LN03', 'Jamal', '45000')

1 row created.

SQL> /
Enter value for l: LN07
Enter value for b: Airport
Enter value for a: 50000
old 1: insert into TLoan values('&l', '&b', '&a')
new 1: insert into TLoan values('LN07', 'Airport', '50000')

1 row created.

SQL> /
Enter value for l: LN05
Enter value for b: Thennur
Enter value for a: 35000
old 1: insert into TLoan values('&l', '&b', '&a')
new 1: insert into TLoan values('LN05', 'Thennur', '35000')

1 row created.

SQL> /
Enter value for l: LN06
Enter value for b: KK Nagar
Enter value for a: 60000
old 1: insert into TLoan values('&l', '&b', '&a')
new 1: insert into TLoan values('LN06', 'KK Nagar', '60000')

1 row created.
SQL> /
Enter value for l: LN07
Enter value for b: Jamal
Enter value for a: 26000
old 1: insert into TLoan values('&l', '&b', '&a')
new 1: insert into TLoan values('LN07', 'Jamal', '26000')

1 row created.

SQL> /
Enter value for l: LN03
Enter value for b: Mathur
Enter value for a: 39000
old 1: insert into TLoan values('&l', '&b', '&a')
new 1: insert into TLoan values('LN03', 'Mathur', '39000')

1 row created.

SQL> /
Enter value for l: LN07
Enter value for b: KK Nagar
Enter value for a: 70000
old 1: insert into TLoan values('&l', '&b', '&a')
new 1: insert into TLoan values('LN07', 'KK Nagar', '70000')

1 row created.

SQL> /
Enter value for l: LN01
Enter value for b: Thennur
Enter value for a: 52000
old 1: insert into TLoan values('&l', '&b', '&a')
new 1: insert into TLoan values('LN01', 'Thennur', '52000')

1 row created.

SQL> select * from TLoan;

L_NO B_NAME AMOUNT


------ --------------- ----------
LN01 Jamal 50000
LN04 Mathur 30000
LN03 Jamal 45000
LN07 Airport 50000
LN05 Thennur 35000
LN06 KK Nagar 60000
LN07 Jamal 26000
LN03 Mathur 39000
LN07 KK Nagar 70000
LN01 Thennur 52000

10 rows selected.

Without Tuple:

SQL> select C_Name from TBorrow, TLoan where TBorrow.L_No = TLoan.L_No and B_Name =
'Jamal';
C_NAME
----------
Ajay
Chandru
Dharsan
Faizal
Jack

SQL> select C_Name from TBorrow, TLoan where TBorrow.L_No = TLoan.L_No and B_Name =
'KK Nagar';

C_NAME
----------
Harish
Jack

With Tuple:

SQL> select A.C_Name from TBorrow A, TLoan D where A.L_No = D.L_No and D.B_Name =
'Jamal';

C_NAME
----------
Ajay
Chandru
Dharsan
Faizal
Jack

SQL> select A.C_Name from TBorrow A, TLoan D where A.L_No = D.L_No and D.B_Name =
'Mathur';

C_NAME
----------
Dharsan
Faizal
Elwin
Idris

SQL> commit;

SQL> create table TBranch(B_Name varchar(15), City varchar(10), Assets number(10));

Table created.

SQL> insert into TBranch values('&b', '&c', '&a');


Enter value for b: Jamal
Enter value for c: Trichy
Enter value for a: 1200000
old 1: insert into TBranch values('&b', '&c', '&a')
new 1: insert into TBranch values('Jamal', 'Trichy', '1200000')

1 row created.
SQL> /
Enter value for b: Subramaiyapuram
Enter value for c: Trichy
Enter value for a: 500000
old 1: insert into TBranch values('&b', '&c', '&a')
new 1: insert into TBranch values('Subramaiyapuram', 'Trichy', '500000')

1 row created.

SQL> /
Enter value for b: KK Nagar
Enter value for c: Thanjavur
Enter value for a: 4500000
old 1: insert into TBranch values('&b', '&c', '&a')
new 1: insert into TBranch values('KK Nagar', 'Thanjavur', '4500000')

1 row created.

SQL> /
Enter value for b: Thennur
Enter value for c: Coimbatore
Enter value for a: 700000
old 1: insert into TBranch values('&b', '&c', '&a')
new 1: insert into TBranch values('Thennur', 'Coimbatore', '700000')

1 row created.

SQL> /
Enter value for b: Mathur
Enter value for c: Kovai
Enter value for a: 300000
old 1: insert into TBranch values('&b', '&c', '&a')
new 1: insert into TBranch values('Mathur', 'Kovai', '300000')

1 row created.

SQL> /
Enter value for b: Thillai Nagar
Enter value for c: Chennai
Enter value for a: 600000
old 1: insert into TBranch values('&b', '&c', '&a')
new 1: insert into TBranch values('Thillai Nagar', 'Chennai', '600000')

1 row created.

SQL> /
Enter value for b: Menambakam
Enter value for c: Chennai
Enter value for a: 900000
old 1: insert into TBranch values('&b', '&c', '&a')
new 1: insert into TBranch values('Menambakam', 'Chennai', '900000')

1 row created.

SQL> /
Enter value for b: Court Road
Enter value for c: Salem
Enter value for a: 5000000
old 1: insert into TBranch values('&b', '&c', '&a')
new 1: insert into TBranch values('Court Road', 'Salem', '5000000')

1 row created.

SQL> /
Enter value for b: Chathram
Enter value for c: Tirupur
Enter value for a: 450000
old 1: insert into TBranch values('&b', '&c', '&a')
new 1: insert into TBranch values('Chathram', 'Tirupur', '450000')

1 row created.

SQL> /
Enter value for b: Abishek Puram
Enter value for c: Madurai
Enter value for a: 650000
old 1: insert into TBranch values('&b', '&c', '&a')
new 1: insert into TBranch values('Abishek Puram', 'Madurai', '650000')

1 row created.

SQL> select * from TBranch;

B_NAME CITY ASSETS


--------------- ---------- ----------
Jamal Trichy 1200000
Subramaiyapuram Trichy 500000
KK Nagar Thanjavur 4500000
Thennur Coimbatore 700000
Mathur Kovai 300000
Thillai Nagar Chennai 600000
Menambakam Chennai 900000
Court Road Salem 5000000
Chathram Tirupur 450000
Abishek Puram Madurai 650000

10 rows selected.

SQL> select distinct A.B_Name from TBranch A, TBranch U where A.Assets > U.Assets
and U.City = 'Trichy';

B_NAME
---------------
Abishek Puram
Court Road
Jamal
KK Nagar
Menambakam
Thennur
Thillai Nagar

7 rows selected.

SQL> select * from TBorrow order by C_Name asc;


C_NAME L_NO
---------- ------
Ajay LN01
Bharath LN02
Chandru LN01
Dharsan LN03
Elwin LN04
Faizal LN03
Giri LN05
Harish LN06
Idris LN04
Jack LN07

10 rows selected.

SQL> select * from TBorrow order by C_Name desc;

C_NAME L_NO
---------- ------
Jack LN07
Idris LN04
Harish LN06
Giri LN05
Faizal LN03
Elwin LN04
Dharsan LN03
Chandru LN01
Bharath LN02
Ajay LN01

10 rows selected.

SQL> create table TDeposit(C_Name varchar(10), A_No varchar(6));

Table created.

SQL> insert into TDeposit values('&n', '&a');


Enter value for n: Arun
Enter value for a: AN01
old 1: insert into TDeposit values('&n', '&a')
new 1: insert into TDeposit values('Arun', 'AN01')

1 row created.

SQL> /
Enter value for n: Siva
Enter value for a: AN02
old 1: insert into TDeposit values('&n', '&a')
new 1: insert into TDeposit values('Siva', 'AN02')

1 row created.

SQL> /
Enter value for n: Praveen
Enter value for a: AN03
old 1: insert into TDeposit values('&n', '&a')
new 1: insert into TDeposit values('Praveen', 'AN03')

1 row created.

SQL> /
Enter value for n: Dinesh
Enter value for a: AN04
old 1: insert into TDeposit values('&n', '&a')
new 1: insert into TDeposit values('Dinesh', 'AN04')

1 row created.

SQL> /
Enter value for n: Bharath
Enter value for a: AN05
old 1: insert into TDeposit values('&n', '&a')
new 1: insert into TDeposit values('Bharath', 'AN05')

1 row created.

SQL> /
Enter value for n: Chandru
Enter value for a: AN06
old 1: insert into TDeposit values('&n', '&a')
new 1: insert into TDeposit values('Chandru', 'AN06')

1 row created.

SQL> /
Enter value for n: Santhosh
Enter value for a: AN07
old 1: insert into TDeposit values('&n', '&a')
new 1: insert into TDeposit values('Santhosh', 'AN07')

1 row created.

SQL> /
Enter value for n: Kemilas
Enter value for a: AN08
old 1: insert into TDeposit values('&n', '&a')
new 1: insert into TDeposit values('Kemilas', 'AN08')

1 row created.

SQL> /
Enter value for n: Idris
Enter value for a: AN09
old 1: insert into TDeposit values('&n', '&a')
new 1: insert into TDeposit values('Idris', 'AN09')

1 row created.

SQL> /
Enter value for n: Giri
Enter value for a: AN10;
old 1: insert into TDeposit values('&n', '&a')
new 1: insert into TDeposit values('Giri', 'AN10;')

1 row created.
SQL> select * from TDeposit;

C_NAME A_NO
---------- ------
Arun AN01
Siva AN02
Praveen AN03
Dinesh AN04
Bharath AN05
Chandru AN06
Santhosh AN07
Kemilas AN08
Idris AN09
Giri AN10;

10 rows selected.

SQL> select C_Name from TDeposit union select C_Name from TBorrow;

C_NAME
----------
Ajay
Arun
Bharath
Chandru
Dharsan
Dinesh
Elwin
Faizal
Giri
Harish
Idris

C_NAME
----------
Idris
Jack
Kemilas
Praveen
Santhosh
Siva

17 rows selected.

SQL> select C_Name from TDeposit intersect select C_Name from TBorrow;

C_NAME
----------
Bharath
Chandru
Giri

SQL> select C_Name from TDeposit minus select C_Name from TBorrow;

C_NAME
----------
Arun
Dinesh
Idris
Kemilas
Praveen
Santhosh
Siva

7 rows selected.

SQL> select C_Name from TBorrow minus select C_Name from TDeposit;

C_NAME
----------
Ajay
Dharsan
Elwin
Faizal
Harish
Idris
Jack

7 rows selected.

SQL> select * from TLoan;

L_NO B_NAME AMOUNT


------ --------------- ----------
LN01 Jamal 50000
LN04 Mathur 30000
LN03 Jamal 45000
LN07 Airport 50000
LN05 Thennur 35000
LN06 KK Nagar 60000
LN07 Jamal 26000
LN03 Mathur 39000
LN07 KK Nagar 70000
LN01 Thennur 52000

10 rows selected.

SQL> select avg(Amount) from TLoan;

AVG(AMOUNT)
-----------
45700

SQL> select max(Amount) from TLoan;

MAX(AMOUNT)
-----------
70000

SQL> select min(Amount) from TLoan;


MIN(AMOUNT)
-----------
26000

SQL> select sum(Amount) from TLoan;

SUM(AMOUNT)
-----------
457000

SQL> select count(Amount) from TLoan;

COUNT(AMOUNT)
-------------
10

SQL> select min(Amount) from(select distinct Amount from TLoan order by Amount
desc) where rownum < 4;

MIN(AMOUNT)
-----------
52000

You might also like