1. What's the maximum size of a row?

• 8060 bytes.

2. Explain Active/Active and Active/Passive cluster configurations

• Active/Active and Active/Passive are cluster configurations in SQL Server. Active/Active clusters have multiple nodes that can host instances simultaneously, providing load balancing. Active/Passive clusters have one active node and one or more passive nodes that act as failover nodes.

3. Explain the architecture of SQL Server

• SQL Server architecture includes components such as the relational engine, storage engine, and SQL OS.

4. What is lock escalation?

• Lock escalation is the process of converting many fine-grained locks (like row or page locks) into higher-level locks (like table locks) to reduce memory usage. From SQL Server 7.0 onwards, lock escalation is dynamically managed by SQL Server.

5. What is the difference between DELETE

• TABLE and TRUNCATE TABLE commands? DELETE TABLE is a logged operation, so each row's deletion is logged in the transaction log, making it slower. TRUNCATE TABLE deletes all rows but logs the deallocation of data pages, making it faster. TRUNCATE TABLE can also be rolled back.

6. Explain the storage models of OLAP

• OLAP storage models include MOLAP (Multidimensional OLAP), ROLAP (Relational OLAP), and HOLAP (Hybrid OLAP).

7. What are constraints? Explain different types of constraints

• Constraints enforce data integrity automatically. Types include:

• NOT NULL: Ensures that a column cannot have NULL values.

• CHECK: Ensures that all values in a column satisfy a condition.

• UNIQUE: Ensures that all values in a column are unique.

• PRIMARY KEY: Uniquely identifies each row in a table.

• FOREIGN KEY: Ensures referential integrity by linking to a primary key in another table.

8. What is an index? What are the types of indexes? How many clustered indexes can be created on a table?

• Indexes speed up data retrieval. Types include:
• Clustered Index: Orders the table rows based on the index key. Only one clustered index per table is allowed.
• Non-Clustered Index: Separate from the table data, with its own storage and a B-tree structure.
• Creating an index on each column improves query performance but can slow data modification and consume more disk space.

9. What is RAID and what are different types of RAID configurations?

• RAID (Redundant Array of Independent Disks) provides fault tolerance and performance improvements. Common RAID levels include:

• RAID 0: Striping, no redundancy.

• RAID 1: Mirroring.

• RAID 5: Striping with parity.

• RAID 6: Striping with double parity.

• RAID 10: Combination of mirroring and striping.

• RAID 50: Combination of RAID 5 and RAID 0.

10. What are the steps you will take to improve performance of a poor performing query?

• Potential issues include missing indexes, table scans, outdated statistics, blocking, and poorly written queries. Tools for troubleshooting include: SET SHOWPLAN_ALL ON, SET

SHOWPLAN_TEXT ON

SET STATISTICS IO ON

SQL Server Profiler

Windows Performance Monitor

Graphical execution plans in Query Analyzer

11. What are the steps you will take if you are tasked with securing an SQL Server?

Security measures include:

• Prefer NT authentication.
• Use server, database, and application roles for access control.
• Secure physical database files with NTFS permissions.
• Use a strong, unguessable SA password.
• Restrict physical access and rename the Administrator account.
• Disable the Guest account, enable auditing, and use encryption (SSL, firewalls).

12. What is a deadlock and what is a live lock? How will you go about resolving deadlocks?

• Deadlock: Two processes wait indefinitely for each other’s locks. SQL Server detects and terminates one process to resolve it.
• Live Lock: Occurs when a series of shared locks repeatedly denies an exclusive lock. SQL Server detects after multiple denials and refuses further shared locks.

13. What is blocking and how would you troubleshoot it?

• Blocking occurs when one connection holds a lock that another connection needs, causing the latter to wait. Troubleshoot with:

sp_who2, sp_lock system stored procedures. SQL Server Profiler. Query execution plans.

14. Explain CREATE DATABASE syntax Basic syntax:

CREATE DATABASE database_name
[ON {PRIMARY | FILEGROUP ...}]
[LOG ON {PRIMARY | FILEGROUP ...}]
[OPTION (option_list ...)]

For example, creating a database with specific filegroups and sizes.

15. How to restart SQL Server in single-user mode? How to start SQL Server in minimal configuration mode?

• Use sqlservr.exe with the -m parameter for single-user mode and -f for minimal configuration mode.

16. As a part of your job, what are the DBCC commands that you commonly use for database maintenance?

• Common DBCC commands include:
• DBCC CHECKDB
• DBCC CHECKTABLE
• DBCC CHECKCATALOG
• DBCC CHECKALLOC
• DBCC SHOWCONTIG
• DBCC SHRINKDATABASE
• DBCC SHRINKFILE

