SQL Lecture on SELECT Command and Joins

Overview of Database Query Language (DQL)

• DQL: Database Query Language
  • Purpose: Retrieve data
  • Primary command: SELECT

SELECT Command Syntax

• Basic Syntax: SELECT [list of attributes] FROM [tables] [WHERE condition]
  • SELECT: Command used to retrieve data
  • list of attributes: Columns to be fetched
  • tables: Source tables (can be one or multiple)
  • WHERE condition: Optional filter condition

Practical Examples

• Retrieve all columns from a table:

  SELECT * FROM student;
  

• Retrieve specific columns:

  SELECT Sid, Sname, Sh FROM student;
  

• Using the WHERE clause for conditions:

  SELECT * FROM student WHERE Sh > 15;
  

Comparison with Relational Algebra

• Relational Algebra: Uses sigma (σ) for selection
• SQL Select vs Relational Algebra Select: SQL’s SELECT is equivalent to the relational algebra projection for specified columns.

Filtering Data

• Horizontal Filtration:

  SELECT * FROM student WHERE Sh > 50 AND Sh < 45;
  

• Logical Operators: AND, OR, NOT
  • AND: Both conditions must be true
  • OR: Either condition can be true
  • NOT: Inverts the condition

SQL Queries Based on Relational Operators

• Operators: <, <=, >, >=, =, !=
  • SQL Not Equal: != or <>
• Range Queries:

  SELECT * FROM student WHERE Sh BETWEEN 15 AND 45;
  

• Text and Date Range Usage: SQL BETWEEN operator can be used with text and dates
• Set Operators:

  SELECT * FROM student WHERE Sh IN (25, 26);
  

  SELECT * FROM student WHERE Sh NOT IN (25, 26);
  

• Pattern Matching: Using LIKE for text searching
  • %: Zero or more characters
  • _: Single character
  • Example:

    SELECT * FROM student WHERE Sname LIKE 'J%';
    

Handling NULL Values

• IS NULL and IS NOT NULL: For handling NULL values

  SELECT * FROM student WHERE Sname IS NULL;
  

  SELECT * FROM student WHERE Sname IS NOT NULL;
  

Ordering Results

• ORDER BY Clause: Sorting results
  • Default: Ascending order
  • Example:

    SELECT * FROM student ORDER BY Sh DESC, Sid ASC;
    

• ORDER BY must be the last clause in the SQL statement

Joining Tables

• Cross Join: Cartesian product of tables

  SELECT * FROM student, department;
  

• Inner Join: Matches rows between tables

  SELECT * FROM student s INNER JOIN department d ON s.dnum = d.dnum;
  

  • Alias Names: Using 'AS' for table and column aliases

  SELECT * FROM department AS d INNER JOIN student AS s ON d.dnum = s.dnum;
  

• Outer Joins: Includes unmatched rows in results
  • Left Join:

    SELECT * FROM student AS s LEFT JOIN department AS d ON s.dnum = d.dnum;
    

  • Right Join:

    SELECT * FROM student AS s RIGHT JOIN department AS d ON s.dnum = d.dnum;
    

  • MySQL does not support full outer join directly

Aggregate Functions

• Functions: SUM, AVG, MIN, MAX, COUNT
  • Example:

    SELECT COUNT(*) FROM student;
    SELECT AVG(Sh) FROM student;
    

• Functions take one column as input
• Using functions with conditions:

  SELECT COUNT(Sname) FROM student WHERE Sh > 25;
  

Summary

• Select Command: Used for data retrieval
• Conditional Filtering: Using the WHERE clause
• Joins: Combining multiple tables
• Aggregate Functions: Performing calculations on data

Next Topics

• Continuing further with advanced queries and operations