Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- create OMDB --
- -- drop tables --
- DROP VIEW all_albums
- /
- DROP TABLE albums
- /
- DROP TYPE disk_type
- /
- DROP TYPE mp3_type
- /
- DROP TYPE album_type
- /
- DROP TYPE artist_array_type
- /
- DROP TYPE artist_type
- /
- DROP TYPE review_table_type
- /
- DROP TYPE review_type
- /
- -- create types --
- CREATE OR REPLACE TYPE artist_type AS object
- (artistName VARCHAR(50),
- artistRole VARCHAR(25))
- /
- CREATE TYPE artist_array_type
- AS varray(5) OF artist_type
- /
- CREATE OR REPLACE TYPE review_type AS object
- (reviewerName VARCHAR(25),
- reviewDate DATE,
- reviewText VARCHAR(250),
- reviewScore NUMBER)
- /
- CREATE OR REPLACE TYPE review_table_type AS TABLE OF review_type
- /
- CREATE OR REPLACE TYPE album_type AS object
- (albumTitle VARCHAR(50),
- albumPlaytime NUMBER(3), -- minutes
- albumReleaseDate DATE,
- albumGenre VARCHAR(15),
- albumPrice NUMBER(9,2),
- albumTracks NUMBER(2),
- albumArtists artist_array_type,
- albumReviews review_table_type,
- member FUNCTION discountPrice RETURN NUMBER,
- member FUNCTION containsText (pString1 VARCHAR2, pString2 VARCHAR2) RETURN INTEGER)
- NOT instantiable NOT final
- /
- CREATE OR REPLACE TYPE disk_type under album_type
- ( mediaType VARCHAR(10),
- diskNum NUMBER(2), -- number of disks
- diskUsedPrice NUMBER(9,2),
- diskDeliveryCost NUMBER(9,2),
- overriding member FUNCTION discountPrice RETURN NUMBER)
- /
- CREATE OR REPLACE TYPE mp3_type under album_type
- (downloadSize NUMBER, -- size in MB
- overriding member FUNCTION discountPrice RETURN NUMBER)
- /
- /
- -- Implement function
- CREATE OR REPLACE TYPE BODY album_type
- AS
- member FUNCTION discountPrice RETURN NUMBER IS
- v_discount NUMBER(9,2);
- BEGIN
- NULL;
- END;
- member FUNCTION containsText (pString1 VARCHAR2, pString2 VARCHAR2) RETURN INTEGER IS
- BEGIN
- IF INSTR(LOWER(pString1),LOWER(pString2)) > 0 THEN RETURN 1;
- ELSE RETURN 0;
- END IF;
- END;
- END;
- /
- -- Overriding Function
- CREATE OR REPLACE TYPE BODY disk_type AS
- overriding member FUNCTION discountPrice
- RETURN NUMBER IS v_discount NUMBER(9,2);
- BEGIN
- IF mediaType = 'Vinyl' AND (albumReleaseDate < (SYSDATE - INTERVAL '1' YEAR)) THEN RETURN (0.8*albumPrice);
- ELSE IF mediaType = 'Audio CD' AND (albumReleaseDate < (SYSDATE - INTERVAL '1' YEAR)) THEN RETURN (0.85*albumPrice);
- ELSE RETURN albumPrice;
- END IF;
- END IF;
- END;
- END;
- /
- CREATE OR REPLACE TYPE BODY mp3_type AS
- overriding member FUNCTION discountPrice
- RETURN NUMBER IS v_discount NUMBER(9,2);
- BEGIN
- IF albumReleaseDate < (SYSDATE - INTERVAL '2' YEAR) THEN RETURN (0.9*albumPrice);
- ELSE RETURN albumPrice;
- END IF;
- END;
- END;
- /
- --- MP3 TYPE --
- CREATE OR REPLACE TYPE BODY mp3_type AS
- overriding member FUNCTION discountPrice
- RETURN NUMBER IS v_discount NUMBER(9,2);
- BEGIN
- IF albumReleaseDate < (SYSDATE - INTERVAL '2' YEAR) THEN RETURN (0.9*albumPrice);
- ELSE RETURN albumPrice;
- END IF;
- END;
- END;
- /
- -- create tables --
- CREATE TABLE albums OF album_type
- object id system generated
- nested TABLE albumReviews store AS store_reviews
- /
- -----------
- -- Vinyl --
- -----------
- INSERT INTO albums
- VALUES(
- disk_type('The Essential Bob Dylan', 99, '8-Jul-2016', 'Pop', 37.00, 32,
- artist_array_type( artist_type('Bob Dylan', 'Composer'),
- artist_type('Bob Dylan', 'Vocals')
- ),
- review_table_type( review_type('Shawn', '2-Aug-2019', 'Great compilation of some of his most known songs!', 5),
- review_type('Reuben', '18-Feb-2013', 'Captures Bob Dylan transformation from a folk song composer', 5)
- ),
- 'Vinyl', 2, NULL, 11.0
- )
- )
- /
- INSERT INTO albums
- VALUES(
- disk_type('Sketches of Spain', 45, '8-Mar-2011', 'Jazz', 14.99, 6,
- artist_array_type( artist_type('Miles Davis', 'Composer'),
- artist_type('Miles Davis', 'Musician')
- ),
- review_table_type( review_type('Frederick', '16-Sep-2016', 'Recommend listening while viewing sunset', 5),
- review_type('Juliet', '12-Mar-2018', 'Early days of the Great Miles--no lover of jazz should be without this album.', 5)
- ),
- 'Vinyl', 1, 16.29, 7.00
- )
- )
- /
- INSERT INTO albums
- VALUES(
- disk_type('Bob Dylan''s Greatest Hits',45,'31-Jan-2017','Pop Rock',29.87,10,
- artist_array_type(artist_type('Bob Dylan','Composer'),
- artist_type('Bob Dylan','Vocals')
- ),
- review_table_type(
- review_type('Kandy','16-Mar-2015','Early Dylan in all his glory.',5),
- review_type('Steward','18-Feb-2013','Captures Bob Dylan transformation from a folk song Composer to a rock legend',4)
- ), 'Vinyl',1,NULL,11.00
- )
- )
- /
- INSERT INTO albums
- VALUES(
- disk_type('Harvest (2009 Remaster)', 44, '21-Jun-2009', 'Rock Country', 28.50, 10,
- artist_array_type( artist_type('Neil Young', 'Composer'),
- artist_type('Neil Young', 'Vocals')
- ),
- review_table_type( review_type('John', '18-Feb-2019', 'I absolutely LOVE this CD!', 5),
- review_type('Stewart', '18-Feb-2013', 'Sound good in vinyl!', 5)
- ),
- 'Vinyl', 1, 14.99, 11.00
- )
- )
- /
- INSERT INTO albums
- VALUES(
- disk_type('Kind of Blue (Legacy Edition)', 155, '20-Jan-2009', 'Jazz', 19.99, 21,
- artist_array_type( artist_type('Miles Davis', 'Composer'),
- artist_type('Miles Davis', 'Musician')
- ),
- review_table_type( review_type('Laurence', '10-Sep-2014', 'Very very special recording.', 5)
- ),
- 'Vinyl', 3, 16.99, 10.00
- )
- )
- /
- --Audio CDs--
- INSERT INTO albums
- VALUES(
- disk_type('Harvest (2009 Remaster)', 44, '21-Jun-2009', 'Rock Country', 10.50, 10,
- artist_array_type( artist_type('Neil Young', 'Composer'),
- artist_type('Neil Young', 'Vocals')
- ),
- review_table_type( review_type('John', '18-Feb-2019', 'I absolutely LOVE this CD!', 5),
- review_type('Anthony', '16-Aug-2019', 'Neil Young''s signature album.', 4)
- ),
- 'Audio CD', 1, 4.99, 11.00
- )
- )
- /
- INSERT INTO albums
- VALUES(
- disk_type('The Essential Bob Dylan', 99, '8-Jul-2016', 'Pop', 26.17, 32,
- artist_array_type( artist_type('Bob Dylan', 'Composer'),
- artist_type('Bob Dylan', 'Vocals')
- ),
- review_table_type( review_type('Christopher', '24-Jun-2016', 'This is a terrific album.', 5),
- review_type('Cauley', '2-Aug-2015', 'There can only be one Bob Dylan. God blessed him with the gift of verse.', 5)
- ),
- 'Audio CD', 2, NULL, 7.00
- )
- )
- /
- INSERT INTO albums
- VALUES(
- disk_type('Bob Dylan''s Greatest Hits', 50, '1-Jun-1999', 'Pop Rock', 20.81, 10,
- artist_array_type( artist_type('Bob Dylan', 'Composer'),
- artist_type('Bob Dylan', 'Vocals')
- ),
- review_table_type( review_type('Kandy', '16-Mar-2015', 'Early Dylan in all his glory.', 5),
- review_type('Stewart', '18-Feb-2013', 'Captures Bob Dylan transformation from a folk song composer to a rock legend.', 4)
- ),
- 'Audio CD', 1, NULL, 7.00
- )
- )
- /
- INSERT INTO albums
- VALUES(
- disk_type('Kind Of Blue (Legacy Edition)', 155, '20-Jan-2009', 'Jazz',19.99, 21,
- artist_array_type( artist_type('Miles Davis', 'Composer'),
- artist_type('Miles Davis', 'Musician')
- ),
- review_table_type( review_type('Amy', '17-Apr-2018', 'Poor quality sound compared to the vinyl record.', 2)
- ),
- 'Audio CD', 3, 16.99, 10.00
- )
- )
- /
- INSERT INTO albums
- VALUES(
- disk_type('Sketches of Spain', 45, '20-Jan-2009', 'Jazz', 3.11, 6,
- artist_array_type( artist_type('Miles Davis', 'Composer'),
- artist_type('Miles Davis', 'Musician')
- ),
- review_table_type( review_type('Sara', '3-Oct-2016', 'Another must have! One of Miles finest works', 5),
- review_type('Douglas', '14-Jun-2014', 'You might like it, but I admit it seems like a difficult listen.', 5)
- ),
- 'Audio CD', 1, 6.41, 7.00
- )
- )
- /
- INSERT INTO albums
- VALUES(
- disk_type('Gustav Mahler Symphony No.9 ', 45, '12-Oct-2017', 'Classical', 23.10, 5,
- artist_array_type( artist_type('David Zinman', 'Conductor'),
- artist_type('Gustav Miller', 'Composer'),
- artist_type('Tonhalle Orchaestra', 'Orchestra')
- ),
- review_table_type( review_type('Lindon', '3-Dec-2010', 'This is an uneventful but fine recording.', 3),
- review_type('Prescott', '24-Aug-2013', 'This is truly a spellbinding record.', 5)
- ),
- 'Audio CD', 1, 15.20, 7.00
- )
- )
- /
- ---MP3 Queries---
- INSERT INTO albums
- VALUES(
- mp3_type('Bob Dylan''s Greatest Hits', 55, '1-Jan-2019', 'Pop Rock', 5.98, 10,
- artist_array_type(
- artist_type('Bob Dylan', 'Composer'),
- artist_type('Bob Dylan', 'Vocals')
- ),
- review_table_type( review_type('Mandy', '16-Mar-2019', 'Fantastic music!.', 5)
- ),
- 60
- )
- )
- /
- INSERT INTO albums
- VALUES(
- mp3_type('Best of Neil Young', 153, '21-Feb-2019', 'Pop Rock', 17.50, 35,
- artist_array_type( artist_type('Neil Young', 'Composer'),
- artist_type('Neil Young', 'Vocals')
- ),
- review_table_type( review_type('John', '16-Apr-2019', 'Great artist and great music.', 5)
- ),
- 165
- )
- )
- /
- INSERT INTO albums
- VALUES(
- mp3_type('Harvest (2009 Remaster)', 44, '21-Jun-2009', 'Rock Country', 9.49, 10,
- artist_array_type( artist_type('Neil Young', 'Composer'),
- artist_type('Neil Young', 'Vocals')
- ),
- review_table_type( review_type('John', '16-Apr-2019', 'Great artist and great music.', 5)
- ),
- 52
- )
- )
- /
- INSERT INTO albums
- VALUES(
- mp3_type('Sketches of Spain', 45, '16-Aug-2013', 'Jazz', 24.99, 6,
- artist_array_type( artist_type('Miles Davis', 'Composer'),
- artist_type('Miles Davis', 'Musician')
- ),
- review_table_type( review_type('Douglas', '14-Jun-2014', 'You might like it but I admit it seems like a difficult listen.', 5)
- ),
- 51
- )
- )
- /
- INSERT INTO albums
- VALUES(
- mp3_type('B.B. King Greatest Hits', 114, '16-Jul-2013', 'Rock Blues', 11.49, 24,
- artist_array_type( artist_type('B.B. King', 'Vocals'),
- artist_type('B.B. King', 'Guitar')
- ),
- review_table_type( review_type('David', '18-May-2015', 'You might like it but I admit it seems like a difficult listen.', 5)
- ),
- 125
- )
- )
- /
- INSERT INTO albums
- VALUES(
- mp3_type('The Essential Bob Dylan', 99, '8-Jul-2016', 'Pop', 16.00, 32,
- artist_array_type( artist_type('Bob Dylan', 'Composer'),
- artist_type('Bob Dylan', 'Vocals')
- ),
- review_table_type( review_type('Christopher', '24-Jun-2016', 'This is a terrific album.', 5),
- review_type('Cauley', '2-Apr-2015', 'There can only be one Bob Dylan. God blessed him with the gift of verse', 5)
- ),
- 112
- )
- )
- /
- INSERT INTO albums
- VALUES(
- mp3_type('Other Peoples Lives', 42, '15-Feb-2019', 'Rock Dance', 9.49, 10,
- artist_array_type( artist_type('Stats', 'Composer'),
- artist_type('Stats', 'Vocals')
- ),
- review_table_type( review_type('George', '17-Sep-2019', 'Good dancing music.', 3)
- ),
- 45
- )
- )
- /
- --Create ALL_ALBUMS view for Q7 to Q9
- CREATE OR REPLACE VIEW all_albums (albumTitle, albumType, albumPrice, discount, albumUsedPrice) AS
- SELECT a.albumTitle, COALESCE(TREAT(VALUE(a) AS Disk_Type).MediaType,'MP3'),
- albumPrice, (albumPrice-a.discountPrice()),
- COALESCE(TREAT(VALUE(a) AS Disk_Type).diskUsedPrice , 0)
- FROM albums a;
- /
- -- Query 1 --
- SELECT DISTINCT ALBUMTITLE, ALBUMRELEASEDATE, ALBUMPRICE FROM albums a, TABLE(a.ALBUMARTISTS) c
- WHERE
- ALBUMRELEASEDATE > '1-Jan-2015' AND
- c.artistName = 'Neil Young'
- /
- -- Query 2 ---
- SELECT DISTINCT albumtitle, c.artistname FROM albums a, TABLE(a.ALBUMARTISTS) c
- WHERE VALUE(a) IS OF (MP3_TYPE)
- AND
- albumtitle NOT IN (
- SELECT DISTINCT albumtitle
- --,c.artistname
- FROM albums a
- --, table(a.ALBUMARTISTS) c
- WHERE VALUE(a) IS OF (disk_type)
- )
- ORDER BY ALBUMTITLE
- /
- -- Query 3 --
- WITH abc AS (
- SELECT albumTitle, AVG(c.reviewScore) AS averageScore FROM albums a, (TABLE(a.ALBUMREVIEWS)) c
- WHERE VALUE(a) IS OF (MP3_TYPE)
- HAVING COUNT(c.reviewScore) > 1
- GROUP BY albumTitle
- )
- SELECT * FROM abc
- WHERE averageScore = (SELECT MIN(averageScore) FROM abc)
- /
- --- Query 4 ---
- SELECT albumtitle FROM albums a
- WHERE VALUE(a) IS OF (DISK_TYPE)
- INTERSECT
- SELECT albumtitle FROM albums a
- WHERE VALUE(a) IS OF (MP3_TYPE)
- ORDER BY albumtitle
- /
- -- Q5 answer is implemented in the member function section from line 79---
- -- View for Q6 and Q7
- CREATE OR REPLACE VIEW all_albums (albumTitle, albumType, albumPrice, discount, albumUsedPrice) AS
- SELECT a.albumTitle, COALESCE(TREAT(VALUE(a) AS Disk_Type).MediaType,'MP3'),
- albumPrice, (albumPrice-a.discountPrice()),
- COALESCE(TREAT(VALUE(a) AS Disk_Type).diskUsedPrice , 0)
- FROM albums a;
- /
- -- Q6 Select the largest discount
- SELECT * FROM all_albums
- WHERE discount = (SELECT MAX(discount) FROM all_albums)
- /
- --- Q7 Select the largest usedPrice
- SELECT * FROM all_albums
- WHERE albumUsedPrice = (SELECT MAX(albumUsedPrice) FROM all_albums)
- /
- -- Q8 Select AlbumTitle, ReviewScore that has word great
- SELECT DISTINCT albumTitle, c.reviewText, c.reviewScore FROM albums a, TABLE(a.ALBUMREVIEWS) c
- WHERE a.containsText(c.ReviewText, 'great') > 0
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement