Plan du site  
français  English
pixel
pixel

Articles - Étudiants SUPINFO

SELECT statement

1.1.

Does iSQL*Plus commands access the database?

FALSE

1.2.

Does the following SELECT statement execute successfully?

SELECT last_name, job_id, salary AS Sal
FROM   employees;

TRUE

1.3.

Does the following SELECT statement execute successfully?

SELECT *
FROM   job_grades;

TRUE

1.4.

There are four coding mistakes in this statement:

SELECT employee_id, last_name sal x 12 ANNUAL SALARY
FROM   employees;
  • The EMPLOYEES table does not contain a column called sal

  • the multiplication operator is “*”, not “x”

  • the “ANNUAL SALARY” alias cannot include spaces. The alias should be enclosed in double quotation marks

  • a comma is missing after the LAST_NAME column

1.5.

You have to determine the structure of the DEPARTMENTS table and its content.

DESCRIBE departments

SELECT *
FROM   departments;

1.6.

Then, you need to determine the structure of the EMPLOYEES table.

DESC employees

1.7.

The HR department wants a query to display the last name, job code, hire date, and employee number for each employee, with employee number appearing first. Provide an alias STARTDATE for the hire_date column. Save your SQL statement to a file named lab_02_01.sql so that you can disperse this file to the HR department.

SELECT employee_id, last_name, job_id,hire_date STARTDATE
FROM   employees;

SAVE C:\labs\lab_01_01.sql

1.8.

The HR department needs a query to display all unique job codes from the EMPLOYEES table.

SELECT DISTINCT job_id
FROM   employees;

1.9.

The HR department wants more descriptive column headings for its report on employees. Get the statement from lab_02_01.sql in iSQL*Plus. Name the column headings Emp #, Employee and Title (case-sensitive).

GET C:\labs\lab_01_01.sql

SELECT employee_id "Emp #",
       last_name "Employee", job_id "Job",
       hire_date "Hire Date"
FROM   employees;

1.10.

The HR department has requested a report of all employees and their job IDs. Display the last name concatenated with the job ID (separated by comma and space) and name the column “Employee and Title”.

SELECT last_name||' ,'||job_id "Employee and Title"
FROM   employees;

1.11.

To familiarize yourself with the data in the EMPLOYEES table, create a query to display all the data from the EMPLOYEES table. Separate each column output by a comma. Name the column title THE_OUTPUT.

SELECT employee_id ||', '||
       first_name ||', '||
       last_name ||', '||
       email ||', '||
       phone_number ||', '||
       job_id ||', '||
       manager_id ||', '||
       hire_date ||', '||
       salary ||', '||
       commission_pct ||', '||
       department_id THE_OUTPUT
FROM   employees;

Restricting and sorting data

1.1.

Due to budget issues, the HR department needs a report that displays the last name and salary of employees earning more that $12,000. Save your statement in lab_02_02.sql.

SELECT last_name, salary
FROM   employees
WHERE  salary > 12000;

1.2.

Create a report that displays the last name and department number for employee number 176.

SELECT last_name, department_id
FROM   employees
WHERE  employee_id = 176;

1.3.

The HR departments needs to find high-salary and low-salary employees. Modify lab_02_02.sql to display the last name and salary for all employees whose salary is not in the range of $5,000 to $12,000. Save your statement in lab_01_03.sql.

SELECT last_name, salary
FROM   employees
WHERE  salary NOT BETWEEN 5000 AND 12000;

1.4.

Create a report to display the last name, job id, and start date for the employees with the last names of Matos and Taylor. Order the query in ascending order by start date.

SELECT   last_name, job_id, hire_date
FROM     employees
WHERE    last_name IN ('Matos', 'Taylor')
ORDER BY hire_date;

1.5.

The HR department needs your assistance with creating some queries. Display the last name and department number of all employees in department 20 or 50 in ascending alphabetical order by name.

SELECT   last_name, department_id
FROM     employees
WHERE    department_id IN (20, 50)
ORDER BY last_name ASC;

1.6.

Modify lab_02_03.sql to list the last name and salary of employees who earn between $5,000 and $12,000 and are in department 20 or 50. Label the columns “Employee” and “Monthly Salary”, respectively. Resave your script as lab_02_04.sql.

SELECT last_name "Employee", salary "Monthly Salary"
FROM   employees
WHERE  salary BETWEEN 5000 AND 12000
AND    department_id IN (20, 50);

1.7.

The HR department needs a report that displays the last name and hire date for all employees who were hired id 1994.

SELECT last_name, hire_date
FROM   employees
WHERE  hire_date LIKE '%94';

1.8.

Create a report to display the last name and job title of all employees who do not have a manager.

SELECT last_name, job_id
FROM   employees
WHERE  manager_id IS NULL;

1.9.

Display the last name, salary, and commission for all employees who earn commissions. Sort data in descending order of salary and commissions.

SELECT   last_name, salary, commission_pct
FROM     employees
WHERE    commission_pct IS NOT NULL
ORDER BY salary DESC, commission_pct DESC;

1.10.

Create a report that displays the last name and salary of employees who earn more than an amount that the user specifies prompt. Save the query in lab_02_05.sql.

SELECT last_name, salary
FROM   employees
WHERE  salary < &sal_amount;

1.11.

Display all employee last names in which the third letter of the name is “a”.

SELECT last_name
FROM   employees
WHERE  last_name LIKE '__a%';

1.12.

The HR department wants to run reports based on a manager. Create a query that prompts the user for a manager id and generates the employee id, last name, salary, and department for that manager’s employees. The HR department wants the ability to sort the report on a selected column. Test with the following values:

  • manager id = 103, sorted by employees’ last name

  • manager id = 201, sorted by salary

  • manager id = 124, sorted by employee id

SELECT   employee_id, last_name, salary, department_id
FROM     employees
WHERE    manager_id = &mgr_num
ORDER BY &order_col;

1.13.

Display the last name of all employees who have both an “a” and an “e” in their last name.

SELECT last_name
FROM   employees
WHERE  last_name LIKE '%a%'
AND    last_name LIKE '%e%';

1.14.

Display the last name, job, and salary for all employees whose job is sales representative (SA_REP) or stock clerk (ST_CLERK) and whose salary is not equal to $2,500, $3,500, or $7,000.

SELECT last_name, job_id, salary
FROM   employees
WHERE  job_id IN ('SA_REP', 'ST_CLERK')
AND    salary NOT IN (2500, 3500, 7000);

1.15.

Modify lab_02_05.sql to display the last name, salary, and commission for all employees whose commission amount is 20%. Save your statement in lab_02_06.sql.

SELECT last_name "Employee", salary "Salary", commission_pct
FROM   employees
WHERE  commission_pct = .20;
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)