SQL Queries for Practice

If your objective is to be a data administrator, database developer, or data analyst, then it is important to master SQL queries. If you want to work with structured data then you must know how to retrieve, handle, and analyze data from relational databases.

In this article, we will learn several SQL queries that will help you increase your querying abilities.

Following are the SQL queries:

1. Write the query to create a new table.

CREATE TABLE tableName (
 columnName datatype,
 columnName datatype,
  ...
);

2. Write the query to insert a new record into a table:

INSERT INTO tableName (Column1, Column2, ?)
VALUES (Value1, Value2, ?);

3. Write the query to delete records from a table:

4. Write the query to update records in a table:

UPDATE tableName
SET columnName = newValue
WHERE condition;

5. Write the query to delete a table from the database:

6. Write the query to select all records from a table to retrieve data:

7. Write the query to select the specific column from a table:

8. Write the query to select distinct values from a column:

9. Write the query to filter records using a WHERE clause:

10. Write the query to sort records in descending order:

SELECT * FROM tableName
ORDER BY columnName DESC;

11. Write the query to sort records in ascending order:

SELECT * FROM tableName
ORDER BY columnName ASC;

12. Write the query to join two tables based on a common column:

SELECT * FROM Table1 JOIN Table2
ON Table1.columnName = Table2.columnName;

13. Write the query to count the number of rows in a table.

We will use the COUNT() function to count the number of rows. The statement is given below:

14. Write the query to group records and calculate aggregate functions:

SELECT columnName, COUNT(*), AVG(columnName)
FROM tableName GROUP BY columnName;

15. Write the query to limit the number of rows returned in a result set.

We will use the LIMIT clause to limit the number of rows. The statement is given below:

16. Write the query to find the sum of values in a column:

17. Write the query to find the average value of a column:

18. Write the query to get the minimum value from a column:

19. Write the query to retrieve the maximum value from a column:

20. Write the query to retrieve rows with values within a specified range:

SELECT columnName FROM tableName
WHERE columnName
BETWEEN Value1 AND Value2;

21. Write the query to retrieve rows with values matching a list of specified values:

SELECT columnName FROM tableName
WHERE columnName
IN (Value1, Value2, Value3);

22. Write the query to search for patterns in a column using wildcard characters:

23. Write the query to filter data based on aggregate functions in a GROUP BY query:

SELECT Column1, COUNT(Column2)
FROM tableName
GROUP BY Column1 HAVING COUNT(Column2) > 5;

24. Write the query to retrieve rows with dates within a specified range:

SELECT columnName FROM tableName
WHERE columnName
BETWEEN '2023-01-01' AND '2023-07-30';

25. Write the query to combine the result sets of two or more SELECT statements:

SELECT * FROM Table1
UNION SELECT columnName FROM Table2;

26. Write the query to perform conditional logic in a query:

SELECT columnNamse, CASE WHEN Condition1 THEN 'Value1'
WHEN Condition2 THEN 'Value2'
ELSE 'Value3'
END AS newColumn FROM tableName;

27. Write the query to delete all rows from a table, but keeps the table structure:

28. Write the query to create an index on one or more columns of a table:

29. Write the query to modify the structure of an existing table:

30. Write the query to return the first non-null value from a list of expressions:

SELECT COALESCE(Column1, Column2, … , ColumnN) AS Result
FROM tableName;

31. Write the query to return null if two expressions are equal, otherwise, return the first expression.

We will use the NULLIF() function to do so, the statement is given below:

Real-life-based questions to practice SQL queries:

Let us look at some real-life based questions to practice the SQL queries discussed above:

1. Write the query to create a table named 'employees' with fields like empId, empName, empAge, empAddress, and empSalary.

Query:

CREATE TABLE employees (
          empId INT,
          empName VARCHAR(40),
          empAge INT,
          empAddress VARCHAR(40),
          empSalary INT
        );

