Databases Reference
In-Depth Information
This gives you the following result:
PRODNUM
VALUE
16386
647.50
19440
175.00
19441
337.40
21765
329.90
21766
491.35
24013
367.50
26722
34.50
This gives you the calculated value based on the quantity on hand times the unit
price. The resulting column is named VALUE, as specified in the query.
To limit this to returning just the first three rows, you could run the
following:
SELECT TOP 3 PRODNUM, (QOH * UNITPRICE) AS [VALUE]
FROM PRODUCT
Limiting the returned rows usually makes more sense if you are also ordering
the result. You'll learn how to do that a little later in this chapter.
What if you want a more permanent record of the result? You can use the
INTO keyword to write the result to a table. For example:
SELECT PRODNUM, (QOH * UNITPRICE) AS [VALUE]
INTO PRODVAL FROM PRODUCT
This creates the table PRODVAL in the default database (GeneralHardware).
The table has two columns, PRODNUM and VALUE, and the results you saw
earlier as the table rows. When using INTO, the destination table cannot
already exist. If you attempted to run the same statement again, you'd get the
following error:
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'PRODVAL' in the
database.
Keep in mind that error text and error message numbers are DBMS-specific.
This is the error that you would see using SQL Server 2005 if you specify a table
name that already exists. If you do want to recreate the same table, you must
drop the table before running the query.
Next, let's take a quick look at the DISTINCT keyword. Suppose you want
to find out which salespersons currently have one or more customers assigned.
To do so, you might run a statement like the following:
SELECT SPNUM FROM CUSTOMER
Using the data in our sample database, the result you get is:
