1 / 16

Advanced SQL Queries and Relational Algebra: Analyzing Movie Stars and Birthdates

This article explores advanced SQL queries that utilize relational algebra concepts to analyze movie databases. It specifically focuses on extracting distinct movie titles featuring stars born in 1974. Through various query examples and logical representations, the text elaborates on how SQL is expressible and non-expressible in relational algebra. It highlights variations in the queries, discussing the significance of conditions, groupings, and averages in retrieving relevant data while ensuring clarity in relational algebra equivalence.

mateja
Download Presentation

Advanced SQL Queries and Relational Algebra: Analyzing Movie Stars and Birthdates

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 16.3 Parser to Logical Query Plans

  2. 16.1. SQL(not RAE) Figure 16.2 select distinct movietitle from starsIn where starname in (select name from moviestar where birthdate like '%1974%'); NOTRAE=Relational Algebra NOT Expressible SQL

  3. 16.3. SQL/RAE of Figure 16.19 select distinct movietitle from starsIn a, (select name from moviestar where birthdate like '%1974%') temp where a.starname = temp.name; • RAE=Relational Algebra Expressible SQL

  4. 16.3Example16.19 Figure 16.24 πmovie title starname=name πname StarsIn σmovieYear lile ‘%1974’ MovieStar This is the RAE SQL

  5. 16.3. SQL/RAE of Figure 16.19 select movietitle from (select starname,movietitle From starsIn) a, (select name from moviestar where birthdate like '%1974%') b where a.starname = b.name;

  6. 16.3Example16.19 Figure 16.24 πmovie title starname=name starame, movie title πname σmovieYear lile ‘%1974%’ StarsIn MovieStar This is the RAE SQL

  7. SQL in Figure 16.20 Select distinct m1.movieTitle, m1.movieYear From StarsIn m1 Where m1.movieYear – 40 <= ( Select AVG(birthdate) From StarsIn m2, Moviestar s Where m2.starName=s.name AND m1.movieTitle = m2.movieTitle AND m1.movieyear = m2.movieyear );

  8. SQL in Figure 16.22 Select distinct m.movieTitle, m.movieYear From StarsIn m1, ( Select m2.movieTitle, m2.movieyear, AVG(birthdate) as ave From StarsIn m2, Moviestar s Where m2.starName=s.name Group by m2.movieTitle, m2.movieyear ) m Where m1.movieTitle = m.movieTitle and m1.movieYear – 40 <=ave;

  9. πm1.movieYearm1.movieYear m1.movieYear -40  abd m2.movietitle=m1.movietitle and m2.movietitle=m1.movietitle γm2.movieTitle, m2.movieyear,AVG(birthDate)ave StarsIn m2,starname=s.name StaesIn MovieStar

  10. πm1.movieYearm1.movieYear m1.movieYear -40  abd m2.movietitle=m1.movietitle and m2.movietitle=m1.movietitle πm1.movieYear,m1.movieYear γm2.movieTitle, m2.movieyear,AVG(birthDate)ave StarsIn m2,starname=s.name StaesIn MovieStar

  11. πm1.movieYearm1.movieYear m1.movieYear -40  abd γm2.movieTitle, m2.movieyear,AVG(birthDate)ave m2,starname=s.name MovieStar StaesIn

  12. πm1.movietitlem1.movieYear m1.movieYear -40  abd γm2.movieTitle, m2.movieyear,AVG(birthDate)ave m2,starname=s.name m1.movietitle m1.movieYear m1.movietitle m1.movieYear StaesIn MovieStar

  13. Lecture on Whiteboard Select PNAME, Sum (QTY) From Parts natural join Shipments Group by PNAME;

  14. Lecture on Whiteboard • pname ,SUM(qty) -->sum •  (Natural Join) Shipments Parts pname ,SUM(qty) -->sum (Shipment  Parts)

  15. Lecture on Whiteboard • Select F.PNAME, Sum (F.QTY) as sum • From ( Select PNAME, QTY • FROM ( SELECT PNUM,PNAME • From Parts) • natural join • ( Select PNUM, QTY • From Shipments) • )F • Group by F.pname;

  16. pname ,SUM(qty) -->sum •  •  pname. qty •  •  (Natural join)     π pnum,qty π pnum, pname   Shipments Parts

More Related