Let T(a,b,c) be a relation in which attribute a is not NULL. Consider the SQL query: SELECT * FROM T Tcopy WHERE a NOT IN (SELECT a FROM T WHERE Tcopy.a>10 ); Which of the following RA queries produces the same result as the SQL query above? Select one: O Ja>10(Ta.b.c (T)) O Ja<=10 (T) OT- Ga<=10 (T) O Tabc(Oa>10 (T))

Let T(a,b,c) be a relation in which attribute a is not NULL. Consider the SQL query: SELECT * FROM T Tcopy WHERE a NOT IN (SELECT a FROM T WHERE Tcopy.a>10 ); Which of the following RA queries produces the same result as the SQL query above? Select one: O Ja>10(Ta.b.c (T)) O Ja<=10 (T) OT- Ga<=10 (T) O Tabc(Oa>10 (T))

Database Systems: Design, Implementation, & Management

12th Edition

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Carlos Coronel, Steven Morris

Chapter11: Database Performance Tuning And Query Optimization

Section: Chapter Questions

Problem 21P: Problems 1721 are based on the following query: SELECT V_CODE, V_NAME, V_CONTACT, V_STATE FROM...

See similar textbooks

Similar questions

Write a SQL trigger to carryout the following action: If an account is deleted, then write a trigger to delete thedependent tuple(s) from the depositor table for every owner of the deleted account. Notethat there may be jointly-owned bank accounts. In other words, you are to write a triggerthat performs the exact action of an ON DELETE CASCADE clause of a FOREIGNKEY CONSTRAINT. You must submit both your trigger function definition, and yourtrigger definition
Consider the following parameters of a relation r (A, B, C) in which A be the primary key. nr = 20000 V (B, r) = 120 max (C, r) = 500 min (C, r) = 0 Estimate the size of the following selection operations. a)σ A = k (r) b)σ A = k1 ∨ B = k2 (r) c)σ C >= Z ∧ C <= 105 (r)
(d) Write an SQL statement, using the inner join construct, that will, for the lecturer John Smith, list in alphabetical order the students taking any module which he teaches. (e) What information does the output from the following SQL query provide? SELECT Lecturer, COUNT(DISTINCT Student) FROM class INNER JOIN staff ON class.Module = staff.Module WHERE Grade < 40 GROUP BY Lecturer; (f) Write an SQL query which would obtain the following information: for each lecturer, what is the average grade obtained by students across all of the modules which they have taught?
A. The relation R(a,b) may have duplicate tuples. Which of the following queries has a result that is guaranteed not to have duplicates, regardless of what tuples R contains? a) SELECT a, AVG(b) FROM R GROUP BY a b) SELECT b FROM R WHERE a = b c) SELECT a FROM R WHERE a >= ALL (SELECT a FROM R) d) SELECT a FROM R WHERE a = 1 B. Relation R(a,b) has no duplicate tuples, although neither a nor b is a key. Relation S(c,d) may have duplicates. Which of the following queries is guaranteed to produce no duplicates? a) SELECT c, d FROM S WHERE c IN (SELECT a FROM R) AND d IN (SELECT b FROM R) b) SELECT S.c, S.d FROM R, S WHERE R.a = S.d AND R.b = S.c c) SELECT c FROM S WHERE c = d d) SELECT c, d FROM S GROUP BY c, d
) Consider the relations r(A, B, D), and s(B, D, F), in which A, B, D and F are their attributes. Thefollowing SQL query based on these two relations has a nested subquery. Provide an equivalent SQL querywhich does not have any nested subquery.select A, Bfrom rwhere r.B < some (select Bfrom swhere s.D= r.D)
Let's assume that the following indices are now set on the schema from the previous task: - Level 2 B+ index on the attribute Vehicle.year - Hash index on the attribute Rental.date Our DBMS has the following algorithms to perform the join: - Block nested loops join - Index nested loops join Translate the following SQL query into relational algebra and find the plan that reads the fewest pages or blocks: SELECT Vehicle.brand, Vehicle.model FROM Vehicle JOIN Rental ON (Vehicle.vid = Rental.vid) WHERE Vehicle.year = 2015 AND Rental.date > ’31.12.2022’
Problems 1721 are based on the following query: SELECT V_CODE, V_NAME, V_CONTACT, V_STATE FROM VENDOR WHERE V_STATE = 'TN' ORDER BY V_NAME; Assume that you have 10,000 different products stored in the PRODUCT table and that you are writing a web-based interface to list all products with a quantity on hand (P_QOH) that is less than or equal to the minimum quantity, P_MIN. What optimizer hint would you use to ensure that your query returns the result set to the web interface in the least time possible? Write the SQL code.
Suppose that you are given a relation R = (A,B,C,D,E) with the following functional dependencies: {BC → ADE, D → B}. Find all candidate keys. thank you in advance
Consider the following SQL query on the university schema: select avg(salary)-(sum(salary) / count(*)) from instructorWe might expect that the result of this query is zero since the average of a set of numbers is defined to be the sum of the numbers divided by the number of numbers. Indeed this is true for the example instructor relation in Figure 2.1. However, there are other possible instances of that relation for which the result would not be zero. Give one such instance, and explain why the result would not be zero.
Consider the following relational schema: s (i, w, x) t (k,I, w) and the following tuples: s = {(1,5,3), (2,8,2)} t= {(1,1,7), (9, null, 2), (5,0,5), (2,2,7)} complete the SQL expression to yield the result below: SQL Expression SELECT .......................... t.w FROM t WHERE t.i .......................... (1,2,7,9); RESULT t.w 7
B2. Consider the following SQL query in the STUDENT relation.SELECT SNO FROM STUDENTWHERE (CR_HOURS > 12 OR MAJOR != “DATABASE”)AND DNAME = “IT”AND (MAJOR = “DATABASE” OR CR_HOURS > 12); a. Simplify the query using idempotency rules b. Draw the relational algebra tree for the above-simplified query.
The following query is given as an expression of relational algebra.Project[Account.aid,Transfer.to_aid](Join[Account.aid=Transfer.from_aid]( Select[amount>1000000](Transfer), Account))All tables have a hash-based index defined on the keys. Assume that we have a B+ treedefined on Transfer.amount and a hash index on Transfer.from_aid.Assume also that our DBMS implements two join algorithms: a) the index nested loopsjoin, and b) the hash join?Determine a query evaluation plan that reads the least number of blocks from the disk.You can use rough estimations!b) Present alternative implementations of the relational algera operation SELECT.

SEE MORE QUESTIONS

Recommended textbooks for you

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

SEE MORE TEXTBOOKS