Structured Query Language (SQL) is a powerful tool used to manage and manipulate relational databases. SQL skills are in high demand, and being able to answer SQL interview questions can be the key to landing your dream job as a data analyst, database administrator, or software developer. In this blog post, we’ll cover the top 10 SQL interview questions that you should be prepared to answer in order to impress your interviewer and land your dream job.
What is a primary key and why is it important?
A primary key is a unique identifier for each record in a database table. It is a column or a combination of columns that uniquely identify a row in a table. The primary key is used to enforce data integrity, which ensures that each record in the table is unique and can be identified without ambiguity.
Primary keys are important for several reasons:
Uniqueness, Data Integrity, Indexing and Referential Integrity
Overall, primary keys are an essential component of database design and are critical for ensuring data integrity, consistency, and accuracy.
What is a foreign key and how does it relate to a primary key?
A foreign key is a column or set of columns in a table that refers to the primary key of another table. It establishes a relationship between two tables by creating a link between the data in the tables.
The foreign key ensures that the data in the referencing table matches the data in the referenced table. In other words, it ensures that the values in the referencing table exist in the referenced table. This relationship helps to maintain referential integrity between the tables, which ensures that the data is consistent and accurate.
Thus, foreign keys and primary keys are closely related, with foreign keys referencing primary keys to establish relationships between tables in a database.
What is the difference between a JOIN and a UNION in SQL?
JOIN and UNION are two SQL operations used to combine data from multiple tables or queries. JOIN is used to combine columns from two or more tables based on a common column, while UNION is used to combine the results of two or more SELECT statements into a single result set.
JOIN returns a single result set that combines columns from multiple tables based on a common column, while UNION returns a single result set that combines the results of multiple SELECT statements with compatible columns.
JOIN combines columns with matching data types, while UNION combines columns with compatible data types.
JOIN returns all rows that match the join condition, including duplicates, while UNION eliminates duplicate rows from the combined result set by default.
How do you select the top N rows from a table?
You can use the SELECT statement with the LIMIT clause. The LIMIT clause is used to limit the number of rows returned by a query.
To select the top N rows, you can specify the number N after the LIMIT keyword. For example, the query “SELECT * FROM table_name LIMIT N” will return the first N rows from the table.
You can also use the ORDER BY clause to specify the column to order the results by, and use the DESC keyword to order the results in descending order. For example, the query “SELECT * FROM table_name ORDER BY column_name DESC LIMIT N” will return the top N rows in descending order based on the specified column.
How do you delete duplicate rows in a table?
You can use the DELETE statement with a subquery. The subquery identifies the duplicate rows by grouping the rows by the columns that you want to use to identify duplicates, and then counting the number of rows in each group. The DELETE statement then deletes all but one row from each group of duplicates.
Here’s an example SQL query to delete duplicate rows from a table:
DELETE FROM table_name
WHERE column_name NOT IN (
SELECT MIN(column_name)
FROM table_name
GROUP BY column1, column2, …
);
What is the difference between a subquery and a join?
Subqueries are used to retrieve data from one table based on data from another table, while joins are used to combine data from multiple tables into a single result set. Subqueries can be less efficient than joins and are typically used for more complex data retrieval tasks, while joins are simpler and more efficient for combining data based on common columns.
How do you use GROUP BY and HAVING in a query?
GROUP BY and HAVING are SQL clauses that are used to perform aggregate functions on groups of rows in a table. Here’s an example of how to use GROUP BY and HAVING in a query:
Suppose we have a table named “orders” that contains information about customer orders, including the customer ID, order ID, and order amount. We want to find the total order amount for each customer who has placed orders totaling more than $1000.
We can use the GROUP BY and HAVING clauses to perform this query. Here’s the SQL code:
SELECT customer_id, SUM(order_amount) as total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(order_amount) > 1000;
What is a view in SQL and how do you create one?
A view in SQL is a virtual table that is based on the result of a SELECT statement. Views do not store data themselves, but instead provide a way to retrieve data from one or more tables using a customized query.
Views are useful for simplifying complex queries, providing controlled access to data, and improving the performance of queries by pre-calculating complex joins or aggregations.
To create a view in SQL, you use the CREATE VIEW statement followed by the SELECT statement that defines the view. Here is the basic syntax for creating a view:
CREATE VIEW view_name AS
SELECT column1, column2, …
FROM table_name
WHERE condition;
How do you use the LIKE operator to search for specific values in a table?
The LIKE operator in SQL is used to search for specific patterns or values in a column of a table. The LIKE operator can be used with wildcard characters to search for values that match a certain pattern.
Here’s an example of how to use the LIKE operator to search for specific values in a table:
Suppose we have a table named “products” that contains information about different products, including the product name and description. We want to search for all products that have the word “phone” in their name or description.
We can use the LIKE operator with the “%” wildcard character to perform this query. Here’s the SQL code:
SELECT *
FROM products
WHERE name LIKE ‘%phone%’ OR description LIKE ‘%phone%’;
What is the difference between the WHERE and HAVING clauses in SQL?
WHERE clause is used to filter rows based on individual column values before aggregation, while the HAVING clause is used to filter groups based on aggregate values after aggregation.