HW: Intro to SQL due Tue 18 Apr 10:30

\begin{purpose}
By completing this assignment you will develop proficiency in c...
... queries in SQL to extract information from an existing database.
\end{purpose}

Instructions

REMINDER: When following the instructions below you need to use names and type exactly.

Perform these actions:

  1. (2 pts) Log in to your CPanel account and create a new database called books. Add the MySQL user called ``class'' to the new database and give that user all privileges. The remaining steps are to be taken in phpMyAdmin in the new database you just created.

  2. (2 pts) Using your browser download the file makebooks.sql from the course website by right-clicking on it in the file list and saving it to your computer. See: http://josephus.hsutx.edu/classes/dw/source/

  3. (2 pts) Click on the ``Import'' tab (in phpMyAdmin) and browse to the newly downloaded file. Then click the ``Go'' button. This make take about a minute to complete.

Now that you have created tables and imported data into them you are ready to write some queries. Once you get a query working the way you want it to you will paste the query you typed into a text document that you will upload into canvas.

Construct SQL queries to accomplish each of the following tasks:

  1. (2 pts) a list of books that have fewer than 70 pages; (in your text file, below this query state the number of rows displayed by this query)
  2. (2 pts) a list of all info about all books (including author name, publisher name, etc.) (in your text file, below this query state the number of rows displayed by this query)
  3. (2 pts) a list of books whose publication year is not known (in your text file, below this query state the number of rows displayed by this query)
  4. (3 pts) a count of the number of books in the database; in a separate query get a count of the number of authors in the database (in your text file, write the count given by each query accompanied by an explanation as to how these results are possible)
  5. (2 pts) a list of books by all authors whose last name is Baker; the query should show only the following fields: isbn, title, year, author last name and author first name; it should be ordered by first name, then by title (in your text file, below this query state the number of rows displayed by this query)
  6. (3 pts) a list of book titles that appear more than once (in your text file, below this query state the number of rows displayed by this query)
  7. (2 pts) use the query from problem 2 to create a named view called big ; (in your text file answer this question: after this view is created, what does the query SELECT * FROM big produce?)
  8. (2 pts) use the view from problem 7 as the starting point for a query that will list isbn, title, publisher, and author name of all books by authors whose last name begins with `Z' (in your text file, below this query state the number of rows displayed by this query)

You will store each of the SQL queries you create in a single text file that you will email as an attachment to cscihomework@hsutx.edu.

Quick Links