SQL Most frequent Interview questions

 

Basic SQL Queries

1. What is SQL, and what are its major components?

   Answer: SQL (Structured Query Language) is used for managing and manipulating relational databases. Its major components include:

   • DDL (Data Definition Language): Defines database schema (e.g., CREATE, ALTER, DROP).

   • DML (Data Manipulation Language): Manages data within schema objects (e.g., SELECT, INSERT, UPDATE, DELETE).

   • DCL (Data Control Language): Controls access to data (e.g., GRANT, REVOKE).

   • TCL (Transaction Control Language): Manages transactions (e.g., COMMIT, ROLLBACK).

2. How do you retrieve all columns from a table?

   Answer: Use the SELECT statement with an asterisk:

$$SELECT\ast FROMtabl{e}_{n}ame;SELECT\; *\; FROM\; table\_name;$$

3. What is the difference between WHERE and HAVING clauses?

   Answer:

   • WHERE: Filters rows before grouping; used with SELECT, UPDATE, DELETE.

   • HAVING: Filters groups after aggregation; used with GROUP BY.

4. How do you sort query results?

   Answer: Use the ORDER BY clause:

$$SELECTcolum{n}_{n}ame(s)FROMtabl{e}_{n}ameORDERBYcolum{n}_{n}ame[ASC\mathrm{\mid }DESC];SELECT\; column\_name(s)\; FROM\; table\_name\; ORDER\; BY\; column\_name\; [ASC|DESC];$$

5. Explain how to use the LIKE operator.

   Answer: The LIKE operator is used for pattern matching in a WHERE clause:

$$SELECTcolum{n}_{n}ame(s)FROMtabl{e}_{n}ameWHEREcolum{n}_{n}ameLIKEpattern;SELECT\; column\_name(s)\; FROM\; table\_name\; WHERE\; column\_name\; LIKE\; pattern;$$

• % represents zero or more characters.

• _ represents a single character.

Advanced SQL Queries

6. What are JOINs in SQL, and what are the different types?

   Answer: JOINs combine rows from two or more tables based on related columns. Types include:

   • INNER JOIN: Returns matching rows from both tables.

$$SELECTcolumnsFROMtable1INNERJOINtable2ONtable1.column=table2.column;SELECT\; columns\; FROM\; table1\; INNER\; JOIN\; table2\; ON\; table1.column\; =\; table2.column;$$

• LEFT (OUTER) JOIN: Returns all rows from the left table and matching rows from the right table.

$$SELECTcolumnsFROMtable1LEFTJOINtable2ONtable1.column=table2.column;SELECT\; columns\; FROM\; table1\; LEFT\; JOIN\; table2\; ON\; table1.column\; =\; table2.column;$$

• RIGHT (OUTER) JOIN: Returns all rows from the right table and matching rows from the left table.

$$SELECTcolumnsFROMtable1RIGHTJOINtable2ONtable1.column=table2.column;SELECT\; columns\; FROM\; table1\; RIGHT\; JOIN\; table2\; ON\; table1.column\; =\; table2.column;$$

• FULL (OUTER) JOIN: Returns all rows when there is a match in either table.

$$SELECTcolumnsFROMtable1FULLJOINtable2ONtable1.column=table2.column;SELECT\; columns\; FROM\; table1\; FULL\; JOIN\; table2\; ON\; table1.column\; =\; table2.column;$$

7. How do you find the second highest salary from a table?

   Answer: Use the LIMIT and OFFSET clauses or a subquery:

8. What is a subquery, and how is it used?

   Answer: A subquery is a query within another query, used for complex filtering and transformations:

$$SELECTcolum{n}_{n}ame(s)FROMtabl{e}_{n}ameWHEREcolum{n}_{n}ame=(SELECTcolum{n}_{n}ameFROManothe{r}_{t}ableWHEREcondition);SELECT\; column\_name(s)\; FROM\; table\_name\; WHERE\; column\_name\; =\; (SELECT\; column\_name\; FROM\; another\_table\; WHERE\; condition);$$

9. Explain the use of the CASE statement in SQL.

   Answer: The CASE statement performs conditional logic in queries:

