Simply SQL - Section 1 | SitePoint Premium (2024)

Almost every web site nowadays, it seems, uses a database. Your objective, as a web developer, is to be able to design or build web sites that use a database. To do this, you must acquire an understanding of and the ability to useStructured Query Language(SQL), the language used to communicate with databases.

We'll start by introducing the SQL language and the major SQL statements that you’ll encounter as a web developer.

SQL Statement Overview

In the past, SQL has been criticized for having an inappropriate name. Structured Query Language lacks a proper structure, does more than just queries, and only barely qualifies as a programming language. You might think it fair criticism then, but let me make three comments:

• Structurerefers to the fact that SQL is about tables of data or, more specifically, tabular structures. A table of data has columns and rows. There are many instances where we’ll encounter an alternative that isn’t, strictly speaking, a table, but looks and acts like one. This tabular structure will be explained in Chapter3.

• While SQL includes many different types of statements, the main one is theSELECTstatement, which performs aqueryagainst the database, to retrieve data. Querying data effectively iswhere the action is, the primary focus of the first eight chapters. Designing the database effectively is covered in the last three chapters.

• The SQL language has been standardized. This is immensely important, because when you learn effective SQL, you can apply your skills in many different database environments. You can develop sites for your client or boss using any of today’s common database systems—whether proprietary or open source—because they all support SQL.

Those three concepts—tabular structures, effective querying, and SQL standards—are the secret to mastering SQL. We’ll see these concepts throughout the book.

We’ll begin our overview of SQL statements by looking at their components: keywords, identifiers, and constants.

Keywords, Identifiers, and Constants

Just as sentences are made up of words that can be nouns, verbs, and so on, an SQL statement is made up of words that are keywords, identifiers, and constants. Every word in an SQL statement is always one of these:

Keywords

These are words defined in the SQL standard that we use to construct an SQL statement. Many keywords are mandatory, but most of them are optional.

Identifiers

These are names that we give to database objects such as tables and columns.

Constants

These are literals that represent fixed values.

Let’s look at an example:

Here is a perfectly respectable SQL statement. Let’s examine its keywords, identifiers, and constants:

• SELECT,FROM, andWHEREare keywords.SELECTandFROMare mandatory, butWHEREis optional. We'll cover only the important keywords in SQL in this book. However, they’re all listed in AppendixD for your reference.

• name,teams, andidare identifiers that refer to objects in the database.nameandidare column names, whileteamsis a table name.We’ll define both columns and tables later on in this chapter but, yes, they are exactly what you think they are.

• Theequalssign (=) is anoperator, a special type of keyword.

• 9is anumeric constant. Again, we'll look at constants later in the chapter.

So there you have it. Our sample SQL statement is made up of keywords, identifiers, and constants. Not so mysterious.

Clauses

In addition, we often speak of the clauses of an SQL statement. This book has entire chapters devoted to individual clauses. Aclauseis a portion of an SQL statement. The name of the clause corresponds to the SQL keyword that begins the clause. For example, let’s look at that simple SQL statement again:

TheSELECTclause is:

TheFROMclause is:

TheWHEREclause is:

Syntax

Each clause in an SQL statement has syntax rules for how it may be written.Syntaxsimply means how the clause is put together—what keywords, identifiers, and constants it consists of, and, more importantly, whether they are in the correct order, according to SQL’sgrammar. For example, theSELECTclause must start with the keywordSELECT.

Up to this point, I’ve alluded to a couple of database object types: tables and columns. To reference database objects in SQL statements we use their identifiers, which are names that are assigned when the objects are first created. This leads naturally to the question ofhowthose objects are created.

Before we answer that, let’s take a moment to introduce some new terminology. SQL statements can be divided into two types: DDL and DML.

Data Definition Language(DDL)

DDL is used to managedatabase objectslike tables and columns.

Data Manipulation Language(DML)

DML is used to manage thedatathat resides in our tables and columns.

The terms DDL and DML are in common use, so if you run into them, remember that they’re just different types of SQL statements. The difference is merely a convenient way to distinguish between the types of SQL statements and their effect on the database. DDL changes the database structure, while DML changes only the data. Depending on the project, and your role as a developer, you may not have the authority or permission to write DDL statements. Often, the database already exists, so rather than change it, you can only manipulate the data in it using DML statements.

