Transcript IMS 3005 Course Introduction - University of Central Florida

IMS 4212: Intro to SQL
Introduction to SQL—Topics
•
•
•
•
Introduction to SQL
SQL & Programming
Categories of SQL Statements
The SELECT Query
– Limiting columns
– Limiting rows with WHERE
– Sorting (ordering) results
– Renaming the output columns with AS
– Computed columns
– Functions in SQL
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
1
IMS 4212: Intro to SQL
What is SQL?
• SQL stands for "Structured Query Language" and is a
language for manipulating relational databases
• Pronounced "S"-"Q"-"L" or "SEQUEL"
• ANSI SQL is the 'base' version of SQL
• Each manufacturer has their own dialect of SQL
• Most differences are in special purpose corners of the
language
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
2
IMS 4212: Intro to SQL
What is SQL (cont.)
• SQL is not a programming language
– SQL comes with the database
– Database engine interprets SQL statements, acts on
them, and (if pertinent) returns a result
• SQL can be entered directly in most DBMS
• SQL can be embedded in a programming language and
passed to the database engine
• We will learn SQL and then use it in our applications
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
3
IMS 4212: Intro to SQL
SQL and Programming
• SQL represents a division of labor between the
database and the program you write
– You create the interface for input and output
– You translate user needs into SQL 'behind the scenes'
and pass SQL to the DBMS
– DBMS executes commands
– You take results (including errors!) and display them to
the user through your interface
• SQL can contain complex procedural code in addition
to data manipulation statements
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
4
IMS 4212: Intro to SQL
SQL & Programming (cont.)
• You will only rarely enter SQL directly at the DBMS
level except for instructional needs and testing
– Most have front ends for creating and modifying
database structures
– In large systems the database administrator (DBA) will
do the database structure creation
• You will constantly use SQL in programs, though and
need to be expert in its use
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
5
IMS 4212: Intro to SQL
SQL & Programming (cont.)
• SQL follows an 80/20 rule
– You will use 20% of the language to do 80% of the
necessary tasks
– You need to be aware of what exists in the other 80%
of the language
• Sometimes you're going to have one of those 20%
tasks
• Many employers ask about SQL skills in interviews
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
6
IMS 4212: Intro to SQL
Categories of SQL Statements
• SQL statements are divided into two groups
– Data definition language: Create and modify the
structure of the database
• Tables
• Relationships
• Rules
• Indices
– Data manipulation language
• Add and delete records
• Retrieve records
• Update records
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
7
IMS 4212: Intro to SQL
Categories of SQL Statements (cont)
• Data Definition Language
– CREATE TABLE statement
– CREATE INDEX statement
– ALTER TABLE statement
– CONSTRAINT clauses
– DROP statement
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
8
IMS 4212: Intro to SQL
Categories of SQL Statements (cont.)
• Data Manipulation Language
– SELECT statement
– INSERT INTO statement
– UPDATE statement
– DELETE statement
– JOIN operations
• INNER JOIN, LEFT JOIN, RIGHT JOIN, OUTER
JOIN
– WHERE, ORDER BY, & HAVING clauses
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
9
IMS 4212: Intro to SQL
The SELECT Statement (Query)
• SELECT provides the mechanism for retrieving data
from the database
– Can assemble data together from multiple tables into
logical records
– Can limit which records you retrieve
– Can limit which fields you retrieve
– Can perform calculations and display the results along
with 'raw' data values
• SELECT will be a workhorse in your database
programming and you should understand it thoroughly
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
10
IMS 4212: Intro to SQL
Introduction To the Query Editor
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
11
IMS 4212: Intro to SQL
SELECT Statement—Simple Example
SELECT SQL keyword
Return all fields (columns)
SELECT *
FROM Suppliers;
FROM SQL keyword indicates
tables involved in the query
Note that SQL
keywords are
in all capital letters
Indicates only Suppliers
table is used
This query returns all fields from all records in the Suppliers
table
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
12
IMS 4212: Intro to SQL
SELECT Statement—Exercise
• Change active database to
NorthWind
• Enter the query below and
click Execute or press "F5"
SELECT *
FROM Products
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
13
IMS 4212: Intro to SQL
Query Results Pane
Editing
window
Results
Pane
Records
returned
(Ctrl-R will eliminate the results pane
after a query has run)
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
14
IMS 4212: Intro to SQL
The SELECT Clause—Limiting Output Columns
SELECT CompanyName, Phone, Fax
FROM Suppliers;
• SELECT keyword may be followed by a list of fields or
other expressions
• Only these fields will be returned in the result set!
• Fields may be specified in any order (very important)
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
15
IMS 4212: Intro to SQL
Exercises
• Enter and run the following queries
SELECT ProductID, ProductName, UnitsInStock
FROM Products
SELECT ProductName, ProductID, UnitsInStock
FROM Products
• Write the query to return just Customer company
names and countries
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
16
IMS 4212: Intro to SQL
The WHERE Clause—Limiting Records
SELECT CompanyName, Phone, Fax
FROM Suppliers
WHERE Country = 'USA';
• The WHERE clause establishes a condition that can be
tested to be either True or False for any record
– Usually tests value of one or more fields
• Only the records whose values are TRUE for the
specified WHERE clause criteria are returned in the
result set
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
17
IMS 4212: Intro to SQL
The WHERE Clause—Limiting Records (cont.)
• String and Date literal values must be delimited
– SQL Server uses single quotes as delimiters for all data
types needing delimiters
– Some databases use different delimiters
• String values in single or double quotes
• Date values in pound signs (#) or single quotes
• Numeric literals do not use delimiters
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
18
IMS 4212: Intro to SQL
The WHERE Clause—Compound Tests
• The WHERE Clause can contain complex tests
• Entire test must evaluate to True for record to be
displayed
SELECT *
• Use logical operators FROM Suppliers
– NOT
WHERE Country = 'USA'
OR Country = 'Germany';
– AND or OR
– Parenthetical grouping
– <> (notequal), =, <, <=, >, >=
• Follows the same precedence rules as compound logical
tests in VB programming
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
19
IMS 4212: Intro to SQL
Exercises
• List all of the customers in Germany
• List all of the products where the units in stock are less
than the reorder point
• List all products in Category 2
• List all products in Category 2 where the units in stock
is less than 20 but do not list the product if it has been
discontinued (Discontinued = 1 indicates the product
has been discontinued)
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
20
IMS 4212: Intro to SQL
Some Interesting Tricks
• Run each of these queries:
SELECT 4
SELECT 4, 'Test Value', ProductName
FROM Products
SELECT *
FROM Products
WHERE 4 = 4
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
21
IMS 4212: Intro to SQL
Reordering Query Output with ORDER BY
• Run these four queries:
SELECT ProductID, ProductName, UnitPrice
FROM Products
SELECT ProductID, ProductName, UnitPrice
FROM Products
ORDER BY ProductName
SELECT ProductID, ProductName, UnitPrice
FROM Products
ORDER BY ProductName DESC
SELECT ProductID, ProductName, UnitPrice
FROM Products
ORDER BY SupplierID
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
22
IMS 4212: Intro to SQL
Reordering Query Output with ORDER BY (cont)
SELECT ProductID, ProductName, SupplierID
FROM Products
SELECT ProductID, ProductName, SupplierID
FROM Products
ORDER BY SupplierID
SELECT ProductID, ProductName, SupplierID
FROM Products
ORDER BY SupplierID, ProductName
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
23
IMS 4212: Intro to SQL
Aliasing Column Names with AS
SELECT CustomerID, CompanyName
FROM Customers
SELECT CustomerID, CompanyName AS 'Company Name'
FROM Customers
SELECT CustomerID, CompanyName AS Company
FROM Customers
SELECT CustomerID, CompanyName AS Company Name
FROM Customers
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Name'.
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
24
IMS 4212: Intro to SQL
Calculated Columns
SELECT ProductID, ProductName,
UnitsInStock * UnitPrice AS 'Value'
FROM Products
ORDER BY Value
SELECT LastName, FirstName,
LastName + ', ' + FirstName AS 'Employee Name'
FROM Employees
Always provide a column name to a calculated column with
the AS expression
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
25
IMS 4212: Intro to SQL
Functions
• SQL Server has many intrinsic
functions that may be used in
SQL statements
• We will be mixing these in
with examples throughout the
lab
• Browsing these on your own
can pay off for you
• Pay special attention to date
and time functions!!!
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
26
IMS 4212: Intro to SQL
Some Date Functions in Action
SELECT GetDate()
SELECT DatePart(mm, GetDate())
SELECT DateName(mm, GetDate())
SELECT OrderID, OrderDate, ShippedDate,
DateDiff(dd, OrderDate, ShippedDate)
AS 'Fulfillment Time'
FROM Orders
WHERE ShippedDate IS NOT NULL
• List all of the order information where the orders took
more than five days to ship
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
27
IMS 4212: Intro to SQL
Commenting SQL Statements
Block comment
CREATE PROCEDURE [dbo].[up_Order_Invoice_OrderInfo]
/**********************************************************
SP: up_Order_Invoice
Takes an OrderID as a parameter and returns all data needed
to create an invoice for the order except order details
/* … */
Must work with the SP up_Order_Invoice_Details to create
all order invoice information.
Created by Dr. Larry West, MIS Department, UCF
Last Modified: 9 January 2007
**********************************************************/
--Create parameter
@OrderID int
AS
--Execute the query
SELECT CUSTOMERS.*, ORDERS.*,
EMPLOYEES.LastName, EMPLOYEES.FirstName
FROM CUSTOMERS, ORDERS,
EMPLOYEES
WHERE ORDERS.OrderID = @OrderID
AND ORDERS.CustomerID = CUSTOMERS.CustomerID
AND ORDERS.EmployeeID = EMPLOYEES.EmployeeID
Dr. Lawrence West, Management Dept., University of Central Florida
[email protected]
Single line comment
-- (two hyphens)
28