10 Examples of SELECT Queries in SQL

Hello guys, if you are new to SQL or learning SQL to become a programmer or data scientist then the first thing you should start with SELECT query. I know that many people will say that your first need to know how to create a database, a table, and learn about INSERT,  UPDATE, and DELETE but I suggest you to learn SELECT first. Why? because in 90% cases you will not create new tables, or database but you will be using SELECT queries to retrieve data from existing tables, and I can say from my 20 years of experience in programming and Software development. How to learn SELECT query? Well, the best way is to just execute queries and learn then create requirement like how to find employees whose salary greater than or less than certain amount etc. Once you reach that level, you are good to start with advanced SELECT concepts.

In this article, I Will show you 10 examples of SELECT query from basic to advanced level so not just read but open up your SQL editor and start typing this query. Most of the database comes with sample tables but if you don't find any then you can also create using GUI editors like MySQL workbench or SQL Server Management studio or even import data from CSV file.

10 Examples of SELECT query in SQL

Most of the Structured Query Language (SQL) is a powerful tool for interacting with relational databases. Among its fundamental operations is the SELECT statement, used to retrieve data from one or more tables. In this article, we'll explore 10 examples of SELECT queries that showcase the versatility and capabilities of SQL.

1. Basic SELECT

The simplest SELECT query retrieves all columns from a specific table.

SELECT * FROM employees;

This query fetches all rows and columns from the "employees" table.




2. Selecting Specific Columns

You can choose specific columns to retrieve rather than fetching all.

SELECT employee_id, first_name, last_name FROM employees;

This query retrieves only the "employee_id," "first_name," and "last_name" columns from the "employees" table.




3. Filtering with WHERE

The WHERE clause allows you to filter data based on specific conditions.

SELECT * FROM orders WHERE order_date >= '2024-01-01';

This query retrieves orders made after January 1, 2024.




4. Sorting with ORDER BY

The ORDER BY clause is used to sort the result set based on one or more columns.

SELECT product_name, unit_price FROM products ORDER BY unit_price DESC;

This query retrieves product names and prices, sorted in descending order based on unit price.




5. Limiting Results with LIMIT:

LIMIT is useful for restricting the number of rows returned. Not All database support limit but MySQL does. 

SELECT * FROM customers LIMIT 10;

This query retrieves the first 10 rows from the "customers" table.




6. Aggregating Data with GROUP BY

GROUP BY is used to aggregate data based on certain columns.

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

This query calculates the average salary for each department.




7. Filtering Aggregated Data with HAVING

HAVING is used in conjunction with GROUP BY to filter aggregated data.

SELECT department_id, AVG(salary) FROM employees 
GROUP BY department_id HAVING AVG(salary) > 50000;

This query retrieves departments with an average salary greater than 50,000.




8. Joining Tables

JOIN allows you to combine rows from two or more tables based on related columns.

SELECT employees.employee_id, employees.first_name, departments.department_name

FROM employees

INNER JOIN departments ON employees.department_id = departments.department_id;

This query retrieves employee names along with their corresponding department names by joining the "employees" and "departments" tables.




9. Using Aliases

Aliases can be used to provide more readable output.

SELECT first_name AS "First Name", last_name AS "Last Name" FROM employees;

This query retrieves employee first and last names with customized column aliases.



10. Subqueries

Subqueries allow you to nest one query inside another.

SELECT product_name FROM products 
WHERE category_id 
IN (SELECT category_id FROM categories 
WHERE category_name = 'Electronics');

This query retrieves product names from the "products" table where the category is 'Electronics,' using a subquery to find the corresponding category_id.

And, here is a nice table to summarize all the 10 examples of SELECT clause I have shared in this article.

That's all about 10 examples of SELECT clause in SQL. In conclusion, SQL's SELECT statement provides a powerful set of tools for retrieving and manipulating data from relational databases. These 10 examples showcase the flexibility and richness of the language, from basic retrieval to advanced operations involving filtering, sorting, grouping, and joining multiple tables. Mastering these techniques is essential for effective database management and data analysis.

If you like this kind of article with examples do let me know and I can share more of these. if you don't like or want to see any improvement please leave comment and I will try my best to improve the article.