Structured Query Language

1. What is SQL?
SQL means Structured Query Language and is used to communicate with relational
databases. It proposes a standardized way to interact with databases, allowing users to
perform various operations on the data, including retrieval, insertion, updating, and deletion.

2. What are the different types of SQL commands?

 SELECT: Retrieves data from a database.

 INSERT: Adds new records to a table.
 UPDATE: Modifies existing records in a table.
 DELETE: Removes records from a table.
 CREATE: Creates a new database, table, or view.
 ALTER: Modifies the existing database object structure.
 DROP: Deletes an existing database object.
📚 Different Types of SQL Commands
SQL (Structured Query Language) commands are categorized based on their purpose in
managing and manipulating databases.

✅ 1. DDL – Data Definition Language

Purpose: Define and manage database structure (schemas, tables, etc.)
Command Description Example
Creates new tables, CREATE TABLE users (id INT, name
CREATE
databases, views VARCHAR(50));
Modifies existing database ALTER TABLE users ADD email
ALTER
objects VARCHAR(100);
Deletes tables, views, or
DROP DROP TABLE users;
databases
Removes all records but keeps
TRUNCATE TRUNCATE TABLE orders;
table
RENAME TABLE orders TO
RENAME Renames database objects
customer_orders;

✅ 2. DML – Data Manipulation Language

Purpose: Manipulate data inside database tables
Command Description Example
Retrieves data from one or more
SELECT SELECT * FROM users;
tables
INSERT INTO users (id, name) VALUES
INSERT Adds new records to a table
(1, 'John');
UPDATE users SET name = 'Jane'
UPDATE Modifies existing records
WHERE id = 1;
DELETE Removes records from a table DELETE FROM users WHERE id = 1;

✅ 3. DCL – Data Control Language

Purpose: Manage permissions and access control
Command Description Example
GRANT Gives privileges to users/roles GRANT SELECT ON users TO 'john';
Removes privileges from REVOKE SELECT ON users FROM
REVOKE
users/roles 'john';

✅ 4. TCL – Transaction Control Language

Purpose: Control transactions and ensure data integrity
Command Description Example
Saves all changes in a
COMMIT COMMIT;
transaction
ROLLBACK Reverts changes in a transaction ROLLBACK;
SAVEPOINT Sets a point to rollback to SAVEPOINT sp1;
Sets properties for the SET TRANSACTION READ
SET TRANSACTION
transaction ONLY;

✅ 5. DQL – Data Query Language

Purpose: Fetch data from the database
Command Description Example
SELECT Retrieves data SELECT name FROM employees WHERE id = 2;
Note: SELECT is often placed under DML but is technically a DQL operation focused on
reading data.
 🧠 Summary Table
Type Full Form Key Commands
DDL Data Definition Language CREATE, ALTER, DROP, TRUNCATE
DML Data Manipulation Language SELECT, INSERT, UPDATE, DELETE
DCL Data Control Language GRANT, REVOKE
TCL Transaction Control Language COMMIT, ROLLBACK, SAVEPOINT
DQL Data Query Language SELECT

3. What is a primary key in SQL?

It is a unique identifier for each record in a table. It ensures that each row in the table has a
distinct and non-null value in the primary key column. Primary keys enforce data integrity
and create relationships between tables.
🔑 Primary Key in SQL – Explained
A Primary Key is a column (or a set of columns) in a table that uniquely identifies each
row in that table.
✅ Key Characteristics of a Primary Key
Property Description
Uniqueness No two rows can have the same primary key value
Not Null Primary key columns cannot be null
One per Table A table can have only one primary key
Implied Index SQL automatically creates an index on the primary key
Can Be Composite A primary key can consist of multiple columns

📌 Example – Single Column Primary Key

CREATE TABLE users (
user_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);

 user_id is the primary key — unique and non-null for each user.

📌 Example – Composite Primary Key (Multi-column)

CREATE TABLE orders (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id)
);

 The combination of order_id and product_id must be unique.

 Useful in many-to-many relationships or when a single field isn't unique.

❌ What Happens If You Violate Primary Key Rules

Operation Result
Insert duplicate key ❌ Error: Duplicate entry for primary key
Insert NULL key ❌ Error: Primary key cannot be null

🔄 Alter Table to Add Primary Key

ALTER TABLE employees
ADD CONSTRAINT pk_emp_id PRIMARY KEY (emp_id);

🔓 To Drop a Primary Key

ALTER TABLE employees
DROP PRIMARY KEY;
 Note: If the primary key is a composite, dropping it will remove the entire constraint.

🧠 Primary Key vs Unique Key

Feature Primary Key Unique Key
Uniqueness ✅ Yes ✅ Yes
Allows NULLs ❌ No ✅ Yes (1 or more NULLs allowed)
Count per table Only 1 Can have multiple

