SQL Fundamentals

Introduction to SQL and Syntax

What is SQL?

SQL stands for Structured Query Language.

is a standard programming language for accessing databases.

What can SQL do?

execute, retrieve , insert , update, delete, create, set permission.

Types of SQL Commands/Syntax

Data retrieval is a language used to allow users select records from the database

DML is a language that enables users to access or manipulate data as organized by the appropriate data model.

DDL is a language used to allow users to define the database and its objects. 

TC includes SQL transactions used to control transactions. 

DCL is a language that provides users with privilege commands

Constraints

Joins

Sorting and Filtering Data

Filtering

Use of WHERE clause

The use of LIKE, IN, EXISTS, BETWEEN, ANY, SOME, and ALL comparison conditions

Logical condition precedence: (), NOT, AND, OR

NULL values and sorting

Sorting

Use of ORDER BY clause

Sorting results in ascending and descending order 

Sorting by Multiple Columns 

Sorting by one column, then another 2. Understanding the order of sorting columns

Grouping and Aggregating Data (GROUP BY, HAVING)

Indexes

Naming Conventions

Oracle SQL Functions

There are predefined functions within SQL that can be used to perform specific actions on the data retrieved. Commonly used in our current code is:

Single Row Functions

Numeric Functions (i.e. ABS, CEIL, FLOOR, MOD, TRUNC, ROUND)

Character Functions returning Character Values (i.e. REPLACE, LPAD, RPAD, LTRIM, RTRIM, CONCAT, TRIM, SUBSTR)

Character Functions returning Numeric Values (i.e. LENGTH, INSTR)

Aggregate Functions- Returns a Single Row for the Entire Set

Typically needs the GROUP BY Clause to work (i.e. AVG, MIN, MAX, SUM, RANK, DENSE_RANK, COUNT)

Analytic Functions – Can return a multi-row data set, with one column having the calculated value

PARTITION BY

ORDER BY in the SELECT clause

OVER

WHERE clause

Used to filter data based on a set criteria with the use of different filtering conditions:

Equi (=), Anti (!=, <>), and Range (<, >, <=, >=). 

LIKE / NOT LIKE

IN / NOT IN

EXISTS / NOT EXISTS

BETWEEN

IS NULL / IS NOT NULL

Indexes

B-Tree Index

Bitmap Index

Function-Based Index

Reverse Key Index

Index-Organized Table (IOT)

Domain Index

Bitmap Join Index

Spatial Index