Frequently asked questions on a basic level for SQL job interviews:
1. What is SQL, and what is it used for?
SQL stands for Structured Query Language, and it is used for managing relational databases. It allows users to store, retrieve, and manipulate data in a relational database management system (RDBMS).
2. What are the types of SQL commands?
There are three types of SQL commands:
i) DDL (Data Definition Language) - used to define database objects, such as tables, indexes, and constraints.
ii) DML (Data Manipulation Language) - used to manipulate data within a database, such as inserting, updating, and deleting records.
iii) DQL (Data Query Language) - used to retrieve data from a database, such as SELECT statements.
3. What is a stored procedure?
A stored procedure is a precompiled SQL statement that is stored in the database. It can be called by other SQL statements or applications, and it can include input and output parameters. Stored procedures are often used to encapsulate complex logic and improve performance by reducing network traffic.
4. What is an index in SQL?
An index is a data structure that is used to improve query performance by allowing data to be accessed more quickly. An index is created on one or more columns of a table, and it provides a faster way to find specific rows based on the values in those columns. Indexes are often used on primary keys, foreign keys, and frequently accessed columns.
5. What is a trigger in SQL?
A trigger is a database object that is associated with a table and is automatically executed in response to certain events, such as insert, update, or delete operations. Triggers can be used to enforce business rules, maintain data integrity, and perform auditing tasks.
6. What is a join in SQL?
A join is an SQL operation that combines rows from two or more tables based on a related column between them. It is used to retrieve data from multiple tables in a single query. There are several types of joins, including inner join, outer join, and cross join.
7. What is a relational database?
A relational database is a type of database that stores data in tables that are related to each other through common fields.
8. What is a primary key?
A primary key is a unique identifier for each record in a table. It is used to enforce data integrity and to ensure that each record can be uniquely identified.
9. What is a foreign key?
A foreign key is a field in a table that is used to establish a relationship with the primary key of another table.
10. What is an inner join?
An inner join is a type of join that returns only the rows that have matching data in both tables.
Example:
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id;
11. What is an outer join?
An outer join is a type of join that returns all rows from one table and only matching rows from the other table.
Example:
SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;
12. What is a subquery?
A subquery is a query that is nested inside another query. It is used to retrieve data that will be used in the main query.
Example:
SELECT *
FROM customers
WHERE customer_id IN
(SELECT customer_id
FROM orders
WHERE order_date = '2022-02-20');
13. What is a view in SQL?
A view is a virtual table that is based on the result of a SELECT statement. It can be used to simplify queries or to provide an abstraction layer between the database and the application. Views are often used to restrict access to sensitive data.
Example:
CREATE VIEW customer_order_count AS
SELECT customers.customer_name, COUNT(orders.order_id) AS order_count
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_name;
14. What is a stored procedure?
A stored procedure is a precompiled block of SQL statements that can be called by other programs or scripts. It is used to simplify complex tasks and to improve performance.
Example:
CREATE PROCEDURE sp_get_customer_orders
@customer_id INT
AS
BEGIN
SELECT *
FROM orders
WHERE customer_id = @customer_id
END;
15. What is a trigger?
A trigger is a special type of stored procedure that is automatically executed in response to certain events, such as a data change in a table.
Example:
CREATE TRIGGER tr_insert_customer
ON customers
FOR INSERT
AS
BEGIN
INSERT INTO customer_log (customer_id, log_message)
VALUES (INSERTED.customer_id, 'New customer added')
END;
16. What is ACID?
ACID stands for Atomicity, Consistency, Isolation, and Durability. It is a set of properties that guarantee reliable processing of database transactions.
17. What is a self-join in SQL?
A self-join is when a table is joined to itself. This is often used to compare rows within a single table. For example, if you have a table of employees with columns for their ID and their manager's ID, you can use a self-join to find all the employees who report to the same manager.
18. What is a subquery in SQL?
A subquery is a query within another query. It is often used to filter results or to find data based on a condition. For example, you could use a subquery to find all the customers who have placed an order in the past month.
19. What is a correlated subquery in SQL?
A correlated subquery is a subquery that references a column from the outer query. This allows you to filter the results of the subquery based on data from the outer query. For example, you could use a correlated subquery to find all the customers who have placed an order that is larger than the average order size for their region.
20. What is a transaction in SQL?
A transaction is a series of SQL statements that are executed as a single unit of work. If any of the statements fail, the entire transaction is rolled back and the database is returned to its previous state. Transactions are used to ensure data consistency and to prevent data corruption.
21. What is normalization in SQL?
Normalization is the process of organizing data in a database to reduce redundancy and dependency. There are several levels of normalization, each with its own set of rules for how data should be organized. Normalization is important for data consistency, maintainability, and scalability.
22. What is denormalization in SQL?
Denormalization is the process of intentionally introducing redundancy into a database to improve performance. This is often done by duplicating data in multiple tables or by combining data from multiple tables into a single table. Denormalization can improve query performance but can also make data maintenance more complex.
23. What is a clustered index in SQL?
A clustered index is an index that determines the physical order of the data in a table. This means that the data is physically stored on disk in the same order as the clustered index. A table can have only one clustered index.
24. What is a non-clustered index in SQL?
A non-clustered index is an index that does not determine the physical order of the data in a table. Instead, it provides a faster way to find specific rows based on the values in the indexed columns. A table can have multiple non-clustered indexes.
25. What is a cursor in SQL?
A cursor is a database object that allows you to process rows of data one at a time. Cursors are often used when you need to perform complex processing on each row, or when you need to update or delete rows based on certain conditions.
SQL For Beginners book>>Download>>more>>price>>
=========
No comments:
Post a Comment