SQL is a crucial querying language used to create and modify databases. If you are preparing for an SQL interview, this article will help you to understand the important questions which are asked and the answers associated with it.

There are various profiles like data analyst, database administrator, and data architect that require the knowledge of SQL. Apart from guiding you in your interviews, this article will also give a basic understanding of SQL.

Here are some of the probable SQL Interview questions that may be asked in a SQL interview:

Q1. What is DBMS?

Ans. Before starting with SQL, let us have a brief understanding of DBMS (Database Management System). In simple terms, it is a software that is used to create and manage databases. We are going to stick with RDBMS (relational DBMS) in this article (SQL). There are also non-relational DBMS like MongoDB used for big data analysis.

Q2. What is SQL?

Ans. SQL (structured querying language) is the language used to create, update and modify a database — pronounced both as ‘Se-quell’ and ‘S-Q-L’.

Q3. What are the subsets of SQL?

Ans. There are three subsets of SQL – 1) data definition language (DDL), 2) data manipulation language (DML) and 3) data control language (DCL).

Q4. What is DDL?

Ans. DDL (data definition language) allows end-users to CREATE, ALTER and DELETE database objects.

Q5. What is DML?

Ans. DML (data manipulation language) allows end-users insert, update, retrieve and delete data in a database.

Q6. What is DCL?

Ans. DCL (data control language) allows end-users to control the access to the database and GRANT or REVOKE permissions to manipulate or modify the database.

LEARN DATA ANALYSIS NOW>>

Q7. What is the primary key?

Ans. A primary key is used to define a column that uniquely identifies each row. Null value and duplicate values are not allowed to be entered in the primary key column.

Q8. What is a foreign key?

Ans. A foreign key is used to maintain the referential link integrity between two data tables. It prevents actions that can destroy links between a child and a parent table.

Also, Read>> Data Science vs. Big Data vs. Data Analytics

Q9. What is a unique key?

Ans. A unique key is used to uniquely identify each record in a database. A primary key is a special kind of unique key.

Q10. What are the three types of indexes in SQL?

Ans. The three types of indexes in SQL are– unique index, clustered index, and non-clustered index.

Also, Read>> How to Become a Data Analyst from Scratch?

Q11. What is the difference between TRUNCATE and DELETE?

Ans. Difference between TRUNCATE and DELETE are:

DELETE TRUNCATE
Delete command is used to delete a row in a table. Truncate is used to delete all the rows from a table.
You can rollback data after using the delete statement. You cannot rollback data.
It is a DML command. It is a DDL command.
It is slower than a truncate statement. It is faster.

Q12. What is the difference between:

SELECT * FROM MyTable WHERE MyColumn <> NULL

SELECT * FROM MyTable WHERE MyColumn IS NULL

Ans. The first line will not work because NULL means no value, and you cannot use scalar value operators. This is why there is a separate IS a NULL predicate in SQL.

Also, Read>> Data Science vs Data Analysis: Which one is better?

Q13. What are joins and what are the different types of joins?

Ans. Join is a query that retrieves related columns or rows. There are four types of joins – inner join left join, right join, and full/outer join.

Q14. What port does the SQL server use?

Ans. The standard port for the SQL server is 1433.

Q15. What is the difference between CHAR and VARCHAR?

Ans. CHAR is a fixed-length character data type, while VARCHAR is a variable-length character data type.

Q16. What is a subquery in SQL?

Ans. A subquery is a query within another query. When there is a query within a query, the outer query is called the main query, while the inner query is called a subquery.

Q17. How to find:

duplicate records with one field?

duplicate records with more than one field?

Ans. Finding duplicates with one field

SELECT COUNT(field)

FROM table_name

GROUP BY field

HAVING COUNT(field) > 1

with more than one field

SELECT field1,field2,field3, COUNT(*)

FROM table_name

GROUP BY field1,field2,field3

HAVING COUNT(*) > 1

Q18. What is a constraint, and how many levels of constraints are there?

Ans. Constraints are the representation of a column to enforce data entity and consistency. There are two levels of constraint – column level and table level.

Q19. What is Auto Increment in SQL?

Ans. Auto increment allows a unique number to be generated whenever a new record is created in a table—usually used with PRIMARY KEY.

Q20. What are the authentication modes in SQL Server?

Ans. SQL Server has two authentication modes –

  • Windows Mode – Default. This SQL Server security model is integrated with Windows
  • Mixed Mode – Supports authentication both by Windows and by SQL Server

We can change modes by selecting tools of SQL Server configuration properties and then hover to the security page.

