SQL basics

Posted in software by Christopher R. Wirz on Tue Mar 13 2012



Structured Query Language

SQL is a comprehensive database language used for defining, manipulating, and controlling data in relational database management systems. It has been implemented in many commercial systems including Oracle, IBM's DB2, Microsoft SQL Server, and open-source systems like MySQL and PostgreSQL.

SQL Data Definition and Data Types

Key Terms:

  • Schema: A named collection of database objects (tables, views, etc.) owned by a specific user
  • Catalog: A named collection of schemas
  • Base Table: An actual table created and stored as a file by the DBMS
  • Virtual Relation: A table that may not correspond to an actual physical file (e.g., views)

Creating Schemas and Tables


CREATE SCHEMA COMPANY AUTHORIZATION 'Jsmith';

CREATE TABLE EMPLOYEE (
    Fname VARCHAR(15) NOT NULL,
    Lname VARCHAR(15) NOT NULL,
    Ssn CHAR(9) PRIMARY KEY,
    Bdate DATE,
    Address VARCHAR(30),
    Sex CHAR(1),
    Salary DECIMAL(10,2),
    Super_ssn CHAR(9),
    Dno INT NOT NULL,
    FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn),
    FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber)
);

SQL Data Types

  • Numeric:

    • INTEGER or INT, SMALLINT
    • FLOAT, REAL, DOUBLE PRECISION
    • DECIMAL(i,j) or DEC(i,j) or NUMERIC(i,j)

  • Character String:

    • Fixed length: CHAR(n) or CHARACTER(n)
    • Variable length: VARCHAR(n) or CHARACTER VARYING(n)
    • Large objects: CHARACTER LARGE OBJECT or CLOB

  • Bit String:

    • Fixed length: BIT(n)
    • Variable length: BIT VARYING(n)
    • Large objects: BINARY LARGE OBJECT or BLOB

  • Boolean: TRUE, FALSE, or UNKNOWN

  • Date and Time:

    • DATE: 'YYYY-MM-DD'
    • TIME: 'HH:MM:SS'
    • TIMESTAMP: Combined DATE and TIME with fractions of seconds
    • INTERVAL: For relative values used to increment/decrement date/time values

Domains

Create reusable data types that can be referenced by multiple attributes:


CREATE DOMAIN SSN_TYPE AS CHAR(9);

Specifying Constraints in SQL

Key Terms:

  • Attribute Constraint: Restricts values for a specific attribute
  • DEFAULT Value: Value used when no value is provided
  • CHECK Clause: Specifies a condition that data must satisfy
  • PRIMARY KEY: Uniquely identifies each row in a table
  • UNIQUE: Defines candidate/alternate keys
  • FOREIGN KEY: Creates relationships between tables
  • Referential Integrity: Ensures relationships between tables remain consistent
  • Referential Triggered Action: Action taken when a referenced row is modified or deleted

Constraint Types


-- Attribute constraints
Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21)

-- Default values
Mgr_start_date DATE DEFAULT CURRENT_DATE

-- Key constraints
PRIMARY KEY (Ssn)
UNIQUE (Dname)

-- Foreign key with triggered actions
FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn)
    ON DELETE SET NULL
    ON UPDATE CASCADE

Named Constraints


CONSTRAINT dept_mgr_constraint 
    CHECK (Dept_create_date <= Mgr_start_date)

Basic Retrieval Queries in SQL

Key Terms:

  • SELECT-FROM-WHERE Block: Basic structure of SQL queries
  • Projection Attributes: Attributes selected for output
  • Selection Condition: Boolean condition to filter rows
  • Join Condition: Condition for combining rows from multiple tables
  • Select-Project-Join Query: A query involving selection, projection, and join operations
  • Alias/Tuple Variable: Alternative name for a table in a query
  • Qualified Attribute Names: Table-prefixed attribute names to resolve ambiguity
  • Multiset (Bag): A collection that allows duplicate elements (default in SQL)

Basic Query Structure


SELECT <attribute list>
FROM <table list>
WHERE <condition>
ORDER BY <attribute list>;

