Introduction to SQL

Structured Query Language (SQL) is a standardized programming language specifically designed for managing and manipulating relational databases. It has become the universal language for database management systems.

What is SQL?

SQL (often pronounced as “sequel”) is a domain-specific language used for managing data in relational database management systems. It was developed by IBM in the 1970s and has since become the standard language for relational database management.

History of SQL

  • 1970: Dr. Edgar F. Codd published a paper describing the relational database model
  • 1974: IBM developed SEQUEL (Structured English Query Language)
  • 1979: Oracle became the first commercial RDBMS to use SQL
  • 1986: SQL became an ANSI standard
  • 1987: SQL became an ISO standard
  • 1989, 1992, 1999, 2003, 2006, 2008, 2011, 2016: Various revisions to the SQL standard

Key Features of SQL

  1. Non-procedural language: You specify what you want, not how to get it
  2. Easy to learn and use: Uses English-like syntax
  3. Standardized: Supported by virtually all relational database systems
  4. Powerful and flexible: Can perform complex operations with concise commands
  5. Data independence: Separates the logical view from physical storage
  6. Client-server architecture: Supports remote database access

SQL Language Components

SQL can be divided into several language components:

1. Data Definition Language (DDL)

Used to define database structure:

  • CREATE: Create database objects (tables, views, indexes)
  • ALTER: Modify existing database objects
  • DROP: Delete database objects
  • TRUNCATE: Remove all records from a table, but not the table itself
  • RENAME: Rename database objects

2. Data Manipulation Language (DML)

Used to manipulate data within the database:

  • SELECT: Retrieve data from the database
  • INSERT: Add new data to a table
  • UPDATE: Modify existing data
  • DELETE: Remove data from a table
  • MERGE: Combine INSERT and UPDATE operations

3. Data Control Language (DCL)

Used to control access to data:

  • GRANT: Give privileges to users
  • REVOKE: Take away privileges from users

4. Transaction Control Language (TCL)

Used to manage transactions:

  • COMMIT: Save transactions permanently
  • ROLLBACK: Restore database to original state since the last COMMIT
  • SAVEPOINT: Create points within a transaction to ROLLBACK to
  • SET TRANSACTION: Specify transaction characteristics

Basic SQL Syntax

Creating a Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE,
    salary DECIMAL(10,2),
    department_id INT
);

Inserting Data

INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary, department_id)
VALUES (1, 'John', 'Doe', '2023-01-15', 50000.00, 3);

Querying Data

SELECT first_name, last_name, salary
FROM employees
WHERE department_id = 3
ORDER BY salary DESC;

Updating Data

UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 3;

Deleting Data

DELETE FROM employees
WHERE employee_id = 1;

SQL Data Types

SQL supports various data types, which can be categorized as:

Numeric Types

  • INT/INTEGER: Whole numbers
  • DECIMAL/NUMERIC: Fixed-precision numbers
  • FLOAT/REAL: Floating-point numbers
  • SMALLINT: Small integers
  • BIGINT: Large integers

Character/String Types

  • CHAR(n): Fixed-length strings
  • VARCHAR(n): Variable-length strings
  • TEXT: Large variable-length strings

Date and Time Types

  • DATE: Stores date values (YYYY-MM-DD)
  • TIME: Stores time values (HH:MM:SS)
  • DATETIME/TIMESTAMP: Stores date and time values

Boolean Type

  • BOOLEAN: Stores TRUE or FALSE values

Binary Types

  • BLOB: Binary Large Objects
  • BINARY/VARBINARY: Binary data of fixed/variable length

SQL Operators

Arithmetic Operators

  • +: Addition
  • -: Subtraction
  • *: Multiplication
  • /: Division
  • %: Modulus (remainder)

Comparison Operators

  • =: Equal to
  • >: Greater than
  • <: Less than
  • >=: Greater than or equal to
  • <=: Less than or equal to
  • <> or !=: Not equal to

Logical Operators

  • AND: Returns TRUE if both conditions are TRUE
  • OR: Returns TRUE if either condition is TRUE