Due Date: August 21, 2023
Update your name on W3School to your first name.
Add KC2023to24_PX to the end.
The X at the end ws your period.
In my case, my name would be JoeKC2023to24_P9.png.
Use the picture and update your profile on W3School.
Click here and watch video on how to do this.
Due Date: August 1, 2023
1. We need to find the best FREE online SQL engine.
Start to google "Best Free Online SQL Engine"
We will vote and decide on the one we will use in this class.
2. Name this database file: PX_Student_lastname
This database will include tables for
students, courses, and enrollment information.
Please note that this is a simplified example
for educational purposes and may not cover
all possible features of a real-world student management system.
```sql
-- Table: Students
CREATE TABLE Students (
student_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
date_of_birth DATE,
gender VARCHAR(10),
contact_email VARCHAR(100),
contact_phone VARCHAR(20),
address VARCHAR(200)
);
-- Table: Courses
CREATE TABLE Courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100),
course_description TEXT,
course_teacher VARCHAR(100)
);
-- Table: Enrollments
CREATE TABLE Enrollments (
enrollment_id INT PRIMARY KEY,
student_id INT,
course_id INT,
enrollment_date DATE,
FOREIGN KEY (student_id) REFERENCES Students(student_id),
FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);
Due Date: August 1, 2023
In this schema:
- The `Students` table stores information
about each student, such as their ID, first
name, last name, date of birth, gender,
contact details, and address.
- The `Courses` table holds information
about the courses offered at the high school,
including the course ID, name, description,
and teacher's name.
- The `Enrollments` table tracks the
enrollments of students in specific courses.
It includes an enrollment ID, student ID
(foreign key referencing the `Students` table),
course ID (foreign key referencing the `Courses` table), and the enrollment date.
Remember that this is a basic example,
and in a real-world scenario, you might
need to expand upon it by adding more
tables and attributes to handle additional
features like attendance tracking,
grades, parent/guardian information,
and more. Additionally, you would
need to consider database normalization,
indexing, and data integrity constraints
for a production-level system.
Run each of the statments below.
1. **Insert a new student:**
```sql
INSERT INTO Students (student_id, first_name, last_name, date_of_birth, gender, contact_email, contact_phone, address)
VALUES (1, 'John', 'Doe', '2005-05-15', 'Male', 'john.doe@example.com', '123-456-7890', '123 Main St');
-- You will need to add 5 more records that you makeup.
```
2. **Insert a new course:**
```sql
INSERT INTO Courses (course_id, course_name, course_description, course_teacher)
VALUES (1, 'Mathematics', 'Introduction to algebra and calculus', 'Ms. Smith');
-- You will need to add 5 more records that you makeup.
```
3. **Enroll a student in a course:**
```sql
INSERT INTO Enrollments (enrollment_id, student_id, course_id, enrollment_date)
VALUES (1, 1, 1, '2023-08-24');
-- You will need to add 5 more records that you makeup.
```
4. **Update student's contact email:**
```sql
UPDATE Students
SET contact_email = 'new.email@example.com'
WHERE student_id = 1;
```
5. **Change course teacher's name:**
```sql
UPDATE Courses
SET course_teacher = 'Mr. Johnson'
WHERE course_id = 1;
```
6. **Withdraw a student from a course:**
```sql
DELETE FROM Enrollments
WHERE student_id = 1 AND course_id = 1;
```
