*** Please be concise and show full explanation. Please help. Show your own work

Question

*** Please be concise and show full explanation. Please help. Show your own work and follow instructions. Databases ***

With the tables provided down below, create queries that have to do with the tables. Has to run on Oracle SQL Developer. More information provided below:

Create 2 stored procedures (can be anything) that have to do with the following tables of the database. In order for credit to be considered, these queries have to have some complexity to them (shouldn't be outright simple). In addition, provide an output along with the statements to show that the queries do as they are told. Provide a small description of the queries as well. These have to be implemented as stored procedures (can cover anything as well). Make sure that there is some complexity to the queries. When talking about anything, an example would be where the stored procedure will fetch the course id based on the passed course name and the course id is then inserted into the registered table along with PID and grade (this is already done). Please help. All of the information should be here.

The tables are down below:

assigned_parking: (person id, parking spot id)

courses: (course id, semester, course name, final date)

department: (department id, department name)

department_assigned: (major id, department id)

faculty: (person id, department id)

major: (major id, depatment id, major name)

parking: (parking spot id)

person: (person id, person's name [any name can go here])

registered: (courses id, person id, grade)

room: (room id)

room_assigned: (room id, courses id)

student: (person id, major id)

teaches: (courses id, person id)

Explanation / Answer

Below are the SQL scripts for the questions asked

Question 1

a) List all the employees in department 5, by fname and lnmae, who have worked on project 30.

SQL QUERY

Select
E.LName,
E.FName
from Employee as E
inner join Department as D on D.DNumber = E.DNo
inner join Project as P on P.Dnum = D.DNumber
Where D.DNumber = 5 AND P.PNumber = 30

The query above will return the employee lastname and firstname by considering only employees who works in the department number = 5 and project number = 30

Question 2

b) List all female employees, by lname and salary, who have worked on project "Reorganization".

SQL QUERY

Select
E.LName,
E.Salary
from Employee as E
inner join Department as D on D.DNumber = E.DNo
inner join Project as P on P.Dnum = D.DNumber
Where E.Sex = 'F' AND UPPER(P.PName) = 'REORGANIZATION'

The query above returns the female employee's name by considering the employees who works in the project Reorganization and the employee gender must be female

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.