NoSQL<\/strong> databases are non-relational and can handle unstructured or semi-structured data (e.g., MongoDB, Cassandra).<\/li>\n<\/ul>\n\n\n\nQuestion 2:<\/strong> How do you optimize a slow SQL query?
Answer:<\/strong><\/p>\n\n\n\n\n- Add indexes<\/strong> to frequently queried columns.<\/li>\n\n\n\n
- Use EXPLAIN<\/strong> to analyze query execution plans.<\/li>\n\n\n\n
- Avoid selecting all columns<\/strong> (SELECT *), and instead query only the needed fields.<\/li>\n<\/ul>\n\n\n\n
Question 3:<\/strong> What are transactions in SQL?
Answer:<\/strong>
<\/strong>A transaction<\/strong> is a sequence of operations executed as a single unit. It guarantees ACID<\/strong> properties (Atomicity, Consistency, Isolation, Durability) to ensure data integrity.<\/p>\n\n\n\nQuestion 4:<\/strong> How do you handle database migrations in a live system?
Answer:<\/strong><\/p>\n\n\n\n\n- Use database migration tools<\/strong> (like Liquibase or Flyway) to automate changes.<\/li>\n\n\n\n
- Apply changes in small, incremental batches<\/strong>.<\/li>\n\n\n\n
- Back up the database<\/strong> before significant changes.<\/li>\n<\/ul>\n\n\n\n
Question 5:<\/strong> What is the difference between JOIN and UNION in SQL?
Answer:<\/strong><\/p>\n\n\n\n\n- JOIN<\/strong> combines rows from two or more tables based on a related column.<\/li>\n\n\n\n
- UNION<\/strong> combines results from two queries into a single result set, eliminating duplicates.<\/li>\n<\/ul>\n\n\n\n
Question 6: <\/strong>You are given the following tables in a company database:<\/p>\n\n\n\nEmployees<\/strong>(id<\/code>, name<\/code>, department_id<\/code>, salary<\/code>)
Departments<\/strong>(id<\/code>, name<\/code>)
Orders<\/strong>(order_id<\/code>, employee_id<\/code>, customer_id<\/code>, order_amount<\/code>, order_date<\/code>)
Customers<\/strong>(id<\/code>, name<\/code>, region<\/code>)<\/p>\n\n\n\nTasks:<\/strong><\/p>\n\n\n\nA)<\/strong> List each employee, their department name, the total number of orders they handled, and their average order amount, including employees who have not handled any orders.<\/p>\n\n\n\nB)<\/strong> Find the second-highest salary in the company.<\/p>\n\n\n\nC)<\/strong> Suggest indexes for performance optimization.<\/p>\n\n\n\nD)<\/strong> Explain how to design the Employees and Departments tables in 3NF<\/strong>.<\/p>\n\n\n\nE)<\/strong> Safely transfer all orders from one employee to another using a transaction, explaining ACID properties.<\/p>\n\n\n\nAnswers:<\/strong><\/p>\n\n\n\nA) Employee Orders with Aggregation and Join:<\/strong>
To list each employee with their department, total orders, and average order amount, including employees with no orders:<\/p>\n\n\n\n\n
SELECT \n e.id AS EmployeeID,\n e.name AS EmployeeName,\n d.name AS DepartmentName,\n COUNT(o.order_id) AS TotalOrders,\n COALESCE(AVG(o.order_amount), 0) AS AvgOrderAmount\nFROM Employees e\nLEFT JOIN Departments d ON e.department_id = d.id\nLEFT JOIN Orders o ON e.id = o.employee_id\nGROUP BY e.id, e.name, d.name;\n<\/code><\/pre>\n<\/div><\/div>\n\n\n\nExplanation:<\/strong> LEFT JOIN<\/code> ensures employees with no orders are included. COUNT<\/code> and AVG<\/code> calculate total orders and average amount. COALESCE<\/code> handles nulls for employees with zero orders.<\/p>\n\n\n\nB) Second-Highest<\/strong> Salary:
To find the second-highest salary in the company:<\/p>\n\n\n\nSELECT MAX(salary) AS SecondHighestSalary\nFROM Employees\nWHERE salary < (SELECT MAX(salary) FROM Employees);\n<\/code><\/pre>\n\n\n\nExplanation:<\/strong> The subquery finds the highest salary first, then the main query selects the next highest salary below it.<\/p>\n\n\n\nC) Indexing for Performance Optimization:<\/strong>
To improve query performance on large tables, the following indexes can be used:<\/p>\n\n\n\nCREATE INDEX idx_employee_department ON Employees(department_id);\nCREATE INDEX idx_orders_employee ON Orders(employee_id);\nCREATE INDEX idx_orders_date ON Orders(order_date);\n<\/code><\/pre>\n\n\n\nExplanation:<\/strong> Indexes speed up joins and queries, and reduce full table scans on large tables.<\/p>\n\n\n\nD) Designing Tables in 3NF:<\/strong>
Employees and Departments tables can be designed in 3NF as follows:<\/p>\n\n\n\n\n- 1NF:<\/strong> All columns are atomic, no repeating groups.<\/li>\n\n\n\n
- 2NF:<\/strong> All non-key columns depend on the full primary key.<\/li>\n\n\n\n
- 3NF:<\/strong> Remove transitive dependencies.
Example:<\/strong> Employees(id<\/code>, name<\/code>, department_id<\/code>, salary<\/code>) references Departments(id<\/code>, name<\/code>). No redundant data; department name stored only once.<\/li>\n<\/ul>\n\n\n\nE) Transferring Orders Using a Transaction:<\/strong>
To safely transfer all orders from one employee to another:<\/p>\n\n\n\nSTART TRANSACTION;\n\nUPDATE Orders\nSET employee_id = 102 -- new employee\nWHERE employee_id = 101; -- old employee\n\nCOMMIT;\n<\/code><\/pre>\n\n\n\nExplanation:<\/strong><\/p>\n\n\n\n\n- Atomicity:<\/strong> All orders transfer together or none if rollback occurs.<\/li>\n\n\n\n
- Consistency:<\/strong> Database remains valid.<\/li>\n\n\n\n
- Isolation:<\/strong> Other transactions don\u2019t see partial updates.<\/li>\n\n\n\n
- Durability:<\/strong> Changes persist even if the system crashes.<\/li>\n<\/ul>\n\n\n\n
![\"what](\"https:\/\/www.guvi.in\/blog\/wp-content\/uploads\/2024\/11\/1-10.png\" "\\"\\"")
<\/figure>\n\n\n\n![\"Top](\"https:\/\/www.guvi.in\/blog\/wp-content\/uploads\/2024\/11\/2-10.png\" "\\"\\"")
<\/figure>\n\n\n\n