Top SQL Interview Questions & Answer 2023 For Freshers & Experienced

Top SQL Interview Questions FAQ in 2023

1. What is SQL?

   • SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. It allows users to store, retrieve, and modify data stored in a database.

2. What are the different types of SQL statements?

   • SQL statements can be categorized into four main types:
     - Data Definition Language (DDL): Used to define and manage the structure of database objects (e.g., CREATE, ALTER, DROP).
     - Data Manipulation Language (DML): Used to manipulate data within the database (e.g., SELECT, INSERT, UPDATE, DELETE).
     - Data Control Language (DCL): Used to control access and permissions to the database (e.g., GRANT, REVOKE).
     - Transaction Control Language (TCL): Used to manage transactions in the database (e.g., COMMIT, ROLLBACK).

3. What is the difference between SQL and MySQL?

   • SQL is a language used to manage relational databases, while MySQL is an open-source Relational Database Management System (RDBMS) that uses SQL as its language. In other words, SQL is the language, and MySQL is the software that implements and supports that language.

4. Explain the difference between a database and a table.

   • A database is a collection of related data that is organized and structured. It acts as a container for tables, views, indexes, and other database objects. A table, on the other hand, is a structured representation of data within a database. It consists of rows and columns, where each row represents a record, and each column represents a specific attribute or field of that record.

5. What is a primary key?

   • A primary key is a column or a set of columns in a table that uniquely identifies each row in that table. It enforces the uniqueness and integrity of the data. A primary key cannot contain null values, and there can be only one primary key per table.

6. What is a foreign key?

   • A foreign key is a column or a set of columns in a table that refers to the primary key of another table. It establishes a relationship between two tables by enforcing referential integrity. A foreign key ensures that the values in the referencing column(s) match the values in the referenced primary key column(s).

7. What is normalization?

   • Normalization is the process of organizing and structuring a database design to eliminate redundancy and dependency issues. It involves dividing larger tables into smaller, well-structured tables and defining relationships between them. Normalization helps to improve data integrity, reduce storage space, and optimize database performance.

8. What are the different levels of normalization?

   • The different levels of normalization are:
     - First Normal Form (1NF): Eliminates duplicate rows and ensures atomicity of values in a table.
     -Second Normal Form (2NF): Eliminates partial dependencies by moving non-key attributes to separate tables.
     -Third Normal Form (3NF): Eliminates transitive dependencies by moving non-key attributes to separate tables.
     -Fourth Normal Form (4NF): Eliminates multivalued dependencies by moving multi-valued attributes to separate tables.
     -Fifth Normal Form (5NF) or Boyce-Codd Normal Form (BCNF): Further eliminates join dependencies and ensures all dependencies are based on the candidate keys.

9. What is an index in SQL?

   • An index is a database object used to speed up the retrieval of data from a table. It is created on one or more columns of a table and stores a sorted copy of the data values along with a pointer to the actual data. Indexes improve query performance by allowing the database engine to quickly locate the required data.

10. What are the different types of joins in SQL?

    • The different types of joins in SQL are:
      - INNER JOIN: Returns only the matching rows from both the tables involved in the join.
      - LEFT JOIN (or LEFT OUTER JOIN): Returns all the rows from the left table and the matching rows from the right table.
      - RIGHT JOIN (or RIGHT OUTER JOIN): Returns all the rows from the right table and the matching rows from the left table.
      - FULL JOIN (or FULL OUTER JOIN): Returns all the rows from both the tables, including the non-matching rows.
      - CROSS JOIN: Returns the Cartesian product of rows from both the tables.
      - SELF JOIN: Joins a table to itself, treating it as two separate tables with aliases.
      

11. Explain the difference between INNER JOIN and OUTER JOIN.

    • INNER JOIN returns only the matching rows from both the tables based on the join condition. It excludes the non-matching rows from the result set. OUTER JOIN (e.g., LEFT JOIN, RIGHT JOIN, FULL JOIN) includes the matching rows from both the tables and also includes the non-matching rows from one table, based on the join condition.