Q 21.  What is PL/SQL?

Ans. PL/SQL or Procedural Language for SQL was developed by Oracle and is a procedural language. It is an extension of SQL, and enables the programmer to write code in a procedural format. Both PL/SQL and SQL run within the same server process and have features like – robustness, security, and portability of the Oracle Database.

Q 22. What is the difference between a clustered and non-clustered index?

Ans. Difference between clustered and non-clustered index are:

Clustered Index – A clustered index is used to order the rows in a table. It has leaf nodes consisting of data pages. A table can possess only one clustered index.

Non-clustered index – A non-clustered index stores the data and indices at different places. It also has leaf nodes that contain index rows. A table can possess numerous non-clustered indexes.

Q 23. What is SQL Profiler?

Ans. SQL Server Profiler is an interface for creating and saving data about each event of a file. It also allows a system administrator to analyze and replay trace results when a problem is being diagnosed.

Q 24. What is collation sensitivity?

Ans. Collation sensitivity defines the rules to sort and compare the strings of character data, based on correct character sequence, case-sensitivity, character width, and accent marks, among others.

Q 25. What are the different types of collation sensitivity?

Ans. There are 4 types of collation sensitivity, which include –

  • Accent sensitivity
  • Case sensitivity
  • Kana sensitivity
  • Width sensitivity

Q 26. What is the SQL Server Agent?

Ans. SQL Server Agent is a Microsoft Windows service that executes day-to-day tasks or jobs of SQL Server Database Administrator (DBA). This service enables the implementation of tasks on scheduled date and time.

Q 27. What is COALESCE?

Ans. COALESCE returns the first non-NULL expression within its arguments from more than one column in the arguments. The syntax for COALESCE is –

COALESCE (expression 1, expression 2, … expression n)

Q 28. SQL Server runs in which TCP/IP port? Can it be changed?

Ans. SQL Server runs on port 1433, and it can be changed from the Network Utility TCP/IP properties.

Q 29. What is “scheduled jobs” or “scheduled tasks“?

Ans. Scheduled jobs or tasks allows automated task management on regular or predictable cycles. One can schedule administrative tasks and decide the order of the tasks.

Q 30. Name the encryption mechanisms in the SQL server.

Ans. The encryption mechanism used in SQL server are –

  • Transact-SQL functions
  • Asymmetric keys
  • Symmetric keys
  • Certificates
  • Transparent Data Encryption

Q 31. Name symmetric key encryption algorithms supported in the SQL server.

Ans. SQL Server supports several symmetric key encryption algorithms, such as DES, Triple DES, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES.

Q 32. Can you name different types of SQL commands?

Ans. Yes. SQL commands are divided into following –

  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)
  • Data Query Language (DQL)
  • Data Control Language (DCI)
  • Transaction Control Language (TCL)

Q 33. Can you name different types of DDL commands?

Ans. DDL commands are divided into following –

CREATE – Used to create the database or its objects like table, index, function, views, triggers, etc.

DROP – Used to delete objects

ALTER – Used to change database structures

TRUNCATE – Used to erase all records from a table, excluding its database structure

COMMENT – Used to add comments to the data dictionary

RENAME – Used to rename a database object

Q 34. Name different DML commands in SQL.

Ans. DML commands are divided into following –

SELECT – Used to select specific database data

INSERT – Used to insert new records into a table

UPDATE – Used to update existing records

DELETE – Used to delete existing records from a table

MERGE – Used to UPSERT operation (insert or update)

CALL – Used to call a PL/SQL or Java subprogram

EXPLAIN PLAN – Used to interpret data access path

LOCK TABLE – Used to control concurrency

Q 35. Name different DCL commands in SQL.

Ans. DCL commands are –

GRANT – Used to provide user access privileges to the database

DENY – Used to deny permissions to users

REVOKE – Used to withdraw user access by using the GRANT command

Q 36. What is Database Relationship?

Ans. A Database Relationship is defined as the connection between two relational database tables. Here the primary table has a foreign key that references the primary key of another table.

Q 37. What are the different types of Database relationships in SQL?

Ans. There are three types of Database Relationship –

One-to-one – Both tables can have only one record

One-to-many – The single record in the first table can be related to one or more records in the second table

Many-to-many – Each record in both the tables can be related to any number of records

Q 38. What is Data Integrity?

Ans. Data integrity attributes to the accuracy, completeness, and consistency of the data in a database. It also refers to the safety and security of data and is maintained by a collection of processes, rules, and standards that were implemented during the design phase.

