MIS 5113 Introduction to SQL Structured Query Language

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 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 Customer 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; Team. Num 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; Team. Num 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; Play. Num 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