70+ Top SQL Interview Questions and Answers

SQL Interview Questions

Download all Questions as .PDF

What is a database?

A database is an organized collection of structured data stored electronically. It allows for efficient storage, retrieval, and management of data.

What is SQL?

SQL, or Structured Query Language, is a programming language used to manage and interact with databases. It allows you to:

1. Retrieve Data: Get information from a database.
2. Insert Data: Add new information to a database.
3. Update Data: Change existing information in a database.
4. Delete Data: Remove information from a database.

In simple terms, SQL helps you work with data stored in a database.

What is an SQL statement?

An SQL statement ,also known as an SQL command, is used to interact with a database. It can perform various operations such as retrieving , updating , deleting data, or creating database objects like tables and indexes. Some examples of SQL statements are SELECT, CREATE, DELETE, DROP, REVOKE, and so on.

What is a schema?

A schema is a structure that defines the organization of data in a database. It includes tables, views, indexes, procedures, and other database objects, and helps organize and manage the data logically.

-- To view all schemas
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;

--To view the schema of a specific table
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table_name'; 

What is a table and a field in SQL

Table: A table is a collection of related data organized in rows and columns within a database. Each row represents a record, and each column represents a field.

Field: A field is a single column in a table, representing a specific piece of data within a record. It holds individual data values for each row in the table.

How to create a Table?

CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    BirthDate DATE,
    HireDate DATE,
    Salary DECIMAL(10, 2)
); 

How to add a column to a table?

Use the ALTER TABLE statement with the ADD clause.

ALTER TABLE table_name
ADD new_column_name data_type; 

How to rename a column of a table?

ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name; 

How to delete a column from a table?

ALTER TABLE table_name
DROP COLUMN column_name; 

What is an Alias?

An alias is a temporary name given to a table or column for the duration of a query. It helps make the query results more readable and can simplify complex queries.

-- Column Alias
SELECT column_name  AS alias_name FROM table_name;

-- Table Alias
SELECT t.column1 FROM table_name AS  t ; 

What is a Clause?

A clause is a part of a SQL statement that specifies a condition or operation. Clauses define how to filter, sort, group, or join data within a query. Some examples are WHERE, LIMIT, HAVING, LIKE, AND, OR, ORDER BY, etc.

What is DDL?

Data Definition Language (DDL) is used to define and manage database structures. It includes commands like CREATE, ALTER, and DROP, which are used to create, modify, and delete database objects such as tables, indexes, and schemas.

What is DML?

Data Manipulation Language (DML) is used to manage and manipulate data within database tables. It includes commands like SELECT, INSERT, UPDATE, and DELETE, which are used to retrieve, add, modify, and remove data.

What are the different types of SQL commands?

1) Data Definition Language (DDL):

• CREATE: Creates new database objects like tables, indexes, and views.
• ALTER: Modifies existing database objects.
• DROP: Deletes database objects.
• TRUNCATE: Removes all rows from a table, but the table structure remains.

2) Data Manipulation Language (DML):

• SELECT: Retrieves data from the database.
• INSERT: Adds new rows of data to a table.
• UPDATE: Modifies existing data within a table.
• DELETE: Removes rows of data from a table.

3) Data Control Language (DCL):

• GRANT: Gives users access privileges to the database.
• REVOKE: Removes access privileges from users.

4) Transaction Control Language (TCL):

• COMMIT: Saves all changes made during the current transaction.
• ROLLBACK: Reverts changes made during the current transaction.
• SAVEPOINT: Sets a point within a transaction to which you can later roll back.

5) Data Query Language (DQL):

• SELECT: Used to query and retrieve data from the database.

What are the different types of SQL relationships? Can you describe and give examples of each type?

1) One-to-One Relationship: Each row in ‘Table A’ is linked to one and only one row in ‘Table B’, and vice versa.

example: A ‘Person’ table with a ‘Passport’ table where each person has one unique passport.

Person (PersonID, Name)
Passport (PassportID, PersonID, PassportNumber) 

2) One-to-Many Relationship: A row in ‘Table A’ can be associated with many rows in ‘Table B’, but a row in ‘Table B’ is associated with only one row in ‘Table A’.

example: A ‘Customer’ table with an ‘Orders’ table where each customer can place multiple orders.

