Plan du site  
français  English
pixel
pixel

Articles - Étudiants SUPINFO

Lab 03 - Functions in SQL

Single Row Functions

1.1.

Write a query to display the current date. Label this column Date.

SELECT sysdate "Date"
FROM   dual;

1.2.

The HR department needs a report to display the employee number, last name, salary, and salary increased by 15.5% (expressed as whole number) for each employee. Label the column “New Salary”. Place your SQL statement in a text file named lab_03_01.sql.

SELECT employee_id, last_name, salary,
       ROUND(salary * 1.155, 0) "New Salary"
FROM   employees;

1.3.

Modify your previous script to add a column that subtracts the old salary form the new salary. Label the column Increase. Save as lab_03_02.sql.

SELECT employee_id, last_name, salary,
       ROUND(salary * 1.155, 0) "New Salary"
       ROUND(salary * 1.155, 0) – salary "Increase"
FROM   employees;

1.4.

Write a query that displays the last name (with the first letter uppercased and all other letters lowercased) and the length of the last name for all employees whose name starts with the letter J, A, or M. Give each column an appropriate label. Sort by employees’ last names.

SELECT   INITCAP(lastname), LENGTH(last_name)
FROM     employees
WHERE    SUBSTR(last_name, 0, 1) IN ('J','M','A')
ORDER BY last_name;

1.5.

Rewrite the query so that the user is prompted to enter a letter that starts the last name. For example, if the user enters H when prompted for a letter, then the output should show all employees whose last name starts with the letter H.

SELECT   INITCAP(last_name), LENGTH(last_name)
FROM     employees
WHERE    last_name LIKE '&start_letter%'
ORDER BY last_name;

1.6.

The HR dept’ wants to find the length of employment for each employee. Display the last name and calculate the number of months between today and the date on which the employee was hired. Order by the number of months worked. Round up to a whole number.

SELECT   last_name, ROUND(MONTHS_BETWEEN(SYSDATE, hire_date))
FROM     employees
ORDER BY 2;

1.7.

Create a report that produces the following for each employee: <employee last name> earns <salary> monthly but wants <3 times salary>. Label the column “Dream Salaries”.

SELECT last_name ||' earns '|| TO_CHAR(salary, 'fm$99,999.00') 
       || ' monthly but wants '|| TO_CHAR(salary * 3, 'fm$99,999.00')
FROM   employees;

1.8.

Create a query to display the last name and salary for all employees. Format the salary to be 15 characters long, left-padded with $ symbol. Label the column.

SELECT last_name, LPAD(salary, 15, '$') SALARY
FROM   employees;

1.9.

Display each employee’s last name, hire date, and salary review date, which is the first Monday after six months of service. Label the column REVIEW. Format the dates to appear in the format similar to “Monday, the Thirty-First of July, 2000.”.

SELECT last_name, hire_date,
       TO_CHAR(NEXT_DAY(ADD_MONTHS(hire_date, 6),'MONDAY'),
       'fmDay, "the" Ddspth "of" Month, YYYY') REVIEW
FROM   employees;

1.10.

Display the last name, hire date, and day of the week on which the employee started. Label the column DAY. Order the results by the day of the week, starting with Monday.

SELECT   last_name, hire_date,
         TO_CHAR(hire_date, 'DAY') DAY
FROM     employees
ORDER BY TO_CHAR(hire_date-1, 'd');

1.11.

Create a query that displays the employees’ last names and commission amounts. If an employee does not earn commission, show “No Commission”. Label the column COMM.

SELECT last_name,
       NVL(TO_CHAR(commission_pct), 'No Commission')
FROM   employees;

1.12.

Create a query that displays the first eight characters of the employees’ last names and indicates the amounts of their salaries with asterisks. Each asterisk signifies a thousand dollars. Sort the data in descending order of salary. Label the column EMPLOYEES_SALARY.

SELECT   RPAD(last_name, 8) ||' '||
         RPAD(' ', salary/1000+1, '*') EMPLOYEES_SALARY
FROM     employees
ORDER BY salary DESC;

1.13.

Using the CASE function, write a query that displays the grade of all employees based on the value of the column JOB_ID, using the following data:

SELECT job_id, CASE job_id
       WHEN 'ST_CLERK' THEN 'E'
       WHEN 'SA_REP' THEN 'D'
       WHEN 'IT_PROG' THEN 'C'
       WHEN 'ST_MAN' THEN 'B'
       WHEN 'AD_PRES' THEN 'A'
       ELSE '0' END GRADE
