SQL

WHAT ARE SQL JOINS?
In SQL, a join is used to combine rows from two or more tables based on a shared column. Joins can be used to retrieve data in various ways, depending on the relationship between tables.
Joins are classified into four types:
Full Join, Inner Join, Left Join, Right Join.

HOW CAN I MAKE EMPTY TABLES THAT SHARE ANOTHER TABLE’S STRUCTURE?
It is possible to create empty tables with the same structure by using the INTO operator to fetch the data from one table into a new table while setting a WHERE clause to false for each entry. Since the WHERE clause is in effect, SQL as a result creates a new table with a duplicate structure to accommodate the fetched entries, but nothing is stored in the new table.

WHAT DOES “UNIQUE CONSTRAINT” MEAN?
The UNIQUE Constraint prevents the occurrence of the same values in a column in two records. Every value in a column is guaranteed to be unique by the UNIQUE constraint.

WHAT DO THE SQL TERMS “TABLE” AND “FIELD” MEAN?
A table is defined as a collection of rows and columns of organised data. Simply put, it is a table-formatted collection of related data.
The number of columns in a table is referred to as a field, and rows and columns are referred to as tuples and attributes in this context. Fields represent the traits and attributes in the record and hold detailed information about the data.

WHAT DISTINGUISHES A PRIMARY KEY FROM A UNIQUE KEY?
Both primary and unique keys can have distinct values, but only unique keys can have a null value for a primary key. There can only be one primary key per table, but there can be many unique keys.

What is the SELECT statement?
A SELECT command gets zero or more rows from one or more database tables or views. In most applications, SELECT is the most frequently used data manipulation language (DML) command. Due to the declarative nature of SQL, SELECT queries define a result set rather than how to calculate it.

LIST THE VARIOUS SQL RELATIONSHIP TYPES.
In the database, there are various kinds of relations:
Each record in one table corresponds to a maximum of one record in the other in a connection known as a one-to-one relationship between two tables.
The most common connection, where a record in one table is linked to several records in another, is one-to-many and many-to-one.
When defining a relationship that necessitates multiple instances on both sides, the term “many-to-many” is used.
Self-Referencing Relationships – This technique should be used when a table needs to declare a relationship with itself.

WHAT IS A SELF-JOIN?
A join type that can be used to link two tables is a self-join. It is a unary relationship as a result. Each row of the table is joined in a self-join to all other rows in the table as well as to itself. In order to combine and compare rows from the same database table, a self-join is typically used.

WHAT ARE THE COMMENTS IN SQL?
SQL Comments are used to add clarification to specific areas of SQL statements as well as to stop them from being executed. In many programming languages, comments play a significant role. Unsupported by a Microsoft Access database are the comments. As a result, the examples in Mozilla Firefox and Microsoft Edge use the Microsoft Access database.
Comments on a Single Line: It begins with two consecutive hyphens (–).
Comments that span multiple lines: It begins with * and ends with *.

WHAT DO THE COMMANDS UNION, MINUS, AND INTERSECT MEAN?
The results of the two tables are combined using the UNION operator, and any duplicate entries are also removed.The first query’s rows are returned using the MINUS operator, but the second query’s rows are not returned.The results of both queries are combined into one row using the INTERSECT operator.

WHAT IS A VIEW?
In SQL, a view is a fictitious table built using the results of a SQL statement. Like a true table, a view also has rows and columns. A view contains fields from one or more actual database tables.

WHAT IS NORMALIZATION?
The process of efficiently organising structured data in a database is known as normalisation. It involves building tables, establishing connections between them, and specifying the rules for those connections. These guidelines can be used to control consistency and redundancy, giving the database more flexibility.

