Top Sql Interview Questions And Answers [2024]

1. What is SQL? 
SQL stands for Structured Query Language. SQL is an ANSI(American National Standards Institute) standard.It's a  language used for interaction with database.

 

2. What are the subsets of SQL?

 1. Data Definition Language (DDL)

• Purpose: Used to define and manage all the objects in a database, such as tables, indexes, and schemas.

• Common Commands:

  • CREATE: Creates new database objects like tables, indexes, or schemas.
  • ALTER: Modifies existing database objects.
  • DROP: Deletes database objects.
  • TRUNCATE: Removes all records from a table, but the table structure remains.
     

• Example:

 2. Data Manipulation Language (DML)

• Purpose: Used for querying and modifying data in the database.

• Common Commands:

  • SELECT: Retrieves data from the database.
  • INSERT: Adds new records to a table.
  • UPDATE: Modifies existing records.
  • DELETE: Removes records from a table.
     

• Example:
  
   

3. Data Control Language (DCL)

• Purpose: Used to control access to data in the database.

• Common Commands:

  • GRANT: Gives privileges to users.
  • REVOKE: Takes away privileges from users.
     

• Example:

4. Transaction Control Language (TCL)

• Purpose: Manages transactions within a database, ensuring that they are completed correctly and consistently.

• Common Commands:

  • COMMIT: Saves all changes made in the transaction.
  • ROLLBACK: Reverts changes made in the transaction.
  • SAVEPOINT: Sets a point within a transaction to which you can later roll back.
  • SET TRANSACTION: Defines the properties of a transaction, like its isolation level.
     

• Example:

 

 

3. What are SQL dialects?
SQL dialects are variations or extensions of the standard SQL language implemented by different database management systems (DBMS). While all these dialects follow the core SQL standard, they may include additional features, functions, or syntax specific to the particular database system.
ex - MySQL, PostgreSQL, Oracle SQL, Microsoft SQL Server (T-SQL), SQLite, IBM Db2 SQL

 

4. Difference between SQL and MYSQL ?
SQL is a standardized language used for querying and managing databases, while MySQL is a specific relational database management system (RDBMS) that uses SQL to perform database operations.

5. What is a database? 
A database is an organized collection of data that is stored and accessed electronically. It allows for efficient retrieval, management, and manipulation of data. Databases are designed to handle large amounts of information and can be used for a variety of purposes, from storing customer records in a business to managing the backend data for a website or application.

 

6. What is a DBMS? 
A Database Management System (DBMS) is software that provides an interface for users and applications to interact with databases. It manages the storage, retrieval, and updating of data in a database, ensuring that the data is organized, secure, and accessible. The DBMS acts as an intermediary between the user, the applications, and the physical data stored in the database. 

 Key Functions of a DBMS:

1. Data Storage, Retrieval, and Update:

   • Manages how data is stored, retrieved, and updated in a database. Users and applications can perform operations like inserting new data, querying existing data, updating records, and deleting data.

2. Data Security:

   • Ensures that only authorized users can access or modify the data. It provides mechanisms for user authentication, access control, and data encryption.

3. Data Integrity:

   • Maintains the accuracy and consistency of data by enforcing rules and constraints, such as primary keys, foreign keys, and unique constraints.

4. Data Independence:

   • Separates the data from the applications that use the data. This means that changes to the data structure do not necessarily require changes to the applications.

5. Concurrency Control:

   • Manages simultaneous data access by multiple users or applications, ensuring that data remains consistent and accurate, and preventing conflicts or data corruption.

6. Backup and Recovery:

   • Provides mechanisms for regularly backing up data and recovering it in case of data loss or corruption.

7. Transaction Management:

   • Supports transactions, ensuring that a series of operations either all succeed or all fail, maintaining data consistency. This is governed by the ACID properties (Atomicity, Consistency, Isolation, Durability).
      

6. Explain tables and fields in SQL?
Tables Definition: A table in SQL is a structured set of data organized into rows and columns. It represents an entity, such as "Customers," "Orders," or "Products," where each row is a record, and each column represents a specific attribute or property of that entity. 

Fields Definition: A field in SQL is a single piece of data, defined by a column in a table. Fields represent the smallest unit of data within a table. Each field corresponds to a specific attribute of the entity.

 

7. What is primary key in DBMS ?
In a Database Management System (DBMS), a primary key is a unique identifier for each record in a database table. It ensures that each record can be uniquely identified by its key value, which is crucial for maintaining data integrity and enabling efficient data retrieval.

Here are some key characteristics of a primary key:

1. Uniqueness: The value of a primary key must be unique for each record in the table. No two rows can have the same primary key value.

