Scribd
Upload
82 views8 pages

Database Exercise: Creating Tables

The document outlines the creation of database tables for Students, Courses, Teachers, and Enrollments, including their respective fields and relationships. It also provides sample data insertion for each table and basic SQL queries to retrieve information such as all students, courses, teachers, enrollments, and specific student grades. Additionally, it demonstrates how to find students enrolled in a specific course and retrieve grades for a particular student.

Uploaded by

kaleabashenafi5
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
82 views8 pages

Database Exercise: Creating Tables

The document outlines the creation of database tables for Students, Courses, Teachers, and Enrollments, including their respective fields and relationships. It also provides sample data insertion for each table and basic SQL queries to retrieve information such as all students, courses, teachers, enrollments, and specific student grades. Additionally, it demonstrates how to find students enrolled in a specific course and retrieve grades for a particular student.

Uploaded by

kaleabashenafi5
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

1.

Creating Tables
Let's create tables for Students, Courses,
Teachers, and Enrollments.
1. 1 Create Students table
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfBirth DATE
);
1.2 Create Courses table
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100),
Credits INT
);
1.3 Create Teachers table
CREATE TABLE Teachers (
TeacherID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50)
);

1.4 Create Enrollments table to link Students and


Courses
CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
Grade CHAR(2),
FOREIGN KEY (StudentID) REFERENCES
Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES
Courses(CourseID)
);

2. Inserting Data
Now, let's insert some sample data into these tables.
2.1 Insert data into Students table
INSERT INTO Students (StudentID, FirstName,
LastName, DateOfBirth)
VALUES
(1, 'Alice', 'Johnson', '2005-09-01'),
(2, 'Bob', 'Smith', '2006-02-15'),
(3, 'Charlie', 'Brown', '2005-11-30');

2.2 Insert data into Courses table


INSERT INTO Courses (CourseID, CourseName,
Credits)
VALUES
(101, 'Mathematics', 3),
(102, 'English', 4),
(103, 'Science', 3);
2.3 Insert data into Teachers table
INSERT INTO Teachers (TeacherID, FirstName,
LastName, Department)
VALUES
(1, 'John', 'Doe', 'Mathematics'),
(2, 'Jane', 'Smith', 'English'),
(3, 'Emily', 'Jones', 'Science');

2.4 Insert data into Enrollments table


INSERT INTO Enrollments (EnrollmentID,
StudentID, CourseID, Grade)
VALUES
(1, 1, 101, 'A'),
(2, 1, 102, 'B'),
(3, 2, 103, 'A'),
(4, 3, 101, 'C'),
(5, 3, 102, 'B');
3. Basic Queries
Now, let's run some basic queries to retrieve data.
3.1 Retrieve all students
SELECT * FROM Students;
3.2 Retrieve all courses
SELECT * FROM Courses;
3.4 Retrieve all teachers
SELECT * FROM Teachers;
3.5 Retrieve all enrollments
SELECT * FROM Enrollments;
3.6 Find students enrolled in a specific course
(e.g., Mathematics)
SELECT [Link], [Link]
FROM Students s
JOIN Enrollments e ON [Link] = [Link]
JOIN Courses c ON [Link] = [Link]
WHERE [Link] = 'Mathematics';

3.7 Find the grades of a specific student (e.g.,


Alice Johnson)
SELECT [Link], [Link]
FROM Courses c
JOIN Enrollments e ON [Link] = [Link]
JOIN Students s ON [Link] = [Link]
WHERE [Link] = 'Alice' AND [Link] =
'Johnson';

You might also like