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 requested queries on the ITCO630_A database and save them in a file called ITCO630_P5.SQL, we need to execute the following steps. Firstly, we will need to define the database we want to use with a USE statement. Then, we can proceed with each of the query requirements.

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

To accomplish this, we will need to join the appropriate tables to retrieve the necessary information. The tables involved in this query are likely the “students” table and the “assignments” table. By joining these tables on the appropriate keys, the query will return the desired information. The query would look similar to the following:

SELECT *
FROM students s
JOIN assignments a ON s.assignment_id = a.assignment_id
WHERE a.assignment_name = ‘mid-term exam’;

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

For this query, we need to retrieve the assignment names that are being worked on by students at Central University. We will need to join the students table with the universities table to get the required information. Additionally, we will need to use DISTINCT to eliminate any duplicate assignment names. The query would look like this:

SELECT DISTINCT a.assignment_name
FROM students s
JOIN universities u ON s.university_id = u.university_id
JOIN assignments a ON s.assignment_id = a.assignment_id
WHERE u.university_name = ‘Central University’;

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

To retrieve the last names of students working on assignment A1, we will need to join the students table with the assignments table and filter the results to only include students working on assignment A1. The query would look like this:

SELECT s.last_name
FROM students s
JOIN assignments a ON s.assignment_id = a.assignment_id
WHERE a.assignment_name = ‘A1’;

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

To retrieve the student numbers and start dates of students with start dates equal to the earliest date, we will need to use a subquery to find the earliest start date and then join the students table with the subquery results. The query would look like this:

SELECT s.student_number, s.start_date
FROM students s
JOIN (SELECT MIN(start_date) AS earliest_date FROM students) e ON s.start_date = e.earliest_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.

To insert yourself into the student table, you will need to use the INSERT INTO statement. Replace the placeholders with your own information, such as your last name, phone number, and start date. The query would look like this:

INSERT INTO students (last_name, student_number, university_id, start_date)
VALUES (‘YourLastName’, ‘12345’, (SELECT university_id FROM universities WHERE university_name = ‘Central University’), ‘2022-01-01’);

To show all the records in the student table, you can use the following query:

SELECT *
FROM students;

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

To delete yourself from the student table, you will need to use the DELETE statement with a matching condition on your student number. The query would look like this:

DELETE FROM students WHERE student_number = ‘12345’;

To show all the records in the student table after the deletion, you can use the following query:

SELECT *
FROM students;

Once you have completed all the queries and saved them in the file ITCO630_P5.SQL, you can create a zip file called ITCO630_P5.ZIP with your query file included.

Need your ASSIGNMENT done? Use our paper writing service to score better and meet your deadline.


Click Here to Make an Order Click Here to Hire a Writer