Use the schema provided in Figure 5.5 of your textbook to create database and po

Question

Use the schema provided in Figure 5.5 of your textbook to create database and populate it with sufficient sample data. Use your understanding of database operations to find use cases and write queries for the following criteria: (also take print screen of the output to associate with each query)

a. Demonstrating use of join between two tables: specify a valid question (use case) that would result in a join operation of any two tables, also provide the SQL query.

b. Demonstrating use of join between three tables: specify a valid question (use case) that would result in a join operation of any three tables, also provide the SQL query.

c. Demonstrating use of join between two tables and aggregating the results (group by): specify a valid question (use case) that would result in a join operation of any two tables and then aggregation, also provide the SQL query.

d. Demonstrating use of join between three tables and aggregating the results (group by): specify a valid question (use case) that would result in a join operation of any three tables and then aggregation, also provide the SQL query.

e. Demonstrating use of join between three tables, aggregating the results (group by) and filtering the final result using having: specify a valid question (use case) that would result in a join operation of any three tables and then aggregation along with having, also provide the SQL query.

Explanation / Answer

Here Figure 5.5. not provided... If you provide link I will change according to that...

Here is example:

-- a)

SELECT *

FROM Student S JOIN Enroll E ON S.stud_id = E.stud_id;

--b)

SELECT *

FROM (Student S JOIN Enroll E ON S.stud_id = E.stud_id) JOIN Course C ON E.course_id = C.course_id;

--c)

SELECT S.stud_id, COUNT(*)

FROM Student S JOIN Enroll E ON S.stud_id = E.stud_id

GROUP BY S.stud_id;

--d)

SELECT C.course_id, COUNT(*)

FROM (Student S JOIN Enroll E ON S.stud_id = E.stud_id) JOIN Course C ON E.course_id = C.course_id

GROUP BY C.course_id;

-- e)

SELECT C.course_id, COUNT(*)

FROM (Student S JOIN Enroll E ON S.stud_id = E.stud_id) JOIN Course C ON E.course_id = C.course_id

GROUP BY C.course_id

HAVING COUNT(*)>10;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

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