Start-Tech Academy

September 4, 2024

SQL Interview Questions

Master the SQL questions that top companies ask. This guide highlights key interview questions and answers to help you showcase your expertise and land your next data-driven role.

Top SQL Interview Questions

In today’s data-driven world, SQL (Structured Query Language) is a vital skill across many industries. Whether you’re pursuing a career in data analysis, software development, or database management, strong SQL knowledge is often a must-have. Employers use SQL interview questions to evaluate your ability to work with databases, retrieve and manipulate data, and solve complex problems.

These questions are more than just routine—they’re critical in proving your expertise and setting yourself apart from other candidates. This guide covers some of the most commonly asked SQL interview questions, giving you the insight and preparation you need to excel in any SQL-focused role.

1. Question: Explain the difference between RANK(), DENSE_RANK(), and ROW_NUMBER() in SQL. Provide an example where each function is used.

Answer:

  • RANK(): Assigns a rank to each row within a partition of a result set. The rank is incremented based on the value of the column, but if there are ties, the next rank will be skipped.
  • DENSE_RANK(): Similar to RANK() but does not skip any ranks in the event of a tie.
  • ROW_NUMBER(): Assigns a unique sequential integer to rows within a partition, without considering any ties.

Example:

SELECT 

    column_name,

    RANK() OVER (ORDER BY column_name) AS rank,

    DENSE_RANK() OVER (ORDER BY column_name) AS dense_rank,

    ROW_NUMBER() OVER (ORDER BY column_name) AS row_number

FROM 

    table_name;

2. Question: How can you find the nth highest salary in a table without using TOP or LIMIT?

Answer: You can use a correlated subquery to find the nth highest salary:

SELECT DISTINCT salary 

FROM employee e1 

WHERE N-1 = (

    SELECT COUNT(DISTINCT salary) 

    FROM employee e2 

    WHERE e2.salary > e1.salary

);

Replace N with the desired rank.

3. Question: What is a CTE (Common Table Expression), and how does it differ from a subquery? Provide an example.

Answer: A CTE is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. Unlike subqueries, CTEs can be self-referencing and can be used multiple times in the query.

Example:

WITH Sales_CTE AS (

    SELECT SalesPersonID, SUM(TotalDue) AS TotalSales

    FROM SalesOrderHeader

    GROUP BY SalesPersonID

)

SELECT SalesPersonID

FROM Sales_CTE

WHERE TotalSales > 100000;

4. Question: How would you optimize a query that involves joining multiple large tables?

Answer:

  • Indexes: Ensure that relevant columns used in joins and filters have indexes.
  • Joins: Use the correct join type (e.g., INNER JOIN, LEFT JOIN) and consider the order of joins.
  • Query Execution Plan: Analyze the execution plan to identify bottlenecks.
  • Subqueries: Convert subqueries into joins where possible.
  • Partitioning: Use table partitioning for large datasets.
  • Temp Tables: Consider breaking the query into steps using temporary tables.

5. Question: How do you perform a recursive query in SQL?

Answer: Recursive queries are typically done using CTEs with the WITH RECURSIVE clause.

Example:

WITH RECURSIVE EmployeeHierarchy AS (

    SELECT EmployeeID, ManagerID, EmployeeName

    FROM Employees

    WHERE ManagerID IS NULL

    UNION ALL

    SELECT e.EmployeeID, e.ManagerID, e.EmployeeName

    FROM Employees e

    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID

)

SELECT * FROM EmployeeHierarchy;

This will return the hierarchy of employees and their managers.

6. Question: Explain the concept of indexing and its types in SQL. How do indexes affect performance?

Answer: Indexing is a data structure technique used to quickly locate and access data in a database. Indexes can be created using one or more columns of a table to improve the speed of data retrieval operations.

Types of Indexes:

  • Clustered Index: Sorts and stores the data rows of the table or view in order based on the key values. A table can have only one clustered index.
  • Non-Clustered Index: Contains a copy of part of the data from the table, ordered in the non-clustered index. A table can have multiple non-clustered indexes.
  • Unique Index: Ensures that all values in the index key are unique.
  • Composite Index: An index on multiple columns.

