Structured Query Language (SQL) is a special-purpose language for interacting with relational databases. The vast majority of applications that use data are backed by a good relational database system. SQL is a language that enables you to work with databases. Using SQL, you can insert records, update and delete records, and can also create new objects such as databases and tables.
There are far more advanced features that include creating stored procedures or SQL scripts, views, and setting permissions on database objects (such as tables, stored procedures, and views). Although ANSI (American National Standards Institute) has established SQL standards, there are many different versions of SQL based on different types of databases (or vendors). However, to be in compliance with the ANSI standard, they need to at least support the major commands such as DELETE, INSERT, UPDATE, WHERE, etc.
SQL is classified or divided into DDL and DML statements, Data Manipulation Language (DML), and Data Definition Language (DDL).
DML enables you to work with the data that goes into the database. DML is used to insert, select, update, and delete records in the database.
- SQL SELECT - Retrieve data from DB
- SQL INSERT - Insert data to DB
- SQL UPDATE - Update data to DB
- SQL DELETE - Delete data from the DB
DDL statements are the subset of SQL statements that helps you to change the structure of the database schema in some way by creating, deleting, or modifying schema objects such as databases, tables, and views.
- CREATE DATABASE - creates a new database
- ALTER DATABASE - modifies the database
- DROP DATABASE – deletes the database
- CREATE TABLE - creates a new table
- ALTER TABLE - modifies the table
- DROP TABLE – deletes the table
Now, let's get ahead and know the SQL interview questions that may strike in your next interview.
Things to Keep in Mind While Answering SQL Interview Questions
Let’s also look at some of the key things that you need to keep in mind while answering the interview questions. The general questions will revolve around key concepts like Constraints, Indexes, Views, Joins, Functions, Stored Procedures, Transactions, and Locks. You may also get some scenario-based questions than objective questions when the interviewer wants to test your knowledge in deep for example: How will you tune your SQL and Stored Procedures and what are the best practices? The level of the interview entirely depends on the position. It could be simple SQL for analyst roles, or it could be more complex SQL interview questions and data modeling questions for engineering roles.
SQL Interview Questions and Answers
1. A person issues a query shown below:
SELECT FirstName FROM StaffList
WHERE FirstName LIKE'_A%'
Which names does this query return? Select all that are applicable.
A) Allen
B) CLARK
C) JACKSON
D) David
2. Which of the following functions can be used only with numeric values? (Select all that are applicable.)
A) AVG
B) MIN
C) LENGTH
D) SUM
E) ROUND
3. Which function do you use to remove all padded characters to the right of a character value in a column with a char datatype?
A) RTRIM
B) RPAD
C) TRIM
4. In Sales Table, you need to return sales data ordered by customer name and date of sale. For each customer, the most recent sale must be listed first. Which query should you use?
A) SELECT CustomerName, SalesDate FROM Sales ORDER BY SalesDate DESC, CustomreName;
SELECT TOP(1) Salary FROM
{
SELECT DISTINCT TOP(10) Salary FROM EMPLOYEE ORDER BY Salary DESC
} AS EMP ORDER BY SALARY
B) SELECT CustomerName, SalesDate FROM Sales ORDER BY CustomreName;
SELECT TOP(1) Salary FROM
{
SELECT DISTINCT TOP(10) Salary FROM EMPLOYEE ORDER BY Salary
} AS EMP ORDER BY SALARY DESC
C) SELECT CustomerName, SalesDate FROM Sales ORDER BY CustomreName, SalesDate;
SELECT TOP(1) Salary FROM
{
SELECT DISTINCT TOP(10) Salary FROM EMPLOYEE ORDER BY Salary
} AS EMP ORDER BY SALARY
D) SELECT CustomerName, SalesDate FROM Sales ORDER BY CustomreName, SalesDate DESC;
SELECT TOP(1) Salary FROM
{
SELECT DISTINCT TOP(10) Salary FROM EMPLOYEE ORDER BY Salary DESC
} AS EMP ORDER BY SALARY DESC
5. Select a name from instructor where salary <=1,00,000 and salary >=90,000; How can this query be replaced?
A) Select name from instructor where salary between 1,00,000 and 90,000
B) Select name from instructor where salary < 90,000 and salary >= 1,00,000
C) Select name from instructor where salary <= 90,000 and salary >= 1,00,000
D) Select name from instructor where salary between 90,000 and 1,00,000
6. What is the fastest way of accessing a row in a table?
Using ROWID.
7. Select the correct join for the following SQL:
SELECT CUSTOMER.T. CUSTOMER_ID, ORDER_T. CUSTOMER_ID, NAME, ORDER_ID FROM CUSTOMER_T, ORDER_T where CUSTOMER_T.CUSTOMER_ID=ORDER_T.CUSTOMER.ID
A) Outer Join
B) Cartesian Join
C) Equi-Join
D) Natural Join
8. What is Referential Integrity Constraint?
Maintaining the integrity of the data through a set of rules that restrict the values of one or more columns of the tables based on the values of the primary key or unique key of the referenced table.
9. Identify correct ANSI SQL Syntax. Select all that are applicable.
A) Select e* from emp e join dept d on e.deptno=d.deptno
B) Select e* from emp e, dept d where e.deptno=d.deptno
C) Select e* from emp e inner join dept d on e.deptno=d.deptno
D) Select *from emp INNER JOIN dept USING (deptno)
10. Which are the applicable constraints that must not conflict with updated value? Select all that are applicable.
A) Secondary Key
B) Unique Indexes
C) NOT NULL
D) Primary Key
11. Consider the following statements and what would be the result?
While (1=1)
BEGIN WAITFOR TIME ’01:00’
EXEC sp_update_stats
RAISEERROR (‘Statistics updated for the database’, 1,1) WITH LOG
END
A) Updates the statistics of every table in the database only once at 1:00 AM, writes a log entry to SQL Server log and NT application log
B) Updates the statistics of every table in the database every night at 1:00 AM, writes a log entry to SQL Server log and NT application log
C) Error Message
D) Updates the statistics of every table in the database every night at 1:00 AM
12. Which of the update queries listed below is/are valid?
A) UPDATE Supplier SET city=’Chennai’ AND Phone=’8929182918’ AND Fax=’044-42323233’
B) UPDATE Supplier SET city=’Chennai’, Phone=’8929182918’, Fax=’044-42323233’
C) Options A & B
D) None of the above
13. How does one implement IF-THEN-ELSE in a select statement in ANSI SQL?
A) select decode (sex, ’M’,’Male’,’F’,’Female’,’Unknown’) from employees
B) select name, CASE When SAL>1000 THEN ‘OverPaid’ ELSE ‘UnderPaid’ END from EMP;
C) Option A& B
D) None of the above
14. Write 3 SQL queries to get an accurate count of the number of records in a table using query analyzer?
SELECT * FROM table1
SELECT COUNT (*) FROM table1
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2
15. With SQL, how do you select all the records from a table named “Persons” where the value of the column “FirstName” starts with an “a”?
A) SELECT * from Persons WHERE FirstName STARTSWITH ‘a’
B) SELECT * from Persons WHERE FirstName LIKE ‘%a’
C) SELECT * from Persons WHERE FirstName = ‘%a%’
D) SELECT * from Persons WHERE FirstName LIKE ‘%a’
16. What happens when the parenthesis is removed from the below SQL statements?
SELECT e.emp_id,(.15*e.salary) + (.5*e.comiss_pct) + (s.sales*(.35*e.bonus)) AS CALC VALUE FROM emp e, sales s where e.emp_id=s.emp_id;
A) Error Message will be displayed
B) CALC_VALUE column will be higher
C) CALC_VALUE column will be lower
D) There will be no difference in the value displayed CALC_VALUE column.
17. What is an Index and its use?
An Index is an optional structure related to a table. It is used to access rows directly and can be created to increase the performance of data retrieval. The index can be created on one or more columns of a table.
18. How can you change “Hanson” into “Nilsen” in the “LastName” column in the Persons Table?
A) UPDATE SET LastName=’Hanson’ INTO LastName=’Nileson’ FROM PERSONS
B) UPDATE SET LastName=Nilesen WHERE LastName=’Hanson’ FROM PERSONS
C) UPDATE PERSONS SET LastName=’Hansen’ INTO LastName=’Nilson’
D) UPDATE PERSONS SET LastName=’Nilsen’ WHERE LastName=’Hanson’
19. What is the difference between TRUNCATE & DELETE?
TRUNCATE commits after deleting the entire table, i.e., cannot be rolled back. Database triggers do not fire on TRUNCATE.
DELETE allows the filtered deletion. Deleted records can be rolled back or committed. Database triggers fire on DELETE.
20. What are the possible results of CHECK constraints?
A) True
B) False
C) UNKNOWN
D) All of the above
21. What is the difference between SUBSTR and INSTR?
INSTR (String1, String2(n, (m)),
INSTR returns the position of the nth occurrence of string 2 in
string1. The search begins from the nth position of string1.
SUBSTR (String1 n, m)
SUBSTR returns a character string of size m in string1, starting from the nth position of string1.
22. Which is faster - IN or EXISTS?
Considering a large subquery result, EXISTS is faster than IN because IN returns a value whereas EXISTS returns a Boolean value.
23. What is the highest value that a BYTE data field can store?
The highest value that a BYTE field can store is 255 (from -128 to 127).
24. From the given WHERE clauses, which will return only rows that have a NULL in the Expenses column?
A) WHERE Expenses <>
B) WHERE Expenses IS NULL
C) WHERE Expenses = NULL
D) WHERE Expenses NOT IN (*)
25. Which of the following statements is Data Manipulation Language commands?
A) INSERT
B) UPDATE
C) GRANT
D) TRUNCATE
E) CREATE
26. Write a query to display the employee name and respective manager name in the Emp table including the manager is null.
SELECT a.empname ||' is working under '||b. emp name FROM EMP a, EMP b WHERE a.mgr=b.empno UNION SELECT 'There is no manager to '|| emp name FROM EMP WHERE mgr IS NULL
27. Which of the following statements are correct with regards to the COMMIT and ROLLBACK query in SQL? Select all that apply.
A) COMMIT and ROLLBACK commands are used only while creating and dropping tables
B) ROLLBACK command is used to undo transactions since the last commit or rollback was issued
C) COMMIT command is used to save transactions since the last commit or rollback was issued
D) Everything that is included within the BEGIN and END statements will execute as a unit or will not execute at all
28. Write a query to find out nth highest salary from the emp table
SELECT DISTINCT (a.salary) FROM EMP A
WHERE &N = (SELECT COUNT (DISTINCT (b.salary)) FROM EMP B
WHERE a.salary<=b.salary);
29. Which Set Transaction Isolation Level lock mode is isolated and needs that every transaction completes in a sequence?
A) Serializable
B) Read Committed
C) Read Uncommitted
D) Repeatable Read
30. Which of the following options accurately represents or describes the VIEWs in SQL?
A) A view is a group of SQL statements forming a logical unit of work.
B) Views are queries that can provide users with a managed (limited) presentation of data or in other words, provides an abstraction of the table.
C) Views are nothing but a set of nested inner and outer queries.
D) Views are created using CREATE TABLE statements.
Conclusion
These 30 SQL interview questions and answers would have given you an insight into what kind of questions could be asked in your next interview, and you need to be prepared for it.
SQL is evolving rapidly and is one of the widely used query languages for data extraction and analysis from relational databases. Despite the outburst of NoSQL in recent years, SQL is still making its way back to become the widespread interface for data extraction and analysis. It is also not very hard to learn unlike some programming languages, and the good news is that if you learn how to write SQL queries, you would be able to work with almost any of the relational database management systems. Simplilearn has many courses in SQL which can help you get foundational and deep knowledge in SQL and eventually become a SQL expert.
SQL training is highly recommended for anyone who would like to make their career in SQL. So why wait? Enroll in our course below to become a SQL expert now.