17. What are statistics, under what circumstances they go out of date, how do you update them?

• Statistics help the query optimizer by providing data distribution information. Update statistics if:
• There are significant changes in the indexed column's values.
• Large data modifications occur.
• The table is repopulated or the database is upgraded.
• Use UPDATE STATISTICS command or rely on automatic updates.

18. What are the different ways of moving data/databases between servers and databases in SQL Server?

Methods include:

• Backup/Restore
• Detach/Attach
• Replication
• Data Transformation Services (DTS)
• Bulk Copy Program (BCP)
• Log Shipping
• INSERT...SELECT, SELECT...INTO
• Generating INSERT scripts

19. Explain different types of BACKUPs available in SQL Server. Given a particular scenario, how would you go about choosing a backup plan?

Types include:

• Full Backup: A complete backup of the database.
• Differential Backup: Backs up changes since the last full backup.
• Transaction Log Backup: Captures changes since the last log backup.
• File Group Backup: Backs up specific filegroups.
• Choose based on recovery needs, backup frequency, and storage.

20. What is database replication? What are the different types of replication you can set up in SQL Server?

Replication copies/moves data between databases. Types include:

• Snapshot Replication: Periodic snapshots of data.
• Transactional Replication: Continuous data changes with options for immediate or queued updating subscribers.
• Merge Replication: Combines data from multiple sources, allowing updates at any node.

21. How to determine the service pack currently installed on SQL Server?

• Use the global variable @@VERSION to retrieve build information, which indicates the service pack level.

22. What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?

Cursors allow row-by-row processing of results. Types include:

• Static: Snapshot of data.

• Dynamic: Reflects changes in data.

• Forward-only: Can only move forward.

• Keyset-driven: Reflects changes to the set of rows.

• Disadvantages: Performance overhead, resource consumption, and more network roundtrips. Avoid by using set-based operations or while loops.

23. Write down the general syntax for a SELECT statement covering all the options

• Basic syntax:

SELECT select_list
[INTO new_table_]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC]]

24. What is a join and explain different types of joins?

• Joins are used to relate tables and select data based on relationships. Types include:
• INNER JOIN: Returns rows with matching values in both tables.
• LEFT (OUTER) JOIN: Returns all rows from the left table and matched rows from the right table.
• RIGHT (OUTER) JOIN: Returns all rows from the right table and matched rows from the left table.
• FULL (OUTER) JOIN: Returns rows when there is a match in one of the tables.
• CROSS JOIN: Returns the Cartesian product of two tables.

25. What is a view? How to create a view? How to drop a view? A view is a virtual table based on the result of a SELECT query. Create a view using:

CREATE VIEW view_name AS
SELECT column_list
FROM table_source
[WHERE search_condition]

Drop a view with:

DROP VIEW view_name

26. What is the purpose of the SQL Server Profiler? What are some of the tasks you can accomplish using SQL Server Profiler?

• SQL Server Profiler is used to monitor and capture SQL Server events. Tasks include:
• Tracking SQL queries and execution times.
• Identifying slow queries and performance bottlenecks.
• Auditing and analyzing SQL Server activity.
• Diagnosing and troubleshooting issues.

27. What is SQL Server Agent? What are some of the tasks you can accomplish using SQL Server Agent?

• SQL Server Agent automates and schedules tasks. Tasks include:
• Scheduling jobs such as backups and index maintenance.
• Executing SSIS packages.
• Running T-SQL scripts.
• Alerting and notification setup.
• Automating routine administrative tasks.

28. Explain the difference between a UNION and a UNION ALL. What are the requirements for using UNION?

• UNION combines results from multiple queries and removes duplicate rows. UNION ALL includes all rows, including duplicates. Requirements:
• Same number of columns.
• Compatible data types in corresponding columns.

29. What is normalization? What are different levels of normalization? Explain with examples.

• Normalization organizes data to reduce redundancy and improve integrity. Levels include:
• First Normal Form (1NF): Eliminate duplicate columns from the same table.
• Second Normal Form (2NF): Remove subsets of data that apply to multiple rows and create separate tables.
• Third Normal Form (3NF): Remove columns that are not dependent on the primary key.
• Boyce-Codd Normal Form (BCNF): A stronger version of 3NF.

30. What is a subquery? What are the different types of subqueries?

• A subquery is a query nested inside another query. Types include:
• Single-row subquery: Returns one row.
• Multiple-row subquery: Returns multiple rows.
• Correlated subquery: References columns from the outer query.
• Non-correlated subquery: Independent of the outer query.

31. What is the difference between a scalar function and a table-valued function?

