Consider the relation EMPLOYEE(Eid, Name, Did, Salary, Age, Phone). Create indexes to support the following three important queries. Describing these indexes (tree, hash, cluster, etc.) and explaining your reasons are sufficient. You do not need to write the SQL statements. SELECT E.Phone SELECT E.Name SELECT E.Did, AVG (E.Salary) FROM EMPLOYEE E FROM EMPLOYEE E FROM EMPLOYEE E WHERE E.Eid = ... some ID WHERE E.Age < 45 GROUP BY E.Did

Consider the relation EMPLOYEE(Eid, Name, Did, Salary, Age, Phone). Create indexes to support the following three important queries. Describing these indexes (tree, hash, cluster, etc.) and explaining your reasons are sufficient. You do not need to write the SQL statements. SELECT E.Phone SELECT E.Name SELECT E.Did, AVG (E.Salary) FROM EMPLOYEE E FROM EMPLOYEE E FROM EMPLOYEE E WHERE E.Eid = ... some ID WHERE E.Age < 45 GROUP BY E.Did

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

Implement the following ER model and sample data using Oracle SQL Developer and your CDM Oracle database account. You will submit a SQL script file and its entire outputs. The table name should be STUDENTS (plural). Each StudentID should be a five-digit number. LastName and FirstName must be entered. 12341, Doe, Jane 12342, [last name of your choice], [first name of your choice] 12343, [last name of your choice], [first name of your choice] Enrollment The table name should be ENROLLMENTS (plural). Quarters should be something like 2021Fall, 2022Winter, 2022Spring, 2022Summer1, 2022Summer2, 2022Summer10, and so forth. Grades in future quarters are entered as null. 2021Fall, 12341, IS411, A 2022Winter, 12342, IS421, null 2022Summer10, 12343, [course number of your choice], null Course The table name should be COURSES (plural). CourseNumbers should be strings of up to seven characters (e.g., IS411, DSC411, CSEC440). The course and instructor names should be strings of characters.…
This question is related to the Database:- Suppose you are creating a database for a library management system. Explain how you would make a table for the "books" entity and insert some sample data, and then alter the table to add a new column for the "authorID" attribute and update "authorID" with some data. Finally, make a SQL query to retrieve all the books that were published after the year 2000. Note that this book schema has several columns, including "book_id" as the primary key, "title", "authorN", "publisher", "publication_year", "isbn", "language", and "num_pages". It also includes "available" column that is set to "true" by default and can be used to track the availability of the book. Please don't use handwriting...
Write a SQL statement to get the list of all the law firms from thedatabase in alphabetical order by firm name. Include all thecolumns from the LawFirm table.Write a SQL statement to get the list of all the law firms with aname that contains the letter Z. Include all fields from the LawFirmtable.Write a SQL statement to get the list of all the lawyers, showingonly the name and surname of the lawyer, and the name of theirfirm. What is the difference between the WHERE and HAVING clauses in SQLstatements? What is the purpose of an index in a SQL database?
Consider an employee database with two relationsemployee (employee name, street, city)works (employee name, company name, salary) where the primary keys are underlined.Write a function avg salary that takes a company name as an argument and finds the average salary of employees at that company. Then, write an SQL statement, using that function, to find companies whose employees earn a higher salary, on average, than the average salary at “First Bank”.
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…
Table: Team Member Primary key: Team MemberID Team MemberID 1 2 3 Team MemberName Joe Bloggs Samantha Smith Pete Ngwenya Table: Task Primary key: TaskID Foreign key: Team MemberID TaskID 1 2 3 Description Build login screen Implement inventory management Add logo to splahs 1 screen TeamMemberEmail joe@theoffice.com sam@theoffice.com pete@theoffice.com Duration StartDate Team MemberID 2022-05-06 1 2022-05-15 1 4 10 2022-05-06 2
Let's wa(k through a review of basic SQL syntax, using as an example the database that was mentionedearlier. This database has the following simple structure(* indicates a primary key):Courses: CourseID*, CourseName, TeacherIDTeachers: TeacherID*, TeacherNameStudents; StudentID*, StudentNameStudentCourses: CourseID*, StudentID*Using the above table, implement the following queries.Query 1: Student Enrollment Query 2: Teacher Class Size
The following SQL queries use the instances of R1 of "Reserves" the instances of B1 of "Boats", and the instances of S1 of "Sailors" relation. An instance R1 of Reserves bid 101 103 103 day 10/10/96 11/12/96 10/11/96 12/01/96 12/05/96 10/10/09 10/10/98 104 10/07/98 11/10/98 09/08/98 09/05/98 09/08/98 SIC 22 22 31 31 22 22 31 64 64 74 103 102 104 102 101 102 103 An instance B1 of Boats bid 101 102 103 bname Interlake Interlake Clipper Marine 104 bcolor blue red green red An instance S2 of Sailors SIG 28 31 21 44 26 58 22 22 29 29 22 32 2 64 21 74 85 95 71 sname yuppy Tubber guppy rusty Dustin Brutus Andy Horte Horatio Zorba Art Bob Amy rating 81 5 10 11 1 & |10|| El 3 115 age 35 155 135 135 45 33 25 135 18 25 635 46 1.If we wish to list out only the names and ratings of the sailors, we can write this in SQL statement as 2.Find out only the names and ages of sailors. 3.To find out the names and ratings of sailors with a rating above 8, we can combine two of the preceding queries as:…
For the following database scheme Employee(empNo,fName.IName.address.DOB.sex,position.deptNo) Department(deptNo.deptName,mgrEmpNo) Project(projNo.projName,deptNo) WorksOn(empNo.projNo.dateWorked,hoursWorked) Write an SQL statement to find all the details of employees who are female
Consider the following database Scherpa for Car Ownership Database. In the database, we store information about cars and their owners. A car is owned by one owner, but an owner mayown many cars. Car (No, model, year, OwnerlD) Owner (ID, CPR, Name, address,Tel) Write SQL statements to: 1. List ID and name of all customers living in 'Bahrain'. 2. Delete all cars records from Car with unknown owner.
Consider an employee database with two relationsemployee (employee_name, street, city)works (employee_name, company_name, salary) where the primary keys are underlined. Write a function avg_salary that takes a company name as an argument and finds the average salary of employees at thatcompany. Then, write an SQL statement, using that function, to find companieswhose employees earn a higher salary, on average, than the average salary at“First Bank”.
Given the Bank database that contains the following tables (primary keys are underlined and foreign keys are preceded with #): Agency(agencyNumber, agencyName, city, assets) Client (clientNumber, firstName, lastName, clientCity) Account(accountNumber, #agencyNumber, #clientNumber, balance) Loan(loanNumber, #agencyNumber, #clientNumber, amount ) Without using DISTINCT, write the SQL query equivalent to the following one SELECT DISTINCT clientNumber FROM Account WHERE balance < 1000 OR balance > 100000 ;