FROM   employees;

1.14.

Rewrite the statement in the preceding exercise using the CASE syntax.

SELECT job_id, DECODE(job_id,
       'ST_CLERK', 'E',
       'SA_REP', 'D',
       'IT_PROG', 'C',
       'ST_MAN', 'B',
       'AD_PRES', 'A',
       '0') GRADE
FROM   employees;;

Group functions

1.1.

Does a group function work across many rows to produce one result per group?

TRUE

1.2.

Does a group function include nulls in calculations?

FALSE

1.3.

Does the WHERE clause restricts rows prior to inclusion in a group calculation?

TRUE

1.4.

Find the highest, lowest, sum, and average salary of all employees. Label the columns Maximum, Minimum, Sum, and Average, respectively. Round your results to the nearest whole number. Save your SQL statement in lab_03_03.sql.

SELECT ROUND(MAX(salary), 0) "Maximum",
       ROUND(MIN(salary), 0) "Minimum",
       ROUND(SUM(salary), 0) "Sum",
       ROUND(AVG(salary), 0) "Average"
FROM   employees;

1.5.

Modify your script to display the minimum, maximum, sum, and average salary for each job type. Save your script as lab_03_04.sql.

SELECT   job_id, ROUND(MAX(salary), 0) "Maximum",
         ROUND(MIN(salary), 0) "Minimum",
         ROUND(SUM(salary), 0) "Sum",
         ROUND(AVG(salary), 0) "Average"
FROM     employees
GROUP BY job_id;

1.6.

Write a query to display the number of people with the same job.

SELECT   job_id, COUNT(*)
FROM     employees
GROUP BY job_id;

1.7.

Generalize the query so that the user in the HR department is prompted for a job title. Save the script in a file lab_03_05.sql.

SELECT   job_id, COUNT(*)
FROM     employees
WHERE    job_id = '&job_title'
GROUP BY job_id;

1.8.

Determine the number of managers without listing them. Label the column “Number of Managers”. Use the manager_id column to determine the number of managers.

SELECT COUNT(DISTINCT manager_id)
FROM   employees;

1.9.

Find the difference between the highest and lowest salaries. Label the column DIFFERENCE.

SELECT MAX(salary)-MIN(salary) DIFFERENCE
FROM   employees;

1.10.

Create a report to display the manager number and the salary of the lowest-paid employee for that manager. Exclude anyone whose manager is not known. Exclude any groups where the minimum salary is $6,000 or less. Sort the output in descending order of salary.

SELECT   manager_id, MIN(salary)
FROM     employees
WHERE    manager_id IS NOT NULL
GROUP BY manager_id
HAVING   MIN(salary) > 6000
ORDER BY MIN(salary) DESC;

1.11.

Create a query that will display the total number of employees and, of that total, the number of employees hired in 1995, 1996, 1997, and 1998. Create appropriate column headings.

SELECT COUNT(*) total,
       SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 1995,1,0)) "1995",
       SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 1996,1,0)) "1996",
       SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 1997,1,0)) "1997",
       SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 1998,1,0)) "1998"
FROM   employees;

1.12.

Create a matrix query to display the job, the salary for that job based on department number, and the total salary for that job, for departments 20, 50, 80, and 90, giving each column an appropriate heading. The required result must correspond to the following table:

SELECT   job_id "Job",
         SUM(DECODE(department_id, 20, salary)) "Dept 20",
         SUM(DECODE(department_id, 50, salary)) "Dept 50",
         SUM(DECODE(department_id, 80, salary)) "Dept 80",
         SUM(DECODE(department_id, 90, salary)) "Dept 90",
         SUM(salary) "Total"
FROM     employees
GROUP BY job_id;
About SUPINFO | Contacts & addresses | Teachers | Press | INVESTOR | Conditions of Use & Copyright | Respect of Privacy
Logo de la société Cisco Logo de la société IBM Logo de la société Sun-Oracle Logo de la société Apple Logo de la société Sybase Logo de la société Novell Logo de la société Intel Logo de la société Accenture Logo de la société SAP Logo de la société Prometric Logo du IT Academy Program par Microsoft

SUPINFO International University is globally operated by EDUCINVEST Belgium - Avenue Louise, 534 - 1050 Brussels
and is accredited in France by Association Ecole Supérieure d'Informatique de Paris (ESI SUPINFO)