SQL Fundamentals

Overview to SQL

• Originally called 'Sequel', Structured English Query Language
• Designed and implemented at IBM, with ANSI Standards: SQL-86 or SQL1, SQL-92 or SQL2, SQL-99 or SQL3
• Most modern DBMS use a variety of SQL, mostly based on SQL2, increasingly SQL3
• Can be used from other languages and often extended to provide common programming constructs (if-then tests, loops, variables, etc.)

SQL Data Types

• char(n): fixed-length character string
• varchar(n): variable-length character string with specific maximum length n
• int: integer
• smallint: small integer
• numeric(p, d): fixed-point number with user-specified precision
• float(n): floating-point number, with precision of at least n digits
• real, double precision

SQL Basic Schema Definition

• Defines an SQL relation using the create table command
• General form: create table r (A1 D1, A2 D2, ..., An Dn, ...);
• r is the name of the relation, Ai is the name of an attribute, Di specifies the type of attribute Ai

Integrity Constraints

• SQL prevents updates that violate an integrity constraint
• Supports various integrity constraints, including:
  • Primary key: primary key attributes are required to be not null and unique
  • Foreign key: defined through constraints, specifying columns, referenced table, and referenced columns

Implementing E/R Designs

• Entities and attributes: each entity becomes a table, with attributes as columns
• Primary keys are defined through constraints
• Relationships:
  • 1:1: usually not used or treated as a special case of M:1
  • M:1: represented as a foreign key from the M-side to the 1
  • M:M: split into two M:1 relationships

Example

• Creating tables for department, course, and instructor entities
• Defining primary keys and foreign keys

More SQL

• DROP TABLE: deletes a table
• ALTER TABLE: changes a table
  • Adds or removes a column
  • Adds or removes a constraint
• INSERT, UPDATE, and DELETE: add, change, or remove rows from a table
  • Use WHERE clauses to specify which rows to change or remove
  • Be careful with these, as incorrect WHERE clauses can destroy data

Deleting Tables

• DROP TABLE removes a table
• DELETE FROM removes all tuples from a table

Changing Tables

• ALTER TABLE:
  • Adds a new column: alter table r add A D;
  • Removes an existing column: alter table r drop A;
  • Adds a new constraint: alter table r add constraint;
  • Removes an existing constraint: alter table r drop constraint;

INSERT and UPDATE

• INSERT INTO: adds a row to a table
• UPDATE: changes row(s) in a table
• Both use WHERE clauses to specify which rows to change or add