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.

Data Definition Language Commands Overview

MySQL Basic Elements

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

MySQL Elements Overview

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., 2222, 1818, 19971997).
    • Integer Literals: Numbers without fractional or decimal components (e.g., 1717).
    • Real Literals: Numbers containing a explicit decimal point (e.g., 17.017.0, 17.517.5).
Data Types

Data types specify the kind of data a column can hold and determine the valid operations that can be performed on it.

Data Types Classification

  1. Numeric Data Types:

    • int: Used for integer values without decimal places.
    • Decimal(m,d): Used for floating-point or real numbers, where mm denotes the total number of digits (precision) and dd represents the number of digits to the right of the decimal point (scale).
  2. Date and Time Data Types:

    • date: Stores calendar dates in the standard YYYY-MM-DD format.
    • time: Stores time values in the standard HH:MM:SS format.
  3. String Data Types:

    • char(m): Fixed-length string storing up to a maximum of mm characters.
    • varchar(m): Variable-length string storing up to a maximum of mm characters.
Comparison: CHAR vs VARCHAR Data Types

Char vs Varchar Comparison

S.NO.Char DatatypeVarchar 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 nn bytes. If an inserted value is shorter than length nn, trailing blanks/spaces are padded, maintaining a constant size of nn bytes.When defined as VARCHAR(n), the maximum storage size is nn 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:

ECODEENAMEGENDERGRADEGROSS
1001RaviME450000
1002AkashMA135000
1004NULLMB238965
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:

ECODEENAMEGRADE
1001RaviE4
1006RubyA1
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:

ECODEENAMEGENDERGRADEGROSS
1002AkashMA135000
1006RubyFA145000
1005SunnyMA230000
1009NeemaFA252000

The NOT IN operator retrieves records that do not match the specified set:

SELECT * FROM EMPLOYEE 
WHERE GRADE NOT IN ('A1', 'A2');

Output:

ECODEENAMEGENDERGRADEGROSS
1001RaviME450000
1004NeelaFB238965
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_NoNameMarks
1ARUNNULL
2RAVI56
4SANJAYNULL

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 COMMANDDROP 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 COMMANDUPDATE 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.ConstraintDescription
1NOT NULLEnsures that a column cannot store a NULL value.
2DEFAULTAssigns a default value to a column when no explicit value is supplied.
3UNIQUEGuarantees that all stored values in a column are distinct/different.
4CHECKVerifies that all values inserted into a column satisfy a defined boolean condition.
5PRIMARY KEYUniquely identifies each record/row in a table (combines NOT NULL and UNIQUE).
6FOREIGN KEYEnforces 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 8080 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 11, 22, and 33 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 −2>0-2 > 0 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:

EMPNOENAMEJOBSALDEPTNO
8369SMITHCLERK298510
8499ANYASALESMAN987020
8566AMIRSALESMAN876030
8698BINAMANAGER564320
8912SURNULL300010

Function Usage and Evaluation

  1. AVG():
SELECT AVG(SAL) FROM EMPL;

Output: 6051.66051.6

  1. COUNT(): COUNT(*) returns the total row count including duplicate and NULL records. 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)
  1. MAX():
SELECT MAX(SAL) FROM EMPL;
-- Output: 9870
  1. MIN():
SELECT MIN(SAL) FROM EMPL;
-- Output: 2985
  1. 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:

JOBCOUNT(*)
CLERK1
SALESMAN2
MANAGER1

Example 2: Calculate the salary sum for each department.

SELECT DEPTNO, SUM(SAL)
FROM EMPL
GROUP BY DEPTNO;

Output:

DEPTNOSUM(SAL)
105985
2015513
308760

Example 3: Calculate the average salary for each department.

SELECT DEPTNO, AVG(SAL)
FROM EMPL
GROUP BY DEPTNO;

Output:

DEPTNOAVG(SAL)
102992.5
207756.5
308760.0