$$SELECTcolum{n}_{n}ame,CASEWHENcondition1THENresult1WHENcondition2THENresult2ELSEresultNENDFROMtabl{e}_{n}ame;SELECT\; column\_name,\; CASE\; WHEN\; condition1\; THEN\; result1\; WHEN\; condition2\; THEN\; result2\; ELSE\; resultN\; END\; FROM\; table\_name;$$

10. How do you create and use temporary tables?

    Answer: Temporary tables store intermediate results and exist only during the session:

$$CREATETEMPORARYTABLEtem{p}_{t}abl{e}_{n}ame(colum{n}_{d}efinitions);CREATE\; TEMPORARY\; TABLE\; temp\_table\_name\; (column\_definitions);$$

Insert data and use it as a regular table.

Data Aggregation and Analysis

11. What are aggregate functions in SQL, and can you name a few?

    Answer: Aggregate functions perform calculations on a set of values and return a single value:

    • COUNT: Counts rows.

$$SELECTCOUNT(\ast )FROMtabl{e}_{n}ame;SELECT\; COUNT(*)\; FROM\; table\_name;$$

• SUM: Adds numeric values.

$$SELECTSUM(colum{n}_{n}ame)FROMtabl{e}_{n}ame;SELECT\; SUM(column\_name)\; FROM\; table\_name;$$

• AVG: Calculates average value.

$$SELECTAVG(colum{n}_{n}ame)FROMtabl{e}_{n}ame;SELECT\; AVG(column\_name)\; FROM\; table\_name;$$

• MAX/MIN: Finds maximum/minimum value.

$$SELECTMAX(colum{n}_{n}ame),MIN(colum{n}_{n}ame)FROMtabl{e}_{n}ame;SELECT\; MAX(column\_name),\; MIN(column\_name)\; FROM\; table\_name;$$

12. How do you group data in SQL?

    Answer: Use the GROUP BY clause:

$$SELECTcolum{n}_{n}ame,aggregat{e}_{f}unction(colum{n}_{n}ame)FROMtabl{e}_{n}ameGROUPBYcolum{n}_{n}ame;SELECT\; column\_name,\; aggregate\_function(column\_name)\; FROM\; table\_name\; GROUP\; BY\; column\_name;$$

13. Explain the difference between UNION and UNION ALL.

    Answer:

    • UNION: Combines results of two queries, removing duplicates.

$$SELECTcolum{n}_{n}ame(s)FROMtable1UNIONSELECTcolum{n}_{n}ame(s)FROMtable2;SELECT\; column\_name(s)\; FROM\; table1\; UNION\; SELECT\; column\_name(s)\; FROM\; table2;$$

• UNION ALL: Combines results of two queries, including duplicates.

$$SELECTcolum{n}_{n}ame(s)FROMtable1UNIONALLSELECTcolum{n}_{n}ame(s)FROMtable2;SELECT\; column\_name(s)\; FROM\; table1\; UNION\; ALL\; SELECT\; column\_name(s)\; FROM\; table2;$$

14. How do you handle NULL values in SQL?

    Answer: Use functions like IS NULL, IS NOT NULL, COALESCE, and NVL:

$$SELECTcolum{n}_{n}ameFROMtabl{e}_{n}ameWHEREcolum{n}_{n}ameISNULL;SELECT\; column\_name\; FROM\; table\_name\; WHERE\; column\_name\; IS\; NULL;$$

$$SELECTCOALESCE(colum{n}_{n}ame,defaul{t}_{v}alue)FROMtabl{e}_{n}ame;SELECT\; COALESCE(column\_name,\; default\_value)\; FROM\; table\_name;$$

15. What is the purpose of the DISTINCT keyword?

    Answer: DISTINCT removes duplicate rows in the result set:

$$SELECTDISTINCTcolum{n}_{n}ame(s)FROMtabl{e}_{n}ame;SELECT\; DISTINCT\; column\_name(s)\; FROM\; table\_name;$$

Database Design and Optimization

16. What are primary keys and foreign keys in SQL?

    Answer:

    • Primary Key: A unique identifier for each record in a table.

$$CREATETABLEtabl{e}_{n}ame(colum{n}_{n}amedatatypePRIMARYKEY);CREATE\; TABLE\; table\_name\; (column\_name\; datatype\; PRIMARY\; KEY);$$

• Foreign Key: A reference to the primary key in another table to establish relationships.

