Lesson Overview
In this lesson, students learn how to retrieve data from tables, choose specific columns, rename output columns, perform calculations, and filter results using different conditions.
SELECT
FROM
WHERE
Aliases
Math Operators
BETWEEN
IN
LIKE
IS NULL
AND / OR
Trainer
Mohamed Alswaify
Mobile: 0564842804
Basic Query Structure
SELECT * | column1, column2, ...
FROM table_name
[WHERE bool_expr AND | OR bool_expr]
Math Operators
*Multiplication/Division+Addition-Subtraction||Concatenation
Boolean Expressions
>, >=, <, <=, =, <>, !=[NOT] BETWEEN ... AND ...[NOT] IN (...)[NOT] LIKE '...'IS [NOT] NULL
Examples
Example 1
SELECT *
FROM employees;
Example 2
SELECT last_name, salary, hire_date, job_id
FROM employees;
Example 3
SELECT last_name, salary, hire_date, job_id, salary * 12 AS annual_salary
FROM employees;
Example 4 - Alias with Spaces
SELECT last_name name, salary, hire_date, job_id, salary * 12 AS "annual salary"
FROM employees;
Example 5 - Required HR Query
Display the employee ID first, then last name, job ID, and hire date with alias STARTDATE.
SELECT employee_id, last_name, job_id, hire_date AS STARTDATE
FROM employees;
Example 6 - Concatenation
SELECT first_name || ' ' || last_name AS full_name
FROM employees;
SELECT first_name || ' bin ' || last_name AS full_name
FROM employees;
Example 7 - Salary Filter
SELECT last_name, salary, hire_date, job_id
FROM employees
WHERE salary >= 15000;
Example 8 - Job Filter
SELECT last_name, salary, hire_date, job_id
FROM employees
WHERE job_id = 'IT_PROG';
Example 9 - BETWEEN
SELECT last_name, salary, hire_date, job_id
FROM employees
WHERE salary BETWEEN 2500 AND 7500;
Example 10 - Employee Name and Salary
SELECT first_name || ' ' || last_name AS "employee name", salary
FROM employees
WHERE salary BETWEEN 4000 AND 9000;
Example 11 - IN and NULL
SELECT last_name, salary, hire_date, job_id, department_id
FROM employees
WHERE department_id IN (30, 60, 90);
SELECT last_name, salary, hire_date, job_id, department_id
FROM employees
WHERE department_id IS NULL;
Example 12 - LIKE
SELECT last_name, salary, hire_date, job_id, department_id
FROM employees
WHERE last_name LIKE 'F%';
SELECT last_name, salary, hire_date, job_id, department_id
FROM employees
WHERE last_name LIKE '%z';
Example 13 - Commission is NULL
SELECT last_name, salary, hire_date, job_id, department_id
FROM employees
WHERE commission_pct IS NULL;
Example 14 - NOT IN
SELECT last_name, salary || '$', job_id
FROM employees
WHERE salary NOT IN (2500, 3500, 7000);
Example 15 - AND / OR
SELECT last_name, job_id, salary
FROM employees
WHERE job_id IN ('ST_CLERK', 'SA_REP')
AND salary NOT IN (2500, 3500, 7000);
Questions
Display the last name, job ID, hire date, and employee ID for each employee, with the employee ID appearing first. Provide the alias STARTDATE for the HIRE_DATE column.
Display the employee name and salary for all employees who have a salary greater than or equal to 4000 and less than or equal to 9000.
Display the employee name, job, and department for all employees who work in department 30, 50, or 70.
Display the employee name, department, and salary for all employees who have no commission (NULL).
Display the last name, job, and salary for all employees whose salary is not equal to 2500, 3500, or 7000.
Task
Write a query that displays the last name for all employees whose name starts with the letters J, A, or M.
Hint: Use
LIKE with OR, not IN.