MySQL installation, databases, tables and data types

Choose an engine

For exact MySQL labs, use Ubuntu's mysql-server package or RDS for MySQL. The AL2023 LAMP tutorial uses MariaDB packages; that is not Oracle MySQL. For AL2023 local MariaDB inspect available package names with dnf list available '*mariadb*'; use the supported repository version and its documentation. Course SQL targets MySQL 8.x and uses broadly compatible InnoDB syntax unless stated otherwise.

Ubuntu installation

sudo apt update
sudo apt install -y mysql-server
sudo systemctl enable --now mysql
sudo mysql

The local administrative account may use socket authentication. Do not “fix” this by unnecessarily enabling remote root access. For RDS connect with mysql -h DB_ENDPOINT -u ADMIN -p --ssl-mode=VERIFY_IDENTITY --ssl-ca=/path/global-bundle.pem after configuring the private network and CA bundle.

Create the training database

CREATE DATABASE academy CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE academy;
CREATE TABLE students (
  student_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  city VARCHAR(80),
  joined_on DATE NOT NULL
) ENGINE=InnoDB;
CREATE TABLE courses (
  course_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  fee DECIMAL(10,2) NOT NULL CHECK (fee >= 0)
) ENGINE=InnoDB;
CREATE TABLE enrollments (
  student_id BIGINT UNSIGNED NOT NULL,
  course_id INT UNSIGNED NOT NULL,
  score DECIMAL(5,2),
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
) ENGINE=InnoDB;
SHOW TABLES;
DESCRIBE students;
SHOW CREATE TABLE enrollments;

Data type choices

Use DECIMAL for exact currency rather than floating-point types. DATE stores a calendar date; timestamp/timezone semantics need a consistent application convention. VARCHAR has a maximum length, not a guarantee of valid email content. NULL means unknown/absent and differs from empty text or zero. NOT NULL enforces presence but not a business-valid value. CHECK constraints are enforced in modern MySQL 8.x; old versions differ.

Seed data

INSERT INTO students (full_name,email,city,joined_on) VALUES
('Asha Patil','asha@example.com','Pune','2026-01-10'),
('Rahul Shah','rahul@example.com','Nashik','2026-01-12'),
('Meera Joshi','meera@example.com',NULL,'2026-02-01');
INSERT INTO courses (title,fee) VALUES ('AWS',12000),('Linux',6000);
INSERT INTO enrollments VALUES (1,1,85),(1,2,92),(2,1,78);

Verification and assignment

Count three students, two courses and three enrollments. Attempt a duplicate email and an enrollment for missing student 999; both should fail. Explain why the column types of related keys must be compatible. Use a disposable database so accidental lab cleanup cannot affect real data.

Official reference

MySQL CREATE TABLE

Interview and revision check

Why use DECIMAL for fees?

DECIMAL represents fixed-point decimal values exactly within its precision/scale, avoiding binary floating-point rounding behaviour for money.