DATABASE EXPERT

🗄️
DBA-DEV TOOLKIT
👨‍💻 Vinayak Vishweshwara Dabgar
⚙️ Oracle Apps DBA - Consultant
💻 DBA - Developer
🏗️ Database Architect
🚀 Performance Tuning Expert
🗃️ Database Expertise
🟢 Oracle Database
🔵 Microsoft SQL Server
🟠 MySQL
🐘 PostgreSQL
☁️ Amazon RDS
📊 Amazon Redshift
🔗 Professional Links
🌐 Professional Website 🎓 Corporate Training LMS 📧 vdabgar.in@gmail.com 💼 LinkedIn Profile
📍 Office Address
🏠 No.20, SAI SADAN
🏢 Near LVS Apartments
🛣️ TC Palya Main Road
📌 Krishnarajapura
🌆 Bangalore - 560036
🌆 Karnataka, India
⬅ Back
Oracle DQL 01 - SQL Fundamentals


/*===============================================================
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert

PART 01A – SQL FUNDAMENTALS

Topics Covered
--------------
• SQL Environment
• SELECT Statement
• Column Aliases
• Expressions
• DISTINCT
• ORDER BY
• FETCH FIRST
• HR Schema Exploration

Schema Used
-----------
HR

Compatible Versions
-------------------
Oracle 11g
Oracle 12c
Oracle 19c
Oracle 21c
Oracle 23ai
Oracle 26ai
===============================================================*/


/*---------------------------------------------------------------
SECTION 1
VERIFY HR SCHEMA TABLES
---------------------------------------------------------------*/

SELECT table_name
FROM all_tables
WHERE owner = 'HR'
ORDER BY table_name;



/*---------------------------------------------------------------
QUESTION 1
Display all employees
---------------------------------------------------------------*/

SELECT *
FROM hr.employees;

/*
Explanation

This query retrieves all columns and rows
from the HR employees table.
*/



/*---------------------------------------------------------------
QUESTION 2
Display employee id and name
---------------------------------------------------------------*/

SELECT
employee_id,
first_name,
last_name
FROM hr.employees;



/*---------------------------------------------------------------
QUESTION 3
Display employee salary
---------------------------------------------------------------*/

SELECT
employee_id,
salary
FROM hr.employees;



/*---------------------------------------------------------------
QUESTION 4
Display employee details sorted by salary
---------------------------------------------------------------*/

SELECT
employee_id,
first_name,
salary
FROM hr.employees
ORDER BY salary DESC;



/*---------------------------------------------------------------
QUESTION 5
Display employees sorted by department
---------------------------------------------------------------*/

SELECT
employee_id,
first_name,
department_id
FROM hr.employees
ORDER BY department_id;



/*---------------------------------------------------------------
COLUMN ALIASES
---------------------------------------------------------------*/


/*---------------------------------------------------------------
QUESTION 6
Use alias for column names
---------------------------------------------------------------*/

SELECT
employee_id AS emp_id,
first_name AS employee_name
FROM hr.employees;



/*---------------------------------------------------------------
QUESTION 7
Alias with spaces
---------------------------------------------------------------*/

SELECT
employee_id "Employee ID",
salary "Monthly Salary"
FROM hr.employees;



/*---------------------------------------------------------------
STRING CONCATENATION
---------------------------------------------------------------*/


/*---------------------------------------------------------------
QUESTION 8
Create full employee name
---------------------------------------------------------------*/

SELECT
first_name || ' ' || last_name AS full_name
FROM hr.employees;



/*---------------------------------------------------------------
QUESTION 9
Generate employee email
---------------------------------------------------------------*/

SELECT
first_name || '.' || last_name || '@company.com' AS email
FROM hr.employees;



/*---------------------------------------------------------------
ARITHMETIC EXPRESSIONS
---------------------------------------------------------------*/


/*---------------------------------------------------------------
QUESTION 10
Calculate annual salary
---------------------------------------------------------------*/

SELECT
employee_id,
salary,
salary*12 AS annual_salary
FROM hr.employees;



/*---------------------------------------------------------------
QUESTION 11
Calculate salary after tax
---------------------------------------------------------------*/

SELECT
employee_id,
salary,
salary - (salary*0.30) AS salary_after_tax
FROM hr.employees;