WHAT IS THE DIFFERENCE BETWEEN NOW() AND CURRENT_DATE
The constant time that NOW() returns is the moment when the statement started to run. (Within a trigger or stored function, NOW() returns the moment the function or triggering statement started to run.
The only distinction between NOW() and CURRENT DATE() is that NOW() will retrieve the current date and time in the format “YYYY-MM DD HH:MM:SS,” whereas CURRENT DATE() will retrieve the current day’s date in “YYYY-MM DD.”

WHAT IS A FOREIGN KEY?
A FOREIGN KEY is a field or group of fields in a table that serve as a direct reference to the PRIMARY KEY in a different table. Referential integrity in the relationship between two tables is guaranteed by a foreign key constraint.The table containing the candidate key is referred to as the referenced or parent table, and the table with the foreign key constraint is referred to as the child table.

WHAT IS A CONSTRAINT?
Constraint can be used to specify the limit on the data type of table. Constraints can be specified while creating or altering the table statement.

WHAT IS A QUERY?
A database query is a piece of code created to retrieve data from a database. A query can be created so that it matches our expectations for the set of results. Just a simple query to the database.

WHAT IS AN INDEX?
An index is a performance improvement technique that makes it possible to retrieve records from a table more quickly. Since an index creates an entry for each value, data retrieval will be quicker.

WHAT ARE THE DIFFERENT TYPES OF INDEXES IN SQL?
Unique Index: If the column is uniquely indexed, this index prevents the field from having duplicate values. An automatic unique index can be used if a primary key is defined.
Clustered Index: This index uses key values to conduct searches while rearranging the table’s physical order. There can only be one clustered index per table.
Non-Clustered Index: A non-clustered index keeps the data in a logical order while not changing the physical order of the table. Nonclustered indexes can be numerous for each table.

WHAT IS MEANT BY DENORMALIZATION?
Database administrators employ the denormalization technique to boost the effectiveness of their database infrastructure. The concept of denormalization is based on normalisation, which is defined as correctly organising a database into tables for a specific purpose. In order to resolve problems with database queries that combine data from multiple tables into a single table, we can use this method to add redundant data into a normalised database.

DATABASE FUNDAMENTALS & DESIGN

WHAT IS THE DIFFERENCE BETWEEN DBMS AND RDBMS?
DBMS (Database Management System): Software that stores data as files. It doesn’t necessarily require a relationship between data points (e.g., XML, File System).
RDBMS (Relational DBMS): Stores data in a tabular format (rows and columns) where tables are linked by common keys. Examples include SQL Server, PostgreSQL, and MySQL.
Finance Context: Almost all financial transaction systems (Core Banking, Insurance Claims) use RDBMS to ensure data consistency.

EXPLAIN THE DIFFERENT TYPES OF SQL COMMANDS (DDL, DML, DCL, TCL, DQL).
DDL (Definition): CREATE, ALTER, DROP (defines the structure).
DML (Manipulation): INSERT, UPDATE, DELETE (modifies the data).
DQL (Query): SELECT (retrieves the data).
DCL (Control): GRANT, REVOKE (manages permissions).
TCL (Transaction): COMMIT, ROLLBACK (manages transaction stability).

WHAT ARE THE ACID PROPERTIES IN SQL?
Atomicity: The entire transaction happens at once or not at all.
Consistency: The database remains in a valid state before and after the transaction.
Isolation: Multiple transactions can occur without interfering with each other.
Durability: Once a transaction is committed, it remains saved even during a system failure.
Critical for: Ensuring a bank transfer doesn’t “lose” money between accounts if the power goes out.

WHAT IS THE DIFFERENCE BETWEEN A PRIMARY KEY AND A UNIQUE KEY?
Primary Key: Uniquely identifies a record. It cannot be NULL. There can only be one per table.
Unique Key: Ensures values in a column are unique. It can accept one NULL value. You can have multiple unique keys.

WHAT IS A FOREIGN KEY AND “REFERENTIAL INTEGRITY”?
A Foreign Key is a column in one table that points to the Primary Key of another table. It maintains Referential Integrity by ensuring that you cannot add a record to the “child” table if the corresponding “parent” record does not exist (e.g., you can’t assign a claim to a Policy ID that doesn’t exist).

EXPLAIN NORMALIZATION AND ITS TYPES.
Normalization is the process of organizing data to reduce redundancy and improve data integrity.
1NF: Each cell must contain atomic (single) values.
2NF: Must be in 1NF and all non-key attributes must depend on the whole primary key.
3NF: Must be in 2NF and have no “transitive functional dependencies” (column A depends on B, which depends on C).

WHEN WOULD YOU USE DENORMALIZATION?
In data warehousing or reporting (OLAP systems). By intentionally adding redundancy, you reduce the number of complex “Joins,” which makes read-only reporting queries much faster.

DATA RETRIEVAL & FILTERING

WHAT IS THE DIFFERENCE BETWEEN THE WHERE AND HAVING CLAUSES?
WHERE: Filters rows before any groupings are made. It cannot be used with aggregate functions (like SUM or AVG).
HAVING: Filters groups after the GROUP BY clause has been applied.
Example: WHERE Salary > 5000 vs. HAVING AVG(Salary) > 5000.

EXPLAIN THE LOGIC OF GROUP BY.
It collapses multiple rows with the same values into summary rows. It is almost always used with aggregate functions.
Actuarial Use: SELECT Region, COUNT(PolicyID) FROM Claims GROUP BY Region; to find total claims per region.

WHAT DO THE AGGREGATE FUNCTIONS DO?
SUM(): Total value.
AVG(): Arithmetic mean.
COUNT(): Number of rows.
MIN() / MAX(): Lowest and highest values.

WHAT IS THE DIFFERENCE BETWEEN SELECT DISTINCT AND GROUP BY?
DISTINCT: Simply removes duplicate rows from the final result set.
GROUP BY: Groups rows for the purpose of performing calculations (aggregates) on them.

WHAT IS THE “ORDER OF EXECUTION” IN A SQL QUERY?
Understanding this helps you debug why certain aliases created in the SELECT clause can’t be used in the WHERE clause.

JOINS & RELATIONSHIPS (CRUCIAL FOR INTERVIEWS)

EXPLAIN THE 4 MAIN TYPES OF JOINS.
INNER JOIN: Returns records that have matching values in both tables.
LEFT (OUTER) JOIN: Returns all records from the left table, and matched records from the right. Unmatched right-side columns show as NULL.
RIGHT (OUTER) JOIN: Reverse of the Left Join.
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table.

WHAT IS A SELF-JOIN AND WHEN WOULD YOU USE IT?
Joining a table to itself.
Example: In an Employees table where one column is ManagerID. To find the name of an employee’s manager, you join the employees table (as e1) to the employees table (as e2).

WHAT IS A CROSS JOIN?
It produces a “Cartesian Product,” matching every row of the first table with every row of the second. (If Table A has 10 rows and Table B has 10, the result has 100 rows).

DIFFERENCE BETWEEN UNION AND JOIN?
JOIN: Combines data horizontally (adds columns from another table).
UNION: Combines data vertically (appends rows from one result set to another).

DIFFERENCE BETWEEN UNION AND UNION ALL?
UNION: Removes duplicate rows and sorts the result.
UNION ALL: Keeps all duplicates and is much faster because it doesn’t perform the “distinct” check.

ADVANCED ANALYTICS & SUBQUERIES

WHAT IS A SUBQUERY? EXPLAIN NESTED VS. CORRELATED.
Nested: An inner query that runs once and passes its result to the outer query.
Correlated: An inner query that executes once for every row processed by the outer query (SLOWER BUT POWERFUL).

WHAT ARE CTES (COMMON TABLE EXPRESSIONS)?
A temporary result set defined using the WITH clause.
Why use it: It makes complex queries much easier to read and maintain than nested subqueries.

EXPLAIN WINDOW FUNCTIONS (RANK, DENSE_RANK, ROW_NUMBER).
ROW_NUMBER(): Assigns a unique sequential integer to rows.
RANK(): Assigns the same rank to ties, but skips the next rank (e.g., 1, 2, 2, 4).
DENSE_RANK(): Assigns the same rank to ties and does not skip the next rank (e.g., 1, 2, 2, 3).

WHAT ARE LEAD AND LAG FUNCTIONS?
Used to look at the value of the next row (LEAD) or the previous row (LAG) without a self-join.
Finance Use: Calculating month-over-month growth in revenue.

HOW DO YOU HANDLE NULL VALUES?
Using IS NULL or IS NOT NULL.
COALESCE(val1, val2): Returns the first non-null value in a list. Use this to replace NULLs with 0 in financial reports.

PERFORMANCE & OBJECTS

WHAT IS AN INDEX AND WHY IS IT USED?
A data structure (like a book’s index) that allows the database to find rows faster without scanning every single row in a table.

CLUSTERED VS. NON-CLUSTERED INDEX?
Clustered: Determines the physical order of data in the table (only 1 per table).
Non-Clustered: A separate structure that points back to the data (multiple allowed).

WHAT IS A VIEW?
A virtual table based on the result-set of a SQL statement. It doesn’t store data itself but provides a way to simplify complex queries for end-users.

STORED PROCEDURE VS. FUNCTION.
Stored Procedure: Can perform any task (DML/DDL), can return multiple values, and is used for “business logic.”
Function: Must return a single value and cannot perform DML (INSERT/UPDATE) on permanent tables.

WHAT IS A TRIGGER?
A stored program that automatically “fires” or executes when a specific event (INSERT, UPDATE, or DELETE) occurs in a table.

DELETE VS. TRUNCATE VS. DROP.
DELETE: Removes specific rows (can use WHERE), logged per row, slower.
TRUNCATE: Removes all rows, faster, does not log individual deletions, structure remains.
DROP: Removes the entire table and its structure from the database.

WHAT IS “SQL INJECTION” AND HOW DO YOU PREVENT IT?
A security vulnerability where an attacker “injects” malicious SQL code into a query via user input. Prevented by using Parameterized Queries or Stored Procedures.

HOW DO YOU FIND THE 2ND HIGHEST SALARY IN AN EMPLOYEES TABLE?
SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees);