Due: 11:55 pm on the connex site.
Hand in exactly as shown, failure to adhere to file types will result in 0 marks
q1.txt
q2.txt
q3.csv
3 questions, 10 marks total
Worth 10% of your final grade
Question 1 [4 marks]:
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)
Question 2 [3 Marks]:
Write queries to get the following information from your database in Question 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)
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)
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
Question 3 [3 Marks]:
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
