SQL is a powerful tool for managing and manipulating data in a database.
In this blog post, we’ll explore the first three SQL commands and provide examples of how to use them effectively.
SQL Introduction: A guide to using SELECT, FROM and WHERE
1. USING SELECT
The SELECT command is one of the most basic and essential SQL commands. It is used to retrieve data from a database table.
Here are three examples of how to use the SELECT command:
Example 1: Retrieve all columns from a table
SELECT *
FROM customers;
This query retrieves all the columns from the customers table.
customer_id | first_name | last_name | phone | city | |
---|---|---|---|---|---|
1 | John | Doe | johndoe@xyz.com | 555-123-4567 | New York |
2 | Jane | Smith | janesmith@xyz.com | 555-234-5678 | Los Angeles |
3 | Bob | Johnson | bobjohnson@xyz.com | 555-345-6789 | Chicago |
4 | Mary | Brown | marybrown@xyz.com | 555-456-7890 | Houston |
Example 2: Retrieve specific columns from a table
SELECT first_name, last_name, email
FROM customers;
This query retrieves only the first name, last name, and email columns from the customers table.
first_name | last_name | |
---|---|---|
John | Doe | johndoe@xyz.com |
Jane | Smith | janesmith@xyz.com |
Bob | Johnson | bobjohnson@xyz.com |
Mary | Brown | marybrown@xyz.com |
Example 3: Retrieve data based on a condition
SELECT first_name, last_name, email
FROM customers;
WHERE first_name = 'John'
This query retrieves only the first name, last name, and email columns from the customers table where first name is John
first_name | last_name | |
---|---|---|
John | Doe | johndoe@xyz.com |
2. USING FROM
The FROM command is used to specify the table or tables from which data is to be retrieved.
Here are three examples of how to use the FROM command:
Example 1: Retrieve data from a single table
SELECT *
FROM customers;
This query retrieves all the columns from the customers table.
customer_id | first_name | last_name | phone | city | |
---|---|---|---|---|---|
1 | John | Doe | johndoe@xyz.com | 555-123-4567 | New York |
2 | Jane | Smith | janesmith@xyz.com | 555-234-5678 | Los Angeles |
3 | Bob | Johnson | bobjohnson@xyz.com | 555-345-6789 | Chicago |
4 | Mary | Brown | marybrown@xyz.com | 555-456-7890 | Houston |
Example 2: Retrieve data from multiple tables using JOIN
SELECT customers.first_name, orders.order_date
FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id;
This query retrieves the first name of each customer and the date of each order by joining the customers and orders tables on the customer_id column. Only customers who have made an order will be returned in the output.
first_name | order_date |
---|---|
John | 2022-01-02 |
Jane | 2022-02-05 |
Bob | 2022-02-22 |
Example 3: Retrieve data from a subquery
SELECT *
FROM (
SELECT first_name, last_name, city
FROM customers
WHERE city = 'New York'
) AS ny_customers;
This query retrieves the first name, last name and city of customers who live in New York by using a subquery to filter the data and then using the subquery as a table in the outer query.
first_name | last_name | city |
---|---|---|
John | Doe | New York |
3. USING WHERE
The WHERE command is used to filter rows based on a specified condition.
Here are three examples of how to use the WHERE command:
Example 1: Filter rows based on a single condition
SELECT *
FROM customers
WHERE city = 'Los Angeles';
This query retrieves all the columns from the customers table where the city is Los Angeles.
customer_id | first_name | last_name | phone | city | |
---|---|---|---|---|---|
2 | Jane | Smith | janesmith@xyz.com | 555-234-5678 | Los Angeles |
Example 2: Filter rows based on multiple conditions
SELECT *
FROM orders
WHERE order_date >= '2022-01-01'
AND total_amount > 1000;
This query retrieves all the columns from the orders table where the order date is on or after January 1, 2022, and the total amount is greater than 1000.
order_id | customer_id | order_date | total_amount |
---|---|---|---|
2 | 2 | 2022-02-05 | 399.99 |
Example 3: Filter rows based on a subquery
SELECT *
FROM orders
WHERE customer_id IN (
SELECT customer_id
FROM customers
WHERE city = 'New York'
);
This query retrieves all the columns from the orders table where the customer_id is in the list of customer_ids returned by the subquery, which filters the customers table to only those who live in New York.
order_id | customer_id | order_date | total_amount |
---|---|---|---|
1 | 1 | 2022-01-02 | 120.50 |
Conclusion
In conclusion, the SELECT, FROM, and WHERE commands are the foundation of SQL programming.
You can write simple to complex SQL queries and manipulate data effectively by mastering these commands.
0 Comments