Effect on Performance:

  • Read Operations: Indexes significantly speed up SELECT queries by reducing the amount of data scanned.
  • Write Operations: Indexes can slow down INSERT, UPDATE, and DELETE operations as the index also needs to be updated.
  • Storage: Indexes consume additional storage.

7. Question: What is a PIVOT and UNPIVOT in SQL, and when would you use them?

Answer:

  • PIVOT: Used to rotate rows into columns.
  • UNPIVOT: Converts columns back into rows.

Example:

SELECT * FROM (

    SELECT ProductID, Year, SalesAmount

    FROM Sales

) AS SourceTable

PIVOT (

    SUM(SalesAmount) 

    FOR Year IN ([2021], [2022], [2023])

) AS PivotTable;

8. Question: What are Window Functions, and how are they different from aggregate functions?

Answer: Window Functions perform calculations across a set of table rows related to the current row. Unlike aggregate functions, which return a single result for a group of rows, window functions can return multiple rows.

Example:

SELECT 

    EmployeeID,

    Salary,

    AVG(Salary) OVER (PARTITION BY DepartmentID) AS AvgDeptSalary

FROM 

    Employees;

9. Question: How do you detect and resolve deadlocks in SQL?

Answer:

  • Detection: Deadlocks can be detected using SQL Server Profiler, Extended Events, or by enabling the trace flag 1222 to log deadlock information.
  • Resolution:
    • Code Optimization: Ensure that your transactions acquire locks in the same order.
    • Timeouts: Set a timeout for transactions to reduce the chance of deadlocks.
    • Use WITH (NOLOCK): For queries where data consistency is not critical.
    • Retry Logic: Implement retry logic in your application when a deadlock occurs.

10. Question: Explain the concept of database normalization. What are the different normal forms?

Answer: 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.

Normal Forms:

  • 1NF (First Normal Form): Ensures that each column contains atomic values and that each record is unique.
  • 2NF (Second Normal Form): Meets all the requirements of 1NF, and all non-key attributes are fully dependent on the primary key.
  • 3NF (Third Normal Form): Meets all the requirements of 2NF, and there is no transitive dependency (non-key attributes should not depend on other non-key attributes).
  • BCNF (Boyce-Codd Normal Form): A stricter version of 3NF where every determinant is a candidate key.
  • 4NF (Fourth Normal Form): Ensures no multi-valued dependencies other than a candidate key.
  • 5NF (Fifth Normal Form): Ensures no join dependencies.

11. Question: How would you write a query to identify and remove duplicate records from a table?

Answer: You can identify and remove duplicates using a CTE with ROW_NUMBER():

Example:

WITH CTE AS (

    SELECT 

        column1, 

        column2, 

        ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY (SELECT NULL)) AS row_num

    FROM 

        table_name

)

DELETE FROM CTE WHERE row_num > 1;

12. Question: What is the difference between HAVING and WHERE clauses? Can you use them together?

Answer:

  • WHERE: Filters rows before any groupings are made. It applies to individual rows.
  • HAVING: Filters groups after the GROUP BY operation has been performed. It is used to filter aggregated results.

Example of using both:

SELECT 

    DepartmentID, 

    COUNT(EmployeeID) AS EmployeeCount

FROM 

    Employees

WHERE 

    Salary > 50000

GROUP BY 

    DepartmentID

HAVING 

    COUNT(EmployeeID) > 10;

In this query, WHERE filters employees with a salary greater than 50,000, and HAVING filters departments with more than 10 such employees.

13. Question: Describe the concept of indexing strategy. How would you design an index for a large table with frequent reads and infrequent writes?

Answer: An effective indexing strategy balances performance improvements in read operations with the overhead on write operations.

For a large table with frequent reads:

  • Clustered Index: On the primary key or a frequently queried column.
  • Non-Clustered Indexes: On columns used in WHERE, JOIN, and ORDER BY clauses.
  • Covering Indexes: Include all columns needed by a query to prevent additional lookups.
  • Partitioning: For very large tables, partitioning the data can help by reducing the amount of data scanned.

