Overview

• Most popular query language for databases
• Three main components:
  1. Data Definition: Create, Alter, Delete tables (Schema)
  2. Data Manipulation: Find/manipulate specific data
  3. Data Control: Rights, permissions (not covered)

Basics

• Queries end with semicolon (;)
• Non-variable syntax in CAPS (e.g., SELECT, FROM)
• Table names, attributes, values in normal case

Data Definition

CREATE TABLE

CREATE TABLE <table name> (
  <attribute1> <domain>,
  <attribute2> <domain>,
  <attribute3> <domain>,
  [Table Constraints]
);

Example:

CREATE TABLE Employee (
  empID INT(9) NOT NULL,
  name VARCHAR(100) NOT NULL,
  salary DOUBLE(10,2),
  age INT(3),
  department CHAR(4),
  PRIMARY KEY (empID),
  FOREIGN KEY (department) REFERENCES Department(code)
);

Constraints

• NOT NULL: Attribute can't be null
• PRIMARY KEY: Unique identifier
• FOREIGN KEY: References another table

SQL Domains

• CHAR(n): Fixed-length string
• VARCHAR(n): Variable-length string
• DOUBLE(n,d): Floating point