4. What is a foreign key?

Foreign key is a field in one table referencing the primary key in another. It establishes a
relationship between the two tables, ensuring data consistency and enabling data retrieval
across tables.

A Foreign key is a field (or collection of fields) in one table that uniquely identifies a row of
another table. It’s a key used to link two tables together in a relational database.
Key Concepts:
 A foreign key in one table points to a primary key in another table.
 It ensures referential integrity: values in the foreign key column must exist in the
referenced column.
 It can prevent actions that would leave orphan records in the database.

Example:
Let’s say you have two tables: Customers and Orders.
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name VARCHAR(100)
);

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,
OrderDate DATE,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
In this example:
 CustomerID is the primary key in the Customers table.
 CustomerID in the Orders table is a foreign key pointing to
Customers(CustomerID).

5. Explain the difference between the DELETE and TRUNCATE commands

Professionals use the DELETE command to remove particular rows from a table based on a
condition, allowing you to selectively delete records. TRUNCATE, on the other hand,
removes all rows from a table without specifying conditions. TRUNCATE is faster and uses
fewer system resources than DELETE, but does not log individual row deletions.

Feature DELETE TRUNCATE

Deletes specific rows ✅ Yes (WHERE clause) ❌ No (removes all rows)
Transaction-safe ✅ Yes ⚠️Depends on DBMS
Fires triggers ✅ Yes ❌ Usually not
Faster performance ❌ Slower ✅ Faster
Resets identity column ❌ No ✅ Yes (in some DBMS)
Logged operation ✅ Fully ⚠️Minimal/logged differently

6. What is a JOIN in SQL, and what are its types?

A JOIN operation merges information from two or more tables by utilizing a common column
that links them together. Various types of JOINs exist, like INNER JOIN, LEFT JOIN, RIGHT
JOIN, and FULL JOIN. These JOIN variations dictate the manner in which data from the
involved tables is paired and retrieved.

JOIN Type Description

INNER
Returns only rows with matching values in both tables.
JOIN
Returns all rows from the left table and matched rows from the right table.
LEFT JOIN
Unmatched rows get NULL.
RIGHT
Returns all rows from the right table and matched rows from the left table.
JOIN
FULL JOIN Returns all rows when there's a match in either table (LEFT or RIGHT).
CROSS Returns the Cartesian product of both tables (every row of A with every row
JOIN of B).

7. What do you mean by a NULL value in SQL?

A NULL value in SQL represents the absence of data in a column. It is not the same as an
empty string or zero; it signifies that the data is missing or unknown. NULL values can be
used in columns with optional data or when the data is unavailable.

8. Define a Unique Key in SQL

Often referred to as a unique constraint, a unique key guarantees that every value in a
column (or a combination of columns) remains distinct and cannot be replicated within a
table. In contrast to a primary key, a table has the flexibility to incorporate multiple unique
keys.

9. What is a database?
A database is a systematically organized collection of data arranged into tables composed of
rows and columns. Its primary purpose is to efficiently store, manage, and retrieve data.

10. Explain the differences between SQL and NoSQL databases

SQL databases are characterized by their use of structured tables and strict adherence to a
predefined schema, making them ideal for managing structured data with a strong focus on
data consistency and transaction support.

In contrast, NoSQL databases are non-relational and excel in handling unstructured or semi-
structured data, frequently employed for scalable, distributed, and adaptable data storage
solutions.

B. SQL Interview Questions for Intermediate

11. What is a table and a field in SQL?

In SQL (Structured Query Language), a table is a structured data collection organized into
rows and columns. Each column in a table is called a field, representing a specific attribute
or property of the data.

12. Describe the SELECT statement

The SELECT statement fetches data from one or multiple tables. It enables you to specify
the desired columns to retrieve, apply filters through the WHERE clause, and manage the
result's sorting using the ORDER BY clause.

13. What is a constraint in SQL?

A constraint in SQL defines rules or restrictions that apply to data in a table, ensuring data
integrity. Common constraints include:

 PRIMARY KEY: Ensures the values' uniqueness in a column.

 FOREIGN KEY: Enforces referential integrity between tables.
 UNIQUE: Ensures the uniqueness of values in a column.
 CHECK: Defines a condition that data must meet to be inserted or updated.
 NOT NULL: Ensures that there are no NULL values in a column.
Constraint Description Example Usage
PRIMARY Uniquely identifies each row in a table.
PRIMARY KEY (id)
KEY Cannot be NULL.
FOREIGN Ensures values in one table match values FOREIGN KEY (user_id)
KEY in another (referential integrity). REFERENCES Users(id)
Ensures all values in a column (or group of
UNIQUE UNIQUE (email)
columns) are unique.
Ensures a column cannot have NULL
NOT NULL name VARCHAR(100) NOT NULL
values.
Ensures all values in a column meet a
CHECK CHECK (age >= 18)
condition.
Sets a default value for a column if none is created_at DATE DEFAULT
DEFAULT
provided. CURRENT_DATE

14. What is normalization in SQL?

Normalization is a method for streamlining data storage within a database, reducing
redundancy, and enhancing data integrity. This approach entails dividing tables into more
manageable, interrelated tables and establishing connections between them.

Normalization in SQL is the process of organizing data in a database to reduce redundancy

and improve data integrity. It involves dividing large tables into smaller, related tables and
defining relationships between them.

🔹 Why Normalize?
 Eliminate redundant (duplicate) data
 Ensure data consistency
 Simplify data maintenance
 Improve query performance (in many cases)

Normal
Rules Purpose
Form
- All columns contain atomic (indivisible)
1NF Removes repeating groups or arrays.
values. - Each row is unique.
- Must be in 1NF - All non-key columns Eliminates partial dependencies (for
2NF
depend on the whole primary key. composite keys).
- Must be in 2NF - No transitive Ensures that non-key columns
3NF
dependencies (non-key → non-key). depend only on the primary key.
- Stronger version of 3NF - Every
BCNF Fixes anomalies not handled by 3NF.
determinant must be a candidate key.

15. How do you use the WHERE clause?

The WHERE clause within SQL queries serves the purpose of selectively filtering rows
according to specified conditions, thereby enabling you to fetch exclusively those rows that
align with the criteria you define. For example:
SELECT * FROM employees WHERE department = 'HR';

16. What are indexes in SQL?

Indexes improve the speed of data retrieval operations. They provide a quick way to locate
specific rows in a table by creating a sorted data structure based on one or more columns.
Indexes are essential for optimizing SQL query performance.

17. Explain GROUP BY in SQL

The GROUP BY clause organizes rows from a table into groups based on the values in one
or more columns. It is commonly employed alongside aggregate functions like SUM,
COUNT, AVG, MIN, and MAX to perform computations on grouped data.

18. What is an SQL alias?

An SQL alias serves as a transitory label bestowed upon a table or a column within a query,
primarily enhancing the clarity of query outcomes or simplifying the process of renaming
columns for improved referencing. For example:
SELECT first_name AS "First Name", last_name AS "Last Name" FROM employees;
19. Explain ORDER BY in SQL
The ORDER BY clause is used to sort the result set of a SQL query based on one or more
columns. You can specify each column's sorting order (ascending or descending). For
example:
SELECT * FROM products ORDER BY price DESC;

20. Describe the difference between WHERE and HAVING in SQL

The WHERE clause restricts individual rows before they are grouped, such as when filtering
rows prior to a GROUP BY operation.
Conversely, the HAVING clause is utilized to filter groups of rows after they have been
grouped, like filtering groups based on aggregate values.

Feature WHERE HAVING

Filters rows before grouping ✅ Yes ❌ No
Filters rows after grouping ❌ No ✅ Yes
Can use aggregate functions (e.g. SUM,
❌ No ✅ Yes
COUNT)
Used with GROUP BY Optional Usually used with GROUP BY
Works on individual rows ✅ Yes ❌ No (works on groups of rows)

21. What is a view in SQL?

An SQL view is essentially a virtual table that derives its data from the outcome of a
SELECT query. Views serve multiple purposes, including simplifying intricate queries,
enhancing data security through an added layer, and enabling the presentation of targeted
data subsets to users, all while keeping the underlying table structure hidden.

22. What is a stored procedure?

A SQL stored procedure comprises precompiled SQL statements that can be executed
together as a unified entity. These procedures are commonly used to encapsulate business
logic, improve performance, and ensure consistent data manipulation practices.

A stored procedure is a precompiled collection of one or more SQL statements that are
stored and executed on the database server. It’s like a function in programming — you can
reuse, parameterize, and control logic more efficiently.

🔹 Key Features of Stored Procedures

Feature Description
Reusability Write once, use many times
Performance Compiled once and executed faster than individual queries
Security Users can execute without seeing the underlying code
Parameterization Accepts input (and sometimes output) parameters
Logic & Control Flow Supports variables, IF statements, loops, etc.

23. What is a trigger in SQL?

An SQL trigger consists of a predefined sequence of actions that are executed automatically
when a particular event occurs, such as when an INSERT or DELETE operation is
performed on a table. Triggers are employed to ensure data consistency, conduct auditing,
and streamline various tasks.

A trigger in SQL is a special type of stored procedure that automatically executes (or
"fires") in response to certain events on a table or view — such as INSERT, UPDATE, or
DELETE.

🔹 Why Use Triggers?

Use Case Example