Result: The 'employees' table will look like you can see below.

empId	empName	empAge	empAddress	empSalary

2. Write the query to insert values in the above 'employees' table.

Query:

INSERT INTO employees
VALUES(1, 'Daksh', 25, 'Jaipur', 32000),
(2, 'Ananya', 30, 'Bhopal', 37000),
(3, 'Aarush', 32, 'Jaipur', 35000),
(4, 'Reyansh', 30, 'Bhopal', 40000)
(5, 'Jhalak', 28, 'Jaipur', 39000);

Result: After inserting values in the 'employees' table, it will look like you can see below.

empId	empName	empAge	empAddress	empSalary
1	Daksh	25	Jaipur	32000
2	Ananya	30	Bhopal	37000
3	Aarush	32	Jaipur	35000
4	Reyansh	30	Bhopal	40000
5	Jhalak	28	Jaipur	39000

3. Consider the 'employees' table and write the query to delete record of 'empId=1' from the 'employees' table:

Query:

Result: The above statement will delete the record of 'empId=1' from the 'employees' table.

empId	empName	empAge	empAddress	empSalary
2	Ananya	30	Bhopal	37000
3	Aarush	32	Jaipur	35000
4	Reyansh	30	Bhopal	40000
5	Jhalak	28	Jaipur	39000

4. Consider the 'employees' table and write the query to update the age of 'empId=5' in the 'employees' table:

Query:

Result: The above statement will update the age of 'empId=5' in the 'employees' table.

empId	empName	empAge	empAddress	empSalary
1	Daksh	25	Jaipur	32000
2	Ananya	30	Bhopal	37000
3	Aarush	32	Jaipur	35000
4	Reyansh	30	Bhopal	40000
5	Jhalak	28	Jaipur	39000

5. Consider the 'employees' table and write the query to select all records from the 'employees' table:

Query:

Result: As you can see below, above statement will select all records from the 'employees' table.

empId	empName	empAge	empAddress	empSalary
1	Daksh	25	Jaipur	32000
2	Ananya	30	Bhopal	37000
3	Aarush	32	Jaipur	35000
4	Reyansh	30	Bhopal	40000
5	Jhalak	28	Jaipur	39000

6. Consider the 'employees' table and write the query to select the 'empName' and 'empSalary' columns from the 'employees' table:

Query:

Result: The above statement will select the 'empName' and 'empSalary' columns from the 'employees' table.

empName	empSalary
Daksh	32000
Ananya	37000
Aarush	35000
Reyansh	40000
Jhalak	39000

7. Consider the 'employees' table and write the query to select distinct values from the 'empAddress' column in the 'employees' table:

Query:

Result: The above statement will select the distinct value from the 'empAddress' column in the 'employees' table.

empAddress
Jaipur
Bhopal

8. Consider the 'employees' table and write the query to filter and select those records where empAddress='Jaipur' using a WHERE clause:

Query:

SELECT * FROM employees
WHERE empAddress='Jaipur';

Result: The above statement will filter the records from the 'employees' table.

empId	empName	empAge	empAddress	empSalary
1	Daksh	25	Jaipur	32000
3	Aarush	32	Jaipur	35000
5	Jhalak	28	Jaipur	39000

9. Consider the 'employees' table and write the query to sort records of the 'employees' table in descending order.

Query:

SELECT * FROM employees
ORDER BY empName DESC;

Result: The above statement will sort the records of the 'employees' table in descending order.

empId	empName	empAge	empAddress	empSalary
4	Reyansh	30	Bhopal	40000
5	Jhalak	28	Jaipur	39000
1	Daksh	25	Jaipur	32000
2	Ananya	30	Bhopal	37000
3	Aarush	32	Jaipur	35000

10. Consider the 'employees' table and write the query to sort records of the 'employees' table in ascending order.

Query:

SELECT * FROM employees
ORDER BY empName ASC;

