SQL (Structured Query Language) is a widely used language to interrogate relational databases. Relational database technologies are widely used to store large amounts of structured data, i.e. data that can be organized in records with fields, where records correspond to entities in the broadest sense of the word, and fields correspond to attributes of these entities. A collection of records is stored in what is often called a table.
An example of a relational database is our student information system, Banner. Thus, Banner contains a student table in which it stores information about students, such as their names, addresses, date of birth and so on, a grade table in which it stores the grades that students received in a particular course, and so on.
A little thought will show that we can store tables as 2-dimensional arrays, where the rows are the records and the columns the fields. The cell in which the row and column intersect would then store the value that the entity that is represented by the record has for the field stored in the column. The following is a simplified example of what a student table might look like. We assume that the first column is the student's ID. the second the first name, the third the last name, the fourth the county in which the student resides, and the fifth the year in which they were born:
0033 | Sandra | Smith | Cobb | 1989 |
1144 | Bo | Harrison | DeKalb | 1987 |
2255 | Sam | Mitchell | Cobb | 1969 |
3366 | Sabrina | Jones | Cobb | 1979 |
4477 | Harry | Johnson | Cobb | 1982 |
5588 | Lisa | Simpson | Paulding | 1982 |
and so on |
As said, we may also have a table in which we store the results of the grades that students got in certain courses. The following is an example, where the first column is the student ID, the second the semester in which they took the course, the third the prefix of the course, the fourth the number of the course, the fifth the grade, and the sixth the name of the instructor:
5588 | 201405 | CSE | 1301J | A | Ian Salts |
5588 | 201405 | COMM | 2400 | B | Evan Duggan |
5588 | 201408 | CSE | 1302J | A | Ian Salts |
0033 | 201405 | CSE | 1301J | C | Ian Salts |
0033 | 201405 | COMM | 2400 | A | Evan Duggan |
and so on |
Now storing data in a table is one thing (and you typically use SQL for this as well), the reason you store data is that you can retrieve it and analyze it. For example, we may be interested in all students who reside in Cobb and who were born after 1970. If we were using a non-declarative programming language such as Java or C++, we would have to write a routine that would process the 2-dimensional array in which we had stored the table. In other words, we would have to write a routine that would look something like, where MAX is the size of the first dimension of the array, and STUDENT is the array in question:
for (i = 0; i < MAX; i++) {
if (STUDENT[i,2] == "Cobb" && STUDENT[i,3] > 1970) {
/** ADD i to the RESULT
....
}
}
As you start retrieving data stored in multiple tables, you will understand that this will get complicated. As an exercise, try to write a routine that retrieves all students who reside in Cobb, were born after 1970, and received a grade of C or better in a course taught by Evan Duggan. Use the the dropbox associated with this module to show your result.
Fortunately, SQL releases you from the need to write complicated routines to retrieve information from tables. Rather than writing routines to tell the computer not only what to retrieve but also how to do it, SQL simply allows you to tell the program what to retrieve through logical statements. Thus, the following is a SQL query to retrieve all students residing in Cobb and born after 1970:
SELECT * FROM STUDENTS
WHERE County = 'Cobb' AND DoB > 1970
You will agree that this is easier than writing routines to process 2-dimensional arrays, and you can imagine how much easier it gets when you have to retrieve data from multiple tables.
SELECT is the SQL command to retrieve data from a database and the WHERE clause specifies the conditions that records have to satisfy to be retrieved. In the query above, we used the AND operator, which you have of course encountered in the sub-module on propositional logic. However, SQL allows you to use quantifiers in the WHERE clause as well. In order to see how this works, please go to http://www.w3resource.com/sql/boolean-operator/sql-boolean-operators.php. Read this page, as well as the pages that you can reach from here for ALL and EXISTS. Email me the times at which you accessed each of these three pages, and the products or services that were advertised at the bottom of each page. Use the drop box associated with this sub-module to show your results.
If you do not quite understand how you would use quantifiers in SQL, do not worry. It will covered in great detail in CSE3153.