Introduction to Databases for the Web: Pt. 2

Sunday Sep 13th 1998 by Selena Sol
Share:

SQL is the prime focus of this installment in our continuing introductory series by Selena Sol. Learn how to grab and modify data from a DB using standard SQL statements.

Retrieving Data

Okay, enough of all that DB Admin stuff. Let's get down to the nitty gritty of retrieving data from your database.

In SQL, the "SELECT - FROM" statement is used to grab data from a database. The statement follows the generic syntax of:

SELECT column_name 
FROM table_name;

There are a few things to notice about the format of this SQL statement that will help us understand all SQL statement formats.

First, notice that the statement ends in a semicolon. Like English, SQL requires termination punctuation so that the SQL database will know when you are done speaking to it. In the case of SQL, the semicolon (;) character works like the period in English.

Second, notice that the statement spans multiple lines. This is more of a convention than a necessity. Like HTML, SQL ignores whitespace in a statement, so you could just as easily write SELECT column_name FROM table_name. The reason we break the statement up into multiple lines is to increase readability. As your SQL statements get more and more complex, you will find that if you break them up into logical blocks, they will be easier to read.

Finally, notice that we have used all uppercase letters for our keywords (like SELECT and FROM). This is a good idea as even though most implementations of SQL are case insensitive, some are not. Okay, let's get back to the example...

In other words, if you wanted to get all the names of the employees in the EMPLOYEE table, you would type:

SELECT EMP_NAME 
FROM EMPLOYEES;

In the case of our sample database, you would get the following results:

    EMP_NAME
    -------------
    Lim Li Chuen
    Lim Sing Yuen
    Loo Soon Keat
    -------------

We are going to be using the sample database throughout the day in order to show examples, so get familiar with the tables.

You needn't limit yourself to single columns of course, The column_name parameter may take a comma delimited list so that if you also wanted a report for the employee number and salary, for example, you would simply use:

SELECT EMP_NAME, EMP_NUMBER, EMP_SALARY 
FROM EMPLOYEES;

In this case, you would get the following results:

EMP_NAME	EMP_NUMBER 	EMP_SALARY
------------------------------------------
Lim Li Chuen	001		90,000
Lim Sing Yuen	002		40,000
Loo Soon Keat	003		50,000
------------------------------------------

Note that if you specify a column name that does not exist, you will get an error.

Wildcards

As in most languages, SQL provides a set of wildcards that are used as shortcuts to represent whole categories of values. For example, oftentimes, you may want all the data for the columns in your table but you don't want to write all the column names in a comma-delimited list.

To make such queries more efficient, SQL provides the "*" wildcard that specifies "ALL" of something. For example, to select all the columns in the PRODUCTS table, we would use:

SELECT * 
FROM PRODUCTS;

The database would then respond with:

P_NUM	P_QUANTITY	P_PRICE
-------------------------------
001	104	 	 99.99
002	12		865.99
003	2000   		 50.00
-------------------------------

Of course you could achieve the same results (with more work) using:

SELECT P_NUM, P_QUANTITY, P_PRICE 
FROM PRODUCTS;

Where

Suppose you don't want all of the data for all of the rows in a table. Instead, perhaps you only want data for rows that match some criteria. For example, perhaps I want to see the data for the employees, but I only am interested in seeing the data for the employees who make over $45,000 per year.

Well, SQL provides the "where" clause for just these circumstances. The "where" clause allows you to specify conditions that a column cell must meet if it is to be considered a match and be returned in the results. In SQL syntax terminology, the WHERE clause is called the predicate.

The generic syntax of the WHERE clause looks something like the following:

SELECT column_name
FROM table_name
WHERE where_clause;

Consider the following case in which we ask the database to return only the rows in the SALES table in which the Employee number is equal to "101"

SELECT * 
FROM SALES
WHERE E_NUM = 101;

In this case, the database would return the following:

---------------------------------
001   001   1   99.99   101   102
003   002   1  865.99   101   103
---------------------------------

The WHERE clause can be used in conjunction with various testing operators besides the "=" sign. Specifically, you can use the ">", "<", "<=", or ">=" operators to select ranges. Thus, to get a report of all the employees making more than 45,000 per year, you might use the following:

SELECT *
FROM EMPLOYEES
WHERE EMP_SALARY > 45000;

If you are comparing a column of the CHARACTER data type, you can place the match string in single quotes ('').

For example, to find out Rick Tan's phone number from the CLIENTS table, you might use:

SELECT C_PHONE, C_NAME
FROM CLIENTS
WHERE C_NAME = 'Rick Tan';

In this case, the database would return the following:

C_PHONE           C_NAME
------------------------
649-2038        Rick Tan
------------------------

Note that although SQL is generally case insensitive, when you are matching CHARACTERS using the single quotes, you must be aware of case. Thus, WHERE name = 'CHRIS' would not return the same as WHERE name = 'Chris'.

And, Or, and Not

So far, the WHERE clauses we have discussed have been pretty darn simple. And as such, they are not all that useful in real world situations in which multiple conditions affect our needs. Actually, SQL provides a set of operators that allow you to combine multiple predicate statements. Specifically, a WHERE clause can be made up of multiple predicates by chaining them together with the AND, OR, and NOT operators.

These operators work as you might expect. The AND operator specifies multiple conditions which a column must match in order to be returned. The OR operator specifies multiple acceptable conditions. And the NOT operator specifies negativity in a match. However, these are best understood by example.

Consider the case in which we want to return only rows from the SALES table in which the salesman was salesman number "101", but we only want sales in which the price was greater than $100.00:

SELECT P_NUM, S_AMOUNT, E_NUM
FROM SALES
WHERE E_NUM = 101 AND S_AMOUNT > 100;

In this case, our database would return:

P_NUM     S_AMOUNT       E_NUM
------------------------------
002         865.99         101
------------------------------

In this case, the row was returned because S_AMOUNT was greater than "100" "AND" E_NUM was equal to "101". Now consider the same query using OR:

SELECT P_NUM, S_AMOUNT, E_NUM
FROM SALES
WHERE E_NUM = 101 OR S_AMOUNT > 100;

In this case, we would get the following response:

P_NUM     S_AMOUNT       E_NUM
------------------------------
001          99.99         101
001         199.98         102
003         865.99         101
------------------------------

Notice that the first row satisfied the condition because E_NUM was equal to "101" even though S_AMOUNT was less than "100". Likewise, the second row was selected because S_AMOUNT was greater than "100" even though E_NUM was not equal to "101."

Another important thing to note about AND and OR is that if you decide to combine multiple conditions, you must be very careful about how you compose your predicate. AND and OR conditions can be defined logically using parentheses as in most languages, but the order of the parentheses has meaning. Thus

SEX = "M" AND (WEIGHT > 150 OR AGE < 35)

is much different than saying

(SEX = 'M' AND WEIGHT > 150) OR AGE < 35

In the first case, no female subject could be returned whereas in the second case, a female could be returned if she was younger than 35.

Finally, the NOT operator is used to specify the reverse condition.

Thus, if you want a list of all the employees other than Lim Sing Yuen from the sample database, you would use:

SELECT EMP_NUM, EMP_NAME
FROM EMPLOYEES
WHERE NOT (EMP_NAME = 'Lim Sing Yuen');

In this case, the database would return the following:

EMP_NUM     EMP_NAME
--------------------
001     Lim Li Chuen
003    Loo Soon Keat
--------------------

Between

Like AND, OR and NOT, the BETWEEN operator is used to modify the WHERE clause. The BETWEEN operator works much like the combination of >=, AND, and <=. The fact is that, such circumstances arose so frequently, that the developers of SQL simply made a shortcut for the operation. Thus, to get a listing of all the employees with salaries between the range of 30,000 and 60,000, you could use the long hand version such as:

SELECT EMP_NAME, EMP_SALARY
FROM EMPLOYEES
WHERE EMP_SALARY >= 30000 
  AND EMP_SALARY <= 60000;

Or, you could use the BETWEEN operator such as:

SELECT EMP_NAME, EMP_SALARY
FROM EMPLOYEES
WHERE EMP_SALARY 
    BETWEEN 30000 AND 60000;

In either case, you'd get the following results:

EMP_NAME       EMP_SALARY
-------------------------
Lim Sing Yuen       40000
Loo Soon Keat       50000
-------------------------

As you can see, the BETWEEN operator is mainly a convenience operator to allow you to type less. As you might expect, the BETWEEN operator comes with its sister NOT BETWEEN operator. Thus, you could get all the employees who make more than 60,000 or who make less than 45,000 using:

SELECT EMP_NAME, EMP_SALARY
FROM EMPLOYEES
WHERE EMP_SALARY 
   NOT BETWEEN 45000 AND 60000;

In this case, you'd get the following results:

EMP_NAME      EMP_SALARY
------------------------
Lim Li Chuen       90000
Lim Sing Yuen      40000
------------------------

In

Like BETWEEN, the IN operator is used as a shorthand to specify multiple parameters in one statement. The IN operator looks generically like:

SELECT column_name
FROM table_name
WHERE column_name IN ('value', 'value', value);

The reverse, or course, looks like:

SELECT column_name
FROM table_name
WHERE column_name NOT IN ('value', 'value', value);

As you might have guessed, the IN operator works just the same as stringing multiple OR's together. The list of comma separated values defines a set of acceptable conditions. Thus, to get a listing of all the clients in the 90031 or the 90102 zip codes, you could string together OR operators such as:

SELECT C_NAME, C_ZIP
FROM CLIENTS
WHERE C_ZIP = 90031 
   OR C_ZIP = 90102;

Or using the IN operator, you would have:

SELECT C_NAME, C_ZIP
FROM CLIENTS
WHERE C_ZIP 
   IN ('90031', '90102');

In either case, you'd get the following results:

C_NAME            C_ZIP
-----------------------
Rick Tan          90031
Stephen Petersen  90102
-----------------------

Like

The LIKE operator is another extremely powerful tool to help you define your search. In its most simple form, the LIKE operator functions just like the "=" operator. That is, it matches for equality in the WHERE clause. Thus, if we were to search our EMPLOYEES table, the statement:

SELECT EMP_NAME, EMP_NUM 
FROM EMPLOYEES
WHERE 
   EMP_NAME = 'Lim Sing Yuen';

Would be the same as saying:

SELECT EMP_NAME, EMP_NUM 
FROM EMPLOYEES
WHERE 
   EMP_NAME LIKE 'Lim Sing Yuen';

In either case, you would get:

EMP_NAME      EMP_NUM
---------------------
Lim Sing Yuen     002
---------------------

However, the real umph of the LIKE operator comes from its ability to incorporate wildcards. Specifically, the LIKE operator can be used with the "%" and the "_" operator. The "%" operator can be used to match any string of any length and the "_" operator is used to match any single character. But this is best seen by example. Consider the following:

SELECT EMP_NAME, EMP_NUM 
FROM EMPLOYEES
WHERE EMP_NAME LIKE 'Lim%';

In this case, you would get the following:

EMP_NAME       EMP_NUM
----------------------
Lim Li Chuen       001
Lim Sing Yuen      002
----------------------

Notice that in both cases, the search string begins with "Lim". The "%" wildcard specifies that so long as the string begins with the string "Lim", it matches.

Consider the "_" operator.

SELECT EMP_NAME, EMP_NUM 
FROM EMPLOYEES
WHERE EMP_NUM LIKE '00_';

In this case, you would get all the records returned that included a "00" followed by any other character:

EMP_NAME       EMP_NUM
----------------------
Lim Li Chuen       001
Lim Sing Yuen      002
Loo Soon Keat      003
----------------------

Null

A final WHERE clause operator is the IS NULL/IS NOT NULL operator. This operator is used to test whether or not a column has a value or not. For example, we can grab all the columns from the EMPLOYEES table that have employee numbers using the following command.

SELECT EMP_NAME, EMP_NUM 
FROM EMPLOYEES
WHERE EMP_NUM IS NOT NULL;

In response, you would get:

EMP_NAME       EMP_NUM
----------------------
Lim Li Chuen       001
Lim Sing Yuen      002
Loo Soon Keat      003
----------------------

Order By

So far we have focussed on simply grabbing data from our database. We have not spent much time talking about how we can massage that data. One of the most common ways to manipulate data grabbed from a database is to order it, perhaps alphabetically by last name, or perhaps numerically, from high to low.

SQL provides the ORDER BY operator for just this purpose. The generic use of ORDER BY looks something like:

SELECT column_names 
FROM table
WHERE where_clause [OPTIONAL]
ORDER BY column_name;

Let's look at this by example. Suppose we have the following table:

Click here to view table 1.

Thus, if you would like to get an alphabetized list of artists, you would use:

SELECT BAND_NAME, COST
FROM CD
ORDER BY BAND_NAME;

You should get the following:

BAND_NAME              COST
---------------------------
Big Bad Voodoo Daddy  12.99
Indigo Swing          13.99
Levay Smith           13.99
Louis Jordan          12.99
Louis Jordan          10.99	
Louis Prima            9.99
---------------------------

Note that you can also specify the column number instead of the column name when defining an ORDER BY so that the following command would be just the same as the previous:

SELECT BAND_NAME, COST
FROM CD
ORDER BY 1;

Of course, you can also sort by multiple columns. That is, you specify an initial column to order by and then you choose subsequent columns with which to sort identical values in the initial column. For example, notice that if we sort by COST as well as BAND_NAME, the albums for Louis Jordan are sorted by cost as well as name:


SELECT BAND_NAME, COST
FROM CD
WHERE BAND_NAME LIKE 'Louis%'
ORDER BY BAND_NAME, COST;

You should get the following in which the 10.99 album is now listed first:

BAND_NAME      COST
-------------------
Louis Jordan  10.99
Louis Jordan  12.99	
Louis Prima    9.99
-------------------

Finally, you can use the DESC keyword to reverse the sort. Thus, to get a report sorted by cost in a descending order, you would use:

SELECT BAND_NAME, COST
FROM CD
ORDER BY COST DESC;

You would get the following view:

BAND_NAME               COST
----------------------------
Levay Smith            13.99
Indigo Swing           13.99
Big Bad Voodoo Daddy   12.99
Louis Jordan           12.99
Louis Jordan           10.99
Louis Prima             9.99
----------------------------

Performing Math

Sometimes, it is useful to actually massage the numerical data returned by performing basic arithmetic on the results. SQL gives you plenty of useful tools for doing just this. The most basic tools for arithmetic include the "+", "-", "*", and "/" operators as you might expect.

This basic type of arithmetic is usually performed in the SELECT clause and usually involves creating a new column based on the total achieved by doing the math. For example, consider the following example in which we subtract a 1.00 sale value to each of our products, add on the tax, and display the result in a virtual column "REAL_PRICE".

Note that we call this a virtual column because there is no "REAL_PRICE" column in the actual table. It only exists in this view. Note also that arithmetic can only be applied to numeric columns. Finally, note that arithmetic follows the usual precedence rules. For example, equations within parentheses are evaluated before they are applied to equations outside of parentheses.


SELECT P_NUM, P_PRICE, 
   REAL_PRICE = 
      (P_PRICE - 1.00) + 
      ((P_PRICE - 1.00) * .07)
