CS 272 Software Development

CS 272-01, CS 272-02 • Spring 2023

Canvas GitHub Piazza Live Polls

SQL Demo: Faculty Contact

This is another SQL demo that illustrates the process of designing tables and creating complex SELECT statements from those tables to get different views of the data. The primary table we want to create is as follows:

name email twitter courses
Greg D. Benson benson@usfca.edu @gregorydbenson CS 315, CS 326
David Guy Brizan dgbrizan@usfca.edu @davidguybrizan CS 245, CS 463
Christopher Brooks cbrooks@usfca.edu   CS 462
Mehmet Emre memre@usfca.edu @maemre CS 345, CS 414
Sophie J. Engle sjengle@usfca.edu @sjengle CS 186, CS 272, CS 360
Alark Joshi apjoshi@usfca.edu @alark CS 110, CS 112, CS 360, CS 480
EJ Jung ejung2@usfca.edu   CS 112, CS 221, CS 245
Olga Karpenko okarpenko@usfca.edu   CS 112, CS 245, CS 272, CS 490
Michael C. Kremer mkremer@usfca.edu   CS 110, CS 386
Matthew Malensek mmalensek@usfca.edu @MatthewMalensek CS 220, CS 326
Phil Peterson phpeterson@usfca.edu   CS 221, CS 272, CS 315
Vahab Pournaghshband vpournaghshband@usfca.edu   CS 221, CS 336
Kelsey Urgo kurgo@usfca.edu   CS 110, CS 486
Ellen Veomett eveomett@usfca.edu   CS 245
David Wolber wolberd@usfca.edu @wolberd CS 107, CS 110, CS 112
Beste F. Yuksel byuksel@usfca.edu @BesteFYuksel CS 107, CS 110, CS 490

Before viewing the rest of this demonstration, see if you can determine the tables and columns that should be created first.

Quick Setup

If you do not want to copy/paste the commands for this example, they can be found in the sql subdirectory of the cs272 public folder on the lab computers.

For example, you can create the tables and insert the rows for this example by doing:

SOURCE /home/public/cs272/sql/faculty.sql

To double check this created the necessary tables, do:

| Tables_in_user01 |
| faculty_courses  |
| faculty_names    |
| faculty_twitter  |

The other faculty_????.sql files contain the various SELECT statements from this demonstration.

Next: Creating Tables