Audit Trail Automatically log changes to a table
Enforce Business Rules Prevent actions like deleting a VIP customer
Auto-Update Values Set timestamps or calculate fields automatically
Validate Data Check constraints more complex than CHECK allows

24. What are aggregate functions? Can you name a few?

Aggregate functions in SQL perform calculations on a set of values and return a single
result.

 SUM: To calculate the sum of values in a column.

 COUNT: To count a column's number of rows or non-null values.
 AVG: To calculate the average of values in a column.
 MIN: To retrieve the minimum value in a column.
 MAX: To retrieve the maximum value in a column.

25. How do you update a value in SQL?

The UPDATE statement serves the purpose of altering pre-existing records within a table. It
involves specifying the target table for the update, the specific columns to be modified, and
the desired new values to be applied. For example:

UPDATE employees SET salary = 60000 WHERE department = 'IT';

26. What is a self-join, and how would you use it?

A self-join is a type of join where a table is joined with itself. It is useful when creating
relationships within the same table, such as finding hierarchical relationships or comparing
rows with related data.

27. Explain different types of joins with examples

 INNER JOIN: Gathers rows that have matching values in both tables.
 RIGHT JOIN: Gathers all rows from the right table and any matching rows from
the left table.
 LEFT JOIN: Gathers all rows from the left table and any matching rows from the
right table.
 FULL JOIN: Gathers all rows when there's a match in either table, including
unmatched rows from both tables.
Example:
SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

28. What is a SQL subquery?

A subquery refers to a query that is embedded within another query. It is often used in
SELECT, INSERT, UPDATE, or DELETE statements to retrieve data that is then used in the
main query to perform operations like filtering or comparison.
For example, to find employees with salaries greater than the average salary:
SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

29. How do you optimize SQL queries?

SQL query optimization involves improving the performance of SQL queries by reducing
resource usage and execution time. Strategies include using appropriate indexes, optimizing
query structure, and avoiding costly operations like full table scans.

SQL Query Optimization – Key Points

1. Use Proper Indexing

o Index columns in WHERE, JOIN, ORDER BY, GROUP BY.
o Don’t over-index.
2. Avoid SELECT *
o Fetch only required columns.
3. Use Efficient WHERE Clauses
o Filter data early and precisely.
4. Optimize Joins
o Use correct join types.
o Ensure join columns are indexed.
5. Limit the Result Set
o Use LIMIT to avoid unnecessary rows.
6. Avoid Functions on Indexed Columns
o Functions on columns disable index usage.
7. Use EXPLAIN or EXPLAIN PLAN
o Analyze execution plan to detect slow operations.
8. Use Denormalization or Caching (for read-heavy systems)
o Consider materialized views or Redis for performance.
9. Prefer Joins or CTEs over Subqueries
o Replace correlated subqueries with JOIN or WITH.
10. Batch Operations
o Use bulk INSERT, UPDATE, DELETE.
11. Partition Large Tables
o Improves query performance for huge datasets.
12. Keep Statistics Updated
o Run ANALYZE to help the optimizer.
13. Use Connection Pooling
o Improves performance in app-layer (e.g., HikariCP in Spring Boot).

30. What is the difference between UNION and UNION ALL?

UNION merges the outcomes of two or more SELECT statements, removing duplicate rows,
whereas UNION ALL merges the results without removing duplicates. While UNION ALL is
faster, it may include duplicate rows.

31. What are correlated subqueries?

It is a type of subquery that references columns from the surrounding outer query. This
subquery is executed repeatedly, once for each row being processed by the outer query,
and its execution depends on the outcomes of the outer query.

32. Explain ACID properties in SQL

ACID - Atomicity, Consistency, Isolation, and Durability. They are essential properties that
ensure the reliability and integrity of database transactions:

 Atomicity (single, indivisible unit of transactions)

 Consistency (transactions bring the DB from one consistent state to another)
 Isolation (transactions are isolated from each other)
 Durability (committed transactions are permanent and survive system failures.)

33. What is a transaction in SQL?

A transaction in SQL is a sequence of one or more SQL operations treated as a single unit
of work. Transactions ensure that database operations are either completed successfully or
rolled back entirely in case of failure.

34. How do you implement error handling in SQL?

Error handling in SQL is typically achieved using try-catch blocks (in SQL Server) or
EXCEPTION blocks (in Oracle). These blocks allow you to handle and log errors gracefully
to prevent application crashes.

35. What is a cursor, and how is it used?

In SQL, a cursor is a database element employed to fetch and control data one row at a time
from a result set. Cursors are frequently used within stored procedures or triggers when it
becomes necessary to process data sequentially.

36. Describe the data types in SQL

SQL supports various data types, including numeric, character, date/time, and binary types.
Common data types include INT, VARCHAR, DATE, and BLOB, among others. Data types
define the kind of values a column can hold.

