Database Lab
LAB - 5
Database Systems Lab
Name: Muhammad salman
Reg. #: UW-23-SW-BS-023
Semester:2nd
Date: 05/04/2024
Submitted To: Engr Faiza Bukhari
Presentation/ Code Comment Response Total
Procedure s&
conclusion
1|Page
Database Lab
Task 1:
First I create schema and then table “emp” into the schema and then show all the
value.
1. Display ename, salary and concated result of deptno and empno
from table EMP where salary is less than 2500 or job is equal to
MANAGER.
SELECT Ename, Salary, CONCAT(Dept_no, EmpN0) AS
Dept_Emp_Concat
FROM EMP
WHERE Salary < 2500 OR Job = 'Manager';
Output:
2|Page
Database Lab
2. Display first 3 characters of ename, deptno and job from table
EMP where second character of ename is ‗A‘ and deptno is 30 or
job is SALESMAN.
SELECT SUBSTRING(Ename, 1, 3) AS First_Three_Chars,
SUBSTRING(Dept_no, 1, 3) AS Deptno, SUBSTRING(Job,
1, 3) AS Job
FROM EMP
WHERE SUBSTRING(Ename, 2, 1) = 'A' AND (Dept_no =
30 OR Job = 'Salesman');
Output:
3. Right Append dollar sign to make salary 8 digit value and
calculate remainder of annual salary from monthly salary and
rename it as REMAINDER where ename is KING and salary is
greater than 1500 from table EMP.
SELECT Ename, CONCAT(RIGHT(CONCAT(Salary, '$'),
8), '$') AS Salary, (Salary % 12) AS REMAINDER
FROM EMP
WHERE Ename = 'King' AND Salary > 1500;
Output:
4. Round 345.6665 upto 2 digits from table DUAL.
SELECT ROUND(345.6665, 2) AS Rounded_Value;
Output:
3|Page
Database Lab
5. Write a query that displays enames with first letter capitalized
and all other letters in lowercase and length of enames whose
enames start with J, A or N.
SELECT CONCAT(UPPER(SUBSTRING(Ename, 1, 1)),
LOWER(SUBSTRING(Ename, 2))) AS Capitalized_Ename,
LENGTH(Ename) AS Ename_Length
FROM EMP
WHERE SUBSTRING(Ename, 1, 1) IN ('J', 'A', 'N');
Output:
Task 2
Execute these queries and analyze the results.
1. Select ‗The job id for‘ || UPPER(ename) || ‗is‘ || LOWER (job) AS
―Employee Details‖ FROM emp;
Output:
2. Select empno, ename, deptno from emp where LOWER(ename)
= ‗smith‘;
Output:
3. Select empno, UPPER(ename), deptno from emp where
INITCAP(ename) = ‗Martin‘;
Output:
4. Select empno, CONCAT(ename,job), LENGTH(ename),
INSTR(ename, ‗a‘) from emp where SUBSTR (job,5) = ‗MAN‘;
Output:
5. Select empno, CONCAT(ename,job), LENGTH(ename),
INSTR(ename, ‗a‘) from emp where SUBSTR (ename,-1,1) =
‗S‘;
Output:
6. Select ROUND (45.698,2), ROUND (45.987,0), ROUND (45.369,-
1) from DUAL;
4|Page
Database Lab
Output:
7. Select TRUNC (67.765, 2), TRUNC(67.876), TRUNC(67.567, -2)
from DUAL;
Output:
8. Select ename, sal, MOD(sal, 5000) FROM emp WHERE job =
‗SALESMAN‘;
Output:
9. Select ename, hiredate from emp where ename LIKE ‗S%‘;
Output:
10. Select SYSDATE from DUAL;
Output:
11. Select aname, (SYSDATE – hiredate)/7 WEEKS from emp where
deptno = 10;
Output:
12. Select empno, hiredate, MONTHS_BETWEEN( SYSDATE,
hiredate)
Tenure,ADD_MONTHS(hiredate, 6) Review, NEXT_DAY (hiredate,
‗FRIDAY‘),
LAST_DAY(hiredate) from emp where
MONTHS_BETWEEN(SYSDATE, hiredate) >
36;
Output:
13. Select empno, hiredate, ROUND (hiredate, ‗MONTH‘), TRUNC
(hiredate, ‗MONTH‘) from emp WHERE hiredate LIKE ‗%97‘;
5|Page
Database Lab
Comments;
In this lab we learned that;
Conclusion;
__________________END________________
6|Page