Result: The above statement will sort the records of the 'employees' table in ascending order.

empId	empName	empAge	empAddress	empSalary
3	Aarush	32	Jaipur	35000
2	Ananya	30	Bhopal	37000
1	Daksh	25	Jaipur	32000
5	Jhalak	28	Jaipur	39000
4	Reyansh	30	Bhopal	40000

11. Consider the 'employees' table and write the query to count the number of rows in the 'employees' table.

Query:

Result: The above statement will count the number of rows in the 'employees' table.

COUNT(*)
5

12. Consider the 'employees' table and write the query to retrieve all employees whose salary is between 35000 and 40000 in the 'employees' table.

Query:

SELECT empSalary FROM employees
WHERE empSalary
BETWEEN 35000 AND 40000;

Result: The above statement will retrieve all employees whose salary is between 35000 and 40000 in the 'employees' table.

empId	empName	empAge	empAddress	empSalary
2	Ananya	30	Bhopal	37000
3	Aarush	32	Jaipur	35000
4	Reyansh	30	Bhopal	40000
5	Jhalak	28	Jaipur	39000

13. Consider the 'employees' table and write the query to add another column called 'Department'.

Query:

Result: As no value has been inserted in the new column 'Department' so by default it contains NULL.

empId	empName	empAge	empAddress	empSalary	Department
1	Daksh	25	Jaipur	32000	NULL
2	Ananya	30	Bhopal	37000	NULL
3	Aarush	32	Jaipur	35000	NULL
4	Reyansh	30	Bhopal	40000	NULL
5	Jhalak	28	Jaipur	39000	NULL

14. Consider a table called 'products' with fields such as product_id, product_name, product_quantity, and product_price.

product_id	product_name	product_quantity	product_price
1	Speaker	2	10000
2	Printer	1	21000
3	Key Board	5	2500
4	Refill Cartridge	6	1500
5	CD Drive	4	2000

i) Write the query to calculate the sum of values in the 'product_price' column in the 'products' table.

Query:

SELECT SUM(product_price)
FROM products;

Result:

SUM(product_price)

37000

ii) Write the query to calculate the average value of the 'product_price' column in the 'products' table.

Query:

SELECT AVG(product_price)
FROM products;

Result:

AVG(product_price)

7400

iii) Write the query to retrieve the minimum value from the 'product_price' column in the 'products' table.

Query:

SELECT MIN(product_price)
FROM products;

Result:

MIN(product_price)

1500

iv) Write the query to retrieve the maximum value from a 'product_price' column in the 'products' table.

Query:

SELECT MAX(product_price)
FROM products;

Result:

MAX(product_price)

21000

V) Write the query to group records and calculate aggregate functions for the 'product_price' column in the 'products' table.

Query:

SELECT product_price, COUNT(product_price), AVG(product_price)
FROM products GROUP BY product_price;

Result:

product_price	COUNT(product_price)	Age(product_price)
10000	1	10000
21000	1	21000
2500	1	2500
1500	1	1500
2000	1	2000

15. Consider a table called 'mobilephones' with fields such as Id, Name, Company, Quantity, and Price.

Id	Name	Company	Colour	Quantity	Price
1	Samsung Galaxy A23	Samsung	Blue	1	20000
2	iPhone 13 mini	Apple	Pink	2	65000
3	iPhone 12	Apple	Black	1	54000
4	Motorola Edge 30 Fusion	Motorola	Viva Magenta	2	38000
5	Samsung Galaxy Z Flip3 5G	Samsung	Black	4	48000

i) Write the query to select all customers from a 'Colour' column starting with "bl" in the 'mobilephones' table.

Query:

SELECT * FROM mobilephones
WHERE Colour LIKE 'bl%';

Result:

product_price	COUNT(product_price)	Age(product_price)
10000	1	10000
21000	1	21000
2500	1	2500
1500	1	1500
2000	1	2000

15. Consider a table called 'mobilephones' with fields such as Id, Name, Company, Quantity, and Price.

Id	Name	Company	Colour	Quantity	Price
1	Samsung Galaxy A23	Samsung	Blue	1	20000
3	iPhone 12	Apple	Black	1	54000
5	Samsung Galaxy Z Flip3 5G	Samsung	Black	4	48000

ii) Write the query to select all mobile names whose average is greater than 45000 from the 'Price' column in the 'mobilephones' table.

Query:

SELECT Name, AVG(Price)
FROM mobilephones
GROUP BY Name HAVING AVG(Price) > 45000;

Result:

Name	AVG(Price)
iPhone 13 mini	65000
iPhone 12	54000
Samsung Galaxy Z Flip3 5G	48000

iii) Write the query to select all mobile names whose sum is smaller than 45000 from a 'Price' column in the 'mobilephones' table.

Query:

SELECT Name, SUM(Price)
FROM mobilephones
GROUP BY Name HAVING SUM(Price) < 45000;

Result:

Name	SUM(Price)
Samsung Galaxy A23	20000
Motorola Edge 30 Fusion	38000

16. Consider the 'customers' table with Id, firstName, middleName, lastName, and Address.

Id	firstName	middleName	lastName	Address
1	Shruti	NULL	Bansal	Goa
2	NULL	Raj	Singh	Goa
3	Aadi	Kumar	NULL	Goa
4	Nishtha	NULL	NULL	Mumbai
5	NULL	NULL	Kapoor	Mumbai

i) Write the query to return the first non-null value from the firstName, middleName, and lastName columns.

Query:

SELECT Id, Address COALESCE(firstName, middleName, lastName) AS NAME
FROM customers;

Result:

Id	Address	NAME
1	Goa	Shruti
2	Goa	Raj
3	Goa	Aadi
4	Mumbai	Nishtha
5	Mumbai	Kapoor

ii) Write the query to delete all rows from a 'customers' table, but keeps the table structure.

Query:

Result: As you can see, the above statement has deleted all rows but left with only the table structure.

Id	firstName	middleName	lastName	Address

17. Consider the two tables: one is the 'teachers' table which has fields such as teachers_id, Name, teachers_age, and teachers_address. The other is the 'students' table which has fields such as students_id, Name, students_age, and students_address.

The following is the 'teachers' table:

teachers_id	Name	teachers_age	teachers_address
100	Karishma	35	Noida
101	Yasha	29	Meerut
102	Kartik	40	Noida
103	Milan	36	Lucknow

The following is the 'students' table:

students_id	Name	students_age	students_address
200	Harsh	19	Noida
201	Palak	18	Lucknow
202	Himanshi	20	Roorkee
203	Ansh	19	Roorkee

i) Write the query to combine the 'Name' column in the 'teachers' table with the 'Name' column in the 'students' table using the UNION operator.

Query:

SELECT Name FROM teachers
UNION SELECT Name FROM students;

Result:

Name
Karishma
Yasha
Kartik
Milan
Harsh
Palak
Himanshi
Ansh

ii) Write the query to find the teacher's name whose age is between 34 and 41 in the 'teachers' table.

Query:

SELECT * FROM teachers
WHERE teachers_age 
BETWEEN '34' AND '41';

Result:

teachers_id	Name	teachers_age	teachers_address
100	Karishma	35	Noida
102	Kartik	40	Noida
103	Milan	36	Lucknow

Conclusion:

In this article, you have learned about several SQL queries for practice. You have read various real-life-based questions and used SQL queries to solve them. Practicing SQL queries is important if you want to become proficient at managing and retrieving data from a database.

With regular practice, you can understand SQL syntax properly, and improve query optimization techniques. You can easily identify and correct errors. If you master SQL queries, you will be able to extract valuable insights from databases and make data-driven decisions that will help advance your career.

Next TopicNested Query In SQL

← prev next →