SELECT Queries and Retrieving Data

# CHAPTER 7

SELECT Queries and Retrieving Data #

1. Introduction #

2. Learning Objectives #

• Use the SELECT * command to fetch an entire table.

• Retrieve specific, targeted columns to optimize network bandwidth.

• Eliminate duplicate results using the DISTINCT keyword.

• Rename output columns on the fly using Aliases (AS).

3. The SELECT * Command (Fetching Everything) #

-- Retrieve absolutely everything from the customers table
SELECT * FROM customers;

*Result: The database will return a massive grid containing every row and column in the table.*

4. Selecting Specific Columns #

You must explicitly ask for the specific columns you need by separating them with commas.

-- Optimized! Only fetch the first_name and email columns!
SELECT first_name, email FROM customers;

5. Removing Duplicates (DISTINCT) #

-- Returns a clean list of unique countries (e.g., USA, UK, Canada)
SELECT DISTINCT country FROM customers;

6. Renaming Columns in the Output (Aliases / AS) #

-- Rename the output columns to be highly readable
SELECT 
    first_name AS 'First Name', 
    emp_sal AS 'Annual Salary' 
FROM employees;

7. Mini Project: The E-Commerce Dashboard #

-- 1. The Home Page wants to display a simple list of all product names and their prices
SELECT product_name, price FROM products;

-- 2. The Marketing team wants a list of all unique cities we have shipped to
SELECT DISTINCT shipping_city FROM orders;

-- 3. The Accountant wants a clean report of inventory counts
SELECT 
    product_name AS 'Item', 
    stock_quantity AS 'Remaining Inventory' 
FROM products;

8. Common Mistakes #

• Forgetting the FROM Keyword: SELECT name, email users; will throw a syntax error. You must explicitly tell SQL *where* the data is coming from: SELECT name, email FROM users;.

• Using SELECT * in Production Code: Using the asterisk is great when you are manually exploring a database in phpMyAdmin. However, hardcoding SELECT * into a PHP or Node.js backend application is a severe architectural anti-pattern. Always list your columns explicitly in production code.

9. Best Practices #

• Formatting Large Queries: As your queries get longer, do not write them all on one massive horizontal line. Break them into vertical lines for readability.

  SELECT 
      id, 
      name 
  FROM 
      users;
  ```

### 10. Exercises
1. What does the asterisk `*` symbol represent in a `SELECT` statement?
2. What SQL keyword is used to eliminate duplicate rows from the output grid?

### 11. SQL Challenges
Write a query to retrieve only the `title` and `author` columns from the `books` table, but use Aliases to rename the output columns to `Book Name` and `Written By`.

sql SELECT title AS 'Book Name', author AS 'Written By' FROM books; ``

• Q: Explain the purpose of a Column Alias (AS) in a SELECT query. Does utilizing an Alias permanently alter the structural schema of the underlying database table?

• Q: Describe a scenario where a Data Analyst would rely heavily on the DISTINCT keyword. What data problem does it solve?