Advanced SQL Interview Questions And Answers Pdf
1. What is Failover clustering overview?
Answer: Failover clustering is mainly used for data availability. Typically, in a failover cluster, there are two machines.
One machine provides the basic services and the second is available to run the service when the primary system fails.
The primary system is monitored periodically to check if it works. This monitoring may be performed by the failover computer or an independent system also called a cluster controller. In an event of failure of the primary computer, the failover system takes control.
2. What is lock escalation?
Answer: Lock escalation is used to convert row locks and page locks into table locks thereby “escalating” the smaller or finer locks. This increases system performance as each lock is nothing but a memory structure. Too many locks would mean more consumption of memory. Hence, escalation is used.
Lock escalation from SQL Server 7.0 onwards is dynamically managed by SQL Server. It is the process of converting a lot of low-level locks into higher-level locks.
3. What is Built-in/Administrator?
Answer: The Builtin/Administrator account is basically used during some setup to join some machine in the domain. It should be disabled immediately thereafter. For any disaster recovery, the account will be automatically enabled. It should not be used for normal operations.
4. What is the difference between cross join and natural join?
Answer: The cross join produces the cross product or Cartesian product of two tables whereas the natural join is based on all the columns having the same name and data types in both the tables.
5. What is SQL Injection?
Answer: SQL Injection is a type of database attack technique where malicious SQL statements are inserted into an entry field of database such that once it is executed the database is opened for an attacker. This technique is usually used for attacking Data-Driven Applications to have access to sensitive data and perform administrative tasks on databases.
For Example: SELECT column_name(s) FROM table_name WHERE condition;
6. List the ways in which Dynamic SQL can be executed?
Answer:
Following are the ways in which dynamic SQL can be executed:
Write a query with parameters.
Using EXEC.
Using sp_executesql.
7. What are Views used for?
Answer: A view refers to a logical snapshot based on a table or another view. It is used for the following reasons:
- Restricting access to data.
- Making complex queries simple.
- Ensuring data independence.
- Providing different views of the same data.
8. What is Auto Increment in SQL?
Answer: Autoincrement keyword allows the user to create a unique number to get generated whenever a new record is inserted into the table.
This keyword is usually required whenever a PRIMARY KEY is used.
9. What is the difference between Union and Union All command?
Answer: The fundamental difference between Union and Union All command is, Union is by default distinct i.e. it combines the distinct result set of two or more select statements. Whereas, Union All combines all the rows including duplicates in the result set of different select statements.
10. What are transactions in SQL?
Answer: The transaction is a set of operations performed in a logical sequence. It is executed as a whole, if any statement in the transaction fails, the whole transaction is marked as failed and not committed to the database.
11. What is SQL Sandbox in SQL Server?
Answer: SQL Sandbox is the safe place in SQL Server Environment where untrusted scripts are executed. There are 3 types of SQL sandbox, such as
- Safe Access Sandbox: Here a user can perform SQL operations such as creating stored procedures, triggers, etc. but cannot have access to the memory and cannot create files.
- External Access Sandbox: User can have access to files without having a right to manipulate the memory allocation.
- Unsafe Access Sandbox: This contains untrusted codes where a user can have access to memory.
12. What is the difference between clustered and non-clustered indexes?
Answer: One table can have only one clustered index but multiple nonclustered indexes.
Clustered indexes can be read rapidly rather than non-clustered indexes.
Clustered indexes store data physically in the table or view and non-clustered indexes do not store data in the table as it has separate structure from the data row.
13. What are the different types of locks in the database?
Answer:
The different types of locks in the database are-
- Shared locks – Allows data to be read-only(Select operations), prevents the data to be updated when in the shared lock.
- Update locks – Applied to resources that can be updated. There can be only one update lock on a data at a time.
- Exclusive locks – Used to lock data being modified(INSERT, UPDATE, or DELETE) by one transaction thus ensuring that multiple updates cannot be made to the same resource at the same time.
- Intent locks – A notification mechanism using which a transaction conveys that intends to acquire a lock on data.
- Schema locks – Used for operations when schema or structure of the database is required to be updated.
- Bulk Update locks – Used in case of bulk operations when the TABLOCK hint is used.
14. What is a Table in a Database?
Answer: A table is a database object used to store records in a field in the form of columns and rows that holds data.
15. What is database testing?
Answer: Database testing involves checking the integrity of actual data in the front end with the data present in the database. It involves validating the data in the database, checking that there are no orphan records (record with a foreign key to a parent record that has been deleted”), no junk records are present, updating records in the database and verify the value in the front end.
16. What is a Relationship and what are they?
Answer: Relation or links are between entities that have something to do with each other. Relationships are defined as the connection between the tables in a database.
There are various relationships, namely:
- One to One Relationship.
- One to Many Relationship.
- Many to One Relationship.
- Self-Referencing Relationship.
17. How can you insert NULL values in a column while inserting the data?
Answer: NULL values can be inserted in the following ways:
Implicitly by omitting column from column list.
Explicitly by specifying NULL keyword in the VALUES clause
18. What is a View in SQL?
Answer: Views in SQL are kind of virtual tables. A view also has rows and columns as they are in a real table in the database. We can create a view by selecting fields from one or more tables present in the database. A View can either have all the rows of a table or specific rows based on a certain condition.
The CREATE VIEW statement of SQL is used for creating Views.
Basic Syntax:
CREATE VIEW view_name AS
SELECT column1, column2…..
FROM table_name
WHERE condition;
view_name: Name for the View
table_name: Name of the table
condition: Condition to select rows
For more details on how to create and use view, please refer to this article.
19. What is a join in SQL? What are the types of joins?
Answer: An SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. Different types of Joins are:
INNER JOIN: The INNER JOIN keyword selects all rows from both the tables as long as the condition satisfies. This keyword will create the result-set by combining all rows from both the tables where the condition satisfies i.e value of the common field will be same.
LEFT JOIN: This joins returns all the rows of the table on the left side of the join and matching rows for the table on the right side of the join. The rows for which there is no matching row on the right side, the result-set will contain null. LEFT JOIN is also known as LEFT OUTER JOIN
RIGHT JOIN: RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of the join. The rows for which there is no matching row on the left side, the result-set will contain null. RIGHT JOIN is also known as the RIGHT OUTER JOIN.
FULL JOIN: FULL JOIN creates the result-set by combining the result of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both the tables. The rows for which there is no matching, the result-set will contain NULL values.
To know about each of these joins in details, refer this.
20. What is RDBMS?
Answer: An RDBMS or Relational Database Management System is a type of DBMS having relationships between the tables using indexes and different constraints like primary key, foreign key, etc. The use of indexes and constraints helps in faster retrieval and better management of data within the databases.
21. What is the difference between DBMS and RDBMS?
Answer: The primary difference between DBMS and RDBMS is, in RDBMS we have relations between the tables of the database. Whereas in DBMS there is no relation between the tables(data may even be stored in files).
RDBMS has primary keys and data is stored in tables. DBMS has no concept of primary keys with data stored in navigational or hierarchical form.
RDBMS defines integrity constraints in order to follow ACID properties. While DBMS doesn’t follow ACID properties.
22. What is a field in a table?
Answer: A field is an entity used for storing a particular type of data within a table like numbers, characters, dates, etc.
23. How to avoid duplicate records in a query?
Answer: The SQL SELECT DISTINCT query is used to return only unique values. It eliminates all the duplicated values.
View Detailed Post.
24. What is the difference between an inner and outer join?
Answer: An inner join returns rows when there is at least some matching data between two (or more) tables that are being compared.
An outer join returns rows from both tables that include the records that are unmatched from one or both the tables.
25. What is SQL?
Answer: SQL stands for Structured Query Language. It is a language used to interact with the database, i.e to create a database, to create a table in the database, to retrieve data or update a table in the database, etc. SQL is an ANSI(American National Standards Institute) standard. Using SQL, we can do many things, for example – we can execute queries, we can insert records in a table, we can update records, we can create a database, we can create a table, we can delete a table, etc.
26. Explain how to send email from SQL database?
Answer: SQL Server has a feature for sending mails. Stored procedures can also be used for sending mail on demand. With SQL Server 2005, MAPI client is not needed for sending mails.
The following is the process for sending emails from the database.
Make sure that the SQL Server Mail account is configured correctly and enable Database Mail.
Write a script to send an e-mail. The following is the script.
USE [YourDB]
EXEC msdb.dbo.sp_send_dbmail
abc@intellipaat.com;pqr@intellipaat.com’
@body = ‘ A warm wish for your future endeavor’,
@subject = ‘This mail was sent using Database Mail’ ;
27. What are the different types of SQL commands?
Answer: SQL commands are the set of commands used to communicate and manage the data present in the database. The different type of SQL commands are-
- DDL – Data Definition Language
- DML – Data Manipulation Language
- DCL – Data Control Language
- TCL – Transactional Control Language
28. What is the temp table?
Answer: A temp table is a temporary storage structure to store the data temporarily.
29. How to make a remote connection in a database?
Answer:
The following is the process to make a remote connection in the database:
Use SQL Server Surface Area Configuration Tool for enabling the remote connection in the database.
Click on Surface Area Configuration for Services and Connections.
Click on SQLEXPRESS/Database Engine/RemoteConnections.
Select the radio button: Local and Remote Connections and select ‘Using TCP/IP only’ under Local and Remote Connections.(Azure Training )
Click on OK button / Apply button
30. What is the difference between delete, truncate and drop command?
Answer: The difference between the Delete, Truncate and Drop command is – Delete command is a DML command, it removes rows from a table based on the condition specified in the where clause, being a DML statement we can rollback changes made by delete command.
Truncate is a DDL command, it removes all the rows from a table and also frees the space held, unlike delete command. It takes a lock on the table while delete command takes a lock on rows of the table.
Drop is a DDL command, it removes the complete data along with the table structure(unlike truncate command that removes only the rows).
31. Can a table contain multiple PRIMARY KEY’s?
Answer: The short answer is no, a table is not allowed to contain multiple primary keys but it allows to have one composite primary key consisting of two or more columns.
32. What do you mean by Denormalization?
Answer: Denormalization refers to a technique which is used to access data from higher to lower forms of a database. It helps the database managers to increase the performance of the entire infrastructure as it introduces redundancy into a table. It adds the redundant data into a table by incorporating database queries that combine data from various tables into a single table.
33. What is the largest value that can be stored in a BYTE data field?
Answer: The largest number that can be represented in a single byte is 11111111 or 255. The number of possible values is 256 (i.e. 255 (the largest possible value) plus 1 (zero), or 28).
34. What are Operators available in SQL?
Answer: SQL Operator is a reserved word used primarily in an SQL statement’s WHERE clause to perform operations, such as arithmetic operations and comparisons. These are used to specify conditions in an SQL statement.
There are three types of Operators.
- Arithmetic Operators
- Comparison Operators
- Logical Operators
- View Detailed Post
35. List out the ACID properties and explain?
Answer: Following are the four properties of ACID. These guarantees that the database transactions are processed reliably.
- Atomicity
- Consistency
- Isolation
- Durability
36. What are the different types of subquery?
Answer: There are two types of subquery namely, Correlated and Non-Correlated.
Correlated subquery: These are queries which select the data from a table referenced in the outer query. It is not considered as an independent query as it refers to another table and refers the column in a table.
Non-Correlated subquery: This query is an independent query where the output of subquery is substituted in the main query.
Ques.31. Define the select into a statement.
Ans. Select into statement is used to directly select data from one table and insert into other, the new table gets created with the same name and type as of the old table-
37. What are the ACID properties?
Answer: ACID properties refer to the four properties of transactions in SQL-
- Atomicity – All the operations in the transaction are performed as a whole or not performed at all.
- Consistency – State of database changes only on successfully committed transaction.
- Isolation – Even with concurrent execution of the multiple transactions, the final state of the DB would be same as if transactions got executed sequentially. In other words, each transaction is isolated from one another.
- Durability – Even in the state of crash or power loss the state of the committed transaction remain persistent.
38. What are locks in SQL?
Answer: Locks in SQL are used for maintaining database integrity in case of concurrent execution of the same piece of data.
39. How many types of Privileges are available in SQL?
Answer: There are two types of privileges used in SQL, such as
- System Privilege: System privileges deal with an object of a particular type and specifies the right to perform one or more actions on it which include Admin allows a user to perform administrative tasks, ALTER ANY INDEX, ALTER ANY CACHE GROUP creates/ALTER/DELETE TABLE, CREATE/ALTER/DELETE VIEW, etc.
- Object Privilege: This allows to perform actions on an object or object of another user(s) viz. table, view, indexes, etc. Some of the object privileges are EXECUTE, INSERT, UPDATE, DELETE, SELECT, FLUSH, LOAD, INDEX, REFERENCES, etc.
40. Write the Syntax for STUFF function in an SQL server?
Answer:
- STUFF (String1, Position, Length, String2)
- String1 – String to be overwritten
- Position – Starting location for overwriting
- Length – Length of substitute string
- String2- String to overwrite.
41. Is it possible to import data directly from T-SQL commands without using SQL Server Integration Services? If so, what are the commands?
Answer: Yes, six commands are available to import data directly in the T-SQL language. These commands include :
BCP: The bulk copy (BCP) command of Microsoft SQL Server provides you with the ability to insert large numbers of records directly from the command line. In addition to being a great tool for command-line aficionados, BCP is a powerful tool for those seeking to insert data into a SQL Server database from within a batch file or other programmatic method.
Bulk Insert: The BULK INSERT statement was introduced in SQL Server 7 and allows you to interact with BCP (bulk copy program) via a script.
OpenRowSet: The OPENROWSET function can be referenced in the FROM clause of a query as if it were a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query might return multiple result sets, OPENROWSET returns only the first one.
OPENDATASOURCE Provides ad hoc connection information as part of a four-part object name without using a linked server name.
OPENQUERY: Executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name.
Linked Servers: Configure a linked server to enable the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server. Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle.
42. In what version of SQL Server were synonyms released?
Answer: How do synonyms work and explain its use cases? Synonyms were released with SQL Server 2005.
Synonyms enable the reference of another object (View, Table, Stored Procedure or Function) potentially on a different server, database or schema in your environment. In simple words, the original object that is referenced in the whole code is using a completely different underlying object, but no coding changes are necessary. Think of this as an alias as a means to simplify migrations and application testing without the need to make any dependent coding changes.
Synonyms can offer a great deal of value when converting underlying database objects without breaking front end or middle-tier code. This could be useful during a re-architecture or upgrade project.
Become Master of SQL by going through this SQL training course.
43. What is Normalization and what are the advantages of it?
Answer: Normalization is the process of organizing data to avoid duplication and redundancy. Some of the advantages are:
- Better Database organization
- More Tables with smaller rows
- Efficient data access
- Greater Flexibility for Queries
- Quickly find the information
- Easier to implement Security
- Allows easy modification
- Reduction of redundant and duplicate data
- More Compact Database
- Ensure Consistent data after modification
- Apart from this SQL Interview Questions Blog, if you want to get trained from professionals on this technology.
44. What is the difference between DROP and TRUNCATE?
Answer: TRUNCATE removes all rows from the table which cannot be retrieved back, DROP removes the entire table from the database and it cannot be retrieved back.
45. What is the difference between Nested Subquery and Correlated Subquery?
Answer: Subquery within another subquery is called Nested Subquery. If the output of a subquery is depending on column values of the parent query table then the query is called Correlated Subquery.
SELECT admin(SELEC Firstname+’ ‘+Lastname FROM Employee WHERE
empid=emp. admin)AS EmpAdminId FROM Employee
This query gets details of an employee from the Employee table.
46. What is Normalization? How many Normalization forms are there?
Answer: Normalization is used to organize the data in such manner that data redundancy will never occur in the database and avoid insert, update and delete anomalies.
There are 5 forms of Normalization
- First Normal Form (1NF): It removes all duplicate columns from the table. Creates a table for related data and identifies unique column values
- First Normal Form (2NF): Follows 1NF and creates and places data subsets in an individual table and defines the relationship between tables using the primary key
- Third Normal Form (3NF): Follows 2NF and removes those columns which are not related through the primary key
- Fourth Normal Form (4NF): Follows 3NF and do not define multi-valued dependencies. 4NF also known as BCNF
47. What is Relationship? How many types of Relationship are there?
Answer: The relationship can be defined as the connection between more than one tables in the database.
There are 4 types of relationships
- One to One Relationship
- Many to One Relationship
- Many to Many Relationship
- One to Many Relationship
48. State some properties of Relational databases?
Answer: In relational databases, each column should have a unique name
The sequence of rows and columns in relational databases are insignificant
All values are atomic and each row is unique
49. What are Nested Triggers?
Answer: Triggers may implement data modification logic by using INSERT, UPDATE, and DELETE statement. These triggers that contain data modification logic and find other triggers for data modification are called Nested Triggers.
50. What is the Cursor?
Answer: A cursor is a database object which is used to manipulate data in a row-to-row manner.
Cursor follows steps as given below
- Declare Cursor
- Open Cursor
- Retrieve row from the Cursor
- Process the row
- Close Cursor
- Deallocate Cursor
51. What is Collation?
Answer: Collation is a set of rules that check how the data is sorted by comparing it. Such as Character data is stored using correct character sequence along with case sensitivity, type, and accent.
52. What do we need to check in Database Testing?
Answer: Generally, in Database Testing following thing is to need to be tested
- Database Connectivity
- Constraint Check
- Required Application Field and its size
- Data Retrieval and Processing With DML operations
- Stored Procedures
- Functional flow
53. Define join and name different types of joins?
Answer: Join keyword is used to fetch data from related two or more tables. It returns rows where there is at least one match in both the tables included in the join. Read more here. ()
Type of joins are:
- Right Join
- Outer Join
- Full Join
- Cross Join
- Self Join.
54. What are Entities and Relationships?
Answer:
Entities: A person, place, or thing in the real world about which data can be stored in a database. Tables store data that (Sql Server Training )represents one type of entity. For example – A bank database has a customer table to store customer information. Customer table stores this information as a set of attributes (columns within the table) for each customer.
Relationships: Relation or links between entities that have something to do with each other. For example – The customer name is related to the customer account number and contact information, which might be in the same table. There can also be relationships between separate tables (for example, customer to accounts).
55. What is the difference between NULL value, Zero, and Blank space?
Answer: As I mentioned earlier, Null value is a field with no value which is different from zero value and blank space.
A null value is a field with no value.
Zero is a number
Blank space is the value we provide. The ASCII value of space is CHAR(32).
56. What is the difference between Having clause and Where clause?
Answer: Both specify a search condition but Having clause is used only with the SELECT statement and typically used with GROUP BY clause.
If GROUP BY clause is not used then Having behaved like WHERE clause only.
57. What is the need for group functions in SQL?
Answer: Group functions work on the set of rows and returns one result per group. Some of the commonly used group functions are AVG, COUNT, MAX, MIN, SUM, VARIANCE.
58. What are transaction and its controls?
Answer: A transaction can be defined as the sequence task that is performed on databases in a logical manner to gain certain results. Operations performed like Creating, updating, deleting records in the database comes from transactions.
In simple word, we can say that a transaction means a group of SQL queries executed on database records.
There are 4 transaction controls such as
- COMMIT: It is used to save all changes made through the transaction
- ROLLBACK: It is used to roll back the transaction such as all changes made by the transaction are reverted back and the database remains as before
- SET TRANSACTION: Set the name of the transaction
- SAVEPOINT: It is used to set the point from where the transaction is to be rolled back
59. How many Aggregate Functions are available there in SQL?
Answer: SQL Aggregate Functions calculates values from multiple columns in a table and returns a single value.
There are 7 aggregate functions we use in SQL
- AVG(): Returns the average value from specified columns
- COUNT(): Returns number of table rows
- MAX(): Returns largest value among the records
- MIN(): Returns smallest value among the records
- SUM(): Returns the sum of specified column values
- FIRST(): Returns the first value
- LAST(): Returns Last value
60. What is Stored procedure?
Answer: A Stored Procedure is a collection of SQL statements that have been created and stored in the database to perform a particular task. The stored procedure accepts input parameters and processes them and returns a single value such as a number or text value or a result set (set of rows).
Note: Browse latest SQL Interview Questions and SQL Tutorial Videos. Here you can check SQL Training details and SQL Training Videos for self learning. Contact +91 988 502 2027 for more information.