2.3 Writing Basic Queries: SELECT, FROM, WHERE

SQL queries are the fundamental way to interact with a database. The SELECT, FROM, and WHERE clauses form the core of SQL's querying capabilities. These clauses allow you to retrieve specific data from a database, define the data source, and apply filters.

2.3.1 What Is a Query?

A query is a request to access or manipulate data in a database. Writing queries allows you to:

• Retrieve specific data.
• Filter rows based on conditions.
• Combine data from multiple tables.

Components of a Basic Query

A simple SQL query structure looks like this:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

2.3.2 The SELECT Clause

The SELECT clause specifies the columns you want to retrieve.

• Syntax:

  SELECT column_name1, column_name2, ...
  

  • Use * to select all columns.
• Example:
  Retrieve all columns from the employees table:

  SELECT * FROM employees;
  

  Retrieve specific columns:

  SELECT name, salary FROM employees;
  

2.3.3 The FROM Clause

The FROM clause defines the table from which the data is retrieved.

• Syntax:

  FROM table_name
  

• Example:
  Select data from the customers table:

  SELECT name, city FROM customers;
  

2.3.4 The WHERE Clause

The WHERE clause filters rows based on conditions. It limits the data retrieved to only those rows that meet the specified criteria.

• Syntax:

  WHERE condition
  

• Supported Operators:
  • Comparison: =, !=, <, >, <=, >=
  • Logical: AND, OR, NOT
  • Pattern Matching: LIKE (e.g., WHERE name LIKE 'J%')
  • Range: BETWEEN (e.g., WHERE age BETWEEN 18 AND 30)
  • Set: IN (e.g., WHERE department IN ('HR', 'IT'))
• Example:
  Retrieve employees with a salary greater than 50,000:

  SELECT name, salary 
  FROM employees 
  WHERE salary > 50000;
  

2.3.5 Combining SELECT, FROM, and WHERE

Let’s combine all three clauses in a query:

Example: Retrieve names and email addresses of employees who work in the "IT" department and earn more than 60,000:

SELECT name, email
FROM employees
WHERE department = 'IT' AND salary > 60000;

2.3.6 Real-World Examples

1. Retrieve Customer Details by City:

   SELECT name, phone
   FROM customers
   WHERE city = 'New York';
   

2. Find Products Priced Between $50 and $100:

   SELECT product_name, price
   FROM products
   WHERE price BETWEEN 50 AND 100;
   

3. Search Using a Pattern:
   Retrieve employee names that start with the letter "J":

   SELECT name
   FROM employees
   WHERE name LIKE 'J%';
   

4. Exclude Specific Data:
   Retrieve all employees except those in the "HR" department:

   SELECT name, department
   FROM employees
   WHERE department != 'HR';
   

Tips for Writing Effective Queries

1. Use Aliases for Readability:
   Shorten table or column names for convenience.

   SELECT e.name AS EmployeeName, e.salary
   FROM employees AS e
   WHERE e.salary > 50000;
   

2. Start Small and Test:
   Begin with a simple SELECT and gradually add complexity with WHERE and other clauses.
3. Avoid Selecting Unnecessary Data:
   Use specific column names instead of * to improve performance and reduce data transfer.

Conclusion

The combination of SELECT, FROM, and WHERE forms the foundation of SQL queries. By mastering these basic clauses, you can retrieve specific, filtered, and meaningful data from your database. This knowledge is essential for performing more advanced data operations later in your SQL learning journey.