Due Date: September 1, 2023
Student Schema
Create Schema and execute commands below.
The file names will be:
PX_Students_lastname (The actual SQL code.)
PX_Students_lastname.mp4 (A video of the program running.)
PX_Students_lastname.png (A picture of program inside the IDE.)
Be sure to drop the 3 files off into google classroom.
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: September 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;
```
Due Date: September 8, 2023
B. Execute commands below successfully commands below.
1. **Retrieve All Data from a Table:**
* Assuming you have a table named `employees`, fetch all columns and rows from it.
```SQL
SELECT * FROM employees;
```
2. **Retrieve Specific Columns:**
* Fetch only the `first_name` and `last_name` columns from the `employees` table.
```SQL
SELECT first_name, last_name FROM employees;
```
3. **Filter Using WHERE:**
* Get details of employees with a specific `job_id` (e.g., 'MANAGER').
```SQL
SELECT * FROM employees WHERE job_id = 'MANAGER';
```
4. **Sorting Results:**
* List employees in descending order of their `salary`.
```SQL
SELECT * FROM employees ORDER BY salary DESC;
```
5. **Count Rows:**
* Count the number of employees with a salary greater than 50000.
```SQL
SELECT COUNT(*) FROM employees WHERE salary > 50000;
```
6. **Sum, Average, Min, and Max:**
* Find the sum, average, minimum, and maximum salary from the `employees` table.
```SQL
SELECT SUM(salary), AVG(salary), MIN(salary), MAX(salary) FROM employees;
```
7. **Use of DISTINCT:**
* Find the distinct job IDs from the `employees` table.
```SQL
SELECT DISTINCT job_id FROM employees;
```
8. **Using LIKE Operator:**
* Fetch details of employees whose name starts with 'Jo'.
```SQL
SELECT * FROM employees WHERE first_name LIKE 'Jo%';
```
9. **Using BETWEEN:**
* Retrieve employees whose salary is between 30000 and 50000.
```SQL
SELECT * FROM employees WHERE salary BETWEEN 30000 AND 50000;
```
10. **Using IN Operator:**
* Get details of employees who are in one of the following departments: 'HR', 'Finance', 'Sales'.
```SQL
SELECT * FROM employees WHERE department IN ('HR', 'Finance', 'Sales');
```
11. **Combining Conditions with AND & OR:**
* Find employees in the 'IT' department with a salary greater than 40000.
```SQL
SELECT * FROM employees WHERE department = 'IT' AND salary > 40000;
```
12. **Limiting Results:**
* Retrieve the top 5 highest-earning employees.
```SQL
SELECT * FROM employees ORDER BY salary DESC LIMIT 5;
```
13. **Using Aggregate Functions with GROUP BY:**
* Count employees in each department.
```SQL
SELECT department, COUNT(*) FROM employees GROUP BY department;
```
14. **Filtering Aggregated Data with HAVING:**
* Find departments that have more than 5 employees.
```SQL
SELECT department, COUNT(*) AS num_employees FROM employees GROUP BY department HAVING num_employees > 5;
```
15. **Join Two Tables:**
* Assume you have another table named `departments` with a `department_id`
as a primary key. Fetch employee names along with their respective department names using a JOIN operation.
```SQL
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
```
Remember to adjust table and column names according to your own database
schema. Also, practice is key. Keep experimenting with different queries,
and you'll improve rapidly.