$$CREATETABLEtabl{e}_{n}ame(colum{n}_{n}amedatatype,FOREIGNKEY(colum{n}_{n}ame)REFERENCESanothe{r}_{t}able(colum{n}_{n}ame));CREATE\; TABLE\; table\_name\; (column\_name\; datatype,\; FOREIGN\; KEY\; (column\_name)\; REFERENCES\; another\_table(column\_name));$$

17. How do you create indexes in SQL, and why are they important?

    Answer: Indexes improve query performance by speeding up data retrieval:

$$CREATEINDEXinde{x}_{n}ameONtabl{e}_{n}ame(colum{n}_{n}ame);CREATE\; INDEX\; index\_name\; ON\; table\_name\; (column\_name);$$

18. What is normalization, and why is it important?

    Answer: Normalization organizes database tables to reduce data redundancy and improve data integrity. It involves dividing large tables into smaller, related ones.

19. Explain the concept of database transactions.

    Answer: Transactions are a sequence of operations performed as a single unit of work. They ensure data integrity using ACID properties (Atomicity, Consistency, Isolation, Durability):

    • BEGIN TRANSACTION: Start a transaction.

    • COMMIT: Save changes.

    • ROLLBACK: Revert changes.

20. How do you optimize SQL queries for performance?

    Answer: Tips for optimizing SQL queries:

    • Use indexes on columns used in WHERE, JOIN, and ORDER BY clauses.

    • Avoid SELECT *; specify columns.

    • Use appropriate JOINs and avoid unnecessary subqueries.

    • Limit the number of returned rows using LIMIT or TOP.

    • Use query execution plans to identify bottlenecks.

Advanced SQL Concepts

21. What is a Stored Procedure, and how do you create one?

    Answer: Stored procedures are precompiled SQL code stored in the database, used to perform tasks and encapsulate logic:

$$CREATEPROCEDUREprocedur{e}_{n}ameASBEGINSQLstatements;END;CREATE\; PROCEDURE\; procedure\_name\; AS\; BEGIN\; SQL\; statements;\; END;$$

22. Explain Triggers and how they are used in SQL.

    Answer: Triggers automatically execute specified SQL code in response to certain events (e.g., INSERT, UPDATE, DELETE) on a table:

asic SQL Queries

1. What is SQL?

   Answer: SQL (Structured Query Language) is a standard language used to manage and manipulate relational databases.

2. How do you retrieve all columns from a table?

   Answer: Use the SELECT statement.

   sql

   SELECT * FROM table_name;
   

3. How do you filter records based on a condition?

   Answer: Use the WHERE clause.

   sql

   SELECT * FROM table_name WHERE condition;
   

4. Explain the GROUP BY clause.

   Answer: GROUP BY groups rows that have the same values in specified columns into summary rows.

   sql

   SELECT column1, COUNT(*) FROM table_name GROUP BY column1;
   

5. What is the purpose of the HAVING clause?

   Answer: HAVING is used to filter groups based on conditions, similar to WHERE but for groups.

   sql

   SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*) > 1;
   

Advanced SQL Concepts

6. What are JOIN operations in SQL?

   Answer: JOIN operations combine rows from two or more tables based on related columns.

   • INNER JOIN: Returns matching rows from both tables.

   sql

   SELECT * FROM table1 INNER JOIN table2 ON table1.common_column = table2.common_column;
   

7. Explain the difference between INNER JOIN and LEFT JOIN.

   Answer:

   • INNER JOIN: Returns rows with matching values in both tables.

   • LEFT JOIN: Returns all rows from the left table and matching rows from the right table. Non-matching rows from the right table return NULL.

   sql

   SELECT * FROM table1 LEFT JOIN table2 ON table1.common_column = table2.common_column;
   

8. What is a UNION operator?

   Answer: UNION combines the results of two or more SELECT queries, removing duplicates.

   sql

   SELECT column1 FROM table1 UNION SELECT column1 FROM table2;
   

9. Describe a SUBQUERY and its use.

   Answer: A SUBQUERY is a query nested within another query. Used for complex filtering and calculations.

   sql

   SELECT * FROM table1 WHERE column1 = (SELECT MAX(column1) FROM table2);
   

10. What is a CROSS JOIN?

    Answer: CROSS JOIN returns the Cartesian product of two tables, combining all rows from both tables.

    sql

    SELECT * FROM table1 CROSS JOIN table2;
    

Database Design and Management

11. Explain Normalization in databases.

    Answer: Normalization organizes data to reduce redundancy and improve data integrity. Includes several normal forms (1NF, 2NF, 3NF).

12. What is Denormalization and when would you use it?

    Answer: Denormalization adds redundancy to improve read performance, often used in data warehousing.

13. Define a Primary Key.

    Answer: A Primary Key uniquely identifies each record in a table and cannot be NULL.

    sql

    CREATE TABLE table_name (id INT PRIMARY KEY, column1 TEXT);
    

14. What is a Foreign Key?

    Answer: A Foreign Key links one table to another, establishing a relationship between them.

    sql

    CREATE TABLE child_table (id INT, parent_id INT, FOREIGN KEY (parent_id) REFERENCES parent_table(id));
    

15. Describe the difference between ACID properties and BASE properties.

    Answer:

    • ACID: Atomicity, Consistency, Isolation, Durability; ensures reliable transactions.

    • BASE: Basically Available, Soft state, Eventual consistency; used in NoSQL databases for scalability and flexibility.

SQL Functions and Aggregate Operations

16. What are Aggregate Functions?

    Answer: Functions that perform calculations on multiple rows and return a single value (e.g., SUM, AVG, COUNT, MAX, MIN).

    sql

    SELECT AVG(column1) FROM table_name;
    

17. Explain the CASE statement in SQL.

    Answer: CASE allows conditional logic in SQL queries.

    sql

    SELECT column1,
           CASE
               WHEN condition1 THEN result1
               WHEN condition2 THEN result2
               ELSE default_result
           END
    FROM table_name;
    

18. What is the COALESCE function?

    Answer: COALESCE returns the first non-NULL value in a list of arguments.

    sql

    SELECT COALESCE(column1, column2, 'default') FROM table_name;
    

19. How do you perform a STRING CONCATENATION in SQL?

    Answer: Use the || operator or CONCAT function.

    sql

    SELECT column1 || ' ' || column2 AS concatenated FROM table_name;
    -- or
    SELECT CONCAT(column1, ' ', column2) AS concatenated FROM table_name;
    

20. What is the RANK() function and how is it used?

    Answer: RANK() assigns a rank to each row within a partition of a result set.

    sql

    SELECT column1, RANK() OVER (PARTITION BY column2 ORDER BY column3) AS rank FROM table_name;
    

SQL Performance and Optimization

21. How do you improve SQL query performance?

    Answer: Techniques include:

    • Using indexes.

    • Writing efficient joins.

    • Avoiding unnecessary columns in SELECT.

    • Limiting result sets with WHERE and LIMIT.

    • Using query execution plans.

22. What are Indexes and their types?

    Answer: Indexes improve query performance by speeding up data retrieval. Types include:

    • B-tree indexes.

    • Hash indexes.

    • Bitmap indexes.

23. Explain the concept of an Execution Plan.

    Answer: An execution plan shows how SQL Server executes a query, including indexes used, join types, and query cost. It's used for performance tuning.

24. What is Partitioning in SQL databases?

    Answer: Partitioning divides large tables into smaller, manageable pieces called partitions, improving query performance and manageability.

    sql

    CREATE TABLE table_name PARTITION BY RANGE (column_name);
    

25. Describe Database Caching.

    Answer: Database caching stores frequently accessed data in memory to reduce query execution time and improve performance.

Transactional SQL

26. What is a Transaction in SQL?

    Answer: A transaction is a sequence of operations performed as a single logical unit of work, ensuring data integrity.

    sql

    BEGIN TRANSACTION;
    -- SQL operations
    COMMIT; -- or ROLLBACK;
    

27. Explain SAVEPOINT in transactions.

    Answer: SAVEPOINT creates a point within a transaction to which you can later roll back.

    sql

    SAVEPOINT savepoint_name;
    -- SQL operations
    ROLLBACK TO savepoint_name;
    

28. What is Isolation Level in SQL transactions?

    Answer: Isolation level determines the visibility of changes made in a transaction to other transactions. Levels include:

    • Read Uncommitted

    • Read Committed

    • Repeatable Read

    • Serializable

29. Describe the ROLLBACK command.

    Answer: ROLLBACK undoes transactions that have not been committed, reverting the database to its previous state.

    sql

    ROLLBACK;
    