For infrequent writes:

  • Minimal Indexes: Only create necessary indexes to avoid overhead on write operations.
  • Batch Updates: If possible, perform batch updates to minimize the impact of index updates.

14. Question: How do you handle large datasets in SQL when the query performance is critical?

Answer:

  • Partitioning: Split large tables into smaller, more manageable pieces.
  • Indexes: Ensure proper indexing on critical columns.
  • Query Optimization: Optimize the SQL queries, remove unnecessary subqueries, and use joins efficiently.
  • Materialized Views: Use materialized views for complex, frequently used queries.
  • Archiving: Archive historical data that is not frequently accessed.
  • Caching: Use caching mechanisms for frequently accessed data.

15. Question: What is a CROSS APPLY and OUTER APPLY in SQL? How are they different from a JOIN?

Answer:

  • CROSS APPLY: Applies a table-valued function to each row of the outer query’s result set. It acts like an INNER JOIN, where only rows with matching results in the applied function are returned.
  • OUTER APPLY: Similar to CROSS APPLY but returns all rows from the outer query, with NULLs for columns of the table-valued function where no matches exist. It acts like a LEFT JOIN.

Example:

SELECT 

    e.EmployeeID, 

    d.DepartmentName

FROM 

    Employees e

CROSS APPLY 

    (SELECT DepartmentName FROM Departments d WHERE e.DepartmentID = d.DepartmentID) d;

16. Question: Explain the concept of transactions in SQL. What are ACID properties?

Answer: A transaction is a sequence of operations performed as a single logical unit of work. A transaction must be either fully completed or fully rolled back.

ACID Properties:

  • Atomicity: Ensures that all operations within a transaction are completed successfully. If not, the transaction is aborted.
  • Consistency: Ensures that a transaction brings the database from one valid state to another.
  • Isolation: Ensures that the operations of a transaction are isolated from other transactions.
  • Durability: Ensures that the result of a committed transaction is permanently stored in the database, even in the event of a system failure.

17. Question: What is the difference between DELETE, TRUNCATE, and DROP commands in SQL?

Answer:

  • DELETE: Removes rows from a table based on a WHERE clause. It can be rolled back and triggers are fired.
  • TRUNCATE: Removes all rows from a table but does not log individual row deletions. It cannot be rolled back if not within a transaction, and triggers do not fire.
  • DROP: Removes the table and its structure from the database entirely. It cannot be rolled back and all data, indexes, and triggers associated with the table are removed.

18. Question: How can you perform a full-text search in SQL?

Answer: Full-text search is used to search for text within text-based columns. This can be implemented using the FULLTEXT index in SQL Server or CONTAINS, FREETEXT functions.

Example:

SELECT *

FROM Products

WHERE CONTAINS(ProductDescription, ‘SQL’);

You must first create a full-text index on the column being searched.

19. Question: What is a materialized view, and how does it differ from a regular view?

Answer:

  • Materialized View: Stores the result of a query physically on the disk, which can be periodically refreshed. It allows for faster query performance because the data is precomputed.
  • Regular View: A virtual table that does not store data physically. It is a stored query that pulls data from the underlying tables each time it is accessed.

Example of creating a Materialized View:

CREATE MATERIALIZED VIEW mv_employee_sales AS

SELECT 

    e.EmployeeID, 

    SUM(s.SalesAmount) AS TotalSales

FROM 

    Employees e

JOIN 

    Sales s ON e.EmployeeID = s.EmployeeID

GROUP BY 

    e.EmployeeID;

20. Question: What is a UNION and how does it differ from UNION ALL?

Answer:

  • UNION: Combines the result sets of two or more SELECT statements and removes duplicate rows.
  • UNION ALL: Combines the result sets of two or more SELECT statements and includes all duplicate rows.

Example:

SELECT FirstName FROM Employees

UNION

SELECT FirstName FROM Customers;

This returns a list of unique first names from both Employees and Customers tables.

21. Question: Explain the concept of subqueries in SQL. Provide an example of a correlated subquery.

Answer: A subquery is a query nested inside another query. A correlated subquery is a subquery that references columns from the outer query, making it execute once for each row of the outer query.

Example:

SELECT e.EmployeeID, e.FirstName

