This article will show some important questions and answers to practice SQL.
Introduction
SQL is the base to handle different databases. Even some of the NoSQL databases use an extension of SQL to query data. Data Scientists, BI Analysts, BI Developers, DBAs, Database Developers, Data Engineers, Reporting Analysts, and several other jobs require SQL knowledge as part of the knowledge required. For data management, SQL knowledge is the base to handle databases and the numbers of jobs related to databases are growing each day. The software industry, including databases, is the world’s most in-demand profession. That is why in this article, we will show some questions and answers to practice SQL.
Requirements
In order to start, you need the SQL Server installed and the Adventureworks sample database installed. The following links can help you if you do not have them installed:
- How to install SQL Server developer edition
- Install and configure the AdventureWorks2016 sample database
Q1. Please select all the information of persons table that the name starts with A
A1. You will show all the columns with the select * and the where [FirstName] like ‘a%’ will filter the data to all the persons that the name starts with a. The query used is the following:
|
1 2 3 4 5 6 |
SELECT * FROM [Person].[Person] WHERE [FirstName] LIKE 'a%' |
The LIKE operator is very common in SQL queries. The like ‘a%’ will show all the first names that start with the letter a. There are several other operators that you need to practice like the EXISTS, IN, =, <>, ANY. The following link provides more information about the operators:
Note that the use of * is a bad practice for big tables, but in this case, it is a small table, so it will not impact performance. In general, try to select only the columns required and not all.
Q2. Create a store procedure that receives the first name of the person table as input and the last name as output.
In SQL practice, it is necessary to mention the stored procedures because they are frequently used in SQL Server. The stored procedures are T-SQL sentences that cannot be applied to other databases like Oracle, MySQL, PostgreSQL. The following sample of code shows how to create a stored procedure named GetLastName. It receives the parameter @firstname and returns the last name of the provided firstname. It queries the Person table:
|
1 2 3 4 5 6 7 8 9 10 11 |
ALTER PROCEDURE GetLastName @firstname varchar(50), @lastname varchar(50) OUTPUT AS BEGIN SELECT @lastname=lastname FROM [Person].[Person] WHERE FirstName = @firstname END |
To call the stored procedure using T-SQL, you will need to declare a variable to store the output variable. We use the execute command to call a stored procedure. John is the parameter value for the first name and the last name is the output of the stored procedure:
|
1 2 3 4 5 |
DECLARE @mylastname nvarchar(50) execute dbo.GetLastName 'John', @mylastname out SELECT @mylastname as lastname |
The result displayed by this store procedure invocation will be something like this:
For more information about creating stored procedures, please refer to this link:
- Learn SQL: User-Defined Stored Procedures
- SQL Server stored procedures for beginners
- CREATE PROCEDURE (Transact-SQL)
Q3. Which query would you execute to delete all the rows from the person table with minimal bulk-logged activity?
A3. The truncate table sentence removes data without logging individual row deletions. It is the most efficient way to remove all the data. The delete command, on the other hand, logs a lot of data if we have multiple rows and can consume a lot of space in the transaction log files. So, the code will be the following:
Truncate table person.person
To practice SQL, try the truncate and delete statements. For more detailed information about the differences between the truncate and the delete sentences, refer to this link:
Q4. Create a query to show the account number and customerid from the customer table for the customer without sales orders.
A4. The query would be something like this:
|
1 2 3 4 5 6 7 8 |
SELECT c.[AccountNumber],c.CustomerID FROM [Sales].[Customer] c LEFT JOIN [Sales].[SalesOrderHeader] s ON c.customerid=s.customerid WHERE s.salesorderid IS NULL |
The result displayed will be something like this:
We are using the left join to look for all the customers without sales, so the salesorderid will be null. In SQL practice, you need to know the use of the different JOINS like the LEFT JOIN, RIGHT JOIN, CROSS JOIN. We created some articles about the different types of JOINs here. It is very important for you to check, practice, and study all the options:
We could also use the EXISTS or the IN operators instead of using the JOINS. The performance is different according to the scenario. The performance in queries is out of the scope of this SQL practice. However, we have an entire article about this topic here:
Q5. You have a table with some of the temperatures in Celsius of some patients. Create a function or a stored procedure to get the convert Fahrenheit to Celsius. Would you use a function or a stored procedure?
A5. Basically, the question here is to create a stored procedure or a function to convert from Celsius to Fahrenheit. If you already have a table with some rows in Celsius, the easiest option is to use a function. The function could be something like this:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE FUNCTION ConvertToCelcius(@Temperature decimal(14,8)) Returns Int AS BEGIN Declare @fahrenheit decimal(14,8) Set @fahrenheit = ((@Temperature * (9.0/5.0)) + 32) Return (@fahrenheit) END |
Invoking a function is easier than a stored procedure. For more information about the use of functions vs stored procedures, refer to this link:
Q6. Create a query to show the top 10 customerIDs of users with more Orders.
A6. For this practice test, we will use the TOP 10 to get the customer IDs with more orders. We will use the SUM to SUM the Order Quantity column. Note that for aggregated functions like the SUM, the alias is needed to define the column name in the query. Also, the Sum is grouped by the customer ID. Usually aggregated functions come with the GROUP BY clause. Finally, we are using the order by to order the result in descendant order:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT TOP 10 SUM([OrderQty]) as orderqty,CustomerID FROM [Sales].[SalesOrderHeader] sh INNER JOIN [Sales].[SalesOrderDetail] sd on sh.SalesOrderID=sd.salesorderid GROUP BY CustomerID ORDER BY SUM([OrderQty]) desc |
The result displayed will be something like this:
For more information about aggregated functions like the SUM, MAX, MIN, AVG, refer to this link:
Conclusion
In this article, we show different questions for SQL practice. We show some questions and answers to practice SQL and improve the knowledge. If you have more questions about this topic, do not hesitate to contact us.
He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023