30. What are Triggers in SQL?

    Answer: Triggers are automated actions executed in response to specific events on a table or view (e.g., INSERT, UPDATE, DELETE).

    sql

    CREATE TRIGGER trigger_name
    AFTER INSERT ON table_name
    FOR EACH ROW
    BEGIN
       -- Trigger logic
    END;

Advanced SQL Queries and Functions

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

    Answer:

    • UNION: Combines results of two queries, removing duplicates.

    • UNION ALL: Combines results, including duplicates.

    sql

    SELECT column1 FROM table1
    UNION
    SELECT column1 FROM table2;
    
    SELECT column1 FROM table1
    UNION ALL
    SELECT column1 FROM table2;
    

32. How do you use the EXISTS operator in SQL?

    Answer: EXISTS tests for the existence of rows in a subquery.

    sql

    SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id);
    

33. Explain the RANK(), DENSE_RANK(), and ROW_NUMBER() functions.

    Answer:

    • RANK(): Assigns ranks with gaps for ties.

    • DENSE_RANK(): Assigns ranks without gaps for ties.

    • ROW_NUMBER(): Assigns unique sequential numbers.

    sql

    SELECT column1, RANK() OVER (ORDER BY column2) AS rank FROM table_name;
    SELECT column1, DENSE_RANK() OVER (ORDER BY column2) AS dense_rank FROM table_name;
    SELECT column1, ROW_NUMBER() OVER (ORDER BY column2) AS row_num FROM table_name;
    

34. How do you perform a SELF JOIN in SQL?

    Answer: SELF JOIN joins a table with itself.

    sql

    SELECT a.column1, b.column2
    FROM table_name a
    JOIN table_name b ON a.id = b.id;

Practical SQL Scenarios

36. How do you find the second highest salary from a table?

    Answer: Use the DISTINCT and LIMIT clauses with a subquery.

    sql

    SELECT DISTINCT salary FROM table_name ORDER BY salary DESC LIMIT 1 OFFSET 1;
    

37. How do you detect duplicate records in a table?

    Answer: Use the GROUP BY and HAVING clauses.

    sql

    SELECT column1, COUNT(*)
    FROM table_name
    GROUP BY column1
    HAVING COUNT(*) > 1;
    

38. How do you delete duplicate records from a table?

    Answer: Use a CTE (Common Table Expression) and ROW_NUMBER() function.

    sql

    WITH CTE AS (
        SELECT column1, ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column1) AS row_num
        FROM table_name
    )
    DELETE FROM CTE WHERE row_num > 1;
    

39. How do you find the nth highest salary in a table?

    Answer: Use the DENSE_RANK() function in a subquery.

    sql

    SELECT salary FROM (
        SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
        FROM table_name
    ) WHERE rank = n;
    

40. How do you update data in one table based on data in another table?

    Answer: Use the UPDATE statement with a JOIN.

    sql

    UPDATE table1
    SET table1.column1 = table2.column2
    FROM table2
    WHERE table1.id = table2.id;
    

SQL Performance and Optimization

41. What are Composite Indexes and their benefits?

    Answer: Composite indexes include multiple columns, improving query performance for searches involving those columns.

    sql

    CREATE INDEX idx_composite ON table_name (column1, column2);

42. What is a Clustered Index and how is it different from a Non-Clustered Index?

    Answer:

    • Clustered Index: Sorts and stores data rows in the table based on the indexed column. Only one per table.

    • Non-Clustered Index: Creates a separate structure with pointers to the data rows. Multiple per table.

    sql

    CREATE CLUSTERED INDEX idx_clustered ON table_name (column1);
    CREATE NONCLUSTERED INDEX idx_nonclustered ON table_name (column1);
    

43. How do you handle large datasets in SQL?

    Answer: Techniques include:

    • Partitioning tables.

    • Indexing columns.

    • Using efficient joins and subqueries.

    • Limiting results with LIMIT or TOP.

44. What is the purpose of Materialized Views?

    Answer: Materialized views store query results physically to improve performance for complex queries that are frequently executed.

    sql

    CREATE MATERIALIZED VIEW view_name AS SELECT * FROM table_name;
    

Transactional SQL and Data Integrity

46. What is Referential Integrity and how is it enforced?

    Answer: Referential integrity ensures that relationships between tables remain consistent. It's enforced using Foreign Key constraints.

    sql

    ALTER TABLE child_table ADD CONSTRAINT fk_name FOREIGN KEY (parent_id) REFERENCES parent_table(id);
    

47. How do you implement Soft Deletes in SQL?

    Answer: Use a column (e.g., is_deleted) to mark records as deleted instead of physically removing them.

    sql

    UPDATE table_name SET is_deleted = 1 WHERE id = value;
    

48. What is the MERGE statement and its use?

    Answer: MERGE performs insert, update, or delete operations based on conditions.

    sql

    MERGE INTO target_table USING source_table
    ON (target_table.id = source_table.id)
    WHEN MATCHED THEN
        UPDATE SET target_table.column1 = source_table.column1
    WHEN NOT MATCHED THEN
        INSERT (column1, column2) VALUES (source_table.column1, source_table.column2);

51. What is a WINDOW FUNCTION and how is it used?

    Answer: Window functions perform calculations across a set of table rows related to the current row. Examples include ROW_NUMBER(), RANK(), DENSE_RANK(), and LAG().

    sql

    SELECT column1, RANK() OVER (PARTITION BY column2 ORDER BY column3) AS rank FROM table_name;
    

52. Explain the LAG() and LEAD() functions.

    Answer:

    • LAG(): Accesses data from a previous row in the same result set.

    • LEAD(): Accesses data from a subsequent row in the same result set.

    sql

    SELECT column1, LAG(column2, 1) OVER (ORDER BY column3) AS previous_value FROM table_name;
    

53. What are Common Table Expressions (CTEs) and their use?

    Answer: CTEs provide a way to write temporary named result sets, which can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

    sql

    WITH CTE AS (
        SELECT column1, column2 FROM table_name
    )
    SELECT * FROM CTE;
    

54. How do you use the PIVOT and UNPIVOT operators in SQL?

    Answer:

    • PIVOT: Transforms rows into columns.

    • UNPIVOT: Transforms columns into rows.

    sql

    -- PIVOT example
    SELECT * FROM (
        SELECT column1, column2, column3 FROM table_name
    ) PIVOT (SUM(column3) FOR column2 IN ([val1], [val2], [val3]));
    
    -- UNPIVOT example
    SELECT * FROM (
        SELECT column1, val1, val2, val3 FROM table_name
    ) UNPIVOT (value FOR column2 IN (val1, val2, val3));
    

55. Describe the JSON Functions available in SQL.

    Answer: SQL provides functions to handle JSON data, such as JSON_VALUE(), JSON_QUERY(), and OPENJSON().

    sql

    SELECT JSON_VALUE(json_column, '$.key') AS value FROM table_name;
    

SQL Query Optimization and Indexing

56. How do you handle NULL values in SQL?

    Answer: Use functions like IS NULL, IS NOT NULL, COALESCE(), and IFNULL().

    sql

    SELECT column1 FROM table_name WHERE column2 IS NULL;
    SELECT COALESCE(column1, 'default') FROM table_name;

Advanced SQL Techniques for Data Analysis

