Comprehensive RDBMS and MySQL Study Guide
Relational Database Management System (RDBMS) Concepts
A database is an organized collection of related data stored in a computer system so that it can be easily accessed, managed, and updated. A Database Management System (DBMS) is the software program used to create, maintain, and manipulate these databases. Common commercial and open-source DBMS software includes MySQL, Oracle, and Microsoft Access. Real-life application domains that depend on databases include school libraries, hospitals, banking systems, and defense or police departments.
A DBMS serves multiple core operational functions:
- Storing data in a structured tabular format.
- Querying the database to extract specific information.
- Sorting and manipulating saved records.
- Validating input data and enforcing integrity constraints to avoid inconsistencies.
- Producing flexible onscreen or printed reports.
A Relational Database is a database architecture that stores data across separate tables that are linked or related through common columns known as keys. MySQL is a freely available, open-source Relational Database Management System (RDBMS) that utilizes Structured Query Language (SQL). Inside a MySQL database, information is stored in tables; a single database can house thousands of individual records across numerous tables.
Structured Query Language (SQL) is the standard language used to create, query, and operate relational databases.
Relational Database Terminology
- Relation: A table storing logically related data.
- Tuple: A single row within a relation.
- Attribute: A column within a relation.
- Degree: The total number of attributes (columns) present in a relation.
- Cardinality: The total number of tuples (rows) present in a relation.
- Primary Key: A set of one or more attributes that uniquely identifies each tuple within a relation.
- Candidate Key: All attribute combinations within a relation that are capable of serving as a primary key.
- Alternate Key: Any candidate key that has not been selected as the primary key.
- Foreign Key: A non-key attribute in a child table whose values are derived from the primary key of another parent table.
- Referential Integrity: A system of rules used by an RDBMS to guarantee that relationships between records in related tables remain valid, preventing accidental deletion or modification of related data. Referential integrity is strictly enforced via foreign keys.
Classification of SQL Statements and MySQL Elements
SQL statements are categorized into functional sub-languages:
- Data Definition Language (DDL): Commands that define, alter, or manage database structures. Examples include creating, altering, and dropping tables, granting and revoking roles or privileges, and general maintenance commands.
- Data Manipulation Language (DML): Commands that perform data operations including retrieval, insertion, deletion, and modification of data stored inside database tables.

MySQL Basic Elements
MySQL language elements are divided into four primary components: Literals, Datatypes, Nulls, and Comments.

Literals
A literal refers to a fixed data value, which can be character-based or numeric:
- Character Literals: Fixed string values enclosed in single or double quotes (e.g.,
'replay','Raj','8','306'). - Numeric Literals: Raw numbers not enclosed in quotation marks (e.g., , , ).
- Integer Literals: Numbers without fractional or decimal components (e.g., ).
- Real Literals: Numbers containing a explicit decimal point (e.g., , ).
Data Types
Data types specify the kind of data a column can hold and determine the valid operations that can be performed on it.

Numeric Data Types:
int: Used for integer values without decimal places.Decimal(m,d): Used for floating-point or real numbers, where denotes the total number of digits (precision) and represents the number of digits to the right of the decimal point (scale).
Date and Time Data Types:
date: Stores calendar dates in the standardYYYY-MM-DDformat.time: Stores time values in the standardHH:MM:SSformat.
String Data Types:
char(m): Fixed-length string storing up to a maximum of characters.varchar(m): Variable-length string storing up to a maximum of characters.
Comparison: CHAR vs VARCHAR Data Types

