[answered] MIS562 Week 4 Assignments Name: _______________________ Hom

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,

 

manager

 

FROM employee

 

WHERE manager = USER

 

WITH CHECK OPTION CONSTRAINT

 

my_employee_ck_manager Page 4

 

---

Solution details:

About this Question

STATUS

Answered

QUALITY

Approved

DATE ANSWERED

Sep 18, 2020

EXPERT

Tutor

ANSWER RATING

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).

This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student.

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.