61. How do you calculate a Moving Average in SQL?

    Answer: Use window functions like AVG() with OVER() to calculate moving averages.

    sql

    SELECT column1, AVG(column2) OVER (ORDER BY column1 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM table_name;
    

62. Describe the FIRST_VALUE() and LAST_VALUE() functions.

    Answer:

    • FIRST_VALUE(): Returns the first value in an ordered set of values.

    • LAST_VALUE(): Returns the last value in an ordered set of values.

    sql

    SELECT column1, FIRST_VALUE(column2) OVER (ORDER BY column3) AS first_value FROM table_name;
    

63. What is the PERCENT_RANK() function?

    Answer: PERCENT_RANK() calculates the relative rank of a row within a result set as a percentage.

    sql

    SELECT column1, PERCENT_RANK() OVER (PARTITION BY column2 ORDER BY column3) AS percent_rank FROM table_name;
    

64. How do you perform Hierarchical Queries in SQL?

    Answer: Use WITH RECURSIVE CTEs to perform hierarchical or recursive queries.

    sql

    WITH RECURSIVE hierarchy AS (
        SELECT column1, column2 FROM table_name WHERE column1 = root_value
        UNION ALL
        SELECT t.column1, t.column2 FROM table_name t
        JOIN hierarchy h ON t.parent_column = h.column1
    )
    SELECT * FROM hierarchy;

Transactional Control and Error Handling

66. How do you handle Deadlocks in SQL?

    Answer: Deadlocks occur when two transactions wait for each other to release resources. Handle them by:

    • Implementing a retry mechanism.

    • Optimizing transactions to reduce lock duration.

    • Using SET DEADLOCK_PRIORITY to set the priority of transactions.

67. What is Error Handling in SQL and how do you implement it?

    Answer: Error handling captures and manages errors during SQL execution using constructs like TRY...CATCH.

    sql

    BEGIN TRY
        -- SQL statements
    END TRY
    BEGIN CATCH
        -- Error handling statements
    END CATCH;
    

68. Explain the concept of Savepoints in transactions.

    Answer: Savepoints create intermediate points within a transaction to which you can roll back without affecting the entire transaction.

    sql

    SAVEPOINT savepoint_name;
    -- SQL operations
    ROLLBACK TO savepoint_name;
    

69. How do you use Transactional Control Commands in SQL?

    Answer: Transactional control commands include BEGIN TRANSACTION, COMMIT, and ROLLBACK to manage transactions.

    sql

    BEGIN TRANSACTION;
    -- SQL operations
    COMMIT; -- or ROLLBACK;
    

Complex Data Analysis and Transformation

71. How do you create Derived Tables in SQL?

    Answer: Derived tables are temporary tables created within a SELECT statement using subqueries.

    sql

    SELECT * FROM (SELECT column1, column2 FROM table_name) AS derived_table;
    

72. What is a Pivot Table in SQL and how do you create one?

    Answer: Pivot tables summarize data by transforming rows into columns using aggregation functions.

    sql

    SELECT * FROM (
        SELECT column1, column2, column3 FROM table_name
    ) PIVOT (SUM(column3) FOR column2 IN ([val1], [val2], [val3]));
    

73. Explain ETL (Extract, Transform, Load) in the context of SQL.

    Answer: ETL processes extract data from sources, transform it to fit operational needs, and load it into a target database. It involves data cleaning, integration, and loading.

74. How do you perform String Manipulation in SQL?

    Answer: Use functions like CONCAT(), SUBSTRING(), REPLACE(), and LTRIM()/RTRIM().

    sql

    SELECT CONCAT(column1, ' ', column2) AS full_name FROM table_name;
    SELECT SUBSTRING(column1, 1, 5) AS substring FROM table_name;
    SELECT REPLACE(column1, 'old', 'new') AS replaced_string FROM table_name;

75. What is Data Wrangling and how is it done in SQL?

    Answer: Data wrangling is the process of cleaning, transforming, and preparing raw data for analysis. In SQL, it involves:

    • Removing duplicates:

    sql

    DELETE FROM table_name WHERE id NOT IN (SELECT MIN(id) FROM table_name GROUP BY column1, column2);
    

    • Handling missing values:

    sql

    UPDATE table_name SET column1 = 'default_value' WHERE column1 IS NULL;
    

    • Standardizing formats:

    sql

    UPDATE table_name SET column1 = UPPER(column1);

76. How do you perform Recursive Queries in SQL?

    Answer: Recursive queries are written using WITH RECURSIVE to retrieve hierarchical or nested data.

    sql

    WITH RECURSIVE subordinates AS (
        SELECT employee_id, manager_id FROM employees WHERE manager_id IS NULL
        UNION ALL
        SELECT e.employee_id, e.manager_id FROM employees e
        INNER JOIN subordinates s ON e.manager_id = s.employee_id
    )
    SELECT * FROM subordinates;
    

77. What are Cross Tab Queries and how do you create them in SQL?

    Answer: Cross tab queries, or pivot queries, summarize data by converting rows into columns.

    sql

    SELECT * FROM (
        SELECT column1, column2, column3 FROM table_name
    ) PIVOT (SUM(column3) FOR column2 IN ([val1], [val2], [val3]));
    

78. How do you implement Row-Level Security in SQL Server?

    Answer: Use security policies to restrict access to rows based on user attributes.

    sql

    CREATE SECURITY POLICY security_policy_name
    ADD FILTER PREDICATE user_has_access(column) ON table_name,
    ADD BLOCK PREDICATE user_has_access(column) ON table_name;
    

79. What is the MERGE statement and its use cases?

    Answer: MERGE allows performing INSERT, UPDATE, or DELETE operations based on a condition, often used in data warehousing.

    sql

    MERGE target_table USING source_table
    ON target_table.id = source_table.id
    WHEN MATCHED THEN UPDATE SET target_table.column1 = source_table.column1
    WHEN NOT MATCHED THEN INSERT (column1, column2) VALUES (source_table.column1, source_table.column2);

81. What is the difference between Batch Processing and Streaming Processing in SQL?

    Answer:

    • Batch Processing: Processes large volumes of data at once.

    • Streaming Processing: Processes data continuously as it arrives.

96. What are Temporal Tables in SQL Server?

    Answer: Temporal tables automatically track changes to data over time, providing historical data views.

    sql

    CREATE TABLE table_name (
        column1 INT,
        column2 VARCHAR(100),
        PERIOD FOR SYSTEM_TIME (start_time, end_time)
    ) WITH (SYSTEM_VERSIONING = ON);
    

97. Explain the Spatial Data types and their use cases.

    Answer: Spatial data types store and manipulate geographical and geometric data. Use cases include GIS (Geographic Information Systems), mapping, and location-based services.

    sql

    CREATE TABLE spatial_table (id INT, location GEOGRAPHY);
    

98. How do you handle Recursive Relationships in SQL databases?

    Answer: Use Recursive CTEs to query hierarchical data structures.

    sql

    WITH RECURSIVE hierarchy AS (
        SELECT id, parent_id FROM table_name WHERE parent_id IS NULL
        UNION ALL
        SELECT t.id, t.parent_id FROM table_name t
        JOIN hierarchy h ON t.parent_id = h.id
    )
    SELECT * FROM hierarchy;
    

99. What is the purpose of Window Aggregates in SQL?

    Answer: Window aggregates perform calculations across a set of table rows related to the current row, such as SUM(), AVG(), and COUNT().

    sql

    SELECT column1, SUM(column2) OVER (PARTITION BY column3 ORDER BY column4) AS running_total FROM table_name;
    

100. How do you perform Pattern Matching using SQL?

Answer: Use the LIKE operator and regular expressions for pattern matching. sql SELECT * FROM table_name WHERE column1 LIKE 'pattern%';

These additional SQL questions delve deeper into advanced topics, data integrity, performance optimization, and specialized operations, providing you with a comprehensive foundation for your interviews in data-related roles.

104. What are Common Table Expressions (CTEs) and their benefits?

Answer: CTEs are temporary result sets that improve query readability and can be referenced within a larger query. sql WITH CTE AS ( SELECT column1, column2 FROM table_name ) SELECT * FROM CTE WHERE column2 > 10;

105. How do you optimize a Subquery?

Answer: Convert subqueries to joins, use indexes, and ensure subqueries are correlated efficiently. sql SELECT * FROM table_name t1 WHERE EXISTS (SELECT 1 FROM table_name2 t2 WHERE t1.id = t2.id);

SQL Security and Data Protection

111. How do you implement Row-Level Security (RLS) in SQL?

Answer: Use security policies to restrict access to rows based on user attributes. sql CREATE SECURITY POLICY security_policy_name ADD FILTER PREDICATE user_has_access(column) ON table_name;

112. What is SQL Injection and how to prevent it?

Answer: SQL injection is a security vulnerability allowing execution of malicious SQL. Prevent it using parameterized queries and input validation. sql -- Parameterized query example SELECT * FROM table_name WHERE column_name = @parameter;

113. Describe the concept of Role-Based Access Control (RBAC).

Answer: RBAC assigns permissions to roles rather than individual users, simplifying access management. sql CREATE ROLE role_name; GRANT SELECT, INSERT ON table_name TO role_name;

119. How do you handle Temporal Data in SQL?

Answer: Use temporal tables to track historical data changes. sql CREATE TABLE table_name ( column1 INT, column2 VARCHAR(100), PERIOD FOR SYSTEM_TIME (start_time, end_time) ) WITH (SYSTEM_VERSIONING = ON);

120. What are User-Defined Functions (UDFs) and their types?

Answer: UDFs are custom functions defined by users to perform specific tasks. Types include scalar, table-valued, and inline table-valued functions. sql CREATE FUNCTION function_name (@parameter INT) RETURNS INT AS BEGIN RETURN (@parameter * 2); END;

$$\mathrm{<br>}$$