Chapter3

Introduction to SQL

  • Presented by Dr. Zahra Nematzadeh
  • Based on "Database System Concepts, 7th Ed."

Overview

  • SQL (Structured Query Language) is key for interacting with databases.
  • Topics covered:
    • SQL Query Language
    • Data Definition in SQL
    • Basic Query Structure
    • Additional Operations
    • Set Operations
    • Handling NULL Values
    • Aggregate Functions
    • Nested Subqueries
    • Database Modification

SQL Parts

  • DML (Data Manipulation Language): Used for querying and modifying data.
  • DDL (Data Definition Language): Specifies structure and constraints of data.
    • Commands for integrity constraints, view definitions, and transaction control.
  • Embedded SQL: Allows incorporating SQL statements within programming languages.
  • Authorization: Controls access to data.

Domain Types in SQL

  • char(n): Fixed length character strings.
  • varchar(n): Variable length character strings.
  • int: Integer values (machine-dependent).
  • smallint: Smaller integer values.
  • numeric(p,d): Precision defined numbers (p total, d decimal places).
  • real: Floating point values.
  • float(n): Floating point with precision of at least n.   

Create Table Construct

  • Command: CREATE TABLE r (A1 D1, A2 D2, ..., An Dn);
    • r: Relation name
    • Ai: Attribute name
    • Di: Data type
  • Example: CREATE TABLE instructor (ID char(5), name varchar(20), dept_name varchar(20), salary numeric(8,2));

Integrity Constraints

  • Types include:
    • Primary Key: Uniquely identifies rows.
    • Foreign Key: References primary keys from other tables.
    • Not Null: Ensures fields contain values.
  • SQL prevents updates violating these constraints.

Additional Relation Definitions

  • Definitions for student, takes, and course tables specifying attributes and constraints.

Table Updates

  • Insert: Add new rows to a table.
  • Delete: Remove rows (does not delete the structure).
  • Drop Table: Removes table structure if it exists.
  • Alter: Modifies table structure (adding/removing attributes).

Basic Query Structure

  • Format: SELECT Ai FROM Ri WHERE P;
  • The result is a relation derived from the query.

The SELECT Clause

  • Lists desired attributes using SQL keywords (case insensitive).
  • Allows duplicates by default; use DISTINCT to eliminate.
  • An asterisk (*) retrieves all attributes.
  • Example: SELECT 'XYZ' AS alias;

LIMIT Clause

  • Reduces number of rows returned in a query to manage large datasets.

The WHERE Clause

  • Specifies conditions for row selection; supports logical operators and comparison operators.
  • Examples include finding records based on salary, age, etc.

Predicates in the WHERE Clause

  • Operators include BETWEEN, IN, LIKE, and more.
  • SQL can handle complex conditions using Boolean operations.

From Clause

  • Indicates the relations involved in the query and can generate Cartesian products.

Examples of Usage

  • Several illustrative examples of queries that include filtering, grouping, and ordering results.

Set Operations

  • Union, Intersect, and Except: Manage results from multiple queries while automatically eliminating duplicates.

Handling NULL Values

  • Represent missing or unknown data.
  • NULLs affect arithmetic operations and can be checked using IS NULL or IS NOT NULL.

Aggregate Functions

  • Functions like COUNT, MIN, MAX, SUM, AVG summarize data across rows.
    Examples of usage include counting and averaging over specific criteria.

Grouping Data

  • Using the GROUP BY clause to aggregate data, often in conjunction with aggregate functions.

HAVING Clause

  • Filters results after group formation, differentiating from WHERE which is applied earlier in the query process.

Subqueries

  • SQL supports nested queries to extract deeper insights; can be nested in SELECT, FROM, or WHERE clauses.

Modification of the Database

  • Execute updates through DELETE, INSERT, or UPDATE commands, detailing necessary clauses for targeting specific entries.