12.2. Basic SQL Queries for QA

SELECT first_name, email FROM users;

The SELECT statement is used in SQL to retrieve data from a database. In the example given, we are fetching two specific columns - 'first_name' and 'email' from the 'users' table. This allows us to obtain only the data we need rather than retrieving all columns from the table, which might be unnecessary and inefficient.

Imagine you are in a library. Instead of asking for every book in the library, you ask only for books on a specific subject. Similarly, using SELECT helps you fetch only the necessary information from a database.

SELECT * FROM orders WHERE status = 'pending';

The WHERE clause filters results based on specified conditions. In this case, we are selecting all records from the 'orders' table where the status is 'pending'. This allows us to narrow down our results to only the orders that are still waiting to be processed.

Think of it as a grocery list. If you only want to buy fruits, you filter out all other items. The WHERE clause does the same for database queries, allowing you to focus on specific records.

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

The JOIN clause is used to combine records from two or more tables based on a related column between them. In this example, we are joining the 'orders' table with the 'users' table to retrieve the order ID along with the corresponding user's name by matching the user IDs.

Consider it like connecting dots between two separate maps. Just like how you can find a location on one map by referring to another, JOIN helps you bring together information from different tables for a complete picture.

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

The GROUP BY clause aggregates results based on one or more columns. Here, we're counting how many orders exist for each status (e.g., 'pending', 'completed'). This is helpful for summarizing data into meaningful statistics.

Imagine categorizing books by genre in a library. You group all fiction books together, count how many there are, and then do the same for other genres. GROUP BY helps in creating similar summaries in databases.

SELECT * FROM users ORDER BY created_at DESC;

The ORDER BY clause sorts the results based on one or more columns. In this case, we are sorting the users by the 'created_at' column in descending order, meaning the most recently created records will appear first.

Think about how you organize your email inbox. You might want to see the most recent emails at the top. ORDER BY does precisely this for your query results, helping you find relevant data quickly.

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

The LIKE operator is used for pattern matching in SQL. In this example, we are retrieving users whose email addresses end with '@gmail.com'. The '%' symbol is a wildcard that stands for any sequence of characters.

It's like searching for all books that contain the word 'adventure' in their title. You can use the word 'adventure%' to find titles that start with 'adventure' or '%adventure' to find ones that end with 'adventure'. LIKE helps you filter based on patterns.

--