SQL Interview Questions And Answers: Learn More About SQL

1. When is the use of the UPDATE_STATISTICS command recommended? 🔄

• The UPDATE_STATISTICS command is typically used when there’s been a large processing of data, such as extensive deletions, modifications, or bulk copying into tables. This command helps update the indexes to reflect these changes.
  
  

2. What types of joins are possible in SQL Server? 🚀

• In SQL Server, the types of joins include INNER JOINs, OUTER JOINs (which are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS, and FULL OUTER JOINS), and CROSS JOINs.
  
  

3. What is the difference between a HAVING CLAUSE and a WHERE CLAUSE? 🤔

• The HAVING CLAUSE is used for specifying a search condition for a group or an aggregate, typically in conjunction with the GROUP BY clause. In the absence of GROUP BY, it acts like a WHERE clause. The WHERE CLAUSE, on the other hand, is applied to each row before they become part of the GROUP BY function in a query.
  
  

4. What is a sub-query and its properties? 🔍

• A sub-query, also known as a sub-select, is a SELECT statement nested within another SQL statement. It’s executed within parentheses and is used to return a single row as an atomic value or multiple rows using the IN keyword. Key properties include being enclosed in parentheses, placed on the right side of a comparison operator, and not containing an ORDER-BY clause. A sub-query can appear in various places like the SELECT statement column list, FROM, GROUP BY, HAVING, and ORDER BY clauses.
  
  

5. What are the types of sub-queries? 🌟

• Sub-queries can be classified as single-row subqueries (returning only one row), multiple-row subqueries (returning multiple rows), and multiple-column subqueries (returning multiple columns).
  
  

6. What is SQL Profiler? 📊

• SQL Profiler is a graphical tool for monitoring events in Microsoft SQL Server. It allows administrators to capture and analyze data about events, which is useful for identifying performance issues. Filters can be applied to reduce the size of the monitored data.
  
  

7. What are User Defined Functions in SQL Server? 🛠️

• User-Defined Functions in SQL Server are custom functions that accept parameters and return either a scalar value or a table data type. They enable more flexibility in SQL queries and data manipulation.
  
  

8. What kinds of User-Defined Functions can be created in SQL Server 2000? 📈

• There are three types: Scalar (returning a scalar data type), Inline Table-Valued (returning a table data type, similar to a parameterized view), and Multi-statement Table-Valued (also returning a table but with support for multiple T-SQL statements).
  
  

9. Which TCP/IP port does SQL Server run on, and how can it be changed? 🔌

• SQL Server typically runs on port 1433. This can be changed in the Network Utility TCP/IP properties for both client and server.
  
  

10. What are the authentication modes in SQL Server, and how can they be changed? 🔐

• SQL Server has two authentication modes: Windows mode and mixed mode (SQL & Windows). The mode can be changed in SQL Enterprise Manager under SQL Server Configuration Properties.
  
  

11. Where are SQL Server usernames and passwords stored? 🗄️

• Usernames and passwords are stored in the master database, specifically in the sysxlogins table.
  
  

12. Which command in Query Analyzer gives you the version of SQL server and operating system? 💻

• The command is: SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY('productlevel'), SERVERPROPERTY('edition').
  
  

13. What is SQL Server agent? 🕵️‍♂️

• SQL Server Agent is a key component for managing SQL Server. It is used for scheduling tasks and scripts, making the life of a database administrator easier.
  
  

14. Can a stored procedure call itself or be recursive? How many levels of SP nesting are possible? 🔄

• Yes, stored procedures can be recursive, meaning they can call themselves. Up to 32 levels of nesting are possible.
  
  

15. What is @@ERROR? ❗

• @@ERROR is an automatic variable in SQL Server that returns the error code of the last Transact-SQL statement executed. If there is no error, it returns zero. To use it further in the process, its value should be saved to a variable immediately after the statement.
  
  

16. What is RDBMS? 💾

• Relational Database Management Systems (RDBMS) maintain data records and indices in tables. They allow the creation and maintenance of relationships across different data and tables using tables, where relationships are expressed by data values rather than pointers, ensuring a high degree of data independence.
  
  

17. What is normalization in databases? 📐

• Database normalization is the process of organizing data in a database to minimize redundancy. It typically involves dividing a database into multiple tables and defining relationships between them, with the goal of isolating data so that changes in one field are made in just one table and then propagated through the rest.
  
  

