SQL Notes

SQL Basics

• SQL Definition: Standard language used in relational databases for creating and manipulating database content.

Types of SQL Commands

• Data Definition Language (DDL): Used for creating and modifying database structures.
  • Commands: CREATE, ALTER, DROP
• Data Manipulation Language (DML): Used for querying and managing data within the database.
  • Commands: INSERT, UPDATE, DELETE, SELECT
• Data Control Language (DCL): Used for controlling access to data within the database.
  • Commands: GRANT, REVOKE

SQL Statements

• Case Sensitivity: SQL is generally not case sensitive, except within quotes.
• Line Formatting: SQL statements can span multiple lines for readability. Each statement must end with a semicolon.
• Keywords: Cannot be split across lines.

DDL (Data Definition Language)

CREATE Statement

• Purpose: Allows the creation of database structures.
• Usage: Focus on CREATE TABLE statements for defining new tables.
• Data Types:
  • Character data needs to be in quotes.
  • Numeric data does not require quotes.
  • Examples:
  • CHAR: Fixed length fields (e.g., phone numbers, zip codes).
  • VARCHAR: Variable length fields (e.g., names, descriptions).

ALTER Statement

• Purpose: Changes the structure of an existing database.
• Adding Columns: Use the ADD clause of the ALTER TABLE command.
• Example:

  ALTER TABLE Inventory
  ADD CONSTRAINT InventoryPK PRIMARY KEY(InventoryID);

DROP Statement

• Purpose: Removes database structures.
• Note: Dropping a table also deletes its contents.
• Referential Integrity: A table with a foreign key must be dropped before the table with the primary key.

DCL (Data Control Language)

Privileges Management

• GRANT: Assigns privileges to users.
• DENY: Restricts specific privileges.
• REVOKE: Removes previously granted privileges.

DML (Data Manipulation Language)

SELECT Statement

• Purpose: Queries the database to retrieve specific data.
• Basic Format:

  SELECT column1, column2
  FROM table_name
  WHERE condition;

INSERT Statement

• Purpose: Adds new rows to a table.
• Example:

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

UPDATE Statement

• Purpose: Modifies existing rows based on a condition.
• Example:

  UPDATE table_name
  SET column1 = value1
  WHERE condition;

DELETE Statement

• Purpose: Deletes rows from a table based on a condition.
• Example:

  DELETE FROM table_name
  WHERE condition;

Sample SQL Queries

• Customer Count:

  SELECT COUNT(*) AS CustomerCount
  FROM CUSTOMER
  WHERE CustType IN ('1', '2');

• Output: 8

  • Average Weight of Shipments:

  SELECT ShipmentNo, AVG(Weight) AS AvgWeight
  FROM PACKAGE
  GROUP BY ShipmentNo
  ORDER BY AvgWeight ASC;

• Cities per Truck:

  SELECT TruckNo, COUNT(DISTINCT CityID) AS NumCities
  FROM PACKAGE
  GROUP BY TruckNo;

• Shipments over 50 lbs:

  SELECT * FROM PACKAGE
  WHERE Weight > 50;

• Package and Average Weight by Customer:

  SELECT CustID, COUNT(*) AS NumPackages, AVG(Weight) AS AvgWeight
  FROM PACKAGE
  WHERE CustID > 102
  GROUP BY CustID
  HAVING AVG(Weight) BETWEEN 45 AND 80;

Database Integrity

Entity Integrity

• Primary Key (PK): Uniquely identifies a record in a table.
• Foreign Key (FK): Links records between two tables and maintains referential integrity.

Operational Integrity

• CHECK Constraint: Limits the values that can be placed in a column.
• NOT NULL: Specifies that a column cannot have a NULL value.

Default Values

• DEFAULT Constraint: Assigns a default value for a column if no value is specified during an insert.

Practical SQL Exercises

• Creating Tables: Practice creating tables following sample structure provided.
• Altering Tables: Practice adding/removing columns and constraints to existing tables.
• Dropping Tables: Understand the sequence of dropping tables while adhering to referential integrity rules.