2. School Database Practice

School Database Practice

This exercise repeats the basic database workflow with a school domain. The goal is to get comfortable writing tables from a plain-English requirement.

Requirement

Create a database named schooldb with two tables:

Create and select the database

CREATE DATABASE schooldb;

USE schooldb;

Create the students table

CREATE TABLE students (
  StudentID INT,
  FirstName VARCHAR(25),
  LastName VARCHAR(25),
  Email VARCHAR(25),
  EnrollmentDate DATE
);

DATE stores values in YYYY-MM-DD format.

Create the courses table

CREATE TABLE courses (
  CourseID INT,
  CourseName VARCHAR(25),
  Department VARCHAR(25),
  Credits INT
);

This table stores course metadata. There are still no constraints, so MySQL will not stop duplicate course IDs yet.

Insert sample students

INSERT INTO students (StudentID, FirstName, LastName, Email, EnrollmentDate)
VALUES
  (1, "Krish", "Naik", "support@krish.com", "2024-09-09"),
  (2, "Krish1", "Naik1", "support1@krish.com", "2024-09-10");

Check the rows:

SELECT *
FROM students;

Insert sample courses

INSERT INTO courses (CourseID, CourseName, Department, Credits)
VALUES
  (101, "Introduction to Stats", "Statistics", 3),
  (102, "Data Science", "Data Analytics", 3);

Check the rows and list the tables:

SELECT *
FROM courses;

SHOW TABLES;

Clean up

Drop child objects first, then the database.

DROP TABLE courses;
DROP TABLE students;

DROP DATABASE schooldb;

Assignment

Create your own database named companydb with two tables:

Keep this first version simple and do not add constraints yet. Constraints are introduced later in the series.