• Scalar Function: Returns a single value (e.g., GETDATE(), LEN()).
• Table-Valued Function: Returns a table (e.g., TABLE data type), used in FROM clauses like a table.

32. What is a stored procedure? How do you create a stored procedure?

• A stored procedure is a precompiled collection of SQL statements.

CREATE PROCEDURE procedure_name [parameters] AS BEGIN SQL statements END

33. What is a trigger? Explain different types of triggers.

• A trigger is a special type of stored procedure that automatically executes in response to certain events. Types include:

• DML Trigger: Fires in response to INSERT, UPDATE, DELETE.

• DDL Trigger: Fires in response to data definition language (DDL) changes like CREATE, ALTER, DROP.

• LOGON Trigger: Fires in response to a user logging in.

34. What is an execution plan? How can you view the execution plan of a query?

• An execution plan shows how SQL Server will execute a query. View using:
• SQL Server Management Studio (SSMS) graphical execution plan.
• SET SHOWPLAN_XML ON for XML plans.
• SET SHOWPLAN_TEXT ON for text-based plans.

35. What are transactions? Explain the ACID properties of transactions.

• Transactions are sequences of operations performed as a single unit.
• ACID properties:
• Atomicity: All operations are completed or none.
• Consistency: Database remains in a consistent state before and after the transaction.
• Isolation: Transactions are isolated from each other.
• Durability: Changes are permanent once committed.

36. What are the different types of SQL Server backups?

Types include:

• Full Backup: A complete backup of the database.
• Differential Backup: Backs up changes since the last full backup.
• Transaction Log Backup: Captures changes since the last log backup.
• File Backup: Backs up specific files.
• Filegroup Backup: Backs up specific filegroups.

37. How can you improve SQL Server performance?

Techniques include:

• Optimizing queries and indexing.
• Updating statistics.
• Monitoring and addressing bottlenecks.
• Configuring SQL Server memory and processor settings.
• Using database compression.

38. What is a schema? How do you create and modify schemas?

• A schema is a container for database objects. CREATE SCHEMA schema_name Modify using:

ALTER SCHEMA schema_name TRANSFER object_name

39. What are dynamic SQL and prepared statements?

• Dynamic SQL: SQL statements constructed and executed at runtime.
• Prepared Statements: SQL statements prepared and optimized before execution, improving performance and security.

40. What is SQL injection? How can you prevent it?

• SQL injection is a code injection technique where malicious SQL is executed. Prevent by:
• Using parameterized queries.
• Validating and sanitizing inputs.
• Employing ORM frameworks.

41. What is data warehousing?

• Data warehousing is the process of collecting, storing, and analyzing large volumes of data from multiple sources for decision-making. It involves ETL (Extract, Transform, Load) processes, data integration, and OLAP.

42. Explain the different types of indexes available in SQL Server.

• Types include:
• Clustered Index: Sorts and stores data rows based on the index key.
• Non-Clustered Index: Stores a separate structure with pointers to data rows.
• Unique Index: Ensures all values are unique.
• Composite Index: An index on multiple columns.

43. What are the best practices for SQL Server security?

• Best practices include:
• Use least privilege principle for accounts.
• Regularly update and patch SQL Server.
• Encrypt sensitive data.
• Implement strong authentication and authorization policies.

44. How can you perform data migration between different SQL Server versions?

• Methods include:
• Backup and restore across versions.
• Using SQL Server Integration Services (SSIS).
• Export/Import Wizard for schema and data.
• Generate scripts for schema and data.

45. What is SQL Server Reporting Services (SSRS)? What are the components of SSRS?

• SSRS is a reporting tool that allows creating, managing, and delivering reports. Components include:
• Report Designer: Tool for creating reports.
• Report Server: Manages and processes reports.
• Report Manager: Web-based interface for managing reports and subscriptions.

46. What is SQL Server Integration Services (SSIS)? What are the key components of SSIS?

• SSIS is a data integration tool for ETL processes.
• Key components include:
• Data Flow Tasks: For data extraction, transformation, and loading.
• Control Flow Tasks: For managing the workflow of tasks.
• SSIS Packages: Containers for control and data flow tasks.
• Data Sources and Destinations: For connecting to various data sources.

47. What is SQL Server Analysis Services (SSAS)? What are the key components of SSAS?

• SSAS is used for data analysis and OLAP.
• Key components include:
• Data Warehousing: Storage of data for analysis.
• Data Cubes: Multidimensional data structures for querying.
• Dimensions: Attributes by which data is analyzed.
• Measures: Quantitative data points for analysis.

48. How do you troubleshoot SQL Server performance issues?

