Published on September 28, 2007
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)
Enroll for OS and Databases certification trainings through Simplilearn.com. Get access to our OS and Databases practice tests and webinars to help you ...