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 or from stream processing in a .
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 Enrollment)
- 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
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, Enrollment;
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 – Taken from: http://www.w3resource.com/sql/sql-syntax.php