PRACTICAL 3 & 4

DDL Commands & Constraints

1.Write the syntax to create the grad_candidates

table

CREATE TABLE graduate_candidates (

student_id NUMBER(6,0),
last_name VARCHAR2(75),
first_name VARCHAR2(75)],
credits NUMBER(5,2),
graduation_date DATE
);
2.CONFIRM CREATION OF THE TABLE

DESC graduate_candidates;

3.Create a new table using grad_candidates.

CREATE TABLE o_grad_candidates AS (SELECT *
FROM graduate_candidates);
4.Create a new table using a subquery. Name the
new table your first name -- e.g., gaurav_table.
Using a subquery, copy grad_candidates into
gaurav_table.
CREATE TABLE anshuman_table AS (SELECT *
FROM graduate_candidates);
desc anshuman_table

6.In your o_grad_candidates table, enter a new

column called “adm_date.” The datatype for the
new column should be VARCHAR2. Set the DEFAULT
for this column as SYSDATE

alter table o_grad_candidates add "adm_date"

varchar2(10) default SYSDATE; desc
o_grad_candidates
7.In your o_grad_candidates table, increase the
length of last_name column by 10 and remove the
credits column
alter table o_grad_candidates alter
column last_name type VARCHAR2(85); desc
o_grad_candidates

8.Create a new column in the anshuman_table

table called start_date. Use the TIMESTAMP WITH
LOCAL TIME ZONE as the datatype

alter table anshuman_table add start_date

TIMESTAMP WITH LOCAL TIME ZONE;
9.Write syntax to change the name of credit
column by grad_credit
alter table o_grad_candidates rename column
credits to grad_credit; desc
o_grad_candidates
10.Rename o_grad_candidates to n_grad_candidates
ALTER TABLE o_grad_candidates RENAME TO
n_grad_candidates ;desc n_grad_candidates
 PRACTICAL 5
DML COMMANDS

1.Create the tables mentioned below and insert

the rows as shown. Please assume the datatype
and constraints as required.

CREATE TABLE AD_ACADEMIC_SESSIONS

(
ID NUMBER(4),
NAME VARCHAR2(10)
);

CREATE TABLE AD_DEPARTMENTS

(
ID NUMBER(4),
NAME VARCHAR2(10),
HEAD VARCHAR2(20)
);
CREATE TABLE AD_PARENT_INFORMATION
(
ID NUMBER(4),
PARENT1_FN VARCHAR2(10),
PARENT1_LN VARCAHR2(10),
PARENT2_FN VARCHAR2(10),
PARENT2_LN VARCAHR2(10)
);

INSERTING VALUES-

INSERT INTO AD_ACADEMIC_SESSIONS(ID,NAME) VALUES

(100,'SPRING');
INSERT INTO AD_ACADEMIC_SESSIONS(ID,NAME) VALUES
(200,'SPRING1');
INSERT INTO AD_ACADEMIC_SESSIONS(ID,NAME)
VALUES (300,'SPRING2');
2.Add 2 new rows in AD_ACADEMICS_SESSIONS
table with name as “Summer Break Session” and
“Winter Break Session”.

ALTER TABLE AD_ACADEMIC_SESSIONS

ADD SUMMER_BREAK VARCHAR(50),
ADD WINTER_BREAK_SESSION VARCHAR(50);

3.Update the name “Computer Science” with

“Computer Science and Engineering”
inAD_DEPARTMENTStable.
UPDATE TABLE AD_DEPARTMENTS SET COLUMN NAME=
‘COMPUTER SCIENCE AND ENGINEERING’ WHERE
NAME=’COMPUTER SCIENCE’;

4.Update the PARENT1_LN as NULL for ID 620 in

AD_PARENT_INFORMATION table.
UPDATE TABLE AD_PARENT_INFORMATION SET COLUMN PARENT1_LN =
NULL WHERE ID=620;
5. Delete the 2 new rows added in question 2.

DELETE FROM AD_ACADEMIC_SESSIONS WHERE ID

IN(200,300);
 PRACTICAL 6
DQL AND SORTING DATA

EXERCISE – 1

(A) List department names and location from the

department table.
SELECT DNAME,LOC FROM DEPT;

(B) List the employees belonging to the

department 20.
SELECT E_NAME FROM EMP WHERE DEPT_NO = 20;

(C) List the name and salary of the employees

whose salary is more than 1000.
SELECT E_NAME, SAL FROM EMP WHERE SAL >
1000;

(D) List the employee number and name of

managers

SELECT EMP_NO, ENAME,JOB FROM EMP WHERE

JOB = ‘MGR’;

(E) List the name of clerks working in the dept

20

SELECT E_NAME, SAL FROM EMP WHERE DEPT_NO

= 20;
 (F) List the name of analyst and the salesman

SELECT E_NAME, SAL FROM EMP WHERE JOB

