Implement a Relational Database with SQL

What is SQL?

Up to now, we’ve looked at the DBMS, its features, and how to put together a robust database that should operate quickly and store cohesive records.

Now it’s time to look at the ways you view and modify data once your tables are populated.

Nope. Databases use what is known as a fourth generation language, SQL, to retrieve data from the database. Pronounced S-Q-L, or like the word "sequel," it stands for Structured Query Language and is the standard for relational database management systems.

Third generation languages are more traditional programming languages such as C++, JAVA, C#, and so forth. With these, you have to specify how to retrieve the data. In SQL, you only have to write the SQL statement, and the DBMS determines how the data is retrieved. SQL is a much more human-readable language, with statements that often resemble sentences rather than programming code.

Work With CRUD

In general, SQL allows for CRUD statements. CRUD represents Create (or insert), Read (or select), Update, and Delete. CRUD statements are the most basic database table commands used to manipulate table data and structure. 

Create is an operation that can create a new table or database object such as a table or index, while the INSERT operation creates a new record in the table.

Read allows the user to select data from one or more tables to view the results without making any actual changes to the data.

Update gives the user the option to update a single field in a record or all values in a field or column. 

Delete actions can remove one or more records from a table or drop a table all together. 

Add Filter Parameters With the WHERE Clause

How quickly can you read through a million records? 😵

It turns out that reading though records you don’t need really need isn’t at the top of anyone’s list. Fortunately, there is a way to filter out records that don’t fit what you are looking for. The WHERE statement sets up a condition that allows you to narrow your search to only those records which have meaning for you at the moment. It defines the values from the rows you are searching for in your operation.

If you have a Student table and are looking to narrow your results to include only students who have completed more than 30 credit hours, you would write a statement like this to get your results: SELECT * FROM Students WHERE CreditHours > 30. Use the "greater than" operator to get records that have a value of more than 30.

You can organize this statement vertically:

Here is an example of a SELECT statement with no WHERE clause filtering data:

And the results:

And here is an example of a SELECT statement with a WHERE clause:

And the results filtered by the WHERE clause:

Get Data From Multiple Tables With the JOIN Statement

It's not often that you can get all of the data you need from one table. Most of the time it's found across multiple tables and you need to search them for the right results. Remember that the foreign key (FK) in one table links back to the primary key (PK) in another. This method allows you to link or chain multiple tables together.

If you had an online book database and two tables, one for Authors with the AuthorID, LastName and FirstName and a second table for Books with BookID, AuthorID, and Title, you could get data from both tables with a JOIN statement. Use the AuthorID as the link between the tables since this is the primary key in the Authors table and foreign key in the Books table. The statement would look something like this. SELECT a.LastName, a.FirstName, bk.Title from Books bk JOIN Authors auth on bk.AuthorID=auth.AuthorID.

We joined the two tables together using the JOIN statement and let the DBMS know the keys we are joining by using the Authors aa on b.AuthorID=a.AuthorID statement. 

These are called aliases. You can alias the tables by using an abbreviation after the table name. We aliased Books by using bk, and Authors by using auth.

Aliases can even be just one letter since you use them to get fields from both tables in the SELECT statement. Below you'll notice that the alias b has been used for Books instead of bk.

Examine Other SQL Functionality (LIKE, IN/NOT IN, etc.)

There are more reserved keywords in SQL that help filter data even further and in different ways than what you’ve seen so far. Wildcard characters are typically the percent sign (%) and allow you to define WHERE clauses which have values similar to the characters you enter. If you put it in a statement at the beginning such as %son, you are looking for all last names that end in son and any other characters before it. If it is placed at the end, such as Wil%, you will get all last names beginning with Wil, and any characters beyond. Using this method would return any records with this exact phrase with no concern about what is before or after the text. The operation is helpful when you may not be sure of the exact data you are looking for or are looking for text containing specific letters or phrases.

When you are looking for specific text or numbers, you can use the IN or NOT IN reserved words to retrieve specific results. If you are looking at country abbreviations and need those from the US and UK, you would formulate a statement similar to WHERE CountryCode IN (US,UK) to retrieve only those records for those particular country codes. Add the NOT reserved word in before IN to get countries that are not the US or UK.

Let’s Recap!

• CRUD is an acronym used to define database operations of create, read, update, and delete.

• Use the WHERE clause in SELECT statements to filter the records retrieved.

• Use JOIN statements to join two or more tables when reading or updating records.

• Aliases are used as a shorthand notation for table names, often seen with JOIN statements. For example, "SELECT * FROM Students s"; the "s" is the aliased table name and can be used in place of the full table name.

• You can further filter records with the use of LIKE, IN, NOT IN, and other reserved SQL words.

 Now that we're familiar with SQL, we're ready begin populating your database with data!