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

Moving Soon!

 Hey Guys, I've started a website of my own and will be publishing articles there moving forward.

Take a look if you're interested: www.migsisip.com/

Thanks for all your support throughout the years!

Stop using Dynamic SQL! Seriously.

From my experience, research and general knowledge, Dynamic SQL should be avoided as much as possible,

There are multiple reliable sources that say this, even Oracle’s revered top DBA, Tom Kyte, and Oracle’s own PL/SQL Evangelist and Oracle ACE, Steven Feuerstein has mentioned this multiple times in AskTOM and Oracle Blogs.

To list some of the disadvantages of Dynamic SQL (from research and my own inputs):

It cannot be detected by the Oracle Enterprise Manager to have bad statistics because it is not parsed before it is run. It’s basically invisible to the DB parser.

The query this is not being “recorded” in the pool, therefore, we cannot predict if this certain piece of code will perform badly or not, we cannot quantify its statistics.

There are great efficiencies to be gained by using static sql, as PLSQL will cacheopen cursors and reuse statements, even before they are run.

Dynamic SQL is not scalable. Static SQL is scalable.

Does not really have any big advantage over static SQL apart from being “flexible”, but the disadvantages greatly outweighs these advantages.

It’s actually not true that Dynamic SQL performs better, in fact: dynamic SQL is more complex than static SQL, it executes more slowly than static SQL. Especially when done wrong.

We should Re-use code whenever possible, but only when that is appropriate. And I believe we can re-use code without doing Dynamic SQL. Do we REALLY need that SQL to be dynamic? 

Dynamic SQL is only really considered when we don’t know what parameters are used before runtime, something like in OBIEE, where the user will be the one to input the columns they want and need, then run the query.

I believe we can re-write these programs to use fixed, structured, and static queries instead.

Dynamic SQL is prone to SQL-Injection in General. I don’t know if there will be a time where we will encounter this issue, but it will be a huge mess if it does happen.

Personally, I find them extremely hard to debug, hard to read, and impractical because we only find out the actual query at runtime.

We cannot see the dependencies between programs because as I mentioned, they are invisible and only appear during runtime.

It will be hard to see which programs get impacted when we change a certain code, which in turn, will result in another bug, etc. 

I can list down more Pros and Cons, but I believe these are enough details to establish that the disadvantages of Dynamic SQL outweighs the advantages.

I strongly urge that our dev teams start to write Static SQL instead of Dynamic SQL.

To be edited further...