Consider the following relations. • Student (zno, sname, age) · Enroll (zno, cno) • Course (cno, cname, semester) Given the following SQL, which one is the best index to improve the query performance. SELECT S.zno, S.sname, count(cno) FROM Student S, Enroll E WHERE S.zno = E.zno GROUP BY S.zno HAVING count(cno) >10;

Consider the following relations. • Student (zno, sname, age) · Enroll (zno, cno) • Course (cno, cname, semester) Given the following SQL, which one is the best index to improve the query performance. SELECT S.zno, S.sname, count(cno) FROM Student S, Enroll E WHERE S.zno = E.zno GROUP BY S.zno HAVING count(cno) >10;

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

Consider the following relational schemaemployee (ename, street, city)works (ename, cname, salary, jdate)company (cname, city)manages (ename, mname)Write down the following queries in SQL 1. Find the names of all employees who earn more than every employee of Square Pharma. 2. Give all managers in this database a 4 percent salary raise, unless the salary would be greaterthan Tk.100,000. In such cases, give only a 3 percent raise.
Just return SQL code/query with proper functions for each of the following. No output needed. Database Schema: Beers(name, manf)Bars(name,addr,license)Drinkers(name,addr,phone)Likes(drinker, beer)Sells(bar,beer,price)Frequents(drinker, bar) 1. Find all distinct drinkers whose phone numbers come from area code 917 and who like Budweiser or Bud (synonim!) 2. What beers does Mike like? 3. Which town has the most drinkers? 4. What bars are frequented by drinkers from that town (3)? 5. Provide all bars which serve beers that Mike likes 6. Who likes at least one same beer that Joe or Mike like? 7. All bars which sell at least one beer which is liked by at least one drinker who frequents these bars 8. Drinkers who like some beers sold by Caravan bar 9. Bars which sell Budweiser and are frequented by some drinkers who like Budweiser 10. Bars which are frequented by Mike and Steve 11. Drinker who like at least two beers that Mike likes 12. Bars which sell at least 3 beers that Mike likes (do…
Q1) Consider the employees database , where the primary keys are underlined. Give an expression in SQL for each of the following queries. Employee(ID. name, age, address, salary, departmet, position,ministry_name) Ministry(ministry_name, address,No_of _employee, No_of_dept., category) 1- Find all employees who live in baghdad and work in ministery of foreign affairs And older than all the employees who work in the consuls department. 2- Find the name of employees who work as a counselor in ministries that have more than 5 departments . 3- Insert a new employee (o the database.
Write the following queries, based on the database schema Product(maker, model, type)PC(model, speed, ram, hd, price)Laptop(model, speed, ram, hd, screen, price)Printer(model, color, type, price) of Exercise 2.4.1. You should use at least one subquery in each of your answersand write each query in two significantly different ways (e.g., using differentsets of the operators EXISTS, IN, ALL, and ANY). a) Find the makers of PC's with a speed of at least 3.0.b) Find the printers with the highest price.! c) Find the laptops whose speed is slower than that of any PC.! d) Find the model number of the item (PC, laptop, or printer) with thehighest price.! e) Find the maker of the color printer with the lowest price.!! f) Find the maker(s) of the PC(s) with the fastest processor among all thosePC's that have the smallest amount of RAM.
Consider the following relational schema:Course (cid, cname, dept, credit)Student (sid, sname, dept, gpa)Enroll (cid, sid, grade, semester) The meaning of these relations is straightforward and the keys are underlined. Write thefollowing queries in SQL. d) Find the name s of students who took courses offered by CS department and noother department. e) Find the names of all classes that have 10 or more students enrolled.
Consider the following SQL query that seeks to find a list of titles of all coursestaught in Spring 2017 along with the name of the instructor.select name, titlefrom instructor natural join teaches natural join section natural join coursewhere semester = 'Spring' and year = 2017What is wrong with this query?
Consider a scenario in which the SQL statement is of the form SELECT DISTINCT c1, c2, c3 from T. Why might it make sense to create an index to optimize this query?
Consider the following relational schema:Course (cid, cname, dept, credit)Student (sid, sname, dept, gpa)Enroll (cid, sid, grade, semester) The meaning of these relations is straightforward and the keys are underlined. Write thefollowing queries in SQL. a) List all the students who never got a grade above ‘C’.b) Find the number of students not enrolled in any class.c) Find name of students in EE department who took all the courses offered by CSdepartment.
3. Using the following DB schema for the PATIENT table, right a query that will return all fields for patients from KS and any other state that starts with a K and sort ascending by MRN. PATIENT (MRN, FirstName, LastName, Address, City, State, Zip, PrimaryCareProvider)
Consider the following relational schema and answer the following questions. User (email, name, address, householdSize) a. Express in relational algebra the query that finds all pairs of users where the two people both claim to have a household size 2 and have the same address and returns their names and the common address. b. Express the above query in SQL. c. Write in SQL the query that finds the users whose household size is at least 50% more than the average household size and returns their name and household size, sorted by household size. (Hint: decompose the problem into sub-problems, and you may use a view to capture an intermediate result.) d. Write in SQL the query that finds all users each having a household size different from the total number of users having the same address as him or her. (Hint: again, decompose the problem into sub-problems, and you may use a view to capture a intermediate result.)
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 schema and write SQL query for given statements. Student ( regNo, sName, sCity, sCGPA, sAge)Course ( cCode, cName, credit_hours)Enrollment (regNo, cCode, semester,quality_points,Grade) 1- Display 2nd row of Student table using Cursors. 2- Display all records of Enrollment table which are placed at even rows. 3- Execute given query in SQL and do as directed.a) UPDATE STUDENT SET ECITY = ‘ISB’ WHERE EID>101b) Count how many records have been updated in above query using cursor. 4- Write a mechanism which must not allow user to delete more than 1 records using cursor and transaction.