DBMS

 In a system where multiple transactions are executed concurrently, isolation levels manage the extent to which the operations of one transaction are isolated from those of other transactions. Isolation level helps prevent common transaction anomalies: Dirty read : reading uncommitted data from another transaction Non-repeatable read : data changes after it has been read within the same transaction  Phantom Read : New rows are added or removed by another transaction after a query has been executed Isolation level: It determines the degree to which the operations in one transaction are isolated from those in other transactions  application ----> T1(Read, Write )-----|                      ------> T2( read , Write)-----|                         ===========================> both transcation goes to transaction logs  After the transac...

ACID Properties (Explained Simply) ACID is a set of four properties that help databases execute transactions reliably and correctly , even in tough situations like system crashes or network failures . Together, they make sure your data stays accurate and trustworthy. A — Atomicity (All or Nothing) Atomicity means a transaction is treated as one single unit of work : If all steps succeed → COMMIT If any step fails → ROLLBACK (everything is undone) Example: Transferring money from Account A to Account B should either complete fully or not happen at all—no partial updates. C — Consistency (Rules Must Always Hold) Consistency ensures the database always moves from one valid state to another valid state . This means: Reads return valid data from the database Writes must follow database rules/constraints (like primary keys , foreign keys , checks, etc.) Example: If a foreign key requires a related record to exist, the database won’t allow an update that breaks that relation...

 Subqueries/ Inner queries/ Nested Queries:  A SQL subquery is a query nested within another SQL statement. Whenever we want to retrieve data based on the result of another query, we use nested queries When to Use SQL Subqueries? Subqueries can be especially useful in the following scenarios: When a query needs to perform a calculation or retrieve a value from another table without using a join. When a query's condition depends on aggregated data. When combining results from multiple tables in complex ways. When working with conditional logic, such as retrieving data that meets certain criteria based on other data. Advantages of SQL Subqueries Simplicity:  Subqueries can make complex queries easier to read and understand by breaking them down into smaller parts. Modularity:  Subqueries can be reused and modified without affecting the outer query. Flexibility:  Subqueries provide a way to retrieve data without the need for joins. Disadvantages of SQL Subqueries P...

  Views :  A view in SQL is essentially a saved SQL query that acts like a virtual table.  -Unlike physical tables, a view doesn’t store data itself; rather, it retrieves data from one or more tables every time you query it. How to create a view CREATE VIEW active_customer AS SELECT customer_id, name, email from customers where status = "active" Views are read-only by default Deleting a view does not affect the base data. Why to use it:  1. Simplify complex queries:  CREATE VIEW order_summary AS SELECT c.name, o.order_date, SUM(oi.quantity * oi.unit_price) AS total FROM customers c JOIN orders o ON c.customer_id = o.customer_id JOIN order_items oi ON o.order_id = oi.order_id GROUP BY c.name, o.order_date;                     Now to use : SELECT * FROM order_summary; 2. Security: for example, an HR table consists of salary and other things, but needs to show few only  CREATE VIEW employee_public...

  Union : The Union operator in SQL is used to combine the results of two or more SELECT QUERIES  into a single result set and give the unique rows by removing duplicate rows  To use UNION, keep in mind:    - For all SELECT commands number of columns must be the same     - The data type for the corresponding position of columns will be the same     - columns should be listed in the same order across all SELECT statements example:      SELECT ID FROM CUSTOMERS     UNION     SELECT ID FROM ORDERS; UNION ALL : The Union All operator in SQL is used to combine the results of two or more SELECT QUERIES into a  single result, and gives all rows by not removing duplicate rows  example:       SELECT id from customers      UNION ALL     SELECT id from orders      

Joins are used to combine rows from two or more tables based on a related column between them.  Types of joins 1. Inner Join Purpose: Retrieves rows that have matching values in both tables. Example: SELECT * FROM customers  INNER JOIN orders  ON customers.id = orders.customer_id; 2. Left Join (Left Outer Join) Purpose: Fetches all records from the left table and the matched records from the right table. If there’s no match, the result is NULL on the right side. Example: SELECT * FROM customers  LEFT JOIN orders  ON customers.id = orders.customer_id; 3. Right Join (Right Outer Join) Purpose: Fetches all records from the right table and the matched records from the left table. If there’s no match, the result is NULL on the left side. Example: SELECT * FROM customers  RIGHT JOIN orders  ON customers.id = orders.customer_id; 4. Full Join (Full Outer Join) Purpose: Returns matching rows from both tables, plus all remaining records from both tables—even if ...

 Clauses are conditions that help us to make queries more specific or decide what data to fetch     ex: WHERE, HAVING, ORDER BY, LIMIT, GROUP BY 1. WHERE clauses: they filter the rows based on the specified conditions     ex select * from employee where age>20 2. LIMIT clauses are used to restrict the number of rows returned by a query.       ex select * from employee LIMIT 2 3 ORDER BY clauses are used to sort the result in ascending or descending order. by default in ascending order     ex select * from employee ORDER BY salary desc Difference between having and where clause - Where clause is used before the aggregations - Having clause is used after aggregations