Changes between Version 6 and Version 7 of UseCase003

Timestamp:

05/21/26 18:18:36 (6 days ago)

Author:

232012

Comment:

--

UseCase003

@@ -25 +25 @@
 {{{
 SELECT release_id, title, genre, record_label, release_date, cover_photo 
-FROM Release;
+FROM RELEASES;
 }}}
 }}}
 
-3. System matches each Release with its corresponding Artist to display the artist_name.
+3. System matches each Release with its corresponding Artist.
 
 {{{#!div style="margin-left: 20px;"
 {{{
-SELECT r.title, a.artist_name 
-FROM Release r
-JOIN Artist a ON r.artist_id = a.artist_id;
+SELECT 
+    r.release_id,
+    r.title, 
+    STRING_AGG(a.artist_name, ', ' ORDER BY ra.release_ordinal) AS artist_names
+FROM RELEASES r
+JOIN RELEASE_ARTISTS ra ON r.release_id = ra.release_id
+JOIN ARTISTS a ON ra.artist_id = a.artist_id
+GROUP BY r.release_id, r.title;
 }}}
 }}}
@@ -43 +48 @@
 {{{#!div style="margin-left: 20px;"
 {{{
-SELECT release_id, format, price, stock 
-FROM Product 
-WHERE release_id = @current_release_id;
+SELECT product_id, format, price, stock, product_description 
+FROM PRODUCTS 
+WHERE release_id = :current_release_id;
 }}}
 }}}
@@ -53 +58 @@
 {{{#!div style="margin-left: 20px;"
 {{{
-SELECT release_id, 'Album' AS type FROM Album
+SELECT release_id, 'Album' AS type FROM ALBUMS
 UNION
-SELECT release_id, 'Single' AS type FROM Single;
+SELECT release_id, 'Single' AS type FROM SINGLE_RELEASES;
 }}}
 }}}