Structured Query Language (SQL) Overview

Study Notes

Data Definition Language (DDL)
- Creates, modifies, and deletes database structures
- Used to create tables, indexes, and views.
Data Manipulation Language (DML)
- Used to retrieve, add, delete, and update data in tables
- Examples include INSERT, DELETE, UPDATE, and SELECT statements
Data Control Language (DCL)
- Used to control access and permissions to the database
- Examples include GRANT and REVOKE statements

Character Strings:
- char(n): Fixed-length string with consistent size
- varchar(n): Variable-length string with varying size
Exact Numerics:
- tinyint: Stores small integer values between 0 and 255.
- smallint: Stores integer values between -32,768 and 32,767.
- int: Stores larger integer values.
- bigint: Stores even larger integer values.
- decimal(p, s): Stores decimal numbers with specified precision (p) and scale (s).
Dates and Times:
- date: Stores dates in the format 'YYYY-MM-DD'.

Relational Schema Design:
- Focus on defining relationships between tables
- Identify primary keys (PKs) and foreign keys (FKs) to establish links
Example:
- EMPLOYEE table stores employee information with columns like enum, ename, salary, and floor.
- PROJECT table stores project details with pnum, pname, and leader.
- WORKS_ON table connects employees to projects.

Table Names and SQL Reserved Words:
- Use all uppercase letters, separate words with underscores (e.g., EMPLOYEE).
Attribute Names:
- Use lowercase letters and underscores to separate words (e.g., ename).