12.2.1. SELECT – Retrieve Data

SELECT first_name, email FROM users;

In SQL, the SELECT statement is used to retrieve data from a database. In the example provided, the query selects two specific columns, first_name and email, from the users table. This means that the results will display only the first names and email addresses of all users stored in that table.

Think of a SELECT query like ordering a specific item from a menu at a restaurant. Just as you might order a cheeseburger instead of looking at all the items on the menu, you specify which columns you want from the database instead of retrieving everything.

SELECT * FROM orders WHERE status = 'pending';

The WHERE clause filters results returned by the SELECT statement. In the provided example, it fetches all records from the orders table that have a status of 'pending'. The * means that it retrieves all columns for those specific rows that match the condition.

Imagine you're at a library looking for a specific book. Instead of searching through every book, you use a search tool to filter results by author name or genre. Similarly, the WHERE clause allows you to focus on specific records that meet certain criteria in the database.

SELECT orders.id, users.name
FROM orders
JOIN users ON orders.user_id = users.id;

The JOIN operation merges records from two tables based on a related column. In this query, it selects the order ID and the name of the user who placed the order, combining data from the orders and users tables where the user_id matches the id in the users table.

Think of JOIN as a collaboration between two departments in a company. For example, the orders department needs information from the customer service department. By querying both departments' records together, they can get a comprehensive view of who ordered what.

SELECT status, COUNT(*) FROM orders GROUP BY status;

The GROUP BY statement is used to group rows that have the same values in specified columns into summary rows. This query counts the number of orders for each order status (like 'pending', 'completed', etc.), providing insights into the distribution of different order states in the database.

Imagine you're a teacher collecting homework assignments and you want to know how many students submitted on time versus late. You can group the submissions by 'on-time' and 'late', which helps you see the overall performance at a glance.

SELECT * FROM users ORDER BY created_at DESC;

The ORDER BY clause is used to sort the retrieved records. In this example, it sorts all user records from the users table in descending order based on the created_at timestamp, meaning the most recently created user records will appear first.

Think of sorting your email inbox. If you arrange your messages by the date they were received, with the newest ones at the top, it becomes easier to see the latest communications. Similarly, ORDER BY helps you organize data efficiently.

SELECT * FROM users WHERE email LIKE '%@gmail.com';

The LIKE operator is used in the WHERE clause to search for a specified pattern in a column. In this case, the query retrieves all users with an email address that ends in '@gmail.com'. The '%' symbol acts as a wildcard, allowing for any characters to appear before that domain.

Imagine you're looking for all the books in a library that have 'science' in their title. Instead of checking each book individually, you search using a keyword filter. LIKE works in a similar way, allowing you to find records in a database that match a specific format or pattern.

--