Consider the following schema (keys are underlined): Product(pid , name, type, manufacturer, price), Buys(cid, pid ), Customer(cid , cname, age, gender) Write the following query in relational algebra and SQL (For relational algebra use pi for n, sigma for o, join for M, div for +) . "Find the names of all customers who have purchased all products manufactured by Sears." . "Find the names of all customers who have not purchased the most expensive product." • "Find the names and cids of all customers who have purchased the second most expensive product." You can assume that no two products have the same price.

Consider the following schema (keys are underlined): Product(pid , name, type, manufacturer, price), Buys(cid, pid ), Customer(cid , cname, age, gender) Write the following query in relational algebra and SQL (For relational algebra use pi for n, sigma for o, join for M, div for +) . "Find the names of all customers who have purchased all products manufactured by Sears." . "Find the names of all customers who have not purchased the most expensive product." • "Find the names and cids of all customers who have purchased the second most expensive product." You can assume that no two products have the same price.

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

Use the following table to find the OUTPUT of the following SQL queries: Table Name: Patients MEDICAL_NO FIRST_NAME SUR_NAME DOB GENDER PHONE FAX BLOOD_TYPE WEIGHT SMOKER PCR_TEST 1001 Said Alalawi 12-Jun-2001 Male 98877445 22114454 A. 68 NO Negative 1002 Othman Mualla 20-Apr-2000 Male B 78 YES Positive 1003 Sameera Bader 19-Oct-2005 Female 25541122 52 NO Positive 1004 Fahad Alhinai 24-Feb-2004 Male 91122544 AB 66 YES Negative 1005 Shamsa Alfahdi 15-Sep-2002 Female B 35 NO Negative SELECT CONCAT (SUBSTR(first_name, 1,2), weight) FROM patients WHERE blood_type= 'B' and weight > 70; Answer:
Consider Following Tables. Student ( regNo, sName, sCity, sCGPA, sAge) Course ( cCode, cName, credit_hours) Enrollment (regNo, cCode, semester, Grade) Write SQL queries for the following statements. NOTE: you must use “CARTESIAN PRODUCT” query only. Display grade of ‘Ali’ in course ‘CS101’. Display all Grades of ‘Irshad’ in 7th Display names of all courses and grades of ‘Amir’. Display list of courses which are taught in 7th (course name must not repeat) Display list of all students by name and course name they have Display total number of ‘F’ grade by ‘Amir’. Display all those students who have not enrolled any course
Q2 Consider the following relations: BRANCH (branch no, street, area, city, degree, Tel_no, Fax_no) STAFF (Staff_no, Frame, Luame. address. position, salary. branch_no) Write the following queries in SQL: 1. Find all the branches that number less than 453 and found in area ‘34 east’ 2. Find staff whose salary is greater than the salary of every member of staff at branch 71 3. List all stlf who their last name ends with ‘it 4. Find all the staff that works in branch 345 and have salary between 15000 and 45000,
Write SQL queries for the following. Display details of those patients whose age is either 25 or 36 or 29 without using comparison operator. Display first name and last name of those doctors whose salary is less than or equal to 77,000 and greater than or equal to 50,000 using between operator. Display details of those doctors who neither belong to Isb nor Rwp nor Multan without using in and like operator. Display full name of doctors whose first name is other than Tania. Display first name of doctors whose first name contains any of vowels (a,e,i,o,u). Display names of those patients whose name’s second letter is neither ‘a’ nor ‘d’ Display details of those male patients whose age is 25 or 36. Display details of those patients whose name contains less than 5 characters. Display first 4 doctors who are earning least salary. Display details of all patients in such a way that female patients are shown before male patients. Display the unique cities from doctor table. Display all…
Using the university schema, fill in the blank to complete the SQL query that finds the ID and name of each student who has not taken any course offered before 2017.
Using the relations in the appendix, write the following queries in SQL:i. Display a list of all instructors, showing each instructor’s ID and the number of sections taught. Your query should use an outer join, and should not use sub-queries.ii. Write the same query as in part a, but using a scalar subquery and not using outer join.iii. Display the list of all course sections offered in spring 2018, along with the ID and name of each instructor teaching the section. If a section has more than one instructor, that section should appear as many times in the result as it has instructors. If a section does not have any instructor, it should still appear in the result with the instructor name set to “—”.iv. Display the list of all departments, with the total number of instructors in each department, without using sub-queries. Make sure to show departments that have no instructors, and list those departments with an instructor count of zero.
Three tables are given Student: studentNO, firstName, lastName, studentProgram Professor: professorID, professorProgram, professorName Student_professor: studentNO, professorID, mentor Complete the following requirements: Using the UNION operator, do a full outer join to display the student last name student program, professor name and professor program. Referring to the model created in step 2, determine the PK/FK relationships to build the SQL joining the three Create a query/subquery to display student first name, last name and program. The student program must be same as that in the professor table and the professor program name starts with a ‘C’. This query will use a subquery and not a join.
Consider Following Tables. Student ( regNo, sName, sCity, sCGPA, sAge) Course ( cCode, cName, credit_hours) Enrollment (regNo, cCode, semester, Grade) Write SQL queries for the following statements. NOTE: you must use “CARTESIAN PRODUCT” query only. Display grade of ‘Ali’ in course ‘CS101’. Display all Grades of ‘Irshad’ in 7th Display names of all courses and grades of ‘Amir’. Display list of courses which are taught in 7th (course name must not repeat) Display list of all students by name and course name they have Display total number of ‘F’ grade by ‘Amir’. Display all those students who have not enrolled any course
I need help solving this in PYTHON
Assume a database of Employees of a company have been given as shown below: Employee: Projects: Use SQL queries to find the followings: Find the average salary paid to the employees working in each project together with the project name. For each project, count the number of employees who are paid less than the average salary of that project. Find the name of the projects which have no employees assigned. Determine if there is any project with a budget less than the total salary paid to its employees. If yes, find the name of the project and its budget. The management wants to know which projects have been finished. Add an attribute to Projects table names status to show the current state of the project (Use SQL)
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
Given the table PRODUCT(ProductID, ProductName, ProductClass, ProductWeight, ProductStyle, ProductColor, ProductPrice). Note: ProductWeight and ProductPrice are numeric fields, and the other attributes are character fields, i.e., store text. Write a SQL query to display the product class whose average price is at least 50