Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Management Information Systems, 13TH ED.
MANAGING THE DIGITAL FIRM
Kenneth C. Laudon ● Jane P. Laudon
Learning Track 2: Introduction to SQL
fiis Hands-On Guide is a brief introduction to Structured Query Language (SQL), the standard
language for relational database management systems. SQL can be used for defining database
structures, querying databases, and updating database data. If you are using Access, you will prob-
CUSTOMER TABLE WITH SAMPLE DATA
CUST
ID FNAME
MID
NAME LNAME ADDR1 ADR2 CITY STATE ZIP HOME PHONE
WORK
PHONE CELL PHONE EMAIL DOB SEX
PERSONAL
INFO
OLD
CUST ID
1 Georgia A Brown 88 Main Street Missoula NY 10521 (914) 332-1234 (914) 222-8182 (912) 455-5555 gbrown@qqq.org 7/7/1951 F WORKS AT M8
2 Patrick Malone 42 Sweetbriar L Bocarain NY 10598 (914) 333-4333 (211) 222-2121 PMAL@AOL.COM 6/12/1981 M
3 Juliet Q Montague 11 Venice Plaza Apt. 22 Bocarain NY 10598 (914) 888-8181 (212) 921-0404 (212) 222-1221 12/25/1971 F
4 Mary
Smith Marian Lane Catalan NY 10520 (914) 821-3666 1/23/1959 F
Chapter 6: Foundations of Business Intelligence: Databases and
Information Management
Chapter 6 Learning Track 2 2
APPOINTMENT TABLE WITH SAMPLE DATA
CUSTID SERVICE_ID APPT_DATE APPT_TIME EMP_SS PROD_NUM APPT_COMMENT
1 2 11/5/2013 3:30 PM 123456789
1 2 11/7/2013 2:00 PM 123456789
5 3 11/5/2013 12:00 PM 123456789 3
5 3 11/5/2013 9:00 AM 123456789 2
6 5 11/5/2013 11:30 AM 987654321
Basic SQL Syntax Rules
SQL, like all computer languages has a particular syntax or grammar which must be followed in
order for the commands to be understood and executed properly. Its most basic rules are:
1. Identifiers (names of tables, columns, and other objects) should contain between 1 and 30
3. SQL statements can take up more than one line (and there are no restrictions on the number of
4. Commands begin with the SQL operator (e.g., CREATE or SELECT).
6. Field (column) names are separated from table names by a period (e.g., tblCUSTOMER.
FNAME, tblCUSTOMER.LNAME). (Access uses this convention.) fie name of the table
Chapter 6 Learning Track 2 3
the CUSTOMER and APPOINTMENT tables). fien the table name must be included (e.g.,
tblCUSTOMER.CUSTID and tblAPPOINTMENT.CUSTID).
7. Strings must be enclosed in quotation marks. A string is text for a collection of bits that is
foreign to the database. fie SQL statement to find the phone number for all of the customers
whose last name is MacGregor would be written as:
8. Nested operations must be enclosed in parentheses. One of the strengths of SQL is its ability
Using the Select Statement
fie most often used SQL command is SELECT, which returns rows of data from columns in the
tables that the user would like to see in a result table. Following are a series of SELECT statements
which demonstrate how to have all or specified columns returned in the result table. fie names of
the columns to be queried follow the keyword SELECT and the name of the table to use follows the
keyword FROM.
SELECT every column and every row
SELECT only some columns and every row
Chapter 6 Learning Track 2 4
continued
ADDR1 LNAME
88 Main Street Brown
42 Sweetbriar Lane Malone
11 Venice Plaza Montague
Sorting Data: Using Order By
fie following examples show the ORDER BY statement which is used to sort the data returned by
the SELECT statement.
Select only some columns and every row in sorted order by one field
fie ORDER BY command causes the records to be sorted in order by the contents of the column
name following it.
LNAME FNAME
Brown Jim
Brown Thomas
Brown Georgia
Greene Jeanne
Chapter 6 Learning Track 2 5
continued
Select only some columns and every row in sorted order by several fields
fie returned records may be sorted in order by several fields. Each listed field after the ORDER BY
clause is separated by a comma. First the records are sorted by last name, and if the last names are
the same they are sorted by first name.
ORDER BY LNAME, FNAME;
LNAME FNAME
Brown Georgia
Brown Jim
Brown Thomas
Selecting Only Records That Meet Specified Criteria:
Using Where
fie WHERE clause determines which records are returned based on criteria described in the
clause. It includes con ditions for selecting specific rows in a table. Operators, such as = (equal), >
Select only records where the field SEX contains an F in sorted order by last name and first name.
SELECT LNAME, FNAME, SEX
Chapter 6 Learning Track 2 6
continued
LNAME FNAME SEX
Brown Georgia F
Montague Juliet F
Select only the records where SEX does not contain an F in sorted order by last name and first name
SELECT FNAME, LNAME, SEX
FNAME LNAME SEX
Jim Brown M
Select only the records where SEX contains an F and the zip code is 10520 in sorted order by last
name and first name.
fie logical operator AND is used to select information that is contained within two dierent
fields. Multiple AND and OR operators can be contained within one WHERE clause.
fie zip code 10520 is enclosed in quotes because it is a string. fie ZIP code field is a text field and
Chapter 6 Learning Track 2 7
Joining Multiple Tables
Data from two or more tables can be joined together to provide data from both tables where the
contents of a field found in both tables matches. Both tblCUSTOMER and tblAPPOINTMENT are
needed to see who has an appointment on a particular date. Since both tables contain the custom-
er’s ID, the contents of this field must match for the records to be returned.
Select the appointment date, time, and last name of all male customers with appointments.
SELECT APPT_DATE, APPT_TIME, LNAME
fie preceding SQL syntax that works with most DBMS products. Access, however, uses a slightly
dierent syntax for this particular operation. It would be:
SELECT tblAPPOINTMENT.APPT_DATE,
APPT_DATE APPT_TIME LNAME
11/5/2004 8:30 AM Malone
