Q1 Study the file Seuss_Data.sql using either Notepad++, Textpad, Sublime Text o
ID: 3727471 • Letter: Q
Question
Q1
Study the file Seuss_Data.sql using either Notepad++, Textpad, Sublime Text or any other text editor that makes it easy to read and modify. Subsequently, create a database in a file called q1.sql containing the following tables: Artists, BestSongsEver, and Languages. Artists has the following fields: Name, NumberOfMembers, CurrentlyActive (e.g. The Cure, 5, 1) or (David Bowie, 1, 0) BestSongsEver will include the following fields: Name, SongTitle, Year, Ranking, Genre, and at least two additional fields that you will make up. (e.g. David Bowie, Heroes, 1977, 4, Rock, SOMETHING, SOMETHINGELSE) Languages has the following two fields Ranking, Language In staying with the previous example (e.g. 4, English) but also (4, French) and (4, German) as Bowie’s song Heroes, which I ranked as number 4 of the best songs of all time, has additional French and German versions. Create at least 10 different artists, 15 different songs (and rank them) and make sure to fill in the Language table as well for each song. You need to have at least some songs that are not in English. Further also confirm that you have both currently active and not currently active artists. Make sure the database gets created and filled in, and once you have this working hand in the file as q1.txt (IMPORTANT: due to issues with connex, it needs to be a .txt NOT a .sql file)
Q2
Write queries to get the following information from your database in Question 1 1. Show all the songs in your database ordered by your ranking (best song ranked number 1 followed by number 2 etc.) alongside your two additional fields: (order of fields: Ranking, SongTitle, SOMETHING, SOMETHINGELSE) 2. Show all the songs in your database of currently active bands ordered by year (oldest first) (order of fields: Ranking, SongTitle, Year, Name, NumberOfMembers, CurrentlyActive) 3. Show all the unique languages alphabetically of currently not active artists (so without repeating the same language twice in the output): (order of fields: Language) Put your working queries in the q2 .txt file (each on a separate line) the file should contain 3 lines (one for each query). Hand in q2.txt
Q3
Write a query to create an inner join on Name using tables Artists and BestSongsEver. Direct the resulting output into a q3.csv file. Hand in q3.csv
-Create the tables -_ create table characters ( name text, title text, human int, year int)i create table books title int, year int, pages int) Insert data into each table-- insert into characters values( 'Cat in the Hat', 'The Cat in the Hat', 0, 1958); insert into characters values( 'Sally', 'The Cat in the Hat', 1, 1958); insert into characters values('Thing One', 'The Cat in the Hat', 0, 1958) insert into characters values ( Thing Two', 'The Cat in the Hat, 0, 1958); insert into characters values ( 'Fish', 'The Cat in the Hat', 0, 1958) insert into characters values( Sallys Brother', 'The Cat in the Hat', 1, 1958); insert into characters values ( Sam-I-Am', "Green Eggs and Ham', 0, 1960) insert into characters values( 'Once-ler', "The Lorax', 1, 1971) insert into characters values('Ted', The Lorax, 1, 1971); insert into characters values (OHare' 'The Lorax', 1, 1971); insert into characters values ( Once-ler', "The Lorax', 1, 1971); insert into characters values ( 'The Grinch', "How the Grinch Stole Christmas ', 0, 1957 insert into characters values (Cindy-Lou Who', "How the Grinch Stole Christmas, 1, 1957) insert into characters values ( "Max, "How the Grinch Stole Christmas ', 0, 1971); insert into characters values( 'Mr. Brown', 'Mr. Brown Can Moo! Can You?', 1, 1970); insert into characters values ( 'Horton', "Horton Hears a Who', 0, 1954) insert into characters values( 'Jo-Jo', 'Horton Hears a Who', 0, 1954) insert into characters values( 'Mayor of Who-ville, "Horton Hears a Who', 0, 1954); insert into characters values (Wickersham Brothers,'Horton Hears a Who', 0, 1954); insert into characters values( 'Kangaroos "Horton Hears a Who', 0, 1954) insert into characters values( 'Horton', 'Horton Hatches the Egg', 0, 1940) insert into characters values ( 'Mayzie', 'Horton Hatches the Egg', 0, 1940) insert into books values (The Cat in the Hat', 1958, 61) insert into books values(Green Eggs and Ham, 1960, 62); insert into books values ( 'The Lorax', 1971, 72); insert into books values(How the Grinch Stole Christmas',1957, 64) insert into books values ('Mr. Brown Can Moo! Can You?, 1970, 32) insert into books values( 'Horton Hears a Who, 1954, 64) insert into books values ( 'Horton Hatches the Egg, 1940, 64); mode column .header on select * from characters;Explanation / Answer
Q1)
create table Artists(Name text, NumberOfMembers int, CurrentlyActive tinyint);
create table BestSongsEver(Name text, SongTitle text, Year int, Ranking int, Genre text, followers int, likes int);
create table Languages(Ranking int, Language text);
insert into Artists values('The Cure', 5,1);
insert into Artists values('David Bowie', 1,0);
insert into Artists values('Ed Sheeran', 5,1);
insert into Artists values('Kendrick Lamar', 4,1);
insert into Artists values('Post Malone', 4,1);
insert into Artists values('French Montana', 2,0);
insert into Artists values('Niall Horan', 4,0);
insert into Artists values('Cardi B', 2,1);
insert into Artists values('Shawn Mendes', 3,0);
insert into Artists values('Luis Fonsi', 5,1);
insert into BestSongsEver values('David Bowie', 'Heroes',1977, 4, 'Rock', 20000, 374);
insert into BestSongsEver values('The Cure', 'How log it is',1978, 1, 'Pop', 189, 238473);
insert into BestSongsEver values('David Bowie', 'Baby sona',2003, 2, 'DJ', 10000, 1000);
insert into BestSongsEver values('Ed Sheeran', 'You wanna',2006, 3, 'Club', 15000, 2347);
insert into BestSongsEver values('Shawn Mendes', 'What is',2007, 5, 'Rock', 6000, 23875);
insert into BestSongsEver values('Niall Horan', 'Do you an rock',2011, 2, 'Classic', 2670, 3877);
insert into BestSongsEver values('Shawn Mende', 'Just wanna',2008, 5, 'Rock', 9373, 43895);
insert into BestSongsEver values('Cardi B, 'Are emaindi',2009, 3, 'Party', 18747, 49878);
insert into BestSongsEver values('Shawn Mendes', 'Panchanava',2007, 4, 'DJ', 28764, 3489);
insert into BestSongsEver values('Cardi B', 'Sahore',1988, 1, 'Rock', 28742, 5784);
insert into BestSongsEver values('Niall Horan', 'Baebe',1991, 3, 'Club', 34867, 4389);
insert into BestSongsEver values('Cardi B', 'Hello rock',1978, 4, 'Rock', 377863, 3487);
insert into Languages values(4, English);
insert into Languages values(4, French);
insert into Languages values(4, German);
insert into Languages values(1, Telugu);
insert into Languages values(2, Hindi);
insert into Languages values(5, Tamil);
Q2)
SELECT Ranking, SongTitle, followers, likes FROM BestSongsEver ORDER BY Ranking;
SELECT Ranking, SongTitle, Year, B.Name, NumberOfMembers, CurrentlyActive FROM BestSongsEver B JOIN Artists A ON B.Name=A.Name WHERE CurrentlyActive=1 ORDER BY B.Year ASC;
SELECT Language FROM (Artists A JOIN BestSongsEver B ON A.Name=B.Name) JOIN Languages L ON L.Ranking=B.Ranking WHERE CurrentlyActive = 0 ORDER BY Language ASC;
Q3)
.headers on
.mode csv
.output q3.csv
SELECT * FROM Artists A INNER JOIN BestSongsEver B ON A.Name=B.Name;
.quit
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.