Skip to main content

Relational Databases

Database Theory is Challenging#

It is not likely that you will absorb any of information in this video on Entity Relationship Design without first being exposed to some database examples. So that is what we will do today: we will focus on getting the project done. You will learn by doing. Tomorrow we can go into more depth regarding the bigger picture regarding databases. The VLOOKUP function in Excel seems complicated, but compared to SQL, it is pretty easy because it walks you through the steps. There is not a help step in SQL. You just have to know it, or have a cheat sheet. We will use a cheat sheet!

Relative, Absolute, and Mixed References#

Before we discuss the VLOOKUP function, we need to be aware of absolute and relative cell references. If you want to learn more:

It is not necessary to have watched the video to follow along in class.

Short version

we must use absolute references in our lookup data range. Excel uses relative references by default. To make a column or row reference absolute, manually place a dollar sign ($) in front of the reference. You may also cycle through the choices by clicking in the formula bar

formula bar

and typing mac: command + T or pc: fn + F4.

See Table 1 for examples.

Table 1

TypeCellAction
Relative Reference:A2None; default in Excel
Absolute Reference:$A$2enter dollar sign in front of column and row reference
Mixed Reference:$A2 or A$2enter dollar sign in front of column or row reference

VLOOKUP Demo#

Here is an excel file you can download if you want to follow along in class: VLOOKUP Examples. I put in some Star Wars and Harry Potter data to make it a little more fun. We will practice this a lot.

SQL#

It's pronounced ess-que-el, and it stands for Structured Query Language.

This is the main thing you need to take away from this recitation — you select a field (AKA column), from a table, where some sort of condition, or conditions, can be matched. And if you want, you can order (or sort) your data from ASC ascending, or DESC descending.

SELECT column1, column2, ...
FROM table_name
WHERE condition;
ORDER BY DESC

For example, if you used SQL to find all book titles in a book database that contained the word freedom you would have to know what table in the database contained all the book titles.

  1. If you looked in a full output from the author table you would not find any titles:

    • tblAuthor @ CarolinaCloudApps

    • tblAuthor @ backupsite

    • In this output, you only have the results, so you don't even know what the column name is for the author.

  2. Nor would you find the title in a full output of the publisher table:

  3. If you looked in the book table you would finally find the listed titles:

    • tblBook @ CarolinaCloudApps
    • tblBook @ backupsite
    • However, you should not trust that the html headings are the actual database field (column) names. They might just be labels. One clue that they are not trustworthy is that some of the headers contain spaces: LOC Number and Pub FK. Notice that the SELECT statement example is looking for column1, column2. When you named your table in your VLOOKUP exercise, Excel would not allow you to put in a space or a hyphen. It would allow an underscore. Underscores are very common in Database column names.

    TLDR; the actual field name for the book title is tblBook_Title.

  4. Here is a

    to our book database. Here you can find all of the tables and all of the column heads ("field names" are more appropriate when discussing databases.)

  5. The reference page has a lot of information that will help you get started with writing your own SQL statements. I will demo several in class. There is a cut and paste example that you can use to find titles that contain the word freedom.

SQL Tasks#

This will require you to have the help page open in a window, the SQL entry page open in a window and this page open in a window.

Create three SQL queries#

Query 01:#

select all the titles from tblBook where the language is German and include both the titles and the language in the result.

If your query is successful, paste a copy of the query in cell N1 on your 05-Summary worksheet

Query 02:#

select all the names from tblPublisher where the city is like Berlin and include both the name and the country in the result

if your query is successful, paste a copy of the query in cell N2 on your 05-Summary worksheet

Query 03:#

select all the titles from tblBook where the topic includes the word Virginia and include both the titles and the topic in the result, sorted by the title

if your query is successful, paste a copy of the query in cell N3 on your 05-Summary worksheet

Edit this page