SQL (Structured Query Language) is essential
for data analysts to extract and manipulate data efficiently. Below are some
fundamental SQL queries that every data analyst should know:
1. SELECT
Statement
The SELECT statement is the backbone of SQL, used to retrieve data from one or
more tables.
sql
Copy code
SELECT
column1, column2
FROM
table_name;
- Example: To get customer names and emails from a
customer table:
sql
Copy code
SELECT
name, email
FROM
customers;
2. WHERE
Clause
Used to filter records based on specific
conditions.
sql
Copy code
SELECT
column1, column2
FROM
table_name
WHERE
condition;
- Example: To find customers from 'New York':
sql
Copy code
SELECT
name, email
FROM
customers
WHERE city
= 'New York';
3. JOIN
Statements
Joins are used to combine rows from two or
more tables based on related columns.
- INNER JOIN:
Returns records that have matching values in both tables.
sql
Copy code
SELECT
a.column1, b.column2
FROM table1
a
INNER JOIN
table2 b
ON a.id =
b.id;
- Example: To list orders with
customer names:
sql
Copy code
SELECT
orders.id, customers.name
FROM orders
INNER JOIN
customers
ON
orders.customer_id = customers.id;
- LEFT JOIN: Returns all records from the left table
and matched records from the right table.
sql
Copy code
SELECT
a.column1, b.column2
FROM table1
a
LEFT JOIN
table2 b
ON a.id =
b.id;
4. GROUP BY
Clause
Groups rows that share a property and
aggregates data (often with functions like COUNT, SUM, AVG).
sql
Copy code
SELECT
column1, COUNT(*)
FROM
table_name
GROUP BY
column1;
- Example: To count the number of orders per
customer:
sql
Copy code
SELECT
customer_id, COUNT(*)
FROM orders
GROUP BY
customer_id;
5. HAVING
Clause
Used to filter groups after aggregation
(similar to WHERE but works
with aggregates).
sql
Copy code
SELECT
column1, COUNT(*)
FROM
table_name
GROUP BY
column1
HAVING
COUNT(*) > 1;
- Example: To find customers with more than one
order:
sql
Copy code
SELECT
customer_id, COUNT(*)
FROM orders
GROUP BY
customer_id
HAVING
COUNT(*) > 1;
6. ORDER BY
Clause
Sorts the result set by one or more columns,
in ascending or descending order.
sql
Copy code
SELECT
column1, column2
FROM
table_name
ORDER BY
column1 DESC;
- Example: To list the highest priced products:
sql
Copy code
SELECT
name, price
FROM
products
ORDER BY
price DESC;
7. LIMIT Clause
Limits the number of rows returned in a query.
sql
Copy code
SELECT
column1
FROM
table_name
LIMIT 10;
- Example: To get the first 5 customers:
sql
Copy code
SELECT name
FROM
customers
LIMIT 5;
8. UPDATE
Statement
Updates existing records in a table.
sql
Copy code
UPDATE
table_name
SET column1
= value1
WHERE
condition;
- Example: To change the email of a customer:
sql
Copy code
UPDATE
customers
SET email =
'newemail@example.com'
WHERE id =
1;
9. DELETE
Statement
Deletes records from a table.
sql
Copy code
DELETE FROM
table_name
WHERE
condition;
- Example: To delete orders older than 2023:
sql
Copy code
DELETE FROM
orders
WHERE order_date < '2023-01-01';
10.
Subqueries
A query nested inside another query, used when
filtering or combining results.
sql
Copy code
SELECT
column1
FROM
table_name
WHERE
column2 = (SELECT column2 FROM other_table WHERE condition);
- Example: To find customers who placed orders
worth more than $500:
sql
Copy code
SELECT name
FROM
customers
WHERE id IN
(SELECT customer_id FROM orders WHERE total > 500);
11. CASE
Statement
Used for conditional logic in queries.
sql
Copy code
SELECT
column1,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result3
END
FROM
table_name;
- Example: To categorize customers based on
spending:
sql
Copy code
SELECT
name,
CASE
WHEN total_spent > 1000 THEN
'Premium'
ELSE 'Regular'
END AS customer_type
FROM
customers;
Conclusion
Mastering these SQL queries will empower data
analysts to handle complex data extraction, manipulation, and reporting tasks
efficiently.
0 Comments