Chapter3

Chapter 3: Introduction to SQL

Overview of SQL

• SQL (Structured Query Language) is a standardized language for managing and manipulating databases.

• Key components: Data Definition Language (DDL), Data Manipulation Language (DML), and various SQL constructs.

SQL Query Language Structure

• Basic Components of SQL:

  • DML: Queries, inserts, deletes, updates tuples in the database.

  • DDL: Defines database schema, constraints, and view definitions.

  • Transaction Control: Commands to manage transactions in relational databases.

  • Authorization: Managing users' permissions on data access.

Domain Types in SQL

• Basic Data Types:

  • char(n): Fixed length character string of length n.

  • varchar(n): Variable length character string.

  • int: Integer type.

  • smallint: Smaller range integer.

  • numeric(p,d): Exact numeric values with precision p and d.

  • real and float(n): Floating point types.

Creating Tables

• CREATE TABLE Syntax:

  CREATE TABLE table_name (
    column_name datatype,
    ...
    [column_constraint]
  );

• Example:

  CREATE TABLE instructor (
    ID char(5),
    name varchar(20),
    dept_name varchar(20),
    salary numeric(8,2)
  );

Integrity Constraints in SQL

• Constraints ensure data integrity, examples include:

  • Primary Key: Uniquely identifies records in a table.

  • Foreign Key: References primary key in another table, maintaining relationship.

  • NOT NULL: Ensures a column cannot have a null value.

Basic Table Operations

• Insert Command:

  • Inserts new tuples into a table.

• Delete Command:

  • Deletes specific rows or entire tables without affecting table structure.

• Update Command:

  • Modifies existing data in table rows based on criteria.

Basic Query Structure

• Standard format:

  • Query Structure:

  SELECT attributes FROM relations WHERE criteria;

• SELECT Clause: Specifies attributes to retrieve, allows for conditions and filtering.

  • Use of DISTINCT to eliminate duplicate results.

  • Using wildcards in searching with LIKE operator.

Ordering Results

• ORDER BY Clause: Sorts the results by specified fields.

  • Can specify ascending (ASC) or descending (DESC) order.

Working with NULL Values

• NULL signifies missing or unknown values in SQL.

• Use IS NULL and IS NOT NULL predicates to filter records.

• Any arithmetic operation with NULL results in NULL.

Set Operations

• SQL supports operations like UNION, INTERSECT, and EXCEPT to handle results from multiple queries.

• UNION ALL: Retains duplicates, while UNION eliminates duplicates.

Aggregate Functions

• Facilitates data summarization in queries, examples include:

  • COUNT(), SUM(), AVG(), MIN(), and MAX().

• Commonly used with GROUP BY to summarize data.

Nested Queries and Subqueries

• SQL allows nesting of queries, useful for filtering or generating complex results.

  • A subquery can be placed in various clauses such as SELECT, FROM, and WHERE.

Case Statement and Conditional Updates

• Allows changing row values based on specific conditions, optimizing update operations.

Conclusion

• SQL provides a robust syntax for managing and querying relational databases, establishing the foundation for effective database manipulation and data integrity.