I’m still doing my Stanford online course on relational databases. Today, I learned about the basics of SQL, a special programming language designed for managing data held in a relational database. The teacher of the class, Jennifer Widom, kicked off the class by talking about the difference between a Data Definition Language (‘DDL’) and a Data Manipulation Language (‘DML’):

Data Definition Language (‘DDL’)

• Create a table in the database
• Drop a table from the database

Data Manipulation Language (‘DML’)

• Query the database -> “Select” statement
• Modify the database -> “Insert”, “Alert” or “Update” statement

Jennifer then told us about the Basic “Select” statement (see Fig. 1 below), explaining that the result of such a statement is to return a relation with a set of data attributes. For example, when you take a simple college admissions database as a starting point where there are 3 relations, each relation having its own set of unique attributes:

• College ( College Name, State and Enrolment)
• Student (Student ID, Student Name, GPA and Size High School)
• Apply (Student ID, College Name, Major and Decisions)

Jennifer then gave us the following examples:

Query involving a single relation

select sID, sName, GPA

from Student

where > 3.6

This query will give you the name and student IDs of those applicants with a GPA higher than 3.6.

Query combining two relations

select sName, Major

from Student, Apply

where Student.sID = Apply.sID

This query will give you data on the names and student IDs for those students applied, filtered by Major.

Jennifer pointed out that SQL is a multi-set model and it therefore allows duplicates. You can eliminate duplicate values by adding the keyword “distinct” to your query. Jennifer also mentioned that SQL is an unordered model which means that you can sort results.

You can include an “order by” clause in your query and add “descending” to order the results of your query:

where Apply.sID = Student.sID

and Apply.cName = College.cName

order by GPA desc, Enrolment;

Main learning point: I found this class about creating a basic “select” statement particularly helpful, as it helped me to get a better understanding of how basic SQL queries are constructed.

Fig. 1 – Elements of the basic “Select” statement in SQL