SQL Sample Database

A sample database is a pre-populated database with sample data that helps in learning, testing, and demonstrating SQL queries. Below is an example of a simple SQL sample database used for learning and practicing basic SQL operations.

 

Example: Sample Database - School

The School database consists of several tables such as Students, Courses, Enrollments, and Teachers. Here are the table structures and sample queries:

 

Table Structures

1. Students Table

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    date_of_birth DATE,
    email VARCHAR(100)
);

2. Courses Table

CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    course_description TEXT
);

3. Teachers Table

CREATE TABLE Teachers (
    teacher_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    subject VARCHAR(100)
);

4. Enrollments Table

CREATE TABLE Enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    teacher_id INT,
    enrollment_date DATE,
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id),
    FOREIGN KEY (teacher_id) REFERENCES Teachers(teacher_id)
);

 

Sample Data

Inserting Sample Data

-- Inserting data into Students table
INSERT INTO Students (student_id, first_name, last_name, date_of_birth, email)
VALUES 
(1, 'John', 'Doe', '2000-05-15', '[email protected]'),
(2, 'Jane', 'Smith', '1999-09-23', '[email protected]');

-- Inserting data into Courses table
INSERT INTO Courses (course_id, course_name, course_description)
VALUES
(101, 'Math 101', 'Introduction to Mathematics'),
(102, 'Science 101', 'Introduction to Science');

-- Inserting data into Teachers table
INSERT INTO Teachers (teacher_id, first_name, last_name, subject)
VALUES
(1, 'Michael', 'Johnson', 'Math'),
(2, 'Sarah', 'Davis', 'Science');

-- Inserting data into Enrollments table
INSERT INTO Enrollments (enrollment_id, student_id, course_id, teacher_id, enrollment_date)
VALUES
(1, 1, 101, 1, '2022-09-01'),
(2, 2, 102, 2, '2022-09-01');

 

Sample Queries

1. Select All Students

SELECT * FROM Students;

2. Get All Enrollments with Student and Course Details

SELECT 
    e.enrollment_id, 
    s.first_name AS student_first_name, 
    s.last_name AS student_last_name, 
    c.course_name 
FROM Enrollments e
JOIN Students s ON e.student_id = s.student_id
JOIN Courses c ON e.course_id = c.course_id;

3. Get Teachers and Their Courses

SELECT 
    t.first_name AS teacher_first_name,
    t.last_name AS teacher_last_name,
    c.course_name
FROM Teachers t
JOIN Enrollments e ON t.teacher_id = e.teacher_id
JOIN Courses c ON e.course_id = c.course_id;

4. Count the Number of Students Enrolled in Each Course

SELECT 
    c.course_name, 
    COUNT(e.student_id) AS num_students
FROM Courses c
LEFT JOIN Enrollments e ON c.course_id = e.course_id
GROUP BY c.course_name;

5. Find Students Enrolled in a Specific Course

SELECT 
    s.first_name, 
    s.last_name 
FROM Students s
JOIN Enrollments e ON s.student_id = e.student_id
JOIN Courses c ON e.course_id = c.course_id
WHERE c.course_name = 'Math 101';

 

Summary

The School sample database helps demonstrate basic SQL concepts like data retrieval, joins, and aggregations. It contains tables for Students, Courses, Teachers, and Enrollments, providing a foundation for practicing SQL queries and database management techniques.