Q&A 2 🧭 How do you navigate inside MySQL/MariaDB?
2.1 Explanation
When you start MySQL from the XAMPP control panel, you’re actually entering MariaDB, an open-source, drop-in replacement for MySQL. It behaves almost identically to MySQL but often includes performance improvements and extra features.
Once you see the prompt MariaDB [(none)]>, you are inside the MySQL/MariaDB shell and ready to run SQL commands:
2.2 SQL/MySQL Code
-- scripts/cdi_learning.sql
-- =========================================
-- CDI foundational schema and sample data
-- Idempotent + safe to re-run
-- =========================================
DROP DATABASE cdi_learning;
-- Step 1: Create the CDI learning database (if it doesn't exist)
CREATE DATABASE IF NOT EXISTS cdi_learning;
-- Step 2: Switch into the database
USE cdi_learning;
-- Step 3: Create tables (if they don't exist yet)
-- Add uniqueness to keep data clean across re-runs
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
enrolled_date DATE,
CONSTRAINT uq_students_name_email UNIQUE (name, email)
);
CREATE TABLE IF NOT EXISTS courses (
id INT AUTO_INCREMENT PRIMARY KEY,
course_name VARCHAR(100) NOT NULL,
CONSTRAINT uq_courses_name UNIQUE (course_name)
);
CREATE TABLE IF NOT EXISTS enrollments (
student_id INT NOT NULL,
course_id INT NOT NULL,
PRIMARY KEY (student_id, course_id), -- prevent duplicate enrollments
CONSTRAINT fk_enr_student FOREIGN KEY (student_id) REFERENCES students(id),
CONSTRAINT fk_enr_course FOREIGN KEY (course_id) REFERENCES courses(id)
);
-- Step 4: Insert sample courses (insert only if missing)
INSERT INTO courses (course_name)
SELECT 'Data Science'
WHERE NOT EXISTS (SELECT 1 FROM courses WHERE course_name='Data Science');
INSERT INTO courses (course_name)
SELECT 'SQL Basics'
WHERE NOT EXISTS (SELECT 1 FROM courses WHERE course_name='SQL Basics');
INSERT INTO courses (course_name)
SELECT 'Python Programming'
WHERE NOT EXISTS (SELECT 1 FROM courses WHERE course_name='Python Programming');
-- Step 5: Insert sample students (insert only if missing)
INSERT INTO students (name, email, enrolled_date)
SELECT 'Alice Example','alice@example.com','2025-08-10'
WHERE NOT EXISTS (
SELECT 1 FROM students WHERE name='Alice Example' AND email='alice@example.com'
);
INSERT INTO students (name, email, enrolled_date)
SELECT 'Bob Tester','bob@test.com','2025-08-11'
WHERE NOT EXISTS (
SELECT 1 FROM students WHERE name='Bob Tester' AND email='bob@test.com'
);
-- Step 6: Insert enrollments (lookup IDs dynamically + avoid duplicates)
INSERT INTO enrollments (student_id, course_id)
SELECT s.id, c.id
FROM students s
JOIN courses c ON c.course_name='Data Science'
WHERE s.name='Alice Example' AND s.email='alice@example.com'
AND NOT EXISTS (
SELECT 1 FROM enrollments e WHERE e.student_id = s.id AND e.course_id = c.id
);
INSERT INTO enrollments (student_id, course_id)
SELECT s.id, c.id
FROM students s
JOIN courses c ON c.course_name='SQL Basics'
WHERE s.name='Alice Example' AND s.email='alice@example.com'
AND NOT EXISTS (
SELECT 1 FROM enrollments e WHERE e.student_id = s.id AND e.course_id = c.id
);
INSERT INTO enrollments (student_id, course_id)
SELECT s.id, c.id
FROM students s
JOIN courses c ON c.course_name='SQL Basics'
WHERE s.name='Bob Tester' AND s.email='bob@test.com'
AND NOT EXISTS (
SELECT 1 FROM enrollments e WHERE e.student_id = s.id AND e.course_id = c.id
);
-- Step 7: Run analysis queries
-- Only courses with enrollments (INNER JOIN)
SELECT
c.course_name,
COUNT(e.student_id) AS total_students
FROM courses c
INNER JOIN enrollments e
ON c.id = e.course_id
GROUP BY c.course_name
ORDER BY total_students DESC;
-- All courses including empty ones (LEFT JOIN)
SELECT
c.course_name,
COUNT(e.student_id) AS total_students
FROM courses c
LEFT JOIN enrollments e
ON c.id = e.course_id
GROUP BY c.course_name
ORDER BY total_students DESC;2.3 Example usage inside the MariaDB prompt
MariaDB [cdi_learning]> SOURCE /Users/tmbmacbookair/Dropbox/GITHUB_REPOs/cdi-database-management/scripts/cdi_learning.sql;Example output
MariaDB [cdi_learning]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| cdi_learning |
| information_schema |
| mysql |
| performance_schema |
| phpmyadmin |
| test |
+--------------------+
6 rows in set (0.001 sec)MariaDB [cdi_learning]> USE cdi_learning;
Database changedMariaDB [cdi_learning]> SHOW TABLES;
+------------------------+
| Tables_in_cdi_learning |
+------------------------+
| courses |
| enrollments |
| students |
+------------------------+
3 rows in set (0.053 sec)-- Inspect a specific table eg table listed above is courses, enrollments, students
DESCRIBE students;
DESCRIBE courses;
DESCRIBE enrollments;MariaDB [cdi_learning]> DESCRIBE students;
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | MUL | NULL | |
| email | varchar(100) | NO | | NULL | |
| enrolled_date | date | YES | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
4 rows in set (0.014 sec)
MariaDB [cdi_learning]> DESCRIBE courses;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| course_name | varchar(100) | NO | UNI | NULL | |
+-------------+--------------+------+-----+---------+----------------+
2 rows in set (0.007 sec)
MariaDB [cdi_learning]> DESCRIBE enrollments;
+------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| student_id | int(11) | NO | PRI | NULL | |
| course_id | int(11) | NO | PRI | NULL | |
+------------+---------+------+-----+---------+-------+
2 rows in set (0.006 sec)2.4 🛠️ (Optional) Add MySQL to Your PATH
To run mysql from anywhere in your terminal:
Add the following line at the end of the file:
Then apply the change:
Now you can run:
2.5 🚪 Exiting the MySQL Shell
To exit the MySQL/MariaDB shell at any time, simply type:
or
Then press Enter. This will return you to your normal terminal prompt.