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


No comments:

Post a Comment