Q 39. What is the difference between Rename and Alias?

Ans. ‘Rename’ is changing the name of a database object and giving it a permanent name.

‘Alias’ is a temporary name given to a database object.

Q 40. What are SQL constraints?

Ans. SQL constraints are the set of rules to limit the insertion, deletion, or updating of data in the databases. They restrict the type of data going in a table for maintaining data accuracy and integrity.

Q 41. What is Normalization?

Ans. Normalization is a database design technique to organize tables to reduce data redundancy and data dependency.

 Q 42. What are the different types of Normalization?

Ans. There are six different types of Normalization –

  • First Normal Form (1NF)
  • Second Normal Form (2NF)
  • Third Normal Form (3NF)
  • Boyce-Codd Normal Form (BCNF)
  • Fourth Normal Form (4NF)
  1. Fifth Normal Form (5NF)

Q 43. What is Denormalization?

Ans. Denormalization is a database optimization technique for increasing a database infrastructure performance by adding redundant data to one or more tables.

Q 44. What are the differences between the Primary key and Unique key?

Ans. Differences between Primary key and Unique key are:

Primary Key Unique Key
Enforces column uniqueness in a table Determines a row which isn’t a primary key
Does not allow NULL values Accepts one NULL value
Has only one primary key Has more than one unique key
Creates clustered index Creates non-clustered index

Q 45.  Which are the main steps in Data Modeling?

Ans. Following are the main steps in Data Modeling:

  • Get and analyze business requirements.
  • Create a quality conceptual and logical data model
  • Select the target database to create scripts for physical schema using a data modeling tool

Q 46. What is a Join?

Ans. Join is an SQL operation for establishing a connection between two or more database tables. Joins allow selecting data from a table basis data from another table.

 Q 47. What are the different types of joins?

Ans. Types of joins:

  • INNER JOINs
  • OUTER JOINs
  • CROSS JOINs
  • LEFT OUTER JOINS
  • RIGHT OUTER JOINS
  • FULL OUTER JOINS

Q 48. What is Referential Integrity?

Ans. Referential integrity is a relational database concept that suggests that accuracy and consistency of data should be maintained between primary and foreign keys.

Q 49. What is Business Intelligence?

Ans. Business intelligence (BI) includes technologies and practices for collecting, integrating, analyzing, and presenting business information. It combines business analytics, data mining, data visualization, data tools and infrastructure, and best practices.

Q 50. What is faster between a table variable and a temporary table?

Ans. Between these, a table variable is faster mostly as it is stored in memory, whereas a temporary table is stored on disk. In case the size of the table variable exceeds memory size, then both the tables perform similarly.

Q 51. Mention the types of privileges available in SQL?

Ans. Following are the types of privileges used in SQL:

System Privilege: It deals with an object of a specific type and indicates actions on it which include admin that helps users to perform administrative tasks, alter any cache group, and alter any index.

 Object Privilege: It helps users to perform actions on an object using commands like table, view, indexes. There are other object privileges used in SQL are EXECUTE, INSERT, SELECT, FLUSH, LOAD, INDEX, UPDATE, DELETE, REFERENCES, etc.

Q 52. Explain what a relationship is? Mention the types of relationships?

Ans. Relationship in the database can be defined as the connection between more than one table.

Following are the four types of relationships:

  • One to One Relationship
  • Many to One Relationship
  • One to Many Relationship
  • Many to Many Relationship

Q 53. Explain database white box testing.

Ans. Database white box testing includes:

  • Database Tables, Data Model, and Database Schema
  • Referential integrity rules
  • Decision Coverage, Condition Coverage, and Statement Coverage
  • Database Consistency and ACID properties
  • Database triggers and logical views

Q 54. Exhibit the students who are having the same batch ID and study in the same department as student ids, 1002 and 1004.

Ans. 

select x.student_id ,

x.department_id

from students x

where (department_id, batch_id)

in (Select department_id , batch_id

from students

where student_id in (1002,1004))

and x.student_id not in (1002, 1004)

Q 55. What are the types of acid properties?

Ans. Following are the four types of acid properties:

  • Isolation
  • Atomicity
  • Consistency
  • Durability

 Q 56. Explain string functions in SQL?

Ans. SQL string functions are used for string manipulation.

Following are the extensively used SQL string functions:

  • UPPER(): Converts character data to upper case
  • LOWER(): Converts character data to lower case
  • SUBSTRING() : Extracts characters from a text field
  • RTRIM(): Removes all whitespace at the end of the string
  • LEN(): Returns the length of the value in a text field
  • REPLACE(): Updates the content of a string.
  • LTRIM(): Removes all whitespace from the beginning of the string
  • CONCAT(): Concatenates function combines multiple character strings

 