2. Non-nullable: A primary key must contain a value; it cannot be NULL. This ensures that every record has a unique and identifiable key.

3. Immutable: Ideally, the value of a primary key should not change. Changing primary key values can complicate database relationships and integrity.

4. Single Column or Composite: A primary key can consist of a single column (simple key) or multiple columns (composite key). Composite keys are used when a single column alone cannot guarantee uniqueness.

For example, in a table of employees, an EmployeeID might serve as the primary key. Each EmployeeID is unique to a specific employee, making it easy to retrieve or update records based on that ID.


8. What is unique key in DBMS ?
In a Database Management System (DBMS), a unique key is a constraint that ensures all values in a column or a set of columns are unique across the rows in a table. Unlike the primary key, which uniquely identifies each record in a table and is not allowed to be NULL, a unique key constraint enforces uniqueness but allows for NULL values, provided that the column(s) containing NULLs do not conflict with the uniqueness rule.

Example - Consider a table of users where each user must have a unique email address:

In this example:

• UserID is the primary key and uniquely identifies each user.
• Email is defined as a unique key, ensuring that no two users can have the same email address.

 

 9. What is a foreign key in DBMS ?
In a Database Management System (DBMS), a foreign key is a column or a set of columns in a table that establishes a link between data in two tables. It is used to enforce referential integrity, which ensures that the relationship between tables remains consistent and valid.

Example - Consider two tables: Orders and Customers. Each order should be associated with a customer, and you want to ensure that every order references a valid customer.

In this example:

• CustomerID in the Customers table is the primary key.
• CustomerID in the Orders table is a foreign key that references the CustomerID in the Customers table.

This relationship ensures that every Order must be linked to a valid Customer. If you attempt to insert an order with a CustomerID that does not exist in the Customers table, the database will prevent the insertion, thus maintaining referential integrity.

 

710. What is a JOIN in SQL ?. Explain each JOIN with Example.
In SQL, a JOIN is used to combine rows from two or more tables based on a related column between them. Joins are crucial for querying relational databases where data is distributed across multiple tables. Here are the main types of joins with explanations and examples

 Example: Suppose we have two tables: employees and departments.

employees table:

employee_id	name	department_id
1	Alice	10
2	Bob	20
3	Charlie	30

   
departments table:

department_id	department_name
10	HR
20	IT
30	Finance

 1. INNER JOIN
  An INNER JOIN returns only the rows where there is a match in both joined tables. If a row in one table has no corresponding row in the other table, it will not be included in the result. 

Query 
 

Result:

name	department_name
Alice	HR
Bob	IT

 

2. LEFT JOIN (or LEFT OUTER JOIN)
A LEFT JOIN returns all rows from the left table (the first table in the join), and the matched rows from the right table (the second table in the join). If there is no match, the result is NULL for columns from the right table.:

 

Using the same tables as above:

Query:

Result:

name	department_name
Alice	HR
Bob	IT
Charlie	NULL

Charlie is included in the results with NULL for the department name since there is no corresponding department.

 

3. RIGHT JOIN (or RIGHT OUTER JOIN)
A RIGHT JOIN returns all rows from the right table, and the matched rows from the left table. If there is no match, the result is NULL for columns from the left table.:

Query:


Result:

name	department_name
Alice	HR
Bob	IT
NULL	Finance

The department Finance is included in the results with NULL for the employee name, indicating that there are no employees in that department.

4. FULL JOIN (or FULL OUTER JOIN)
A FULL JOIN returns all rows when there is a match in one of the tables. It combines the results of both LEFT JOIN and RIGHT JOIN. Where there is no match, the result is NULL for the columns of the table that does not have a match.:

Query:

Result:

name	department_name
Alice	HR
Bob	IT
Charlie	NULL
NULL	Finance

This result includes all employees and departments, with NULL where there is no match.
 

5. CROSS JOIN
A CROSS JOIN returns the Cartesian product of the two tables, which means it combines each row of the first table with every row of the second table. It does not require a condition to join.

Example:

Query:


Result:

name	department_name
Alice	HR
Alice	IT
Alice	Finance
Bob	HR
Bob	IT
Bob	Finance
Charlie	HR
Charlie	IT
Charlie	Finance

In this example, every employee is paired with every department.

Summary
INNER JOIN:  Returns only matching rows from both tables.
LEFT JOIN:  Returns all rows from the left table and matched rows from the right table, with NULL where there is no match.
RIGHT JOIN:  Returns all rows from the right table and matched rows from the left table, with NULL where there is no match.
FULL JOIN:  Returns all rows when there is a match in either table, with NULL where there is no match.
CROSS JOIN:  Returns the Cartesian product of the two tables, combining each row of the first table with every row of the second table