12. What is a subquery in SQL?

    • A subquery, also known as a nested query or inner query, is a query nested inside another query. It is used to retrieve data from one or more tables and provide the result to the outer query. The result of the subquery is used as a condition or an expression in the outer query.

13. What is a view in SQL?

    • A view is a virtual table derived from one or more tables or views in a database. It behaves like a table, but it does not store any data on its own. Views are used to simplify complex queries, restrict access to certain columns, or provide a summarized or aggregated view of the data.

14. What is a stored procedure?

    • A stored procedure is a named and pre-compiled set of SQL statements that are stored in the database. It allows you to group multiple SQL statements into a single unit and execute them as a whole. Stored procedures can be invoked with input parameters, and they can also return output parameters or result sets.

15. What is a trigger in SQL?

    • A trigger is a special type of stored procedure that is automatically executed in response to specific events, such as an INSERT, UPDATE, or DELETE operation on a table. Triggers are used to enforce data integrity, perform logging, or automate certain database actions.

16. What is ACID in the context of databases?

    • ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability. It defines the properties that ensure reliable and transactional behavior in a database system. Atomicity: Ensures that a transaction is treated as a single unit of work. It either executes completely or is rolled back if any part of it fails. Consistency: Ensures that a transaction brings the database from one valid state to another. It enforces integrity constraints and rules defined on the data. Isolation: Ensures that each transaction is executed in isolation from other concurrent transactions. It prevents interference and maintains data integrity.

      Durability: Ensures that once a transaction is committed, its changes are permanent and survive any subsequent failures.

17. Explain the difference between UNION and UNION ALL.

    • UNION is used to combine the result sets of two or more SELECT statements into a single result set. It removes duplicate rows from the result set. UNION ALL also combines the result sets of two or more SELECT statements into a single result set but does not remove duplicate rows. It simply concatenates all the rows.

18. What is a self-join?

    • A self-join is a join operation where a table is joined with itself. It is useful when you want to combine rows from the same table based on a related column or condition.

19. What is the difference between CHAR and VARCHAR data types?

    • CHAR is a fixed-length character data type that stores a specific number of characters, padded with spaces if necessary. It takes up the same amount of storage space regardless of the actual data length. VARCHAR is a variable-length character data type that stores a varying number of characters, up to a maximum length specified. It takes up only the necessary amount of storage space based on the actual data length.

20. What is the difference between TRUNCATE and DELETE statements?

    • TRUNCATE is a DDL statement used to remove all the data from a table, but it keeps the structure of the table intact. It is faster and uses fewer system resources than the DELETE statement. TRUNCATE cannot be rolled back. DELETE is a DML statement used to remove specific rows from a table based on a condition. It also removes the data, but it can be rolled back if executed within a transaction.

21. How can you retrieve the top 'n' rows from a table?

    • You can retrieve the top 'n' rows from a table by using the TOP clause in SQL Server, the LIMIT clause in MySQL and PostgreSQL, or the FETCH FIRST clause in Oracle. For example: SQL Server: SELECT TOP n * FROM table_name; MySQL, PostgreSQL: SELECT * FROM table_name LIMIT n; Oracle: SELECT * FROM table_name FETCH FIRST n ROWS ONLY;

22. What is the difference between a clustered and non-clustered index?

    • A clustered index determines the physical order of data rows in a table. There can be only one clustered index per table, and it defines the storage order of the table. A non-clustered index is a separate structure that contains a copy of the indexed columns and a pointer to the actual data rows. Multiple non-clustered indexes can be created on a table.

23. What is the purpose of the GROUP BY clause in SQL?

    • The GROUP BY clause is used to group rows based on one or more columns in a table. It is often used with aggregate functions (e.g., COUNT, SUM, AVG) to perform calculations on each group of rows.