FROM PRODUCTS;

The command will yield the following view

P_NUM  P_PRICE  REAL_PRICE
--------------------------
001      99.99      105.92
002     865.99      925.54
003      50.00       52.43
--------------------------

Another useful arithmetic tool is the SUM operator that is used to total a column. The basic format looks like:

SELECT SUM (column_name)
FROM table_name;
WHERE optional_where_clause;

For example, to get a SUM of all the products that cost less than 100.00 you could use:

SELECT SUM (P_PRICE)
FROM PRODUCTS
WHERE P_PRICE < 100.00;

The command will yield the following view:

SUM (P_PRICE)
-------------
       149.99
-------------

Maximums and Minimums

Another couple of useful tools are the MAX and MIN operators that allow you to grab the boundary values in a column (alphanumeric). The most generic syntax follows something like:

SELECT MAX (column_name)
FROM table_name
WHERE where_clause [optional];

For example, consider the following case in which we grab the employee with the highest salary:

SELECT MAX (EMP_SALARY)
FROM EMPLOYEES;

In this case, we would get the following:

MAX (EMP_SALARY)
----------------
           90000
----------------

Of course, you can also redefine the column name in the view by assigning the MAX or MIN value to a column name such as in the following case:


SELECT top_salary = MIN (EMP_SALARY)
FROM EMPLOYEES
WHERE EMP_COMMISSION = '20%';

In this case, we would get the following:

top_salary
----------
     40000
----------

Counting Records

It is also very easy to count the number of records that meet a certain criteria. This function is performed with the COUNT operator and follows the syntax:

SELECT COUNT (column_name)
FROM table_name
WHERE where_clause [optional];

In other words, to count the number of employees in the EMPLOYEES table, you would use:

SELECT COUNT (EMP_NAME)
FROM EMPLOYEES;

which would return the following:

COUNT (EMP_NAME)
----------------
               3
----------------

Note that sometimes, it is preferable to use SELECT COUNT(*) instead of specifying a column_name. This is because the COUNT operator does not consider columns with null values. Thus, if you specified a column_name and one of the rows in that table had a null value for column_name, your count would be off. Using "*" assures that all rows are counted even if they include null values. And by the way, most implementations of SQL will also require you to use the DISTINCT operator if you specify a column_name.

Distinct

It will often be the case that several rows will contain duplicate data in a specified column and that you will only want to view one to represent the group. For example, suppose you wanted to find out which employees had made sales in the SALES table.

If you were to use:

SELECT E_NUM
FROM SALES;

you would get:

E_NUM
-----
  101
  102
  101
-----

In this case, Employee number 101 has been reported twice even though once is enough. In this case, you would use the DISTINCT operator to filter out duplicates.

SELECT DISTINCT E_NUM
FROM SALES;

You would get:

E_NUM
-----
  101
  102
-----

Another common usage of distinct is to use it with a COUNT such as:

SELECT num_successful_salesmen  = 
   COUNT (DISTINCT E_NUM)
FROM SALES;

which would produce:

num_successful_salesmen
------
     2
------

Averages

A final tool that you can use to get aggregate data about a table is the AVG operator. The AVG operator follows the expected syntax of:

SELECT AVG(column_name)
FROM table_name
WHERE optional_where_clause;

For example, to determine the average salary of employees from the EMPLOYEES table, you would use:

SELECT AVG(EMP_SALARY)
FROM EMPLOYEES;

That SQL code would yield:

AVG (SALARY)
------------
       90000
------------

Joins

Up till now we have focussed on grabbing data from a single table in the database. However, the true power of the relational database comes from the fact that data is stored in multiple tables that are related by values within them. Thus, performing a uni-table SELECT is only the most trivial of operations afforded by SQL.

The true power of the SQL database lies in its ability to create views based upon the data in multiple tables using the JOIN operation.

