Before diving into the details of SQL, it’s a good idea to develop an overall perspective on the language and how it works. This chapter contains a quick tour of SQL that illustrates its major features and functions. The goal of the quick tour is not to make you proficient in writing SQL statements; that is the goal of Part II of this book. Rather, by the time you’ve finished this chapter, you will have a basic familiarity with the SQL language and an overview of its capabilities.
1. A Simple Database
The examples in the quick tour are based on a simple relational database for a small distribution company. The database, shown in Figure 2-1, stores the information needed to implement a small order processing application. Specifically, it stores the following information:
- The customers who buy the company’s products
- The orders placed by those customers
- The salespeople who sell the products to customers
- The sales offices where those salespeople work
This database, like most others, is a model of the “real world.” The data stored in the database represents real entities—customers, orders, salespeople, and offices. There is a separate table of data for each different kind of entity. Database requests that you make using the SQL language parallel real-world activities, as customers place, cancel, and change orders, as you hire and fire salespeople, and so on. Let’s see how you can use SQL to manipulate data.
2. Retrieving Data
First, let’s list the sales offices, showing the city where each one is located and its year- to-date sales. The SQL statement that retrieves data from the database is called SELECT. This SQL statement retrieves the data you want:
The SELECT statement asks for three pieces of data—the city, the office number, and the amount of sales—for each office. It also specifies that all of this data comes from the OFFICES table, which stores data about sales offices. The results of the query appear, in tabular form, immediately after the request.
The SELECT statement is used for all SQL queries. For example, here is a query that lists the names and year-to-date sales for each salesperson in the database. It also shows the quota (sales target) and the office number where each person works. In this case, the data comes from the next SALESREPS table.
SQL also lets you ask for calculated results. For example, you can ask SQL to calculate the amount by which each salesperson is over or under quota:
The requested data (including the calculated difference between sales and quota for each salesperson) once again appears in a row/column table. Perhaps you would like to focus on the salespeople whose sales are less than their quotas. SQL lets you retrieve that kind of selective information very easily, by adding a mathematical comparison to the previous request:
You can use the same technique to list large orders in the database and find out which customer placed the order, which product was ordered, and in what quantity. You can also ask SQL to sort the orders based on the order amount:
3. Summarizing Data
SQL not only retrieves individual pieces of data from the database, but it can be used to summarize the database contents as well. What’s the average size of an order in the database? This request asks SQL to look at all the orders and find the average amount:
SELECT AVG(AMOUNT)
FROM ORDERS
AVG(AMOUNT)
—————
$8,256.37
You could also ask for the average order size for a particular customer:
SELECT AVG(AMOUNT)
FROM ORDERS
WHERE CUST = 2103
AVG(AMOUNT)
—————
$8,895.50
Finally, let’s find out the total value of the orders placed by each customer. To do this, you can ask SQL to group the orders together by customer number and then total the orders for each customer:
4. Adding Data to the Database
You can also use SQL to add new data to the database. For example, suppose you just opened a new Western region sales office in Dallas, with target sales of $275,000. Here’s the INSERT statement that adds the new office to the database, as office number 23:
INSERT INTO OFFICES (CITY, REGION, TARGET, SALES, OFFICE)
VALUES (‘Dallas’, ‘Western’, 275000.00, 0.00, 23)
1 row inserted.
Similarly, if Mary Jones (employee number 109) signs up a new customer, Acme Industries, this INSERT statement adds the customer to the database as customer number 2125 with a $25,000 credit limit:
INSERT INTO CUSTOMERS (COMPANY, CUST_REP, CUST_NUM, CREDIT_LIMIT)
VALUES (‘Acme Industries’, 109, 2125, 25000.00)
1 row inserted.
5. Deleting Data
Just like the SQL INSERT statement adds new data to the database, the SQL DELETE statement removes data from the database. If Acme Industries decides a few days later to switch to a competitor, you can delete Acme’s customer information from the database with this statement:
DELETE FROM CUSTOMERS
WHERE COMPANY = ‘Acme Industries’
1 row deleted.
And if you decide to terminate all salespeople whose sales are less than their quotas, you can remove them from the database with this DELETE statement:
DELETE FROM SALESREPS
WHERE SALES < QUOTA
1 rows deleted.
6. Updating the Database
You can also use SQL to modify data that is already stored in the database. For example, to increase the credit limit for First Corp. to $75,000, you would use the SQL UPDATE statement:
UPDATE CUSTOMERS
SET CREDIT_LIMIT = 75000.00
WHERE COMPANY = ‘First Corp.’
1 row updated.
The UPDATE statement can also make many changes in the database at once. For example, this UPDATE statement raises the quota for all salespeople by $15,000:
UPDATE SALESREPS
SET QUOTA = QUOTA + 15000.00
8 rows updated.
7. Protecting Data
An important role of a database is to protect the stored data from access by unauthorized users. For example, suppose your assistant, named Mary, has not been previously authorized to insert data about new customers into the database. This SQL statement grants her that permission:
GRANT INSERT
ON CUSTOMERS
TO MARY
Privilege granted.
Similarly, the following SQL statement gives Mary permission to update data about customers and to retrieve customer data with the SELECT statement:
GRANT UPDATE, SELECT
ON CUSTOMERS
TO MARY
Privilege granted.
If Mary is no longer allowed to add new customers to the database, this REVOKE statement will disallow it:
REVOKE INSERT
ON CUSTOMERS
FROM MARY
Privilege revoked.
Similarly, this REVOKE statement will revoke all of Mary’s privileges to access customer data in any way:
REVOKE ALL
ON CUSTOMERS
FROM MARY
Privilege revoked.
8. Creating a Database
Before you can store data in a database, you must first define the structure of the data. Suppose you want to expand the sample database by adding a table of data about the products your company sells. For each product, the data to be stored includes the following:
- A three-character manufacturer ID code
- A five-character product ID code
- A product description of up to 30 characters
- The price of the product
- The quantity currently on hand
This SQL CREATE TABLE statement defines a new table to store the products’ data:
CREATE TABLE PRODUCTS
(MFR_ID CHAR(3),
PRODUCT_ID CHAR(5),
DESCRIPTION VARCHAR(20),
PRICE MONEY,
QTY_ON_HAND INTEGER)
Table created.
Although more cryptic than the previous SQL statement examples, the CREATE TABLE statement is still fairly straightforward. It assigns the name PRODUCTS to the new table and specifies the name and type of data stored in each of its five columns.
Once the table has been created, you can fill it with data. Here’s an INSERT statement for a new shipment of 250 size 7 widgets (product ACI-41007), which cost $225.00 apiece:
INSERT INTO PRODUCTS (MFR_ID, PRODUCT_ID, DESCRIPTION, PRICE, QTY_ON_HAND)
VALUES (‘ACI’, ‘41007’, ‘Size 7 Widget’, 225.00, 250)
1 row inserted.
Finally, if you discover later that you no longer need to store the products’ data in the database, you can erase the table (and all of the data it contains) with the DROP TABLE statement:
DROP TABLE PRODUCTS
Table dropped.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.