/*---------------------------------------------------------------
QUESTION 12
Increase salary by 10 percent
---------------------------------------------------------------*/

SELECT
employee_id,
salary,
salary*1.10 AS increased_salary
FROM hr.employees;



/*---------------------------------------------------------------
DISTINCT CLAUSE
---------------------------------------------------------------*/


/*---------------------------------------------------------------
QUESTION 13
Display unique department ids
---------------------------------------------------------------*/

SELECT DISTINCT department_id
FROM hr.employees;



/*---------------------------------------------------------------
QUESTION 14
Display job ids
---------------------------------------------------------------*/

SELECT DISTINCT job_id
FROM hr.employees;



/*---------------------------------------------------------------
ORDER BY CLAUSE
---------------------------------------------------------------*/


/*---------------------------------------------------------------
QUESTION 15
Sort employees by last name
---------------------------------------------------------------*/

SELECT
employee_id,
last_name
FROM hr.employees
ORDER BY last_name;



/*---------------------------------------------------------------
QUESTION 16
Sort by salary descending
---------------------------------------------------------------*/

SELECT
employee_id,
salary
FROM hr.employees
ORDER BY salary DESC;



/*---------------------------------------------------------------
QUESTION 17
Sort using column position
---------------------------------------------------------------*/

SELECT
employee_id,
salary
FROM hr.employees
ORDER BY 2 DESC;



/*---------------------------------------------------------------
FETCH FIRST ROWS
---------------------------------------------------------------*/


/*---------------------------------------------------------------
QUESTION 18
Top 5 highest paid employees
---------------------------------------------------------------*/

SELECT
employee_id,
salary
FROM hr.employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;



/*---------------------------------------------------------------
QUESTION 19
Top 10 employees by salary
---------------------------------------------------------------*/

SELECT
employee_id,
salary
FROM hr.employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;



/*---------------------------------------------------------------
OFFSET EXAMPLE
---------------------------------------------------------------*/

SELECT
employee_id,
salary
FROM hr.employees
ORDER BY salary DESC
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY;



/*---------------------------------------------------------------
DUAL TABLE EXAMPLES
---------------------------------------------------------------*/


SELECT SYSDATE FROM dual;

SELECT USER FROM dual;

SELECT 2*5+10 FROM dual;



/*---------------------------------------------------------------
STRING OPERATIONS
---------------------------------------------------------------*/


SELECT
'Oracle' || ' Database'
FROM dual;



SELECT
'Hello ' || USER
FROM dual;



/*---------------------------------------------------------------
CHARACTER FUNCTIONS
---------------------------------------------------------------*/


SELECT
UPPER(first_name)
FROM hr.employees;



SELECT
LOWER(last_name)
FROM hr.employees;



SELECT
INITCAP(first_name)
FROM hr.employees;



SELECT
SUBSTR(first_name,1,3)
FROM hr.employees;



SELECT
LENGTH(first_name)
FROM hr.employees;



/*---------------------------------------------------------------
TRIM FUNCTIONS
---------------------------------------------------------------*/

SELECT TRIM(' Oracle ') FROM dual;

SELECT LTRIM('     Oracle') FROM dual;

SELECT RTRIM('Oracle      ') FROM dual;



/*---------------------------------------------------------------
NUMERIC FUNCTIONS
---------------------------------------------------------------*/

SELECT ROUND(123.456,2) FROM dual;

SELECT TRUNC(123.456,2) FROM dual;

SELECT MOD(10,3) FROM dual;

SELECT CEIL(2.3) FROM dual;

SELECT FLOOR(2.9) FROM dual;

SELECT POWER(2,3) FROM dual;

SELECT SQRT(16) FROM dual;



/*---------------------------------------------------------------
DATE FUNCTIONS
---------------------------------------------------------------*/

SELECT SYSDATE FROM dual;

SELECT SYSTIMESTAMP FROM dual;

SELECT ADD_MONTHS(SYSDATE,6) FROM dual;

SELECT LAST_DAY(SYSDATE) FROM dual;

SELECT NEXT_DAY(SYSDATE,'MONDAY') FROM dual;



/*---------------------------------------------------------------
CONVERSION FUNCTIONS
---------------------------------------------------------------*/

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM dual;

SELECT TO_DATE('2025-01-01','YYYY-MM-DD') FROM dual;

SELECT TO_NUMBER('1000')+500 FROM dual;



/*---------------------------------------------------------------
NULL FUNCTIONS
---------------------------------------------------------------*/

SELECT NVL(NULL,0) FROM dual;

SELECT NVL2(NULL,'YES','NO') FROM dual;

SELECT COALESCE(NULL,NULL,'DEFAULT') FROM dual;

SELECT NULLIF('A','A') FROM dual;



/*---------------------------------------------------------------
END OF PART 01A
---------------------------------------------------------------*/


/*===============================================================
PART 01B – SQL FUNDAMENTALS


Topics Covered
--------------
• CASE Expressions
• Advanced ORDER BY
• Reporting Queries
• Salary Calculations
• HR Schema Exploration
• Data Formatting
===============================================================*/



/*---------------------------------------------------------------
CASE EXPRESSIONS
---------------------------------------------------------------*/


/*---------------------------------------------------------------
QUESTION 20
Classify employees by salary
---------------------------------------------------------------*/

SELECT
employee_id,
salary,
CASE
    WHEN salary >= 20000 THEN 'EXECUTIVE'
    WHEN salary >= 10000 THEN 'MANAGER'
    WHEN salary >= 5000 THEN 'SENIOR STAFF'
    ELSE 'STAFF'
END AS salary_grade
FROM hr.employees;



/*---------------------------------------------------------------
QUESTION 21
Department classification
---------------------------------------------------------------*/

SELECT
employee_id,
department_id,
CASE
    WHEN department_id = 10 THEN 'ADMINISTRATION'
    WHEN department_id = 20 THEN 'MARKETING'
    WHEN department_id = 30 THEN 'IT'
    ELSE 'OTHER'
END AS department_group
FROM hr.employees;



/*---------------------------------------------------------------
QUESTION 22
Salary band classification
---------------------------------------------------------------*/

SELECT
employee_id,
salary,
CASE
WHEN salary < 5000 THEN 'LOW'
WHEN salary BETWEEN 5000 AND 15000 THEN 'MEDIUM'
ELSE 'HIGH'
END salary_band
FROM hr.employees;



/*---------------------------------------------------------------
EMPLOYEE REPORTING QUERIES
---------------------------------------------------------------*/


/*---------------------------------------------------------------
QUESTION 23
Display employee names and department names
---------------------------------------------------------------*/

SELECT
e.first_name,
e.last_name,
d.department_name
FROM hr.employees e,
     hr.departments d
WHERE e.department_id = d.department_id;



/*---------------------------------------------------------------
QUESTION 24
Display employees and job titles
---------------------------------------------------------------*/

SELECT
e.first_name,
j.job_title
FROM hr.employees e,
     hr.jobs j
WHERE e.job_id = j.job_id;



/*---------------------------------------------------------------
QUESTION 25
Display employee city
---------------------------------------------------------------*/

SELECT
e.first_name,
l.city
FROM hr.employees e,
     hr.departments d,
     hr.locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;



/*---------------------------------------------------------------
STRING REPORTING
---------------------------------------------------------------*/


SELECT
first_name || ' ' || last_name AS employee_name
FROM hr.employees;



SELECT
first_name || ' earns ' || salary
FROM hr.employees;



SELECT
'Employee ' || employee_id || ' salary is ' || salary
FROM hr.employees;



/*---------------------------------------------------------------
SALARY REPORTING
---------------------------------------------------------------*/


SELECT
employee_id,
salary,
salary*12 AS yearly_salary
FROM hr.employees;



SELECT
employee_id,
salary,
salary + 1000 AS bonus_salary
FROM hr.employees;



SELECT
employee_id,
salary,
salary * 1.25 AS projected_salary
FROM hr.employees;



/*---------------------------------------------------------------
DATE REPORTING
---------------------------------------------------------------*/


SELECT
employee_id,
hire_date,
SYSDATE - hire_date AS days_worked
FROM hr.employees;



SELECT
employee_id,
hire_date,
MONTHS_BETWEEN(SYSDATE, hire_date) AS months_worked
FROM hr.employees;



SELECT
employee_id,
hire_date,
ADD_MONTHS(hire_date,12) AS review_date
FROM hr.employees;



/*---------------------------------------------------------------
DATA FORMATTING
---------------------------------------------------------------*/


SELECT
employee_id,
TO_CHAR(salary,'$99999') salary_format
FROM hr.employees;



SELECT
employee_id,
TO_CHAR(hire_date,'DD-MON-YYYY')
FROM hr.employees;



SELECT
employee_id,
TO_CHAR(hire_date,'YYYY-MM-DD')
FROM hr.employees;



/*---------------------------------------------------------------
ADVANCED ORDER BY
---------------------------------------------------------------*/


SELECT
employee_id,
salary
FROM hr.employees
ORDER BY salary DESC;



SELECT
employee_id,
salary
FROM hr.employees
ORDER BY salary ASC;



SELECT
employee_id,
salary,
department_id
FROM hr.employees
ORDER BY department_id, salary DESC;



/*---------------------------------------------------------------
TOP-N QUERIES
---------------------------------------------------------------*/


SELECT
employee_id,
salary
FROM hr.employees
ORDER BY salary DESC
FETCH FIRST 3 ROWS ONLY;



SELECT
employee_id,
salary
FROM hr.employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;



/*---------------------------------------------------------------
SUBQUERY STYLE TOP-N
---------------------------------------------------------------*/


SELECT *
FROM
(
SELECT employee_id,
salary
FROM hr.employees
ORDER BY salary DESC
)
WHERE ROWNUM <= 5;



/*---------------------------------------------------------------
DUAL PRACTICE
---------------------------------------------------------------*/


SELECT
'Oracle SQL Training'
FROM dual;



SELECT
100 + 200
FROM dual;



SELECT
USER
FROM dual;



SELECT
SYSDATE
FROM dual;



/*---------------------------------------------------------------
EMPLOYEE DATA EXPLORATION
---------------------------------------------------------------*/


SELECT COUNT(*) FROM hr.employees;

SELECT MIN(salary) FROM hr.employees;

SELECT MAX(salary) FROM hr.employees;

SELECT AVG(salary) FROM hr.employees;



/*---------------------------------------------------------------
DEPARTMENT DATA
---------------------------------------------------------------*/


SELECT * FROM hr.departments;



SELECT
department_name
FROM hr.departments;



SELECT
department_id,
department_name
FROM hr.departments
ORDER BY department_name;



/*---------------------------------------------------------------
JOB DATA
---------------------------------------------------------------*/


SELECT * FROM hr.jobs;



SELECT
job_title,
min_salary,
max_salary
FROM hr.jobs;



/*---------------------------------------------------------------
LOCATION DATA
---------------------------------------------------------------*/


SELECT * FROM hr.locations;



SELECT
city,
state_province,
country_id
FROM hr.locations;



/*---------------------------------------------------------------
COUNTRY DATA
---------------------------------------------------------------*/


SELECT * FROM hr.countries;



SELECT
country_name
FROM hr.countries;



/*---------------------------------------------------------------
REGION DATA
---------------------------------------------------------------*/


SELECT * FROM hr.regions;



SELECT
region_name
FROM hr.regions;



/*---------------------------------------------------------------
PERFORMANCE NOTES
---------------------------------------------------------------*/

/*
Best Practices

1 Always select only required columns

2 Avoid SELECT *

3 Use ORDER BY only when required

4 Use FETCH FIRST for top-N queries

5 Use indexes on filtering columns
*/



/*---------------------------------------------------------------
END OF PART 01

Disclaimer and Limitation of Liability

The author provides all scripts, queries, and related materials “as is”, without any express 
or implied warranties, including but not limited to warranties of accuracy, completeness, 
reliability, merchantability, or fitness for a particular purpose.

By using, executing, or implementing any part of this material, the user acknowledges 
and agrees that they do so at their own risk. The author shall not be held liable for any 
direct, indirect, incidental, consequential, special, or exemplary damages, including but 
not limited to loss of data, loss of profits, system failures, security breaches, or any 
other damages or losses arising from the use or misuse of these materials.

Users are strongly advised to thoroughly review, validate, and test all scripts, queries, 
and configurations in a controlled, non-production (test) environment prior to deploying 
them in any live or production systems.

It is the sole responsibility of the user to ensure that the use of these materials 
complies with all applicable local, national, and international laws, regulations, 
and organizational policies.

By proceeding to use these materials, the user agrees to indemnify and hold harmless 
the author from any claims, damages, liabilities, or expenses arising from their use.

---------------------------------------------------------------*/