FROM Employees e

WHERE e.Salary > (SELECT AVG(Salary) FROM Employees WHERE DepartmentID = e.DepartmentID);

This query returns employees whose salary is greater than the average salary of their respective department.

22. Question: What is a CASE statement in SQL, and how is it used?

Answer: The CASE statement allows conditional logic in SQL queries, similar to an IF-THEN-ELSE structure.

Example:

SELECT 

    EmployeeID,

    Salary,

    CASE 

        WHEN Salary > 50000 THEN ‘High’

        WHEN Salary BETWEEN 30000 AND 50000 THEN ‘Medium’

        ELSE ‘Low’

    END AS SalaryCategory

FROM 

    Employees;

This query categorizes employees based on their salary.

23. Question: What is a VIEW in SQL? How is it different from a table?

Answer: A VIEW is a virtual table created by a SELECT query that can be queried like a regular table. Unlike a table, a view does not store data itself; it retrieves data from underlying tables.

Example:

CREATE VIEW HighSalaryEmployees AS

SELECT EmployeeID, FirstName, LastName, Salary

FROM Employees

WHERE Salary > 50000;

24. Question: How do you implement pagination in SQL?

Answer: Pagination can be implemented using the OFFSET and FETCH (in SQL Server) or LIMIT and OFFSET (in MySQL) clauses.

Example:

SELECT * FROM Employees

ORDER BY EmployeeID

OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

This retrieves the second page of results, assuming 10 results per page.

In MySQL:

SELECT * FROM Employees

ORDER BY EmployeeID

LIMIT 10 OFFSET 10;

25. Question: Explain the concept of a FOREIGN KEY. How does it enforce referential integrity?

Answer: A FOREIGN KEY is a column (or set of columns) in one table that uniquely identifies a row of another table. It creates a link between the two tables and ensures that the value in the foreign key column corresponds to a valid primary key in the related table.

Example:

CREATE TABLE Orders (

    OrderID INT PRIMARY KEY,

    CustomerID INT,

    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)

);

This ensures that every CustomerID in Orders exists in the Customers table.

26. Question: How do you update data in one table based on data in another table?

Answer: You can update data using a JOIN in an UPDATE statement.

Example:

UPDATE e

SET e.Salary = e.Salary * 1.1

FROM Employees e

JOIN Departments d ON e.DepartmentID = d.DepartmentID

WHERE d.DepartmentName = ‘Sales’;

This query increases the salary of employees in the Sales department by 10%.

27. Question: What is the COALESCE() function in SQL? How does it differ from ISNULL()?

Answer: The COALESCE() function returns the first non-NULL value in a list of expressions. It can accept multiple arguments.

Example:

SELECT COALESCE(NULL, NULL, ‘Default Value’, ‘Another Value’) AS Result;

This will return ‘Default Value’.

Difference from ISNULL():

  • ISNULL(): Accepts only two arguments and returns the second if the first is NULL.
  • COALESCE(): Accepts multiple arguments and returns the first non-NULL value.

28. Question: How would you find all employees who have birthdays in the current month?

Answer: You can use the MONTH() function to extract the month from the DateOfBirth column and compare it with the current month.

Example:

SELECT EmployeeID, FirstName, LastName, DateOfBirth

FROM Employees

WHERE MONTH(DateOfBirth) = MONTH(GETDATE());

This query retrieves employees whose birthdays are in the current month.

29. Question: What are aggregate functions in SQL? List some common aggregate functions and provide an example.

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

Common Aggregate Functions:

  • SUM(): Returns the sum of a numeric column.
  • AVG(): Returns the average value.
  • COUNT(): Returns the number of rows.
  • MAX(): Returns the maximum value.
  • MIN(): Returns the minimum value.

Example:

SELECT DepartmentID, AVG(Salary) AS AverageSalary

FROM Employees

GROUP BY DepartmentID;

This query returns the average salary for each department.

30. Question: How can you find rows in a table that do not have a matching row in another table?

Answer: You can use a LEFT JOIN with a WHERE clause to filter out the rows where the join condition did not match.

Example:

SELECT e.EmployeeID, e.FirstName, e.LastName