The next section looks at DDL SQL statements, and how database objects are created and managed.

Data Definition Language

In the beginning the Universe was created. This has made a lot of people very angry and been widely regarded as a bad move.

--Douglas Adams

Where do database objects like tables and columns come from? They are created, modified, and finally removed from the database using DDL, the Data Definition Language part of SQL. Indeed those three tasks are accomplished using theCREATE,ALTER, andDROPSQL statements.

CREATE, ALTER, and DROP

Of the many DDL statements,CREATE,ALTER, andDROPare the three main ones that web developers need to be aware of. (The others are more advanced and beyond the scope of this book.) Even if you haven’t been granted the authority or permission to execute DDL statements on a given project, it helps to know the DDL to see how tables are structured and interconnected.

The CREATE Statement

Earlier on, I suggested that a tabular structure is one of the main concepts you need to understand when learning SQL. It’s actually quite simple, and a table of data looks exactly like you would intuitively expect it to—it has columns and rows. Each table contains information concerning a set of items. Eachrowin a table represents a single item. Eachcolumnrepresents one piece of information that can be stored about each item. Figure1.1 provides a visualization of a table calledteamswith three columns namedid,name, andconference. The table pictured also contains some data; each row in the table represents a single team and can store three pieces of information about that individual team: its id number, its name, and its conference (its division or league).

Figure1.1.Tables have rows, and rows have columns

Here’s an example of a DDL statement; this is the statement that creates the database table pictured in Figure1.1:

TheCREATE TABLEstatement creates theteamstable—but not the data—with three columns namedid,name, andconference. This is a table used in theTeams and Gamessample application, one of several sample applications used in the book. All the applications are described in AppendixB.

It would be optimistic to expect you to understand everything in theCREATE TABLEstatement above at this stage. (I’m sure some of you, new to SQL, might be wondering “What’s anid?” or “What doesPRIMARY KEYdo?” and so on.) We simply want to see an example of theCREATE TABLEstatement, and not be sidetracked by design issues for the Teams and Games application.

Note that the keywords of theCREATE TABLEstatement are all in capital letters, while the identifiers are all in lower case letters. This choice is part of my coding style.

Notice also the formatting and white space. Imagine having to read this SQL statement all on one long line:

Neatness helps us to spot parts of the statement we have omitted or mispelled, like theNOT NULLthat was accidentally left off thenamecolumn in the one line version of the statement above. Did you spot the omission before you read this?

Looking at the sampleCREATE TABLEstatement, we see that each of the three columns is given a data type (e.g.,INTEGER,VARCHAR), and is also designatedNULLorNOT NULL. Again, please don’t worry if these terms are new to you. We will discuss how they work and what they’re for in Chapter9. This introductory chapter is not supposed to teach you the SQL statements in detail, merely introduce them to you and briefly describe their general purpose.

Are there other database objects that we can create besides tables? Yes. There are schemas, databases, views, triggers, procedures and several more but we’re getting ahead of ourselves again. ManyCREATEstatements are for administrative use only and hence solely used by designated database administrators (DBAs). Learning to be a DBA is such a large subject, it requires a book of its own just to cover its scope! Needless to say, our coverage of Database Administration topics will be kept to a minimum.

The ALTER Statements

As its name suggests,ALTERchanges an object in a database. Here’s an exampleALTERstatement:

The keywordDROPidentifies what’s being dropped, or removed, from the table. In this example, theteamstable is being altered by removing theconferencecolumn. Once the column is dropped, it’s no longer part of the table.

Note that if we tried to run the sameALTERstatement for a second time, a syntax error would occur because the database cannot remove a column that does not exist from a table. Syntax errors can arise from more than just the improper construction of the SQL statement using keywords, identifiers, and constants. Many syntax errors arise from attempting to alter what are perceived (wrongly) to be the current structure or current contents of the table.

The DROP Statement

TheDROPstatement—to round out our trio of basic DDL statements—drops, removes, deletes, obliterates, cancels, blows away, and/or destroys the object it is dropping. After theDROPstatement has been run, the object is gone.

The syntax is as simple as it can be:

To summarize, the Data Definition Language statementsCREATE,ALTER, andDROPallow us to manage database objects like tables and columns. In fact, they can be very effective when used together, such as when you need to start over.

