Create the following queries on the ITCO630_A database used in unit 3 and save them all in a file called ITCO630_P5.SQL. Please note that you can execute individual queries in a query file by highlighting the lines that you want to execute before running the script. Remember to define what database to use with a USE statement. Using a join, get the full details of all the students who work on the mid-term exam assignment. Get the assignment names (duplicates eliminated) being worked on by students at Central University. Get the last names of all the students who are working on assignment A1. Get the student numbers and start dates of all the students with start dates equal to the earliest date. Insert yourself into the student table using the last five digits of your phone number as the student number and show yourself as attending Central University. Then show all the records in the student table. Delete yourself from the student table by matching your student number, and then show all the records in the student table. You should create a zip file called ITCO630_P5.ZIP with your query file (ITCO630_P5.SQL) included.

In order to complete the given task, the following queries need to be executed on the ITCO630_A database and saved in a file called ITCO630_P5.SQL.

1. Using a join, get the full details of all the students who work on the mid-term exam assignment.

SELECT *
FROM student s
JOIN assignment_work aw ON s.student_number = aw.student_number
WHERE aw.assignment_id = ‘mid-term’;

This query combines the student and assignment_work tables using a join on the student_number column. It then filters the result to only include records where the assignment_id is ‘mid-term’, which represents the mid-term exam assignment.

2. Get the assignment names (duplicates eliminated) being worked on by students at Central University.

SELECT DISTINCT a.assignment_name
FROM student s
JOIN assignment_work aw ON s.student_number = aw.student_number
JOIN assignment a ON aw.assignment_id = a.assignment_id
WHERE s.university_name = ‘Central University’;

This query retrieves the assignment names from the assignment table that are being worked on by students at Central University. It achieves this by joining the student, assignment_work, and assignment tables and filtering the result to only include records where the university_name is ‘Central University’. The DISTINCT keyword ensures that duplicate assignment names are eliminated from the result.

3. Get the last names of all the students who are working on assignment A1.

SELECT s.last_name
FROM student s
JOIN assignment_work aw ON s.student_number = aw.student_number
WHERE aw.assignment_id = ‘A1’;

This query retrieves the last names of students who are working on assignment A1. It uses a join between the student and assignment_work tables based on the student_number column. The result is filtered to include only records where the assignment_id is ‘A1’.

4. Get the student numbers and start dates of all the students with start dates equal to the earliest date.

SELECT student_number, start_date
FROM student
WHERE start_date = (SELECT MIN(start_date) FROM student);

This query retrieves the student numbers and start dates of all the students whose start dates are equal to the earliest date among all students. It achieves this by using a subquery to find the minimum start date from the student table and then selecting the student_number and start_date from the student table where the start_date matches the minimum date.

5. Insert yourself into the student table using the last five digits of your phone number as the student number and show yourself as attending Central University. Then show all the records in the student table.

INSERT INTO student (student_number, last_name, first_name, university_name)
VALUES (‘12345’, ‘Your Last Name’, ‘Your First Name’, ‘Central University’);

SELECT *
FROM student;

This query inserts a new record into the student table with your information, using the last five digits of your phone number as the student number and showing yourself as attending Central University. The SELECT statement afterward retrieves all the records from the student table, including the newly inserted record.

6. Delete yourself from the student table by matching your student number, and then show all the records in the student table.

DELETE FROM student
WHERE student_number = ‘12345’;

SELECT *
FROM student;

This query deletes the record from the student table that matches your student number. It then retrieves all the records from the student table, now excluding the record that was deleted.