Interview Preparation &
Essential Concepts for
Introduction to Databases
Author: Muhammad Jahanzaib
Relational Databases - Topics
Author: Muhammad Jahanzaib
Instructions for Learning and Interview Preparation
Remember, databases are vast and complex topics that take time and practice to fully understand. Don't get discouraged if you encounter difficulties. Instead, keep practicing and seek help when needed. �Good luck!
Author: Muhammad Jahanzaib
Introduction to Databases
A database is a collection of data that is organized in a way that allows easy access, retrieval, and management of data. It can be stored and managed using computer software and is designed to meet the specific needs of an organization or application.
Key Points:
Definition:
Author: Muhammad Jahanzaib
Introduction to Databases
Examples:
Author: Muhammad Jahanzaib
Introduction to Databases
Best Practices::
Author: Muhammad Jahanzaib
Relational vs Non-relational databases
Feature | Relational Databases | Non-Relational Databases |
Structure | Data is stored in tables with rows and columns | Data is stored in documents, key-value pairs, or graph structures |
Scalability | Vertical scaling is common, but can also scale horizontally | Designed for horizontal scaling |
Data Integrity | Enforce referential integrity through constraints | No enforced data integrity |
Query Language | SQL (Structured Query Language) | Language specific to database |
ACID Compliance | ACID (Atomicity, Consistency, Isolation, Durability) compliant | Not necessarily ACID compliant |
Data Normalization | Follows normalization rules to reduce redundancy | Does not necessarily follow normalization rules |
Use cases | Best suited for structured data with well-defined relationships between data elements | Best suited for unstructured data or semi-structured data, such as log files, social media data, or documents |
Examples | MySQL. PostgreSQL. Oracle Database. Microsoft SQL Server. IBM Db2. MariaDB. | MongoDB, Apache Cassandra, Redis, Couchbase and Apache HBase. |
Author: Muhammad Jahanzaib
Relational Databases
Relational databases are databases that organize data into one or more tables, with each table consisting of columns and rows. The relationships between tables are established through primary keys and foreign keys.��
Key points:
Definition:
Author: Muhammad Jahanzaib
Introduction to Databases
Examples:
Best Practices::
Author: Muhammad Jahanzaib
Normalization
Normalization is the process of organizing data in a database in a way that minimizes redundancy and dependency. This helps improve data consistency, integrity, and efficiency.
Key points:
Definition:
Author: Muhammad Jahanzaib
Normalization
Examples:
Best Practices:
Author: Muhammad Jahanzaib
Normalization - First Normal Form (1NF)
First Normal Form (1NF) is a database normalization rule that requires each table to have a primary key and that each column in the table be atomic (i.e., not contain multiple values).
Key points:
Definition:
Author: Muhammad Jahanzaib
Normalization - First Normal Form (1NF)
Examples:
Best Practices::
Author: Muhammad Jahanzaib
Normalization - First Normal Form (1NF) - Interview Questions
Author: Muhammad Jahanzaib
Normalization - Second Normal Form (2NF)
Second Normal Form (2NF) is a database normalization rule that requires each non-key column in a table to be dependent on the entire primary key, not just a part of it.
Key points:
Definition:
Author: Muhammad Jahanzaib
Normalization - Second Normal Form (2NF)
Examples:
Best Practices::
Author: Muhammad Jahanzaib
Normalization - Second Normal Form (2NF) - Interview Questions
Author: Muhammad Jahanzaib
Normalization - Third Normal Form (3NF)
Third Normal Form (3NF) is a database normalization rule that requires all non-key columns in a table to be dependent only on the primary key or other non-key columns, but not on each other.
Key points:
Definition:
Author: Muhammad Jahanzaib
Normalization - Third Normal Form (3NF)
Examples:
Best Practices::
Author: Muhammad Jahanzaib
Normalization - Third Normal Form (3NF) - Interview Questions
Author: Muhammad Jahanzaib
Joins
Joins are used in relational databases to combine data from multiple tables based on a related column between them.
Types of joins:
Definition:
There are several types of joins in relational databases, including:�
Author: Muhammad Jahanzaib
Joins - INNER JOIN
It returns only the rows that have matching values in both tables being joined. Example: SELECT * FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id;
Definition:
Key Points:
Author: Muhammad Jahanzaib
Joins - INNER JOIN
Author: Muhammad Jahanzaib
Joins - LEFT JOIN (or LEFT OUTER JOIN)
LEFT JOIN, also known as LEFT OUTER JOIN, is a type of join operation in relational databases that combines the matching rows from two tables, as well as any unmatched rows from the left (or first) table.
Definition:
Key Points:
Author: Muhammad Jahanzaib
Joins - LEFT JOIN (or LEFT OUTER JOIN)
Author: Muhammad Jahanzaib
Joins - RIGHT JOIN (or RIGHT OUTER JOIN)
A RIGHT JOIN (or RIGHT OUTER JOIN) is a type of join operation in relational databases that returns all the rows from the right table and matching rows from the left table based on a specified join condition. In other words, a RIGHT JOIN returns all the rows from the right table, and if there are matching rows in the left table, those rows are also included in the result set. If there are no matching rows in the left table, NULL values are used for the corresponding columns in the result set.
Definition:
Key Points:
Author: Muhammad Jahanzaib
Joins - RIGHT JOIN (or RIGHT OUTER JOIN)
In this result table, you can see that all rows from the orders table are included, along with matching rows from the customers table based on the customer_id foreign key relationship. Rows from the customers table that don't have matching rows in the orders table are still included in the result, but with NULL values in the columns from the orders table.
Author: Muhammad Jahanzaib
Joins - FULL OUTER JOIN
A FULL OUTER JOIN returns all the matching rows from both tables, as well as the non-matching rows from both tables. If there are no matches, NULL values are returned.
Definition:
Key Points:
Best Practices:
Author: Muhammad Jahanzaib
Joins - FULL OUTER JOIN
Consider two tables, customers and orders, with the following structures:
Author: Muhammad Jahanzaib
Joins - CROSS JOIN (or CARTESIAN JOIN)
A Cross Join, also known as a Cartesian Join, is a type of join in which every row from one table is joined with every row from another table. In other words, it creates a Cartesian product of the two tables.
Definition:
Key Points:
Best Practices:
Author: Muhammad Jahanzaib
Joins - CROSS JOIN (or CARTESIAN JOIN)
Consider two tables, colors and sizes, with the following structures:
Author: Muhammad Jahanzaib
Joins - SELF JOIN
A self join is a regular join, but the table is joined with itself. It is useful when a table has a hierarchical structure or when there is a need to compare rows within the same table.
Definition:
Key Points:
Best Practices:
Author: Muhammad Jahanzaib
Keys in Databases
Keys in databases are used to uniquely identify a row in a table or to establish a relationship between two or more tables. They are constraints that enforce data integrity and ensure that the data is consistent and accurate
Following are the types of keys:
Author: Muhammad Jahanzaib
Keys - Primary Key
A primary key is a column or combination of columns in a table that uniquely identifies each row in the table. It is used to enforce data integrity and ensure that no two rows in a table are identical.
Definition:
Key Points:
Best Practices:
Author: Muhammad Jahanzaib
Keys - Foreign key
A foreign key is a field or set of fields in one table that uniquely identifies a row of another table or the same table.
Definition:
Key Points:
Best Practices:
Author: Muhammad Jahanzaib
Keys - Unique key
A Unique Key is a database constraint that ensures that the values in a column or a group of columns are unique across all the rows in the table. Unlike the primary key, a table can have multiple unique keys. A unique key constraint ensures that no two rows in a table have the same values in the specified column(s).
Definition:
Key Points:
Best Practices:
Author: Muhammad Jahanzaib
Keys - Composite key
A composite key is a combination of two or more columns in a table that together uniquely identifies each row in the table. It is also referred to as a composite primary key. In other words, a composite key is a set of columns that, when combined, uniquely identify a row in a table.
Definition:
Key Points:
Best Practices:
Author: Muhammad Jahanzaib
Keys - Composite key
A composite key is a primary key that consists of multiple columns in a table. Instead of using a single column to uniquely identify each row in a table, a composite key uses two or more columns to form a unique identifier.
Definition:
Key Points:
Best Practices:
Author: Muhammad Jahanzaib
Keys - Super key
A super key is a set of one or more attributes that, taken collectively, allows us to identify a unique record within a table. It is a combination of attributes that can be used to uniquely identify each record in a table. A super key can contain additional attributes beyond those that are minimally required for unique identification.
Definition:
Key Points:
Best Practices:
Author: Muhammad Jahanzaib
Indexes
Indexes in databases are database objects that allow for fast data retrieval operations. They work by creating a data structure that contains a subset of the data in the table, sorted in a way that makes it easy to search for specific values. This data structure is then used by the database engine to quickly locate the rows that match a specific query.
Definition:
Key Points:
Author: Muhammad Jahanzaib
Indexes
Following are some types of indexes:
Single-Column Index: This type of index is created on a single column of a table. For example, if you have a table with a large number of rows, and you frequently search for data in a specific column, you can create a single-column index on that column to speed up the search.
Composite Index: A composite index is created on two or more columns of a table. This type of index is useful when you frequently search for data based on multiple columns. For example, if you have a table with columns for first name, last name, and age, you can create a composite index on all three columns to speed up searches that involve all three fields.
Unique Index: A unique index is similar to a single-column index, but it enforces a unique constraint on the indexed column or columns. This means that the indexed columns must contain only unique values. Unique indexes are commonly used for primary and foreign keys.
Full-Text Index: A full-text index is used to search for text-based data, such as articles, blog posts, or other types of unstructured text. It allows you to search for keywords and phrases within the text, and can be useful for applications such as search engines and content management systems.
Author: Muhammad Jahanzaib
Indexes - Clustered vs Non-Clustered indexes
Structure
Clustered indexes determine the physical order of data rows in a table while non-clustered indexes have a separate structure that maps the index key values to the corresponding data rows.
Query performance
Clustered indexes are generally faster for queries that retrieve a range of data in the clustered index order. Non-clustered indexes are better suited for queries that retrieve a small set of rows based on specific column values.
Number of indexes
A table can have only one clustered index but multiple non-clustered indexes.
Maintenance
When you insert new data into a table with a clustered index, the data is physically inserted in the corresponding order in the clustered index. This means that the entire index may need to be rebuilt periodically. Non-clustered indexes are less affected by inserts and updates as they only contain a copy of the index key values and a pointer to the corresponding data rows.
Author: Muhammad Jahanzaib
Indexes - Clustered vs Non-Clustered indexes
Storage
Clustered indexes consume less storage than non-clustered indexes as they are integrated with the table structure.
Primary key
Clustered indexes are typically created on the primary key of a table, while non-clustered indexes can be created on any column.
Sorting
Clustered indexes maintain the sort order of the table rows while non-clustered indexes do not.
Fragmentation
Non-clustered indexes can suffer from fragmentation which can impact performance. Clustered indexes are less susceptible to fragmentation.
Author: Muhammad Jahanzaib
Transactions
Transactions are a fundamental concept in databases that ensure the consistency and reliability of data. A transaction is a series of operations or actions that are performed as a single unit of work. These actions must be executed in full or not at all. Some key points about transactions include:
Definition:
Atomicity
A transaction is an atomic unit of work, meaning that it must either succeed completely or fail completely. If a transaction fails, all the changes made during that transaction must be rolled back to their previous state.
Consistency
A transaction must leave the database in a consistent state. This means that the database must follow all the integrity constraints, such as primary key, foreign key, unique key, etc.
Isolation
Transactions should be isolated from each other to avoid conflicts. Each transaction should be able to work independently without affecting others.
Durability
Once a transaction is committed, the changes made during that transaction should be permanent and not affected by any system failure.
Author: Muhammad Jahanzaib
What is SQL?
Definition:
Key Points:
Author: Muhammad Jahanzaib
SQL - CRUD Operations
CRUD is an acronym for Create, Read, Update and Delete. Each of these performs different operations, but they all aim to track and manage data, from a database, API, or whatever. �When creating a database or building APIs, you will want users to be able to manipulate any data available either by fetching these data, updating the data, deleting them, or adding more data. These operations are made possible through CRUD operations.
Definition:
Author: Muhammad Jahanzaib
Transactions - Example
Let's say a customer wants to transfer $100 from their checking account to their savings account. Here are the steps involved in this transaction:
Author: Muhammad Jahanzaib
Transactions - Interview Questions
Author: Muhammad Jahanzaib
Triggers
Triggers are special types of stored procedures that are automatically executed in response to certain database events or changes. They are used to enforce business rules or policies and maintain data consistency and integrity.. Here are some examples of triggers:
Definition:
Audit Trail Trigger
This trigger is used to create an audit trail of all the changes made to a specific table. The trigger is fired whenever an insert, update, or delete operation is performed on the table.
Validation Trigger
This trigger is used to validate the data being inserted or updated in a table. The trigger is fired before the insert or update operation is performed and can be used to enforce complex business rules or data validation rules.
Cascade Trigger
This trigger is used to implement cascading updates or deletes on related tables. For example, if a record in the parent table is deleted, all related records in the child table(s) are also deleted.
Security Trigger
This trigger is used to implement security measures on a database. For example, a trigger can be created to prevent unauthorized access to a specific table or to log all attempts to access the database.
Author: Muhammad Jahanzaib
Stored Procedures
A stored procedure is a pre-written program that is stored in a database and can be executed on demand. It is a set of SQL statements that are compiled and stored in the database, and it can be executed by invoking the procedure name.
Definition:
Key Points:
Best Practices:
Author: Muhammad Jahanzaib
Stored Procedures - Example
An example of a stored procedure might be a procedure that calculates the average salary of all employees in a given department. The steps to create and execute this stored procedure might look like this:
2. Execute the stored procedure:
This would calculate and return the average salary of all employees in department 2
Author: Muhammad Jahanzaib
Triggers & Stored Procedures - Interview Questions
Author: Muhammad Jahanzaib
Views
Views in databases are virtual tables that allow users to query data from one or more existing tables in a simplified manner. Instead of writing complex SQL queries every time, views allow users to define a set of commonly used joins and filters as a single view, which can then be queried as if it were a real table. �Some of the key benefits of using views include:
Definition:
Simplified data access
Views provide a simplified way of accessing data from multiple tables without the need to write complex SQL queries every time
Enhanced security:
Views can be used to limit access to specific columns or rows of data, providing an additional layer of security to the database.
Improved performance:
Views can be used to precompute the results of complex queries, which can improve the performance of frequently executed queries.
Improved maintainability:
Views encapsulate complex logic or frequently used queries, making database maintenance and updates easier. View definitions can be modified without affecting the underlying tables or applications, reducing the time and effort required for development and maintenance.
Author: Muhammad Jahanzaib
Views - Example
Consider a database with two tables: Employees and Departments. The Employees table contains information about all the employees in the company, while the Departments table contains information about all the departments in the company. �To create a view that shows the names and salaries of all employees in the Sales department, we can use the following SQL query:
This query will return a result set containing the first name, last name, and salary of all employees in the Sales department.
This view will create a virtual table called SalesEmployees that contains the first name, last name, and salary of all employees in the Sales department. The view can then be queried using a simple SELECT statement:
Author: Muhammad Jahanzaib
Views - Interview Questions
Author: Muhammad Jahanzaib
Aggregate Functions
Aggregate functions are SQL functions that operate on a group of rows and return a single value as a result. They are commonly used in SQL queries to perform calculations and summaries on data. �Here are some common aggregate functions:
Applications:
Aggregate functions are useful for data analysis and reporting as they allow us to perform calculations and summarize data quickly and easily.
Author: Muhammad Jahanzaib
Aggregate Functions with GROUP BY Clause
Aggregate functions can also be used in combination with the GROUP BY clause to group the data based on certain criteria and perform calculations on the grouped data. For example, to find the total sales amount by product category, we can use the SUM function with the GROUP BY clause on the product category column.
An example of using the SUM function to calculate the total sales amount for each product category:
This query selects the product_category column and the sum of sales_amount column for each distinct product_category value in the sales table. The GROUP BY clause groups the data based on the product_category column, and the SUM function calculates the total sales amount for each group. The AS keyword is used to alias the result column as total_sales_amount.
Author: Muhammad Jahanzaib
Aggregate Functions - Interview Questions
Author: Muhammad Jahanzaib
Sub Queries
Subqueries are queries that are embedded inside another query and can be used to retrieve data that will be used as a part of the main query. The subquery is enclosed in parentheses and can be used in various parts of the main query such as the SELECT, FROM, WHERE, and HAVING clauses. Subqueries can be used to perform complex queries and are particularly useful when the result of one query is used as a filter for another query.
Definition:
Key Points:
Author: Muhammad Jahanzaib
Sub Queries - Example
Retrieving data using a subquery in the WHERE clause:
This query retrieves all orders made by customers from the USA. The subquery in the WHERE clause retrieves the IDs of customers from the USA, which are then used to filter the orders table.
Author: Muhammad Jahanzaib