Starting Over

Database development is usually iterative. Or rather, when building and testing your table (or tables—there is seldom only one) you will often find yourself repeating one of the following patterns:

• CREATE, then test

  First, you create a table. Then you test it, perhaps by running someSELECTqueries, to confirm that it works. The table is so satisfactory that it can be used exactly as it is, indefinitely. If only life were like this more often …

• CREATE, then test …ALTER, then test …ALTER, then test …

  You create and test a table, and it’s good enough to be used regularly, such as when your web site goes live. You alter it occasionally, to make small changes. Small changes are easier than larger changes, especially if much code in the application depends on a particular table structure.

• CREATE, then test …DROP,CREATE, then test …

  After creating and testing a table the first time, you realize it’s wrong. Or perhaps the table has been in use for some time, but is no longer adequate. You need to drop it, change your DDL, create it again (except that it’s different now), and then test again.

Dropping and recreating, or starting over, becomes much easier using an SQLscript. A script is a text file of SQL statements that can be run as a batch to create and populate database objects. Maintaining a script allows you to start over easily. Improvements in the design—new tables, different columns, and so on—are incorporated into the SQL statements, and when the script is run, these SQL statements create the objects using the new design. AppendixC contains SQL scripts used for the sample applications in this book. These scripts and more are available to download from the web site for this book, at:https://www.sitepoint.com/premium/books/simply-sql/.

Data Manipulation Language

In the last section, we covered the three main SQL statements used in Data Definition Language. These wereCREATE,ALTER, andDROP, and they are used to manage database objects like tables and columns.

Data Manipulation Language has three similar statements:INSERT,UPDATE, andDELETE. These statements are used to manage the data within our tables and columns.

Remember the earlierCREATEstatement example:

This statement creates a table calledteamsthat has three columns, pictured in Figure1.2.

Figure1.2.Theteamstable

Once the table has been created, we say it exists. And once a table exists we may place our data in it, and we need a way to manage that data. We want to use the table the way it’s currently structured, so DDL is irrelevant for our purposes here (that is, changes aren’t required).

Instead, we need the three DML statements,INSERT,UPDATE, andDELETE.

INSERT, UPDATE, and DELETE

Until we put data into it, the table is empty. Managing our data may be accomplished in several ways: adding data to the table, updating some of the data, inserting some more data, or deleting some or all of it. Throughout this process, the table structure stays the same. Just the table contents change.

Let’s start by adding some data.

The INSERT Statement

TheINSERTDML statement is similar to theCREATEDDL statement, in that it creates a new object in the database. The difference is that whileCREATEcreates a new table and defines its structure,INSERTcreates a new row, inserting it and the data it contains into an existing table.

TheINSERTstatement inserts one or more rows. Here is our first opportunity to see rows in action. Here is how to insert a row of data into theteamstable:

The important part to remember, with our tabular structure in mind, is that theINSERTstatement insertsentire rows. AnINSERTstatement should contain two comma-separated lists surrounded by parentheses. The first list identifies the columns in the new row into which the constants in the second list will be inserted. The first column named in the first list will receive the first constant in the second list, the second column has the second constant, and so on.There must be the same number of columns specified in the first list as constants given in the second, or an error will occur.

In the above example, three constants,9,'Riff Raff', and'F'are specified in theVALUESclause. They are inserted, into theid,name, andconferencecolumns respectively of a single new row of data in theteamstable. Strings, such as'Riff Raff', and'F', are surrounded by single quotes to denote their beginning and end. We’ll look at strings in more detail in Chapter9.

You are allowed (but it would be unusual) to write thisINSERTstatement as:

We noted earlier that the database itself doesn’t care about the order of the columns within a table; however, it’s common practice to order the columns in anINSERTstatement in the order in which they were created for our own ease of reference. As long as we make sure that we list columns and their intended values in the correct corresponding order, this version of theINSERTstatement has exactly the same effect as the one preceding it.

Sometimes you may see anINSERTstatement like this:

This is perhaps more convenient, because it saves typing. The list of columns isassumed. The columns in the new row being inserted are populated according to their perceived position within the table, based on the order in which they were originally added when the table was created. However, we must supply a value for every column in this variation ofINSERT; if we aren’t supplying a value for each and every column, which happens often, we can’t use it. If you do, the perceived list of columns will be longer than the list of values, and we’ll receive a syntax error.