SQL Data Types (Short Summary)

🔢 Numeric Types
 INT / INTEGER: Whole numbers
 SMALLINT: Smaller integers
 BIGINT: Large integers
 DECIMAL(p, s) / NUMERIC: Exact fixed-point
 FLOAT, REAL, DOUBLE: Approximate floating-point

🔤 String/Text Types
 CHAR(n): Fixed-length text
 VARCHAR(n): Variable-length text
  TEXT: Long text (DB-specific limits)

📅 Date & Time Types

 DATE: Only date (YYYY-MM-DD)
 TIME: Only time (HH:MM:SS)
 DATETIME / TIMESTAMP: Date and time

🟢 Boolean
 BOOLEAN: True/False (often stored as 1/0)

🧱 Binary Types
 BINARY(n): Fixed-length binary
 VARBINARY(n): Variable-length binary
 BLOB: Binary Large Object (e.g., images)

📌 Others
 ENUM: Predefined string values (MySQL-specific)
 UUID: Universally unique identifier (PostgreSQL)

37. Explain normalization and denormalization

Normalization is the method used to streamline data in a database, decreasing redundancy
and enhancing data integrity. This procedure includes dividing large tables into smaller,
interconnected ones to eliminate duplicated data. Conversely, denormalization is the
deliberate act of introducing redundancy to enhance SQL query performance.

38. What is a clustered index?

A clustered index in SQL determines the physical order of data rows in a table. Each table
can have only one clustered index, which impacts the table's storage structure. Rows in a
table are physically stored in the same order as the clustered index key.

39. How do you prevent SQL injection?

SQL injection represents a security flaw that arises when SQL queries mishandle untrusted
data, posing a risk of unauthorized access or data tampering. To ward off SQL injection,
employ techniques like parameterized queries, prepared statements, input validation, and
the enforcement of stringent access controls.

40. What are the different types of triggers?

 DML triggers: These triggers fire in response to data manipulation language

(DML) operations such as INSERT, UPDATE, or DELETE.
 DDL triggers: These triggers fire in response to data definition language (DDL)
events, such as table or view creation.

41. Explain the concept of a database schema

In SQL, a database schema is a conceptual container for housing various database
elements, such as tables, views, indexes, and procedures. Its primary purpose is to facilitate
the organization and segregation of these database elements while specifying their structure
and interconnections.
42. How is data integrity ensured in SQL?
Data integrity in SQL is ensured through various means, including constraints (e.g., primary
keys, foreign keys, check constraints), normalization, transactions, and referential integrity
constraints. These mechanisms prevent invalid or inconsistent data from being stored in the
database.

43. What is an SQL injection?

SQL injection is a cybersecurity attack method that involves inserting malicious SQL code
into an application's input fields or parameters. This unauthorized action enables attackers to
illicitly access a database, extract confidential information, or manipulate data.

44. How do you create a stored procedure?

You use the CREATE PROCEDURE statement to create a stored procedure in SQL. A
stored procedure can contain SQL statements, parameters, and variables. Here's a simple
example:

CREATE PROCEDURE GetEmployeeByID(@EmployeeID INT)

AS
BEGIN
SELECT * FROM employees WHERE employee_id = @EmployeeID;
END;

45. What is a deadlock in SQL? How can it be prevented?

A deadlock in SQL occurs when two or more transactions cannot proceed because they are
waiting for resources held by each other. Deadlocks can be prevented or resolved by using
techniques such as locking hierarchies, timeouts, or deadlock detection and resolution
mechanisms.

C. SQL Interview Questions for Experienced

46. Explain different isolation levels in SQL

Isolation levels define the visibility of data changes that one transaction makes to other
concurrent transactions. There are four commonly used isolation levels in SQL:

 READ UNCOMMITTED: At this isolation level, transactions can read changes

made by other transactions even if those changes have not been committed.
While this provides the highest concurrency level, it also introduces the risk of
encountering dirty reads.
 READ COMMITTED: In this level, transactions can only read committed data,
avoiding dirty reads. However, it may still suffer from non-repeatable reads and
phantom reads.
 REPEATABLE READ: Transactions at this level ensure that any data read during
the transaction remains unchanged throughout the transaction's lifetime. It
prevents non-repeatable reads but may still allow phantom reads.
 SERIALIZABLE: This represents the utmost isolation level, guaranteeing absolute
isolation between transactions. While it eradicates all concurrency problems,
locking mechanisms may reduce its efficiency.
47. How does a clustered index work, and how is it different from a non-clustered index?
A clustered index defines the actual storage order of rows within a table, allowing for only
one clustered index per table and directly influencing the on-disk data organization.
Conversely, a non-clustered index does not impact the physical arrangement of data and
can coexist with multiple indexes within the same table.

 Clustered Index: When you create a clustered index on a table, the table's rows
