The Ultimate RDBMS & MySQL Study

Guide
Table of Contents
Part 1: Foundational Concepts
1.​ Introduction to RDBMS
○​ 1.1. What is a Database?
○​ 1.2. The Role of a DBMS
○​ 1.3. The Evolution of Data Models (Hierarchical, Network, Relational)
○​ 1.4. Database Users
2.​ The Relational Model: A Deep Dive
○​ 2.1. Core Components: Tables, Tuples, and Attributes
○​ 2.2. Relational Terminology (Domain, Degree, Cardinality)
○​ 2.3. The Power of Keys (Primary, Foreign, Unique, Composite)

Part 2: The SQL Language

3.​ Introduction to SQL
○​ 3.1. What is SQL?
○​ 3.2. SQL Subsets: DDL, DML, DCL, and TCL
○​ 3.3. SQL Syntax and Case Sensitivity
4.​ Data Definition Language (DDL)
○​ 4.1. MySQL Data Types
○​ 4.2. CREATE TABLE: Building Your Database Structure
○​ 4.3. Constraints: Enforcing Data Integrity
○​ 4.4. ALTER TABLE: Modifying Existing Tables
○​ 4.5. TRUNCATE TABLE: Fast Deletion of All Rows
○​ 4.6. DROP TABLE: Permanently Removing a Table
5.​ Data Manipulation Language (DML)
○​ 5.1. INSERT INTO: Adding New Data
○​ 5.2. UPDATE: Modifying Existing Data
○​ 5.3. DELETE FROM: Removing Specific Rows
○​ 5.4. MySQL's MERGE Alternative: INSERT ... ON DUPLICATE KEY UPDATE
6.​ Transaction Control Language (TCL)
○​ 6.1. Understanding Transactions and ACID Properties
○​ 6.2. COMMIT: Making Changes Permanent
○​ 6.3. ROLLBACK: Undoing Changes
○​ 6.4. SAVEPOINT: Creating Intermediate checkpoints

Part 3: Advanced Data Retrieval

7.​ The SELECT Statement: Mastering Queries
○​ 7.1. Basic SELECT Syntax
○​ 7.2. The WHERE Clause: Filtering Data
○​ 7.3. The ORDER BY Clause: Sorting Results
○​ 7.4. The DISTINCT Keyword: Eliminating Duplicates
○​ 7.5. The LIMIT Clause: Constraining Results
8.​ SQL Functions: Scalar & Aggregate
○​ 8.1. Scalar (Single-Row) Functions
○​ 8.2. Aggregate (Group) Functions
○​ 8.3. The GROUP BY Clause: Summarizing Data
○​ 8.4. The HAVING Clause: Filtering Grouped Data
9.​ Joining Multiple Tables
○​ 9.1. The Concept of Joins
○​ 9.2. INNER JOIN (Equijoin)
○​ 9.3. OUTER JOIN (LEFT and RIGHT)
○​ 9.4. SELF JOIN
○​ 9.5. Other Join Types (CROSS JOIN, NATURAL JOIN)
○​ 9.6. Non-Equijoins
10.​Subqueries: Nested Queries
○​ 10.1. What is a Subquery?
○​ 10.2. Types of Subqueries (Single-Row, Multi-Row, Correlated)
○​ 10.3. Using Subqueries in Different Clauses

Part 4: Database Objects and Security

11.​Advanced Database Objects
○​ 11.1. Views: Stored Queries
○​ 11.2. Indexes: Performance Boosters
12.​Data Control Language (DCL)
○​ 12.1. User Privileges and Security
○​ 12.2. GRANT: Assigning Privileges
○​ 12.3. REVOKE: Removing Privileges
Part 1: Foundational Concepts
1. Introduction to RDBMS
1.1. What is a Database?
A database is a shared, organized collection of logically related data, designed to meet the
information needs of an organization. The key terms here are:
●​ Organized: Data is not stored randomly; it follows a specific structure or model.
●​ Logically Related: The data items have defined relationships with each other (e.g., a
customer is related to the orders they place).
●​ Shared: It is a central repository accessible by multiple users and applications.
The statement that a database is a collection of "logically unrelated data" is False. The logical
relationship between data is a fundamental aspect.

1.2. The Role of a DBMS

A Database Management System (DBMS) is the software that allows users to create,
manage, and interact with a database. It acts as an intermediary between the user and the
physical database. MySQL, Oracle, and Sybase are all examples of popular Relational DBMS
(RDBMS).

1.3. The Evolution of Data Models

