SQL is an important querying language which 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 requires the knowledge of SQL. Apart from guiding you in your interviews, this article will also give a basic knowledge of SQL.
Here are some of the probable SQL Interview questions that may be asked in a SQL interview:
Q1. What is a DBMS?
Before starting with SQL, let us have a brief understanding of DBMS (Database Management System). In simple 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 is also non-relational DBMS like MongoDB used for big data analysis.
Q2. What is SQL?
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?
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?
DDL (data definition language) allows end-users to CREATE, ALTER and DELETE database objects.
Q5. What is DML?
DML (data manipulation language) allows end-users insert, update, retrieve and delete data in a database.
Q6. What is DCL?
DCL (data control language) allows end-users to control the access to the database, to GRANT or REVOKE permissions to manipulate or modify the database.
Q7. What is a primary key?
A primary key is used to define a column which uniquely identifies each row. Null value is not allowed.
Q8. What is a foreign key?
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?
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?
The three type 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?
|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 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?
SELECT * FROM MyTable WHERE MyColumn <> NULL
SELECT * FROM MyTable WHERE MyColumn IS NULL
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 NULL predicate in SQL.
Q13. What are joins and what are the different types of joins?
Join is a query which 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?
The standard port for SQL server is 1433.
Q15. What is the difference between CHAR and VARCHAR?
CHAR is a fixed-length character data type while VARCHAR is a variable-length character data type.
Q16. What is a subquery in SQL?
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 subquery.
How to find:
duplicate records with one field?
duplicate records with more than one field?
with one field
GROUP BY field
HAVING COUNT(field) > 1
with more than one field
SELECT field1,field2,field3, COUNT(*)
GROUP BY field1,field2,field3
HAVING COUNT(*) > 1
Q17. Write an SQL query where to find names in a column starting with the letter A?
SELECT * FROM table_name WHERE column_name LIKE ‘A%’
Q18. What is a constraint and how many levels of constraints are there?
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?
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 it enables the programmer to write code in a procedural format. Both PL/SQL and SQL run within the same server process and has features like – robustness, security, and portability of the Oracle Database.
Q 22. What is the difference between clustered and non-clustered index?
Ans. Clustered Index – A clustered index is used to order the rows in a table. It has leaf nodes comprising 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 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 at scheduled dates and time.
Q 27. What is COALESCE?
Ans. COALESCE returns 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 SQL server.
Ans. The encryption mechanism used in SQL server are –
- Transact-SQL functions
- Asymmetric keys
- Symmetric keys
- Transparent Data Encryption
Q 31. Name symmetric key encryption algorithms supported in 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 35. 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
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 type of Database Relationship 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 a 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 updation of data in the databases. They limit 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 different types of Normalization?
Ans. There are 6 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)
- 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. Cite the differences between Primary key and Unique key.
|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 of Data Modeling?
- Get and analyze business requirements.
- Create quality conceptual and logical data model
- Select target database to create scripts for physical schema using 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 to select data from a table basis data from another table.
Q 47. What are 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 which 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 table variable exceeds memory size then both the tables perform in a similar manner.
The above questions will help you to prepare for an SQL interview and get an idea 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.