Index: SQL/constrains/general_constrains.sql =================================================================== --- SQL/constrains/general_constrains.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) +++ SQL/constrains/general_constrains.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) @@ -0,0 +1,34 @@ +-- Default Media Type Constrain +ALTER TABLE track +ALTER COLUMN media_type_id SET DEFAULT 1; + +ALTER TABLE track +DROP CONSTRAINT IF EXISTS track_media_type_id_fkey; + +ALTER TABLE track +ADD CONSTRAINT track_media_type_id_fkey +FOREIGN KEY (media_type_id) REFERENCES media_type(media_type_id) +ON DELETE SET DEFAULT; + + +SELECT setval(pg_get_serial_sequence('invoice_line', 'invoice_line_id'), (SELECT MAX(invoice_line_id) FROM invoice_line)); + +SELECT setval(pg_get_serial_sequence('playlist_track', 'id'), (SELECT MAX(id) FROM playlist_track)); + +SELECT setval(pg_get_serial_sequence('playlist', 'playlist_id'), (SELECT MAX(playlist_id) FROM playlist)); + +SELECT setval(pg_get_serial_sequence('artist', 'artist_id'), (SELECT MAX(artist_id) FROM artist)); + +SELECT setval(pg_get_serial_sequence('album', 'album_id'), (SELECT MAX(album_id) FROM album)); + +SELECT setval(pg_get_serial_sequence('track', 'track_id'), (SELECT MAX(track_id) FROM track)); + +SELECT setval(pg_get_serial_sequence('media_type', 'media_type_id'), (SELECT MAX(media_type_id) FROM media_type)); + +SELECT setval(pg_get_serial_sequence('genre', 'genre_id'), (SELECT MAX(genre_id) FROM genre)); + +SELECT setval(pg_get_serial_sequence('customer', 'customer_id'), (SELECT MAX(customer_id) FROM customer)); + +SELECT setval(pg_get_serial_sequence('employee', 'employee_id'), (SELECT MAX(employee_id) FROM employee)); + + Index: SQL/constrains/soft_delete.sql =================================================================== --- SQL/constrains/soft_delete.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) +++ SQL/constrains/soft_delete.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) @@ -0,0 +1,66 @@ +--ADDING DELETED_AT COLUMN +DO $$ +DECLARE + r RECORD; +BEGIN + FOR r IN + SELECT table_name + FROM information_schema.tables + WHERE table_schema = 'public' + AND table_type = 'BASE TABLE' + LOOP + EXECUTE format('ALTER TABLE public.%I ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP;', r.table_name); + END LOOP; +END $$; + +--SOFT DELETE TRIGGER +DO $$ +DECLARE + r RECORD; + pk_col TEXT; +BEGIN + FOR r IN ( + SELECT table_name + FROM information_schema.tables + WHERE table_schema = 'public' + AND table_type = 'BASE TABLE' + ) + LOOP + SELECT kcu.column_name INTO pk_col + FROM information_schema.table_constraints tc + JOIN information_schema.key_column_usage kcu + ON tc.constraint_name = kcu.constraint_name + AND tc.table_name = kcu.table_name + WHERE tc.constraint_type = 'PRIMARY KEY' + AND tc.table_name = r.table_name + LIMIT 1; + + IF pk_col IS NULL THEN + RAISE NOTICE 'Table % has no primary key, skipping soft delete trigger', r.table_name; + CONTINUE; + END IF; + + EXECUTE format('ALTER TABLE public.%I ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP;', r.table_name); + + EXECUTE format( + 'CREATE OR REPLACE FUNCTION soft_delete_%I() + RETURNS TRIGGER AS $func$ + BEGIN + UPDATE %I SET deleted_at = NOW() WHERE %I = OLD.%I; + RETURN NULL; + END; + $func$ LANGUAGE plpgsql;', + r.table_name, r.table_name, r.table_name, pk_col + ); + + EXECUTE format( + 'DROP TRIGGER IF EXISTS trg_soft_delete_%I ON %I; + CREATE TRIGGER trg_soft_delete_%I + BEFORE DELETE ON %I + FOR EACH ROW + EXECUTE FUNCTION soft_delete_%I();', + r.table_name, r.table_name, r.table_name, r.table_name, r.table_name + ); + END LOOP; +END $$; + Index: SQL/normalization/personal_info.sql =================================================================== --- SQL/normalization/personal_info.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) +++ SQL/normalization/personal_info.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) @@ -0,0 +1,117 @@ +CREATE TABLE PersonalInfo ( + personal_info_id SERIAL PRIMARY KEY, + address TEXT, + city TEXT, + state TEXT, + country TEXT, + postalcode TEXT, + phone TEXT, + fax TEXT, + email TEXT +); + +ALTER TABLE employee +ADD COLUMN personal_info_id INTEGER REFERENCES PersonalInfo(personal_info_id); + +ALTER TABLE customer +ADD COLUMN personal_info_id INTEGER REFERENCES PersonalInfo(personal_info_id); + +--PERSONAL INFO DATA MIGRATION +INSERT INTO PersonalInfo (address, city, state, country, postalcode, phone, fax, email) +SELECT DISTINCT address, city, state, country, postal_code, phone, fax, email +FROM employee; + +UPDATE employee e +SET personal_info_id = p.personal_info_id +FROM PersonalInfo p +WHERE e.address = p.address + AND e.city = p.city + AND e.state = p.state + AND e.country = p.country + AND e.postal_code = p.postalcode + AND e.phone = p.phone + AND e.fax = p.fax + AND e.email = p.email; + +INSERT INTO PersonalInfo (address, city, state, country, postalcode, phone, fax, email) +SELECT DISTINCT address, city, state, country, postal_code, phone, fax, email +FROM customer; + +UPDATE customer c +SET personal_info_id = p.personal_info_id +FROM PersonalInfo p +WHERE c.address = p.address + AND c.city = p.city + AND c.country = p.country + AND c.email = p.email; + + +ALTER TABLE employee +DROP COLUMN address, +DROP COLUMN city, +DROP COLUMN state, +DROP COLUMN country, +DROP COLUMN postal_code, +DROP COLUMN phone, +DROP COLUMN fax, +DROP COLUMN email; + +ALTER TABLE customer +DROP COLUMN address, +DROP COLUMN city, +DROP COLUMN state, +DROP COLUMN country, +DROP COLUMN postal_code, +DROP COLUMN phone, +DROP COLUMN fax, +DROP COLUMN email; + +ALTER TABLE personalinfo +RENAME COLUMN postalcode TO postal_code; + +SELECT * +FROM employee + +CREATE TABLE Contact ( + contact_id INT PRIMARY KEY, + phone VARCHAR(50), + fax VARCHAR(50), + email VARCHAR(100) +); + +INSERT INTO Contact (contact_id, phone, fax, email) +SELECT + personal_info_id, + Phone, + Fax, + Email +FROM personalinfo; + +ALTER TABLE Employee +ADD contact_id INT; + +UPDATE Employee +SET contact_id = employee.personal_info_id +where employee.personal_info_id IS NOT NULL; + +ALTER TABLE Employee +ADD CONSTRAINT FK_Employee_Contact FOREIGN KEY (contact_id) +REFERENCES Contact(contact_id); + +ALTER TABLE Customer +ADD contact_id INT; + +UPDATE Customer +SET contact_id = customer.personal_info_id +where customer.personal_info_id IS NOT NULL; + +ALTER TABLE Customer +ADD CONSTRAINT FK_Customer_Contact FOREIGN KEY (contact_id) +REFERENCES Contact(contact_id); + +ALTER TABLE personalinfo +DROP COLUMN fax, +DROP COLUMN phone, +DROP COLUMN email; + +ALTER TABLE personalinfo RENAME TO address_info; Index: SQL/procedures/insert_track_with_price.sql =================================================================== --- SQL/procedures/insert_track_with_price.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) +++ SQL/procedures/insert_track_with_price.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) @@ -0,0 +1,35 @@ +CREATE OR REPLACE PROCEDURE insert_track_with_price( + p_name VARCHAR, + p_album_id INT, + p_media_type_id INT, + p_genre_id INT, + p_composer VARCHAR, + p_milliseconds INT, + p_bytes INT, + p_price NUMERIC(10,2) +) +LANGUAGE plpgsql +AS $$ +DECLARE + new_track_id INT; +BEGIN + INSERT INTO track ( + name, album_id, media_type_id, genre_id, composer, milliseconds, bytes + ) + VALUES ( + p_name, p_album_id, p_media_type_id, p_genre_id, p_composer, p_milliseconds, p_bytes + ) + RETURNING track_id INTO new_track_id; + + INSERT INTO price ( + track_id, value, date + ) + VALUES ( + new_track_id, p_price, NOW() + ); +END; +$$; + +GRANT EXECUTE ON PROCEDURE insert_track_with_price( + VARCHAR, INT, INT, INT, VARCHAR, INT, INT, NUMERIC +) TO PUBLIC; Index: SQL/transactions/add_tracks_to_playlist.sql =================================================================== --- SQL/transactions/add_tracks_to_playlist.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) +++ SQL/transactions/add_tracks_to_playlist.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) @@ -0,0 +1,21 @@ +CREATE OR REPLACE FUNCTION add_tracks_to_playlist( + _playlist_id INTEGER, + _playlist_tracks JSON +) RETURNS VOID AS $$ +DECLARE + track JSON; + _track_id INTEGER; +BEGIN + BEGIN + FOR track IN SELECT * FROM json_array_elements(_playlist_tracks) + LOOP + _track_id := (track->>'track_id')::INTEGER; + + INSERT INTO playlist_track (playlist_id, track_id) + VALUES (_playlist_id, _track_id); + END LOOP; + EXCEPTION WHEN OTHERS THEN + RAISE EXCEPTION 'Error adding tracks to playlist: %', SQLERRM; + END; +END; +$$ LANGUAGE plpgsql; Index: SQL/transactions/batch_update_reports_to.sql =================================================================== --- SQL/transactions/batch_update_reports_to.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) +++ SQL/transactions/batch_update_reports_to.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) @@ -0,0 +1,27 @@ +CREATE OR REPLACE FUNCTION batch_update_reports_to( + _updates JSON +) RETURNS VOID AS $$ +DECLARE + item JSON; + _employee_id INTEGER; + _reports_to_id INTEGER; +BEGIN + BEGIN + FOR item IN SELECT * FROM json_array_elements(_updates) + LOOP + _employee_id := (item->>'employee_id')::INTEGER; + _reports_to_id := (item->>'reports_to_id')::INTEGER; + + UPDATE employee + SET reports_to = _reports_to_id + WHERE employee_id = _employee_id; + + IF NOT FOUND THEN + RAISE EXCEPTION 'Employee with id % does not exist', _employee_id; + END IF; + END LOOP; + EXCEPTION WHEN OTHERS THEN + RAISE EXCEPTION 'Error updating reports_to: %', SQLERRM; + END; +END; +$$ LANGUAGE plpgsql; Index: SQL/transactions/create_invoice_with_lines.sql =================================================================== --- SQL/transactions/create_invoice_with_lines.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) +++ SQL/transactions/create_invoice_with_lines.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) @@ -0,0 +1,23 @@ +CREATE OR REPLACE FUNCTION add_invoice_lines_to_existing_invoice( + _invoice_id INTEGER, + _invoice_lines JSON +) RETURNS VOID AS $$ +DECLARE + line JSON; + _track_id INTEGER; + _quantity INTEGER; +BEGIN + BEGIN + FOR line IN SELECT * FROM json_array_elements(_invoice_lines) + LOOP + _track_id := (line->>'track_id')::INTEGER; + _quantity := (line->>'quantity')::INTEGER; + + INSERT INTO invoice_line (invoice_id, track_id, quantity) + VALUES (_invoice_id, _track_id, _quantity); + END LOOP; + EXCEPTION WHEN OTHERS THEN + RAISE EXCEPTION 'Error adding invoice lines: %', SQLERRM; + END; +END; +$$ LANGUAGE plpgsql; Index: SQL/triggers/customer_deletion.sql =================================================================== --- SQL/triggers/customer_deletion.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) +++ SQL/triggers/customer_deletion.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) @@ -0,0 +1,31 @@ +DROP TRIGGER IF EXISTS trg_customer_deletion ON customer; +DROP FUNCTION IF EXISTS customer_deletion(); + +CREATE OR REPLACE FUNCTION customer_deletion() +RETURNS TRIGGER AS $$ +DECLARE + total_spent NUMERIC(10, 2); + invoice_count INTEGER; +BEGIN + SELECT COALESCE(SUM(total), 0), COUNT(*) + INTO total_spent, invoice_count + FROM invoice + WHERE customer_id = OLD.customer_id; + + INSERT INTO deleted_customer_log ( + first_name, last_name, deleted_at, total_spent, invoice_count + ) + VALUES ( + OLD.first_name, OLD.last_name, NOW(), total_spent, invoice_count + ); + + RETURN OLD; +END; +$$ LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS trg_customer_deletion ON customer; + +CREATE TRIGGER trg_customer_deletion +BEFORE DELETE ON customer +FOR EACH ROW +EXECUTE FUNCTION customer_deletion(); Index: SQL/triggers/media_type_deletion.sql =================================================================== --- SQL/triggers/media_type_deletion.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) +++ SQL/triggers/media_type_deletion.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) @@ -0,0 +1,14 @@ +CREATE OR REPLACE FUNCTION prevent_deletion_of_default_media_type() +RETURNS TRIGGER AS $$ +BEGIN + IF OLD.media_type_id = 1 THEN + RAISE EXCEPTION 'Cannot delete default media type (id = 1)'; + END IF; + RETURN OLD; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER trg_prevent_deletion_of_default_media_type +BEFORE DELETE ON media_type +FOR EACH ROW +EXECUTE FUNCTION prevent_deletion_of_default_media_type(); Index: SQL/triggers/normalize_and_validate_customer_email.sql =================================================================== --- SQL/triggers/normalize_and_validate_customer_email.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) +++ SQL/triggers/normalize_and_validate_customer_email.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) @@ -0,0 +1,17 @@ +CREATE OR REPLACE FUNCTION normalize_and_validate_customer_email() +RETURNS TRIGGER AS $$ +BEGIN + NEW.email := LOWER(NEW.email); + + IF NEW.email !~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN + RAISE EXCEPTION 'Invalid email format: %', NEW.email; + END IF; + + RETURN NEW; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER trg_normalize_validate_email +BEFORE INSERT ON customer +FOR EACH ROW +EXECUTE FUNCTION normalize_and_validate_customer_email(); Index: SQL/triggers/prevent_artist_deletion.sql =================================================================== --- SQL/triggers/prevent_artist_deletion.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) +++ SQL/triggers/prevent_artist_deletion.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) @@ -0,0 +1,17 @@ +CREATE OR REPLACE FUNCTION prevent_artist_deletion() +RETURNS TRIGGER AS $$ +BEGIN + IF EXISTS ( + SELECT 1 FROM album WHERE artist_id = OLD.artist_id + ) THEN + RAISE EXCEPTION 'Cannot delete artist with albums.'; + END IF; + + RETURN OLD; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER trg_prevent_artist_deletion +BEFORE DELETE ON artist +FOR EACH ROW +EXECUTE FUNCTION prevent_artist_deletion(); Index: SQL/triggers/prevent_genre_deletion.sql =================================================================== --- SQL/triggers/prevent_genre_deletion.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) +++ SQL/triggers/prevent_genre_deletion.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) @@ -0,0 +1,17 @@ +CREATE OR REPLACE FUNCTION prevent_genre_deletion() +RETURNS TRIGGER AS $$ +BEGIN + IF EXISTS ( + SELECT 1 FROM track WHERE genre_id = OLD.genre_id + ) THEN + RAISE EXCEPTION 'Cannot delete genre with a track of it.'; + END IF; + + RETURN OLD; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER trg_prevent_genre_deletion +BEFORE DELETE ON genre +FOR EACH ROW +EXECUTE FUNCTION prevent_genre_deletion(); Index: SQL/triggers/prevent_price_deletion.sql =================================================================== --- SQL/triggers/prevent_price_deletion.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) +++ SQL/triggers/prevent_price_deletion.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) @@ -0,0 +1,11 @@ +CREATE OR REPLACE FUNCTION prevent_price_deletion() +RETURNS trigger AS $$ +BEGIN + RAISE EXCEPTION 'Deletion from price table is not allowed.'; + RETURN OLD; +END; +$$ LANGUAGE plpgsql; +CREATE TRIGGER trg_no_price_deletes +BEFORE DELETE ON price +FOR EACH ROW +EXECUTE FUNCTION prevent_price_deletion(); Index: SQL/triggers/update_invoice_total_after_delete.sql =================================================================== --- SQL/triggers/update_invoice_total_after_delete.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) +++ SQL/triggers/update_invoice_total_after_delete.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) @@ -0,0 +1,20 @@ +CREATE OR REPLACE FUNCTION update_invoice_total_after_delete() +RETURNS TRIGGER AS $$ +BEGIN + UPDATE invoice + SET total = ( + SELECT COALESCE(SUM(il.quantity * lp.value), 0) + FROM invoice_line il + JOIN ( + SELECT DISTINCT ON (track_id) + track_id, value + FROM price + ORDER BY track_id, date DESC + ) lp ON il.track_id = lp.track_id + WHERE il.invoice_id = OLD.invoice_id + ) + WHERE invoice_id = OLD.invoice_id; + + RETURN OLD; +END; +$$ LANGUAGE plpgsql; Index: SQL/triggers/update_invoice_total_after_insert.sql =================================================================== --- SQL/triggers/update_invoice_total_after_insert.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) +++ SQL/triggers/update_invoice_total_after_insert.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) @@ -0,0 +1,20 @@ +CREATE OR REPLACE FUNCTION update_invoice_total_after_insert() +RETURNS TRIGGER AS $$ +BEGIN + UPDATE invoice + SET total = ( + SELECT COALESCE(SUM(il.quantity * lp.value), 0) + FROM invoice_line il + JOIN ( + SELECT DISTINCT ON (track_id) + track_id, value + FROM price + ORDER BY track_id, date DESC + ) lp ON il.track_id = lp.track_id + WHERE il.invoice_id = NEW.invoice_id + ) + WHERE invoice_id = NEW.invoice_id; + + RETURN NEW; +END; +$$ LANGUAGE plpgsql; Index: SQL/views/avg_price_per_artist.sql =================================================================== --- SQL/views/avg_price_per_artist.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) +++ SQL/views/avg_price_per_artist.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) @@ -0,0 +1,17 @@ +CREATE VIEW avg_price_per_artist AS +WITH latest_price AS ( + SELECT DISTINCT ON (track_id) + track_id, + value + FROM price + ORDER BY track_id, date DESC +) +SELECT + ar.name, + COALESCE(ROUND(AVG(lp.value), 2)::text, 'not enough data') AS avg_price_per_track +FROM artist ar +LEFT JOIN album a ON ar.artist_id = a.artist_id +LEFT JOIN track t ON a.album_id = t.album_id +LEFT JOIN latest_price lp ON t.track_id = lp.track_id +GROUP BY ar.name +ORDER BY ar.name; Index: SQL/views/avg_song_duration_per_artist.sql =================================================================== --- SQL/views/avg_song_duration_per_artist.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) +++ SQL/views/avg_song_duration_per_artist.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) @@ -0,0 +1,7 @@ +CREATE VIEW avg_track_duration_per_artist AS +SELECT ar.name as artist_name, concat(ceil(avg(milliseconds) / 1000), 's') as avg_track_duration_in_seconds +FROM artist ar +left join album al on ar.artist_id = al.album_id +left join track tr on al.album_id = tr.album_id +group by ar.name +order by ar.name Index: SQL/views/media_type_percentage.sql =================================================================== --- SQL/views/media_type_percentage.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) +++ SQL/views/media_type_percentage.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) @@ -0,0 +1,12 @@ +CREATE VIEW media_type_percentage AS +with cte as (SELECT count(track_id) as total + FROM media_type mt + left join track tr on mt.media_type_id = tr.media_type_id) +SELECT mt.name as media_type, + count(tr.track_id) as num_of_tracks, + round(count(tr.track_id) * 100.0 / cte.total, 2) as percentage +FROM media_type mt + left join track tr on mt.media_type_id = tr.media_type_id + cross join cte +group by mt.media_type_id, mt.name, cte.total +order by percentage desc Index: SQL/views/most_listened_genre_per_customer.sql =================================================================== --- SQL/views/most_listened_genre_per_customer.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) +++ SQL/views/most_listened_genre_per_customer.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) @@ -0,0 +1,17 @@ +CREATE VIEW most_listened_genre_per_customer AS +WITH genre_count AS + (SELECT c.customer_id, c.first_name, c.last_name, g.name as genre, count(tr.track_id) as num_songs_per_genre + FROM customer c + left join invoice i on c.customer_id = i.customer_id + left join invoice_line il on i.invoice_id = il.invoice_id + left join track tr on il.track_id = tr.track_id + left join genre g on tr.genre_id = g.genre_id + group by c.customer_id, c.first_name, c.last_name, g.genre_id, g.name + order by c.first_name, num_songs_per_genre desc), + ranked_genres AS (SELECT *, + ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY num_songs_per_genre DESC) AS rn + FROM genre_count) +SELECT first_name, last_name, genre as most_listened_genre +FROM ranked_genres +WHERE rn = 1 +ORDER BY first_name, last_name; Index: SQL/views/rang_list_most_active_customers_view.sql =================================================================== --- SQL/views/rang_list_most_active_customers_view.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) +++ SQL/views/rang_list_most_active_customers_view.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) @@ -0,0 +1,9 @@ +CREATE VIEW rank_list_most_active_customers_view AS +SELECT + c.first_name || ' ' || c.last_name as name, + COUNT(i.invoice_id) as total_orders, + SUM(i.total) as total_money_spent +from customer c +LEFT JOIN invoice i on c.customer_id = i.customer_id +GROUP BY c.first_name, c.last_name +order by total_money_spent desc Index: SQL/views/rank_list_artists.sql =================================================================== --- SQL/views/rank_list_artists.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) +++ SQL/views/rank_list_artists.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) @@ -0,0 +1,11 @@ +CREATE VIEW rank_list_artists AS +SELECT ar.name, + count(il.invoice_line_id) AS num_invoices, + COALESCE(SUM(i.total), 0) AS money_earned +FROM artist ar + LEFT JOIN album al ON ar.artist_id = al.album_id + LEFT JOIN track tr ON al.album_id = tr.album_id + LEFT JOIN invoice_line il ON tr.track_id = il.track_id + LEFT JOIN invoice i on il.invoice_id = i.invoice_id +GROUP BY ar.name +ORDER BY money_earned DESC; Index: SQL/views/tracks_count_per_genre.sql =================================================================== --- SQL/views/tracks_count_per_genre.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) +++ SQL/views/tracks_count_per_genre.sql (revision da2058e8143fa9f5ea3a13cd627153d96c156af1) @@ -0,0 +1,5 @@ +CREATE VIEW track_count_per_genre AS +SELECT g.name as Genre, count(t.track_id) as num_tracks +FROM genre g +LEFT JOIN track t on g.genre_id = t.genre_id +GROUP BY g.name Index: sic/constrains/general_constrains.sql =================================================================== --- music/constrains/general_constrains.sql (revision 15a2122368efafb214f9150adec31a7112118fea) +++ (revision ) @@ -1,34 +1,0 @@ --- Default Media Type Constrain -ALTER TABLE track -ALTER COLUMN media_type_id SET DEFAULT 1; - -ALTER TABLE track -DROP CONSTRAINT IF EXISTS track_media_type_id_fkey; - -ALTER TABLE track -ADD CONSTRAINT track_media_type_id_fkey -FOREIGN KEY (media_type_id) REFERENCES media_type(media_type_id) -ON DELETE SET DEFAULT; - - -SELECT setval(pg_get_serial_sequence('invoice_line', 'invoice_line_id'), (SELECT MAX(invoice_line_id) FROM invoice_line)); - -SELECT setval(pg_get_serial_sequence('playlist_track', 'id'), (SELECT MAX(id) FROM playlist_track)); - -SELECT setval(pg_get_serial_sequence('playlist', 'playlist_id'), (SELECT MAX(playlist_id) FROM playlist)); - -SELECT setval(pg_get_serial_sequence('artist', 'artist_id'), (SELECT MAX(artist_id) FROM artist)); - -SELECT setval(pg_get_serial_sequence('album', 'album_id'), (SELECT MAX(album_id) FROM album)); - -SELECT setval(pg_get_serial_sequence('track', 'track_id'), (SELECT MAX(track_id) FROM track)); - -SELECT setval(pg_get_serial_sequence('media_type', 'media_type_id'), (SELECT MAX(media_type_id) FROM media_type)); - -SELECT setval(pg_get_serial_sequence('genre', 'genre_id'), (SELECT MAX(genre_id) FROM genre)); - -SELECT setval(pg_get_serial_sequence('customer', 'customer_id'), (SELECT MAX(customer_id) FROM customer)); - -SELECT setval(pg_get_serial_sequence('employee', 'employee_id'), (SELECT MAX(employee_id) FROM employee)); - - Index: sic/constrains/soft_delete.sql =================================================================== --- music/constrains/soft_delete.sql (revision 15a2122368efafb214f9150adec31a7112118fea) +++ (revision ) @@ -1,66 +1,0 @@ ---ADDING DELETED_AT COLUMN -DO $$ -DECLARE - r RECORD; -BEGIN - FOR r IN - SELECT table_name - FROM information_schema.tables - WHERE table_schema = 'public' - AND table_type = 'BASE TABLE' - LOOP - EXECUTE format('ALTER TABLE public.%I ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP;', r.table_name); - END LOOP; -END $$; - ---SOFT DELETE TRIGGER -DO $$ -DECLARE - r RECORD; - pk_col TEXT; -BEGIN - FOR r IN ( - SELECT table_name - FROM information_schema.tables - WHERE table_schema = 'public' - AND table_type = 'BASE TABLE' - ) - LOOP - SELECT kcu.column_name INTO pk_col - FROM information_schema.table_constraints tc - JOIN information_schema.key_column_usage kcu - ON tc.constraint_name = kcu.constraint_name - AND tc.table_name = kcu.table_name - WHERE tc.constraint_type = 'PRIMARY KEY' - AND tc.table_name = r.table_name - LIMIT 1; - - IF pk_col IS NULL THEN - RAISE NOTICE 'Table % has no primary key, skipping soft delete trigger', r.table_name; - CONTINUE; - END IF; - - EXECUTE format('ALTER TABLE public.%I ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP;', r.table_name); - - EXECUTE format( - 'CREATE OR REPLACE FUNCTION soft_delete_%I() - RETURNS TRIGGER AS $func$ - BEGIN - UPDATE %I SET deleted_at = NOW() WHERE %I = OLD.%I; - RETURN NULL; - END; - $func$ LANGUAGE plpgsql;', - r.table_name, r.table_name, r.table_name, pk_col - ); - - EXECUTE format( - 'DROP TRIGGER IF EXISTS trg_soft_delete_%I ON %I; - CREATE TRIGGER trg_soft_delete_%I - BEFORE DELETE ON %I - FOR EACH ROW - EXECUTE FUNCTION soft_delete_%I();', - r.table_name, r.table_name, r.table_name, r.table_name, r.table_name - ); - END LOOP; -END $$; - Index: sic/normalization/personal_info.sql =================================================================== --- music/normalization/personal_info.sql (revision 15a2122368efafb214f9150adec31a7112118fea) +++ (revision ) @@ -1,117 +1,0 @@ -CREATE TABLE PersonalInfo ( - personal_info_id SERIAL PRIMARY KEY, - address TEXT, - city TEXT, - state TEXT, - country TEXT, - postalcode TEXT, - phone TEXT, - fax TEXT, - email TEXT -); - -ALTER TABLE employee -ADD COLUMN personal_info_id INTEGER REFERENCES PersonalInfo(personal_info_id); - -ALTER TABLE customer -ADD COLUMN personal_info_id INTEGER REFERENCES PersonalInfo(personal_info_id); - ---PERSONAL INFO DATA MIGRATION -INSERT INTO PersonalInfo (address, city, state, country, postalcode, phone, fax, email) -SELECT DISTINCT address, city, state, country, postal_code, phone, fax, email -FROM employee; - -UPDATE employee e -SET personal_info_id = p.personal_info_id -FROM PersonalInfo p -WHERE e.address = p.address - AND e.city = p.city - AND e.state = p.state - AND e.country = p.country - AND e.postal_code = p.postalcode - AND e.phone = p.phone - AND e.fax = p.fax - AND e.email = p.email; - -INSERT INTO PersonalInfo (address, city, state, country, postalcode, phone, fax, email) -SELECT DISTINCT address, city, state, country, postal_code, phone, fax, email -FROM customer; - -UPDATE customer c -SET personal_info_id = p.personal_info_id -FROM PersonalInfo p -WHERE c.address = p.address - AND c.city = p.city - AND c.country = p.country - AND c.email = p.email; - - -ALTER TABLE employee -DROP COLUMN address, -DROP COLUMN city, -DROP COLUMN state, -DROP COLUMN country, -DROP COLUMN postal_code, -DROP COLUMN phone, -DROP COLUMN fax, -DROP COLUMN email; - -ALTER TABLE customer -DROP COLUMN address, -DROP COLUMN city, -DROP COLUMN state, -DROP COLUMN country, -DROP COLUMN postal_code, -DROP COLUMN phone, -DROP COLUMN fax, -DROP COLUMN email; - -ALTER TABLE personalinfo -RENAME COLUMN postalcode TO postal_code; - -SELECT * -FROM employee - -CREATE TABLE Contact ( - contact_id INT PRIMARY KEY, - phone VARCHAR(50), - fax VARCHAR(50), - email VARCHAR(100) -); - -INSERT INTO Contact (contact_id, phone, fax, email) -SELECT - personal_info_id, - Phone, - Fax, - Email -FROM personalinfo; - -ALTER TABLE Employee -ADD contact_id INT; - -UPDATE Employee -SET contact_id = employee.personal_info_id -where employee.personal_info_id IS NOT NULL; - -ALTER TABLE Employee -ADD CONSTRAINT FK_Employee_Contact FOREIGN KEY (contact_id) -REFERENCES Contact(contact_id); - -ALTER TABLE Customer -ADD contact_id INT; - -UPDATE Customer -SET contact_id = customer.personal_info_id -where customer.personal_info_id IS NOT NULL; - -ALTER TABLE Customer -ADD CONSTRAINT FK_Customer_Contact FOREIGN KEY (contact_id) -REFERENCES Contact(contact_id); - -ALTER TABLE personalinfo -DROP COLUMN fax, -DROP COLUMN phone, -DROP COLUMN email; - -ALTER TABLE personalinfo RENAME TO address_info; Index: sic/procedures/insert_track_with_price.sql =================================================================== --- music/procedures/insert_track_with_price.sql (revision 15a2122368efafb214f9150adec31a7112118fea) +++ (revision ) @@ -1,35 +1,0 @@ -CREATE OR REPLACE PROCEDURE insert_track_with_price( - p_name VARCHAR, - p_album_id INT, - p_media_type_id INT, - p_genre_id INT, - p_composer VARCHAR, - p_milliseconds INT, - p_bytes INT, - p_price NUMERIC(10,2) -) -LANGUAGE plpgsql -AS $$ -DECLARE - new_track_id INT; -BEGIN - INSERT INTO track ( - name, album_id, media_type_id, genre_id, composer, milliseconds, bytes - ) - VALUES ( - p_name, p_album_id, p_media_type_id, p_genre_id, p_composer, p_milliseconds, p_bytes - ) - RETURNING track_id INTO new_track_id; - - INSERT INTO price ( - track_id, value, date - ) - VALUES ( - new_track_id, p_price, NOW() - ); -END; -$$; - -GRANT EXECUTE ON PROCEDURE insert_track_with_price( - VARCHAR, INT, INT, INT, VARCHAR, INT, INT, NUMERIC -) TO PUBLIC; Index: sic/transactions/add_tracks_to_playlist.sql =================================================================== --- music/transactions/add_tracks_to_playlist.sql (revision 15a2122368efafb214f9150adec31a7112118fea) +++ (revision ) @@ -1,21 +1,0 @@ -CREATE OR REPLACE FUNCTION add_tracks_to_playlist( - _playlist_id INTEGER, - _playlist_tracks JSON -) RETURNS VOID AS $$ -DECLARE - track JSON; - _track_id INTEGER; -BEGIN - BEGIN - FOR track IN SELECT * FROM json_array_elements(_playlist_tracks) - LOOP - _track_id := (track->>'track_id')::INTEGER; - - INSERT INTO playlist_track (playlist_id, track_id) - VALUES (_playlist_id, _track_id); - END LOOP; - EXCEPTION WHEN OTHERS THEN - RAISE EXCEPTION 'Error adding tracks to playlist: %', SQLERRM; - END; -END; -$$ LANGUAGE plpgsql; Index: sic/transactions/batch_update_reports_to.sql =================================================================== --- music/transactions/batch_update_reports_to.sql (revision 15a2122368efafb214f9150adec31a7112118fea) +++ (revision ) @@ -1,27 +1,0 @@ -CREATE OR REPLACE FUNCTION batch_update_reports_to( - _updates JSON -) RETURNS VOID AS $$ -DECLARE - item JSON; - _employee_id INTEGER; - _reports_to_id INTEGER; -BEGIN - BEGIN - FOR item IN SELECT * FROM json_array_elements(_updates) - LOOP - _employee_id := (item->>'employee_id')::INTEGER; - _reports_to_id := (item->>'reports_to_id')::INTEGER; - - UPDATE employee - SET reports_to = _reports_to_id - WHERE employee_id = _employee_id; - - IF NOT FOUND THEN - RAISE EXCEPTION 'Employee with id % does not exist', _employee_id; - END IF; - END LOOP; - EXCEPTION WHEN OTHERS THEN - RAISE EXCEPTION 'Error updating reports_to: %', SQLERRM; - END; -END; -$$ LANGUAGE plpgsql; Index: sic/transactions/create_invoice_with_lines.sql =================================================================== --- music/transactions/create_invoice_with_lines.sql (revision 15a2122368efafb214f9150adec31a7112118fea) +++ (revision ) @@ -1,23 +1,0 @@ -CREATE OR REPLACE FUNCTION add_invoice_lines_to_existing_invoice( - _invoice_id INTEGER, - _invoice_lines JSON -) RETURNS VOID AS $$ -DECLARE - line JSON; - _track_id INTEGER; - _quantity INTEGER; -BEGIN - BEGIN - FOR line IN SELECT * FROM json_array_elements(_invoice_lines) - LOOP - _track_id := (line->>'track_id')::INTEGER; - _quantity := (line->>'quantity')::INTEGER; - - INSERT INTO invoice_line (invoice_id, track_id, quantity) - VALUES (_invoice_id, _track_id, _quantity); - END LOOP; - EXCEPTION WHEN OTHERS THEN - RAISE EXCEPTION 'Error adding invoice lines: %', SQLERRM; - END; -END; -$$ LANGUAGE plpgsql; Index: sic/triggers/customer_deletion.sql =================================================================== --- music/triggers/customer_deletion.sql (revision 15a2122368efafb214f9150adec31a7112118fea) +++ (revision ) @@ -1,31 +1,0 @@ -DROP TRIGGER IF EXISTS trg_customer_deletion ON customer; -DROP FUNCTION IF EXISTS customer_deletion(); - -CREATE OR REPLACE FUNCTION customer_deletion() -RETURNS TRIGGER AS $$ -DECLARE - total_spent NUMERIC(10, 2); - invoice_count INTEGER; -BEGIN - SELECT COALESCE(SUM(total), 0), COUNT(*) - INTO total_spent, invoice_count - FROM invoice - WHERE customer_id = OLD.customer_id; - - INSERT INTO deleted_customer_log ( - first_name, last_name, deleted_at, total_spent, invoice_count - ) - VALUES ( - OLD.first_name, OLD.last_name, NOW(), total_spent, invoice_count - ); - - RETURN OLD; -END; -$$ LANGUAGE plpgsql; - -DROP TRIGGER IF EXISTS trg_customer_deletion ON customer; - -CREATE TRIGGER trg_customer_deletion -BEFORE DELETE ON customer -FOR EACH ROW -EXECUTE FUNCTION customer_deletion(); Index: sic/triggers/media_type_deletion.sql =================================================================== --- music/triggers/media_type_deletion.sql (revision 15a2122368efafb214f9150adec31a7112118fea) +++ (revision ) @@ -1,14 +1,0 @@ -CREATE OR REPLACE FUNCTION prevent_deletion_of_default_media_type() -RETURNS TRIGGER AS $$ -BEGIN - IF OLD.media_type_id = 1 THEN - RAISE EXCEPTION 'Cannot delete default media type (id = 1)'; - END IF; - RETURN OLD; -END; -$$ LANGUAGE plpgsql; - -CREATE TRIGGER trg_prevent_deletion_of_default_media_type -BEFORE DELETE ON media_type -FOR EACH ROW -EXECUTE FUNCTION prevent_deletion_of_default_media_type(); Index: sic/triggers/normalize_and_validate_customer_email.sql =================================================================== --- music/triggers/normalize_and_validate_customer_email.sql (revision 15a2122368efafb214f9150adec31a7112118fea) +++ (revision ) @@ -1,17 +1,0 @@ -CREATE OR REPLACE FUNCTION normalize_and_validate_customer_email() -RETURNS TRIGGER AS $$ -BEGIN - NEW.email := LOWER(NEW.email); - - IF NEW.email !~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN - RAISE EXCEPTION 'Invalid email format: %', NEW.email; - END IF; - - RETURN NEW; -END; -$$ LANGUAGE plpgsql; - -CREATE TRIGGER trg_normalize_validate_email -BEFORE INSERT ON customer -FOR EACH ROW -EXECUTE FUNCTION normalize_and_validate_customer_email(); Index: sic/triggers/prevent_artist_deletion.sql =================================================================== --- music/triggers/prevent_artist_deletion.sql (revision 15a2122368efafb214f9150adec31a7112118fea) +++ (revision ) @@ -1,17 +1,0 @@ -CREATE OR REPLACE FUNCTION prevent_artist_deletion() -RETURNS TRIGGER AS $$ -BEGIN - IF EXISTS ( - SELECT 1 FROM album WHERE artist_id = OLD.artist_id - ) THEN - RAISE EXCEPTION 'Cannot delete artist with albums.'; - END IF; - - RETURN OLD; -END; -$$ LANGUAGE plpgsql; - -CREATE TRIGGER trg_prevent_artist_deletion -BEFORE DELETE ON artist -FOR EACH ROW -EXECUTE FUNCTION prevent_artist_deletion(); Index: sic/triggers/prevent_genre_deletion.sql =================================================================== --- music/triggers/prevent_genre_deletion.sql (revision 15a2122368efafb214f9150adec31a7112118fea) +++ (revision ) @@ -1,17 +1,0 @@ -CREATE OR REPLACE FUNCTION prevent_genre_deletion() -RETURNS TRIGGER AS $$ -BEGIN - IF EXISTS ( - SELECT 1 FROM track WHERE genre_id = OLD.genre_id - ) THEN - RAISE EXCEPTION 'Cannot delete genre with a track of it.'; - END IF; - - RETURN OLD; -END; -$$ LANGUAGE plpgsql; - -CREATE TRIGGER trg_prevent_genre_deletion -BEFORE DELETE ON genre -FOR EACH ROW -EXECUTE FUNCTION prevent_genre_deletion(); Index: sic/triggers/prevent_price_deletion.sql =================================================================== --- music/triggers/prevent_price_deletion.sql (revision 15a2122368efafb214f9150adec31a7112118fea) +++ (revision ) @@ -1,11 +1,0 @@ -CREATE OR REPLACE FUNCTION prevent_price_deletion() -RETURNS trigger AS $$ -BEGIN - RAISE EXCEPTION 'Deletion from price table is not allowed.'; - RETURN OLD; -END; -$$ LANGUAGE plpgsql; -CREATE TRIGGER trg_no_price_deletes -BEFORE DELETE ON price -FOR EACH ROW -EXECUTE FUNCTION prevent_price_deletion(); Index: sic/triggers/update_invoice_total_after_delete.sql =================================================================== --- music/triggers/update_invoice_total_after_delete.sql (revision 15a2122368efafb214f9150adec31a7112118fea) +++ (revision ) @@ -1,20 +1,0 @@ -CREATE OR REPLACE FUNCTION update_invoice_total_after_delete() -RETURNS TRIGGER AS $$ -BEGIN - UPDATE invoice - SET total = ( - SELECT COALESCE(SUM(il.quantity * lp.value), 0) - FROM invoice_line il - JOIN ( - SELECT DISTINCT ON (track_id) - track_id, value - FROM price - ORDER BY track_id, date DESC - ) lp ON il.track_id = lp.track_id - WHERE il.invoice_id = OLD.invoice_id - ) - WHERE invoice_id = OLD.invoice_id; - - RETURN OLD; -END; -$$ LANGUAGE plpgsql; Index: sic/triggers/update_invoice_total_after_insert.sql =================================================================== --- music/triggers/update_invoice_total_after_insert.sql (revision 15a2122368efafb214f9150adec31a7112118fea) +++ (revision ) @@ -1,20 +1,0 @@ -CREATE OR REPLACE FUNCTION update_invoice_total_after_insert() -RETURNS TRIGGER AS $$ -BEGIN - UPDATE invoice - SET total = ( - SELECT COALESCE(SUM(il.quantity * lp.value), 0) - FROM invoice_line il - JOIN ( - SELECT DISTINCT ON (track_id) - track_id, value - FROM price - ORDER BY track_id, date DESC - ) lp ON il.track_id = lp.track_id - WHERE il.invoice_id = NEW.invoice_id - ) - WHERE invoice_id = NEW.invoice_id; - - RETURN NEW; -END; -$$ LANGUAGE plpgsql; Index: sic/views/avg_price_per_artist.sql =================================================================== --- music/views/avg_price_per_artist.sql (revision 15a2122368efafb214f9150adec31a7112118fea) +++ (revision ) @@ -1,17 +1,0 @@ -CREATE VIEW avg_price_per_artist AS -WITH latest_price AS ( - SELECT DISTINCT ON (track_id) - track_id, - value - FROM price - ORDER BY track_id, date DESC -) -SELECT - ar.name, - COALESCE(ROUND(AVG(lp.value), 2)::text, 'not enough data') AS avg_price_per_track -FROM artist ar -LEFT JOIN album a ON ar.artist_id = a.artist_id -LEFT JOIN track t ON a.album_id = t.album_id -LEFT JOIN latest_price lp ON t.track_id = lp.track_id -GROUP BY ar.name -ORDER BY ar.name; Index: sic/views/avg_song_duration_per_artist.sql =================================================================== --- music/views/avg_song_duration_per_artist.sql (revision 15a2122368efafb214f9150adec31a7112118fea) +++ (revision ) @@ -1,7 +1,0 @@ -CREATE VIEW avg_track_duration_per_artist AS -SELECT ar.name as artist_name, concat(ceil(avg(milliseconds) / 1000), 's') as avg_track_duration_in_seconds -FROM artist ar -left join album al on ar.artist_id = al.album_id -left join track tr on al.album_id = tr.album_id -group by ar.name -order by ar.name Index: sic/views/media_type_percentage.sql =================================================================== --- music/views/media_type_percentage.sql (revision 15a2122368efafb214f9150adec31a7112118fea) +++ (revision ) @@ -1,12 +1,0 @@ -CREATE VIEW media_type_percentage AS -with cte as (SELECT count(track_id) as total - FROM media_type mt - left join track tr on mt.media_type_id = tr.media_type_id) -SELECT mt.name as media_type, - count(tr.track_id) as num_of_tracks, - round(count(tr.track_id) * 100.0 / cte.total, 2) as percentage -FROM media_type mt - left join track tr on mt.media_type_id = tr.media_type_id - cross join cte -group by mt.media_type_id, mt.name, cte.total -order by percentage desc Index: sic/views/most_listened_genre_per_customer.sql =================================================================== --- music/views/most_listened_genre_per_customer.sql (revision 15a2122368efafb214f9150adec31a7112118fea) +++ (revision ) @@ -1,17 +1,0 @@ -CREATE VIEW most_listened_genre_per_customer AS -WITH genre_count AS - (SELECT c.customer_id, c.first_name, c.last_name, g.name as genre, count(tr.track_id) as num_songs_per_genre - FROM customer c - left join invoice i on c.customer_id = i.customer_id - left join invoice_line il on i.invoice_id = il.invoice_id - left join track tr on il.track_id = tr.track_id - left join genre g on tr.genre_id = g.genre_id - group by c.customer_id, c.first_name, c.last_name, g.genre_id, g.name - order by c.first_name, num_songs_per_genre desc), - ranked_genres AS (SELECT *, - ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY num_songs_per_genre DESC) AS rn - FROM genre_count) -SELECT first_name, last_name, genre as most_listened_genre -FROM ranked_genres -WHERE rn = 1 -ORDER BY first_name, last_name; Index: sic/views/rang_list_most_active_customers_view.sql =================================================================== --- music/views/rang_list_most_active_customers_view.sql (revision 15a2122368efafb214f9150adec31a7112118fea) +++ (revision ) @@ -1,9 +1,0 @@ -CREATE VIEW rank_list_most_active_customers_view AS -SELECT - c.first_name || ' ' || c.last_name as name, - COUNT(i.invoice_id) as total_orders, - SUM(i.total) as total_money_spent -from customer c -LEFT JOIN invoice i on c.customer_id = i.customer_id -GROUP BY c.first_name, c.last_name -order by total_money_spent desc Index: sic/views/rank_list_artists.sql =================================================================== --- music/views/rank_list_artists.sql (revision 15a2122368efafb214f9150adec31a7112118fea) +++ (revision ) @@ -1,11 +1,0 @@ -CREATE VIEW rank_list_artists AS -SELECT ar.name, - count(il.invoice_line_id) AS num_invoices, - COALESCE(SUM(i.total), 0) AS money_earned -FROM artist ar - LEFT JOIN album al ON ar.artist_id = al.album_id - LEFT JOIN track tr ON al.album_id = tr.album_id - LEFT JOIN invoice_line il ON tr.track_id = il.track_id - LEFT JOIN invoice i on il.invoice_id = i.invoice_id -GROUP BY ar.name -ORDER BY money_earned DESC; Index: sic/views/tracks_count_per_genre.sql =================================================================== --- music/views/tracks_count_per_genre.sql (revision 15a2122368efafb214f9150adec31a7112118fea) +++ (revision ) @@ -1,5 +1,0 @@ -CREATE VIEW track_count_per_genre AS -SELECT g.name as Genre, count(t.track_id) as num_tracks -FROM genre g -LEFT JOIN track t on g.genre_id = t.genre_id -GROUP BY g.name