FROM Employees e

LEFT JOIN Orders o ON e.EmployeeID = o.EmployeeID

WHERE o.OrderID IS NULL;

This retrieves all employees who have not placed any orders.

31. Question: Explain the difference between CHAR and VARCHAR data types in SQL.

Answer:

  • CHAR: A fixed-length data type. If the data is shorter than the defined length, it is padded with spaces. It is efficient for storing data of known, consistent length.
  • VARCHAR: A variable-length data type. It only uses as much space as needed for the data, plus an additional byte to store the length. It is more flexible for storing variable-length data.

Example:

CREATE TABLE TestTable (

    FixedLengthColumn CHAR(10),

    VariableLengthColumn VARCHAR(10)

);

32. Question: How would you use a CASE statement to perform conditional aggregation in SQL?

Answer: You can use a CASE statement within an aggregate function like SUM() to conditionally aggregate data.

Example:

SELECT 

    DepartmentID,

    SUM(CASE WHEN Gender = ‘Male’ THEN Salary ELSE 0 END) AS MaleSalary,

    SUM(CASE WHEN Gender = ‘Female’ THEN Salary ELSE 0 END) AS FemaleSalary

FROM 

    Employees

GROUP BY 

    DepartmentID;

This query calculates the total salary for male and female employees separately, grouped by department.

33. Question: What is a SEQUENCE in SQL, and how is it different from IDENTITY?

Answer: A SEQUENCE is a user-defined object that generates a sequence of numeric values according to a specified specification. It is independent of any table and can be used across multiple tables.

Example:

CREATE SEQUENCE EmployeeSeq

START WITH 1

INCREMENT BY 1;

  • IDENTITY: A property attached to a specific column in a table that automatically generates unique values when a new row is inserted. It is specific to a single table.

Example:

CREATE TABLE Employees (

    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50)

);

34. Question: How can you retrieve a list of all tables in a database along with their row counts?

Answer: You can use a combination of INFORMATION_SCHEMA and a dynamic SQL query to retrieve this information.

Example:

SELECT 

    t.TABLE_NAME,

    p.[Rows]

FROM 

    INFORMATION_SCHEMA.TABLES t

JOIN 

    sys.partitions p ON t.TABLE_NAME = OBJECT_NAME(p.OBJECT_ID)

WHERE 

    t.TABLE_TYPE = ‘BASE TABLE’ AND p.index_id IN (0, 1);

This query retrieves the names and row counts of all tables in the current database.

35. Question: What is a TRIGGER in SQL? Provide an example of how to create a trigger.

Answer: A TRIGGER is a set of SQL statements that automatically executes in response to certain events on a particular table or view, such as INSERT, UPDATE, or DELETE.

Example:

CREATE TRIGGER trg_AfterInsert

ON Employees

AFTER INSERT

AS

BEGIN

    INSERT INTO AuditLog (EmployeeID, ChangeDate, ChangeType)

    SELECT EmployeeID, GETDATE(), ‘INSERT’ FROM inserted;

END;

This trigger logs every insert operation on the Employees table into an AuditLog table.

36. Question: What are the benefits of using INDEXES in SQL? Can they have any drawbacks?

Answer: Benefits:

  • Faster Query Performance: Indexes speed up the retrieval of rows by creating a lookup table that SQL Server can use to quickly find data.
  • Efficient Sorting: Indexes can help SQL Server to efficiently sort query results.
  • Uniqueness Enforcement: Unique indexes enforce the uniqueness of values in a column or combination of columns.

Drawbacks:

  • Slower Write Performance: Indexes need to be updated every time data is inserted, updated, or deleted, which can slow down write operations.
  • Storage Space: Indexes consume additional storage space.
  • Complexity: Improper indexing can lead to performance degradation, making it crucial to design and maintain indexes carefully.

37. Question: What is a stored procedure, and how does it differ from a function in SQL?

Answer:

  • Stored Procedure: A precompiled collection of SQL statements that can be executed as a single unit. Stored procedures can perform actions such as modifying data and can return zero, one, or multiple values.
  • Function: A SQL function returns a single value or a table and is often used for calculations or data retrieval. Unlike stored procedures, functions cannot modify the database state.

Differences:

  • Return Type: Functions must return a value, while stored procedures do not have to return anything.
  • Side Effects: Functions cannot change the database state; stored procedures can.
  • Usage: Functions are typically used within SQL statements, whereas stored procedures are executed independently.

38. Question: What are the advantages of using views in SQL?

Answer:

  • Simplified Querying: Views can encapsulate complex queries, making it easier for users to retrieve data without writing complex SQL.
  • Data Security: Views can restrict access to specific data by allowing users to query only the view instead of the underlying tables.
  • Data Abstraction: Views provide a layer of abstraction, allowing changes to the underlying tables without affecting the view’s query structure.
  • Reusability: Views can be reused in multiple queries, reducing redundancy and ensuring consistent query logic.

39. Question: What is a cursor and what are its types?

Answer:

A cursor is a database object used to retrieve, manipulate, and navigate through a result set row-by-row. Types of cursors include:

  • Static: A static cursor makes a temporary copy of the data to be retrieved and uses the copy instead of the original table. Changes made to the original data won’t reflect in the cursor.
  • Dynamic: Reflects all changes made in the database.
  • Forward-only: Moves forward through the result set one row at a time.
  • Keyset-driven: A keyset-driven cursor uses the primary key to fetch rows from the table, reflecting changes made to non-key values.

40. Question: What is the difference between a cursor and a set-based operation?

Answer:

A cursor is a control structure that allows you to process each row of a query result set one at a time using FETCH NEXT or FETCH FIRST, whereas a set-based operation processes all rows at once using SELECT or UPDATE statements.

41. Question: What is the difference between an implicit cursor and an explicit cursor?

Answer:

An implicit cursor is automatically created by SQL Server when executing a query with FOR UPDATE clause, whereas an explicit cursor is manually created using DECLARE CURSOR statement to control the retrieval of rows from a query result set.

42. Question: What is an execution plan and how do you generate it?

Answer: An execution plan shows how SQL Server will execute a query, detailing the steps and operations. It can be generated using the EXPLAIN keyword or by using query tools like SQL Server Management Studio.

43. Question: How do you implement data masking in SQL Server?

Answer: Data masking is an operation of substituting the actual data values with either random ones or fictional ones using TDE or other tools such as Microsoft’s Data Masking Framework.

44. Question: How do you optimize the performance of a query that uses subqueries?

Answer: To optimize the query which uses subquery it is advisable to rewrite it using join, correlated sub query or creating derived tables so that the number of subqueries actually to be run by the system is reduced, which in turn improves query response time. For example, instead of using the subqueries you can use the EXISTS or IN operators, or you can decide to rewrite the query in such a way that it scans only one table.

45. Question: How do you handle large result sets in SQL?

Answer: Another approach to dealing with large result sets includes pagination where you define your LIMIT or TOP clauses, he/she used selection to limit the result sets to specific columns or a range or results, or he/she used cursor or result-set cursor to stream results.

46. Question: Which command is used to find out the SQL Server version?

Answer: The following command is used to identify the version of SQL Server:

  •  SELECT SERVERPROPERTY (‘productversion’)

47. Question: How do you use the GROUP BY clause in SQL?

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

SELECT column1, MAX(*)

FROM table1

GROUP BY column1;

48. Question: Explain the use of the ORDER BY clause.

Answer: The ORDER BY clause is used to sort the result set in either ascending (ASC) or descending (DESC) order.

SELECT column1, column2

FROM table1

ORDER BY column1 ASC, column2 DESC;

49. Question: What is a Function in SQL?

Answer: A function is a reusable set of SQL statements that perform a specific task and return a value. Functions can take input parameters and are often used to encapsulate logic that can be invoked with a single call.

50. Question: Are NULL values the same as that of zero or a blank space? 

Answer:  A NULL value is not at all the same as that of zero or a blank space. NULL values represent a value that is unavailable, unknown, assigned or not applicable whereas a zero is a number and a blank space is a character.

51. Question:What is the difference between a correlated and a non-correlated subquery?

Answer: 

  • Correlated subquery – In correlated subquery, the inner query is dependent on the outer query. Outer query needs to be executed before inner query

