[go: up one dir, main page]
Scribd
Upload
103 views4 pages

Acuña, Lance IT6202 LAB - 007

This document provides instructions for a database management systems laboratory exercise involving writing PL/SQL queries on an Employees table. It includes 9 queries to write involving aggregation functions like COUNT, MIN, MAX, SUM, and AVG. It asks the student to group results by job_id, filter on conditions, concatenate columns, and rename columns. The goal is to practice single row functions and writing queries with aggregation.

Uploaded by

Anime Lover
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
103 views4 pages

Acuña, Lance IT6202 LAB - 007

This document provides instructions for a database management systems laboratory exercise involving writing PL/SQL queries on an Employees table. It includes 9 queries to write involving aggregation functions like COUNT, MIN, MAX, SUM, and AVG. It asks the student to group results by job_id, filter on conditions, concatenate columns, and rename columns. The goal is to practice single row functions and writing queries with aggregation.

Uploaded by

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

Course Code Type Course Code Here

Database Management System


Description
1
College / Department:
LabExer No. 007
Online Education
Laboratory Exercise Page 1 of 1

Direction:
 Use the Employees table (copy and paste the code in SQL
command line)
 Based on the given table: Employees table as shown below, write the PL/SQL that applies single row
function in order to get the printed output per number. Write your answer in a short bond paper.
1. Create a query that display the MINIMUM salary of employees per job_id. Note job_id shoulb
be in lowercase.
Possible Output:
LOWEST SALARY JOB_ID
------------- ----------
4200 it_prog
12000 ac_mgr
.
.
12 rows selected
SQL> select lower(job_id), min(salary) from employees group by
job_id;

LOWER(JOB_ MIN(SALARY)
---------- -----------
it_prog 4200
ac_mgr 12000
ac_account 8300
st_man 5800
ad_asst 4400
ad_vp 17000
sa_man 10500
mk_man 13000
ad_pres 24000
sa_rep 7000
mk_rep 6000

LOWER(JOB_ MIN(SALARY)
---------- -----------
st_clerk 2500

12 rows selected.
2. Create a query that counts the number of ST_CLERK job_id. Rename the column as “Total no.
of ST_CLERK”.
Possible Output:
Total no. of ST_CLERK
---------------------
4
SQL> SELECT COUNT(JOB_ID) AS "TOTAL no. OF ST.CLERK" FROM EMPLOYEES
WHERE JOB_ID = 'ST_CLERK';

TOTAL no. OF ST.CLERK


---------------------
4
3. Create a report that display distinct job_id and the the total number per distinct (unique) job_id.
Possible Output:
JOB_ID Total no. of Job_id
---------- -------------------
IT_PROG 3
AC_MGR 1
.
.
12 rows selected
SQL> SELECT JOB_ID, COUNT(*) AS "TOTAL NO. OF JOB ID" FROM EMPLOYEES
GROUP BY JOB_ID;

JOB_ID TOTAL NO. OF JOB ID


---------- -------------------
IT_PROG 3
AC_MGR 1
AC_ACCOUNT 1
ST_MAN 1
AD_ASST 1
AD_VP 2
SA_MAN 1
MK_MAN 1
AD_PRES 1
SA_REP 3
MK_REP 1

JOB_ID TOTAL NO. OF JOB ID


---------- -------------------
ST_CLERK 4

4. Create a query that display the Job_id and add the ff: function:
 Count the total number of job_id per distinct values; Compute for the summary of salary
per job_id; and Compute for the average salary per job_id
Possible Output:
JOB_ID No. of Job_id Total Salary Average Salary
---------- ------------- ------------ --------------
IT_PROG 3 10200 5100
AC_MGR 1 12000 12000
AC_ACCOUNT 1 8300 8300
SQL> SELECT JOB_ID, COUNT(JOB_ID) AS "TOTAL NO. OF JOB ID",
SUM(SALARY), AVG(SALARY) FROM EMPLOYEES WHERE JOB_ID IN
('IT_PROG','AC_MGR','AC_ACCOUNT') GROUP BY JOB_ID;

JOB_ID TOTAL NO. OF JOB ID SUM(SALARY) AVG(SALARY)


---------- ------------------- ----------- -----------
IT_PROG 3 10200 5100
AC_MGR 1 12000 12000
AC_ACCOUNT 1 8300 8300

5. Create a query that displays the lowest salary rename as “LOWEST SALARY”, maximum
salary rename as “HIGHEST SALARY” and department_id concatenated to job_id.
Possible Output:
LOWEST SALARY HIGHEST SALARY Record
------------- -------------- ----------------------------
4200 6000 60 With Job_Id Of It_Prog
5800 5800 50 With Job_Id Of St_Man
.
.
14 rows selected

6. Create a report that display the smallest (minimum) Surname.. and apply the ff. functions:
 Get the average salary; Group the data per job_id; Get only job_id with a keyword
“REP”; and Apply having clause, which the max salary per job_id is greater than 5000.
7. Create a query that display the Minimum firstname concatenated to lastname. Look for the
maximum salary per department_id. Note that pad function is applied for the function
maximum.
Possible Output:
Complete Name Maximum Salary DEPARTMENT_ID
--------------------- -------------------------------- -------------
KIMBERLEY GRANT $$$$7000
PAT FAY $$$$6000 20
.
.
7 rows selected
8. Create a query that display the maximum lastname concatenated to firstname and rename the
column as “Employees Name”, Job_id and apply the ff. functions:
 Count for the distinct Job_id; Apply where condition whose lastname ends with letter ‘N’;
Group the job_id; and Apply having clause of employees having average salary that is
greater that 10000.
Possible Output
Employees Name JOB_ID COUNT(JOB_ID) JOB_ID
--------------------- ---------- ------------- ----------
De Haan,Lex AD_VP 1 AD_VP
Hartstein,Michael MK_MAN 1 MK_MAN
9. What is the main goal of using GROUP BY clause?
The main goal of the statement group by is to be able to know which information are similar in
terms of there information in the database. With the help of the group by cause we would be
able to analyze the information much quicker and it would be much more faster to look for the
things we needed.
10. What is the difference between where condition and HAVING clause?
The difference between the where condition and having clause is that the where
condition look for something in a group while on the other the having clause exclude or include
a group function.

You might also like