Database System Concepts
7th Edition
ISBN: 9780078022159
Author: Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher: McGraw-Hill Education
expand_more
expand_more
format_list_bulleted
Expert Solution & Answer
Chapter 2, Problem 14E
a.
Explanation of Solution
Query:
∏ID, person_name(σcompany_name=”BigBank”(works))
Explanation:
- The given query is used to find ID and name of each employee who works for BigBank in the employee
database ...
b.
Explanation of Solution
Query:
πID, person_name, city ( employee ⋈employee.id =works.id ( σcompany_name = "BigBank" ( works )) )
Explanation:
- The given query is used to find ID, name and city of residence of each employee who works for BigBank in the employee database...
c.
Explanation of Solution
Query:
πID, person_name, street, city ( σ(company_name = "BigBank" ∧ salary > 10000 ( works ⋈employee.id = works.id employee ))
Explanation:
- The given query is used to find ID, name, street address and city of residence of each employee who works for BigBank and earns more than $10000 in the employee database...
d.
Explanation of Solution
Query:
∏ID, person_name
(σemployee.city=company.city(employee ⋈employee.id=woks.id works
⋈works.company_name=company.company_name company))
Explanation:
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solutionStudents have asked these similar questions
Consider the employee database of Figure 2.17. Give an expression in the relational algebra to express each of the following queries: a. Find the name of each employee who lives in city “Miami”. b. Find the name of each employee whose salary is greater than $100000. c. Find the name of each employee who lives in “Miami” and whose salary is greater than $100000.
Consider the library database of Figure 3.20.Write the following queries in SQL.a. Find the member number and name of each member who has borrowedat least one book published by “McGraw-Hill”.b. Find the member number and name of each member who has borrowedevery book published by “McGraw-Hill”.c. For each publisher, find the member number and name of each memberwho has borrowed more than five books of that publisher.d. Find the average number of books borrowed per member. Take into accountthat if a member does not borrow any books, then that member doesnot appear in the borrowed relation at all, but that member still counts inthe average.
Consider the bank database of Figure 2.18. Give an expression in the relational algebra for each of the following queries: a. Find the name of each branch located in “Chicago”. b. Find the ID of each borrower who has a loan in branch “Downtown”
Knowledge Booster
Similar questions
- Please Write the following queries in relational algebra, using the university schema.a. Find the ID and name of each instructor in the Physics department.b. Find the ID and name of each instructor in a department located in thebuilding “Watson”.c. Find the ID and name of each student who has taken at least one coursein the “Comp. Sci.” department.d. Find the ID and name of each student who has taken at least one coursesection in the year 2018.e. Find the ID and name of each student who has not taken any coursesection in the year 2018.arrow_forwardConsider the relational database of Figure 3.19, where the primary keys are underlined. Give an expression in SQL for each of the following queries.a. Find the ID, name, and city of residence of each employee who works for “First Bank Corporation”.b. Find the ID, name, and city of residence of each employee who works for “First Bank Corporation” and earns more than $10000.c. Find the ID of each employee who does not work for “First Bank Corporation”.d. Find the ID of each employee who earns more than every employee of“Small Bank Corporation”.e. Assume that companies may be located in several cities. Find the nameof each company that is located in every city in which “Small Bank Corporation” is located.f. Find the name of the company that has the most employees (or companies, in the case where there is a tie for the most).g. Find the name of each company whose employees earn a higher salary, on average, than the average salary at “First Bank Corporation”.arrow_forwardWrite the following queries in relational algebra, using the university schema.a. Find the ID and name of each instructor in the Physics department. b. Find the ID and name of each instructor in a department located in thebuilding “Watson”.c. Find the ID and name of each student who has taken at least one coursein the “Comp. Sci.” department.d. Find the ID and name of each student who has taken at least one coursesection in the year 2018.e. Find the ID and name of each student who has not taken any coursesection in the year 2018.arrow_forward
- Using the instance of the Airport database, formulate the following queries in the relationalalgebra: 1)Display the number of all carriers and the average of their revenues. 2)Display the minimum, the average, and the maximum distances of the flights that arescheduled on October 17th, 2017? 3)Display, for each carrierID, the number of flights and the average of their distances. Examples of how to do it below:arrow_forwardConsider the bank database of Figure 2.18. Give an expression in the relational algebra for each of the following queries: a. Find each loan number with a loan amount greater than $10000. b. Find the ID of each depositor who has an account with a balance greater than $6000. c. Find the ID of each depositor who has an account with a balance greater than $6000 at the “Uptown” branch.arrow_forwardConsider the following schema ,write the relational algebra queries for the following: Supplier(sid,name,address) Parts(pid,name,color) Catalog (sid,pid,cost) i)Find sid of supplier who supplies every part) ii)find the name of suppliers who supplies only red parts. iii) find the ID’s of suppliers who supplies only red parts. iv) find the sid of suppliers who supplies only red parts or are at 221 park street.arrow_forward
- Consider the following Account table and express in Relational Algebra each of the questions below: accountID amount accountType customerID ac200 5000 current 100 ac201 8800 Saving 104 ac202 3900 current 103 ac203 2000 current 101 ac204 15700 Saving 105 ac205 9655 Saving 106 List the details of all the accounts of type ‘Saving’. Find the balance amount of the account that is owned by the customer whose id is 106. List the ids of customers who own an account of type “current” and its balance amounts is less than 7000 Omani rials.arrow_forwardWrite a Structured Query Language (SQL) statement for each of the following questions: a) For the appointment with ApptNo ‘A1008’, postpone the ApptDate to a week later. b) Pholdee has quitted his job as massage therapist from the centre, his TheraID is ‘TH211311’.Clear his record from the table. Assume that there is no related child record. c) Calculate the total sales gained from all the services where their serviceDesc prefix is ‘FOOT’.Give a suitable Alias for the column selected.arrow_forwardConsider the following schema for STUDENT- Student (RollNo, Name, City, Father-Name, Branch, Sem, Per) Write the following queries in Relational Algebra: (i) List the records of students. (ii) List the records such students whose city is Mbd. (iii) List the records of those students whose Percentage marks is more than 75%. (iv) List the records of those students whose Percentage marks is less than 40%. (v) List the records of those students whose Percentage marks between 80 to 90%. (vi) Show student’s name and City and branch who live in Delhi. (vii) Show student’s RollNo, Name and Sem whose Percentage Mark is more than 80%. Plz try to give solution of this question within 1hrs as it is very urgent plzz....arrow_forward
- Consider the library database of Figure 3.20. Write the following queries in SQL.For each publisher, find the member number and name of each member who has borrowed more than five books of that publisher.arrow_forwardConsider the Library database given below. Write the following queries in SQL, assuming that the relations are filled with 8 tuples with complete entries and no null values. member (memno, name, age) book (isbn, title, authors, publisher) borrowed (memno, isbn, date) For each publisher, print the members who have borrowed more than two books of that publisher. i. ii. Display name and member number of those members who have not borrowed any book till now. iii. Find out the book titles, which are borrowed by more than one member.arrow_forwardUsing the relational database given in lab work no. 2, write the following queries in SQL:1) Which course titles were given by at least two different instructors during the past?2) Which departments have at least two instructors and at least ten students?3) Which students took COMP117 from Cem Kalyoncu and COMP124 from Zafer Erenel?4) Which students are advised by Soydan Redif and took at least one course from Ezgi Ulker?5) Which students took multiple courses from Zafer Erenel but not a single course from Vesile Evrim?6) For each department, find out the highest salaried instructor name and the highest total credited student name.You should also present the output of each query. ////the database of Lab 2 is on attached file)arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- Database System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill EducationStarting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSONDigital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON
- C How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSONDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningProgrammable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education
Database System Concepts
Computer Science
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:9780134444321
Author:Tony Gaddis
Publisher:PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:9780132737968
Author:Thomas L. Floyd
Publisher:PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:9780133976892
Author:Paul J. Deitel, Harvey Deitel
Publisher:PEARSON
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:9780073373843
Author:Frank D. Petruzella
Publisher:McGraw-Hill Education