In-Depth SQL Notes

Overview of SQL

• SQL stands for Structured Query Language.
• Originally derived from SEQUEL concept, abbreviated by IBM due to copyright issues.

Creating Tables with SQL

• CREATE TABLE Command
  • Syntax:
    sql CREATE TABLE table_name ( column1 datatype, column2 datatype, ... );
  • Example:
    sql CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) );
  • Another Example:
    sql CREATE TABLE employees ( id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), birth_date DATE );

Modifying Tables

• INSERT, DELETE, UPDATE Statements
  • INSERT: Add tuples (rows) to a table.
  • UPDATE: Modify existing tuples that meet a condition.
  • DELETE: Remove tuples that meet a condition.

INSERT Command

• Syntax:

  INSERT INTO table_name (column1, column2)
  VALUES (value1, value2);

• Example:

  INSERT INTO employees (id, first_name, last_name, birth_date)
  VALUES (1, 'John', 'Doe', '1985-01-01');

UPDATE Command

• Used for modifying attribute values in selected tuples.
• Example:

  UPDATE PROJECT 
  SET PLOCATION = 'Bellaire', DNUM = 5 
  WHERE PNUMBER=10;

• Another Example: Give all employees in the 'Research' dept. a 10% raise.

  UPDATE EMPLOYEE 
  SET SALARY = SALARY * 1.1 
  WHERE DNAME='Research';

DELETE Command

• Syntax:

  DELETE FROM table_name WHERE condition;

• Example:

  DELETE FROM employees WHERE id = 1;

• Note: A missing WHERE condition will delete all records in the table.

Table Modifications with ALTER

• ALTER TABLE Command
  • Used for changing an existing table's structure.
  • Can add, modify, drop columns, rename tables, or change constraints.

ADD a Column

• Syntax:

  ALTER TABLE table_name ADD column_name datatype;

DROP a Column

• Syntax:

  ALTER TABLE table_name DROP COLUMN column_name;

• Example:

  ALTER TABLE employees DROP COLUMN email;

Data Types in SQL

• Numeric Data Types:
  • INTEGER, INT, FLOAT, REAL, DOUBLE PRECISION.
• Character Data Types:
  • Fixed length: CHAR(n), Varying length: VARCHAR(n).
• Boolean Data Type: TRUE or FALSE or NULL.
• DATE Data Type: in the format YYYY-MM-DD.

SQL Constraints

• Basic Constraints:
  • Primary Key: Uniquely identifies record; cannot be NULL.
  • Unique Key: All column values must be unique; NULL values allowed.
  • NOT NULL: Prevents NULL values in a column.
  • Foreign Key: References primary key in another table.
  • Check: Validates data conditions (e.g., Dnumber > 0).

Example Constraint Usage

• Create table enforcing data constraints:

  CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    DateOfBirth DATE CHECK (DateOfBirth > '1900-01-01'),
    Salary DECIMAL(10,2) CHECK (Salary > 0)
  );

Sample SQL Queries

1. Count Employees in HR:

   SELECT COUNT(*) FROM employee WHERE department = 'HR';

2. Employees with salaries between 50k and 100k:

   SELECT * FROM employees WHERE SALARY BETWEEN 50000 AND 100000;

3. Retrieve Employee Names Starting with 'S':

   SELECT * FROM employees WHERE EmpFname LIKE 'S%';

JOIN Operations

1. INNER JOIN
   Combines records with matching values in both tables.

   SELECT ... FROM Employees INNER JOIN Departments ON ...;

2. LEFT JOIN
   Includes all records from the left table, unmatched records from the right.
3. RIGHT JOIN
   Includes all records from the right table, unmatched records from the left.
4. FULL JOIN
   Combines all records from both tables and returns unmatched records as NULL.

Aggregate Functions

• Built-in Functions: COUNT, SUM, MAX, MIN, AVG.
• Example Aggregate Query:

  SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM EMPLOYEE;

• GROUP BY Clause:
  • Used to group rows by specified columns and perform aggregate operations.
  • Example:

  SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department;

ORDER BY Clause

• Used to sort results by one or more columns in ascending (ASC) or descending (DESC) order.