Customer (CustomerID, Name)
Orders (OrderID, CustomerID, OrderDate) 

3) Many-to-One Relationship: (reverse of a one-to-many relationship) Many rows in ‘Table A’ can be associated with one row in ‘Table B’.

example: Many employees belonging to one department.

Department (DepartmentID, DepartmentName)
Employee (EmployeeID, DepartmentID, Name) 

4) Many-to-Many Relationship: Rows in ‘Table A’ can be associated with many rows in ‘Table B’ and vice versa.

example: A ‘Students’ table and a ‘Courses’ table where each student can enroll in multiple courses, and each course can have multiple students. Use a junction table to establish the relationship.

Students (StudentID, StudentName)
Courses (CourseID, CourseName)
StudentCourses (StudentID, CourseID) 

Basic SQL Commands and Queries

Basic SQL Commands and Queries.

-- Select all columns from a table
SELECT * FROM Employees

-- Insert
INSERT INTO Employees (EmployeeID, FirstName, LastName, BirthDate, HireDate, Salary)
VALUES (1, 'John', 'Doe', '1980-01-01', '2020-06-15', 50000.00);

-- Update
UPDATE Employees
SET Salary = 55000.00
WHERE EmployeeID = 1;

-- Delete
DELETE FROM Employees WHERE EmployeeID = 1; 

-- Get the Count of Rows in a Table
SELECT COUNT(*) FROM Employees;

--Sorting Records in a Table
/* ASC specifies ascending order (default).*/
/* DESC specifies descending order. */

SELECT * FROM Employees ORDER BY LastName ASC;
SELECT * FROM Employees ORDER BY HireDate DESC;

-- Selecting Distinct Records from a Single Table
SELECT DISTINCT LastName, FirstName FROM Employees;

-- Selecting Distinct Records from Two Tables
SELECT EmployeeID, FirstName, LastName FROM Employees
UNION
SELECT EmployeeID, FirstName, LastName FROM FormerEmployees;

-- Selecting Common Records from Two Tables
SELECT EmployeeID, FirstName, LastName FROM Employees
INTERSECT
SELECT EmployeeID, FirstName, LastName FROM FormerEmployees; 

Intermediate SQL Interview Questions and Answers

What is a Primary Key?

A primary key is a column or a set of columns that uniquely identifies each row in a table. It ensures that no duplicate values or NULLs are present in the primary key column(s).

What is Auto Increment?

Auto Increment is a feature that automatically generates a unique value for a specified column in a table for each new row inserted and is commonly used for primary key columns.

IDENTITY(1,1) is the keyword used to define an auto-increment column. (1,1) specifies the seed and increment values. The first 1 is the seed (starting value), and the second 1 is the increment (how much to increase for each new row).

CREATE TABLE TableName (
    ID INT IDENTITY(1,1) PRIMARY KEY,
); 

What is a foreign key?

A foreign key is a column or a set of columns in one table that links to the primary key in another table. It is used to maintain referential integrity by ensuring that the value in the foreign key column matches a value in the referenced primary key column.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    DepartmentID INT FOREIGN KEY REFERENCES Departments(DepartmentID)
); 

Define a Unique Key in SQL

A unique key is a column or a set of columns that ensures all values in that column or combination of columns are unique. It prevents duplicate values but allows one NULL value.

What is a constraint in SQL?

A constraint is a rule applied to a column or a set of columns in a table to enforce data integrity and ensure the accuracy and reliability of the data.

Common constraints:

1. Primary Key: Ensures each row in a table is unique and not NULL.
2. Foreign Key: Ensures a column’s values match values in another table’s primary key.
3. Unique: Ensures all values in a column are unique.
4. Not Null: Ensures a column cannot have NULL values.
5. Check: Ensures values in a column meet a specific condition.

What is Stored Procedure?

A stored procedure is a named set of pre-written SQL commands/statements that is saved in a database in a compiled ready-to-use form and can be executed as a single unit.

• allows modular programming.
• allows faster execution.
• reduce the network traffic.
• It can be used as a security mechanism.

