Exercise 1

Solution Notes

Output from running Carol's submission

This submission would receive 3/3.

If you added comments, that was a Good Thing To Do, but, for this exercise, you do not receive extra marks for the exercise.

For Carol's code with comments, see Queries against the SQL, Bugs, and Rock 'n' Roll Database Solution Notes.

You should follow the instructions on the lab worksheet.

The Queries against the SQL, Bugs, and Rock 'n' Roll Database lab worksheet clearly states:

Save the code for each query in a separate .sql  file.

You should not have placed more than one query in your q1.sql  file.

See the Marking Scheme for Exercises.

Do not use both  ;  and  /  to terminate a query.
If you do, the test harness will run your query twice.

The  /  means "run the command in the buffer".

In future, if you submit code which causes run a query more than once, you may loose marks.

Exercise 1 asked you to:

Place your code for the first query on the Queries against the SQL, Bugs, and Rock 'n' Roll Database lab worksheet in your  q1.sql  file (remembering to terminate the query with a  ;  or  /).

The first query on that lab worksheet is:

List full details of all artists.

Hint:  Use  *  as your  <select_list>

If you look at the description of the SQL, Bugs, and Rock 'n' Roll Database, you will see that artists and musicians are not the same thing.

If you listed full details of musician, then your code produces incorrect output for the task in the exercise.

What about joining the artist table to the album table to list more information?

The hint given should have led you to realize that this was not what was expected.

Hint:  Use  *  as your  <select_list>

If you joined the tables and used  *  as your  <select_list>, then SQL returned a duplicated column.

If you removed the duplicate column by specifying each column in your  <select_list>, then you did not use  *  as your  <select_list>.

And if you joined the tables without a join condition, then you produced a Cartesian product which definitely not what was wanted.

When writing code, apply Occam's Razor:

"Pluralitas non est ponenda sine neccesitate"
"Plurality should not be posited without necessity"

which is often restated as:

"The simplest solution is usually the best"

What about joining all the tables and listing all the information in the database?

That is not a Good Idea.

One student wrote:

-- A basic query displaying only the Artist_Id and their Name could be executed by the following code:
-- SELECT *
-- WHERE Carol.Artist
-- /

Is this statement true?

One student submitted:

Select distinct *
  from carol.musician;

What is the purpose of the  distinct  keyword?