Express the following SQL queries in relational algebra:
1) What are the dates of the concert of Lionel Richie at the Bell Center?
SELECT date FROM Concert C, Show S, Hall H
WHERE
C.show_id = S.show_id
AND S.hall_id = H.hall_id
AND S.singer = 'Lionel Richie' AND H.name = ‘Bell Center’
Solution:
Explanation:
I have explained each and every part with the help of image attached to it.
Express the following SQL queries in relational algebra: 1) What are the dates of the concert...
Express the following SQL queries in relational algebra: 2) What are the singers who have never scheduled the concert at Saint-Denis 2? SELECT singer FROM Show WHERE singer NOT IN (SELECT singer FROM Show S, Hall H WHERE S.hall_id = H.hall_id AND H.name = 'Saint-Denis 2')
A management organization of shows, concert halls, and ticket sales for shows manages a database with the following relational schema: Show (show_id, title, start_date, duration, singer, hall_id) Concert (concert_id, date, hour, show_id) Hall (hall_id, name, address, capacity) Express the following SQL queries in relational algebra: What are the singers who performed at least one concert in all halls? SELECT singer FROM Show S1 WHERE NOT EXISTS (SELECT * FROM Hall H WHERE NOT EXISTS (SELECT * FROM Show S2 WHERE...
Transcribe the following SQL queries into an equivalent relational algebra expression. 1. SELECT s_number , AVG( grade ) FROM grades GROUP BY s_number HAVING AVG( grade ) > 5 . 5 ; 2. SELECT SUM( volume ) FROM fertilizer_applications LEFT JOIN crop_field ON fertilizer_applications . crop_field_id = crop_field . id GROUP BY field_number HAVING field_number = 3 ;
5.10 Express each of the following queries in relational algebra and (ii) SQL using the Student Registration System schema of Figure 3.4. a. List all courses that are taught by professors who belong to the EE or MGT departments. Exercises 189 b. List the names of all students who took courses both in spring 1997 and fall 1998 38 CHAPTER 3 The Relational Data Model FIGURE 3.4 Fragment of the Student Registration database schema STUDENT (Id:INTEGER, Name:STRING, Address:STRING, Statu PROFESSOR...
This is Relational Algebra NOT SQL!!! I need the following 3 queries written in relational algebra. Retrieve the title of all the Movies in Japanese that don't have any comment. (The language_id for Japanese is 3) Find all the movie titles where an actor called “TOM” OR an actor called “BEN" acted, AND there was another actor called “MARY” acting too. Find the actors that have movies in all the categories. (category IDs are numbered 1-16) actor actor_id number first_name...
Consider following relational database schema and translate the queries ‘a’ and ‘b’ into relational algebra expressions Teacher(ID, Name, Designation, Phone, Address) Course(CourseCode, CourseName, CreditHour) Taught(TeacherID, CourseCode, Semester) a. SELECT Name, Address FROM Teacher, Taught WHERE Teacher.ID=Taught.TeacherID and CourseCode=’IT344’ b. SELECT CourseName FROM Course, Taught WHERE Course.CourseCode=Taught.CourseCode and TeacherID=200
Based on the schemas given below, give the appropriate “SQL” and “Relational algebra” statements for the given queries Marking scheme: 1 mark for each correct syntax for SQL and Relational Algebra Employee (fname, Iname, ssn, sex, salary, dno) Department (dname, dno, managersn, dlocation) Project (pname, pno, plocation, dno) a) Find out which department “Evelyn" is working in. b) Retrieve the names of all the projects which are located in “Isa Town". c) Count the number of employees who receive a...
Write relational algebra formulas what will satisfy the following queries: A- What are the names of all the planes that participated in a dog fight in which Hard Luck (a name of a plane) also participated? B- What are the names of all the dogfights that must have happened entirely under 30,000 feet?
Intro to database systems: Q7. Formulate the following SQL query in relational algebra using an efficient way. The schema is given in the following. Emp(eno, ename, title, city) Proj(pno, pname, budget, city) Works(eno, pno, since) SELECT ename FROM Works , Emp WHERE Works.pno = ‘P150’ AND Works.eno = Emp.eno
Give the following queries in the relational algebra using the following relational schema: Student (sid, name, age, address, email, cgpa) enrolledIn (sid, modulecode, grade) module (modulecode, modulename, credits, lecturername) a) What are the names of students enrolled in module code “cs3020”? b) Give the module code of all the modules that are taught by the teacher named “Altaf”? c) Who teaches the module with code”cs1500”? d) Give the name of all the teachers teaching the student with id”12f6778”.