SELECT Queries

Retrieving Data

The SELECT statement is the most commonly used SQL command. It retrieves data from one or more tables. Let's explore the different ways to query your data.

Basic SELECT

  -- Select everything
  SELECT * FROM Students;

  -- Select specific columns
  SELECT Name, Email FROM Students;

  -- Select with alias
  SELECT Name AS StudentName, Age AS StudentAge FROM Students;

  -- Select unique values
  SELECT DISTINCT Department FROM Students;

  -- Select with limit
  SELECT * FROM Students LIMIT 10;

WHERE Clause

The WHERE clause filters rows based on conditions:

  -- Comparison operators
  SELECT * FROM Students WHERE Age = 25;
  SELECT * FROM Students WHERE Age > 22;
  SELECT * FROM Students WHERE Age >= 21 AND Age <= 25;

  -- IN operator
  SELECT * FROM Students WHERE DeptID IN (10, 20, 30);

  -- LIKE operator (pattern matching)
  SELECT * FROM Students WHERE Name LIKE 'A%';    -- starts with A
  SELECT * FROM Students WHERE Name LIKE '%son';  -- ends with son
  SELECT * FROM Students WHERE Name LIKE '_lice'; -- exactly 5 chars

  -- BETWEEN operator
  SELECT * FROM Students WHERE Age BETWEEN 20 AND 25;

  -- NULL check
  SELECT * FROM Students WHERE Email IS NULL;
  SELECT * FROM Students WHERE Email IS NOT NULL;

ORDER BY

  -- Sort ascending (default)
  SELECT * FROM Students ORDER BY Name ASC;

  -- Sort descending
  SELECT * FROM Students ORDER BY Age DESC;

  -- Sort by multiple columns
  SELECT * FROM Students ORDER BY DeptID, Name;

Combined Example

  -- Find all students in the CS department over 21,
  -- sorted by name, showing only the first 5
  SELECT Name, Age, Department
  FROM Students
  WHERE Department = 'CS' AND Age > 21
  ORDER BY Name
  LIMIT 5;