15 Most Common SQL Queries with Examples

SQL is a must-have skill for every developer or tech professional to handle large amounts of data. To handle this data, you need to do it efficiently and effectively. SQL Query is one such way to work on these data and update them based on the requirements.

In this blog, let’s look at the 15 most common SQL queries with examples. The examples given with these SQL queries will help you to get a clear understanding. These queries are going to help you in dealing with a huge amount of data in minutes. Let’s read about SQL initially and then move into the common SQL Queries with Examples.

What is SQL?

SQL stands for Structured Query Language. It is a domain-specific language used for managing and manipulating relational databases. SQL is essential for interacting with databases, and it provides a standardized way to perform tasks such as querying data, updating data, inserting data, and deleting data in a relational database management system (RDBMS).

Key components of SQL include:

1. Data Definition Language (DDL): DDL is used to define and manage the structure of a database.

DDL Commands:

• CREATE (creates a table)
• ALTER (adds, deletes, modifies, and renames the attributes of the relation)
• DROP (deletes the data)

2. Data Manipulation Language (DML): DML is used for manipulating data within the database.

DML Commands:

• SELECT (retrieve data)
• INSERT (add new data)
• UPDATE (modify existing data)
• DELETE (remove data)

3. Data Query Language (DQL): DQL is a subset of DML focused on retrieving data.

DQL Commands:

• SELECT (used to query the database and retrieve specific information)

4. Data Control Language (DCL): DCL is used to control access to data within the database. Commands like GRANT and REVOKE are used to assign and revoke permissions, determining who can access or modify certain data.

DCL Commands:

• GRANT (used for permitting the users)
• REVOKE (used for removing the authorization)

5. Transaction Control Language (TCL): TCL is used to manage transactions within a database.

TCL Commands:

• COMMIT (to save changes)
• ROLLBACK (to undo changes)

Before we move to the next part, you should have a deeper knowledge of business analytics concepts. You can consider enrolling yourself in GUVI’s Business Analytics and Digital Marketing Career Program, which lets you gain practical experience by developing real-world projects and covers technologies including Power BI, Excel, SQL, Tableau, Data Visualization, etc. 

Additionally, if you would like to explore Marketing Research Techniques through a Self Paced course, try GUVI’s Marketing Research Techniques Self-Paced certification course.

Some of the Most Common SQL Queries

Also Read | How To Learn SQL Using Squid Games?

15 Most Common SQL Queries

Let’s now explore the most common SQL queries by developers:

1. SELECT Statement

The SELECT statement in SQL is used to retrieve data from one or more tables in a database. It is a fundamental and powerful command that allows you to query and display information stored in a relational database.

Syntax: SELECT column1, column2, ... FROM table_name WHERE condition;

Example:

1. SELECT * FROM employees; (* gives you the complete data of the table).

2. SELECT first_name, last_name FROM employees; (gives you all the data of the first name and last name from the table employees).

2. SELECT DISTINCT

The SELECT DISTINCT statement is used to retrieve unique values from a specific column in a table. It eliminates duplicate rows and returns only distinct values.

Syntax: SELECT DISTINCT column1, column2, ... FROM table_name WHERE condition;

Example:

1. SELECT DISTINCT first_name, last_name FROM students; (gives you the unique values of the first name and last name from the table students).

3. WHERE Clause

The WHERE clause in SQL is used to filter the rows returned by a SELECT statement based on a specified condition. It allows you to retrieve only the rows that meet certain criteria.

Syntax: SELECT column1, column2, … FROM table_name WHERE condition;

Example:

1. SELECT * FROM students WHERE age > 20; (gives you the data of students whose age is above 20).

4. ORDER BY Clause

The ORDER BY clause in SQL is used to sort the result set of a SELECT statement based on one or more columns. It allows you to specify the order in which the rows should be displayed, either in ascending (default) or descending order.

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

Example:

1. SELECT * FROM students ORDER BY age; (organizes the data of the table by age in ascending/descending order).

5. LIMIT and OFFSET

The LIMIT and OFFSET clauses in SQL are used to control the number of rows returned by a SELECT statement, allowing you to retrieve a specific subset of the result set. These clauses are often used together to implement pagination or to fetch a limited number of rows starting from a certain position.

Syntax: SELECT column1, column2, … FROM table_name LIMIT number_of_rows OFFSET offset_value;

Example:

1. SELECT * FROM students LIMIT 2 OFFSET 2; (the LIMIT 2 restricts the result set to two rows, and the OFFSET 2 skips the first two rows.)

6. INSERT Statement

The INSERT statement in SQL is used to insert new records (rows) into a table. It adds data to an existing table by specifying the values to be inserted into each column.

Syntax: INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);

Example:

1. INSERT INTO students (student_id, first_name, last_name, age, grade) VALUES (7, ‘Alice’, ‘Johnson’, 21, ‘B’); (adds a new student to the students’ table with the required details).

7. UPDATE Statement

The UPDATE statement in SQL is used to modify existing records (rows) in a table. It allows you to change the values of one or more columns in the specified rows based on a specified condition.

Syntax: UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition;

Example:

1. UPDATE students SET age = 20, grade = ‘B’ WHERE student_id = 3; (update the students’ table with the student’s age as 20, grade as B where id is 3).

8. DELETE Statement

The DELETE statement in SQL is used to remove records (rows) from a table based on a specified condition. It permanently removes data from the table.

Syntax: DELETE FROM table_name WHERE condition;

Example:

1. DELETE FROM students WHERE student_id = 4; (deletes the entire data of student whose id is 4 from the table students).

9. COUNT() Function

The COUNT function in SQL is used to count the number of rows in a table or the number of rows that satisfy a particular condition in a specified column.

Syntax: SELECT COUNT(*) FROM table_name WHERE condition;

Example:

1. SELECT COUNT(*) FROM students; (displays the count of total number of students from the table students).

10. SUM() Function

The SUM function in SQL is used to calculate the sum of values in a numeric column, typically within a specified group or for all rows in a table.

Syntax: SELECT SUM(column_name) FROM table_name WHERE condition;

Example:

1. SELECT SUM(amount) FROM sales; (displays the total amount from the sales table).

11. GROUP BY Clause

The GROUP BY clause in SQL is used to group rows that have the same values in specified columns into summary rows, like “total sales per product” or “average score per student.” It’s often used with aggregate functions like SUM, COUNT, AVG, etc.

Syntax: SELECT column1, column2, …, aggregate_function(column) FROM table_name WHERE condition GROUP BY column1, column2, …;

Example:

1. SELECT product, SUM(amount) as total_sales FROM sales GROUP BY product; (groups the data by the “product” column and displays the result.)

12. HAVING Clause

The HAVING clause in SQL is used in conjunction with the GROUP BY clause to filter the results of a grouped query. While the WHERE clause filters rows before grouping, the HAVING clause filters groups after the grouping has taken place. It is commonly used with aggregate functions like SUM, COUNT, AVG, etc.

Syntax: SELECT column1, column2, …, aggregate_function(column) FROM table_name WHERE condition GROUP BY column1, column2, … HAVING condition;

Example:

1. SELECT product, SUM(amount) as total_sales FROM sales GROUP BY product HAVING total_sales > 1000; (it initially sums up the sales amount and groups the data and then displays only that data which is greater than 1000).

13. JOIN Statements

In SQL, the JOIN statement is used to combine rows from two or more tables based on a related column between them.

There are different types of joins, including INNER JOIN, LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (or RIGHT OUTER JOIN), and FULL JOIN (or FULL OUTER JOIN).

Syntax: SELECT columns FROM table1 JOIN table2 ON table1.column = table2.column;

Example:

1. SELECT students.first_name, students.last_name, courses.course_name FROM students JOIN courses ON students.student_id = courses.course_id; (it combines rows from the “students” and “courses” tables based on the relationship defined in the ON clause.)

14. Subqueries

A subquery, also known as a nested query or inner query, is a query embedded within another query. It can be used to retrieve data that will be used in the main query as a condition to further restrict the data to be retrieved. Subqueries can be used in various parts of a SQL statement, including the SELECT, FROM, WHERE, and HAVING clauses.

Syntax: SELECT column1, column2, … FROM table_name WHERE column_name operator (SELECT column_name FROM another_table WHERE condition);

Example:

1. SELECT first_name FROM students WHERE student_id IN (SELECT student_id FROM grades WHERE grade = ‘A’); (it finds the student_id values from the “grades” table where the grade is ‘A’. The main query then selects the first_name of students whose student_id is in the result set of the subquery.)

15. Views

A view is a virtual table based on the result of a SELECT query. It allows you to simplify complex queries, encapsulate logic, and present a subset or transformation of the data to users without exposing the underlying table structure.

Views are often used to enhance data security by restricting direct access to certain tables and providing a controlled way to access specific data.

Syntax: CREATE VIEW view_name AS SELECT column1, column2, … FROM table_name WHERE condition;

Example:

1. CREATE VIEW high_performers AS SELECT student_id, first_name, last_name, grade FROM students WHERE grade = ‘A’;

“high_performers” view is created, which includes the columns student_id, first_name, last_name, and grade from the “students” table. Only the rows where the grade is ‘A’ are included in the view.

If you want to get an in-depth understanding of SQL, register for this course – MySQL which has a recorded session and is available at a very affordable price.

Now that you have read some of the most common SQL queries, you must use them efficiently. To deal with the database queries, go through the Top SQL Interview Questions With Answers [2024], which is majorly asked in interviews.

Kickstart your career by enrolling in GUVI’s Business Analytics and Digital Marketing Career Program where you will master technologies including Power BI, Excel, SQL, Tableau, and Data Visualization, and build interesting real-life business-analytics projects.

Alternatively, if you want to explore Marketing Research Techniques through a Self Paced course, try GUVI’s Marketing Research Techniques Self Paced certification course.

Wrapping it Up

These top SQL queries will give you an idea of how to work with databases efficiently. There are many SQL queries but you can get started with these to kickstart your SQL journey. These SQL queries will help you to deal with databases, whether you’re a beginner or an experienced developer. You should get handy with these queries to enhance your SQL skills and become a proficient data professional.

Also Explore: MongoDB vs. MySQL: Which Database Should You Learn?

FAQs on SQL Queries

Q1. What is SQL?

Ans. SQL (Structured Query Language) is a standard programming language designed for managing and manipulating relational databases. It enables users to perform various operations such as querying data, updating records, inserting new data, and more.

Q2. Which databases use SQL?

Ans. SQL is used by various relational database management systems (RDBMS) such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server, and Oracle Database, among others.

Q3. How do I retrieve specific columns from a table?

Ans. You can use this query to retrieve specific columns from a table: SELECT column1, column2 FROM table;

Q4. What is a subquery?

Ans. A subquery is a query nested within another query. It can be used to retrieve data that will be used by the main query’s condition.
Example: SELECT column FROM table WHERE column IN (SELECT column FROM another_table);