24. What is the HAVING clause in SQL?

    • The HAVING clause is used to filter the result set of a GROUP BY query based on a condition. It is similar to the WHERE clause but operates on the grouped results rather than individual rows. Explain the difference between a candidate key, a primary key, and a super key. A candidate key is a column or a set of columns in a table that can uniquely identify each row. It can be considered for the primary key selection. A primary key is a candidate key chosen to uniquely identify each row in a table. It enforces the uniqueness and integrity of the data. A super key is a set of one or more columns that can uniquely identify each row in a table. It may contain extraneous attributes and is not necessarily minimal.

25. What is the difference between a correlated and a non-correlated subquery?

    • A non-correlated subquery is an independent subquery that can be executed on its own and does not depend on the outer query. It can be executed once and the result can be used for the entire query.

      A correlated subquery is a subquery that depends on the outer query for its execution. It is executed for each row of the outer query and its result is dependent on the current row being evaluated.

26. What is the purpose of the COALESCE function in SQL?

    • The COALESCE function is used to return the first non-null expression from a list of expressions. It allows you to provide a default value when a column or expression is null.

27. What is the difference between a DDL and a DML statement?

    • DDL (Data Definition Language) statements are used to define and manage the structure of database objects, such as tables, indexes, and views. Examples include CREATE, ALTER, and DROP statements.

      DML (Data Manipulation Language) statements are used to manipulate and retrieve data within the database. Examples include SELECT, INSERT, UPDATE, and DELETE statements.

28. What is the purpose of the COMMIT and ROLLBACK statements?

    • The COMMIT statement is used to permanently save the changes made within a transaction to the database.

      The ROLLBACK statement is used to undo the changes made within a transaction and restore the database to its previous state.

29. What is the difference between a transaction and a session?

    • A transaction is a logical unit of work performed on a database that includes one or more database operations. It ensures that all the operations are executed as a single, atomic unit. A session is a connection established between a user and a database system. It represents the period during which a user interacts with the database. A session can include multiple transactions.

30. Explain the difference between the CHARINDEX and PATINDEX functions.

    • CHARINDEX is a function used to find the starting position of a specified expression within a string. It returns the position as an integer value. PATINDEX is a function used to find the starting position of a specified pattern within a string. It returns the position as an integer value. The pattern can include wildcard characters.

31. What is the purpose of the PIVOT and UNPIVOT operators?

    • The PIVOT operator is used to rotate rows into columns, creating a cross-tabulation or summary report. It transforms unique values from a column into multiple columns in the output. The UNPIVOT operator is used to convert columns into rows. It transforms multiple columns into a single column, creating a normalized view of the data

32. Explain the difference between the WHERE and HAVING clauses.

    • The WHERE clause is used to filter rows based on a condition before grouping and aggregating in a query.

      The HAVING clause is used to filter grouped rows based on a condition after grouping and aggregating in a query.

33. What is the purpose of the CASE statement in SQL?

    • The CASE statement is used to perform conditional logic within a SQL query. It allows you to perform different actions based on different conditions. It can be used in SELECT, UPDATE, and INSERT statements.

34. What is the difference between a temporary table and a table variable?

    • A temporary table is a table that is created and exists only for the duration of a session or a transaction. It is stored in the tempdb database and can be accessed by multiple users. A table variable is a variable that holds a result set similar to a table. It is declared and used within a batch, stored procedure, or function. It has a limited scope and is deallocated when the batch or procedure completes.

35. How can you find duplicate records in a table?

    • To find duplicate records in a table, you can use the GROUP BY clause along with the HAVING clause to identify the rows with a count greater than one. For example:

      scss

      Copy code

      SELECT column1, column2, COUNT(*)

These are some of the top Flutter interview questions and answers in 2023. It's important to note that the field of Flutter development is constantly evolving, so it's essential to stay updated with the latest changes and advancements in the framework.