Duration: 2 hours (Lecture: 2 hours, Lab: 2 hours)

Syllabus Topics:

• Lecture:
  • MySQL Data Types
  • Database Constraints (Primary Key, Unique, Not Null, Foreign Key, Default, Check*)
  • Aggregate Functions
  • Grouping Things Together (Group By, Having)
  • LIKE Operator
  • DISTINCT
  • Sorting (Order By Clause)
  • BETWEEN… AND Operators
  • Comparing Nulls (IS NULL/IS NOT NULL)
  • IN/NOT IN
• Lab:
  • Defining Data Types for Columns
  • Creating, Altering, Dropping Constraints
  • Aggregate Functions: SUM(), AVG(), COUNT(), MAX(), MIN(), COUNT(), Group By, Having Clause
  • Using Like, Distinct, Order By, Between...And
  • Comparing Nulls, Using IN/Not-In

Lecture Notes (In-Depth)

1. MySQL Data Types

MySQL supports a variety of data types to define the kind of data a column can store. Choosing the appropriate data type ensures efficient storage and accurate data handling.

• Numeric Data Types:
  • INT: Integer (whole numbers, e.g., 42). Variants: TINYINT, SMALLINT, MEDIUMINT, BIGINT.
  • DECIMAL(M,D): Fixed-point number (e.g., DECIMAL(10,2) for 12345678.90, where M=10 digits, D=2 decimal places).
  • FLOAT/DOUBLE: Floating-point numbers for approximate values (e.g., 3.14159).
• String Data Types:
  • CHAR(n): Fixed-length string (e.g., CHAR(10) always uses 10 characters).
  • VARCHAR(n): Variable-length string (e.g., VARCHAR(50) stores up to 50 characters).
  • TEXT: Large variable-length text (e.g., for long descriptions).
• Date and Time Data Types:
  • DATE: Stores dates (YYYY-MM-DD, e.g., 2025-07-16).
  • DATETIME: Stores date and time (YYYY-MM-DD HH:MM:SS).
  • TIMESTAMP: Stores date and time, automatically updates on record modification (useful for tracking changes).
  • TIME: Stores time (HH:MM:SS).
• Boolean:
  • TINYINT(1): Used as boolean (0 = false, 1 = true).
• Other Types:
  • BLOB: Binary large object for storing binary data (e.g., images).
  • JSON: Stores JSON data (introduced in MySQL 5.7+).
• Key Considerations:
  • Choose the smallest data type that meets requirements to optimize storage.
  • Example: Use TINYINT for small integers (0-255) instead of INT.

2. Database Constraints

Constraints enforce rules on data to maintain integrity and consistency.

• Primary Key:
  • Ensures each record is uniquely identifiable.
  • Cannot be NULL and must be unique.
  • Example: student_id in a students table.
  • Syntax: student_id INT PRIMARY KEY.
• Unique:
  • Ensures all values in a column (or combination of columns) are unique.
  • Allows NULL values (unlike Primary Key).
  • Example: email in a users table.
  • Syntax: email VARCHAR(100) UNIQUE.
• Not Null:
  • Prevents NULL values in a column.
  • Example: name in a students table must always have a value.
  • Syntax: name VARCHAR(50) NOT NULL.
• Foreign Key:
  • Establishes a relationship between two tables by linking a column to the primary key of another table.
  • Ensures referential integrity (e.g., cannot insert a dept_id that doesn’t exist in the departments table).
  • Example: dept_id in students references departments.dept_id.
  • Syntax: FOREIGN KEY (dept_id) REFERENCES departments(dept_id).