PPA724 queries

Queries and SQL: 

Queries and SQL Professor Jon P. Gant Syracuse University

Benefits of a Standardized Relational Language: 

Benefits of a Standardized Relational Language Reduced training costs. Productivity. Application portability. Application longevity. Reduced dependence on a single vendor. Cross-system communication.

Terminology: 

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.

Slide4: 

A simplified schematic of a typical SQL environment, as described by the SQL-92 standard

Simple Select: 

Simple Select Retrieve the record for Team 20. SELECT * FROM TEAMS WHERE TEAMNUM=20; 20 Yankees New York Simpson

Retrieve Table: 

Retrieve Table Retrieve the entire Teams table. SELECT * FROM TEAMS; 12 Dodgers Los Angeles Wilson 15 Giants San Francisco Johnson 20 Yankees New York Simpson 24 Tigers Detroit Corbin

Simple Project: 

Simple Project Find the numbers and names of all of the teams. SELECT TEAMNUM, TEAMNAME FROM TEAMS; 12 Dodgers 15 Giants 20 Yankees 24 Tigers

Combination of Conditions: 

Combination of Conditions Which players, over 27 years old, have player numbers of at least 1000? SELECT PLAYNUM, PLAYNAME FROM PLAYERS WHERE AGE>27 AND PLAYNUM>=1000; 1131 Johnson 5410 Smith 8366 Gomez

ANDs and ORs: 

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 PLAYERS WHERE AGE>30 OR (AGE<22 AND PLAYNUM<2000); 358 Stevens 21 523 Doe 32 8366 Gomez 33

String Comparison: 

String Comparison Which teams are based in Detroit? SELECT TEAMNUM, TEAMNAME FROM TEAMS WHERE TEAMNAME=‘Detroit’; 24 Tigers

Between: 

Between Which players are between 25 and 27 years old? SELECT PLAYNUM, PLAYNAME FROM PLAYERS WHERE AGE BETWEEN 25 AND 27; 1779 Jones 2007 Dobbs 4280 Cohen 5410 Smith

In: 

In Which teams are in New York or Detroit? SELECT TEAMNUM FROM TEAMS WHERE TEAMCITY IN (‘New York’, ‘Detroit’); 20 24

Like: 

Like Find all of the players whose last names begin with “S”. SELECT PLAYNUM, PLAYNAME FROM PLAYERS WHERE PLAYNAME LIKE ‘S%’; 358 Stevens 5410 Smith 8093 Smith

Distinct: 

Distinct List the names of the companies that manufacture bats for the league. SELECT DISTINCT MANUF FROM BATS; Acme General United Modern

Slide15: 

DDL, DML, DCL, and the database development process

Slide16: 

SQL statement processing order (adapted from van der Lans, p.100)