1. User-Defined Stored Procedures: Created by users to perform specific tasks or operations in a database.
2. System Stored Procedures: Provided by the database system to perform administrative and system-level tasks.
3. Temporary Stored Procedures: Stored in the tempdb database and used for temporary tasks, disappearing after the session ends.
4. CLR Stored Procedures: Created using .NET languages and executed within the SQL Server Common Language Runtime (CLR).

What is a Subquery?

A subquery is a query nested inside another query. It is used to retrieve data that will be used in the main query as a condition to further restrict the data to be retrieved.

SELECT Name 
FROM Employees 
WHERE DeptID = (SELECT DeptID FROM Departments WHERE DeptName = 'Sales'); 

What is a View?

A view is a virtual table that shows data from base tables and other views (i.e., the outcome of a SELECT query), but not stored separately. It does not copy the memory space.

You should use a view in SQL when:

1. Simplifying Complex Queries: To encapsulate complex SQL queries into a single, reusable query.
2. Enhancing Security: To restrict user access to specific rows or columns of data.
3. Ensuring Data Consistency: To provide a consistent and unified presentation of data, even if the underlying table structure changes.
4. Providing Data Abstraction: To present data in a different format or structure without modifying the original tables.
5. Improving Readability and Maintenance: To make queries more readable and easier to maintain by breaking down complex logic into simpler, logical units.

Can we create a view based on another view?

Yes. This is also known as nested views.
note: 1) it can affect performance, 2) the code becomes difficult to read and debug

Can we still use a view if the original table is deleted?

No, you cannot use a view if the original table is deleted, because a view depends on the original table for its data. If the original table is deleted, the view loses its data source and becomes invalid.

What is Trigger?

Triggers are a type of stored procedure that automatically run when data in a table or view is updated, inserted, or deleted. They are powerful tools used to enforce business rules whenever data changes.

A trigger can be executed either ‘before or after’ an insert, update, or delete operation.

What are Cursors?

Cursors allow row-by-row processing of the result sets. (or) A cursor in SQL is a database object used to retrieve, manipulate, and navigate through a result set row by row. It acts as a pointer to the rows of a query result.

Advantages of Cursors

1. Row-by-Row Processing: Allows processing of individual rows, which is useful for complex row-by-row operations.
2. Simplifies Complex Operations: Makes it easier to handle complex logic that can’t be achieved with set-based operations alone.
3. Enhanced Control: Provides fine-grained control over query results and how they are processed.

Disadvantages of Cursors

1. Performance Overhead: Can be slower than set-based operations because they process each row individually.
2. Resource Intensive: Consume more memory and resources, leading to potential scalability issues.
3. Complexity: Can make code more complex and harder to maintain compared to set-based SQL operations.

What are indexes?

Indexes are special database objects that improve the speed of data retrieval. They act like pointers to quickly locate data within a table.

Types of Indexes:

1. Clustered Index: Organizes the actual data rows in the table based on the index key. Only one per table.
2. Nonclustered Index: Creates a separate structure pointing to the data rows. Multiple NonClustered indexes can exist per table.
3. Unique Index: Ensures that all the values in the index key column are unique. It can be Clustered or NonClustered.
4. Full-Text Index: Used for efficient searching of large text-based data. Supports full-text queries.
5. Spatial Index: Used for indexing spatial data types like geometry or geography. Optimizes spatial queries.
6. Filtered Index: NonClustered index with a WHERE clause. Indexes a subset of rows in a table.
7. XML Index: Used for indexing XML data type columns. Optimizes XML data queries.

Clustered and Non-clustered indexes are often used in real-time project.

What is GROUP BY in SQL?

GROUP BY is a clause used to group rows that have the same values in specified columns. It allows you to perform aggregate functions (like COUNT, SUM, AVG) on each group of rows.

What is the use of ORDER BY?

ORDER BY is a clause used to sort the result set of a query by one or more columns, either in ascending (default) or descending order.

SELECT * FROM products ORDER BY price DESC;

Difference between Primary key and Unique key?

1. Uniqueness: Both primary key and unique key ensure all values in the column are unique.
2. Null Values: Primary key does not allow NULL values, while unique key allows one NULL value.
3. Number of Keys Allowed: A table can have only one primary key but can have multiple unique keys.
4. Index Creation: Primary key creates a clustered index by default (if supported), whereas unique key creates a non-clustered index by default.
5. Purpose: Primary key uniquely identifies each record in a table, while unique key ensures the uniqueness of values in specific columns.
6. Identification: Primary key is used to uniquely identify a row, while unique key is used to enforce uniqueness in columns.

