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 |
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.
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
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>