Top 50 interview questions of SQL with solutions

Fundamental Concepts

1. What is SQL?

   • SQL (Structured Query Language) is a domain-specific language used to communicate with and manage relational databases. It's used to retrieve, insert, update, and delete data from databases.

2. What are the different types of SQL statements?

   • DDL (Data Definition Language): Used to create, alter, and drop database objects like tables, indexes, and views.

   • DML (Data Manipulation Language): Used to insert, update, delete, and select data from tables.

   • DCL (Data Control Language): Used to control access to data, such as granting and revoking privileges.

3. What is a database?

   • A database is a structured collection of data organized in a way that allows efficient retrieval and management.

4. What is a table in SQL?

   • A table is a collection of rows and columns that represents a specific entity or concept.

5. What is a column in SQL?

   • A column is a vertical unit of a table that represents a specific attribute or characteristic of the data.

6. What is a row in SQL?

   • A row is a horizontal unit of a table that represents a single instance of the data.

7. What is a primary key in SQL?

   • A primary key is a unique identifier for each row in a table. It must be unique and non-null.

8. What is a foreign key in SQL?

   • A foreign key is a column in one table that references the primary key of another table. It establishes a relationship between the two tables.

Data Manipulation Language (DML)

1. How do you select data from a table in SQL?

   • SELECT column_name(s) FROM table_name;

2. How do you insert data into a table in SQL?

• INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

1. How do you update data in a table in SQL?

• UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

1. How do you delete data from a table in SQL?

• DELETE FROM table_name WHERE condition;

Data Definition Language (DDL)

1. How do you create a table in SQL?

• CREATE TABLE table_name (column1 data_type, column2 data_type, ...);

1. How do you alter a table in SQL?

• ALTER TABLE table_name ADD column_name data_type;

• ALTER TABLE table_name DROP column_name;

• ALTER TABLE table_name MODIFY column_name data_type;  

1. How do you drop a table in SQL?

• DROP TABLE table_name;

Joins

1. What is a join in SQL?

• A join is a way to combine rows from two or more tables based on a related column.

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

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

• LEFT JOIN: Returns all rows from the left table, even if there are no matches in the right table.

• RIGHT JOIN: Returns all rows from the right table, even if there are no matches in the left table.  

• FULL OUTER JOIN: Returns all rows when there is a match in either left or right table.  

1. How do you perform an INNER JOIN in SQL?

• SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column = table2.column;

Subqueries

1. What is a subquery in SQL?

• A subquery is a query that is nested within another query.

1. How do you use a subquery in SQL?

• You can use subqueries as a source of data for the outer query or as a condition in the WHERE clause.

Aggregate Functions

1. What are aggregate functions in SQL?

• Aggregate functions operate on a group of rows and return a single value.

1. What are some common aggregate functions in SQL?

• COUNT(): Returns the number of rows.

• SUM(): Returns the sum of values.

• AVG(): Returns the average of values.

• MAX(): Returns the maximum value.

• MIN(): Returns the minimum value.  

Grouping and Filtering

1. How do you group data in SQL?

• GROUP BY column_name(s);

1. How do you filter data in SQL?

• WHERE condition;

1. How do you combine grouping and filtering in SQL?

• SELECT column_name(s) FROM table_name GROUP BY column_name(s) HAVING condition;

Indexes

1. What is an index in SQL?

• An index is a data structure that improves the speed of data retrieval.

1. How do you create an index in SQL?

• CREATE INDEX index_name ON table_name (column_name);

1. When should you use indexes?

• Indexes are useful when you frequently query a table based on a specific column or set of columns.

Constraints

1. What are constraints in SQL?

• Constraints are rules that enforce data integrity in a database.

1. What are some common constraints in SQL?

• NOT NULL: Ensures that a column cannot contain null values.

• UNIQUE: Ensures that all values in a column are unique.

• PRIMARY KEY: Defines a unique identifier for each row in a table.

• FOREIGN KEY: Establishes a relationship between two tables.

• CHECK: Ensures that a column value satisfies a specific condition.

Views

1. What is a view in SQL?

• A view is a virtual table that presents a subset of data from one or more underlying tables.

1. How do you create a view in SQL?

• CREATE VIEW view_name AS SELECT column_name(s) FROM table_name;

Stored Procedures

1. What is a stored procedure in SQL?

• A stored procedure is a precompiled SQL statement that can be executed multiple times.

1. How do you create a stored procedure in SQL?

• CREATE PROCEDURE procedure_name AS BEGIN ... END;

Transactions

1. What is a transaction in SQL?

• A transaction is a unit of work that is treated as a single operation.

1. How do you start a transaction in SQL?

• BEGIN TRANSACTION;

1. How do you commit a transaction in SQL?

• COMMIT TRANSACTION;

1. How do you rollback a transaction in SQL?

• ROLLBACK TRANSACTION;

Advanced Topics

1. What is normalization in SQL?

• Normalization is the process of organizing data to minimize redundancy and improve data integrity.

1. What are the different normal forms in SQL?

• First Normal Form (1NF): Each column should contain atomic values.

• Second Normal Form (2NF): All non-key attributes should be fully dependent on the primary key.

• Third Normal Form (3NF): All non-key attributes should not be transitively dependent on the primary key.

1. What is a database trigger in SQL?

• A trigger is a stored procedure that is automatically executed when a specific event occurs in a database.

1. What is a database cursor in SQL?

• A cursor is a pointer that allows you to navigate through a result set row by row.

1. What is a database sequence in SQL?

• A sequence is an object that generates a unique number sequence.

1. What is a database synonym in SQL?

• A synonym is an alias for a table, view, or sequence.

1. What is a database role in SQL?

• A role is a collection of privileges that can be granted to users.

1. What is a database user in SQL?

• A user is an entity that can access a database.

1. What is a database privilege in SQL?

• A privilege is a permission granted to a user or role to perform certain actions on a database object.

1. What is a database grant in SQL?

• A grant is a statement that assigns privileges to a user or role.

1. What is a database revoke in SQL?

• A revoke is a statement that removes privileges from a user or role.

  What is database security?

• Database security is the protection of data stored in a database from unauthorized access, modification, or deletion.

  What are the common security threats to databases?

• SQL injection, unauthorized access, data breaches, malware attacks, and denial-of-service (DoS) attacks.

  What are the best practices for database security?

• Use strong passwords, implement access controls, regularly patch and update the database system, encrypt sensitive data, and conduct regular security audits.