Write sql queries to compute the average rating using avg

Assignment Help Database Management System

Reference no: EM13934286

Consider the instance of the Sailors relation shown in Table 1.

1. Write SQL queries to compute the average rating, using AVG; the sum of the ratings, using SUM; and the number of ratings, using COUNT.

2. If you divide the sum computed above by the count, would the result be the same as the average? How would your answer change if the above steps were carried out with respect to the age field instead of rating?

sid 18	sname jones	rating 3	age 30.0
41	jonah	6	56.0
22	ahab	7	44.0
63	moby	null	15.0

Table 1: An Instance of Sailors

3. Consider the following query: Find the names of sailors with a higher rating than all sailors with age <>21. The following two SQL queries attempt to obtain the answer to this question. Do they both compute the result? If not, explain why. Under what conditions would they compute the same result?

SELECT S.sname

FROM Sailors S

WHERE NOT EXISTS ( SELECT *

FROM Sailors S2

WHERE S2.age 21

AND S.rating = S2.rating )

SELECT *

FROM Sailors S

WHERE S.rating > ANY ( SELECT S2.rating

FROM Sailors S2

WHERE S2.age <>21 )

4. Consider the instance of Sailors shown in Figure 5.1. Let us define instance S1 of Sailors to consist of the first two tuples, instance S2 to be the last two tuples, and S to be the given instance.

(a) Show the left outer join of S with itself, with the join condition being sid=sid.

(b) Show the right outer join of S with itself, with the join condition being sid=sid.

(d) Show the left outer join of S1 with S2, with the join condition being sid=sid.

(e) Show the right outer join of S1 with S2, with the join condition being sid=sid. (f) Show the full outer join of S1 with S2, with the join condition being sid=sid.

	sid 18	sname jones	rating 3	age 30.0	sid 18	sname jones	rating 3	age 30.0
4. (a)	41	jonah	6	56.0	41	jonah	6	56.0
	22	ahab	7	44.0	22	ahab	7	44.0
	63	moby	null	15.0	63	moby	null	15.0

1. SELECT AVG (S.rating) AS AVERAGE

FROM Sailors S

SELECT SUM (S.rating)

FROM Sailors S

SELECT COUNT (S.rating)

FROM Sailors S

2. The result using SUM and COUNT would be smaller than the result using AV- ERAGE if there are tuples with rating = NULL. This is because all the aggregate operators, except for COUNT, ignore NULL values. So the first approach would compute the average over all tuples while the second approach would compute the average over all tuples with non-NULL rating values. However, if the aggregation is done on the age field, the answers using both approaches would be the same since the age field does not take NULL values.

3. Only the first query is correct. The second query returns the names of sailors with a higher rating than at least one sailor with age <>21. Note that the answer to the second query does not necessarily contain the answer to the first query. In particular, if all the sailors are at least 21 years old, the second query will return an empty set while the first query will return all the sailors. This is because the NOT EXISTS predicate in the first query will evaluate totrue if its subquery evaluates to an empty set, while the ANY predicate in the second query will evaluate to false if its subquery evaluates to an empty set. The two queries give the same results if and only if one of the following two conditions hold:

- The Sailors relation is empty, or

- There is at least one sailor with age > 21 in the Sailors relation, and for every sailor s, either s has a higher rating than all sailors under 21 or s has a rating no higher than all sailors under 21.

Reference no: EM13934286

Questions Cloud

What should lewis have done : Of all the decisions made by Ken Lewis in this case study, which one do you think did the most damage to his reputation? Why?

Different logistics activities conducted by iaj : a) What are IAJ's logistic requirements and how would you go about identifying them? b) What are the different logistics activities conducted by IAJ and are they being effectively employed by the company?

Describe the demographics of henrietta lacks : Describe the demographics of Henrietta Lacks. Provide a summary of the researcher's actions. What ethical principles in this research are violated?

How would a coach help develop selected competency in client : In today's organizational atmosphere, which of these four would you consider to be the most important competency and why? How would a coach help develop this selected competency in a client?

Write sql queries to compute the average rating using avg : Write SQL queries to compute the average rating, using AVG; the sum of the ratings, using SUM; and the number of ratings, using COUNT.

If you discovered that a colleague at work : If you discovered that a colleague at work had lied on her r sum , what would you do?

Complete the cost of goods manufactured schedule : An incomplete cost of goods manufactured is presented below. Instruction- Complete the cost of goods manufactured schedule for Hobbit Company

Tony escalera solve the quality problem : The data from the drive quality tests conducted at DataStor over the past 150 shifts are contained in the file DATASTOR. The Data Description section contains a description of this data file.

Current health and safety legislation and regulations : Describe the roles and responsibilities of those involved in your selected engineering workplaces under current health and safety legislation and regulations (P2).

User Account

All Pages