Practice SQL SELECT Statements | HR

This page provides exercises and solutions to help you practice SQL SELECT statements. These exercises are based on the HR database, and may be performed online or by running the sample schema scripts on your local database server. For additional exercises in other subjects, use this link.
 

Basic SELECT statements – Exercises

1. Create a query to display all the data from the Employees table.
2. The following SELECT statement executes successfully (True / False)

FROM employees
SELECT last_name, first_name

3. Create a query to display the department number, department name, and manager number. Name the last column (manager number) heading as “MNG” (Employees table).
4. The following SELECT statement executes successfully (True / False)

SELECT department_name, department_name
FROM departments

5. The following SELECT statement executes successfully (True / False)

SeleCT last_NAME, fiRST_NamE, FROM Employees

6. Create a query to display the employee number, first name, last name, phone number and department number (Employees table).
7. Create a query to display the first name, last name, hire date, salary, and salary after a raise of 20%. Name the last column (salary after a raise) heading as “ANNUAL_SAL” (Employees table).
8. Create a query to display the last name concatenated with the first name, separated by space, and the telephone number concatenated with the email address, separated by hyphen. Name the column headings “FULL_NAME” and “CONTACT_DETAILS” respectively (Employees tables).
9. Create a query to display the unique manager numbers from Employees table.
10. Create a query to display the last name concatenated with job_id column, separated by space. Name this column heading as “EMPLOYEE_AND_TITLE” (Employees table).
11. Create a query to display the first name, last name, salary, and hire date concatenated with the literal string “HD”, separated by space. Name the column headings “FN”, “LN”, “SAL”, and “HD” respectively (Employees table).
12. Create a query to display the unique salaries in Employees tables.
13. Create a query to display the unique combination of values in department_id and job_id columns (Employees table).

-- 1  
SELECT  *
FROM   employees 
-- 2
FROM employees 
SELECT last_name , first_name 
-- Error, invalid SQL Statement
-- 3
SELECT department_name , department_id , manager_id AS "MNG"
FROM  departments 
-- 4
SELECT department_name , department_name 
FROM   departments 
-- Valid SQL Statement
-- 5
SeleCT        last_NAME , fiRST_NamE , FROM Employees
-- Error, invalid SQL Statement
-- 6
SELECT employee_id , first_name , last_name , phone_number , department_id 
FROM   employees 
-- 7
SELECT first_name , last_name , hire_date , salary , 
       salary * 12 AS "ANNUAL_SAL"
FROM employees 
-- 8
SELECT first_name || ' ' || last_name AS "FULL_NAME" , 
       phone_number || ' - ' || email AS "CONTACT_DETAILS"
FROM employees 
-- 9
SELECT DISTINCT manager_id 
FROM employees 
-- 10
SELECT last_name || ' ' || job_id AS "EMPLOYEE_AND_TITLE"
FROM employees 
-- 11
SELECT first_name AS "FN" , last_name AS "LN" , salary AS "SAL" , 
       'HD : '|| hire_date AS "HD"
FROM employees 
-- 12
SELECT DISTINCT salary 
FROM employees
-- 13
SELECT DISTINCT department_id , job_id  
FROM employees

-- 1  
SELECT  *
FROM   employees 
-- 2
FROM employees 
SELECT last_name , first_name 
-- Error, invalid SQL Statement
-- 3
SELECT department_name , department_id , manager_id AS 'MNG'
FROM  departments 
-- 4
SELECT department_name , department_name 
FROM   departments 
-- Valid SQL Statement
-- 5
SeleCT        last_NAME , fiRST_NamE , FROM Employees
-- Error, invalid SQL Statement
-- 6
SELECT employee_id , first_name , last_name , phone_number , department_id 
FROM   employees 
-- 7
SELECT first_name , last_name , hire_date , salary , 
       salary * 12 AS 'ANNUAL_SAL'
FROM employees 
-- 8
SELECT first_name + ' ' + last_name AS 'FULL_NAME' , 
       phone_number + ' - ' + email AS 'CONTACT_DETAILS'
FROM employees 
-- 9
SELECT DISTINCT manager_id 
FROM employees 
-- 10
SELECT last_name + ' ' + CAST(job_id AS VARCHAR) AS 'EMPLOYEE_AND_TITLE'
FROM employees 
-- 11
-- In MSSQL, in order to concatenate string with date / number, everything must be converted into string.
-- One way to preform this conversion is using the CAST function.
SELECT first_name AS 'FN' , last_name AS 'LN' , salary AS 'SAL' , 
       'HD : '+ CAST(hire_date AS VARCHAR) AS 'HD'
FROM employees 
-- 12
SELECT DISTINCT salary 
FROM employees
-- 13
SELECT DISTINCT department_id , job_id  
FROM employees