Define Candidate key, Alternate key and Composite key.

Candidate Key: A candidate key is a column, or a set of columns, that can uniquely identify any record in a table. A table can have multiple candidate keys, but only one is chosen as the primary key.

Alternate Key: An alternate key is any candidate key that is not chosen as the primary key. It is still capable of uniquely identifying records but serves as an alternative to the primary key.

Composite Key: A composite key is a key that consists of two or more columns used together to uniquely identify a record. This is used when a single column is not sufficient to ensure uniqueness.

Different between Delete and Truncate?

1. Operation Type: DELETE is a DML (Data Manipulation Language) command, while TRUNCATE is a DDL (Data Definition Language) command.
2. Row-by-Row Deletion: DELETE can remove specific rows based on conditions, but TRUNCATE removes all rows from a table at once.
3. WHERE Clause: DELETE allows the use of a WHERE clause (criteria) to filter rows, but TRUNCATE does not.
4. Transaction Logging: DELETE logs each row deletion, making it slower, whereas TRUNCATE logs less (deallocation of data page), making it faster.
5. Space Reclaiming: DELETE does not immediately free up space, but TRUNCATE does.
6. Triggers Activation: DELETE activates triggers, but TRUNCATE does not.
7. Foreign Key Constraints: DELETE can be used with foreign key constraints, but TRUNCATE cannot if foreign keys are present.
8. Identity Reset: DELETE does not reset the identity column, while TRUNCATE does.

What is the On Delete cascade constraint?

The ON DELETE CASCADE constraint in SQL automatically deletes rows in a child table when the corresponding rows in the parent table are deleted.

/* -- Child Table -- */
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE CASCADE
); 

What is join in SQL? What are the different types of joins? What is the difference between them?

Joins are used to combine rows from two or more tables based on a related column between them. There are different types of joins – Inner Join, Left Join, Right Join, Full Join, Cross Join and Sefl Join.

Types of Joins and differences:

1. Inner Join – Returns rows with matching values in both tables.
   SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id
2. Left Join (or Left Outer Join) – Returns all rows from the left table and matching rows from the right table. If no match, returns NULLs for the right table.
   SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
3. Right Join (or Right Outer Join) – Returns all rows from the right table and matching rows from the left table. If no match, returns NULLs for the left table.
   SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id
4. Full Join (or Full Outer Join) – Returns rows when there is a match in one of the tables. If no match, returns NULLs for non-matching rows from both tables.
   SELECT * FROM table1 FULL JOIN table2 ON table1.id = table2.id
5. Cross Join – Returns the Cartesian product of both tables, meaning all possible combinations of rows
   SELECT * FROM table1 CROSS JOIN table2
6. Self Join – Joins a table to itself
   SELECT a., b. FROM table a, table b WHERE a.id = b.ref_id

What are Clustered and Non-clustered Indexes?

Clustered Index: A clustered index organizes the actual data rows in a table based on the index key. It’s like sorting a book by its chapters; there’s only one way to arrange the pages. A table can have only one clustered index.

Nonclustered Index: A nonclustered index creates a separate structure that points to the data rows, like an index at the back of a book pointing to pages. It does not change the order of the actual data. A table can have multiple nonclustered indexes.

What is the difference between Clustered and Non-clustered indexes?

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

You can use Having Clause with the GROUP BY function in a query and WHERE Clause is applied to each row before, they are part of the GROUP BY function in a query.

What is the difference between BETWEEN and IN operators?

1) BETWEEN operator: To filter the results within a specified range.

SELECT * FROM Employees
WHERE Salary BETWEEN 30000 AND 50000; 

2) IN operator: To filter the results based on a specified set of values.

SELECT * FROM Employees
WHERE DepartmentID IN (1, 4, 8); 

What is the difference between UNION and UNION ALL SQL syntax?

UNION syntax is used to select information from two or more tables. But it selects only distinct records from tables, while UNION ALL selects all records from tables.
Note: Selected records should have the same datatype else the syntax will not work.

Different between UNION and Joins

