Lets Prepare
1. Normalization Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. Objectives of Normalization: Forms of Normalization: Example: Before 1NF: StudentID Name Subjects 1 John Doe Math, Science What We Did: After 1NF: StudentID Name Subject 1 John Doe Math 1 John Doe Science Example: Before 2NF: OrderID ProductID ProductName 1 101 Laptop What We Did: After 2NF: Orders Table: OrderID ProductID…
Design a database system for a multi-floor car parking building where: The system should support both a normalized design for maintaining data integrity and a denormalized design for faster access and scalability. 1. Normalized Database Design In a normalized design, we follow 3NF (Third Normal Form) to minimize data redundancy and ensure data integrity. Schema (Normalized Design) Key Queries (Normalized Design) Trade-offs (Normalized Design) ✅ Pros: ❌ Cons: 2. Denormalized…
What is PIVOT in Databases? PIVOT is a technique used in SQL to transform row data into columns, making it useful for reporting and data visualization. Different databases implement pivoting differently. 1. Example Table (Sales Data) Before explaining the implementation, consider the following Sales table: Product Year Sales A 2023 100 B 2023 200 A 2024 150 B 2024 250 Expected Output (Pivoted Table): Product 2023 2024 A 100 150…
1. Introduction In system design, databases must be chosen based on the requirements of consistency, availability, and partition tolerance. Two key concepts guide this decision: 2. ACID Properties ACID properties define how traditional relational databases (SQL) maintain reliability and correctness. 3. CAP Theorem CAP Theorem states that in a distributed system, a database can achieve at most two of the following three properties: CAP Combinations and Database Types Category Ensures…
Introduction When dealing with large stored procedures or complex queries, it is essential to identify which part of the query is taking more time and optimize it. The execution plan is a vital tool in understanding how the database processes a query and helps in pinpointing performance bottlenecks. 1. Execution Plan in MySQL Using EXPLAIN To analyze a query’s execution plan in MySQL, use: This will output details about how…
Problem 1: Combine Two Tables Explanation: Write a SQL query to combine two tables, Person and Address, by PersonId, ensuring that if an address is missing, it still appears in the output. SQL Approach: SQL Query: Example: Input: Output: Edge Cases Considered: Problem 2: Second Highest Salary Explanation: Write a SQL query to get the second highest salary from the Employee table. If there is no second highest salary, return…
1. Tables in SQL Tables are the basic structure in SQL to store data in rows and columns. Creating a Table Modifying a Table Deleting a Table 2. Views in SQL A view is a virtual table based on the result of a SELECT query. Creating a View Using a View Updating a View Deleting a View 3. Copying Databases and Tables Copying a Table Copying a Database Using a…
1. Introduction to SQL Indexes Indexes in SQL are database objects that improve the speed of data retrieval operations on a table. They work like a book index, helping the database locate data without scanning the entire table. 2. Benefits of SQL Indexes 3. Types of SQL Indexes 3.1 Single-Column Index An index created on a single column. Use Case: Example: 3.2 Composite Index An index created on two or…
1. Introduction to SQL Joins SQL Joins are used to combine rows from two or more tables based on a related column. They are essential for querying data stored across multiple tables in a relational database. 2. Types of SQL Joins 2.1 INNER JOIN Retrieves records that have matching values in both tables. Syntax: Example: Consider two tables: id name course_id 1 John 101 2 Jane 102 3 Alice NULL…
1. Introduction to SQL SQL (Structured Query Language) is a standard language used to interact with relational database systems. It allows users to query, manipulate, and manage data efficiently. 2. SQL Commands SQL commands are categorized into the following types: 2.1 Data Definition Language (DDL) Used to define and manage database schema. Command Description Example CREATE Creates a new database, table, or other database objects. CREATE TABLE students (id INT,…