Worked-out Example

This page illustrates details step-by-step guide to implement SQL for creation of database, tables and eventually query them to obtain data.

Software Installation

• Install mySQL for Windows or Ubuntoo from here. Please ensure that you download sakila and world sample databases offered by mySQL as part of core installer.

• Install mySQL Workbench for Windows or Ubuntoo from here. This a GUI for mySQL database software that helps the database user to review and analysis the datasets available in the database.

Example

Follow below commands and try them on you local machine!

Create a database named "Campus" in MySQL, you can use the following SQL query:

CREATE DATABASE IF NOT EXISTS Campus;

SHOW DATABASES;

This will list all databases, including "Campus" if it was successfully created.

Create the tables "Students", "Faculty", and "Course" under the "Campus" database in MySQL, you can use the following SQL queries. Each table includes relevant columns and data types.

USE Campus;

CREATE TABLE IF NOT EXISTS Students (
    StudentID INT AUTO_INCREMENT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    DateOfBirth DATE,
    Email VARCHAR(100) UNIQUE
);

CREATE TABLE IF NOT EXISTS Faculty (
    FacultyID INT AUTO_INCREMENT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Department VARCHAR(100),
    Email VARCHAR(100) UNIQUE
);

CREATE TABLE IF NOT EXISTS Course (
    CourseID INT AUTO_INCREMENT PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL,
    Credits INT CHECK (Credits > 0),
    FacultyID INT,
    FOREIGN KEY (FacultyID) REFERENCES Faculty(FacultyID)
);

Explanation

• USE Campus;: This command sets the context to the "Campus" database.

• AUTO_INCREMENT: Automatically generates a unique value for each new record.

• PRIMARY KEY: Uniquely identifies each record in the table.

• VARCHAR(n): A variable character string with a maximum length of n.

• DATE: Data type for storing date values.

• UNIQUE: Ensures that all values in a column are different.

• CHECK (Credits > 0): Ensures that credits must be greater than zero.

• FOREIGN KEY: Establishes a relationship between the "Course" and "Faculty" tables.

These queries will create the specified tables with appropriate constraints and relationships.

Insert 10 records into the "Students", "Faculty", and "Course" tables under the "Campus" database in MySQL, you can use the following SQL queries. Each query will insert multiple rows at once.

USE Campus;

-- Inserting records into Students table
INSERT INTO Students (FirstName, LastName, DateOfBirth, Email) VALUES
('John', 'Doe', '2000-05-15', '[e-mail]'),
('Jane', 'Smith', '2001-08-22', '[e-mail]'),
('Emily', 'Johnson', '1999-12-30', '[e-mail]'),
('Michael', 'Brown', '2002-01-10', '[e-mail]'),
('Sarah', 'Davis', '2000-03-25', '[e-mail]'),
('David', 'Wilson', '1998-07-14', '[e-mail]'),
('Laura', 'Garcia', '2001-11-05', '[e-mail]'),
('James', 'Martinez', '1999-09-19', '[e-mail]'),
('Linda', 'Hernandez', '2000-02-28', '[e-mail]'),
('Robert', 'Lopez', '2002-04-18', '[e-mail]');

-- Inserting records into Faculty table
INSERT INTO Faculty (FirstName, LastName, Department, Email) VALUES
('Dr. Alice', 'White', 'Computer Science', '[e-mail]'),
('Prof. Bob', 'Green', 'Mathematics', '[e-mail]'),
('Dr. Carol', 'Black', 'Physics', '[e-mail]'),
('Prof. Dan', 'Blue', 'Chemistry', '[e-mail]'),
('Dr. Eve', 'Red', 'Biology', '[e-mail]');

-- Inserting records into Course table
INSERT INTO Course (CourseName, Credits, FacultyID) VALUES
('Introduction to Programming', 3, 1),
('Calculus I', 4, 2),
('Physics I', 4, 3),
('Organic Chemistry', 4, 4),
('Biology 101', 3, 5);

Explanation

• USE Campus;: Sets the context to the "Campus" database.

• INSERT INTO: Specifies which table to insert data into.

• VALUES: Contains the data to be inserted.

• Each set of parentheses represents a new record to be inserted into the respective table.

These queries will successfully insert 10 records into the "Students" table, 5 records into the "Faculty" table, and 5 records into the "Course" table. Adjust the email addresses and other details as necessary for your specific use case.

Create a "Student_Registration" table under the "Campus" database that contains columns associated with the "Students" and "Course" tables, along with course registration details, you can use the following SQL query:

USE Campus;

CREATE TABLE IF NOT EXISTS Student_Registration (
    RegistrationID INT AUTO_INCREMENT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    RegistrationDate DATE DEFAULT '2025-01-26',
    Status ENUM('Registered', 'Dropped', 'Completed') DEFAULT 'Registered',
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

Explanation

• USE Campus;: Sets the context to the "Campus" database.

• CREATE TABLE IF NOT EXISTS: Creates the table only if it does not already exist.

• RegistrationID INT AUTO_INCREMENT PRIMARY KEY: A unique identifier for each registration record, automatically incremented.

• StudentID INT: Foreign key referencing the "Students" table.

• CourseID INT: Foreign key referencing the "Course" table.

• RegistrationDate DATE DEFAULT '2025-01-26': The date of registration, defaulting to January 26, 2025.

• Status ENUM('Registered', 'Dropped', 'Completed') DEFAULT 'Registered': A status field indicating the current state of the registration, defaulting to 'Registered'.

• FOREIGN KEY constraints ensure referential integrity by linking to corresponding records in the "Students" and "Course" tables.

This query will successfully create the "Student_Registration" table with the specified columns and relationships.

Insert 10 records into the "Student_Registration" table under the "Campus" database, you can use the following SQL query. This query assumes that you have valid StudentID and CourseID values corresponding to the records in the "Students" and "Course" tables.

USE Campus;

INSERT INTO Student_Registration (StudentID, CourseID, RegistrationDate, Status) VALUES
(1, 1, '2025-01-26', 'Registered'),
(2, 2, '2025-01-26', 'Registered'),
(3, 3, '2025-01-26', 'Registered'),
(4, 4, '2025-01-26', 'Registered'),
(5, 5, '2025-01-26', 'Completed'),
(1, 2, '2025-01-26', 'Dropped'),
(2, 3, '2025-01-26', 'Registered'),
(3, 4, '2025-01-26', 'Completed'),
(4, 5, '2025-01-26', 'Registered'),
(5, 1, '2025-01-26', 'Registered');

Explanation

• USE Campus;: Sets the context to the "Campus" database.

• INSERT INTO Student_Registration: Specifies the table into which data will be inserted.

• (StudentID, CourseID, RegistrationDate, Status): Lists the columns to which values will be assigned.

• VALUES: Contains multiple sets of values for each record being inserted.

Each record includes:

• StudentID: Corresponding ID from the "Students" table.

• CourseID: Corresponding ID from the "Course" table.

• RegistrationDate: Set to January 26, 2025.

• Status: Indicates whether the registration is registered, dropped, or completed.

This query will insert 10 records into the "Student_Registration" table as specified. Adjust StudentID and CourseID values as necessary based on your existing data.

Now add a new column called "semester" to the "Student_Registration" table under the "Campus" database, you can use the following SQL query:

USE Campus;

ALTER TABLE Student_Registration 
ADD COLUMN semester VARCHAR(10) AFTER Status;

Explanation

• USE Campus;: Sets the context to the "Campus" database.

• ALTER TABLE Student_Registration: Specifies the table that will be modified.

• ADD COLUMN semester VARCHAR(10): Adds a new column named "semester" with a data type of VARCHAR that can hold up to 10 characters.

• AFTER Status: Positions the new column after the existing "Status" column in the table.

This query will successfully add the "semester" column to the "Student_Registration" table. Adjust the data type and length as necessary based on your specific requirements.

Now retrieve details from the "Students," "Course," and "Student_Registration" tables in the "Campus" database using a JOIN query, you can use the following SQL statement:

USE Campus;

SELECT 
    s.StudentID,
    s.FirstName AS StudentFirstName,
    s.LastName AS StudentLastName,
    s.Email AS StudentEmail,
    c.CourseID,
    c.CourseName,
    c.Credits,
    sr.RegistrationDate,
    sr.Status AS RegistrationStatus,
    sr.semester
FROM 
    Students s
INNER JOIN 
    Student_Registration sr ON s.StudentID = sr.StudentID
INNER JOIN 
    Course c ON sr.CourseID = c.CourseID;

Explanation

• USE Campus;: Sets the context to the "Campus" database.

• SELECT: Specifies the columns to retrieve from the tables.

  • s.StudentID, s.FirstName, s.LastName, s.Email: Columns from the "Students" table.

  • c.CourseID, c.CourseName, c.Credits: Columns from the "Course" table.

  • sr.RegistrationDate, sr.Status, sr.semester: Columns from the "Student_Registration" table.

• FROM Students s: Indicates that the main table is "Students," with an alias s.

• INNER JOIN Student_Registration sr ON s.StudentID = sr.StudentID: Joins the "Student_Registration" table on the matching StudentID.

• INNER JOIN Course c ON sr.CourseID = c.CourseID: Joins the "Course" table on the matching CourseID.

This query will display a comprehensive view of student details, course details, and registration details for all registered students in the "Campus" database as of January 26, 2025. Adjust any column names or aliases as necessary based on your specific schema.