*** 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 in 2 parts:

a) Create 2 queries (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 output along with the statements to show that the queries do as they are told. Provide a small description of the queries as well.

b) Create 3 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.

In total, there should be at least 5 queries, 2 of which can cover anything, and 3 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 a query where a student doesn't park in a specific parking spot. 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

a)
No. of student enrolled in a particular course. Arranging the result in descending order of count of student
. This will also give the most popular courses. 2 tables and joined and aggregate function COUNT is used with
the group by clause. Order by is used to sort the result in descending order.

SELECT CID, SEMESTER, CNAME, COUNT(PID)
FROM courses c
INNER JOIN registered r
ON c.CID = r.CID
GROUP BY CID, SEMESTER, CNAME
ORDER BY COUNT(PID) DESC;

2nd query
This query will give the department detail along with majors in those department and assigned faculty in those department and major.
Data of the department with starts with Computer is fetched. 3 tables namely department, faculty and major are joined on common attributes
to get the result.

SELECT d.DID, d.DNAME, m.MID, m.MNAME, f.PID
FROM department d
INNER JOIN faculty f
ON f.DID = d.DID
INNER JOIN major m
ON m.DID = d.DID
WHERE d.DNAME LIKE 'Computer%';

b)
The stored procedure given below 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.

CREATE OR REPLACE Procedure UpdateCourse
( name_in IN varchar2 )

IS
cnumber number;

cursor c1 is
SELECT CID
FROM courses
WHERE CNAME = name_in;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;
end if;

INSERT INTO registered
( CID,
PID,
GRADE )
VALUES
( name_in,
cnumber,
'X' );

commit;

close c1;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

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