IN(‘ANALYST’,’SALESMEN’);

(G) List the name of employee who are not the

managers

SELECT E_NAME, SAL FROM EMP WHERE JOB <>

‘MANAGER’;

EXERCISE – 2

(A) List the name of the employee whose

employee number are
7369,7521,7839,7934,7788.
SELECT E_NAME FROM EMP WHERE EMP_NO
IN(7369,7521,7839,7934,7788);
(B) List the employees DETAILS NOT BELONGING TO
THE DEPT 10 30 40.
SELECT * FROM EMP WHERE DEPT_NO NOT IN(10,30,40);
(C) List the name and salary of the employees
WHOSE SALARY IN BETWEEN 1000 AND 2000.
SELECT ENAME, SAL FROM EMP WHERE SAL BETWEEN 1000 AND 2000;

(D) List employee names, who have joined before

30th June 81 and after December 81
SELECT ENAME FROM EMP WHERE HIREDATE < ’30-JUNE-81’ OR
HIREDATE >=’01-JAN-82’;
Exercise -3: DISTINCT Clause with SELECT

(A) List the different jobs (designations) available in the EMP table.

QUERY- SELECT DISTINCT JOB FROM EMP;

Exercise -4: Working with NULL Values

(A) List the employee names, who are not eligible for commission.

QUERY- SELECT ENAME FROM EMP WHERE COMM IS NULL;

(B) List the name of the employees and designation (job) of the employee,
who does not report to anybody (manager is NULL).

QUERY – SELECT ENAME, JOB FROM EMP WHERE MGR IS NULL;

(C) List the employees not assigned to any department.

QUERY- SELECT ENAME FROM EMP WHERE DEPTNO IS NULL;

(D) List the employees who are eligible for commission.

QUERY- SELECT * FROM EMP WHERE COMM IS NOT NULL;

(E) List the details of employees, whose salary is greater than 2000 and
commission is NULL.

QUERY- SELECT * FROM EMP WHERE SAL> 2000 AND COMM IS NULL;

Exercise -5: Matching Pattern with Column

(A) List the employees whose names start with an “S”.

QUERY- SELECT * FROM EMP WHERE ENAME LIKE ‘S%’;

(B) List the employees names ending with an “S”.

QUERY-SELECT * FROM EMP WHERE ENAME LIKE ‘%S’;

(C) List the names of employees whose names have exactly 5 Characters.

QUERY- SELECT ENAME FROM EMP WHERE ENAME LIKE ‘-----‘;

 (D) List the employee names having “I” as the second character

QUERY- SELECT ENAME FROM EMP WHERE ENAME LIKE ‘-T%’;

Exercise -6: Using Expression with Column

(A) List the name, salary and PF amount of all the employees (PF is
calculated as 10% of salary).

QUERY- SELECT ENAME , SAL , SAL*0.1 AS PF FROM EMP ;

(B) List the names of employees, who are more than 2 years old in the
organization.

QUERY- SELECT ENAME FROM EMP WHERE ‘2021’

TO_CHAR(HIREDATE,’YYYY’)>2;

Exercise -7: Ordering the Results of a query.

(A) List the empno, ename, sal in ascending order of salary.

QUERY- SELECT EMPNO,ENAME, SAL FROM EMP ORDER BY SAL;

(B) List the employee name and hiredate in descending order of Hiredate.

QUERY- SELECT ENAME, HIREDATE FROM EMP ORDER BY HIREDATE

DESC;

(C) List the employee name, salary, job and department No. in ascending
order of Department No. in ascending order of Department No and then on
descending descending order of salary.

QUERY- SELECT ENAME,SAL,JOB,DEPTNO FROM EMP ORDER BY DEPTNO,

SAL DESC;
 PRACTICAL 7
SUB QUERY AND NESTED QUERY

Create Table Commands-

Customer Table-

Create table customer( customer_id char(4) primary key, customer_name varchar(10),

customer_city varchar (10));

Branch table-

Create table branch( branch_name varchar(10) primary key, branch_city, assets

float(10));

Account table-

Create table account ( account_number char(4) primary key, branch_name varchar(10)

references branch(branch_name), balance float(10));

Depositor table-

Create table depositor (customer_id char(4) references customer(customer_id),

account_number char(4) references account(account_number), primary
key(customer_id,account_number));

Borrower table-

Create table borrower ( customer_id char(4) references customer(customer_id),

loan_number char(4), primary key(customer_id,loan_number));

Exercise-

(A) Find customer ids of those customers who are borrower from the banks and
who appear in the list of account holders.

Query- SELECT C.CUSTOMER_ID FROM CUSTOMER C, DEPOSITOR D,

BORROWER B WHERE C.CUTOMER_ID = D.CUSTOMER_ID AND
C.CUSTOMER_ID= B.CUSTOMER_ID;

(B) Find those customer names who are borrower.