are physically rearranged to match the order of the indexed column(s). This
makes range queries efficient but may slow down insert/update operations.
 Non-clustered Index: Non-clustered indexes are separate data structures that
store a copy of a portion of the table's data and point to the actual data rows. They
improve read performance but come with some overhead during data
modification.

48. Discuss SQL Server Reporting Services

SQL Server Reporting Services is a reporting tool provided by Microsoft for creating,
managing, and delivering interactive, tabular, graphical, and free-form reports. SSRS allows
users to design and generate reports from various data sources, making it a valuable asset
for businesses needing comprehensive reporting capabilities.

49. What are CTEs (Common table expressions)?

Common Table Expressions (CTEs) serve as momentary result sets that you can mention
within SQL statements, typically found within SELECT, INSERT, UPDATE, or DELETE
operations. They're established using the `WITH` keyword and are instrumental in
streamlining intricate queries by dividing them into more digestible components.

50. Explain the MERGE statement

The SQL MERGE statement executes insertions, updates, or deletions on a target table,
guided by the outcomes of a source table or query. It consolidates the functionalities of
several individual statements (INSERT, UPDATE, DELETE) into one comprehensive
statement, rendering it particularly valuable for achieving data synchronization between
tables.

51. How do you use a window function in SQL?

Window functions are employed to perform computations on a group of table rows
associated with the current row. They enable the generation of result sets containing
aggregated data while retaining the distinct details of each row. Typical window functions
encompass ROW_NUMBER(), RANK(), DENSE_RANK(), and SUM() OVER().

52. What is a pivot table, and how do you create one in SQL?
A pivot table is a technique for rotating or transposing rows into columns to better analyze
and summarize data. You can create pivot tables in SQL using the `PIVOT` operator to
convert row-based data into a column-based format.

53. Describe the process of database mirroring

Database mirroring is a high-availability solution in SQL Server that involves creating and
maintaining redundant database copies on separate servers. It ensures data availability and
disaster recovery by automatically failing over to the mirror server in case of a primary server
failure.
54. Explain the concept of table partitioning
Partitioning a table involves breaking down a sizable table into smaller, more easily handled
segments known as partitions. This method can enhance SQL query efficiency by permitting
the Server to focus solely on pertinent partitions while executing queries. Typically,
partitioning uses a column characterized by a high cardinality, such as date or region.

55. How do you handle transactions in distributed databases?

Handling transactions in distributed databases involves ensuring the ACID (Atomicity,
Consistency, Isolation, Durability) properties across multiple databases or nodes. This can
be achieved through distributed transaction management protocols like Two-Phase Commit
(2PC) or by using distributed database systems designed for this purpose.

56. What is the use of the explain plan?

The EXPLAIN plan is a valuable feature in numerous relational database management
systems. This tool offers a comprehensive view of the database engine's strategy for
executing a query, encompassing details such as the selected execution plan, join
techniques, index utilization, and projected costs. Database administrators (DBAs) and
developers rely on EXPLAIN plans to enhance the performance of their queries.

57. Discuss SQL Server integration services (SSIS)

Microsoft provides SQL Server Integration Services as a powerful ETL (Extract, Transform,
Load) tool. It enables data integration from various sources, data transformation as needed,
and data loading into destination systems like data warehouses or databases.
Gain the confidence to ace your next interview and master real-world skills. Join the SQL
Certification Course today! ✍️

58. What are indexed views?

Indexed or materialized views are precomputed result sets stored as physical tables in the
database. They improve query performance by allowing the database engine to access pre-
aggregated or pre-joined data directly from the indexed view, reducing the need for complex
query processing.

59. Explain the concept of database sharding

Database sharding is a horizontal partitioning technique that distributes data across multiple
database instances or servers. It's commonly used in large-scale systems to improve
scalability and performance. Each shard contains a subset of the data, and a sharding
strategy determines how data is distributed.

60. How do you manage large-scale databases for performance?

Managing large-scale databases for performance involves various strategies, including
proper indexing, partitioning, query optimization, hardware optimization, and caching.
Monitoring and fine-tuning the database ensures optimal performance as data volumes
grow.

61. What is a materialized view?

Materialized views are a type of database component designed to maintain the outcomes of
a query in the form of a tangible table. These views undergo periodic updates to ensure that
the stored data remains current. They enhance the efficiency of database queries,
particularly for intricate or frequently executed ones.

62. Discuss the strategies for database backup and recovery

Ensuring data availability and disaster recovery relies on implementing vital backup and
recovery strategies. These strategies encompass various methods, such as full backups,
differential backups, transaction log backups, and regular testing of restoration stored
procedures.

