In the age of information, it should come as no surprise that one of the most in-demand jobs is the ability to manage data, analyze it, and derive insight from data. This is because the information age is driven by the big data sets produced from diverse business processes and ongoing operations in today’s physical and virtual offices. To adequately manage data, one of the tools every analyst will require is the Structured Query Language fondly known as (SQL).
If you’re reading this, it is quite obvious you already know what SQL is but for the sake of anyone looking to kick-start a career in database management let us define it. Search Query Language is a domain-specific language used in designing and programming managed data stored in relational database management systems. SQL has grown to become the dominant language of choice due to two major advantages. First, it introduced the concept of accessing many records or data using a single command which is something older languages such as ISAM or VSAM could not accomplish. The second advantage is that it eliminates the need to specify how to access a record.
Today, many query languages with the features SQL offers exist but it still remains the language of choice for many vendors, data managers, developers, and businesses. Thus, if interested in a career in database management, some knowledge of SQL is important to your pursuit. This post will explore why and 15 SQL-related questions to know before stepping into any interview or office space to clinch that dream job.
Top 15 Questions and Queries Relating to SQL You Should Know
As always, a successful interview outing is determined by the amount of effort you have put into practicing and preparing for the D-day. And to practice effectively, the fifteen questions outlined here should help. The answers to these questions have also been provided and you can choose to customize these answers anyhow they fit.
What is SQL and What is a Database
As with every process, questioning starts with the easiest questions to ease the interviewee into the process. So do not be surprised to come across this question. To answer these questions, a brief definition of what these concepts or terms consist of should be given and if time permits, a brief example of their use should be included in your answer.
The Answer to this is – SQL is an acronym for Structured Query Language and it is a language that can be used to communicate with a database. A database is a structured form of data that is stored in an organized manner in a storage center. An SQL is a language that can be used to access the data stored within a database, as well as manage the data. For example, SQL can be used to create a database of information stored in tabular form. It can also be used to access database information and manipulate them according to specific requirements.
Does SQL Support Programming Language Features?
This question intends to compare SQL with more traditional programming languages. For individuals not well-versed with other programming languages, this could be seen as a trick question. Therefore, the answer to it must describe the programmable features of SQL and contrast it with languages such as C#.
The answer to this is – SQL is a language but it does not support programming in its traditional sense. This means while SQL has command lines that can be used to query, update, or delete data in a database, it cannot be used to execute conditional statements such as looping. This makes SQL more of a command language than the traditional programming language.
Some differences worthy of note between SQL and complete programming languages include:
- SQL is a data-oriented and declarative language while complete programming languages are procedural in nature.
- SQL is primarily used for creating and managing databases while programming languages are used for creating applications.
- SQL tells a database what to do by executing one statement at a time while programming languages tell applications or databases how to execute tasks using blocks of codes or statements.
What is the Difference Between ‘BETWEEN and IN’ operators?
This question is pretty straightforward as it attempts to determine if you understand how simple queries function. The answer to this should include a definition of the operators and a brief analysis of how they can be applied when querying databases.
The answer to this is – The BETWEEN operator is generally used to fetch rows based on a range of specified values. This means the operator will fetch all the rows between two specified values. An example of this is when a BETWEEN Operator is used in accessing a database of 40 rows. The operator can be used to fetch the data between any two rows such as between row 20 to row 35.
On the other hand, the IN operator is used to check or fetch values within a specified set. This means when accessing a database with 40 rows, the IN operator can be used to fetch the data in row 19, 22, and 28 without hassle.
Name two Different Types of Case Manipulation Functions Available in SQL
There are three types of case manipulation functions that can be used in SQL. This question expects you to list them and highlight their specific functions to prove you have insight into how SQL works. This means your answer should not only name the types of case manipulation function but it should also define their functions.
The answer to this is – The answer to this should consist of two of the case manipulation functions in SQL. This includes the LOWER, UPPER, and INTCAP case functions. The functions they serve involves:
LOWER – The LOWER case manipulation function’s purpose is to return strings in lower case. The function takes a string as an argument and manipulates the string or converts it into lower case alphabets.
UPPER – The UPPER case manipulation function returns strings in upper case. This means it takes a string as an argument and converts it to upper case alphabets.
INTCAP – The INTCAP case manipulation function returns strings with the first letter in upper case form and the other letters as lower case letters. This means it takes a string as an argument and converts its first letter to upper case and its remaining letters to lower case letters.
When you use the term Data definition language and Data manipulation language, what do you mean?
This question also intends to assess your understanding of the different concepts in SQL. Thus, the answer should define what both concepts mean and provide examples if time permits.
The answer to this is – The first concept, ‘Data definition language’ is a language that allows you to execute queries such as CREAT, DROP, and ALTER when using SQL. This means executing queries that actually define data. Data manipulation language is used to manipulate the data stored within a database. Both languages are also popularly known with their acronyms which is DDL for data definition language and DML for manipulation language.
Data manipulation language allows you to perform diverse functions such as the following: inserting data or rows into a database, delete data from the database, retrieve or fetch data, update data in a database.
What is a Join statement in SQL and define the different types of Joins?
This question will be asked if the interviewer intends to know more about your ability to use SQL to manipulate and manage a database. The answer you provide is expected to define what join statements are and how its different types function when used.
The answer to this is – Join statements in SQL can be defined as statements used to combine the data or rows contained in two or more tables based on a common field between these tables. There are different types of join statements that can be used in SQL and they include:
INNER JOIN – The inner join statement or keywords is used to select the rows from both tables or multiple tables that meet the condition it specifies. The INNER JOIN keyword will create a result set that combines the rows from tables that meet the specified condition.
LEFT JOIN – The LEFT JOIN statement or keyword, which is also known as the LEFT OUTER JOIN, returns all the rows from the table located at the left side of the JOIN, as well as, the rows at the right side of the join that match. If there happen to be no matching rows at the right side of the join, the result-set will be null for these rows.
RIGHT JOIN – The RIGHT JOIN, RIGHT OUTER JOIN or keyword does the opposite of the LEFT JOIN and this is returning the rows from the table located at the right side of the JOIN and the rows at the left side of the join that matches the returned rows from the right. If there are no matching rows at the left side of the join, the result-set will also be null for the left rows.
FULL JOIN – The FULL JOIN statement creates result-sets that combines the end result of both the LEFT and RIGHT OUTER JOIN statements into a result-set. This means the result-set will consist of all the rows from the RIGHT JOIN and LEFT JOIN and null values will also be retained for rows that do not match.
What is an Index or a Database Index?
This question attempts to assess your ability to speed up queries when using SQL within a database. Thus, the answer should define what an index is and demonstrate its use.
The answer to this is – A database index can be defined as a structure that enhances the speed at which data retrieval operations within a database occurs. To accomplish the task of increasing the speed of data retrievals, a database index uses more resources such as storage space to maintain extra copies of data.
Indexes are created on tables and they require extra disc space to store data but when created, accessing these indexes is achieved more quickly than when searching the entire database. Thus, they are generally used to speed up the search for frequently searched data or values.
What is the Difference Between CHAR and VARCHAR2 datatype in SQL?
This question attempts to assess your understanding of characters and how they are used in SQL. Your answer should define both options in such a way that their differences are highlighted.
The answer to this is – While both of these data types can be used to define characters, CHAR is used for character strings with defined or fixed lengths. On the other hand, VARCHAR2 is used for character strings with varying lengths.
This means if Char(4) is specified as the type used, then only characters with this specified length can be stored within this variable. If we use Varchar2(5), then it will allow for the storing of strings with varying lengths such as a string with a length of 2,3, and 4.
What is A Primary Key, Foreign Key, and a Unique Key?
Here again, the interviewer is interested in assessing your basic understanding of SQL and its functions. This is generally a question asked at the beginning of an interview due to its rather low difficulty level.
The answer to this is – A simple definition of primary and unique keys is enough to answer the question asked here. A primary key is a combination of fields that uniquely specify a row while a unique key refers to a constraint that uniquely identifies every record within a database. It is worth noting that primary keys are a special kind of unique keys and its value can’t be null.
A foreign key refers to a table that can be related to the primary key of another table. This makes it possible to define relationships between two tables in a database.
What are the Different Types of Index?
There are different types of indexes that can be used to enhance the speed at which records can be retrieved. There are different types of Indexes and they include the following:
Unique Index – The unique index ensure fields do not have duplicate values once a column is indexed uniquely.
Clustered Index – Clustered index reorders the physical order of a table and search based on key values. It is important to note that every table can contain only one clustered index.
Non-clustered Index – Non-clustered index refers to indexes that maintain the logical order of data in a table. Tables can have multiple non-clustered data indexes unlike clustered index.
Using the tables shown below answer the following questions below.
Table – Employer Details
EmployerID | Full Name | ManagerID | DateofJoining |
121 | Bilbo Baggins | 321 | 01/31/2014 |
221 | Sam Wise | 986 | 01/30/2015 |
321 | Sam Gamgee | 876 | 27/11/2016 |
Table – Employer Salary and Project
EmployerID | Project | Salary |
121 | P1 | 5000 |
221 | P2 | 2000 |
321 | P1 | 10000 |
Write a SQL query that fetches the count or number of employees currently working on project P1?
This question is practical in nature and attempts to correctly assess your ability to use SQL to query databases. To answer this question, an aggregate function count() can be used to fetch the count of employees working on project P1.
Write a SQL Query to fetch employee names within the earning bracket of 4,000 and 12,000?
To answer this question, knowledge of the BETWEEN clause and how to use it is needed. The BETWEEN clause will return the Employer ID of the staff earning within this range and the ‘where’ clause can be used as a subquery to find the full names of employees in this range.
Write a SQL Query to fetch project-wise count of employees sorted by the projects count in descending order?
The question asked here is two-fold. The first question expects you to fetch the project-wise count. The second question expects you to sort the result in descending order. To do this, GROUPBY clause can be used for sorting the employees and ORDERBY clause can be used to sort the order.
Write an SQL query to fetch employee names and salary records?
To do this, the LEFT OUTER JOIN can be used with the employee detail table. This will sort the employee names and match it to the salary records at the Employee salary and project table.
Write an SQL query to fetch the odd rows from the table?
To accomplish this task, you can use Row_number in the SQL server to query the table to return the odd rows within the tables.
Summary
Preparing for your SQL interview involves more than just reading and memorizing the answers outlined here. The best way to prepare is constantly making use of SQL to manage databases as this gives you the hands-on experience and ability needed to answer the most technical questions that will be thrown your way.