18. What are the different normalization forms? 📋

• The different forms of normalization are:
  • 1NF (First Normal Form): Eliminate repeating groups.
  • 2NF (Second Normal Form): Eliminate redundant data.
  • 3NF (Third Normal Form): Eliminate columns not dependent on the primary key.
  • BCNF (Boyce-Codd Normal Form): Handle certain types of dependency anomalies.
  • 4NF (Fourth Normal Form): Isolate independent multiple relationships.
  • 5NF (Fifth Normal Form): Isolate semantically related multiple relationships.
  • ONF (Optimal Normal Form): Limited to simple (elemental) facts.
  • DKNF (Domain-Key Normal Form): Free from all modification anomalies.
    
    

19. What is a Stored Procedure? 🔄

• A stored procedure is a pre-written SQL script saved in the database that performs operations in the database. They accept input parameters and can return values. They reduce network traffic, improve performance, and ensure database integrity.
  
  

20. What is a Trigger in SQL? 🎯

• A trigger is an SQL procedure that initiates an action in response to an event (INSERT, DELETE, or UPDATE). Triggers maintain data integrity, can’t be directly executed, and can activate stored procedures.
  
  

21. What is a View in SQL? 👀

• A view is a subset of a table and can be used for data retrieval, updating, or deleting. Views reflect the current data in the table they are derived from, and changes in the view affect the source table.
  
  

22. What is an Index in SQL? 🔍

• An index is a database structure that speeds up data retrieval from a table by creating pointers to the data. There are two types: clustered indexes (reorganizing the physical order of the table) and non-clustered indexes (external to the table).
  
  

23. What is the difference between clustered and non-clustered indexes? 📈

• A clustered index sorts and stores the data rows of the table in order based on the index key. A non-clustered index, however, creates a separate structure to maintain data pointers to the original table rows.
  
  

24. What are the different index configurations a table can have? ⚙️

• A table’s index configurations can include no indexes, a clustered index, a clustered index with many non-clustered indexes, a non-clustered index, or many non-clustered indexes.
  
  

25. What are cursors in SQL? 🖱️

• Cursors are objects used to manipulate data in a set on a row-by-row basis. They involve several steps: declare, open, fetch, process, close, and deallocate the cursor.
  
  

26. What is the use of DBCC commands? 🛠️

• DBCC (Database Consistency Checker) commands are used to ensure the integrity of databases, including maintenance, validation tasks, and status checks, such as DBCC CHECKDB, CHECKALLOC, and CHECKFILEGROUP.
  
  

27. What is a Linked Server in SQL? 🔗

• Linked Servers in SQL Server allow connection and querying of other SQL Server databases. They facilitate easy and clean SQL statements for remote data retrieval and manipulation.
  
  

28. What is Collation in SQL? 📜

• Collation refers to the set of rules that determine how data is sorted and compared, particularly in terms of character sequence, case-sensitivity, accent marks, and character width.
  
  

29. What are different types of Collation Sensitivity? 🔠

• Collation sensitivity types include case sensitivity, accent sensitivity, Kana sensitivity (for Japanese characters), and width sensitivity (difference in single-byte and double-byte characters).
  
  

30. What’s the difference between a primary key and a unique key? 🔑

• Both ensure uniqueness, but a primary key creates a clustered index and doesn’t allow NULLs, whereas a unique key creates a non-clustered index and allows one NULL.
  
  

31. How to implement one-to-one, one-to-many, and many-to-many relationships in table design? 🔄

• One-to-One relationships can be implemented as a single table or two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary and foreign key relationships. Many-to-Many relationships use a junction table with composite primary keys.
  
  

32. What is NOLOCK? 🚫🔒

• NOLOCK is a query optimizer hint that allows dirty reads by not taking locks when reading data. It improves concurrency but risks reading data that might be in the process of being updated.
  
  

33. What is the difference between DELETE and TRUNCATE commands? 🗑️

• DELETE removes rows based on a condition and logs each deletion. TRUNCATE removes all rows by deallocating data pages, is faster, does not log each deletion, and cannot be used on tables referenced by foreign key constraints.
  
  

34. What’s the difference between a Function and Stored Procedure? 🤔

• User-Defined Functions (UDFs) can be used in SQL statements and can return tables, allowing them to be used in joins. Stored procedures are not event-driven and can’t be used in the same way as UDFs in SQL statements.