Untitled

This demo will introduce various basic SQL concepts and statements via example. The data elements we will be working with are:

Office Phone Number Description
CASA (415) 422-5050 Office
SLE (415) 422-7256 Office
HPS (415) 422-5797 Office
HPS (888) 471-2290 Fax
CAPS (415) 422-6351 Office
CAPS (415) 422-6352 Office
CAPS (855) 531-0761 After Hours

Database Design

Notice how an office could have between 1 to 3 numbers associated with it? That is a “one to many” (one office to many phone numbers) relationship.

We will often split those values into separate tables so we can have exactly 1 row per item (one row per office in an offices table, and one row per number in an phones table).

The first table offices will capture the unique offices:

office_id office
1 HPS
2 SLE
3 CASA
4 CAPS

And then the next table phones will capture the office numbers:

phone_id area phone description office_id
1 415 422-5050 Office 3
2 415 422-7256 Office 2
3 415 422-5797 Office 1
4 888 471-2290 Fax 1
5 415 422-6351 Office 4
6 415 422-6352 Office 4
7 855 531-0761 After Hours 4

The exact tables will depend on which point you are at in the demo script.

Following Along

Make sure you have already logged into your database before following along with this demo. See the Database Accounts guide for details.

If you want to load the tables associated with this demo instead of following along with the guide, you can use this command after logging into your database account:

SOURCE /home/public/cs272/sql/intro.sql

Next Steps

The next step is to create the underlying tables in the database. Click the link below to get started.

<aside> <img src="/icons/arrow-right_gray.svg" alt="/icons/arrow-right_gray.svg" width="40px" /> Next: Creating Tables

</aside>