Top 65 SQL Interview Questions and Answers | SQL Interview Preparation | SQL Training | Edureka

Summary

This Edureka session focuses on the top 65 SQL interview questions, crucial for aspiring database administrators. The discussion delves into SQL fundamentals, covering data definition, manipulation, control languages, and transaction control. It differentiates between SQL and MySQL, explains various types of joins, keys, constraints, and indexes. The session also addresses normal forms, the ACID property, triggers, and the use of subqueries, aggregate, and scalar functions. Practical examples include pattern matching, fetching specific records, and understanding views, stored procedures, and functions. The comprehensive guide equips individuals with the knowledge to handle SQL queries and database management system operations effectively.

Takeaways

• 🗂️ SQL is integral for managing relational databases and is crucial for roles such as database administrators.
• 🔑 The difference between DELETE and TRUNCATE is that DELETE can be rolled back, is used for row deletion, and is a Data Manipulation Language (DML) command, while TRUNCATE cannot be rolled back, is used to delete all rows, and is a Data Definition Language (DDL) command.
• 📚 SQL subsets include Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL), each serving different functions in database operations.
• 🏢 Database Management Systems (DBMS) come in various types, including hierarchical, relational, network, and object-oriented, each with its unique structure and use case.
• 🔍 JOINs in SQL are used to combine rows from two or more tables based on related columns, with types including INNER JOIN, FULL JOIN, LEFT JOIN, and RIGHT JOIN.
• 🔤 Data types in SQL, such as CHAR and VARCHAR, differ in that CHAR is for fixed-length strings and VARCHAR is for variable-length strings.
• 🔑 Primary keys are essential for uniquely identifying records in a table and can be one of the candidate keys chosen to serve this purpose.
• 🔒 Constraints in SQL, like NOT NULL, UNIQUE, CHECK, DEFAULT, and INDEX, are used to set limits and rules for data integrity and table structure.
• 🔄 Denormalization is a technique used to improve data retrieval speed by introducing redundancy, in contrast to normalization which organizes data to reduce redundancy.
• 📈 The ACID properties (Atomicity, Consistency, Isolation, Durability) ensure reliable processing of transactions in a database system.
• 🔄 Recursive stored procedures in SQL are self-calling functions that repeat until a boundary condition is met, useful for executing the same code multiple times.

Q & A

What is the primary purpose of SQL in the context of database management?

SQL, or Structured Query Language, is the core of relational database management systems. It is used for accessing and managing databases, allowing users to perform operations such as querying, updating, inserting, and deleting data.

What is the difference between the DELETE and TRUNCATE statements in SQL?

The DELETE statement is used to delete specific rows in a table and allows for row-level control and transaction rollback. In contrast, the TRUNCATE statement is used to remove all rows from a table quickly, but it does not support row-level control and the operation cannot be rolled back.

Can you explain the different subsets of SQL?

The different subsets of SQL include the Data Definition Language (DDL) for defining the database schema, Data Manipulation Language (DML) for data manipulation, Data Control Language (DCL) for access control and permissions, and Transaction Control Language (TCL) for managing database transactions.

What is a database management system and what are its types?

A database management system (DBMS) is a software application that interacts with users, applications, and databases to capture, store, retrieve, and analyze data. Types of DBMS include hierarchical, relational, network, and object-oriented systems.

What does it mean by a table and a field in SQL?

In SQL, a table is a collection of organized data in rows and columns, representing a set of records. A field, on the other hand, refers to the columns in a table, each representing a specific data attribute of the records.

What is a JOIN clause in SQL and what are its types?

A JOIN clause in SQL is used to combine rows from two or more tables based on related columns between them. The main types of joins are INNER JOIN, which returns matching rows from both tables, FULL JOIN, which returns all rows from both tables with matching rows first, LEFT JOIN, which returns all records from the left table and matching rows from the right table, and RIGHT JOIN, which is the reverse of LEFT JOIN.

What is the difference between CHAR and VARCHAR data types in SQL?

CHAR and VARCHAR are both used for character data types in SQL. CHAR is used for fixed-length strings, meaning it reserves a specific amount of storage for each value regardless of the content. VARCHAR, however, is for variable-length strings and adjusts the storage based on the actual content length.

What is a primary key in SQL and why is it important?

A primary key in SQL is a column or a set of columns that uniquely identify every row in a table. It is important because it ensures data integrity and is used to establish relationships between tables through foreign keys.

