Nearly all SQL job asprants go through exactly the same nerve-wracking process. It is very hard to estimate if we know enough SQL to make the cut and more importantly prepare for the questions likely to be asked in the interview. Indeed, there are many different types of SQL database jobs with companies on a lookout for newfound skills. So if the process of interview preparation intimidating or if this is the first step to your preparation then worry not we have got it all planned.
SQL programming abilities are extremely desirable and demanded in the market, as there is the massive value of Database Management Systems (DBMS) in practically every software application. In order to grab a job, aspirants need to ace the interview session in which they are asked several SQL questions. We have curated a list of SQL interview questions with answers, which are likely to be examined in the interview session. Further, candidates are prone to be asked SQL basic level questions to advance level SQL questions, depending on their knowledge and several other determinants.
1. What do you understand by Database?
A database is a coordinated combination of data, stored and recovered digitally from a local or remote computer system. Also, databases can be broad and complicated, and such databases are produced using fixed design and modeling procedures.
2. What is DBMS?
A (DBMS) Database Management System is a plan that controls the creation, sustenance, and performance of a database. Also, DBMS can be termed as File Manager that handles data in a database rather than preserving it in file systems.
3. What is SQL?
Structured Query Language or SQL, and it is utilized to interact with the Database. This is a conventional language that is used to accomplish responsibilities such as update, retrieval, deletion, and insertion of data from a database.
4. Define Data warehouse.
A data warehouse associates to a central repository of data from various references of information. Those data are combined, modified, and made ready for mining as well as online processing.
5. What do you understand by a table in a Database?
A table is a database object is used to save records in a field in the format of rows and columns that holds data.
6. What are the various subsets of SQL?
- Data Definition Language (DDL) – It allows execution of various operations on the database like ALTER, CREATE, and DELETE objects.
- Data Manipulation Language (DML) – It enables you to reach and manipulate data. Also, it assists you to update, insert, delete and recover data from the database.
- Data Control Language (DCL) – It allows control access to the database. For example, Grant, Revoke access permissions.
7. What is the difference between SQL and MySQL?
SQL is a conventional language for recovering and forming structured databases. In contrast, MySQL is a relational database management operation, like Oracle, SQL Server, or IBM DB2, that is utilized to maintain SQL databases.
8. What are the two types of DBMS?
There are two kinds of DBMS:
- Relational Database Management System: The data is collected in relations (tables). For Example – MySQL.
- Non-Relational Database Management System: There are no theories of relations, tuples, and attributes. For Example – MongoDB.
9. What are tables and Fields?
A table is a collection of data that are arranged in a model with Rows and Columns. Columns can be classified as vertical, and on the other hand, rows are horizontal. Further, a table has a specified number of columns named as fields but can have any number of rows which is known as a record.
10. What do you mean by join in SQL?
A JOIN clause is practiced to combine rows from two or more tables, based on a related column between them.
11. How many types of Joins are there?
There are four types of joins:
- Inner join: Inner Join in SQL is the common type of join. It is applied to return all the rows from various tables where the join condition is fulfilled.
- Right Join: Right Join in SQL is utilized to return all the rows from the right table but only the matching rows from the left table where the join situation is fulfilled.
- Left Join: Left Join in SQL is practiced to return all the rows from the left table but only the matching rows from the right table where the join situation is satisfied.
- Full Join: Full join returns all the records when there is a match in any of the tables. Therefore, it recovers all the rows from the left-hand side table and all the rows from the right-hand side table.
12. How do we use the DISTINCT statement? And what is its use?
The DISTINCT is used with the SELECT statement. If the record includes duplicate values then the DISTINCT statement is utilized to select distinctive values among duplicate records.
Syntax:
SELECT DISTINCT column_name(s)
FROM table_name;
13. What are the famous Database Management Systems in the IT Industry?
MySQL, Oracle, PostgreSQL, Microsoft SQL Server, DB2, Sybase, MongoDB, and Microsoft Access etc.
14. What are the types of SQL commands?
SQL commands are separated into the following types:
- DML – Data Manipulation Language
- DQL – Data Query Language
- TCL – Transaction Control Language
- DDL – Data Definition Language
- DCL – Data Control Language
15. What do you mean by primary key?
A primary key is a compound of fields that uniquely define a row. This is a specific kind of unique key, and it has an implicit, NOT NULL constraint. It indicates Primary key values cannot be NULL.
16. Explain Constraints in SQL.
Constraints are applied to specify the rules concerning data in the table. It can be employed for single or multiplied fields in an SQL table during the production of the table or after building using the ALTER TABLE command. The constraints are mentioned below:
- DEFAULT – Automatically selects a default value if no value has been defined for the field.
- NOT NULL – Restricts NULL value from being entered into a column.
- UNIQUE – Guarantees unique values to be injected into the field.
- CHECK – Checks that all values in a field comfort a condition.
- PRIMARY KEY – Uniquely recognizes each record in a table.
- INDEX – Indexes a field giving faster retrieval of records.
- FOREIGN KEY – Assures referential uprightness for a record in another table.
17. What do you understand by the data integrity?
Data Integrity describes the correctness as well as the flexibility of the data collected in a database. Also, it represents integrity constraints to drive business rules on the data when it is accessed into an application or a database.
19. Which TCP/IP port does the SQL Server originally works on?
By default, SQL Server works on TCP port number 1433.
18. Define normalization.
Normalization is the method of decreasing redundancy and dependence by adjusting the fields and tables of a database. The main purpose of Normalization is to add, delete or adjust fields that can be performed in a single table.
20. What is the distinction between clustered and non-clustered indexes in SQL?
The differences between the clustered and non clustered index in SQL are :
- A clustered index is utilized for effortless retrieval of data from the database and it’s quicker whereas reading from non clustered index is comparatively slower.
- One table can only have one clustered index, however, it can have many non clustered indexes.
- Clustered index adjusts the way records are saved in a database as it classifies out rows by the column which is set to be clustered index, on the other hand, in a non-clustered index, it does not alter the style it was collected but it produces a separate object within a table which aims back to the primary table rows after exploring.
21. List the diverse types of index configurations feasible for a SQL table?
In SQL a table can have the following index configurations-
- A clustered index
- No indexes
- A non-clustered index
- A clustered index and many non-clustered indexes
- Many non-clustered indexes
22. Explain transactions.
A transaction can be described as a series task that is executed on databases in a logical way to obtain accurate results. Operations like updating, Creating, deleting records made in the database come from transactions. In plain words, we can say that a transaction indicates a group of SQL inquiries executed on database records.
23. What are the transaction controls?
There are four transaction controls:
- COMMIT: It is practiced to save all adjustments made during the transaction.
- ROLLBACK: It is applied to roll back the transaction. All modifications made by the transaction are returned back and the database persists as before.
- SET TRANSACTION: Insert the name of the transaction.
- SAVEPOINT: It is practiced to establish the point where the transaction is to be pushed back.
24. Explain Denormalization.
DeNormalization is a procedure that is used to obtain the data from higher to lower normal modes of the database. It is also a method of interjecting redundancy into a table by consolidating data from the related tables.
25. What is query optimization?
The phase that recognizes a plan for evaluation query which has the minimum estimated cost is known as query optimization.
26. What are the benefits of query optimization?
- The output is produced faster
- A larger amount of queries can be administered in less time
- Decreases time and area complexity
27. What are all the various normalizations?
The forms can be separated into five forms:
- First Normal Form (1NF): This should eliminate all the duplicate columns from the table. Also, formulation of tables for the related data and classification of unique columns.
- Second Normal Form (2NF): Satisfying all obligations of the first regular form. Assigning the subsets of data in different tables and Creation of relationships between the tables using fundamental keys.
- Third Normal Form (3NF): This should meet all necessities of 2NF. Eliminating the columns which are not subordinate to prime key constraints.
- Fourth Normal Form (4NF): Converging all the demands of the third normal form and it should not have multi-valued dependencies.
28. What do you mean by triggers?
Triggers in SQL are a kind of stored method utilized to formulate a response to a special action worked on the table such as INSERT, UPDATE or DELETE. You can request triggers explicitly on the table in the database. Further, Action and Event are two main elements of SQL triggers. When specific actions are implemented, the event happens in answer to that action.
29. Define recovery model.
The recovery model basically discloses to SQL Server what data ought to be stored in the exchange log record and to what extent.
30. What are the distinct DCL commands in SQL?
DCL commands are practiced to make roles, give permission, and control access to the database objects.
- GRANT: To implement user access
- DENY: To reject permissions to users
- REVOKE: To eliminate user access
31. Explain a Self-Join.
A Self-JOIN is a matter of regular join where a table is attached to itself based on any relation between its own column(s). Self-join practices the INNER JOIN or LEFT JOIN clause and a table alias is utilized to indicate diverse names to the table within the inquiry.
32. Define View.
A view is a virtual table that consists of a subset of data restrained in a table. Views are not practically present, and it needs less space to store. The view can have data of one or more extra tables consolidated.
33. What are the various TCL commands in the SQL?
TCL commands are utilized to maintain the changes made by DML statements.
- COMMIT: To draft and collect the changes to the database
- ROLLBACK: To replace the database since the last commit
34. What is Cursor? How to use a Cursor?
A database cursor is a control arrangement that provides for the traversal of records in a database. Also, Cursors help to process after traversals, such as retrieval, addition, and deletion of database records. They can be observed as a pointer to one row in a set of rows.
35. How we one update the view?
SQL CREATE and REPLACE can be utilized for updating the view.
Execute the following query to update the created view.
Syntax:
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
36. Define Index.
An index is a performance tuning process of enabling faster retrieval of records from the table. Also, an index generates an entry for each and every value and it will be quicker to recover data.
37. What are Entities and Relationships?
Entities: A personality, place, or thing in the actual world about which data can be saved in a database. Tables store data that expresses one kind of entity. Example – A bank database has a consumer table to save customer information. Also, the consumer table reserves this data as a set of properties (columns within the table) for each and every customer.
Relationships: Relation or links among entities that have something to do with each other. Example – The customer name is relevant to the customer account number and connection information, which might be in a similar table. There can also be relationships among separate tables (for example, customer to accounts).
38. Define Subquery.
A subquery is a query within a different query. The outer query is called the main query, and the inner query is called the subquery. A subQuery is always administered first, and the effect of the subquery is transferred to the main query.
39. What are the benefits of Views?
Some of the benefits of Views are
- Views hold no space
- Views are utilized to simply recover the consequences of complex queries that require to be performed often.
- Views are used to restrain access to the database or to sneak data complexity.
40. Describe the working of SQL Privileges.
SQL GRANT and REVOKE instructions are utilized to perform privileges in SQL multiple user environments. Also, the administrator of the database can give or remove privileges to or from users of database things by practicing commands like INSERT, DELETE, SELECT, UPDATE, ALL, etc.
41. Define stored procedure.
A stored procedure is a function that contains many SQL statements to obtain the database system. Many SQL statements are incorporated into a stored procedure and execute whenever and wherever needed.
42. What are the benefits of Normalization?
Some of the benefits are:
- More excellent Database organization
- Effective data access
- More Tables with smaller rows
- More comprehensive Elasticity for Queries
- Instantly find the information
- More effortless to implement Security
43. What is a Full Back up in an SQL environment?
A full backup is the widely known sort of reinforcement in SQL Server. This is the complete substitute for the database. It additionally includes a portion of the exchange log so it very well may be obtained.
44. Explain the differentiation between DROP and TRUNCATE commands.
DROP command eliminates a table and it cannot be turned back from the database whereas the TRUNCATE command extracts all the rows from the table.
45. How many kinds of Privileges are possible in SQL?
There are two kind of privileges practiced in SQL, such as:
- System privilege: It deals with the purpose of a particular type and gives users the right to complete one or more actions on it. Also, these actions involve conducting administrative duties, ALTER ANY CACHE GROUP CREATE/ALTER/DELETE TABLE, ALTER ANY INDEX, CREATE/ALTER/DELETE VIEW, etc.
- Object privilege: Object privilege provides to complete actions on an article or object of another user(s) viz. view, table, indexes, etc. Further. some of the object privileges are INSERT, REFERENCES, UPDATE, EXECUTE, SELECT, FLUSH, DELETE, LOAD, INDEX, etc.
46. Explain various kinds of Normalization.
There are various progressive levels of normalization. These are called normal forms. Each following normal form based on the former one. The first three normal modes are normally adequate.
- First Normal Form (1NF) – No recurrent clubs within rows
- Second Normal Form (2NF) – Each non-key (supporting) column amount is conditioned on the entire primary key.
- Third Normal Form (3NF) – Dependent singularly on the primary key and no additional non-key (supporting) column value.
47. Explain difference between DELETE and TRUNCATE commands.
DELETE command is utilized to eliminate rows from the table, and the WHERE clause can be utilized for the conditional collection of parameters. Commit and Rollback can be delivered after removing the statement.
TRUNCATE eliminates all rows from the table. Truncate operation cannot be returned back.
49. When will be the UPDATE_STATISTICS command worked?
As the title infers command stimulates the measurements used by the file to make the search simpler.
50. Explain the ACID property.
ACID holds for Atomicity, Consistency, Isolation, Durability. It is utilized to guarantee that the data transactions are prepared reliably in a database system.
51. Define Atomicity.
Atomicity is the transactions that are fully done or abandoned where transaction leads to a single logical operation of data. It indicates if one part of any transaction breaks, the whole transaction fails and the database status is left unchanged.
52. Explain the difference between Local Variables and Global Variables.
Local Variables: It can be practiced or exist only within the function. These variables are not utilized or referred to by any other functions. These are not associated with other functions. Variables can be produced whenever that function is called.
Global Variables: It can be utilized or exist during the program. The same variable represented in global cannot be practiced in functions. Global variables cannot be generated whenever that function is called.
53. What do you understand by mirroring?
Mirroring is a great accessibility design. It is designed to hold up a hot reserve server which is expected with the quintessential server as far as an exchange. Also, Exchange Log records are exported legitimately from a central server to a secondary server which waits up with the most advanced with the primary server.
54. Explain Alias in SQL.
An alias is a characteristic of SQL that is approved by most, if not all, RDBMSs. It is a provisional name attached to the table or table column for the meaning of a particular SQL query. Also,, aliasing can be applied as an obfuscation method to ensure the real names of database areas.
55. Define durability.
Durability indicates that if a transaction has been performed, it will happen whatever may happen in between such as power loss, crash, or any sort of error.
56. What do you mean by SQL Injection?
SQL Injection is a sort of database attack system where hateful SQL statements are injected into an entry field of the database in a way that once it is administered, the database is disclosed to an attacker for the attack. This method is normally practiced for tackling data-driven applications to have entrance to sensitive data and execute administrative tasks on databases.
57. Is it permissible to take the whole database backup in Log shipping?
Generally, we can take the full database reinforcement. It doesn’t influence the log shipping.
58. What do you understand by Auto Increment?
Autoincrement keyword permits the user to generate a unique number to be created when a new record is entered into the table. AUTOINCREMENT keyword can be practiced in Oracle and IDENTITY keyword can be utilized in SQL SERVER. Mostly this keyword can be utilized whenever PRIMARY KEY is practiced.
59. How can one check the SQL Server version?
SQL Server version can be checked by operating the following command- SELECT @@Version.
60. Define Data warehouse.
The data warehouse is a fundamental repository of data from various sources of information. Those data are combined, transformed, and produced available for mining and online processing. Also, warehouse data have a subset of data called the Data Marts.
61. What is mean by a heap?
A heap is generally a table that does not include any clustered file or non-clustered index.
62. Explain the use of the NVL function.
NVL function is practiced to change the null value to its actual value.
63. Are NULL values the same as that of zero or a blank space?
No, a NULL value is not at all equal or the same as that of zero or a blank space. The NULL value expresses a value that is unavailable, unexplained, assigned, or not applicable whereas zero is a number and blank space is a figure.
64. Why do you think replication is needed on the SQL Server?
This is generally applied to extend the limit of the perusing and to provide an option to its clients to decide among different peculiar servers to play out the read/compose regulations.
65. What do you understand by the Cartesian product?
The product of Cross Join is named a Cartesian product. It recovers rows connecting each row from the first table with every row of the second table.
66. Compose a SQL query to obtain the names of employees that begin with ‘A’?
To show the name of the employees that begin with ‘A’, type in the below command:
1
SELECT * FROM Table_name WHERE EmpName like ‘A%’
67. How many row contrast operators are utilized while working with a subquery?
There are 3-row comparison operators that are used in subqueries such as IN, ANY and ALL.
68. What is Auto Increment?
Autoincrement keyword enables the user to generate a unique number to get created when a new record is entered into a table. Also, autoincrement keyword can be utilized whenever the Primary Key is practiced. This keyword is utilized in Oracle and IDENTITY keyword is practiced in SQL Server.
69. What is the differentiation between DROP and TRUNCATE?
TRUNCATE eliminates all rows from the table which cannot be recovered back, and DROP liquidates the entire table from the database and it also cannot be recovered back.
70. Define user defined functions.
User-defined functions are the functions addressed to accept that logic whenever needed. It is not required to address the same logic numerous times. Instead, the function can be called or administered whenever needed.
71. Write a SQL query that is utilized to demonstrate the current date?
In the SQL, there is a work in capacity known as GetDate() which recovers the current timestamp/date.
72. What are the various operators usable in SQL?
There are three types of SQL operators that are accessible in SQL, namely:
- Arithmetic
- Logical
- Comparison Operators
73. What is the main differentiation among ‘BETWEEN’ and ‘IN’ condition operators?
BETWEEN operator is applied to displaying rows based on a series of values in a row whereas the IN situation operator is utilized to control for values included in a special set of values.
74. Define collation?
Collation is described as a set of rules that define how character data can be classified and examined. This can be utilized to compare A and, other language characteristics and also depends on the width of the characters.
75. Why is there a need for a MERGE statement?
This statement provides conditional updates or the injection of data into a table. It operates an UPDATE if a row survives, or an INSERT if the row does not survive.
76. What do you understand by recursive stored procedure?
The recursive stored method points to a stored method that calls by itself until it relinquishes some boundary situation. This recursive function or method assists the programmers to utilize the same set of code n number of times.
78. Explain Database Black Box Testing.
- Data Mapping
- Data collected and retrieved
- Treatment of Black Box testing techniques such as Equivalence Partitioning and Boundary Value Analysis (BVA).
79. Name the ways in which the Dynamic SQL can be accomplished?
The ways in which the dynamic SQL can be executed:
- Compose a query with parameters.
- EXEC.
- Using sp_executesql.
80. What is the largest power that can be saved in a BYTE data area?
The most extensive number that can be expressed in a single byte is 11111111 or 255. Also, the number of potential values is 256 (i.e. 255 (the largest possible value) plus 1 (zero), or 28).
81. What are all various kinds of collation sensitivity?
- Kana Sensitivity
- Width Sensitivity – Single-byte character and double-byte character.
- Case Sensitivity – A and a and B and b.
- Accent Sensitivity.
82. Name the syntax to add a record to a table?
To add a record in a table INSERT syntax is practiced.
For Instance,
INSERT into table_name VALUES (value1, value2..); |
83. What is the differentiation between the Having and Where clause?
Where clause is utilized to retrieve data from a database that defines appropriate criteria whereas a Having clause is used along with ‘GROUP BY’ to retrieve data that meets accurate criteria defined by the Aggregate functions. Where clause cannot be practiced with Aggregate functions, but the Having clause can.
84. Define Auto Increment in SQL.
Autoincrement keyword enables the user to generate a unique number to get created whenever a new record is entered into the table. This keyword is generally needed whenever PRIMARY KEY in SQL is utilized.
85. Explain Online Transaction Processing (OLTP).
Online Transaction Processing or OLTP operates transaction-based applications which can be utilized for data entry, data retrieval, and data processing. Also, OLTP makes data management easy and effective. Unlike OLAP systems purpose of OLTP systems is to serve real-time transactions.
For Example – Bank Transactions on a daily basis.
86. What is the form of SQL SELECT?
Following is the Order of SQL SELECT statement:
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.
87. How to rename a column in the SQL Server?
When it gets to SQL Server, it is impossible to rename the column with the cooperation of ALTER TABLE command, we would have to use sp_rename.
88. List some properties of Relational databases?
- In relational databases, each and every column should have an individual name.
- The series of rows and columns in relational databases is irrelevant.
- All amounts are atomic and each and every row is unique.
89. What is COMMIT?
COMMIT stores and saves all changes made by DML statements.
90. Explain CHECK Constraint.
A CHECK constraint is utilized to limit the values or type of data that can be stored and saved in a column. They are practiced to enforce domain integrity.
91. What are the values for the BOOLEAN data field?
For a BOOLEAN data field, two values are possible: -1(true) and 0(false).
92. How to present ENUMs and SETs?
ENUMs and SETs are used to show controls of two because of storage optimizations.
93. Explain federated tables.
Federated tables are the one that enables access to the tables which located on the other databases located on other servers.
94. Describe DML and DDL.
- DML- It stands for Data Manipulation Language. INSERT, UPDATE and DELETE are DML statements.
- DDL- It stands for Data Definition Language. CREATE, ALTER, DROP, RENAME are DDL statements.
95. What if a table has one column which defined as TIMESTAMP?
Timestamp field is utilized to get the updated timestamp whenever a row is altered.
96. Define ROWID.
It is an 18 character long pseudo column which is attached with each row of a table.
97. Can a View depend on another View?
Yes, A View is based on another View.
98. Do you have any kind of certification to expand your opportunities as an SQL professional?
Usually, interviewers look for applicants who are solemn about improving their career options by producing the use of further tools like certifications. Certificates are obvious proof that the candidate has put in all attempts to learn new abilities, comprehend them, and put them into use at the most excellent of their capacity. Insert the certifications, if you have any, and do hearsay about them in brief, describing what you learned from the programs and how they’ve been important to you so far.
99. Do you have any prior experience serving in an identical industry like ours?
Here comes an outspoken question. It aims to evaluate if you have the industry-specific abilities that are required for the contemporary role. Even if you do not hold all of the skills and experience, make certain to completely describe how you can still make utilization of the skills and knowledge you’ve accomplished in the past to serve the company.
100. Why are you preparing for the SQL expert position in our company specifically?
By this question, the interviewer is attempting to see how well you can influence them concerning your knowledge in the subject, besides the requirement for practicing structured SQL methodologies. It is always an advantage to already know the job specification in particular, along with the return and the aspects of the company, thereby achieving a comprehensive knowledge of what tools, services, and SQL methodologies are needed to work in the role triumphantly.
We at Testprep training hope that this article help the candidate to successfully clear the SQL Professional job Interview! The candidate can also refer to the free practice test because Practice makes a man perfect! Stay Safe!