Q 57. Write the SQL query to convert the string to UPPERCASE and LOWERCASE.

Ans. The SQL query used to convert the string to UPPERCASE and LOWERCASE is:

STRING UPPER(“naukrilearning”); => NAUKRILEARNING

STRING LOWER(“LEARNERS”); => learners

Q 58. What are the different types of normalization?

Ans. There are four types of normalization:

  • First Normal Form (1NF)
  • Second Normal Form (2NF)
  • Third Normal Form (3NF)
  • Fourth Normal Form (4NF)

Q 59. What is the procedure to hide a specific table name of the schema?

Ans. By using SYNONYMS, we can hide a specific table name of the schema.

SYNTAX

CREATE SYNONYM STU for STUDENTS;

After creating the above synonym, we can access the data of STUDENTS table using STU as table name as below

SELECT * from STU;

Q 60. What is the procedure to eliminate duplicate rows?

Ans. By using the DISTINCT keyword, we can eliminate duplicate records.

SYNTAX

SELECT DISTINCT CLASS_ID

FROM STUDENTS;

Q 61. Find out nth highest salary from emp table?

Ans. SYNTAX

select salary from

(select salary, rownum EP from

(select salary from employees

order by salary desc))

where EP=n;

Q 62. Mention the command used to get back the privileges offered by the GRANT command?

Ans. Following are the commands used to get back the privileges :

  • The SQL command revokes to take away system privileges and object privileges from users and roles
  • REVOKE command is used to get back the privileges offered by the GRANT command

Q 63. What is the procedure to pass variables in a SQL routine?

Ans. Variables can be passed to a SQL routine by using:

  • “&” symbol
  • SQLPLUS command

Q 64. Can a view be updated/inserted/deleted? If yes, at what conditions?

Ans. It is not possible to add the data through a view if the view contains the following:

  • Group by clause
  • Group functions
  • DISTINCT keyword
  • Columns defined by expressions
  • Pseudo column ROWNUM keyword
  • NOT NULL column in the base table that is not selected by the view.

Q 65. What is the procedure to hide a particular table name of our schema?

Ans. By using SYNONYMS, we can hide a particular table name of our schema.

SYNTAX

CREATE SYNONYM STU for STUDENTS;

After creating the above synonym, we can access the data of STUDENTS table using STU as table name as below

SELECT * from STU;

We can also give an alternate name to the objects as well.

Q 66. Explain what is an inline view?

Ans. An inline view is a SELECT statement in the FROM-clause of another SELECT statement. In-line views are used to reduce complex queries by removing join operations and summarizing multiple separate queries into a single query.

SYNTAX

SELECT SALARY FROM

(SELECT SALARY, ROWNUN EP FROM

(SELECT SALARY FROM EMPLOYEES ORDER BY SALARY DESC) )

WHERE EP=7

Q 67. What command is used to create a table by copying the structure of another table?

Ans. SYNTAX

CREATE TABLE STU AS

SELECT * FROM STUDENTS

WHERE 1=2

Invalid Condition

We have to give the invalid condition in the where clause, where the whole data will copy to the new table (STU table). 

Q 68. Mention the use of the DROP option in the ALTER TABLE command.

Ans. The use of the DROP option in the ALTER TABLE command is to drop a particular COLUMN.

SYNTAX

ALTER TABLE TABLE_NAME

DROP COLUMN COLUMN_NAME

Q 69. What are the aggregate functions in SQL?

Ans. SQL aggregate functions allow us to return a single value, which is calculated from values in a column.

Following are the aggregate functions in SQL:

  • AVG() : This function returns the average value
  • COUNT(): This function returns the number of rows
  • MAX() : This function returns the largest value
  • MIN() : This function returns the smallest value
  • ROUND(): This function rounds a numeric field to the number of decimals specified
  • SUM() : This function returns the sum

Q 70. Name different TCL commands in SQL

Ans. Different DCL commands are –

COMMIT – Used to commit a transaction

ROLLBACK – Used to roll back a transaction

SAVEPOINT – Used to roll back the transaction within groups

SET TRANSACTION – Used to specify transaction characteristics

 

The above questions will help you to prepare for an SQL interview and get an idea of what type of questions are asked. You can also go for a certification course to improve your skills and knowledge. Naukri Learning offers a plethora of online database courses to help you become an expert and crack the interviews easily.