What do you mean by a NULL value in SQL?

A NULL value represents missing or unknown data. It is different from zero, an empty string, or any other value. NULL indicates the absence of data in a column that the value for a particular column is not known or does not exist.

What is the difference between COALESCE() & ISNULL()?

SELECT COALESCE(FirstName, LastName, 'NoName') AS Name

SELECT ISNULL(FirstName, 'NoName') AS Name

What are the different types of SQL operators?

• Arithmetic (+, –, *, /, etc.)
• Comparison (>, <, =, >=, etc.)
• Compound (+=, -=, *=, /=, etc.)
• Logical (AND, OR, NOT, BETWEEN, etc.)
• String (%, _, +, ^, etc.)
• Set (UNION, UNION ALL, INTERSECT, and MINUS (or EXCEPT))

What operator is used in the query for pattern (word/text) matching?

The LIKE operator in combination with the % and _ wildcards. The % wildcard represents any number of characters including zero, while _ – strictly one character.

What is the correct order of statements in a SELECT query?

SELECT –> FROM –> JOIN –> ON –> WHERE –> GROUP BY –> HAVING –> ORDER BY –> LIMIT

In what order does the interpreter execute the statements in a SELECT query?

FROM –> JOIN –> ON –> WHERE –> GROUP BY –> HAVING –> SELECT –> ORDER BY –> LIMIT

What is a Function in SQL.

A function is a reusable set of SQL statements that perform a specific task and return a single value. Functions can take input parameters and are often used to encapsulate logic that can be applied to data within queries.

1) Built-in Functions: SUM(), AVG(), COUNT(), MAX(), MIN()

SELECT SUM(Amount) AS TotalSales FROM Sales; 

2) User-defined Functions: Functions created by users to perform custom operations.

Syntax:

CREATE FUNCTION dbo.Square(@Number INT)
RETURNS INT
AS
BEGIN
    RETURN @Number * @Number;
END; 

Usage:

SELECT dbo.Square(4) AS SquareOfFour;  -- Returns 16 

What is Pre-defined function in SQL?

A pre-defined function in SQL is a built-in function provided by the database system that performs a specific operation. These functions are ready to use and do not require user-defined code.

• Aggregate Functions: SUM(), AVG(), COUNT()
• String Functions: CONCAT(), UPPER(), LOWER()
• Date Functions: NOW(), DATEADD(), DATEDIFF()
• Numeric Functions: ROUND(), ABS(), SQRT()

What are Aggregate functions? name a few.

Aggregate functions in SQL perform calculations on multiple rows of a table and return a single value.

1. COUNT: Returns the number of rows.
2. SUM: Returns the total sum of a numeric column.
3. AVG: Returns the average value of a numeric column.
4. MAX: Returns the highest value in a column.
5. MIN: Returns the lowest value in a column.

What is Window Function in SQL?

A window function performs calculations across a set of table rows related to the current row. Unlike aggregate functions, it doesn’t group rows into a single output but provides a result for each row, maintaining the individual row identities.

• ROW_NUMBER(): Assigns a unique number to each row within a partition.
• RANK(): Assigns a rank to each row within a partition, allowing for ties.
• SUM(): Calculates a running total for a column within a partition.

What Scalar Functions? Can you name a few examples?

Scalar function operate on a single value and return a single value. It can be used in the SELECT statement, WHERE clause, or anywhere an expression is allowed.

Some example are – LENGTH(), UPPER(), LOWER(), INITCAP(), SUBSTR(), ROUND(), NOW(), CAST(), CONVERT().

What is the CASE() function?

The CASE function allows you to perform conditional logic in your queries. It returns different values based on specified conditions.

SELECT 
    CASE
        WHEN Salary > 50000 THEN 'High'
        WHEN Salary > 30000 THEN 'Medium'
        ELSE 'Low'
    END AS SalaryLevel
FROM Employees; 

What is a temporary table in SQL, and what are the different types of temporary tables?

A temporary table is a short-lived table used to store data temporarily during a session or for intermediate results in a query.

Two types of Temporary Tables:

• Local Temporary Table: Prefixed with #, limited to the current session.
• Global Temporary Table: Prefixed with ##, available to all sessions until the last session ends.

Usage:

Method 1: To insert data into a temporary table without explicitly creating the table structure, you can use the SELECT INTO statement. This statement creates the temporary table and inserts the data in a single step.

SELECT EmployeeID, FirstName, LastName
INTO #TempTable
FROM Employees
WHERE DepartmentID = 1; 

Method 2: Create a temporary table and then insert data implicitly.

CREATE TABLE #TempTable (
    Column1 INT,
    Column2 NVARCHAR(50)
); 

-- Verify the data in the temporary table
SELECT * FROM #TempEmployees;

What is a Table Variable?

A table variable is a type of variable used to store temporary data in a structured format, similar to a table, within the scope of a batch, stored procedure, or function. @ is used for declaring Table variables.

/* Creating a Table Variable */
DECLARE @TempTable TABLE (
    Column1 INT,
    Column2 NVARCHAR(50)
);

/* Insert data into the table variable */
-- Method 1: Implicit Insertion
INSERT INTO @TempTable (Column1, Column2)
VALUES (1, 'Sample'), (2, 'Example');

-- Method 2: Explicit Insertion 
INSERT INTO @TempTable(Column1, Column2)
SELECT Column1, Column2 FROM ExistingTable WHERE SomeCondition;


/* Select data from the table variable */
SELECT * FROM @TempTable; 

Difference Between Temporary Table and Table Variable.

What is CTE (Common Table Expression)?

A CTE (Common Table Expression) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It makes complex queries easier to read and manage by breaking them into simpler parts. CTEs are defined using the WITH keyword.

WITH SalesCTE AS (
    -- CTE query
    SELECT SalesPerson, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY SalesPerson
)
-- Main query using the CTE
SELECT SalesPerson, TotalSales
FROM SalesCTE
WHERE TotalSales > 10000; 

What is MERGE in SQL? Give a basic example.

MERGE is a statement that allows you to perform INSERT, UPDATE, and DELETE operations in a single statement. It is used to synchronize two tables by merging them based on a specified condition.

MERGE INTO Employees AS target
USING NewEmployees AS source
ON target.EmployeeID = source.EmployeeID
WHEN MATCHED THEN
    UPDATE SET target.Name = source.Name, target.Salary = source.Salary
WHEN NOT MATCHED BY TARGET THEN
    INSERT (EmployeeID, Name, Salary) VALUES (source.EmployeeID, source.Name, source.Salary)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE; 

What is normalization? What are the different types of normalization?

Normalization is the process of organizing a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller, related tables and defining relationships between them. Normalization helps maintain data accuracy and efficiency in a database.

Types of Normalization:

1. First Normal Form (1NF): Ensures each column contains only atomic (indivisible) values and each entry in a column is of the same type.
2. Second Normal Form (2NF): Meets all the requirements of 1NF and ensures that all non-key columns are fully dependent on the primary key.
3. Third Normal Form (3NF): Meets all the requirements of 2NF and ensures that all columns are directly dependent on the primary key, eliminating transitive dependencies.
4. Boyce-Codd Normal Form (BCNF): A stricter version of 3NF where every determinant is a candidate key.
5. Fourth Normal Form (4NF): Meets all the requirements of BCNF and ensures that multi-valued dependencies are eliminated.
6. Fifth Normal Form (5NF): Ensures that any remaining anomalies are eliminated, usually dealing with join dependencies.

What is denormalization?

Denormalization is the process of combining tables to reduce the number of joins needed for queries. This can improve read performance by storing redundant data, making data retrieval faster and simpler. However, it may lead to increased storage requirements and potential data inconsistencies.

How can you raise custom errors from stored procedure?

The RAISERROR statement is used to raise custom errors from a stored procedure.

RAISERROR ('error message', 10, 1);

It accepts three parameters:

1. Error Message: Custom text message
2. Error Severity: Severity levels from 11 to 19 can be used for user-defined errors
3. Error State: State is an integer from 0 to 255

ALTER PROCEDURE AddCustomer
	@Column2 int =NULL
AS

DECLARE @ErrorMsgID int

INSERT Customer VALUES (@Column2)
	
SET @ErrorMsgID =  @@ERROR 
	
IF @ErrorMsgID <> 0
BEGIN
	 RAISERROR ('An error occured updating the Customer table', 10, 1) 