My advice is to always specify the list of column names in anINSERTstatement, as in the first example. It makes things much easier to follow.

Finally, to insert more than one row, we could use the following variant of theINSERTstatement:

This example shows anINSERTstatement that inserts three rows of data, and the result can be seen in Figure1.3. Each row’s worth of data is specified within a set of parentheses, known as arow constructor, and each row constructor is separated by a comma.

Figure1.3.The result of theINSERTstatement: three rows of data

Next up, we want to change some of our data. For this, we use theUPDATEstatement.

The UPDATE Statement

TheUPDATEDML statement is similar to theALTERDDL statement, in that it produces a change. The difference is that, whereasALTERchanges the structure of a table,UPDATEchanges the data contained within a table, while the table’s structure remains the same.

Let’s pretend that the team Riff Raff is changing conferences so we need to update the value in theconferencecolumn fromFtoE; we’ll write the followingUPDATEstatement:

The above statement would change the value of theconferencecolumn in every row toE. This is not really what we wanted to do; we only wanted to change the value for one team. So we add aWHEREclause to limit the rows that will be updated:

Teams_04_UPDATE.sql (excerpt)

As shown in Figure1.4, the above example will update only one value. TheUPDATEclause alone would change the value of theconferencecolumn in every row, but theWHEREclause limits the change to just the one row: where theidcolumn has the value9. Whatever value theconferencecolumn had before, it now hasEafter the update.

Figure1.4.Updating a row in a table

Sometimes, we’ll want to update values in multiple rows. TheUPDATEstatement will set column values for every row specified by theWHEREclause. The classic example, included in every textbook (so I simply had to include it too, although it isn’t part of any of our sample applications), is:

Here, everyone is scoring a 7% raise, but only if theirjobgradeis4or less. TheUPDATEstatement operates on multiple rows simultaneously, but only on those rows specified by theWHEREclause.

Notice that the existing value of thesalarycolumn is used to determine the new value of thesalarycolumn.UPDATEoperates on each row independently of all others, which is exactly what we want, as it’s likely that the salary values are different for most rows.

Finally, there is theDELETEstatement.

The DELETE Statement

TheDELETEDML statement is similar to theDROPDDL statement, in that it removes objects from the database. The difference is thatDROPremoves a table from the database, whileDELETEremoves entire rows of data from a table, but the table continues to exist:

Teams_05_DELETE.sql (excerpt)

Once again, like theUPDATEstatement, the scope of theDELETEstatement is every row which satisfies theWHEREclause. If there is noWHEREclause, all the rows are deleted and the table is left empty; it has a structure, but no rows.

Finally, we are ready to meet theSELECTstatement.

The SELECT Statement

TheSELECTstatement is usually called aquery. Informally, all SQL statements are sometimes called queries (as in “I ran theDELETEquery and received an error”), but theSELECTstatement is truly a query because all it does is retrieve information from the database.

When we run aSELECTquery against the database, it can retrieve data from one or more tables. Exactly how the data in multiple tables is combined, collated, compared, summarized, sorted, and presented—by a single query—is what makes SQL so wonderful.

The power is outstanding. The simplicity is amazing. SQL allows us to produce complex, customized information with a minimum of fuss, in a declarative, non-procedural way, using a small number of keywords.

SELECTis our fourth DML statement, although the operation it performs on the data is simply to retrieve it. Nothing is changed in the database. This is one reason why I prefer to discussSELECTseparately from the other three DML statements. Another is that it breaks up the pleasant symmetry between the DDL and DML statements:

• DDL:CREATE,ALTER,DROP

• DML:INSERT,UPDATE,DELETE… andSELECT

The SELECT Retrieves Data

A simpleSELECTstatement has two parts, orclauses. Both are mandatory:

The purpose of theSELECTstatement is to retrieve data from the database:

• theSELECTclause specifies what you want to retrieve, and

• theFROMclause specifies where to retrieve it from.

TheSELECTclause consists of one or more expressions involving keywords, identifiers, and constants. For example, thisSELECTclause contains one expression, consisting of a single identifier:

In this case the expression in theSELECTclause isname, which is a column name. However, theSELECTclause can contain many expressions, simply by listing them one after another, using commas as separators. For example, we may want to return the contents of several columns from rows in theteamstable:

