DITA - Week 3

When I attempted to query the database with the examples shown in the lecture, I quickly found that I was using the wrong words and descriptions for the entities and the data tables. For example, I am asked in Task 1.1 to use the field 'Company Name' which I tried as 'company name', 'CompanyName' and other variations before having to ask for help.

I used the command "show tables;" to find the first set of data tables, then specified that I wanted publishers in order to find the exact term for 'Company Name' using the command "desc publishers;".

+--------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| pubid | int(10) unsigned | NO | PRI | 0 | |
| name | varchar(100) | YES | | NULL | |
| company_name | varchar(100) | YES | | NULL | |
| address | varchar(100) | YES | | NULL | |
| city | varchar(100) | YES | | NULL | |
| state | varchar(100) | YES | | NULL | |
| zip | varchar(30) | YES | | NULL | |
| telephone | varchar(30) | YES | | NULL | |
| fax | varchar(30) | YES | | NULL | |
| comments | text | YES | | NULL | |

Which gave me the first answer, Company Name is written like company_name. Now on with the tasks.

Task:

Develop SQL queries to return following information :

1. A list of the PubID, Name, Company Name and City for all publishers based in the city of New York
2. A list of all fields for publishers named Prentice Hall.
3. A list of the Title, Year and ISBN for all titles published in 1994.
4. A list of the Title, Year, ISBN and PubID for all titles published since 1980 in year order
5. A list of all fields in the Titles table for books whose title begins with the word 'database' (regardless of upper/lower case letters)
6. A list of all fields in the Titles table for books whose title with the word 'database' anywhere in the title (regardless of upper/lower case letters)
7. A list of the title, Year Published and ISBN for all books with 'SQL' in the title written since 1990 in date order
8. A list of the Company Names of all publishers who have published books on programming since 1990
9. The name of the publisher who published a book with ISBN 0-0280074-8-4
10. The name of the author who wrote "A Beginner's Guide to Basic" listing also, the ISBN and name of this book.

Answers:

1) select PubID, Name, Company_Name, City
from publishers
where City = "New York";

2) select * from publishers where name = "Prentice Hall";

3) select title, year_published, isbn
from titles
where year_published = 1994;

(For 1.3, I managed to get the result, but the entries where too large to fit on the screen. Also, the MySQL shell couldn't fit all the characters from the result on screen, I tried to rearrange them with the added \G or \g on the end of the command, but the result was still off screen.)

4) I originally tried "select title, year_published, isbn, pubid from titles where year_published >1979;" but found that the date was out of order (again the result was too large to fit on the screen). Instead I tried

select title, year_published, isbn, pubid
from titles
where year_published >1979 order by year_published asc;

... and found my result.
5) select *
from titles
where title like "database%";

6) This is similar, only to get an 'any' result I add another wildcard.

select *
from titles
where title like "%database%";

7) "select title, year_published, isbn from titles where title like "%SQL%", year_published >= 1990 order by year_published asc;" was my original guess, however it was not correct. Whereas the entities such as title and year_published can be separated by a comma, the last bit of "%SQL%" and "year_published" had to be written with an and.

select title, year_published, isbn
from titles
where title like "%SQL%" and year_published >= 1990 order by year_published asc;

8) select distinct company_name
from publishers, titles
where title like "%programming%" and publishers.pubid = titles.pubid and year_published >= 1990;

Had real trouble with this one, I knew I had to knit two tables together but struggled to remember learned lessons such as the SELECT bit can have entities seperated by a comma, but from then on I must seperate with AND. Also, finding out the right combination of primary key and foreign key was a step for the imagination.

9) select company_name
from publishers, titles
where publishers.pubid = titles.pubid and isbn like "0-0280074-8-4";

Similar to the previous one, I tied the two parts together with primary and foreign keys, the rest was rather simple.

10) I needed help with this one. With the previous question, I was only attempting to query the company name, whereas here I have to find the author's name as well as the isbn and title of the book. However, I did get a satisfactory answer.

select author, titles.isbn, title
from authors, title_author, titles
where authors.au_id = title_author.au_id and title_author.isbn = titles.isbn
and title =”A Beginner’s Guide to Basic”;

The part I still am trying to comprehend is the where section, with knitting together two parts of the table.