SELECT a.column1

FROM table1 a

WHERE a.column2 = (SELECT MAX(b.column2)

                   FROM table2 b

                   WHERE a.common_column = b.common_column);

  • Non-Correlated subquery – In non-correlated query inner query does not depend on the outer query. They both can run separately.

SELECT column1

FROM table1

WHERE column2 = (SELECT column2 FROM another_table WHERE condition);

52. Question:What is a constraint? Why do we use it?

Answer: :A set of conditions defining the type of data that can be input into each column of a table. Constraints ensure data integrity in a table and block undesired actions.

53. Question: State some Constraints used in SQL.

Answer: 

  • UNIQUE Constraint: Ensures that all values in a column are different.
  • NOT NULL Constraint: Ensures that a column cannot have a NULL value.
  • PRIMARY KEY Constraint: Uniquely identifies each record in a table. A primary key column cannot contain NULL values and must contain unique values.
  • FOREIGN KEY Constraint: Ensures the referential integrity of the data in one table to match values in another table.
  • CHECK Constraint: Ensures that all values in a column satisfy a specific condition.
  • DEFAULT Constraint: Provides a default value for a column when none is specified.
  • INDEX Constraint: Used to create and retrieve data from the database very quickly. (Note: INDEX is not a direct constraint, but it is often included in discussions about performance constraints.)

54. Question: How do you optimize a slow-running SQL query?

Answer: To improve the efficiency of a slow running query we can perform the following process:

  • reviewing the query
  • analyzing its execution plan, 
  • restructuring the query, involving indexes for the tables or the fields used in WHERE, JOIN, ORDER BY clauses, 
  • changing the database settings, 
  • using views or stored procedures, 
  • limiting the amount of data returned, etc.

55. Question: What is the main difference between SQL and PL/SQL?

Answer: SQL is used to issue a single query or single insert/update/delete statement while PL/SQL is Oracle’s procedural SQL and can be used to write an entire program with loops, variables and so on to perform a number of operations at once such as multiple selects/insert/updates/deletes.

56. Question: What are triggers in SQL?

Answer: Triggers are stored procedures that are limited to performing actions whenever there is a specific event on a particular table or view; it can be INSERT, UPDATE, or DELETE. Triggers can also help to implement business rules, validate data, and even keep the audit trails.

CREATE TRIGGER trgAfterInsert

ON Employees

AFTER INSERT

AS

BEGIN

USE EmployeeAudit INSERT INTO EmployeeAudit (EmployeeID, AuditAction, AuditTime)

SELECT EmployeeID, ‘INSERT’, GETDATE()

FROM inserted;

END;

57. Question: How many TRIGGERS are allowed in the MySQL table?

Answer:  6 triggers are allowed in the MySQL table:

  • BEFORE INSERT
  • AFTER INSERT
  • BEFORE UPDATE
  • AFTER UPDATE
  • BEFORE DELETE 
  • AFTER DELETE

58. Question: What do you mean by data integrity?

Answer: 

Data integrity is like a loyal protector, guaranteeing data accuracy and consistency. It is there from the creation to deletion, like a lifelong companion. Integrity constraints are its rules that make sure that data is following the guidelines. It is your faithful friend, guaranteeing your data is correct and dependable.

59. Question: Where are usernames and passwords stored in SQL Server?

Answer: 

Usernames and Passwords in the SQL Server, are stored in the main database in the sysxlogins table.

60. Question:  Name the encryption mechanisms in the SQL server.

Answer: The encryption mechanism used in SQL servers are:

  • Transact-SQL functions – Individual items can be encrypted as they are inserted or updated using Transact-SQL functions.
  • Asymmetric keys – It is made up of a private key and the corresponding public key. Each key can decrypt data encrypted by the other.
  • Symmetric keys – It is used for both encryption and decryption.
  • Certificates – Also known as a public key certificate, it binds the value of a public key to the identity of the person, device, or service that holds the corresponding private key.
  • Transparent Data Encryption – It is a special case of encryption using a symmetric key that encrypts an entire database using that symmetric key.
Start-Tech Academy (JATIN)