Oracle 19c Tutorial

Oracle Database 19c: SQL

Managing Tables Using DML Statements

Introduction to Data Definition Language
Retrieving Data Using the SQL SELECT Statement
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Restricting and Sorting Data(WHERE,=, <=, BETWEEN, IN, LIKE, NULL ,AND, OR, NOT ,Order by , Substitution Variable, Define and Verify)

Limiting Rows by Using a Selection

SELECT *|{[DISTINCT] column [alias],...} FROM table [WHERE logical expression(s)];

SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ; 

SELECT last_name, job_id, department_id FROM employees WHERE last_name = 'Whalen' ; 

SELECT last_name, job_id, department_id FROM employees WHERE last_name = 'WHALEN';

Comparison Operators

SELECT last_name, salary FROM employees WHERE salary <= 3000 ;

SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500 ;

SELECT last_name FROM employees WHERE last_name BETWEEN 'King' AND 'Whalen 10 ';

SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201) ;

SELECT employee_id, manager_id, department_id FROM employees WHERE last_name IN ('Hartstein', 'Vargas');

Pattern Matching Using the LIKE Operator:

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

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

Using NULL Conditions:

SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL ; 

SELECT last_name, job_id, commission_pct FROM employees WHERE commission_pct IS NULL; 

Defining Conditions Using Logical Operators(AND , OR, NOT):

SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >= 10000 AND job_id LIKE '%MAN%' ;

SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >= 10000 OR job_id LIKE '%MAN%' ;

SELECT last_name, job_id FROM employees WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP') ; 

Sorting rows using the ORDER BY clause(ASC: Ascending order, default DESC: Descending order)

SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date ;

SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY department_id DESC ;

SELECT employee_id, last_name, salary*12 annsal FROM employees ORDER BY annsal ; 

Sorting

SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY 3;

SELECT last_name, department_id, salary FROM employees ORDER BY department_id, salary DESC;

SQL row limiting clause in a query:

SELECT employee_id, first_name FROM employees ORDER BY employee_id FETCH FIRST 5 ROWS ONLY;

SELECT employee_id, first_name FROM employees ORDER BY employee_id OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

Using Single-Row Functions to Customize Output
Using Conversion Functions and Conditional Expressions
Reporting Aggregated Data Using the Group Functions
Displaying Data from Multiple Tables Using Joins
Using Subqueries to Solve Queries
Using the Set Operators