10+ Must-Know SQL Queries for Beginners: A Complete Guide to Mastering Databases

SQL Queries Foundation

In our previous post on What is SQL, we discussed the importance of SQL in managing relational databases. Now, we will dive into the foundation of SQL queries, covering key elements like SELECT, WHERE, ORDER BY, and introducing the powerful SELECT DISTINCT statement. These components form the core of retrieving, filtering, and organizing data efficiently.

1. Retrieving Data with the SELECT Statement

The SELECT statement is used to extract data from tables. It allows you to select specific columns or entire rows, making it essential for any SQL operation.

Syntax of the SELECT Statement

SELECT column1, column2, ...
FROM table_name;

• column1, column2: Columns to be retrieved.
• table_name: The name of the table where the data resides.

Retrieve Specific Columns

This query retrieves only the employee ID, first name, last name, and department from the employees table.

SELECT employee_id, first_name, last_name, department 
FROM employees;

Retrieve All Columns

SELECT * 
FROM employees;

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

Using Aliases to Rename Columns

SELECT employee_id AS ID, first_name AS "First Name" 
FROM employees;

Here, we use AS to assign aliases, renaming the employee_id column as ID and first_name as “First Name” in the output.

2. Using SELECT DISTINCT to Avoid Duplicates

The SELECT DISTINCT statement is used to retrieve unique values from a column, eliminating duplicate records from the result set.

Syntax of SELECT DISTINCT

sqlCopy codeSELECT DISTINCT column1, column2, ...
FROM table_name;

• DISTINCT ensures that each combination of selected columns appears only once in the result.

Retrieve Unique Departments

SELECT DISTINCT department 
FROM employees;

This query retrieves the list of all unique departments in the employees table, even if multiple employees belong to the same department.

Retrieve Unique Combinations of Columns

SELECT DISTINCT department, job_title 
FROM employees;

This query returns unique combinations of departments and job titles, ensuring that each department-title pair appears only once.

Use Case: Why Use SELECT DISTINCT?

• Remove duplicates from reports and summaries.
• Get a unique list of categories, locations, or products from a large dataset.
• Ensure data quality by verifying that there are no redundant entries.

Difference Between SELECT and DISTINCT SELECT in SQL

In SQL, the SELECT command retrieves all matching rows from specified columns, including duplicates, whereas SELECT DISTINCT ensures unique rows are returned by removing repetitions. Use DISTINCT SELECT when non-redundant results, such as a list of unique departments, are required.

3. Filtering Rows with the WHERE Clause

The WHERE clause filters records based on specific conditions, making it easier to extract relevant data from large datasets.

Syntax of WHERE Clause

SELECT column1, column2 
FROM table_name 
WHERE condition;

• condition: The logical expression that determines which rows to include.

Simple Filter

SELECT * 
FROM employees 
WHERE department = 'Sales';

This query retrieves all employees from the Sales department.

Comparison Operators

SELECT first_name, salary 
FROM employees 
WHERE salary >= 50000;

This query retrieves employees whose salary is 50,000 or more.

Using AND/OR Conditions

SELECT first_name, last_name, department 
FROM employees 
WHERE department = 'IT' AND salary > 60000;

This query retrieves employees from the IT department with salaries greater than 60,000.

SELECT first_name, last_name 
FROM employees 
WHERE department = 'HR' OR department = 'Finance';

This query retrieves employees from either the HR or Finance department.

Using BETWEEN Operator

SELECT first_name, salary 
FROM employees 
WHERE salary BETWEEN 30000 AND 60000;

This query retrieves employees earning between 30,000 and 60,000.

Using IN Operator

SELECT first_name, department 
FROM employees 
WHERE department IN ('Sales', 'Marketing', 'IT');

This query retrieves employees who belong to either Sales, Marketing, or IT.

4. Sorting Results with ORDER BY Clause

The ORDER BY clause sorts the result set based on one or more columns in ascending or descending order.

Syntax of ORDER BY Clause

SELECT column1, column2 
FROM table_name 
ORDER BY column1 [ASC|DESC];

• ASC: Sorts the results in ascending order (default).
• DESC: Sorts the results in descending order.

Sort by a Single Column (Ascending)

SELECT first_name, salary 
FROM employees 
ORDER BY salary;

This query sorts employees by their salary in ascending order.

Sort by a Single Column (Descending)

SELECT first_name, salary 
FROM employees 
ORDER BY salary DESC;

This query sorts employees by their salary in descending order.

Sort by Multiple Columns

SELECT first_name, department, salary 
FROM employees 
ORDER BY department ASC, salary DESC;

This query sorts employees first by department in alphabetical order and, within each department, by salary in descending order.

4. Combining SELECT, WHERE, and ORDER BY Clauses

By combining the SELECT statement with WHERE and ORDER BY, you can retrieve filtered data and display it in an organized way.

Example: Filter and Sort Employees by Department

SELECT first_name, department, salary 
FROM employees 
WHERE department = 'Sales' 
ORDER BY salary DESC;

This query retrieves employees from the Sales department, sorted by salary in descending order.

Example: Retrieve and Sort Employees by Job Title and Salary

SELECT first_name, job_title, salary 
FROM employees 
WHERE salary BETWEEN 40000 AND 80000 
ORDER BY job_title, salary;

This query retrieves employees earning between 40,000 and 80,000, sorted first by job title and then by salary.

Example: Retrieve Unique Departments with Salary Condition

SELECT DISTINCT department 
FROM employees 
WHERE salary > 50000 
ORDER BY department;

This query retrieves unique departments with employees earning more than 50,000, sorted alphabetically.

Example: Filter Employees and Sort by Joining Date

SELECT first_name, last_name, joining_date 
FROM employees 
WHERE department = 'IT' 
ORDER BY joining_date;

This query retrieves employees from the IT department and sorts them by their joining date.

Example: Retrieve and Sort Employees with Multiple Filters

SELECT first_name, last_name, department, salary 
FROM employees 
WHERE department IN ('Finance', 'HR') AND salary > 60000 
ORDER BY department, salary DESC;

This query retrieves employees from Finance and HR departments earning more than 60,000, sorted first by department and then by salary in descending or

Conclusion

In this post, we explored the essential building blocks of SQL queries: SELECT, WHERE, ORDER BY, and SELECT DISTINCT. These commands help you retrieve, filter, sort, and eliminate duplicates from your data. Mastering these operations will make you proficient in querying relational databases and prepare you for more advanced SQL topics.

In our next blog, we’ll explore data types, operators, and functions in SQL for optimized database management.

FAQs