Simple Queries


-- Query with condition
SELECT Bdate, Address
FROM EMPLOYEE
WHERE Fname = 'John' AND Minit = 'B' AND Lname = 'Smith';

-- Join query
SELECT Fname, Lname, Address
FROM EMPLOYEE, DEPARTMENT
WHERE Dname = 'Research' AND Dnumber = Dno;

Table Aliases


SELECT E.Fname, E.Lname, S.Fname, S.Lname
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn = S.Ssn;

Set Operations

  • UNION: Combines results of two queries, removing duplicates
  • EXCEPT (or MINUS): Retrieves rows in first query but not in second
  • INTERSECT: Retrieves rows that appear in both query results
  • Multiset Operations: UNION ALL, EXCEPT ALL (removes one matching occurrence), INTERSECT ALL (preserve duplicates)

(SELECT Pnumber FROM PROJECT WHERE...)
UNION
(SELECT Pnumber FROM PROJECT WHERE...);

Additional Query Features

  • DISTINCT: Eliminates duplicate rows
  • ALL: Keeps duplicate rows (default)
  • Pattern matching: LIKE with % (any string) and _ (single character)
  • BETWEEN: Range comparison
  • ORDER BY: Sort results (ASC or DESC)

-- Find employees in Houston
SELECT Fname, Lname
FROM EMPLOYEE
WHERE Address LIKE '%Houston,TX%';

-- Salary in a range
SELECT *
FROM EMPLOYEE
WHERE (Salary BETWEEN 30000 AND 40000) AND Dno = 5;

-- Sorted results
SELECT Dname, Lname, Fname
FROM DEPARTMENT, EMPLOYEE
WHERE Dnumber = Dno
ORDER BY Dname, Lname, Fname;

INSERT Command


-- Insert with all values
INSERT INTO EMPLOYEE
VALUES ('Richard', 'K', 'Marini', '653298653', '1962-12-30', 
        '98 Oak Forest, Katy, TX', 'M', 37000, '653298653', 4);

-- Insert with specific attributes
INSERT INTO EMPLOYEE (Fname, Lname, Dno, Ssn)
VALUES ('Richard', 'Marini', 4, '653298653');

-- Insert based on query results
INSERT INTO WORKS_ON_INFO (Emp_name, Proj_name, Hours_per_week)
SELECT E.Lname, P.Pname, W.Hours
FROM PROJECT P, WORKS_ON W, EMPLOYEE E
WHERE P.Pnumber = W.Pno AND W.Essn = E.Ssn;

DELETE Command


-- Delete specific rows
DELETE FROM EMPLOYEE
WHERE Dno = 5;

-- Delete all rows
DELETE FROM EMPLOYEE;

UPDATE Command


-- Update specific attributes
UPDATE PROJECT
SET Plocation = 'Bellaire', Dnum = 5
WHERE Pnumber = 10;

-- Update based on existing values
UPDATE EMPLOYEE
SET Salary = Salary * 1.1
WHERE Dno = 5;

Additional SQL Features

  • Complex queries (nested queries, aggregate functions, grouping)
  • Views, triggers, and assertions
  • Schema modification commands
  • Programming with SQL (embedded SQL, JDBC)
  • Transaction control
  • Authorization (GRANT/REVOKE)
  • Object-relational features
  • Integration with XML and data warehouses

Key Concepts

  1. SQL treats tables as multisets (bags) that allow duplicate rows, unless DISTINCT is specified.
  2. SQL requires that attribute names be unique within a table, but can be reused across different tables.
  3. Referential integrity constraints can specify actions (CASCADE, SET NULL, SET DEFAULT) when referenced rows are updated or deleted.
  4. Schema creation and table creation require appropriate privileges.
  5. The asterisk (*) can be used to select all attributes from specified tables.
  6. Pattern matching with LIKE uses % for any string and _ for any single character.
  7. Attribute and table aliasing helps avoid ambiguity and enables self-joins.

With SQL, you can define database schemas, create tables with appropriate constraints, retrieve data with queries, and modify the database content as needed.