TABLE DEFINITIONS
SQL> CREATE TABLE Emp (
emp_no NUMBER,
emp_name VARCHAR(20),
join_date DATE,
join_basic NUMBER(7, 2),
PRIMARY KEY (emp_no)
);
Table created.
Insert the following data:
EMP NO | EMP NAME | JOIN DATE | JOIN BASIC |
1001 | Subhas bose | 01-JUN-96 | 3000 |
1002 | Nadeem shah | 01-JUN-96 | 2500 |
1003 | Charles babbage | 01-JUN-96 | 3000 |
1004 | Shreyas kumar | 01-JUL-96 | 2500 |
1005 | George boole | 01-JUL-96 | 2800 |
SQL> CREATE TABLE Salary (
emp_no NUMBER,
basic NUMBER(7, 2),
commission NUMBER(7, 2),
deduction NUMBER(7, 2),
salary_date DATE,
FOREIGN KEY (emp_no) REFERENCES Emp
);
Table created.
Insert the following data:
EMP NO | BASIC | COMMISSION | DEDUCTION | SALARY DATE |
1001 | 3000 | 200 | 250 | 30-JUN-96 |
1002 | 2500 | 120 | 200 | 30-JUN-96 |
1003 | 3000 | 500 | 290 | 30-JUN-96 |
1004 | 2500 | 200 | 300 | 30-JUN-96 |
1005 | 2800 | 100 | 250 | 30-JUN-96 |
1001 | 3000 | 200 | 250 | 31-JUL-96 |
1002 | 2500 | 120 | 200 | 31-JUL-96 |
1003 | 3000 | 500 | 290 | 31-JUL-96 |
1004 | 2500 | 200 | 300 | 31-JUL-96 |
1005 | 2800 | 100 | 150 | 31-JUL-96 |
QUERIES
1) To sum the salary of each employee.
SELECT emp_no, SUM(basic)
FROM salary
GROUP BY emp_no;
2) To sum the salary of each employee and sort it on the sum of basic.
SELECT emp_no, SUM(basic)
FROM salary
GROUP BY emp_no
ORDER BY SUM(basic);
3) To sum the salary of each employee and sort it in descending order on the sum of basic.
SELECT emp_no, SUM(basic)
FROM salary
GROUP BY emp_no
ORDER BY SUM(basic) DESC;
4) To sum the salary of each employee and sort it in descending order on the sum of basic. Display name also
SELECT s.emp_no, e.emp_name, SUM(s.basic)
FROM salary s, emp e
WHERE s.emp_no = e.emp_no
GROUP BY s.emp_no, e.emp_name
ORDER BY SUM(s.basic) DESC;
5) To group the data by average salary of each employee.
SELECT s.emp_no, INITCAP(e.emp_name), AVG(s.basic)
FROM salary s, emp e
WHERE s.emp_no = e.emp_no
GROUP BY s.emp_no, e.emp_no
ORDER BY AVG(s.basic);
6) To group the basic by month.
SELECT TO_CHAR(salary_date, ‘MONTH’) “MONTH”, SUM(basic) “TOTAL BASIC”
FROM salary
GROUP BY TO_CHAR(salary_date, ‘MONTH’);
7) To group the data by average salary of each employee and display where average basic is more than 2000..
SELECT s.emp_no, INITCAP(e.emp_name), AVG(s.basic)
FROM salary s, emp e
WHERE s.emp_no = e.emp_no
GROUP BY s.emp_no, e.emp_no
HAVING AVG(s.basic) >= 2000
ORDER BY AVG(s.basic);
SUBQUERIES
8) To list the employees who earn less than the average salary.
SELECT *
FROM salary
WHERE basic < (SELECT AVG(basic)
FROM salary);
9) To list the employees whose deduction is 150.
SELECT *
FROM salary
WHERE emp_no IN (SELECT emp_no
FROM salary
WHERE deduction = 150);
10) To list the names of employees and salary details, whose basic is less than the average salary.
SELECT s.*, e.emp_name
FROM salary s, emp e
WHERE s.emp_no = e.emp_no
AND s.basic < (SELECT AVG(basic)
FROM salary);
WEEK 6
2) Queries (along with subqueries) using ANY, ALL, IN, EXISTS, NOT EXISTS, UNIQUE, INTERSECT, Constraints. Example: select the rollno and name of the student who secured 4th rank in the class.
3) Queries using Aggregate functions (COUNT, SUM, AVG, MAX and MIN), GROUP BY, HAVING and Creation and Dropping of Views.
4) Queries using Conversions, functions (to_char, to_num, and to_date), string function (Conactenation, lpad, rpad, ltrim, rtrim, lower, upper, initcap, length, substr, and instr), date functions (sysdate, next_day, add_months, last_day, months_between, least, greatest, trunk, round, to_char, to_date).
TABLE DEFINITIONS
Branch Schema <branch-name, branch-city, assets>
Customer Schema <customer-name, customer-street, customer-city>
Loan Schema <loan-number, branch-name, amount>
Borrower Schema <customer-name, loan-number>
Account Scheme <account-number, branch-name, balance>
Depositor Scheme <customer-name, account-number>
BRANCH TABLE
Branch Name | Branch City | Assets |
Brighton | Brooklyn | 7100000 |
Downtown | Brooklyn | 9000000 |
Mianus | Horseneck | 400000 |
North Town | Rye | 3700000 |
Perryridge | Horseneck | 1700000 |
Pownal | Bennington | 300000 |
Redwood | Palo Alto | 2100000 |
Round Hill | Horseneck | 800000 |
CUSTOMER TABLE
Customer Name | Customer Street | Customer City |
Adams | Spring | Pittsfield |
Brooks | Senator | Brooklyn |
Curry | North | Rye |
Glenn | Sand Hill | Woodside |
Green | Walnut | Stamford |
Hayes | Main | Harrison |
Johnson | Alma | Palo Alto |
Jones | Main | Harrison |
Lindsay | Park | Pittsfield |
Smith | North | Rye |
Turner | Putnam | Stamford |
Williams | Nassau | Princeton |
LOAN TABLE
Loan Number | Branch Name | Amount |
L-11 | Round Hill | 900 |
L-14 | Downtown | 1500 |
L-15 | Perryridge | 1500 |
L-16 | Perryridge | 1300 |
L-17 | Downtown | 1000 |
L-23 | Redwood | 2000 |
L-93 | Mianus | 500 |
BORROWER TABLE
Customer Name | Loan Number |
Adams | l-16 |
Curry | L-93 |
Hayes | L-15 |
Jackson | L-14 |
Jones | L-17 |
Smith | L-11 |
Smith | L-23 |
Williams | L-17 |
ACCOUNT TABLE
Account Number | Branch Name | Balance |
A-101 | Downtown | 500 |
A-102 | Perryridge | 400 |
A-201 | Brighton | 900 |
A-215 | Mianus | 700 |
A-217 | Brighton | 750 |
A-222 | Redwood | 700 |
A-305 | Round Hill | 350 |
DEPOSITOR TABLE
Customer Name | Account Number |
Hayes | A102 |
Johnson | A-101 |
Johnson | A-201 |
Jones | A-217 |
Lindsay | A-222 |
Smith | A-215 |
Turner | A-305 |
QUERIES
1) To list all the fields from the table Customer.
SELECT branch_name
FROM Loan;
2) To list rows after eliminating duplicates.
SELECT distinct branch_name
FROM Loan;
3) To explicitly list rows, including duplicates.
SELECT all branch_name
FROM Loan;
4) To list fields after applying arithmetic operations.
SELECT loan_number, branch_name, amount *100
FROM Loan;
5) Find all loan numbers for loans made at the Perryridge branch with loan amounts
greater than Rs1200.
SELECT loan_number
FROM Loan
WHERE branch_name = ‘Perryridge’
AND amount > 1200;
6) Find all loan numbers for loans with loan amounts between Rs90,000 and Rs100,000.
SELECT loan_number
FROM Loan
WHERE amount BETWEEN 90000 AND 100000;
Or
SELECT loan_number
FROM Loan
WHERE amount <= 100000
AND amount >= 90000;
7) Find all loan numbers for loans with loan amounts not between Rs90,000 and
Rs100,000.
SELECT loan_number
FROM Loan
WHERE amount NOT BETWEEN 90000 AND 100000;
8) For all customers who have a loan from the bank, find their names, loan numbers and loan amounts.
SELECT customer_name, Borrower.loan_number, amount
FROM Borrower, Loan
WHERE Borrower.loan_number = Loan.loan_number;
Or
SELECT customer_name, Borrower.loan_number AS loan_id, amount
FROM Borrower, Loan
WHERE Borrower.loan_number = Loan.loan_number;
9) Find the customer names, loan numbers and loan amounts for all loans at the Perryridge branch.
SELECT customer_name, Borrower.loan_number, amount
FROM Borrower, Loan
WHERE Borrower.loan_number = Loan.loan_number
AND branch_name = ‘Perryridge’;
Or
SELECT customer_name, T.loan_number, S.amount
FROM Borrower AS T, Loan AS S
WHERE T.loan_number = S.loan_number
AND branch_name = ‘Perryridge’;
10) Find the names of all branches that have assets greater than atleast one branch located in Brooklyn.
SELECT DISTINCT T.branch_name
FROM Branch as T, Branch as S
WHERE T.assets > S.assets
AND S.branch_city = ‘Brooklyn’;
11) Find the names of all customers whose street address includes the substring ‘Main’.
SELECT customer_name
FROM Customer
WHERE customer_street LIKE ‘%Main%’;
12) To list in alphabetic order all customers who have a loan at the Perryridge branch.
SELECT DISTINCT customer_name
FROM Borrower B, Loan L
WHERE B.loan_number = L.loan_number
AND branch_name = ‘Perryridge’
ORDER BY customer_name;
13) To list the entire loan info in descending order of amont.
SELECT *
FROM Loan
ORDER BY amount DESC, loan_number ASC;
14) To find all customers having a loan, an account or both at the bank, without duplicates.
(SELECT customer_name
FROM Depositor)
UNION
(SELECT customer_name
FROM Borrower);
15) To find all customers having a loan, an account or both at the bank, with duplicates.
(SELECT customer_name
FROM Depositor)
UNION ALL
(SELECT customer_name
FROM Borrower);
16) To find all customers having both a loan and an account at the bank, without duplicates.
(SELECT customer_name
FROM Depositor)
INTERSECT
(SELECT customer_name
FROM Borrower);
17) To find all customers having a loan, an account or both at the bank, with duplicates.
(SELECT customer_name
FROM Depositor)
INTERSECT ALL
(SELECT customer_name
FROM Borrower);
18) To find all customers who have an account but no loan at the bank, without duplicates.
(SELECT DISTINCT customer_name
FROM Depositor)
EXCEPT
(SELECT customer_name
FROM Borrower);
19) To find all customers who have an account but no loan at the bank, with duplicates.
(SELECT DISTINCT customer_name
FROM Depositor)
EXCEPT ALL
(SELECT customer_name
FROM Borrower);
20) Find the average account balance at the Perryridge branch
SELECT branch_name, AVG(balance)
FROM Account
WHERE branch_name = ‘Perryridge’;
21) Find the average account balance at the each branch
SELECT AVG(balance)
FROM Account
GROUP BY branch_name;
22) Find the number of depositors for each branch .
SELECT branch_name, COUNT(DISTINCT customer_name)
FROM Depositor D, Account A
WHERE D.account_number = A.account_number
GROUP BY branch_name;
23) Find the number of depositors for each branch where average account balance is more than Rs 1200.
SELECT branch_name, COUNT(DISTINCT customer_name)
FROM Depositor D, Account A
WHERE D.account_number = A.account_number
GROUP BY branch_name
HAVING AVG(balance) > 1200;
24) Find the average balance for all accounts.
SELECT AVG(balance)
FROM Account;
25) Find the number of tuples in the customer relation.
SELECT COUNT(*)
FROM Customer;
26) Find the average balance for each customer who lives in Harrision and has at least three accounts.
SELECT D.customer_name, AVG(balance)
FROM Depositor D, Account A, Customer C
WHERE D.account_number = A.account_number
AND D.customer_name = C.customer_name
AND C.customer_city = ‘Harrison’
GROUP BY D.customer_name
HAVING COUNT(DISTINCT D.account_number) >= 3;
27) Find all the loan number that appear in loan relation with null amount values.
SELECT loan_number
FROM Loan
WHERE amount IS NULL;
28) Find all customers who have both a loan and an account at the bank.
SELECT customer_name
FROM Borrower
WHERE customer_street IN (SELECT customer_name
FROM Depositor);
29) Find all customers who have both an account and a loan at the Perryridge branch
SELECT DISTINCT B.customer_name
FROM Borrower B, Loan L
WHERE B.loan_number L.loan_number
AND branch_name = ‘Perryridge’
AND (branch_name, customer_name) IN
(SELECT branch_name, customer_name
FROM Depositor D, Account A
WHERE D.account_number = A.account_number);
or
SELECT customer_name
FROM Borrower B
WHERE EXISTS (SELECT *
FROM Depositor D
WHERE D.customer_name = B.customer_name);
30) Find all customers who do not have a loan at the bank, but do not have an account the bank.
SELECT DISTINCT customer_name
FROM Borrower
WHERE customer_name NOT IN
(SELECT customer_name
FROM Depositor);
31) Find the names of customers who do have a loan at the bank, and whose names are neither Smith nor Jones.
SELECT DISTINCT customer_name
FROM Borrower
WHERE customer_name NOT IN (‘Smith’, ‘Jones’);
32) Find the names of all branches that have assets greater than those of at least one branch located in Brooklyn.
SELECT DISTINCT T.branch_name
FROM Branch AS T, Branch AS S
WHERE T.assets > S.assets
AND S.branch_city = ‘Brooklyn’;
33) Find the names of all branches that have assets greater than that of each branch located in Brooklyn.
SELECT branch_name
FROM Account
GROUP BY branch_name
HAVING AVG(balance) >= ALL (SELECT AVG(balance)
FROM Account
GROUP BY branch_name);
34) Find all customers who have an account at all the branches located in Brooklyn.
SELECT DISTINCT S.customer_name
FROM Depositor AS D
WHERE NOT EXISTS ((SELECT branch_name
FROM Branch
WHERE branch_city = ‘Brroklyn)
EXCEPT
(SELECT R.branch_name
FROM Depositor AS T, Account AS R
WHERE T.account_number =
R.account_number
AND D.customer_name = t.customer_name));
35) Find all customers who have at most one account at the Perryridge branch.
SELECT T.customer_name
FROM Depositor AS T
WHERE UNIQUE (SELECT R.customer_name
FROM Depositor AS R, Account AS A
WHERE T.customer_name = R.customer_name
AND R.account_number = A.account_number
AND A.branch_name = ‘Perryridge’);
36) Find all customers who have at least two accounts at the Perryridge branch.
SELECT DISTINCT T.customer_name
FROM Depositor AS T
WHERE NOT UNIQUE (SELECT R.customer_name
FROM Depositor AS R, Account AS A
WHERE T.customer_name = R.customer_name
AND R.account_number = A.account_number
AND A.branch_name = ‘Perryridge’);
37) Find the average account balance of those branches where the average account balance is greater than 1200.
SELECT branch_name, avg_balance
FROM (SELECT branch_name, AVG(balance)
FROM Account
GROUP BY branch_name)
AS Branch_avg(branch_name, avg_balance)
WHERE avg_balance > 1200;
38) Find the maximum across all branches of the total balance at each branch.
SELECT MAX(tot_balance)
FROM (SELECT branch_name, SUM(balance)
FROM Account
GROUP BY branch_name)
AS Branch_total(branch_name, tot_balance);
39) Find the all customers who have an account but no loan at the bank.
SELECT d-CN
FROM (Depositor LEFT OUTER JOIN Borrower
ON Depositor.customer_name = Borrower.customer_name)
AS db1(d-CN, account_number, b-CN, loan_number)
WHERE b-CN is null;
40) Find the all customers who have either an account or a loan (but not both) at the bank.
SELECT customer_name
FROM (Depositor NATURAL FULL OUTER JOIN Borrower)
WHERE account_number IS NULL
OR loan_number IS NULL;
WEEK 7
5) Queries (along with subqueries) using ANY, ALL, IN, EXISTS, NOT EXISTS, UNIQUE, INTERSECT, Constraints. Example: select the rollno and name of the student who secured 4th rank in the class.
6) Queries using Aggregate functions (COUNT, SUM, AVG, MAX and MIN), GROUP BY, HAVING and Creation and Dropping of Views.
7) Queries using Conversions, functions (to_char, to_num, and to_date), string function (Conactenation, lpad, rpad, ltrim, rtrim, lower, upper, initcap, length, substr, and instr), date functions (sysdate, next_day, add_months, last_day, months_between, least, greatest, trunk, round, to_char, to_date).
DUAL (ORACLE WORK TABLE):
1) To display system date.
select sysdate from dual;
2) To display arithmetic calculations.
select 2*2 from dual;
3) To display the logged user.
select USER from dual;
4) To display system time.
select TO_CHAR(sysdate, ‘HH:MI:SS’) from dual;
5) To display current month.
select TO_CHAR(sysdate, ‘MONTH’) from dual;
6) To display system date in specified format.
select TO_CHAR(sysdate, ‘dd/mm/yy’) from dual;
7) To display system date in specified format.
select TO_CHAR(sysdate, ‘mm’) from dual;
8) To display date arithmetic.
select ADD_MONTHS(SYSDATE, 5) from dual;
9) To display date arithmetic.
select LAST_DAY(SYSDATE) from dual;
10) To display date arithmetic.
select MONTHS_BETWEEN(SYSDATE, ’01-APR-09’) from dual;
11) To display date arithmetic.
select NEXT_DAY(SYSDATE, ‘MON’) from dual;
GROUP FUNCTIONS:
12) To display average basic salary of the employees.
SELECT SUM(basic) FROM salary;
13) To display minimum basic salary of the employees.
SELECT MIN(basic) FROM salary;
14) To display maximum basic salary of the employees.
SELECT MAX(basic) FROM salary;
15) To display sum of basic salaries of all the employees.
SELECT SUM(basic) FROM salary;
16) To display the number of records in salary table.
SELECT COUNT(*) FROM salary;
STRING FUNCTIONS:
17) To display a field value after left padding.
select lpad('page-1', 10, '*') from dual;
18) To display a field value after left padding.
select Rpad('page-1', 10, '*') from dual;
19) To display a field value after converting to lower case.
select LOWER(‘A’) from dual;
20) To display a field value after converting to upper case.
select LOWER(‘a’) from dual;
21) To display a field value after converting to initial capital case.
select INITCAP(‘HOw are you?’) from dual;
22) To display a substring of a field value.
select substr(‘cse2a’, 4, 2) from dual;
23) To display the length of a field value.
select length(’how long am i?’) from dual;
24) To display a field value after trimming the right side.
select rtrim(‘cse2a’, ‘2a’) from dual;
25) To display a field value after trimming the left side.
SELEct ltrim(‘cse2a’, ‘cse’) from dual;
WEEK 8 (PL/SQL)
6) (i) Creation of simple PL/SQL program which includes declaration section, executable section and exception handling section ( ex: Student marks can be selected from the table and printed for those who secured first class and an exception can be raised if no records were found).
(ii) Insert data into student table and use COMMIT, ROLLBACK and SAVEPOINT in SQL block.
7) Develop a program that includes the features NESTED IF, CASE and CASE expression. The program can be extended using the NULLIF and COALESCE functions.
8) Program development using WHILE LOOPS, numeric FOR LOOPS, nested loops using ERROR handling, BUILT IN exceptions, USER defined exceptions, RAISE APPLICATION ERROR.
9) Program development using creation of procedure, passing parameters IN and OUT procedures.
10) Program development using creation of stored function, invoke functions in SQL statements and write complex functions.
11) Program development using creation of package specification, package bodies, private objects, package variables and cursors and calling stored packages.
12) Develop programs using features of parameters in a CURSOR, FOR UPDATE CURSOR, WHERE CURRENT of clause and CURSOR variables.
Syntax to write a sql program
Declare
<declaration stmts>
Begin
<executable stmts>
[exception <exceptional stmts>]----- optional
End;
/---end of buffer
Example: 1
Create a file DBFOR.SQL, to execute the FOR loop and display the variable.
At SQL Prompt type, ed dbfor to open notepad and type the below program:
Program
declare
cnt number;
begin
dbms_output.put_line('This is a demo of FOR loop ');
for cnt in 1..5 loop
dbms_output.put_line('loop number ' || cnt);
end loop;
end;
/
set serveroutput off
Save the file and at SQL prompt run as:
Execution
SQL>set serveroutput on
SQL> start dbfor (press enter) OR
SQL> @dbfor
OUTPUT:-
This ia a demo of FOR loop
loop number 1
loop number 2
loop number 3
loop number 4
loop number 5
PS:
For syntax:
For <var> in <start_num> .. <endnum> loop
<statement(s);>
End loop;
Example: 2
Create a file DBREVFOR.SQL, to execute the REVERSE FOR loop and display the variable.
Program
begin
dbms_ouput.put_line(‘This is a demo of REVERSE FOR loop’);
for cnt in reverse 1..10 loop
if mod(cnt, 2) = 0 then
dbms_output.put_line(‘loop counter ‘ || cnt);
end if;
end loop;
end;
/
OUTPUT:-
This is a demo of REVERSE FOR loop
loop counter 10
loop counter 8
loop counter 6
loop counter 4
loop counter 2
loop counter 8
loop counter 6
loop counter 4
loop counter 2
PS:
Reverse For syntax:
For <var> in reverse <start_num> .. <endnum> loop
<statement(s);>
End loop;
Other forms of if syntax are:
If <condition> then
<action(s);>
End if;
If <condition> then
<action(s);>
Else
<action(s);>
End if;
If <condition> then
<action(s);>
Elsif <condition> then
<action(s);>
else
<action(s);>
End if;
Example: 3
Create a file DBLOOP.SQL, to execute the LOOP loop and display the variable.
Program
set serveroutput on
declare
cnt number(2) := 0;
begin
dbms_ouput.put_line(‘This is a demo of LOOP loop’);
loop
cnt := cnt + 1;
exit when cnt > 10;
dbms_output.put_line(‘loop counter ‘ || cnt);
end loop;
end;
/
set serveroutput off
OUTPUT:-
This is the demo of LOOP loop
loop counter 1
loop counter 2
loop counter 3
loop counter 4
loop counter 5
loop counter 6
loop counter 7
loop counter 8
loop counter 9
loop counter 10
loop counter 4
loop counter 5
loop counter 6
loop counter 7
loop counter 8
loop counter 9
loop counter 10
PS:
Loop syntax:
loop
<statement(s);>
Exit when <condition>;
End loop;
Example: 4
Create a file DBWHILE.SQL, to execute the WHILE loop and display the variable.
Program
set serveroutput on
declare
cnt number(2) := 1;
begin
dbms_ouput.put_line(‘This is a demo of WHILE loop’);
while cnt <= 10 loop dbms_output.put_line(‘loop counter: ‘ || to_char(cnt, ‘999’));
cnt := cnt + 1;
end loop;
end;
/
set serveroutput off