A data model defines the logical structure of a database. It determines how data is stored,
organized, and manipulated.
●​ Hierarchical Model: Data is organized in a tree-like structure. Each record has one
parent, and data is accessed via a top-down path. Think of a file system on a computer.
●​ Network Model: An extension of the hierarchical model, allowing a record to have
multiple parent records. This creates a more flexible graph-like structure.
●​ Relational Model: This is the most widely used model today. Data is organized into
tables (also called relations), which are collections of rows and columns. This model is
simple, flexible, and powerful.
●​ Conceptual Model: A high-level description of the database that is independent of any
specific DBMS. It focuses on identifying entities, attributes, and relationships.
1.4. Database Users
Different types of users interact with a database in different ways:
●​ End User: Interacts with the database through a front-end application, often without
knowing the underlying details of the database structure. A person using a banking app is
an end user.
●​ Application Programmer: Develops applications that interact with the database. They
write the code (e.g., in Python, Java) that sends SQL queries to the DBMS. Tom, who
designed the payroll software, is an Application Programmer.
 ●​ Database Administrator (DBA): Responsible for the overall management of the
database, including security, performance, backup, and recovery.

2. The Relational Model: A Deep Dive

2.1. Core Components: Tables, Tuples, and Attributes
In the relational model, data is represented in two-dimensional tables.
●​ Table (or Relation): A collection of data about a specific entity (e.g., Employees,
Products).
●​ Attribute (or Column): A named column of a table that represents a property of the
entity (e.g., EmployeeName, Price). The column header is referred to as an Attribute.
●​ Tuple (or Row/Record): A single row in a table, representing a single instance of the
entity (e.g., one specific employee's data).
2.2. Relational Terminology
●​ Domain: The set of all possible legal values that an attribute can hold. For example, the
domain for a Gender attribute might be {'Male', 'Female', 'Other'}.
●​ Degree: The number of attributes (columns) in a table.
●​ Cardinality: The number of tuples (rows) in a table.

Term Refers to... Example (in a 5-column,

10-row table)

Attribute A column One of the 5 columns

Tuple A row One of the 10 rows

Degree Number of columns 5

Cardinality Number of rows 10

Domain Range of valid values E.g., All positive integers for

EmployeeID

2.3. The Power of Keys

Keys are special attributes used to uniquely identify tuples and establish relationships
between tables. A relational database consists of a collection of Tables.
●​ Primary Key (PK): An attribute (or set of attributes) that uniquely identifies each row in a
table.
○​ It cannot contain NULL values.
○​ Its values must be unique.
○​ A table can have only one primary key.
 ○​ Example: creditcardno is a perfect candidate for a primary key in a credit card table.
●​ Foreign Key (FK): An attribute in one table that refers to the primary key of another
table. It is the cornerstone of referential integrity.
○​ It links tables together.
○​ It can contain NULL values.
○​ Example: If we have an Orders table, a CustomerID column in it would be a foreign
key referencing the CustomerID primary key in the Customers table.
●​ Unique Key: Ensures that all values in a column (or a set of columns) are unique.
○​ Unlike a primary key, a unique key constraint can accept one NULL value.
○​ A table can have multiple unique keys.
○​ Example: cardholderphoneno could be a unique key, as two cardholders shouldn't
have the same phone number.
●​ Composite Key: A primary key that consists of two or more attributes working together
to uniquely identify a row.
Part 2: The SQL Language
3. Introduction to SQL
3.1. What is SQL?
SQL (Structured Query Language) is the standard language for communicating with relational
databases. It's used to perform tasks such as retrieving data, inserting new data, and
managing the database structure.

3.2. SQL Subsets: DDL, DML, DCL, and TCL

SQL commands are categorized into subsets:
●​ Data Definition Language (DDL): Defines the database schema.
○​ Commands: CREATE, ALTER, DROP, TRUNCATE.
●​ Data Manipulation Language (DML): Used for accessing and manipulating data.
○​ Commands: SELECT, INSERT, UPDATE, DELETE.
●​ Data Control Language (DCL): Manages user access and permissions.
○​ Commands: GRANT, REVOKE.
●​ Transaction Control Language (TCL): Manages transactions in the database.
○​ Commands: COMMIT, ROLLBACK, SAVEPOINT.
3.3. SQL Syntax and Case Sensitivity
SQL is generally case-insensitive with respect to its keywords, commands, and table/column
names (though this can depend on the OS and database configuration). However, the data
stored inside the tables is case-sensitive. So, the statement "SQL is a case sensitive
language" is False. SELECT is the same as select, but the data 'John' is different from 'john'.

4. Data Definition Language (DDL)

DDL statements are used to build and modify the structure of your tables and other database
objects.

4.1. MySQL Data Types

●​ Numeric:
○​ INT: For whole numbers.
○​ BIGINT: For very large whole numbers.
○​ DECIMAL(p, s): For exact fixed-point numbers. Essential for financial data.
○​ FLOAT, DOUBLE: For approximate floating-point numbers.
●​ String:
○​ VARCHAR(n): For variable-length strings up to n characters.
○​ CHAR(n): For fixed-length strings of n characters.
○​ TEXT: For long-form text.
●​ Date and Time:
 ○​ DATE: Stores a date in YYYY-MM-DD format (the default).
○​ DATETIME: Stores both date and time.
○​ TIMESTAMP: Stores a timestamp, often used for tracking creation/update times.
4.2. CREATE TABLE: Building Your Database Structure
This command creates a new table in the database.

CREATE TABLE CreditCards (​

CreditCardNo BIGINT PRIMARY KEY,​
CardHolderName VARCHAR(100) NOT NULL,​
CardHolderPhoneNo VARCHAR(15) UNIQUE,​
CreditCardType VARCHAR(10),​
ValidityDate DATE NOT NULL,​
CreditLimit DECIMAL(10, 2)​
);​

4.3. Constraints: Enforcing Data Integrity

Constraints are rules applied to columns to ensure the accuracy and reliability of data.
●​ NOT NULL: Ensures a column cannot have a NULL value.
●​ UNIQUE: Ensures all values in a column are different.
●​ PRIMARY KEY: A combination of NOT NULL and UNIQUE.
●​ FOREIGN KEY: Establishes a link between tables, enforcing referential integrity. When
deleting rows, you can specify reference options:
○​ RESTRICT (Default): Prevents deletion if a child row exists.
○​ CASCADE: Deletes the corresponding child rows.
○​ SET NULL: Sets the foreign key in the child rows to NULL.
●​ CHECK: Ensures that all values in a column satisfy a specific condition.
○​ Example: To ensure the amount withdrawn is less than the credit card limit, a table
level check constraint would be used, as it needs to compare two different
columns.
○​ Example: To ensure CreditCardType is only 'Silver' or 'Platinum':​
ALTER TABLE CreditCards​
ADD CONSTRAINT chk_cardtype CHECK (CreditCardType IN ('Silver', 'Platinum'));​

4.4. ALTER TABLE: Modifying Existing Tables

This command is used to add, delete, or modify columns in an existing table.

-- Add a new column​

ALTER TABLE emp ADD COLUMN email VARCHAR(50);​
​
-- Modify a column's data type​
ALTER TABLE emp MODIFY COLUMN name VARCHAR(30);​
​
-- Drop a column​
ALTER TABLE emp DROP COLUMN salary;​

The option alter emp drop column_name; is incorrect syntax; it should be ALTER TABLE....

4.5. TRUNCATE TABLE: Fast Deletion of All Rows

TRUNCATE removes all rows from a table quickly.
●​ It is a DDL command.
●​ It is faster than DELETE because it doesn't log individual row deletions.
●​ It resets any auto-incrementing counters.
●​ You cannot roll back a TRUNCATE command.
4.6. DROP TABLE: Permanently Removing a Table
DROP removes the entire table, including its structure, data, and indexes.
●​ It is a DDL command.
●​ This operation cannot be undone (rolled back).

5. Data Manipulation Language (DML)

DML statements are used to manage the data within schema objects.

5.1. INSERT INTO: Adding New Data

This statement inserts a new row into a table.

-- Syntax 1: Specify columns and values​

INSERT INTO student (stud_id, name, address, dob)​
VALUES (101, 'Smith', '100 Main Street', '1994-02-01');​
​
-- Syntax 2: Provide values for all columns in order​
INSERT INTO student VALUES (102, 'Jones', '200 Park Ave', '1995-03-15');​

If a NOT NULL column is omitted from an INSERT statement (like phoneno in the quiz
example), the statement will fail.

5.2. UPDATE: Modifying Existing Data

This statement modifies existing rows in a table.
-- Give a 10% raise to all employees in department 10​
UPDATE employee​
SET salary = salary * 1.10​
WHERE department_id = 10;​

5.3. DELETE FROM: Removing Specific Rows

This statement removes existing rows from a table.
●​ It is a DML command.
●​ It can be rolled back.
●​ It is slower than TRUNCATE for large tables.
-- Remove all data from the employee table (can be rolled back)​
DELETE FROM employee;​

5.4. MySQL's MERGE Alternative: INSERT ... ON DUPLICATE KEY UPDATE

While standard SQL has a MERGE statement to conditionally insert or update, MySQL does
not support it. The equivalent functionality is achieved using INSERT ... ON DUPLICATE KEY
UPDATE. This is True.

-- If an employee with ID 123 already exists, update their name.​

-- Otherwise, insert a new row.​
INSERT INTO employee (empid, empname) VALUES ('123', 'John')​
ON DUPLICATE KEY UPDATE empname = 'John';​

6. Transaction Control Language (TCL)

6.1. Understanding Transactions
A transaction is a sequence of operations performed as a single logical unit of work.

6.2. COMMIT: Making Changes Permanent

The COMMIT command ends the current transaction and makes all pending changes
permanent. This is True.

6.3. ROLLBACK: Undoing Changes

The ROLLBACK command undoes all the changes made since the beginning of the
transaction or a SAVEPOINT.

6.4. SAVEPOINT: Creating Intermediate checkpoints

A SAVEPOINT is a marker within a transaction that you can roll back to.

Example from Quiz:

ROLLBACK TO SAVEPOINT a; would undo all operations performed after savepoint a, which
includes the DELETE statement and the UPDATE statement.
Part 3: Advanced Data Retrieval
7. The SELECT Statement: Mastering Queries
7.1. Basic SELECT Syntax
The SELECT statement is used to query the database and retrieve data.

-- Retrieve specific columns​

SELECT empname, salary FROM employee;​
​
-- Retrieve all columns​
SELECT * FROM employee;​

7.2. The WHERE Clause: Filtering Data

The WHERE clause is used to filter records and extract only those that fulfill a specified
condition.
●​ BETWEEN ... AND: Selects values within a given range (inclusive).​
SELECT name, salary FROM employee WHERE salary BETWEEN 40000 AND 100000;​
-- This is equivalent to:​
SELECT name, salary FROM employee WHERE salary >= 40000 AND salary <= 100000;​

●​ IN (set): Matches any of a list of values.

●​ LIKE: Used for pattern matching in strings.
○​ %: Matches any sequence of zero or more characters.
○​ _: Matches any single character.
○​ Example: To find employees whose last name is 'kumar':​
SELECT * FROM employee WHERE empname LIKE '%kumar';​

●​ IS NULL / IS NOT NULL: Checks for NULL values. You must use IS NULL, not = NULL.
○​ Example: To find employees who have a manager:​
SELECT empname, manager_id FROM employee WHERE manager_id IS NOT NULL;​

7.3. The ORDER BY Clause: Sorting Results

Used to sort the result set in ascending (ASC) or descending (DESC) order.
●​ Default sort order is ASC.
●​ In a character sort, values are case-sensitive by default in many databases.
●​ NULL values are displayed first when sorting in ascending order. This statement is True.
7.4. The DISTINCT Keyword: Eliminating Duplicates
The DISTINCT keyword is used to return only unique values.

-- Retrieve department IDs without duplication​

SELECT DISTINCT department_id FROM employee;​

7.5. The LIMIT Clause: Constraining Results

A MySQL-specific clause to restrict the number of rows returned.

-- Get the top 5 highest-paid employees​

SELECT empname, salary FROM employee ORDER BY salary DESC LIMIT 5;​

8. SQL Functions: Scalar & Aggregate

8.1. Scalar (Single-Row) Functions
These functions operate on a single row and return one result per row. They can be nested.
●​ String Functions:
○​ SUBSTR(string, start, length): Extracts a substring.​
SELECT SUBSTR("Oracle World", 1, 6) returns 'Oracle'.
●​ Numeric Functions:
○​ ROUND(number, decimals): Rounds a number.
○​ CEIL(number): Rounds a number up to the nearest integer. SELECT CEIL(5.3) returns
6.
●​ Control Flow / Null-Related Functions:
○​ IFNULL(expr1, expr2): If expr1 is not NULL, it returns expr1; otherwise, it returns expr2.
This is MySQL's equivalent of Oracle's NVL.
○​ Example:​
-- If credit limit is NULL, display 'Not Available'​
SELECT IFNULL(cust_credit_limit * 1.15, 'Not Available') AS "NEW CREDIT"​
FROM customers;​

8.2. Aggregate (Group) Functions

These functions operate on a set of rows and return a single result for the entire group. They
ignore NULL values.
●​ COUNT(): Counts the number of rows.
●​ SUM(): Calculates the sum of values.
●​ AVG(): Calculates the average of values.
●​ MAX(): Returns the maximum value.
●​ MIN(): Returns the minimum value.
Important Rule: Group functions cannot be used in the WHERE clause. This is False. They
must be used in the HAVING clause.

8.3. The GROUP BY Clause: Summarizing Data

The GROUP BY statement groups rows that have the same values into summary rows. It is
often used with aggregate functions.

Rule: All columns in the SELECT list that are not in group functions must be in the GROUP BY
clause. This is True.

-- Display the highest credit limit for each income level in each city​
SELECT cust_city, cust_income_level, MAX(cust_credit_limit)​
FROM customers​
GROUP BY cust_city, cust_income_level;​

8.4. The HAVING Clause: Filtering Grouped Data

The HAVING clause was added to SQL because the WHERE keyword could not be used with
aggregate functions.

-- Find departments with more than 5 employees​

SELECT department_id, COUNT(employee_id)​
FROM employees​
GROUP BY department_id​
HAVING COUNT(employee_id) > 5;​

9. Joining Multiple Tables

Joins are used to combine rows from two or more tables based on a related column between
them.

9.2. INNER JOIN (Equijoin)

Returns records that have matching values in both tables. This is the most common type of
join.

SELECT e.last_name, d.department_name​

FROM employees e​
JOIN departments d ON e.department_id = d.department_id;​

9.3. OUTER JOIN (LEFT and RIGHT)

Used when you want to retrieve records that do not meet the join condition (unmatched data)
from one of the tables.
●​ LEFT JOIN: Returns all records from the left table, and the matched records from the
right table.
●​ RIGHT JOIN: Returns all records from the right table, and the matched records from the
left table.
Outer joins are essential when tables have both matched and unmatched data.

9.4. SELF JOIN

A join where a table is joined to itself. This is useful for querying hierarchical data or
comparing rows within the same table.

9.5. Other Join Types

●​ CROSS JOIN: Produces the Cartesian product of two tables (every row from the first
table combined with every row from the second).
●​ NATURAL JOIN: Joins tables based on all columns with the same name.
●​ USING Clause: A shorthand for the ON clause when the columns to be joined have the
same name in both tables and compatible data types.
9.6. Non-Equijoins
A join condition that uses an operator other than equals (=), such as BETWEEN, >, or <. The
equal operator (=) is not appropriate for a non-equijoin.

10. Subqueries: Nested Queries

10.1. What is a Subquery?
A subquery is a SELECT statement nested inside another SQL statement.

10.2. Types of Subqueries

●​ Single-Row Subquery: Returns only one row. Can use standard comparison operators
(=, >, etc.).
●​ Multi-Row Subquery: Returns more than one row. Must use multi-row operators like IN,
ANY, ALL.
○​ IN: Equal to any member in the list.
○​ ANY: Compares a value to each value in a list. Must be preceded by a comparison
operator.
○​ ALL: Compares a value to every value in a list.
○​ The NOT operator can be used with IN, ANY, and ALL.
Example: Find employees who earn more than the average salary.

-- You cannot use AVG(salary) directly in a WHERE clause.​

-- You must use a subquery.​
SELECT last_name, salary​
FROM employee​
WHERE salary > (SELECT AVG(salary) FROM employee);​
Part 4: Database Objects and Security
11. Advanced Database Objects
11.1. Views: Stored Queries
A view is a virtual table based on the result-set of an SQL statement. It contains rows and
columns, just like a real table, but it is a stored query. Views are useful for simplifying complex
queries, providing a layer of security, and presenting data differently.

-- Remove a view​
DROP VIEW emp_dept_vu;​

11.2. Indexes: Performance Boosters

An index is a special lookup table that the database search engine can use to speed up data
retrieval. While indexes dramatically speed up SELECT queries, they can slow down data
modification (INSERT, UPDATE, DELETE) because the index must also be updated.

An index on dept_id may reduce the amount of disk I/O for SELECT statements.

12. Data Control Language (DCL)

DCL commands are used to control access to data within the database.
●​ The owner of an object has all privileges on that object (True) and can grant specific
privileges to others (True).
12.2. GRANT: Assigning Privileges
The GRANT statement gives specific permissions to users.

-- Grant SELECT permission on the department table to user 'bob'​

GRANT SELECT ON department TO 'bob'@'localhost';​
​
-- Grant a privilege to all database users using PUBLIC​
GRANT SELECT ON department TO PUBLIC;​