What Is SQL?

3 4

SQL is a database query and programming language used for accessing data and for querying, updating, and managing relational database systems. Both the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) have defined standards for SQL. ANSI is an organization of industry and business groups that develops trade and communication standards for the United States. ANSI is also a member of ISO and of the International Electrotechnical Commission (IEC). ANSI publishes U.S. standards that correspond to international standards. In 1992, ISO and IEC published an international standard for SQL called SQL-92. ANSI published a corresponding standard, ANSI SQL-92, in the United States. ANSI SQL-92 is sometimes referred to as ANSI SQL. Although different relational databases use slightly different versions of SQL, most comply with the ANSI SQL standard. SQL Server uses the superset of ANSI SQL-92 known as T-SQL, which conforms to the SQL-92 standard defined by ANSI.

The SQL language contains statements that fit into two main programming language categories: DDL and DML. We'll look at these language categories in the following sections.

DDL

DDL is used to define and manage database objects such as databases, tables, and views. (Views are explained in Chapter 18.) DDL statements usually include CREATE, ALTER, and DROP commands for each object. For example, the statements CREATE TABLE, ALTER TABLE, and DROP TABLE are used to initially create a table, alter its properties (by adding or deleting columns, for instance), and drop a table, respectively, as we'll see in the next few paragraphs.

The CREATE TABLE Statement

Let's use DDL to create a sample table named Customer_Data in the MyDB database. We'll use the Customer_Data table in later examples in this chapter. As mentioned, the CREATE TABLE statement is used to create a table. The sample table is defined with four columns, as shown here:

  Use MyDB CREATE TABLE Customer_Data (customer_id  smallint, first_name    char(20), last_name     char(20), phone         char(10)) GO 

This statement creates the structure for the Customer_Data table; the table is empty until data is inserted or bulk copied into it. For more details on database table creation, see Chapter 10.

The ALTER TABLE Statement

The ALTER TABLE statement is used to alter the definition or the attributes of a table. In this example, ALTER TABLE has been used to add a column, middle_initial, to the existing Customer_Data table:

  ALTER TABLE Customer_Data ADD middle_initial char(1) GO 

Now the table definition includes five columns instead of the original four. For more details on using ALTER TABLE, see Chapter 15.

The DROP TABLE Statement

The DROP TABLE statement is used to delete a table definition and all of the data, indexes, triggers, constraints, and permission specifications for that table. To drop our Customer_Data table, use the following command:

  DROP TABLE Customer_Data GO 

For more details on the DROP TABLE statement, see Chapter 15.

DML

DML is used to manipulate the data contained in database objects, using statements such as INSERT, SELECT, UPDATE, and DELETE. These statements allow you to select rows of data by performing queries, insert new rows of data, modify existing rows of data, and delete unwanted rows of data, respectively. This section gives basic examples of each. For more advanced uses of these statements, see Chapters 14 and 20.

The INSERT Statement

An INSERT statement is used to insert a row of data into a table or view. For example, if you want to add a new customer to the sample Customer_Data table, your INSERT statement might look like this:

  INSERT INTO Customer_Data (customer_id, first_name, last_name, phone) VALUES (777, "Frankie", "Stein", "4895873900") 

Notice the list of column names in the second line of the preceding SQL statement. Listing these column names specifies in which column the data values will be placed, in corresponding order. For example, the first data value will be placed into the first column listed, customer_id, the second value will go into the second column listed, and so on. Because we have listed the values to be inserted in the same order in which the columns were defined when the table was created, we do not have to specify the column names at all. We could use the following INSERT statement instead:

  INSERT INTO Customer_Data VALUES (777, "Frankie", "Stein", "4895873900")

CAUTION

---

If you use this form of the INSERT statement and the values to be inserted are not listed in the order in which the columns were created, the values might be placed in the wrong columns (if the data being inserted is compatible with the data types of the columns). If the data is not compatible with the data types, you will receive an error.

The SELECT Statement

A SELECT statement is used to retrieve data from a table or tables. The columns listed and the WHERE clause of the statement determine which data is retrieved. For example, say you want to retrieve the customer_id and first_name column values from the Customer_Data table created earlier. In addition, you want this data only for each row that has the value Frankie in the first_name column. The SELECT statement would look like this:

  SELECT customer_id, first_name FROM Customer_Data WHERE first_name = "Frankie" 

If one row was found to meet the criteria of the SELECT statement, the results might look like this:

  customer_id      first_name -------------    ------------ 777              Frankie 

The UPDATE Statement

An UPDATE statement is used to update, or change, a value or values in a row or rows of a table. For example, if the customer Frankie Stein called and wanted to change his first name in the records to Franklin, the UPDATE statement to change his first name would look like this:

  UPDATE Customer_Data SET first_name = "Franklin" WHERE last_name = "Stein" and customer_id=777 

We included the customer ID in the WHERE clause to make sure that if there are other customers with the same last name of Stein, their first names would not be updated also—only the customer whose ID is 777 will get his name updated.

NOTE

---

When you are writing UPDATE statements, be sure to provide sufficient filters in the WHERE clause so that you do not unintentionally update rows that you should not be updating.

The DELETE Statement

A DELETE statement is used to delete a row or rows of data from a table. You can even delete all rows from a table. To delete all rows from the Customer_Data sample table, you could use one of the following statements:

  DELETE FROM Customer_Data DELETE Customer_Data 

The FROM keyword before the table name is always optional in the DELETE statement; otherwise, the two statements are identical.

To delete the rows from the Customer_Data table in which the value in the customer_id column is less than 100, use the following statement:

  DELETE FROM Customer_Data WHERE customer_id < 100 

Now that we've had a quick overview of the DDL and DML statements provided by SQL, let's take a look at T-SQL.