63. What are the best practices for securing a SQL database?
Securing a SQL database involves implementing access controls, encryption, auditing, and
regular security assessments. Best practices include using strong authentication, limiting
permissions, and keeping database systems and software up to date.

64. Explain the concept of database replication

Replication is copying and synchronizing data from one database to another. It ensures data
availability, load balancing, and disaster recovery. Common replication types include
snapshot, transactional, and merge replication.

65. How do you monitor SQL Server performance?

Monitoring SQL Server performance involves tracking key performance metrics, setting up
alerts for critical events, and analyzing performance bottlenecks. Tools like SQL Server
Profiler and Performance Monitor are commonly used.

66. What is a database warehouse?

A database warehouse is a centralized repository that stores data from various sources for
analytical and reporting purposes. It is optimized for querying and analysis and often
contains historical data.

67. Explain the use of full-text search in SQL

Full-text search in SQL allows users to search for text-based data within large text fields or
documents. It uses advanced indexing and search algorithms to provide efficient and
accurate text-searching capabilities.

68. How do you manage database concurrency?

Database concurrency involves a database system's capability to manage multiple
concurrent transactions while upholding data integrity. To achieve this, various techniques
such as locking mechanisms, optimistic concurrency control, and isolation levels are
employed to oversee and regulate database concurrency.

69. What are the challenges in handling big data in SQL?

Handling big data in SQL involves dealing with large volumes of data that exceed the
capabilities of traditional database systems. Challenges include data storage, processing,
scalability, and efficient querying. Solutions may include distributed databases and big data
technologies like Hadoop and Spark.

70. How do you implement high availability in SQL databases?

High availability in SQL databases ensures that the database remains accessible and
operational despite failures. Techniques like clustering, replication, and failover mechanisms
help achieve high availability.

71. Explain the use of XML data type in SQL Server

The XML data type allows for storing, retrieving, and manipulating XML data. It also provides
support for querying XML documents using XQuery and is commonly used in applications
that deal with XML data structures.

72. Discuss the concept of NoSQL databases and their interaction with SQL
NoSQL databases are non-relational databases for handling large volumes of unstructured
or semi-structured data. They interact with SQL databases through various integration
methods, such as data pipelines, ETL processes, and API-based data transfers.

73. What is a spatial database?

A spatial database stores and queries geometric and geographic data, such as maps, GPS
coordinates, and spatial objects. It provides specialized functions and indexing methods to
support spatial queries and analysis.

74. How do you migrate a database from one SQL server to another?
Database migration involves moving a database from one SQL server or platform to another.
This undertaking demands careful planning, data transfer, schema conversion, and thorough
testing to ensure a smooth transition while minimizing the potential for data loss or system
downtime.

75. Discuss advanced optimization techniques for SQL queries

Advanced optimization techniques for SQL queries include using query hints, indexing
strategies, query rewriting, and understanding the query execution plan. Profiling tools and
performance monitoring are essential for identifying and resolving performance bottlenecks.

D. Query-based SQL Interview Questions and Answers

1. How do you find the third-highest salary in an employee table?

SELECT MIN(Salary) AS ThirdHighestSalary
FROM Employee
WHERE Salary IN (
SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 3
);
Think of this like picking gold, silver, and bronze salaries. The inner query gets the top 3
salaries (ignoring duplicates). Then, the outer part grabs the smallest among those three,
which is exactly the third-highest. This is a simple and clean SQL tip and there’s no need for
complex ranking functions.
2. How do you list employees who don’t report to any manager?
SELECT *
FROM Employee
WHERE ManagerID IS NULL;
Not every employee has a boss in the system, like CEOs, founders, or department heads.
Their ManagerID field is usually empty (NULL). This query finds all such records where the
reporting manager field hasn’t been filled in.

3. How do you count how many employees are in each job title?
sql
CopyEdit
SELECT JobTitle, COUNT(*) AS TotalEmployees
FROM Employee
GROUP BY JobTitle;
Say you want to see how your workforce breaks down, how many developers, designers,
HR folks, etc. This query groups everyone by job title and counts how many fall into each
group. It's like a department headcount but by role.

4. How do you find employees hired this year?

sql
CopyEdit
SELECT *
FROM Employee
WHERE YEAR(JoiningDate) = YEAR(CURDATE());
This one pulls out everyone who joined the company in the current calendar year. It uses the
YEAR() function to compare the year from the JoiningDate with the current year based on
today’s date. Great for yearly hiring reports.

5. How can you spot duplicate salary values?

sql
CopyEdit
SELECT Salary, COUNT(*)
FROM Employee
GROUP BY Salary
HAVING COUNT(*) > 1;
Want to know if multiple employees are being paid exactly the same salary? This query
groups all salaries and shows only the ones that appear more than once. It’s especially
useful when reviewing fairness or compensation overlaps.