• Troubleshooting steps include:
• Analyze execution plans for queries.
• Monitor SQL Server performance counters.
• Check for blocking and deadlocks.
• Review SQL Server Profiler traces.
• Optimize queries and indexes.

49. What is database mirroring?

• Database mirroring is a high-availability solution that involves maintaining a mirror copy of a database on another server. It supports automatic failover, and manual failover, and allows read-only access to the mirrored database.

50. What is log shipping?

• Log shipping is a disaster recovery solution that involves regularly backing up transaction logs from a primary database and restoring them on a standby database. It supports automated backup, restore, and monitoring.

51. What are SQL Server's built-in functions for string manipulation?

• Functions include:
• LEN(): Returns the length of a string.
• SUBSTRING(): Extracts a substring from a string.
• REPLACE(): Replaces occurrences of a substring.
• CONCAT(): Concatenates multiple strings.
• LTRIM(), RTRIM(): Removes spaces from the left or right of a string.

52. What are SQL Server's built-in functions for date and time manipulation?

• Functions include:
• GETDATE(): Returns the current date and time.
• DATEADD(): Adds an interval to a date.
• DATEDIFF(): Calculates the difference between two dates.
• FORMAT(): Formats a date or time value. YEAR(), MONTH(), DAY(): Extract specific parts of a date.

53. What is SQL Server's architecture for handling large volumes of data?

• SQL Server architecture for large data volumes includes features like partitioning, indexing, data compression, and efficient query processing. For detailed information, refer to SQL Server Books Online.

54. Explain the concept of database snapshots.

• Database snapshots provide a read-only, static view of a database at a given point in time. Useful for reporting, auditing, and disaster recovery.

55. What is a columnstore index?

• A columnstore index stores data in columns rather than rows, optimized for read-heavy operations and large data warehouses. It improves query performance by reducing the amount of data read.

56. How do you manage SQL Server user permissions?

• Manage permissions using:
• GRANT: To provide permissions.
• DENY: To prevent permissions.
• REVOKE: To remove previously granted permissions.
• Use roles to group and manage permissions.

57. What are SQL Server security best practices for securing data?

• Best practices include:
• Use encryption for sensitive data.
• Implement auditing and logging.
• Regularly update and patch SQL Server.
• Use strong passwords and authentication methods.

58. What is the SQL Server Agent's role in database management?

• SQL Server Agent automates administrative tasks such as backups, maintenance plans, and running jobs. It schedules and manages these tasks to ensure regular execution.

59. What are some common SQL Server performance tuning techniques?

• Techniques include:
• Index optimization.
• Query optimization and rewriting.
• Regularly updating statistics.
• Using proper data types and structures.
• Monitoring and addressing bottlenecks.

60. How do you back up and restore SQL Server databases?

• Backup: Use BACKUP DATABASE command for full, differential, or transaction log backups.
• Restore: Use RESTORE DATABASE command to restore from backup files. Specify recovery options as needed.

61. What are some key SQL Server configuration settings for optimal performance?

• Key settings include:
• Memory allocation.
• CPU affinity.
• I/O performance settings.
• Database and transaction log file placement.

62. How do you implement high availability in SQL Server?

• Implement high availability using:
• Always On Availability Groups.
• Failover Cluster Instances.
• Database Mirroring.
• Log Shipping.

63. What is SQL Server Profiler and how do you use it for performance tuning?

• SQL Server Profiler captures and analyzes SQL Server events. Use it to monitor query performance, identify slow queries, and diagnose issues.

64. What is a partitioned table in SQL Server?

• A partitioned table divides a large table into smaller, manageable pieces based on a partitioning key. It improves query performance and maintenance.

65. What are some common SQL Server best practices for database design?

• Best practices include:
• Normalize data to reduce redundancy.
• Use indexing for frequently queried columns.
• Implement proper data types and constraints.
• Regularly review and optimize queries.

66. How do you perform database maintenance in SQL Server?

• Maintenance tasks include:
• Regular backups.
• Index rebuilding and reorganizing.
• Updating statistics.
• Checking database integrity.

67. What are SQL Server's built-in functions for mathematical calculations?

• Functions include:
• ABS(): Returns the absolute value.
• ROUND(): Rounds a number to a specified decimal place.
• CEILING(), FLOOR(): Rounds up or down.
• POWER(), SQRT(): Calculates powers and square roots.

68. What is SQL Server's role in data warehousing?

• SQL Server supports data warehousing through its data integration (SSIS), analysis (SSAS), and reporting (SSRS) tools. It provides capabilities for ETL processes, OLAP, and data visualization.

69. How do you use SQL Server's dynamic management views (DMVs) for performance monitoring?

• DMVs provide insights into server performance and health.
• Common DMVs include:
• sys.dm_exec_requests: Shows currently executing queries.
• sys.dm_exec_query_stats: Provides query performance statistics.
• sys.dm_os_performance_counters: Displays performance counters.

70. What is SQL Server's role in application development?

• SQL Server provides a robust database platform for application development, including data storage, retrieval, and manipulation. It supports advanced features like stored procedures, triggers, and functions.

71. What are some key considerations for SQL Server security?

• Considerations include:
• Implementing strong authentication and authorization.
• Encrypting sensitive data.
• Regularly auditing access and changes.
• Keeping SQL Server and related software up to date.

72. What is an SQL Server data file and log file?

• Data File: Stores database objects like tables and indexes. Typically with extensions .mdf (primary) and .ndf (secondary).
• Log File: Stores transaction logs. Usually with extension .ldf.

73. How do you configure SQL Server to use multiple CPUs?

• Configure CPU affinity settings in SQL Server to allocate multiple CPUs for SQL Server processing. Adjust settings using SQL Server Management Studio or the sp_configure system stored procedure.

74. What are SQL Server's built-in functions for data conversion?

• Functions include:
• CAST(): Converts one data type to another.
• CONVERT(): Converts data types with formatting options.
• PARSE(), TRY_PARSE(): Converts strings to date/time values.

75. What is SQL Server's role in cloud computing?

• SQL Server can be deployed on cloud platforms like Azure, providing scalable, managed database services with features like high availability, backup, and recovery.

76. How do you configure SQL Server for disaster recovery?

• Configure disaster recovery using:
• Backup and Restore: Regular backups and offsite storage.
• Log Shipping: Automated log backups and restores.
• Always On Availability Groups: High availability and failover clustering.

77. What are SQL Server's built-in functions for data aggregation?

• Functions include:
• SUM(): Calculates the total sum.
• AVG(): Calculates the average.
• COUNT(): Counts rows or non-NULL values.
• MIN(), MAX(): Finds the minimum and maximum values.

78. What is the difference between the “DELETE” and “TRUNCATE” commands?

• The DELETE command is used to remove rows from a table based on a WHERE condition whereas TRUNCATE removes all rows from a table.
• So we can use a where clause with DELETE to filter and delete specific records whereas we cannot use a Where clause with TRUNCATE.
• DELETE is executed using a row lock, each row in the table is locked for deletion whereas TRUNCATE is executed using a table lock and the entire table is locked for removal of all records.
• DELETE is a DML command whereas TRUNCATE is a DDL command.
• DELETE retains the identity of the column value whereas in TRUNCATE, the Identify column is reset to its seed value if the table contains any identity column.
• To use Delete you need DELETE permission on the table whereas to use Truncate on a table you need at least ALTER permission on the table.
• DELETE uses more transaction space than the TRUNCATE statement whereas Truncate uses less transaction space than DELETE statement.
• DELETE can be used with indexed views whereas TRUNCATE cannot be used with indexed views.
• The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row whereas TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
• Delete activates a trigger because the operation is logged individually whereas TRUNCATE TABLE can't activate a trigger because the operation does not log individual row deletions.

79. What is the difference between the “WHERE” clause and the “HAVING” clause?

• WHERE clause can be used with a Select, Update and Delete Statement Clause but the HAVING clause can be used only with a Select statement.
• We can't use an aggregate functions in the WHERE clause unless it is in a sub-query contained in a HAVING clause whereas we can use an aggregate function in the HAVING clause. We can use a column name in the HAVING clause but the column must be contained in the group by clause.
• WHERE is used before the GROUP BY clause whereas a HAVING clause is used to impose a condition on the GROUP Function and is used after the GROUP BY clause in the query.
• A WHERE clause applies to each and every row whereas a HAVING clause applies to summarized rows (summarized with GROUP BY).
• In the WHERE clause the data that is fetched from memory depending on a condition whereas in HAVING the completed data is first fetched and then separated depending on the condition.

80. What is the difference between “Primary Key” and “Unique Key”?

• We can have only one Primary Key in a table whereas we can have more than one Unique Key in a table.
• The Primary Key cannot have a NULL value whereas a Unique Key may have only one null value.
• By default, a Primary Key is a Clustered Index whereas by default, a Unique Key is a unique non-clustered index.
• A Primary Key supports an Auto Increment value whereas a Unique Key doesn't support an Auto Increment value.

81. What is the difference between a “Local Temporary Table” and “Global Temporary Table”?

• A Local Temporary Table is created by giving it a prefix of # whereas a Global Temporary Table is created by giving it a prefix of ##.
• A Local Temporary Table cannot be shared among multiple users whereas a Global Temporary Table can be shared among multiple users.
• A Local Temporary Table is only available to the current DB connection for the current user and are cleared when the connection is closed whereas a Global Temporary Table is available to any connection once created. They are cleared when the last connection is closed.

82. What are super, primary, candidate and foreign keys?

• A super key is a set of attributes of a relation schema upon which all attributes of the schema are functionally dependent. No two rows can have the same value of super key attributes.
• A Candidate key is minimal super key, i.e., no proper subset of Candidate key attributes can be a super key.
• A Primary Key is one of the candidate keys. - -- One of the candidate keys is selected as most important and becomes the primary key. There cannot be more that one primary keys in a table.
• Foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table.

83. What is the difference between primary key and unique constraints?

• Primary key cannot have NULL value, the unique constraints can have NULL values.
• There is only one primary key in a table, but there can be multiple unique constrains.

84. What is database normalization?

• It is a process of analyzing the given relation schemas based on their functional dependencies and primary keys to achieve the following desirable properties:

1. Minimizing Redundancy

2. Minimizing the Insertion, Deletion, And Update Anomalies

• Relation schemas that do not meet the properties are decomposed into smaller relation schemas that could meet desirable properties.

85. What is SQL?

• SQL is Structured Query Language designed for inserting and modifying in a relational database management system.

86. What are the differences between DDL, DML and DCL in SQL?

• Following are some details of three. DDL stands for Data Definition Language. SQL queries like CREATE, ALTER, DROP and RENAME come under this.
• DML stands for Data Manipulation Language. SQL queries like SELECT, INSERT and UPDATE come under this.
• DCL stands for Data Control Language. SQL queries like GRANT and REVOKE come under this.

87. What is Join?

• An SQL Join is used to combine data from two or more tables, based on a common field between them. For example, consider the following two tables. Student Table EnrollNo StudentName Address 1000 geek1 geeksquiz1 1001 geek2 geeksquiz2 1002 geek3 geeksquiz3 StudentCourse Table CourseID EnrollNo 1 1000 2 1000 3 1000 1 1002 2 1003
• Following is join query that shows names of students enrolled in different courseIDs. SELECT StudentCourse.CourseID, Student.StudentName FROM StudentCourse INNER JOIN Customers ON StudentCourse.EnrollNo = Student.EnrollNo ORDER BY StudentCourse.CourseID;
• The above query would produce following result. CourseID StudentName 1 geek1 1 geek2 2 geek1 2 geek3 3 geek1

88. What is Identity?

• Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers; the value of this cannot be controlled. Identity/GUID columns do not need to be indexed.

89. What is a view in SQL? How to create one

• A view is a virtual table based on the result-set of an SQL statement. We can create using create view syntax. CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

90. What are the uses of view?

• Views can represent a subset of the data contained in a table; consequently, a view can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table. Views can join and simplify multiple tables into a single virtual table
• Views can act as aggregated tables, where the database engine aggregates data (sum, average etc.) and presents the calculated results as part of the data
• Views can hide the complexity of data; for example a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table.
• Depending on the SQL engine used, views can provide extra security.

91. What is a Trigger?

• A Trigger is a code that is associated with insert, update or delete operations. The code is executed automatically whenever the associated query is executed on a table.
• Triggers can be useful to maintain integrity in the database.

92. What is a stored procedure?

• A stored procedure is like a function that contains a set of operations compiled together. It contains a set of operations that are commonly used in an application to do some common database tasks.

93. What is the difference between Trigger and Stored Procedure?

• Unlike Stored Procedures, Triggers cannot be called directly. They can only be associated with queries.

94. What is a transaction? What are ACID properties?

• A Database Transaction is a set of database operations that must be treated as whole, means either all operations are executed or none of them.
• An example can be bank transaction from one account to another account. Either both debit and credit operations must be executed or none of them.
• ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably.

95. What are indexes?

• A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data.
• Data can be stored only in one order on disk. - To support faster access according to different values, faster search like binary search for different values is desired, For this purpose, indexes are created on tables. These indexes need extra space on disk, but they allow faster search according to different frequently searched values.

96. What are clustered and non-clustered Indexes?

• Clustered indexes is the index according to which data is physically stored on disk. Therefore, only one clustered index can be created on a given database table. Non-clustered indexes don’t define physical ordering of data, but logical ordering. Typically, a tree is created whose leaf point to disk records. B-Tree or B+ tree are used for this purpose.

97. What are Primary Keys and Foreign Keys?

• Primary Key: Unique identifier for each row in a table, must contain unique values and cannot be null. A table can only have one primary key. Foreign Key: Ensures data integrity and represents the relationship between tables.

98. What are the different types of SQL or different commands in SQL?

• DDL (Data Definition Language): Defines the structure that holds the data.
• DML (Data Manipulation Language): Used for manipulation of the data itself. Typical operations include Insert, Delete, Update, and Retrieve.
• DCL (Data Control Language): Controls the visibility of data, such as granting database access and setting privileges.
• TCL (Transaction Control Language): Manages transactions in the database.

99. What are the advantages of SQL?

• SQL is not proprietary and is supported by almost every major DBMS (Oracle, SQL Server, MySQL, etc.).
• SQL is easy to learn, using descriptive English words.
• SQL is powerful and capable of performing complex and sophisticated database operations.

100. What is a field in a database?

• Field: An area within a record reserved for a specific piece of data (e.g., Employee Name, Employee ID).

101. What is a Record in a database?

• Record: A collection of values/fields of a specific entity (e.g., Employee, Salary).

102. What is a Table in a database?

• Table: A collection of records of a specific type (e.g., Employee table, Salary table).

103. What is a database transaction?

• Database Transaction: Takes the database from one consistent state to another. If a transaction fails, the database returns to its prior state. If successful, the system reflects the changes permanently.

104. What are the properties of a transaction?

• Properties of a transaction can be summarized as ACID properties:
• Atomicity: All steps in a transaction must complete; if one fails, all are rolled back.
• Consistency: Database moves from one consistent state to another after a successful transaction.
• Isolation: Transactions operate independently.
• Durability: Once completed, updates are permanent and available for all other transactions.

105. What is a Database Lock?

• A database lock informs a transaction if the data item in question is currently being used by other transactions.

106. What are the types of locks?

• Shared Lock: When a shared lock is applied on a data item, other transactions can only read the item, but cannot write into it.
• Exclusive Lock: When an exclusive lock is applied on a data item, other transactions cannot read or write into the data item.

107. What are the different types of normalization?

• First Normal Form (1NF): A relation is in 1NF if all underlying domains contain atomic values only. After 1NF, redundant data can still exist.
• Second Normal Form (2NF): A relation is in 2NF if it is in 1NF and every non-key attribute is fully dependent on the primary key. Redundant data can still exist after 2NF.
• Third Normal Form (3NF): A relation is in 3NF if it is in 2NF and every non-key attribute is non-transitively dependent on the primary key.

108. What is a primary key?

• A primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. Conditions:
• No two rows can have the same primary key value.
• Every row must have a primary key value.
• Primary key field cannot be null.
• Values in primary key columns can never be modified or updated.

109. What is a Composite Key?

• A composite primary key is a type of candidate key representing a set of columns whose values uniquely identify every row in a table. For example, if "Employee_ID" and "Employee Name" are combined to uniquely identify a row, it's called a composite key.

110. What is a Composite Primary Key?

• A composite primary key is a set of columns whose values uniquely identify every row in a table. The table with a composite primary key will be indexed based on the columns specified in the primary key. This key will be referred to in foreign key tables. For example, if the combined effect of columns "Employee_ID" and "Employee Name" is required to uniquely identify a row, it's called a composite primary key.

111. What is a Foreign Key?

• When a "one" table's primary key field is added to a related "many" table to create the common field which relates the two tables, it is called a foreign key in the "many" table. For example, the salary of an employee is stored in the salary table. The relation is established via the foreign key column “Employee_ID_Ref” which refers to the “Employee_ID” field in the Employee table.

112. What is a Unique Key?

• A unique key is similar to a primary key with the difference being the existence of null. A unique key field allows one value as a NULL value.

113. Define SQL Insert Statement.

• SQL INSERT statement is used to add rows to a table. For a full row insert, SQL Query should start with the “insert into” statement followed by the table name and values command, followed by the values that need to be inserted into the table. Insert can be used in several ways:
• To insert a single complete row.
• To insert a single partial row.

114. Define SQL Update Statement.

• SQL Update is used to update data in a row or set of rows specified in the filter condition. The basic format of an SQL UPDATE statement is: Update command followed by the table to be updated and SET command followed by column names and their new values followed by the filter condition that determines which rows should be updated.

115. Define SQL Delete Statement.

• SQL Delete is used to delete a row or set of rows specified in the filter condition. The basic format of an SQL DELETE statement is: DELETE FROM command followed by the table name followed by the filter condition that determines which rows should be deleted.

116. What are wildcards used in the database for Pattern Matching?

• SQL LIKE operator is used for pattern matching. SQL 'Like' command takes more time to process. Suggestions:
• Don't overuse wild cards. If another search operator will do, use it instead.
• When using wild cards, try not to use them at the beginning of the search pattern, unless necessary. Search patterns that begin with wild cards are the slowest to process.
• Pay careful attention to the placement of the wild card symbols. Misplacement might not return the intended data.

117. Define Join and explain different types of joins.

• Join keyword is used to fetch data from related tables and returns rows when there is at least one match in both tables. Types of joins:
• Right Join: Returns all rows from the right table, even if there are no matches in the left table.
• Outer Join: Returns rows when there is a match in one of the tables.
• Left Join: Returns all rows from the left table, even if there are no matches in the right table.
• Full Join: Returns rows when there is a match in one of the tables.

118. What is a Self-Join?

• Self-join is a query used to join a table to itself. Aliases should be used for the same table comparison.

119. What is Cross Join?

• Cross Join will return all records where each row from the first table is combined with each row from the second table.

SQL Interview Questions and Answers on Database Views

1. What is a view?

• Views are virtual tables. Unlike tables that contain data, views simply contain queries that dynamically retrieve data when used.

2. What is a materialized view?

• Materialized views are also views but are disk-based. Materialized views get updated at specific intervals, based on the interval specified in the query definition. We can index materialized views.

3. What are the advantages and disadvantages of views in a database?

Advantages:

• Views don't store data in a physical location.
• Views can be used to hide some columns from the table.
• Views can provide access restrictions, as data insertion, update, and deletion are not possible on the view.

Disadvantages:

• When a table is dropped, the associated view becomes irrelevant.
• Since views are created when a query requesting data from the view is triggered, they are a bit slow.
• When views are created for large tables, they occupy more memory.
• SQL Interview Questions and Answers on Stored Procedures and Triggers

4. What is a stored procedure?

• A stored procedure is a function that contains a collection of SQL queries. The procedure can take inputs, process them, and send back outputs.

5. What are the advantages of a stored procedure?

• Stored Procedures are pre-compiled and stored in the database, enabling the database to execute the queries much faster. Since many queries can be included in a stored procedure, round-trip time to execute multiple queries from source code to the database and back is avoided.

6. What is a trigger?

• A trigger is a set of commands that get executed when an event (Before Insert, After Insert, On Update, On delete of a row) occurs on a table or views.

7. Explain the difference between DELETE, TRUNCATE, and DROP commands.

• DELETE: Once a delete operation is performed, Commit and Rollback can be performed to retrieve data. The WHERE condition can be used along with the delete statement.
• TRUNCATE: Once a truncate statement is executed, Commit and Rollback cannot be performed. The WHERE condition cannot be used with the truncate statement.
• DROP: Drop command is used to drop the table or keys like primary, foreign from a table.

8. What is the difference between Clustered and Non-clustered Index?

• Clustered Index: Reorders the way records in the table are physically stored. There can be only one clustered index per table. It makes data retrieval faster.
• Non-clustered Index: Does not alter the way records are stored but creates a separate object within the table. As a result, insert and update commands will be faster.

9. What is Union, Minus, and Intersect commands?

• MINUS: Returns rows from the first query but not from the second query.
• INTERSECT: Returns rows returned by both queries.

10. What's the difference between a primary key and a unique key?

• Both primary key and unique keys enforce the uniqueness of the column on which they are defined. By default, a primary key creates a clustered index on the column, whereas a unique key creates a non-clustered index by default. Another major difference is that a primary key doesn't allow NULLs, but a unique key allows one NULL only.

11. What are user-defined data types and when should you use them?

• User-defined data types let you extend the base SQL Server data types by providing a descriptive name and format to the database. For example, if your database has a column called Flight_Num appearing in many tables, all of which should be varchar(8), you could create a user-defined datatype called Flight_num_type of varchar(8) and use it across all your tables.

12. How do you implement one-to-one, one-to-many, and many-to-many relationships while designing tables?

• One-to-One: Implemented as a single table and rarely as two tables with primary and foreign key relationships.
• One-to-Many: Implemented by splitting the data into two tables with primary key and foreign key relationships.
• Many-to-Many: Implemented using a junction table with the keys from both tables forming the composite primary key of the junction table.

13. What is bit datatype and what information can be stored inside a bit column?

• Bit datatype is used to store boolean information like 1 or 0 (true or false). Until SQL Server 6.5, bit datatype could hold either a 1 or 0, with no support for NULL. From SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL.

14. Define candidate key, alternate key, composite key.

• Candidate Key: A key that can uniquely identify each row of a table. Generally, a candidate key becomes the primary key of the table.
• Alternate Key: If the table has more than one candidate key, one will become the primary key, and the rest are called alternate keys.
• Composite Key: A key formed by combining at least two or more columns.