| S.NO. | Char Datatype | Varchar Datatype |
|---|---|---|
| 1. | Specifies a fixed-length character string. | Specifies a variable-length character string. |
| 2. | When defined as CHAR(n), MySQL ensures all values occupy exactly bytes. If an inserted value is shorter than length , trailing blanks/spaces are padded, maintaining a constant size of bytes. | When defined as VARCHAR(n), the maximum storage size is bytes. Values are stored using only the exact space required for the text inserted; no blank padding is added for shorter strings. |
Null Values
If a column in a specific row contains no data, it is said to be NULL or contain a null value. NULL represents a missing, unknown, or inapplicable data value.
Database and Table Execution Commands
Database Operations
To list all databases currently available on the server:
SHOW DATABASES;
To create a new database:
CREATE DATABASE <databasename>;
-- Example:
CREATE DATABASE Student;
To select and access an existing database:
USE <databasename>;
-- Example:
USE Student;
To delete an existing database entirely:
DROP DATABASE <databasename>;
-- Example:
DROP DATABASE Student;
To list all tables present within the currently selected database:
SHOW TABLES;
Table Creation
Tables are defined using the CREATE TABLE statement by specifying column names, data types, and optional constraints.
Syntax:
CREATE TABLE <table-name> (
<column_name> <data_type>,
<column_name> <data_type>,
...
);
Example - Creating the EMPLOYEE table:
CREATE TABLE employee (
ECODE integer,
ENAME varchar(20),
GENDER char(1),
GRADE char(2),
GROSS integer
);
Inserting Data into Tables
Data is added to tables using the INSERT INTO command.
Syntax:
INSERT INTO <tablename> [<column_list>]
VALUES (<value1>, <value2>, ...);
Examples:
INSERT INTO employee
VALUES (1001, 'Ravi', 'M', 'E4', 50000);
-- Alternative explicit column insertion:
INSERT INTO employee (ECODE, ENAME, GENDER, GRADE, GROSS)
VALUES (1001, 'Ravi', 'M', 'E4', 50000);
INSERT INTO employee
VALUES (1002, 'Akash', 'M', 'A1', 35000);
Inserting NULL Values:
To leave a field empty, write NULL without quotation marks.
INSERT INTO employee
VALUES (1004, NULL, 'M', 'B2', 38965);
Data Retrieval Operations with SELECT Query
The SELECT statement retrieves stored records from a database.
Syntax:
SELECT <column_name1>, <column_name2>
FROM <tablename>
WHERE <condition>;
Query Variations
Retrieving All Columns
To retrieve every attribute for all records, use the asterisk (*) wildcard:
SELECT * FROM employee;
Given an EMPLOYEE table populated with:
| ECODE | ENAME | GENDER | GRADE | GROSS |
|---|---|---|---|---|
| 1001 | Ravi | M | E4 | 50000 |
| 1002 | Akash | M | A1 | 35000 |
| 1004 | NULL | M | B2 | 38965 |
Selecting Specific Columns
SELECT ECODE, ENAME FROM EMPLOYEE;
SELECT ENAME, GRADE, GROSS FROM EMPLOYEE;
Filtering Rows with WHERE
-- Retrieve rows where GENDER is female:
SELECT * FROM EMPLOYEE WHERE GENDER = 'F';
-- Retrieve rows where GROSS salary exceeds 48000:
SELECT * FROM EMPLOYEE WHERE GROSS > 48000;
Eliminating Redundant Output with DISTINCT
The DISTINCT clause eliminates duplicate values from query results.
SELECT DISTINCT(GENDER) FROM EMPLOYEE;
If GENDER contains M, M, F, M, F, F, the output yields:
| DISTINCT(GENDER) |
|---|
| M |
| F |
Viewing Table Structure
To inspect column definitions, data types, and nullability, execute DESCRIBE or DESC:
DESCRIBE EMPLOYEE;
-- OR
DESC EMPLOYEE;
Column Aliases
Use the AS keyword to assign display labels to query output headers:
SELECT ECODE AS "EMPLOYEE_CODE" FROM EMPLOYEE;
Range Filtering (BETWEEN)
The BETWEEN operator specifies an inclusive search range (including lower and upper bounds):
SELECT ECODE, ENAME, GRADE
FROM EMPLOYEE
WHERE GROSS BETWEEN 40000 AND 50000;
Output:
| ECODE | ENAME | GRADE |
|---|---|---|
| 1001 | Ravi | E4 |
| 1006 | Ruby | A1 |
List Value Matching (IN / NOT IN)
The IN operator checks if a column value matches any value in a discrete set:
SELECT * FROM EMPLOYEE
WHERE GRADE IN ('A1', 'A2');
Output:
| ECODE | ENAME | GENDER | GRADE | GROSS |
|---|---|---|---|---|
| 1002 | Akash | M | A1 | 35000 |
| 1006 | Ruby | F | A1 | 45000 |
| 1005 | Sunny | M | A2 | 30000 |
| 1009 | Neema | F | A2 | 52000 |
The NOT IN operator retrieves records that do not match the specified set:
SELECT * FROM EMPLOYEE
WHERE GRADE NOT IN ('A1', 'A2');
Output:
| ECODE | ENAME | GENDER | GRADE | GROSS |
|---|---|---|---|---|
| 1001 | Ravi | M | E4 | 50000 |
| 1004 | Neela | F | B2 | 38965 |
Pattern Matching (LIKE)
The LIKE operator is used with wildcards for string pattern searching:
%(Percent): Matches zero or more arbitrary characters._(Underscore): Matches exactly one single character.
Examples:
-- Names starting with 'R':
SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE 'R%';
-- Employees where second letter of name is 'e':
SELECT * FROM EMPLOYEE WHERE ENAME LIKE '_e%';
-- Names ending with 'y':
SELECT * FROM EMPLOYEE WHERE ENAME LIKE '%y';
Null Value Queries (IS NULL)
NULL values cannot be compared using equal signs (=). The IS NULL predicate must be used:
SELECT * FROM EMPLOYEE WHERE GROSS IS NULL;
Given STUDENT table:
| Roll_No | Name | Marks |
|---|---|---|
| 1 | ARUN | NULL |
| 2 | RAVI | 56 |
| 4 | SANJAY | NULL |
Querying unassigned marks:
SELECT Name FROM STUDENT WHERE Marks IS NULL;
Output:
| Name |
|---|
| ARUN |
| SANJAY |
Data Manipulation and Structural Modifications
Sorting Query Results (ORDER BY)
The ORDER BY clause sorts output rows in ascending (ASC, default) or descending (DESC) order.
-- Ascending order by ENAME:
SELECT * FROM EMPLOYEE ORDER BY ENAME;
-- Descending order by ENAME for salaries above 40000:
SELECT ENAME FROM EMPLOYEE
WHERE GROSS > 40000
ORDER BY ENAME DESC;
Updating Data (UPDATE)
The UPDATE command alters existing field values in table rows.
Syntax:
UPDATE <tablename>
SET <column_name1> = value1, <column_name2> = value2
WHERE <condition>;
Examples:
-- Update single field:
UPDATE EMPLOYEE
SET GROSS = 55000
WHERE ECODE = 1009;
-- Update multiple columns:
UPDATE EMPLOYEE
SET GROSS = 58000, GRADE = 'B2'
WHERE ECODE = 1001;
-- Expression update (increase all gross salaries by 100):
UPDATE EMPLOYEE SET GROSS = GROSS + 100;
-- Double salary for grades A1 or A2:
UPDATE EMPLOYEE
SET GROSS = GROSS * 2
WHERE GRADE = 'A1' OR GRADE = 'A2';
-- Update grade using compound conditions:
UPDATE EMPLOYEE
SET GRADE = 'A2'
WHERE ECODE = 1004 AND ENAME = 'Neela';
Deleting Records (DELETE)
The DELETE command removes rows from a table based on conditions.
-- Delete specific rows:
DELETE FROM EMPLOYEE WHERE GRADE = 'A1';
-- Delete all rows while preserving table structure:
DELETE FROM EMPLOYEE;
Dropping Tables (DROP TABLE)
The DROP TABLE command completely removes a table and its structural definition from the database.
DROP TABLE employee;
Comparison: DELETE vs DROP TABLE
| S.NO. | DELETE COMMAND | DROP TABLE COMMAND |
|---|---|---|
| 1. | It is a DML command. | It is a DDL command. |
| 2. | Used to delete specific or all rows of data from a table without destroying table structure. | Used to delete all data rows along with the structural definition of the table. The table is removed completely and is no longer recognized. |
| 3. | DELETE FROM <tablename> WHERE <condition>; | DROP TABLE <tablename>; |
Structural Table Alterations (ALTER TABLE)
The ALTER TABLE DDL statement modifies existing table definitions (adding, modifying, or dropping columns and constraints).
-- 1. Adding a Column:
ALTER TABLE EMPLOYEE ADD ADDRESS VARCHAR(50);
-- 2. Renaming a Column and Changing Datatype:
ALTER TABLE EMPLOYEE CHANGE GROSS SALARY INTEGER;
ALTER TABLE EMPLOYEE CHANGE ENAME EM_NAME VARCHAR(30);
-- 3. Modifying Datatype Only:
ALTER TABLE EMPLOYEE MODIFY GRADE VARCHAR(2);
-- 4. Dropping a Column:
ALTER TABLE EMPLOYEE DROP GRADE;
-- 5. Adding Primary Key Constraint:
ALTER TABLE EMPLOYEE ADD PRIMARY KEY (ECODE);
-- 6. Adding Foreign Key Constraint:
ALTER TABLE <tablename>
ADD FOREIGN KEY (<column_name>)
REFERENCES ParentTable (<primary_key_column>);
-- 7. Removing Constraints:
ALTER TABLE EMPLOYEE DROP PRIMARY KEY;
ALTER TABLE EMPLOYEE DROP FOREIGN KEY;
-- 8. Disabling and Enabling Foreign Key Checks:
SET FOREIGN_KEY_CHECKS = 0; -- Disable
SET FOREIGN_KEY_CHECKS = 1; -- Enable
Comparison: ALTER vs UPDATE Commands
| S.NO. | ALTER COMMAND | UPDATE COMMAND |
|---|---|---|
| 1. | It is a DDL command. | It is a DML command. |
| 2. | Used to change the structural definition of an existing table (e.g., adding or dropping columns). | Used to modify existing data values within individual rows of a table. |
| 3. | ALTER TABLE <tablename> ADD <column_name> <datatype>; | UPDATE <tablename> SET <column_name> = value WHERE <condition>; |
Integrity Constraints
An integrity constraint is a rule or check applied to a database column or group of columns to enforce data accuracy and consistency.
| S.No. | Constraint | Description |
|---|---|---|
| 1 | NOT NULL | Ensures that a column cannot store a NULL value. |
| 2 | DEFAULT | Assigns a default value to a column when no explicit value is supplied. |
| 3 | UNIQUE | Guarantees that all stored values in a column are distinct/different. |
| 4 | CHECK | Verifies that all values inserted into a column satisfy a defined boolean condition. |
| 5 | PRIMARY KEY | Uniquely identifies each record/row in a table (combines NOT NULL and UNIQUE). |
| 6 | FOREIGN KEY | Enforces referential integrity by linking to a primary key in another table. |
Detailed Constraint Implementations
NOT NULL Constraint
By default, columns allow NULL values unless constrained by NOT NULL.
CREATE TABLE Customer (
SID integer NOT NULL,
Last_Name varchar(30) NOT NULL,
First_Name varchar(30)
);
Executing INSERT INTO Customer VALUES (NULL, 'Kumar', 'Ajay'); results in an error because SID cannot be NULL.
DEFAULT Constraint
Provides a default fallback value when a column is omitted in an insert query.
CREATE TABLE Student (
Student_ID integer,
Name varchar(30),
Score integer DEFAULT 80
);
Executing INSERT INTO Student (Student_ID, Name) VALUES (10, 'Ravi'); automatically stores in the Score field.
UNIQUE Constraint
Prevents identical duplicate values across rows within a specified column.
CREATE TABLE Customer (
SID integer UNIQUE,
Last_Name varchar(30),
First_Name varchar(30)
);
If SID values , , and exist, attempting INSERT INTO Customer VALUES (3, 'Cyrus', 'Grace'); throws a constraint violation error.
CHECK Constraint
Validates that values satisfy a specific relational expression before insertion.
CREATE TABLE Customer (
SID integer CHECK (SID > 0),
Last_Name varchar(30),
First_Name varchar(30)
);
Executing INSERT INTO Customer VALUES (-2, 'Kapoor', 'Raj'); fails because is false.
PRIMARY KEY Constraint
Uniquely identifies each row. It can be defined at column level or table level (required for composite keys consisting of multiple columns).
-- Column-level definition:
CREATE TABLE Customer (
SID integer NOT NULL PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30)
);
-- Table-level definition:
CREATE TABLE Customer (
SID integer,
Last_Name varchar(30),
First_Name varchar(30),
PRIMARY KEY (SID)
);
-- Composite Primary Key definition:
CREATE TABLE Customer (
Branch integer NOT NULL,
SID integer NOT NULL,
Last_Name varchar(30),
First_Name varchar(30),
PRIMARY KEY (Branch, SID)
);
Aggregate Functions and Data Grouping
Aggregate (or group) functions process multiple rows of data in a table column and return a single summary value.
Primary Aggregate Functions:
AVG(): Calculates the arithmetic mean of a numeric column.COUNT(): Counts the number of rows or non-null values.MAX(): Finds the maximum value in a column.MIN(): Finds the minimum value in a column.SUM(): Calculates the total sum of a numeric column.
Sample Table EMPL:
| EMPNO | ENAME | JOB | SAL | DEPTNO |
|---|---|---|---|---|
| 8369 | SMITH | CLERK | 2985 | 10 |
| 8499 | ANYA | SALESMAN | 9870 | 20 |
| 8566 | AMIR | SALESMAN | 8760 | 30 |
| 8698 | BINA | MANAGER | 5643 | 20 |
| 8912 | SUR | NULL | 3000 | 10 |
Function Usage and Evaluation
AVG():
SELECT AVG(SAL) FROM EMPL;
Output:
COUNT():COUNT(*)returns the total row count including duplicate andNULLrecords.COUNT(column_name)returns the number of non-null records in that column.
SELECT COUNT(*) FROM EMPL;
-- Output: 5
SELECT COUNT(JOB) FROM EMPL;
-- Output: 4 (omits SUR whose JOB is NULL)
MAX():
SELECT MAX(SAL) FROM EMPL;
-- Output: 9870
MIN():
SELECT MIN(SAL) FROM EMPL;
-- Output: 2985
SUM():
SELECT SUM(SAL) FROM EMPL;
-- Output: 30258
Grouping Query Results (GROUP BY)
The GROUP BY clause groups rows that share identical values in specified columns, enabling aggregate functions to evaluate each subgroup independently.
Example 1: Calculate the total number of employees per job category.
SELECT JOB, COUNT(*)
FROM EMPL
GROUP BY JOB;
Output:
| JOB | COUNT(*) |
|---|---|
| CLERK | 1 |
| SALESMAN | 2 |
| MANAGER | 1 |
Example 2: Calculate the salary sum for each department.
SELECT DEPTNO, SUM(SAL)
FROM EMPL
GROUP BY DEPTNO;
Output:
| DEPTNO | SUM(SAL) |
|---|---|
| 10 | 5985 |
| 20 | 15513 |
| 30 | 8760 |
Example 3: Calculate the average salary for each department.
SELECT DEPTNO, AVG(SAL)
FROM EMPL
GROUP BY DEPTNO;
Output:
| DEPTNO | AVG(SAL) |
|---|---|
| 10 | 2992.5 |
| 20 | 7756.5 |
| 30 | 8760.0 |