Learning SQL

We could accomplish everything we want to do with our database by interacting with a Graphical User Interface but learning SQL gives us a lot more power, flexibility, and speed. Also, learning SQL becomes inevitable when the database becomes more complex and the answers we want become more intricate as well. Familiarity in SQL helps us operate DBMS comprehensively since GUIs are proprietary and not uniform. We learned a little bit about SQL in the last chapter, that SQL is an open, declarative language, it is free and that every SQL statement takes the form of a command like "Give me all the people from the list whose names begin with T". Over the next four chapters, we will learn to do neat things with SQL and even write programs using SQL.

There are two types of Statements in SQL: Data Definition Language (DDL) and Data Manipulation Language (DML). DDL deals with creating and modifying data, while DML deals with getting existing data out of the database in a custom way. Previously, we learned about Analytical and Operational data and their difference - Operational data is information that gets modified a lot (e.g. bank account balance, daily sales of a certain retail product etc.) while analytical data is useful data that remains largely static (e.g. courses in a University degree program, census results etc.). DDL deals mostly with Operational data, while DML deals with both analytical and operational data.

The sort of information that will populate the database is an important consideration while choosing DBMS and designing Databases; DML works most efficiently with some designs while DDL works most efficiently with others. Let us look at some basic DML statements in SQL and their equivalent operations in the MS ACCESS Graphical User Interface. The basis of all statements that get data out of the database is the SELECT command.

Consider the GymMembers Tables in our sample HealthyGym Database

Screen Shot 5a: Gymmembers Table

The following SQL command

SELECT MemberAddress, MemberName FROM GymMembers

will yield a result that looks like this:

Screen Shot 5b: Result of select query

As you can see, the query just returned the columns MemberName and MemberAddress in the exact order we specified. The returned group of rows is called the result set. The basic SELECT command syntax is as follows:

SELECT COLUMN NAMES FROM TABLE NAME

Now, let us see how we do the same thing through the MS Access GUI