Database Systems: SQL Advanced Commands (Lecture 10)

1. Conditional Logic (The CASE Expression)

The CASE statement goes through conditions and returns a value when the first condition is met (like an If-Then-Else statement).

• Syntax:

  CASE
      WHEN condition1 THEN result1
      WHEN condition2 THEN result2
      ELSE result
  END;

• Usage in ORDER BY:
  • It allows for Conditional Sorting.
  • Example: If the City column is NULL, sort by Country instead.

  SELECT * FROM EMPLOYEE
  ORDER BY (
      CASE
          WHEN City IS NULL THEN Country
          ELSE City
      END
  );

2. Range Filtering (BETWEEN Operator)

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

• The BETWEEN operator is inclusive: begin and end values are included.
• Syntax:

  SELECT * FROM Products
  WHERE Price BETWEEN 3000 AND 7000;

  (This is equivalent to Price >= 3000 AND Price <= 7000).

3. Limiting Results (LIMIT Clause)

The LIMIT clause is used to specify the number of records to return. It is very useful on large tables to improve performance.

• Syntax:

  SELECT * FROM Products
  ORDER BY Price ASC
  LIMIT 3;

  (This returns only the top 3 cheapest products).

4. Deleting Data (TRUNCATE TABLE)

The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.

• Difference from DROP: DROP TABLE deletes the data AND the structure (schema). TRUNCATE keeps the structure but wipes the data clean.
• Difference from DELETE: TRUNCATE is faster and removes all rows without a WHERE clause.
• Syntax:

  TRUNCATE TABLE Products;