To create a JOIN, you simply define two or more tables in your SELECT-FROM statement. For example, consider the following which joins the CLIENTS and SALES tables into a single view:

SELECT C_NAME, 
       S_NUM, 
       S_QUANTITY
FROM SALES, CLIENTS;

The previous SQL code would yield the following results:

C_NAME           S_NUM S_QUANTITY
---------------------------------
Jason Lim          001          1
Rick Tan           001          1
Stephen Petersen   001          1
Jason Lim          002          2
Rick Tan           002          2
Stephen Petersen   002          2
Jason Lim          003          1
Rick Tan           003          1
Stephen Petersen   003          1
---------------------------------

Notice that the results will contain every combination of the two tables.

But what happens if two tables share the same name? SQL assigns name space by specifying that when two column names are the same that you should specify the table name along with the column name with a period in between. Thus, consider the following example in which column C_NUM is shared by both the CLIENTS and the SALES tables.

SELECT C_NAME, 
       S_NUM, 
       SALES.C_NUM
FROM SALES, CLIENTS;

In this case, SQL will draw the data from the C_NUM column in the SALES table and not from the CLIENTS table. Being able to specify columns exactly is important because it helps us sculpt complex and more useful joins using the WHERE clause.

As you saw above, a raw JOIN returns perhaps too much data. For example, you might want to see unique sales information tied to the name of the client. In this case, you would use the WHERE clause to limit the results as follows:

SELECT C_NAME, 
       S_NUM, 
       S_AMOUNT
FROM SALES, CLIENTS
WHERE CLIENTS.C_NUM = SALES.C_NUM;

C_NAME            S_NUM  S_AMOUNT
---------------------------------
Rick Tan            001     99.99
Jason Lim           002    199.98
Stephen Petersen    003    865.99
---------------------------------

Note that all of the operators for SELECT can work in JOINS including LIKE/NOT LIKE, IN/NOT IN, NULL/NOT NULL, AND/OR/NOT, COUNT, AVG, etc....

Subqueries

As we have already discussed, relational databases are table based. That is, all SQL commands are applied to the contents of tables and then those results are displayed as tables themselves. One great consequence of that is that it is fairly easy to perform queries upon the virtual tables created by other queries. Or, in jargonese, to perform subqueries.

Consider this example in which we want to get a listing of all the clients who made a transaction of greater than $150.00.

SELECT DISTINCT C_NAME, C_PHONE
FROM CLIENTS
WHERE EMP_NUM IN
   (
   SELECT C_NUM
   FROM SALES
   WHERE S_AMOUNT > 150.00
   );

We would expect the following results:

C_NAME             C_PHONE
--------------------------
Jason Lim         456-7890
Stephen Petersen  167-3333
--------------------------

As you can see, the subquery in the WHERE clause would return a set containing "101" and "103". Next, the top-level query would return the two clients. In this case, the DISTINCT operator was not necessary since we don't have many sales in our SALES table. However, for most subqueries, it is useful to include the distinct so that you do not get a client repeated for multiple sales. Of course, you might want that. :)

Adding Data

Granted, most of the work you will be doing with web databases will probably be viewing. For that, the SELECT-FROM-WHERE statements will be perfect for you. However, in many cases you will also want browsers to be able to modify your database. For example, perhaps you want them to be able to add themselves to a mailing list.

The most basic database manipulation is the INSERT operation that adds a new row to a specified table. The INSERT operation follows the basic syntax of:

INSERT INTO 
   table_name (column_names)
VALUES (values);

For example, we might want to add a new EMPLOYEE using:

INSERT INTO EMPLOYEES 
  (EMP_NUM, EMP_NAME, 
   EMP_COMMISSION, EMP_SALARY)
VALUES 
   (004, 'Lee Eng Lock', 
   '15%', 65000);

Of course, since we are assigning values for each column, we need not specify the column names. Thus, the following statement would work as well:

INSERT INTO EMPLOYEES 
VALUES 
(004, 'Lee Eng Lock', '15%', 65000);

We now have:

EMPLOYEES Table
EMP_NUM EMP_NAME EMP_COMMISSION EMP_SALARY
001 Lim Li Chuen 10% 90000
002 Lim Sing Yuen 20% 40000
003 Loo Soon Keat 20% 50000
004 Lee Eng Lock 15% 65000

Note that when we perform an INSERT, the incoming value must correspond to the data type defined for that column. Thus, CHARACTER strings must be enclosed in single quotes. Also note that if a row is inserted and some of the columns are not defined, the column will be filled with NULL. Finally, note that the order in which you specify columns and values does not matter, but if you change the order, you must specify column names.

It is also useful to remember that there is no concept of outside order to an SQL table. That is, you cannot know which row will be stored first or last in a table. Thus, you should be careful when assigning things such as unique ordered row id numbers.

Finally, know that you can easily include a SELECT-FROM-WHERE statement as the VALUE such as:

CREATE TABLE NEW_TABLE
   (EMP_NAME CHAR (20),
    CLIENT_NUM SMALLINT,
    PRODUCT_NUM SMALLINT);

INSERT INTO NEW_TABLE
   (EMP_NAME, CLIENT_NUM, PRODUCT_NUM)
   SELECT EMPLOYEES.EMP_NAME,
          SALES.C_NUM,
          SALES.P_NUM
   FROM EMPLOYEES, SALES
   WHERE SALES.E_NUM = EMPLOYEES.EMP_NUM;

The resulting table would look like:

NEW_TABLE Table
EMP_NAME CLIENT_NUM PRODUCT_NUM
Lim Li Chuen 002 001
Lim Sing Yuen 001 001
Lim Li Chuen 003 002

Modifying Data

It is also easy to modify an existing row using SQL. To do so, you will use the UPDATE operator which follows the basic format of:

UPDATE table_name
SET column_name = some_value
WHERE where_clause;

For example, if we wanted to change the EMPLOYEES table such that Lim Sing Yuen received a $5,000 raise we would say:

UPDATE EMPLOYEES
SET EMP_SALARY = (EMP_SALARY + 5000)
WHERE EMP_NAME = 'Lim Sing Yuen';

or more directly:

UPDATE EMPLOYEES
SET EMP_SALARY = 45000
WHERE EMP_NAME = 'Lim Sing Yuen';

Note also that you can easily modify more than

one column at once in the SET clause. For example, you might say:


UPDATE EMPLOYEES
SET EMP_SALARY = 50000,
    EMP_COMMISSION = '15%'
WHERE EMP_NAME = 'Lim Sing Yuen';

You should also be aware, that if you did not specify a WHERE clause, then the operation affects all rows in the table. Thus the following code would give every employee a $5000 raise:

UPDATE EMPLOYEES
SET EMP_SALARY = (EMP_SALARY + 5000);

Finally, note that the WHERE clause can be as complex as needed, including subqueries if desired. Consider this example in which we give raises to only those employees who make less than the average salary:

UPDATE EMPLOYEES
SET EMP_SALARY = (EMP_SALARY + 1000);
WHERE EMP_SALARY <
   (SELECT AVG(EMP_SALARY)
    FROM EMPLOYEES);

Delete

Finally, SQL provides the DELETE operator for deleting rows from a table. The DELETE operator follows the syntax of:

DELETE FROM table_name
WHERE where_clause;

For example, to delete Lim Li Chuen from the EMPLOYEES table, you would use:

DELETE FROM EMPLOYEES
WHERE EMP_NAME = 'Lim Li Chuen';

Note that multiple rows may be deleted if they match the where clause. Note also that you can easily delete all rows in a table by not specifying a WHERE clause such as:

DELETE FROM EMPLOYEES

Obviously you should be quite careful with DELETE!

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved