Transcript SQL Overview

MIS 5113
Introduction to SQL
Structured Query
Language
Possible Benefits of a Standard
Relational Language
•
•
•
•
•
Reduce training costs
Increase Productivity
Increase application portability
Increase application life
Reduce dependence on a single
vendor
• Allow cross-system communication
Possible Disadvantages of a
standard relational language
• Difficult to change, inhibits
innovation, new features
• Never enough to meet all needs
• Contains compromises
• Vendor-added features result in a
loss of portability
A simplified schematic of a typical
SQL environment
Terminology
• Data Definition Language (DDL):
– Commands that define a database,
including creating, altering, and dropping
tables and establishing constraints.
• Data Manipulation Language (DML)
– Commands that maintain and query a
database.
• Data Control Language (DCL)
– Commands that control a database,
including administering privileges and
committing data.
Data Definition Language (DDL)
•
•
•
•
•
•
Identify appropriate datatypes
Identify columns that should allow null values
Identify columns that need to be unique
Identify all PK/FK mates
Determine any default values to be inserted
Identify columns which need a domain
specification
• Create the table
DDL example in SQL
CREATE TABLE ORDER
(Order_Id
char
not null,
Order_Date
date
default sysdate,
Customer_Id char
not null,
Constraint Order_pk primary key (order_Id) ,
Constraint Order_fk foreign key (Customer_Id
references Customer(Customer_Id));
Order_ID
Cust_ID
Customer
Order
Order_Date
Other DDL commands
•
•
•
•
•
•
•
Drop table
Alter table
create index
drop index
Create view
Drop view
Create schema
DML options
•
•
•
•
Insert
Delete
Update
Select/From/Where
Focus on Retrieval using SELECT
Simple Select
Retrieve the entire Team table.
SELECT * FROM TEAM;
TeamNum Teamname City Coach
Produces the following result:
12 Dodgers Los Angeles Wilson
15 Giants
San Francisco Johnson
20 Yankees New York
Simpson
24 Tigers Detroit
Corbin
Select for specific Attributes
Find the numbers and names of all of
the teams.
SELECT TEAMNUM, TEAMNAME
FROM TEAM;
12 Dodgers
15 Giants
20 Yankees
24 Tigers
Conditional Select (Restrict)
Retrieve the record for Team 20.
SELECT *
FROM TEAM
WHERE TEAMNUM=20;
TeamNum Teamname City Coach
Would produce the following result:
20 Yankees New York Simpson
Operators for Conditional
Statements
•
•
•
•
•
•
=
AND, OR, NOT
<, >,
<=, >=, <>
*, / (numeric comparisons)
*, %, _ (string comparisons)
Combination of Conditions
Which players, over 27 years old, have player
numbers of at least 1000?
SELECT PLAYNUM, PLAYNAME FROM
PLAYER WHERE AGE>27 AND
PLAYNUM>=1000;
PlayNum
Playname
1131 Johnson
5410 Smith
8366 Gomez
Age Position
ANDs and ORs
Which players are over 30 years old or
are less than 22 years old and have a
player number less than 2000?
SELECT * FROM PLAYER WHERE AGE>30
OR (AGE<22 AND PLAYNUM<2000);
358 Stevens 21
523 Doe
32
8366 Gomez 33
Functions
•
•
•
•
•
COUNT
MIN
MAX
SUM
AVG
COUNT
• SELECT COUNT(*) FROM Order_Line
WHERE Order_Num=1004;
• SELECT COUNT(Prod_Desc) from
Product;
ORDER_LINE
Order_Num Prod_ID Quantity
PRODUCT
Prod_ID Prod_Desc Prod_Price
MIN and MAX
SELECT MIN(Prod_Price) FROM Product
SELECT Prod_Id, Prod_Desc FROM Product
WHERE Prod_Price=
(SELECT MAX(Prod_Price) FROM
Product);
PRODUCT
Prod_ID Prod_Desc Prod_Price
String Comparison
Which teams are based in Detroit?
SELECT TEAMNUM, TEAMNAME
FROM TEAM
WHERE CITY=‘Detroit’;
24 Tigers
Between
Which players are between 25 and
27 years old?
SELECT PLAYNUM, PLAYNAME
FROM PLAYER WHERE AGE BETWEEN 25
AND 27;
1779 Jones
2007 Dobbs
4280 Cohen
5410 Smith
In
Which teams are in New York or
Detroit?
SELECT TEAMNUM
FROM TEAM
WHERE CITY IN (‘New York’, ‘Detroit’);
20
24
Like
Find all of the players whose last
names begin with “S”.
SELECT PLAYNUM, PLAYNAME
FROM PLAYER
WHERE PLAYNAME LIKE ‘S%’;
358 Stevens
5410 Smith
8093 Smith
Distinct
List the names of the companies that
manufacture bats for the league.
SELECT DISTINCT MANUF
FROM BAT;
Acme
General
United
Modern
SQL Exercise
• Go to Lab
• Sign into MS SQL Server
• Work problems 1 - 10 in Pratt, page
70 - 71