SQL Explained
SQL, the Standard Query Language, is the interface to nearly all databases
on the market today.
- ANSI SQL 92 is the official standard.
- We use JDBC to tell our database what SQL to run.
- Databases store data in tables. The tables have named columns and rows.
- Picture an Excel spreadsheet as a database table, but stop right there - contrary to some beliefs, Excel is not even close to a database!
- The rows contain the data, called records.
- You can join tables, most often with a primary and foreign key. The primary key must be unique; it identifies a single row in a table. The foreign key need not be unique.
- For example, we might have a table called colleges, and another table called students.
- Colleges would have a primary key field called CollegeID, this would be 32 for CAS or 30 for CECE (shed a tear). There would be one and only one entry for each college (DAAP, CCM, CBA, CAS, CECE, A&S, etc).
- Students would have a foreign key field called HomeCollegeID. Each student would have one and only one record in the students table, and that record would have a CollegeID set to the student's home college.
- Then, if you wanted to see all of the students whos home college is CAS, you could simply join the two tables together on the field CollegeID and HomeCollegeID.
- What benefits do we gain by having multiple tables with few columns instead of one large table with many columns?