E. Popular SQL Server Interview Questions

1. What is the difference between CAST and CONVERT functions in SQL?

In SQL job interviews, you're often asked about data type conversion, and that’s where
CAST() and CONVERT() come in. Both are used to change a value from one data type to
another, like turning a string into a date or a number.
The key difference is that CAST() follows the SQL standard, making it more portable across
different databases. CONVERT() is specific to SQL Server and provides more formatting
options, especially useful when handling date and time formats.
During SQL interview preparation, it’s smart to know when to use each depending on the
system you're working with.

2. How do you design a database schema for a large-scale application?

The first step is understanding the application requirements clearly. Then, normalize the data
to remove redundancy, define clear relationships using foreign keys, and create indexes for
faster lookups.
Also, consider how the system will handle growth, through partitioning, sharding, or caching
strategies. A good schema is not just about structure; it’s about designing with performance,
scalability, and clarity in mind.

3. How do you implement Data Security and Encryption in SQL?

Data protection often comes up during SQL job interviews, and it's an essential part of
working with sensitive databases.
To implement security, use techniques like hashing passwords with HASHBYTES() or
encrypting fields using ENCRYPTBYKEY() in SQL Server. Add role-based access controls
so only authorized users can access or modify confidential information.
Don’t forget to encrypt connections using SSL/TLS. Understanding how to secure data, both
in transit and at rest, is a key part of SQL interview preparation, especially for roles involving
large-scale applications or regulated industries.

SQL Interview Questions at a Glance

Interviewers usually assess how well you understand SQL, from simple commands to how
you apply it in real situations. Let’s look at some of the most common SQL interview
questions based on different experience levels:

A. SQL Interview Questions for Freshers

 General SQL Concepts
As a beginner, the interviewer is likely to ask you to elucidate on what the abbreviation SQL
stands for and how it can be applied to daily data management. They may ask SQL
interview questions about databases, data types that might be stored in a database, and
whether you've used any tools or engines in your coursework or job.
 SQL Commands and Usage
You should be comfortable with basic commands like SELECT, INSERT, UPDATE and
DELETE. They may give you a couple of tasks to do, say, filter data with WHERE and order
the data with ORDER BY. These types of basic SQL interview questions would be included
to ensure you have a good sense of how SQL behaves.
 Keys and Rules in Tables
You should be prepared to explain what the term primary key, unique key or foreign key is.
These terms are important for keeping the data structured the right way. They may also ask
how constraints like NOT NULL or UNIQUE assist in keeping data clean.
 Joins and Handling Missing Data
You’ll likely get questions on combining data from different tables using joins like INNER
JOIN or LEFT JOIN. Interviewers also ask how you’d deal with missing values in the data, so
knowing how NULL works is useful.

B. SQL Interview Questions for Intermediate

 Working with Groups and Totals
You will likely be presented with questions about calculating totals or averages using SUM,
AVG or COUNT. Employers are assessing if you know how to group data using GROUP BY,
which is common in reports.
 Joins and Inside Queries
You may be asked questions about joining tables together and using queries inside other
queries (subqueries); all are common methods of data analysis in projects, so the
interviewers are looking for your ability to easily write those queries easily.
 Combining Results
You might be asked to join results using UNION or find differences using other methods.
These types of SQL interview questions and answers show how you handle more than one
SQL query result at a time.
 Improving Speed
Some questions may touch on how to make queries run faster. You don’t need to go too
deep, but having a basic idea of things like indexes and keeping queries clean can help you
stand out.

C. SQL Interview Questions for Experienced

In a senior-level technical SQL interview, you can expect questions that test your knowledge
of SQL syntax, database design, query optimization, and problem-solving abilities using
SQL. Other technical SQL interview question topics include:
 Enhancing Queries
If you've been working with SQL for any length of time, you can count on being asked about
improving and speeding up your queries. SQL interview questions posed to inexperienced
candidates will be more focused on how they deal with the slowdown of very large
databases while keeping their processes sped up and efficient.
 Managing Data Changes
In addition, employers would like to see how you handle situations requiring more than one
user to manage the same data. You might be asked broader committed questions like how
you save a change, when you can apply a rollback, or how you address public safety with
data revisions.
 Using Stored Procedures
Interviews might also involve complying with, or making use of, stored procedures and
triggers which are good solutions for saving reusable SQL processes and automating some
of the processes. Generally, interviewers want to learn if you utilize either in your practice.
 Solving Business Problems
When it comes to SQL interview questions for professionals/individuals with 5 years of
experience, questions are less about definitions and more about scenarios in your past. So
be prepared to talk about how you might have resolved a data problem, fixed or enhanced a
report you previously created, or how you might have informed the decision-making process
at a team level with SQL data.