Write a query that uses a scalar query in the select clause

Assignment Help Database Management System

Reference no: EM132134646

Solve the following questions using Oracle. You are not allowed to use the syntax of any DBMS other than Oracle. Make sure to upload an electronic copy of your solution to your CSC335 TRACE folder. Name the file hw4.sql. Also, make sure to turn in a stapled hard copy in class on the due date. If the grader is unable to run the file you uplaod to trace, you will receive no grade for this assignment. Each question is worth 10 points.

Find the ids of instructors who are also students using a set operation. Assume that a person is identified by her or his id. So, if the same id appears in both instructor and student, then that person is both an instructor and a student. Remember: set operation means union, intersect or set difference.

Find the ids of instructors who are also students using the set membership operator.

Find the ids of instructors who are also students using a set comparison operator.

Find the ids of instructors who are also students using the exists construct.

Find the names and ids of the students who have taken all the courses that are offered by their departments. Notice, the table course contains information about courses offered by departments.

Find the names and ids of the students who have taken exactly one course in the Spring 2010 semester.

Find the names and ids of the students who have taken at most one course in the Spring 2010 semester. Notice, at most one means one or zero. So, the answer should include students who did not take any course during that semester

Write a query that uses a derived relation to find the student(s) who have taken at least two courses in the Spring 2010 semester. Schema of the output should be (id, number_courses). Remember: derived relation means a subquery in the from clause.

Write a query that uses a scalar query in the select clause to find the number of distinct courses that have been taught by each instructor. Schema of the output should be (name, id, number_courses).

Use an outer join to find names of instructors who did not teach any course in the Spring 2010 semester.

Write a query that uses the with clause or a derived relation to find the id and number of courses that have been taken by student(s) who have taken the most number of courses. Schema of the output should be (id, number_courses).

Reference no: EM132134646

Questions Cloud

Prepare a sustainability assessment report : Prepare a sustainability assessment report referring to the Consolidated Set of GRI Sustainability Reporting Standards 2016 that specifically addresses

Compute the time difference in minutes between the two time : You need to compute the difference in minutes between the start time and end time, returning the number of minutes to your test main.

What are the best practices in writing program : Question: What are the best practices in writing program? Describe each one briefly.

Write a program using python that asks the user to enter : Quantity discounts are given according to the following table: Quantity Discount 10 - 19 20% 20 - 49 30% 50 - 99 40% 100 or more 50%

Write a query that uses a scalar query in the select clause : Find the names and ids of the students who have taken all the courses that are offered by their departments.

How many sons were there and how large was the mans estate : A man left to his eldest son one bezant and a seventh of what was left; then from the remainder, to his next son he left two bezants.

Write a function called grades that takes a list of tuples : Write a function called grades that takes a list of tuples as a parameter. Each tuple in this list contains a name and a grade.

The differences between the limitations on searches : What are the major differences between an officer's felony arrest powers and an officer's misdemeanor arrest powers?

Discuss the concept of punishment : Discuss the concept of punishment. What is punishment and what are the different goals of punishment?

User Account

All Pages