1 / 24

MIS 5113

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.

terence
Download Presentation

MIS 5113

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. MIS 5113 Introduction to SQL Structured Query Language

  2. 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

  3. 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

  4. A simplified schematic of a typical SQL environment

  5. 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.

  6. 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

  7. 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

  8. Other DDL commands • Drop table • Alter table • create index • drop index • Create view • Drop view • Create schema

  9. DML options • Insert • Delete • Update • Select/From/WhereFocus on Retrieval using SELECT

  10. Simple Select Retrieve the entire Team table. SELECT * FROM TEAM; Produces the following result: 12 Dodgers Los Angeles Wilson 15 Giants San Francisco Johnson 20 Yankees New York Simpson 24 Tigers Detroit Corbin TeamNum Teamname City Coach

  11. 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

  12. Conditional Select (Restrict) Retrieve the record for Team 20. SELECT * FROM TEAM WHERE TEAMNUM=20; Would produce the following result: 20 Yankees New York Simpson TeamNum Teamname City Coach

  13. Operators for Conditional Statements • = • AND, OR, NOT • <, >, • <=, >=, <> • *, / (numeric comparisons) • *, %, _ (string comparisons)

  14. 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; 1131 Johnson 5410 Smith 8366 Gomez PlayNum Playname Age Position

  15. 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

  16. Functions • COUNT • MIN • MAX • SUM • AVG

  17. COUNT • SELECT COUNT(*) FROM Order_Line WHERE Order_Num=1004; • SELECT COUNT(Prod_Desc) from Product; ORDER_LINE Order_NumProd_ID Quantity PRODUCT Prod_ID Prod_Desc Prod_Price

  18. 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

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

  20. 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

  21. In Which teams are in New York or Detroit? SELECT TEAMNUM FROM TEAM WHERE CITY IN (‘New York’, ‘Detroit’); 20 24

  22. 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

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

  24. SQL Exercise • Go to Lab • Sign into MS SQL Server • Work problems 1 - 10 in Pratt, page 70 - 71

More Related