sem 3: dbms practical file
TRANSCRIPT
-
8/2/2019 Sem 3: DBMS Practical File
1/34
NEIL ATHE - A2324710
D
02 - 3C
M
S4 Y3
L
305
B
-
8/2/2019 Sem 3: DBMS Practical File
2/34
INDEX
No Date Topic Sign
1 01-AUG-11 Q0: Creating Tables.
2 01-AUG-11 Q1: Solving Queries based on EMP table.
3 08-AUG-11 Q2: Solving Queries using GROUP BY
4 29-AUG-11 Q3: Solving Queries based on ITEM MASTER table.
5 05-SEP-11 Q3: Nested Queries using TRANSACTION table.
6 26-SEP-11 Q4: Queries on Self Joins, Outer Joins, TOP N QUERY
and nested queries on EMP table.
7 17OCT-11 Q5: Queries using 10 SQL functions.
-
8/2/2019 Sem 3: DBMS Practical File
3/34
Q O
(i) Creating table EMP.
CREATE TABLE EMP (
EMPNO NUMBER( 4) ,
ENAME VARCHAR2( 20) ,
JOB CHAR( 10) ,
MGR NUMBER( 4) ,
HI REDATE DATE,
SAL NUMBER( 9, 2) ,
COMM NUMBER( 7, 2) ,
DEPTNO NUMBER( 2)
) ;
Tabl e cr eat ed.
(ii) After Inserting values, Show all rows of table EMP.SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 Smith Clerk 7902 17-DEC-80 800 20
7499 Allen Salesman 7698 20-FEB-81 1600 300 30
7521 Ward Salesman 7698 22-FEB-81 1250 500 30
7566 Jones Manager 7839 02-APR-81 2975 20
7654 Martin Salesman 7698 28-SEP-81 1250 1400 30
7698 Blake Manager 7839 01-MAY-81 2850 30
7782 Clark Manager 7839 09-JUN-81 2450 10
7788 Scott Analyst 7566 09-DEC-82 3000 20
7839 King President 17-NOV-81 5000 10
7844 Turner Salesman 7698 08-SEP-81 1500 0 30
7876 Adams Clerk 7788 03-DEC-81 950 30
7902 Ford Analyst 7566 04-DEC-81 3000 20
12 r ows sel ect ed.
-
8/2/2019 Sem 3: DBMS Practical File
4/34
(i) Creating table DEPT.
CREATE TABLE DEPT_NM (
DEPTNO NUMBER( 2) ,
DNAME var char 2( 20) ,
LOC var char 2( 10)
) ;
Tabl e cr eat ed.
(ii) After Inserting values, Show all rows of table DEPT.
SELECT * FROM DEPT_NM;
DEPTNO DNAME LOC
10 Accounting New York
20 Research Dallas
30 Sales Chicago40 Operations Boston
-
8/2/2019 Sem 3: DBMS Practical File
5/34
(i) Creating table ITEM_MASTER.
CREATE TABLE I TEM_MASTER (
I TNO NUMBER( 4) PRI MARY KEY,
NAME VARCHAR2( 20) NOT NULL,
QOH NUMBER( 5) DEFAULT 100,
CLASS VARCHAR2( 1) NOT NULL CHECK ( CLASS I N ( ' A' , ' B' , ' C' ) ) ,
UOM VARCHAR2( 4) ,
ROL NUMBER( 5) ,
ROQ NUMBER( 5) ,
RATE NUMBER( 8, 2) NOT NULL) ;
Tabl e cr eat ed.
(ii) After Inserting values, Show all rows of table ITEM_MASTER.
SELECT * FROM I TEM_MASTER;
ITNO NAME QOH CLA UOM ROL ROQ RATE
1090 Hammer 234 A pcs 12 34 400.9
1089 Saw 456 B pcs 17 23 800.89
1088 Lawn Mover 123 C pcs 21 21 5000.88
1087 Dish Washer 234 A pcs 76 45 950.87
1067 Baking Oven 145 A pcs 87 34 6000.67
1063 Spark Plug 150 C watt 34 67 750.63
1609 Alternator 168 B watt 50 56 750.091890 Battery 189 A volt 30 40 300.9
1378 Piston 234 B pcs 45 50 250.78
9 r ows sel ect ed.
-
8/2/2019 Sem 3: DBMS Practical File
6/34
(i) Creating table TRANSACTION.
CREATE TABLE TRANSACTI ON_NM (
I TNO NUMBER( 4) ,
TYPE VARCHAR2( 10) ,
QTY NUMBER( 4) ,
RECEI PTNO VARCHAR2( 20) ,
DOT DATE
) ;
Tabl e cr eat ed.
(ii) After Inserting values, Show all rows of table TRANSACTION.
SELECT * FROM TRANSACTI ON_NM;
ITNO TYPE QTY RECEIPTNO DOT
1090 receive 500 A4333 01-JAN-09
1090issue 100 A4336 23-FEB-10
1609 receive 215 A2143 23-FEB-10
1090 issue 150 A4343 12-MAR-10
1087 issue 300 B4143 01-SEP-10
1087 receive 50 A4143 16-DEC-10
1087 receive 50 A4144 20-DEC-10
1087 receive 50 A4145 30-DEC-10
1087 receive 50 A4149 11-JUN-11
1890 issue 25 C4143 15-JUL-11
1087 receive 50 A4151 20-JUL-11
1090 issue 200 A4133 01-AUG-11
1890 receive 15 C4113 10-SEP-11
1089 issue 125 C4041 19-SEP-11
1090 issue 120 A6336 25-SEP-11
1087 receive 50 A4155 26-SEP-11
16 r ows sel ect ed.
-
8/2/2019 Sem 3: DBMS Practical File
7/34
Q1
(i) List the employees belonging to the department number 20.
SELECT * FROM EMP WHERE DEPTNO=20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 Smith Clerk 7902 17-DEC-80 800 20
7566 Jones Manager 7839 02-APR-81 2975 20
7788 Scott Analyst 7566 09-DEC-82 3000 20
7902 Ford Analyst 7566 04-DEC-81 3000 20
4 r ows i n se t .
(ii) List the Names and Salaries of the employees whose salary is more than 1000.
SELECT ENAME, SAL FROM EMP WHERE SAL>1000;
ENAME SAL
Allen 1600
Ward 1250
Jones 2975
Martin 1250
Blake 2850
Clark 2450
Scott 3000
King 5000
Turner 1500
Ford 3000
10 r ows i n set .
-
8/2/2019 Sem 3: DBMS Practical File
8/34
(iii) List the Employee number and Name of the Managers.
SELECT EMPNO, ENAME FROM EMP WHERE JOB LI KE ' Manager ' ;
EMPNO ENAME
7566 Jones7698 Blake7782 Clark
3 r ows i n se t .
(iv) List the name of the clerk working in department number 20.
SELECT ENAME FROM EMP WHERE JOB LI KE ' Cl er k ' AND DEPTNO=20;
ENAME
Smith
1 r ow i n se t .
-
8/2/2019 Sem 3: DBMS Practical File
9/34
(v) List details of employees who have joined before the end of September 81.
SELECT * FROM EMP WHERE HI REDATE < ' 30- SEP- 81' ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 Smith Clerk 7902 17-DEC-80 800 20
7499 Allen Salesman 7698 20-FEB-81 1600 300 30
7521 Ward Salesman 7698 22-FEB-81 1250 500 30
7566 Jones Manager 7839 02-APR-81 2975 20
7654 Martin Salesman 7698 28-SEP-81 1250 1400 30
7698 Blake Manager 7839 01-MAY-81 2850 30
7782 Clark Manager 7839 09-JUN-81 2450 107844 Turner Salesman 7698 08-SEP-81 1500 0 30
8 r ows i n se t .
(vi) List the employee names of those who are not eligible for commission.
SELECT ENAME FROM EMP WHERE COMM I S NULL;
ENAME
Smith
Jones
Blake
Clark
Scott
King
AdamsFord
8 r ows i n se t .
-
8/2/2019 Sem 3: DBMS Practical File
10/34
(vii) List name of employees who are more than 2 years old in organization.
SELECT ENAME FROM EMP
WHERE TO_CHAR( SYSDATE, ' YYYY' ) - TO_CHAR( HI REDATE, ' YYYY' ) >2;
ENAME
Smith
Allen
Ward
Jones
Martin
Blake
ClarkScott
King
Turner
Adams
Ford
12 r ows sel ect ed.
-
8/2/2019 Sem 3: DBMS Practical File
11/34
(viii) List the total, maximum, minimum and average salary of employees, jobwise fordepartment number 20.
SELECT JOB, SUM( SAL) , MAX( SAL) , MI N( SAL) , AVG( SAL)
FROM EMP WHERE DEPTNO=20 GROUP BY JOB;
JOB SUM(SAL) MAX(SAL) MIN(SAL) AVG(SAL)
Analyst 6000 3000 3000 3000
Clerk 800 800 800 800
Manager 2975 2975 2975 2975
3 r ows i n se t .
(ix) List names of all employees who have ll and tt in their name.
SELECT ENAME FROM EMP WHERE ENAME
LI KE ' %t t %' OR ENAME LI KE ' %l l %' ;
ENAME
Allen
Scott
2 r ows i n se t .
-
8/2/2019 Sem 3: DBMS Practical File
12/34
(x) List lowest paid employee working for each manager. Sort the output by salary.
SELECT * FROM EMP WHERE SAL I N
( SELECT MI N( SAL) FROM EMP WHERE MGR I S NOT NULL GROUP BY MGR)
ORDER BY SAL;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 Smith Clerk 7902 17-DEC-80 800 20
7876 Adams Clerk 7788 03-DEC-81 950 30
7521 Ward Salesman 7698 22-FEB-81 1250 500 30
7654 Martin Salesman 7698 28-SEP-81 1250 1400 30
7782 Clark Manager 7839 09-JUN-81 2450 10
7788 Scott Analyst 7566 09-DEC-82 3000 20
7902 Ford Analyst 7566 04-DEC-81 3000 20
7 r ows sel ect ed.
-
8/2/2019 Sem 3: DBMS Practical File
13/34
Q2
(i) List the Job, No of employees in each job. The result should be in descendingorder of the number of employees.
SELECT JOB, COUNT( * ) FROM EMP
GROUP BY JOB ORDER BY COUNT( * ) DESC;
JOB COUNT(*)
Salesman 4
Manager 3
Analyst 2
Clerk 2
President 1
5 r ows sel ect ed.
(ii) List the average salary from each job excluding manager.
SELECT JOB, AVG( SAL) FROM EMP
WHERE JOB NOT LI KE ' Manager ' GROUP BY JOB;
JOB AVG(SAL)
Analyst 3000
Clerk 875
President 5000
Salesman 1400
4 r ows sel ect ed.
-
8/2/2019 Sem 3: DBMS Practical File
14/34
Q3
(i) Display item name, number and Qoh of all items where Qoh is more than 100.
SELECT NAME, I TNO, QOH FROM I TEM_MASTER WHERE QOH>100;
NAME ITNO QOH
Hammer 1090 234
Saw 1089 456
Lawn Mover 1088 123
Dish Washer 1087 234
Baking Oven 1067 145
Spark Plug 1063 150
Alternator 1609 168Battery 1890 189
Piston 1378 234
9 r ows sel ect ed.
-
8/2/2019 Sem 3: DBMS Practical File
15/34
(ii) List the items which have been issued more than 3 times.
SELECT I TNO, NAME FROM I TEM_MASTER WHERE I TNO I N( SELECT I TNO FROM TRANSACTI ON WHERE TYPE LI KE ' i ssue'
GROUP BY I TNO HAVI NG COUNT( I TNO) > 3 ) ;
ITNO NAME
1090 Hammer
-
8/2/2019 Sem 3: DBMS Practical File
16/34
(iii) List the item number, name and value of the cheapest item.
SELECT I TNO, NAME, RATE FROM I TEM_MASTER WHERE RATE I N( SELECT MI N( RATE) FROM I TEM_MASTER) ;
1 r ows sel ect ed.
ITNO NAME RATE
1378 Piston 250.78
-
8/2/2019 Sem 3: DBMS Practical File
17/34
(iv) List item
( S
name of an
SELECLECT I TN
GRO
item if and
NAME FRFROM TR
P BY I TN
Dish Washer
only if the
OM I TEM_ANSACTI O
HAVI NG
NAME
item was r
ASTER WHWHERE T
COUNT( I T
ceived mo
ERE I TNOYPE LI KE
O) > 5 )
e than 5 ti
I N' r ecei ve
;
es.
'
-
8/2/2019 Sem 3: DBMS Practical File
18/34
(v) List the value of items rounded to one decimal place.
SELECT ROUND( RATE, 1) FROM I TEM_MASTER;
ROUND(RATE,1)
400.9
800.9
5000.9
950.9
6000.7
750.6
750.1
300.9
250.8
9 r ows sel ect ed.
(vi) Display the first three letters of all employees.
SELECT SUBSTR( ENAME, 1, 3) FROM EMP;
SUBSTR(EN
Smi
All
War
Jon
Mar
Bla
Cla
Sco
KinTur
Ada
For
12 r ows sel ect ed.
-
8/2/2019 Sem 3: DBMS Practical File
19/34
(vii)
(viii)
SE
IT
1
List the
Display l
ECT * FR
O
088 Lawn
ifferent un
SELECT
st of items
M I TEM_
AME
Mover
t of measu
DI STI NCT
cs
att
olt
3 r o
of class C a
ASTER WH
QOH
123 C
1 r o
ements av
UOM FROM
UOM
s s el ec t ed
d QOH wh
RE CLASS
CLA U
pcs
w sel ect ed.
ilable in ta
I TEM_MA
.
ere rate is
LI KE C
M ROL
2
.
ble.
TER;
ore than 2
AND RAT
ROQ
1 21
000.
> 2000;
RATE
5000.8
-
8/2/2019 Sem 3: DBMS Practical File
20/34
(ix) List the details of all employees in department 10 and 20 in alphabetical order.
SELECT * FROM EMP WHERE DEPTNO I N ( 10, 20) ORDER BY ENAME ASC;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7782 Clark Manager 7839 09-JUN-81 2450 107902 Ford Analyst 7566 04-DEC-81 3000 207566 Jones Manager 7839 02-APR-81 2975 207839 King President 17-NOV-81 5000 107788 Scott Analyst 7566 09-DEC-82 3000 207369 Smith Clerk 7902 17-DEC-80 800 20
6 r ows sel ect ed.
(x) Increate the rate of class B by 12%
UPDATE I TEM_MASTER SET RATE = RATE + 0. 12* RATE WHERE CLASS LI KE ' B' ;
3 r ows updat ed.
SELECT * FROM I TEM_MASTER;
ITNO NAME QOH CLA UOM ROL ROQ RATE
1090 Hammer 234 A pcs 12 34 400.91089 Saw 456 B pcs 17 23 8971088 Lawn Mover 123 C pcs 21 21 5000.881087 Dish Washer 234 A pcs 76 45 950.871067 Baking Oven 145 A pcs 87 34 6000.67
1063 Spark Plug 150 C watt 34 67 750.631609 Alternator 168 B watt 50 56 840.11890 Battery 189 A volt 30 40 300.91378 Piston 234 B pcs 45 50 280.87
9 r ows sel ect ed.
-
8/2/2019 Sem 3: DBMS Practical File
21/34
(xi) List the rate of item which has at least 3 receipts.
SELECT RATE FROM I TEM_MASTER WHERE I TNO I N( SELECT I TNO FROM TRANSACTI ON
GROUP BY I TNO HAVI NG COUNT( RECEI PTNO) > 3 ) ;
RATE
950.87
400.9
-
8/2/2019 Sem 3: DBMS Practical File
22/34
(xii) Update the QOH of items to QOH + 100
UPDATE I TEM_MASTER SET QOH = QOH + 100;
9 r ows updat ed.
SELECT * FROM I TEM_MASTER;
ITNO NAME QOH CLA UOM ROL ROQ RATE
1090 Hammer 334 A pcs 12 34 400.91089 Saw 556 B pcs 17 23 8971088 Lawn Mover 223 C pcs 21 21 5000.881087 Dish Washer 334 A pcs 76 45 950.871067 Baking Oven 245 A pcs 87 34 6000.671063 Spark Plug 250 C watt 34 67 750.631609 Alternator 268 B watt 50 56 840.11890 Battery 289 A volt 30 40 300.91378 Piston 334 B pcs 45 50 280.87
9 r ows sel ect ed.
-
8/2/2019 Sem 3: DBMS Practical File
23/34
(xiii)
IT
1111
Delete d
months.
O
090 Hamm089 Saw087 Dish890 Batter
tails of ite
DELETE
( SELECT
SELECT
WHERE MO
S
NAME
er
asher
s that hav
FROM I TE
DI STI NC
DI STI NCT
THS_BETW
5 r o
LECT * F
QOH
234456234189
no transa
_MASTER
I TNO FR
MI NUS
I TNO FRO
EEN( SYSD
ws del et ed.
ROM I TEM
CLA U
A pcB pcA pcA vol
tions takin
HERE I T
OM I TEM_
M TRANSA
TE, DOT)
.
MASTER;
OM RO
t
g place in t
O I N
ASTER
TI ON
-
8/2/2019 Sem 3: DBMS Practical File
24/34
(xiv) List the items for which one transaction was made.
SELECT I TNO, NAME FROM I TEM_MASTER WHERE I TNO I N( SELECT I TNO FROM TRANSACTI ON
GROUP BY I TNO HAVI NG COUNT( * ) = 1 ) ;
ITNO NAME
1089 Saw
1609 Alternator
-
8/2/2019 Sem 3: DBMS Practical File
25/34
(xv) List the number of items belonging to each class, minimum, maximum, average rates
and total value of items in each class.
SELECT CLASS, COUNT( * ) , MI N( RATE) , MAX( RATE) , AVG( RATE) , SUM( RATE)FROM I TEM_MASTER GROUP BY CLASS;
CLA COUNT(*) MIN(RATE) MAX(RATE) AVG(RATE) SUM(RATE)
A 4 300.9 6000.67 1913.335 7653.34B 3 280.87 897 672.656667 2017.97C 2 750.63 5000.88 2875.755 5751.51
3 r ows sel ect ed.
(xvi) Create a new table having only the items of class B.
CREATE TABLE EMP_CLASS_B AS( SELECT * FROM I TEM_MASTER WHERE CLASS LI KE ' B' ) ;
Tabl e c r eat ed.
SELECT * FROM EMP_CLASS_B;
ITNO NAME QOH CLA UOM ROL ROQ RATE
1089 Saw 556 B pcs 17 23 897
1609 Alternator 268 B watt 50 56 840.11378 Piston 334 B pcs 45 50 280.87
3 r ows sel ect ed.
-
8/2/2019 Sem 3: DBMS Practical File
26/34
Q4
(i) List all employees names, jobs and deptno who have the same job as that of any
employee in department No: 20.
SELECT ENAME, JOB, DEPTNO FROM EMP_NM
WHERE JOB I N
( SELECT JOB FROM EMP_NM WHERE DEPTNO=20) ;
ENAME JOB DEPTNO
Adams Clerk 30
Smith Clerk 20
Clark Manager 10Blake Manager 30
Jones Manager 20
Ford Analyst 20
Scott Analyst 20
7 r ows sel ect ed.
(ii) Using Self Join, list all the employees who have joined before their manager.
SELECT E. EMPNO, E. ENAME FROM EMP_NM E, EMP_NM M
WHERE E. HI REDATE < M. HI REDATE AND E. MGR=M. EMPNO;
EMPNO ENAME
7521 Ward
7499 Allen
7782 Clark7698 Blake
7566 Jones
7369 Smith
6 r ows sel ect ed.
-
8/2/2019 Sem 3: DBMS Practical File
27/34
(iii) List all the employees who earn less than the average salary of all employees.
SELECT * FROM EMP_NM
WHERE SAL < ( SELECT AVG( SAL) FROM EMP_NM) ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 Smith Clerk 7902 17-DEC-80 800 207499 Allen Salesman 7698 20-FEB-81 1600 300 307521 Ward Salesman 7698 22-FEB-81 1250 500 307654 Martin Salesman 7698 28-SEP-81 1250 1400 307844 Turner Salesman 7698 08-SEP-81 1500 0 307876 Adams Clerk 7788 03-DEC-89 950 30
6 r ows sel ect ed.
(iv) List all the employee names along with their managers name. Also list the names
of those employeea who have no manager. (Outer Join)
SELECT W. ENAME AS EMPLOYEE, M. ENAME AS MANAGER
FROM EMP_NM W, EMP_NM MWHERE W. MGR=M. EMPNO( +) ;
EMPLOYEE MANAGER
Ford Jones
Scott Jones
Turner Blake
Martin Blake
Ward Blake
Allen BlakeAdams Scott
Clark King
Blake King
Jones King
Smith Ford
King
12 r ows sel ect ed.
-
8/2/2019 Sem 3: DBMS Practical File
28/34
(v) Display the Department which has no employees.
SELECT DEPTNO FROM DEPT_NM
WHERE DEPTNO NOT I N ( SELECT DI STI NCT DEPTNO FROM EMP_NM) ;
DEPTNO
40
1 r ow sel ect ed.
(vi) List details of employees who earn the minimum salary for their jobs.
SELECT * FROM EMP_NM X
WHERE SAL = ( SELECT MI N( SAL) FROM EMP_NM Y WHERE X. JOB= Y. JOB)
ORDER BY J OB;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7788 Scott Analyst 7566 09-DEC-82 3000 207902 Ford Analyst 7566 04-DEC-81 3000 207369 Smith Clerk 7902 17-DEC-80 800 207782 Clark Manager 7839 09-JUN-81 2450 107839 King President 17-NOV-81 5000 107521 Ward Salesman 7698 22-FEB-81 1250 500 307654 Martin Salesman 7698 28-SEP-81 1250 1400 30
7 r ows sel ect ed.
-
8/2/2019 Sem 3: DBMS Practical File
29/34
(vii) List the employee name, salary, department No for those employees who earn a
salary greater than average salary for their department. Show the output in
order of department no.
SELECT ENAME, SAL, DEPTNO FROM EMP_NM X
WHERE SAL > ( SELECT AVG( SAL) FROM EMP_NM Y WHERE X. DEPTNO = Y. DEPTNO)
ORDER BY DEPTNO;
ENAME SAL DEPTNO
King 5000 10
Jones 2975 20
Ford 3000 20
Scott 3000 20
Allen 1600 30
Blake 2850 30
6 r ows sel ect ed.
(viii) List details of employees who earn the highest salary for their job.
SELECT * FROM EMP_NM
WHERE JOB I N ( SELECT DI STI NCT JOB FROM EMP_NM)
AND SAL I N ( SELECT MAX( SAL) FROM EMP_NM GROUP BY JOB) ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7876 Adams Clerk 7788 03-DEC-89 950 307499 Allen Salesman 7698 20-FEB-81 1600 300 307566 Jones Manager 7839 02-APR-81 2975 207902 Ford Analyst 7566 04-DEC-81 3000 20
7788 Scott Analyst 7566 09-DEC-82 3000 207839 King President 17-NOV-81 5000 10
6 r ows sel ect ed.
-
8/2/2019 Sem 3: DBMS Practical File
30/34
(ix) List the details of those employees who are among the five highest earners of
this company. (TOP N QUERY)
SELECT *
FROM ( SELECT * FROM EMP_NM ORDER BY SAL DESC)
WHERE ROWNUM ANY ( SELECT RATE FROM I TEM_MASTER WHERE CLASS=' B' ) ;
ITNO NAME
1067 Baking Oven
1088 Lawn Mover
1087 Dish Washer
1089 Saw
1063 Spark Plug1609 Alternator
1090 Hammer
1890 Battery
8 r ows sel ect ed.
-
8/2/2019 Sem 3: DBMS Practical File
31/34
Q5
(i) Perform a query using CEIL and FLOOR. (Arithmetic Functions)
S EL E CT CE I L ( 7 . 3 ) , F L OOR( 7 . 3 ) F ROM DUA L ;
CEIL(7.3) FLOOR(7.3)
8 7
(ii) Perform a query using ROUND and TRUNC.
S E L ECT ROUND( 7 . 3 2 6 , 2 ) , T RUNC( 7 . 3 2 6 , 2 ) F ROM DUA L ;
ROUND(7.326,2) TRUNC(7.326,2)
7.33 7.32
(iii) Perform a query using MOD.
S E L E CT MOD( 1 2 , 5 ) F R OM D UA L ;
MOD(12,5)
2
-
8/2/2019 Sem 3: DBMS Practical File
32/34
(iv) Perform a query using RPAD (String Functions)
S E L ECT RP A D( ' He l l o ' , 7 , ' ! ' ) F ROM DUA L ;
RPAD('HELLO',7,'!')
Hello!!
(v) Perform a query using LTRIM and RTRIM.
S E L ECT RT RI M ( L T RI M( ' MMMA GNE T S S ' , ' M' ) , ' S ' ) F ROM DUA L ;
RTRIM(LTRIM('MM
AGNET
(vi) Perform a query using TRANSLATE.
S E L ECT DNA ME , T RA NS L AT E( DNA ME , ' a i e o u ' , ' u o e i ' ) F ROM DE PT _ NM;
TRANSLATE(DNAME,'AIEOU','UOEI')
Accounting AccintongResearch ReseurchSales SulesOperations Operutoins
-
8/2/2019 Sem 3: DBMS Practical File
33/34
(vii)
(viii)
Perform
S E L E
Perform
S E L E C T T O
a query usi
C T N E X T _
26-OCT
a query usi
_ CHA R( T
Tuesd
g NEXT_D
A Y ( ' 2 5 - O
NEXT_
-11
g TO_CHA
_ DA T E ( ' 2
O_CHAR(T
y
Y. (Date F
CT - 2 0 1 1 ' ,
DAY('25-OC
and TO_D
5 - OCT - 2 0 1
_DATE('25-
nctions)
' WE D' )
T-2
ATE.
1 ' ) , ' D
OCT-201
FROM DUAL
y ' ) F ROM
;
D U A L ;
-
8/2/2019 Sem 3: DBMS Practical File
34/34
(ix) Perform a query using NVL. (General Functions)
S E L E C T E MP NO, S A L , C OMM, S A L + C OMM, S A L + N VL ( C OMM, 0 ) F R OM E MP _ N M;
EMPNO SAL COMM SAL+COMM SAL+NVL(COMM,0)
7369 800 8007499 1600 300 1900 19007521 1250 500 1750 17507566 2975 29757654 1250 1400 2650 26507698 2850 28507782 2450 24507788 3000 3000
7839 5000 50007844 1500 0 1500 15007876 950 9507902 3000 3000
1 2 r o ws s e l e c t e d .
(x) Perform a query using DECODE.
S E L ECT NA ME , DE CODE ( NA ME , ' Ha mme r ' , ' Ma l l e t ' , ' S a w' , ' A x e ' ,' B a t t e r y ' , ' Ge n e r a t o r , Un a v a i l a b l e ' ) F ROM I T E M_ MA S T E R;
NAME DECODE(NAME,'HAMMER','MALLET','SA
Hammer MalletSaw AxeLawn Mover UnavailableDish Washer UnavailableBaking Oven UnavailableSpark Plug Unavailable
Alternator UnavailableBattery GeneratorPiston Unavailable
9 r o ws s e l e c t e d .