SQL Overview and Essential Concepts

SQL Overview

• Structured Query Language (SQL) is the standard for relational database management systems (RDBMS).

History of SQL

• 1970: E. Codd develops relational database concept.
• 1974-1979: System R with Sequel (later SQL) created at IBM Research Lab.
• 1979: Oracle markets the first relational DB with SQL.
• 1986: ANSI SQL standard released.
• Updates: Significant updates occurred in 1989, 1992, 1999, and 2003.
• Current status: SQL is widely supported by most major database vendors.

SQL Environment

• Data Definition Language (DDL):
  • Used for commands that define a database, including creating, altering, and dropping tables; establishing constraints.
• Data Manipulation Language (DML):
  • Used for commands that maintain and query data.
• Data Control Language (DCL):
  • Commands that control database access and privileges.

SQL Database Definition

• DDL Commands: Major commands include:
  • CREATE SCHEMA: creates a database.
  • CREATE TABLE: defines a table and its columns.
  • CREATE VIEW: defines a virtual table from one or more views.

Table Creation Steps

1. Identify data types for attributes.
2. Identify which columns can (and cannot) be NULL.
3. Identify columns that must be unique (candidate keys).
4. Identify primary keys and foreign key relationships.
5. Determine default values.
6. Identify constraints on columns (domain specifications).
7. Create the table and associated indexes.

SQL Example: Table Structures

CUSTOMER Table

• Columns:
  • CUSTOMER_ID: NUMBER(11, 0) NOT NULL
  • CUSTOMER_NAME: VARCHAR2(25) NOT NULL
  • CUSTOMER_ADDRESS: VARCHAR2(30)
  • CITY: VARCHAR2(20)
  • STATE: VARCHAR2(2)
  • POSTAL_CODE: VARCHAR2(9)
  • Primary Key: CONSTRAINT CUSTOMER_PK PRIMARY KEY (CUSTOMER_ID)

ORDER Table

• Columns:
  • ORDER_ID: NUMBER(11, 0) NOT NULL
  • ORDER_DATE: DATE DEFAULT SYSDATE
  • CUSTOMER_ID: NUMBER(11, 0)
  • Primary Key: CONSTRAINT ORDER_PK PRIMARY KEY (ORDER_ID)
  • Foreign Key: CONSTRAINT ORDER_FK FOREIGN KEY (CUSTOMER_ID)

Update, Delete, Insert Statements

• INSERT Statement: Adds data to a table.
• DELETE Statement: Removes rows from a table.
• UPDATE Statement: Modifies existing rows.

SELECT Statement

• Used for querying data:
  • Clauses:
  • SELECT: Columns to return.
  • FROM: Tables or views to query.
  • WHERE: Conditions to filter rows.
  • Aggregations and Grouping:
  • Use by GROUP BY for categorizing results.
  • HAVING applies conditions to grouped results.

Joins

• JOIN Types:
  • INNER JOIN: Matches records from both tables.
  • LEFT JOIN: All from left, matches from right.
  • RIGHT JOIN: All from right, matches from left.
  • FULL JOIN: All records with matches in either table.

Transaction Management

• Transactions ensure data integrity:
  • Commands:
  • BEGIN TRANSACTION: Starts a transaction.
  • COMMIT: Saves all changes.
  • ROLLBACK: Cancels changes if needed.

Stored Procedures and Triggers

• Stored Procedures: Precompiled SQL statements that can be reused.
• Triggers: Procedures executed automatically in response to certain events in the database.