If you want to work with databases, you are expected to know the basics of SQL. Here is a compilation of SQL interview questions that you are likely to face. Go through these as well as other common SQL topics in order to impress the interview panel. You will be tested both on your SQL knowledge and your ability to apply it to real-life data manipulations.
Q. What is SQL?
A. SQL is a Structured Query Language that allows you to communicate with a database and handle the data it contains in all kinds of ways. It consists of Data Definition Language commands, such as Create, Alter, Truncate and Drop, and Data Manipulation Language commands, such as Select, Insert, Update and Delete. The most common databases that use SQL as their query language are Oracle, SQL Server, DB2, and MySQL.
Q. How is the primary key different from a unique key?
A. Both the primary and unique keys uniquely identify a record in a database table. One difference is that you can have more than one unique key per table, but only one primary key. Also, the primary key does not allow any null value, while the unique key allows one.
Q. What is the SQL syntax for sorting, and which is the default order?
A. The default sorting order is ascending. These two statements are identical:
select from order by
select from order by asc
For descending order, simply replace “asc” with “desc.”
Q. Explain the use of the Join keyword and its various types.
A. The join keyword is very powerful in SQL. It can be used to combine rows from multiple tables by using common values in certain fields. The type of join decides which rows are to be selected, while the select statement specifies which fields to include in the combination table.
This is the default type of join. It picks all rows that have matching fields, or in other words, that meet the join condition.
- A right outer join picks all rows from the table on the right, even if they do not meet the join condition. Some fields in such rows may have null values in the resulting table.
- A left outer join returns all rows of the left-side table, irrespective of their match with the right-side table.
- A full outer join returns all rows of the left- and right-side tables.
This is a special type of join where a table joins to itself.
This is the Cartesian product of rows from the tables included in the join query statement. In other words, every row from the first table is combined with every row of the second table, one at a time.
Q. Write a SQL query to find the largest element in a column. To make it a little tricky, write another query, this time to find the nth largest element.
A. You can find the largest element using:
select max(ElementName) from TableName.
To find the nth highest element, you need to use “where” and “in” keywords as follows:
select min(ElementName) from TableName where ElementName in (select distinct top n ElementName from TableName order by ElementName desc)
Once you have a strong grasp of the basic SQL concepts, practice writing SQL queries for sample data, and check the results. This will prepare you for theoretical as well as numerical interview questions.