What are constraints in SQL and why are they used?

Constraints in SQL are rules applied to columns in a table to limit the type of data that can be inserted or updated. They are used to ensure the accuracy and reliability of the data in a database, preventing invalid or inconsistent data from being entered.

What is the difference between SQL and MySQL?

SQL is a standard language for managing relational databases, while MySQL is a specific implementation of an SQL-based relational database management system. SQL provides the syntax and commands for database operations, whereas MySQL is a software that uses this language to manage database servers.

What is a unique key in SQL and how does it differ from a primary key?

A unique key in SQL is a column or set of columns that can be used to uniquely identify a row in a table, similar to a primary key. However, while a table can have only one primary key, it can have multiple unique keys. Additionally, a unique key allows null values, whereas a primary key does not.

What is the purpose of a foreign key in SQL?

A foreign key in SQL is used to maintain referential integrity between tables. It is a column or set of columns in a child table that references the primary key in a parent table, ensuring that the data in the child table corresponds to existing data in the parent table.

What is data integrity and why is it important in databases?

Data integrity refers to the accuracy and consistency of data stored in a database. It is important because it ensures that the data is reliable and follows the business rules when entered or modified in the database, preventing data corruption and ensuring data quality.

What are the differences between clustered and non-clustered indexes in SQL?

A clustered index in SQL reorders the physical storage of data in a table based on the indexed column, and each table can have only one clustered index. It is efficient for data retrieval but slower to modify. A non-clustered index, on the other hand, does not change the physical order of data but creates a separate object pointing to the original rows. It allows multiple non-clustered indexes per table but is generally slower for data retrieval compared to clustered indexes.

What is denormalization in the context of databases and what is its purpose?

Denormalization is a technique used to optimize database performance by introducing redundancy into the data structure. It involves adding redundant data to a table or combining data from multiple tables into a single table. This can improve read performance but may increase storage requirements and can affect write performance due to the need to maintain data consistency.

What are entities and relationships in the context of database design?

Entities are real-world objects, such as people, places, or things, that are represented in a database with data. Relationships are associations between these entities that have some form of connection. For example, a customer entity may have a relationship with an order entity if the customer places an order.

What is an index in SQL and what are its benefits?

An index in SQL is a performance tuning method that allows faster retrieval of records from a table. It creates an entry for each value in a column to speed up data retrieval. The benefits of using indexes include improved query performance, especially for large tables, and faster sorting and grouping of data.

What are the advantages of normalization in database design?

Normalization in database design offers several advantages, including better organization of the database with more tables and smaller rows, efficient data access, greater flexibility for queries, quick information retrieval, easier implementation of security, reduction of redundant and duplicate data, and more compact databases. It also ensures consistent data after modifications.

What is the difference between the DROP and TRUNCATE commands in SQL?

The DROP command in SQL is used to remove a table and all its data from the database, and this action cannot be rolled back. It completely deletes the table structure and data. The TRUNCATE command, on the other hand, is used to remove all rows from a table but does not drop the table itself. The table structure and its columns, constraints, indexes, and so on remain intact, but all data is deleted and this action is also not reversible.

What are the different types of normalization and their main characteristics?

There are several types of normalization: First Normal Form (1NF) requires that there are no repeating groups or arrays within a table and each cell should contain a single value. Second Normal Form (2NF) builds on 1NF by ensuring that all non-key columns are fully functional dependent on the primary key. Third Normal Form (3NF) further requires that there is no transitive dependency for non-primary key columns. Boyce-Codd Normal Form (BCNF) is a stronger version of 3NF, ensuring that every determinant is a candidate key.

What is the ACID property in databases and why is it important?

The ACID property in databases stands for Atomicity, Consistency, Isolation, and Durability. It is important because it ensures the reliability of data transactions in a database system. Atomicity ensures that transactions are completed fully or not at all. Consistency ensures that transactions bring the database from one valid state to another while maintaining data integrity. Isolation ensures that concurrent transactions are executed without interference. Durability guarantees that once a transaction is committed, it remains so, even in the event of a system failure.

What is a trigger in SQL and what are its main types?

A trigger in SQL is a special type of stored procedure that is automatically executed in response to certain events on a particular table or view. The main types of triggers are BEFORE triggers, which are executed before an operation, and AFTER triggers, which are executed after an operation.