In addition, each expression can be more complex, consisting of formulas, calculations, and so on. Ultimately then, theSELECTclause can be fairly complex, but it gives us the ability to include everything we need to return from the database. We examine theSELECTclause in Chapter Similarly, theFROMclause can be multifaceted. TheFROMclause specifies the tabular structure(s) that contain the data that we want to retrieve. In this chapter we’ve seen sample queries in which theFROMclause specified a single table; complexity in theFROMclause occurs when more than one table is specified. I suggested earlier that tabular structures are one of the secrets to mastering SQL. We’ll cover them in detail in Chapter3.

We’ll have an overview of theSELECTstatement and its optional clauses,WHERE,GROUP BY,HAVING, andORDER BY, in Chapter2, and then look in detail at each of its clauses in chapters of their own.

The SELECT Statement Produces a Tabular Result Set

One important fact to remember aboutSELECTis that the result of running aSELECTquery isa table.

When your web application (whether it is written in PHP, Java, or any other application language) runs aSELECTquery, the database system returns a tabular structure, and the application handles it accordingly, as rows and columns of data. The query might return a list of selected items in a shopping cart, or posts in active forums threads, or whatever your web application needs to retrieve from your database.

We say that theSELECTstatement produces a tabularresult set. A result set is not actually a table in the database; it isderived fromone or more tables in the database, and delivered, as tabular data, to your application.

One final introduction will complete our introduction to SQL: a comment about standard SQL.

Standard SQL

The nice thing about standards is that there are so many to choose from.

--Andrew S. Tanenbaum

In the beginning, I mentioned that SQL is a standard language. SQL has been standardized both nationally and internationally. If you are relatively new to SQL,do notlook for any information on the standard just yet; you’re only going to confuse yourself. The SQL standard is exceedingly complex. The standard is also not freely available; it must be purchased from the relevant standards organisations.

The fact that the standard exists, though, is very important, and not just because it makes the skill of knowing SQLhighlyportable. The SQL standard is being adopted, in increasing measures, by all relational database systems. New database software releases always seem to mention some specific features of the standard that are now supported.

So as well as your knowledge of using simple SQL to produce comprehensive results being portable, there is a better chance that your next project’s database system will actually support the techniques that you already know. The industry and its practitioners are involved in a positive feedback loop.

And yet, there are differences between standard SQL and the SQL supported by various database systems you’ll encounter—sometimes maddeningly so. These variations in the language are calleddialectsof SQL. Numerous proprietary extensions to standard SQL have been invented for the various different database systems. These extensions can be considerable, occasionally pointless, often counterintuitive, and sometimes obscure.

There is only one sane way to cope.

Read The Fine Manual

Never memorize what you can look up in books.

--Albert Einstein

There will be occasions throughout this book where I’ll suggest referring to the manual. This will be the manual for your particular database system, whatever it may be. All database systems have manuals—often a great many—but fortunately, most are of interest only to DBAs. The one you want is typically calledSQL Reference.

After more than 20 years of writing SQL, I still need to look up certain parts of SQL. Granted, I have committed most of standard SQL to memory, but there are always nuances that trip me up, and new features to learn, and all those proprietary extensions …

Make it easy on yourself. Bookmark the SQL reference manual on your computer, on your company server, or on the Web. Be prepared for syntax errors. But be reassured that they’re easy to fix, if you know where to look in the manual.

Wrapping Up: an Introduction to SQL

In this chapter, we coveredlotsof ground. I hope you’re not feeling completely overwhelmed. The purpose of the whirlwind tour was simply to put the SQL language into the perspective that a typical web developer needs; there are SQL statements for everything you need to do, and, all things considered, these statements are quite simple.

The two basic types of SQL statement are:

• Data Definition Language (DDL) statements:CREATE,ALTER,DROP

• Data Manipulation Language (DML) statements:INSERT,UPDATE,DELETE… andSELECT

If you’re building your own database, you’ll need to know DDL, but you should have some experience using DML first. Designing databases is the subject of the last three chapters in this book.

As mentioned earlier, SQL is mainly about queries, and theSELECTstatement iswhere it’s at. Chapter2 begins our in-depth look at theSELECTstatement, providing an overview of its six clauses.