QUERY- SELECT C.CUSTOMER_NAME FROM CUSTOMER C, BORROWER B

WHERE C.CUSTOMER_ID= B.CUSTOMER_ID;
(C) Find the name of the customers who have a loan from the bank, but do not
have an account at the bank.

QUERY- SELECT C.CUSTOMER_NAME FROM CUSTOMER C, BORROWER B

WHERE C.CUSTOMER_ID=B.CUSTOMER_ID AND C.CUSTOMER_ID NOT IN
(SELECT CUSTOMER_ID FROM DEPOSITOR);

(D) Get the Customer Id and name of those customers who have both account
and loan from the bank.

Query- SELECT C.CUSTOMER_ID, C.CUSTOMER_NAME FROM CUSTOMER C,

DEPOSITOR D, BORROWER B WHERE C.CUTOMER_ID = D.CUSTOMER_ID
AND C.CUSTOMER_ID= B.CUSTOMER_ID;

(E) Get Branch Name of the branch having highest average balance amongst
all branches.

QUERY- SELECT B.BRANCH_NAME FROM BRANCH B, ACCOUNT A WHERE

B.BRANCH_NAME= A.BRANCH_NAME GROUP BY B.BRANCH_NAME
HAVING MAX(SUM(A.BALANCE)/COUNT(B.BRANCH_NAME));
 PRACTICAL 8
JOIN USING MULTIPLE TABLES

• Create a cross-join that displays

details of database tables.
SELECT * FROM EMP E CROSS JOIN DEPT D WHERE E.DEPTNO
= D.DEPTNO;

• Create a query that uses a natural join

to join the database tables.
SEELCT * FROM EMP NATURAL JOIN DEPT ;
• Create a query that uses an inner join
to join the database table

SELECT * FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO = DEPT.DEPTNO;

• Create a query that uses Left outer join

,Full outer join and right outer join to
join the database tables

Full Outer Join

SELECT * FROM EMP c LEFT JOIN DEPT o ON
o.DEPTNO = c.DEPTNO ORDER BY ENAME;
LEFT OUTER JOIN

SELECT * FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

RIGHT OUTER JOIN

SELECT * FROM EMP E RIGHT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

 PRACTICAL 9

Restricting Data Using Group By Clause

Exercise -1:

(A) Write SQL query that displays the total and average payments of all
the credit orders.

QUERY- SELECTSUM(TOTAL), AVG(TOTAL) FROM ORDER WHERE

PAYMENT= ‘CREDIT’ GROUP BY PAYMENT;

(B) Write SQL query that displays the total and average payments
grouped by type of payment.

QUERY- SELECTSUM(TOTAL), AVG(TOTAL) FROM ORDER GROUP BY

PAYMENT;

(C) How many order dates are represented compared to the total number
of orders?

QUERY- SELECT COUNT(DISTINCT DATE_ORDER) FROM ORDER;

(D) Write SQL query that displays the lowest and highest payments of all
the orders

QUERY- SELECT MAX(TOTAL), MIN(TOTAL) FROM ORDER;

 PRACTICAL 10
SINGLE ROW FUNCTION

(A) Using the three separate words “Oracle,” “Internet,” and

“Academy,” use one command to produce the following output:

The Best Class

Oracle Internet Academy

Query- SELECT CONCAT('Oracle', CONCAT(CONCAT(' ', 'Internet'),

CONCAT(' ', 'Academy'))) AS "The Best Class" FROM DUAL;

(B) Use the string “Oracle Internet Academy” to produce the

following output:

The Net
net

QUERY- SELECT SUBSTR('Oracle Internet Academy', 13, 3) AS "The Net"

FROM DUAL;

(C) What is the length of the string “Oracle Internet Academy”?

QUERY- SELECT LENGTH('Oracle Internet Academy') AS "Length"

FROM DUAL

(D) What’s the position of “I” in “Oracle Internet Academy”?

QUERY- SELECT INSTR('Oracle Internet Academy', 'I') AS "Position"

FROM DUAL;

(E) Starting with the string “Oracle Internet Academy”, pad the
string to create

****Oracle****Internet****Academy****
 QUERY- SELECT REPLACE(RPAD(LPAD('Oracle Internet Academy', 27,
'*'),31,'*'),' ', '****') AS "Result" FROM DUAL;

(F) Display Oracle database employee last_name and salary for

employee_ids between 100 and 102. Include a third column that divides each
salary by 1.55 and rounds the result to two decimal places.

QUERY- SELECT LNAME, SAL, ROUND(SAL/1.55,2) AS “ROUNDED SAL”

FROM EMPLOYEES;

(G) 7. Display employee last_name and salary for those employees who
work in department 80. Give each of them a raise of 5.333% and truncate the
result to two decimal places

QUERY- SELECT LNAME, SAL, TRUNCATE(SAL+ SAL*0.05333,2) FROM

EMPLOYEES WHERE DEPTNO=80