I need help completing this assignment for MIS 562 Database Programming and Applications. I have attached a copy of the assignment. It is due in 2 days.
MIS562 Week 4 Assignments
Name: _______________________ Homework (100 points)
Please use the template below to provide your answers. Part 1
Using the tables created in Week 2:
Question (4 pts. per question) SQL Statement or Answer 1. Show a list of all employee names and their
department names and the employees for each
department. Be sure to show all departments
whether there is an employee in the department
or not. Use an outer join.
2. Select all employee names and their department
names. Be sure to show all employees whether
they are assigned to a department or not. Use
an outer join. Using the student schema:
Question (3 pts. per question) SQL Statement or Answer 3. Write a query that performs an inner join of the
grade, student, and grade_type tables.
4. Write a query that performs an inner join of inner
join of the grade, student, and grade_type
tables using the Oracle inner join convention.
5. List all of the zip codes in the ZIPCODE table
that are not used in the STUDENT or
INSTRUCTOR tables. Use a set operator.
6. Write a SQL statement using a set operator to
show which students enrolled in a section are
not enrolled in any classes. Exclude students
with a student id less than 300. Part 2
Question ( 5 pts. per question) SQL Statement or Answer Page 1 MIS562 Week 4 Assignments
1. Write and execute two INSERT statements to
insert rows into the ZIPCODE table for the
following two cities of your choice. After your
INSERT statements are successful, make the
changes permanent. 2. Create a sequence called STUDENT_ID_NEW
that begins with 900 and increments by 1. If the
sequence already exists, drop the sequence and
recreate it. 3. Make yourself a student by writing and executing
an INSERT statement to insert a row into the
STUDENT table with data about you. Use one of
the zip codes you inserted in Exercise 1. Only
insert values into the columns STUDENT_ID
using the sequence you created in Step 2,
FIRST_NAME, LAST_NAME, ZIP,
REGISTRATION_DATE (use a date that is five
days after today), CREATED_BY,
CREATED_DATE, MODIFIED_BY, and
MODIFIED_DATE. Issue a COMMIT command
afterwards. 4. Write an UPDATE statement to update the data
about you in the STUDENT table. Update the
columns SALUTATION, STREET_ADDRESS,
PHONE, and EMPLOYER. Be sure to also
update the MODIFIED_DATE column and make
the changes permanent. 5. Delete the row in the STUDENT table and the
two rows in the ZIPCODE table that you created.
Be sure to issue a COMMIT command
afterwards. Page 2 MIS562 Week 4 Assignments
6. Create a table called TEMP_STUDENT with the
following columns and constraints: a column
STUDID for student ID that is NOT NULL and is
the primary key, a column FIRST_NAME for
student first name; a column LAST_NAME for
student last name, a column ZIP that is a foreign
key to the ZIP column in the ZIPCODE table, a
column REGISTRATION_DATE that is NOT
NULL and has a CHECK constraint to restrict the
registration date to dates after January 1, 2000. 7. Write an INSERT statement violating at least two
of the constraints for the TEMP_STUDENT table
that you just created. 8. Write another INSERT statement that succeeds
when executed, and commit your work. 9. Alter the TEMP_STUDENT table to add two more
columns called EMPLOYER and
EMPLOYER_ZIP. The EMPLOYER_ZIP column
should have a foreign key constraint referencing
the ZIP column of the ZIPCODE table. Update
the EMPLOYER column.
10. Alter the table once again to make the
EMPLOYER column NOT NULL.
11. Write a statement that drops the
EMPLOYER_ZIP column. 12. Drop the TEMP_STUDENT table once you're
done with the exercise. 13. Create a non unique index called
crse_modified_by_i on the MODIFIED_BY
column of the course table. Page 3 MIS562 Week 4 Assignments
14. Change the registration date of Paula Valentine
to today?s date. Create a view called
CURRENT_REGS reflecting all students who
registered today. (You will need to execute this
query in Question 6 on the same day when you
change the date.)
15. Create a view called roster reflecting all students
taught by the instructor Marilyn Frantzen. Query
the view. 16. Given the MY_EMPLOYEE view, what
information is the user allowed to retrieve? Who
can update the SALARY column through the
view? Hint: The USER function returns the name
of the currently logged-in user.
CREATE OR REPLACE VIEW my_employee AS
SELECT employee_id, employee_name, salary,
WHERE manager = USER
WITH CHECK OPTION CONSTRAINT
my_employee_ck_manager Page 4
This question was answered on: Sep 18, 2020
Buy this answer for only: $15
This attachment is locked
We have a ready expert answer for this paper which you can use for in-depth understanding, research editing or paraphrasing. You can buy it or order for a fresh, original and plagiarism-free copy from our tutoring website www.aceyourhomework.com (Deadline assured. Flexible pricing. TurnItIn Report provided)
Pay using PayPal (No PayPal account Required) or your credit card . All your purchases are securely protected by .
About this QuestionSTATUS
Sep 18, 2020EXPERT
GET INSTANT HELP/h4>
We have top-notch tutors who can do your essay/homework for you at a reasonable cost and then you can simply use that essay as a template to build your own arguments.
You can also use these solutions:
- As a reference for in-depth understanding of the subject.
- As a source of ideas / reasoning for your own research (if properly referenced)
- For editing and paraphrasing (check your institution's definition of plagiarism and recommended paraphrase).
NEW ASSIGNMENT HELP?
Order New Solution. Quick Turnaround
Click on the button below in order to Order for a New, Original and High-Quality Essay Solutions. New orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.
WE GUARANTEE, THAT YOUR PAPER WILL BE WRITTEN FROM SCRATCH AND WITHIN YOUR SET DEADLINE.