1.

264 Lecture 8

SQL: Basics, SELECT

 SQL

• Structured query language (SQL) used for

– Data definition (DDL): tables and views (virtual tables)
– Data manipulation (DML): user or program can INSERT,
DELETE, UPDATE or retrieve (SELECT) data
– Access control: security
– Data sharing: by concurrent users
– Data integrity: referential integrity and transactions
• Not a complete language like Java, Visual Basic or C++

– SQL is sub­language of about 30 statements

– Usually embedded in another language or tool for database 
access
– SQL has several inconsistencies; NULLs are problematic
– Portable across operating systems and somewhat among
vendors
 Things that vary among SQL

implementations

• Error codes
• Data types supported (dates/times, currency, string
variations)
• System tables, about the structure of the database
itself
• Interactive SQL
• Programming interface: no vendor follows the
standard
• Dynamic SQL, used for report writers and query
tools
• Implementer­defined variations within the standard

• Database initialization, opening and connection

 SQL SELECT

• SELECT constructed of clauses to get

columns and rows from one or more
tables or views. Clauses must be in order:
– SELECT columns
– INTO new table
– FROM table or view
– WHERE specific rows or a join is created
– GROUP BY grouping conditions (columns)

– HAVING group­property (specific rows)

– ORDER BY ordering criterion ASC | DESC

 Example tables

OrderNbr Cust Prod Qty Amt Disc

1 211 Bulldozer 7 $31,000.00 0.2
Orders
2 522 Riveter 2 $4,000.00 0.3
3 522 Crane 1 $500,000.00 0.4

CustNbr Company CustRep CreditLimit

211 Connor Co 89 $50,000.00
Customers
522 AmaratungaEnterprise 89 $40,000.00
890 Feni Fabricators 53 $1,000,000.00

RepNbr Name RepOffice Quota Sales

SalesReps
53 Bill Smith 1 $100,000.00 $0.00
89 Jen Jones 2 $50,000.00 $130,000.00
Offices

OfficeNbr City State Region Target Sales Phone

1 Denver CO West $3,000,000.00 $130,000.00 970.586.3341
2 New York NY East $200,000.00 $300,000.00 212.942.5574
57 Dallas TX West $0.00 $0.00 214.781.5342
Example schema
 Using SQL Server and Management Studio

Express

• Your SQL Server database engine should start by

default when your system starts
– Ask TAs for help if needed
• Start Management Studio Express (MSE) from
Start­>Programs
• Open Lecture7OrderDB.sql with MSE in Windows
Explorer
– Download the .sql file from the MIT Server
• Select ‘Execute’ from toolbar
– Database should be created and data inserted for
exercises during this class
 SQL queries: SELECT

• Click ‘New Query’ in MSE; type these statements:

• List the sales reps

– SELECT Name, Sales, Quota FROM SalesReps;
• Find the amount each rep is over or under quota

– SELECT Name, Sales, Quota, (Sales­Quota) FROM

SalesReps;
• Find the slackers
– SELECT Name, Sales, Quota, (Sales­Quota) FROM
SalesReps WHERE Sales < Quota;
RepNbr Name RepOffice Quota Sales
53 Bill Smith 1 $100,000.00 $0.00
89 Jen Jones 2 $50,000.00 $130,000.00
 SQL queries: calculation, insert,

delete, update

• Find the average sale
– SELECT AVG(Amt) FROM Orders;
• Find the average sale for a customer
– SELECT AVG(Amt) FROM Orders WHERE Cust = 211;
• Add an office
– INSERT INTO Offices (OfficeNbr, City, State, Region, Target, Sales,
Phone) VALUES (‘55’, ‘Dallas’,‘TX’,‘West’, 200000, 0, ‘214.333.2222’);
• Delete a customer
– DELETE FROM Customers WHERE Company = ‘Connor Co’;
– (Syntax is valid but command will fail due to referential integrity)

• Raise a credit limit
– UPDATE Customers
SET CreditLimit = 75000 WHERE Company = ‘Amaratunga
Enterprises’;
 SELECT: * and duplicates

• Select all columns (fields)

– SELECT * FROM Offices;
• Duplicate rows: query will get two
instances of ‘West’
– SELECT Region FROM Offices;
• Eliminate duplicates:
– SELECT DISTINCT Region FROM Offices;
 NULLs

• NULL values evaluate to NOT TRUE in all cases.

– Insert ‘NewRep’ with NULL (blank or empty) Quota
– Write this statement yourself!
• The following two queries will not give all sales reps:

– SELECT Name FROM SalesReps WHERE Sales > Quota;

– SELECT Name FROM SalesReps WHERE Sales <= Quota;

– A new rep with a NULL quota will not appear in either list

• Check for NULLS by:

– SELECT Name FROM SalesReps WHERE Quota IS NULL;

 SELECT Operators

• SELECT * FROM <table>
– WHERE Disc*Amt > 50000; (Orders)

– WHERE Quota BETWEEN 50000 AND 100000; (SalesReps)

• Range is inclusive (>=50000 and <=100000)
– WHERE State IN (‘CO’, ‘UT’, ‘TX’); (Offices)

– WHERE RepNbr IS NOT NULL; (SalesReps)

– WHERE Phone NOT LIKE ‘21%’; (Offices)

• SQL standard only has 2 wildcards

• % any string of zero or more characters (* in Access)
• _ any single character (? in Access)
• Most databases have additional/different wildcards.
SQL Server has:
• [list]  match any single character in list, e.g., [a­f]
• [^list] match any single character not in list, e.g. [^h­m]
 SELECT: COUNT, GROUP BY

PartID Vendor

123 A

234 A

Parts
345 B

362 A

2345 C

3464 A

4533 C

• Number of parts from vendor A

– SELECT COUNT(*) FROM Parts WHERE Vendor = ‘A’;
– Result: 4
• Number of parts from each vendor
– SELECT Vendor, COUNT(*) AS PartsCount FROM Parts
GROUP BY Vendor;
– Result: Vendor PartsCount
A 4
B 1
C 2
 Exercises

• What is the average credit limit of

customers whose credit limit is less than
$1,000,000?
• How many sales offices are in the West
region?
• Increase the price of bulldozers by 30% in
all orders
• Delete any sales rep with a NULL quota
 Exercises

• What is the average credit limit of customers

whose credit limit is less than $1,000,000?

– SELECT AVG(CreditLimit) FROM Customers WHERE

CreditLimit < 1000000;
• How many sales offices are in the West region?
– SELECT Count(*) FROM Offices WHERE Region= 'West‘;
• Increase the price of bulldozers by 30% in all
orders
– UPDATE Orders SET Amt= Amt*1.3 WHERE Prod=
'Bulldozer‘;
• Delete any sales rep with a NULL quota
– DELETE FROM SalesReps WHERE Quota IS NULL;