END 

What is a Transaction in SQL?

A transaction is a sequence of one or more SQL operations treated as a single unit of work. All operations within a transaction must be completed successfully; otherwise, none of them are applied to the database.

Purpose – ensure data integrity and consistency. It follows the ACID properties (Atomicity, Consistency, Isolation, Durability)

When to Use – Use transactions when you need to ensure that a series of operations are completed successfully together. Common scenarios include:

• Transferring funds between accounts.
• Inserting related data into multiple tables.
• Updating multiple records in a dependent manner.

BEGIN TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;

UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 2;

IF @@ERROR = 0
    COMMIT;
ELSE
    ROLLBACK; 

What is a Pivot Table? Give basic example

A pivot table in SQL is a data summarization tool that transforms rows into columns, allowing you to reorganize and aggregate data for easier analysis. It helps to summarize and analyze large datasets by grouping and displaying data in a cross-tabular format.

SELECT *
FROM (
    SELECT Month, Product, Sales
    FROM SalesData
) AS SourceTable
PIVOT (
    SUM(Sales)
    FOR Month IN ([Jan], [Feb], [Mar], [Apr])
) AS PivotTable; 

What is SQL injection?

SQL injection is a type of cyber attack where an attacker inserts malicious SQL code into an input field, potentially allowing them to manipulate the database and access unauthorized data.

How to Prevent SQL Injection

1. Use Prepared Statements: Parameterize queries to separate SQL code from data.
2. Use Stored Procedures: Encapsulate SQL queries within stored procedures.
3. Validate Input: Ensure that input data is properly validated and sanitized.
4. Limit Database Permissions: Restrict user permissions to only what is necessary.
5. Use ORM Libraries: Object-Relational Mapping libraries often handle parameterization automatically.

Write syntax to create a stored procedure.

CREATE PROCEDURE GetEmployeesByDepartment
    @DeptID INT
AS
BEGIN
    SELECT * 
    FROM Employees 
    WHERE DepartmentID = @DeptID;
END

How to prevent duplicate records when making a query?

1) Using the DISTINCT statement with SELECT

SELECT DISTINCT column1, ... FROM table_name 

2) Using GROUP BY

SELECT FirstName, LastName
FROM Employees
GROUP BY FirstName, LastName; 

How to find the last ID (number) in a table?

1) Using the MAX() function.

SELECT MAX(ID) AS LastID FROM TableName; 

2) Using TOP and ORDER BY

SELECT TOP 1 ID FROM TableName ORDER BY ID DESC; 

How to find 6th highest salary

You can use the ROW_NUMBER() function

SELECT Salary
FROM (
    SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
    FROM Employees
) AS RankedSalaries
WHERE RowNum = 6; 

How to find the values in a text column of a table that start with a certain letter?

Use LIKE operator with %

SELECT * FROM Employees WHERE Name LIKE 'G%' 

How to select random rows from a table?

Using the NEWID() function

SELECT * FROM TableName ORDER BY NEWID(); 

How can you create a new table with the same structure as an existing table?

Using the INTO operator with a WHERE clause that is always false for all records.

SELECT * INTO Employee_copy
FROM Employee WHERE 1 = 2; 

How can database performance be improved?

Measures to Increase Database Performance:

• Indexing: Create indexes on frequently queried columns to speed up searches.
• Query Optimization: Write efficient SQL queries, avoid unnecessary columns in SELECT, and use proper JOINs.
• Normalization: Normalize data to reduce redundancy and improve data integrity.
• Denormalization: Use denormalization where necessary to reduce JOIN operations and improve read performance.
• Partitioning: Split large tables into smaller, manageable pieces (partitions) for faster access.
• Caching: Use caching mechanisms to store frequently accessed data in memory.
• Load Balancing: Distribute database load across multiple servers.
• Hardware Upgrades: Upgrade to faster CPUs, add more RAM, and use SSDs for better performance.
• Regular Maintenance: Perform routine maintenance like updating statistics, rebuilding indexes, and cleaning up logs.
• Connection Pooling: Use connection pooling to reuse database connections efficiently.
• Database Configuration: Optimize database configuration settings for performance.
• Monitoring and Profiling: Monitor database performance and profile queries to identify and address bottlenecks.