2. Consider the following schema: Books (book_id, title, year, pub_id, auth_id, price) Authors(auth_id, name, nationality) Publisher(pub_id, name) Sales(copies_sold, book_id, copies_printed) Write down the relational algebra expressions for the following queries a) Give the count of the books published by Pearson each year. b) Name the authors who wrote some book in 2015 and 2016 priced above 200 rupees. c) List the title, author name and publisher name of books written by Indian authors in the year 2019 d) List the publisher IDs with least number of books printed in 2020 e) List the publisher who made profit<500 (profit= book price* (copies printed – copies sold) f) List the author whose books are priced the highest.

2. Consider the following schema: Books (book_id, title, year, pub_id, auth_id, price) Authors(auth_id, name, nationality) Publisher(pub_id, name) Sales(copies_sold, book_id, copies_printed) Write down the relational algebra expressions for the following queries a) Give the count of the books published by Pearson each year. b) Name the authors who wrote some book in 2015 and 2016 priced above 200 rupees. c) List the title, author name and publisher name of books written by Indian authors in the year 2019 d) List the publisher IDs with least number of books printed in 2020 e) List the publisher who made profit<500 (profit= book price* (copies printed – copies sold) f) List the author whose books are priced the highest.

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 expressions, which use the result of a relational algebra operation as the input to another operation. For each expression, explain in words what the expression does. a. year 2009 (takes) ▷ student b. year 2009 (takes ▷ student) C. ID.name, course_id (student takes)
Please solve asap my deadline is very close
Write a relational algebra query/expression to show names of all faculty who work at any department that uses "Beau" building. Use the College Relational Schema provided below
Using SQL data definition language, write the SQL statements to create each of the following relations. i. student(student id, name, dept id, total credits)ii. department(dept id, building, budget)iii. lecture_hall(hall id, campus, capacity)iv. course(course id, title, dept id, credits) v. takes(student id, course id, semester, year, grade)
Consider the following expressions, which use the result of a relational algebra operation as the input to another operation. For each expression, explain in words what the expression does. a. year 2009 (takes) ▷ student b. year 2009 (takes ▷ student) C. ID.name, course_id (student takes)
Write 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.
All parts of this question use the following database schema: Flights(flno, from, to, distance) Aircraft(aid, aname, cruisingrange) Certified(eid, aid) Employees(eid, ename, salary) c) For each cruisingrange held by at least 3 aircraft, give the lowest salary of employees certified to fly one of those aircraft. Express in SQL only:
Describe the basic operations of relational algebra in simple terms.
Use the following relational schemas for answering the queries in relational algebra; STUDENT(ID, Name, Phone, Gender) ENROLMENT(Student_ID, Course_ID) COURSE(CID, CName, Instructor) Find the name and phone of all students who registered the course ‘ITE1004’ under ‘Prof. Raghu’.
Consider the following schema: Books(bookID, title, publisher, year, genre)Students(studentID, name, major, age)Borrows(bookID, studentID, date) Express the following queries in SQL. a) Print in descending order the names of all students who borrowed a book on April 1, 2021. b) Find the titles and publishers of all books in the Science Fiction genre that are borrowed by Cybersecurity students. c) Find the majors with average age greater than 20 years old.
Perform the following operation in sql for insertion, updating and deletion using the referential integrity constraints