1 / 61

Structured Query Language (SQL) Advanced Topics And How to build something useful

Structured Query Language (SQL) Advanced Topics And How to build something useful. MIS 304 Winter 2006. Goal for this class. Advanced SQL Commands Understand how to use Relational Technologies to solve “M to N” and other advanced problems.

nanji
Download Presentation

Structured Query Language (SQL) Advanced Topics And How to build something useful

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. Structured Query Language (SQL) Advanced Topics And How to build something useful MIS 304 Winter 2006

  2. Goal for this class • Advanced SQL Commands • Understand how to use Relational Technologies to solve “M to N” and other advanced problems. • Understand Relational Database Cost/Benefit analysis. • Be able to write SQL queries to solve advanced problems.

  3. Advanced SQL • SQL is at the same time simple and yet deceptively complex. • Worse, simple queries can run very slowly and complex one very quickly. • The trick to learning SQL is just to do it.

  4. Sub Queries • Anything enclosed in Parentheses ( ) is done first as a separate query. • It returns a result set that you can use in the “main” query. • Commonly used with the IN operator.

  5. IN • SELECT * FROM Product WHERE V_Code = 12345 OR V_Code = 12346; • SELECT * FROM Product WHERE V_Code IN (12345, 12346); • SELECT * FROM Product WHERE V_Code IN (SELECT V_CODE FROM VENDOR WHERE State = ‘MI’);

  6. Sub Query Hints • Sub Queries can be hard to use. • Develop the Sub Query first as a separate query. Make sure it works independently first. • Remember a Sub Query returns a ‘virtual’ table. • Keep sub queries to a minimum.

  7. SELECT DISTINCT • Listing Unique Values SELECT DISTINCT P_CODEFROM POLine;

  8. SELECT TOP* SELECT TOP n FROM nnn WHERE x=y Example: SELECT TOP 5 TotalPop FROM County ORDER BY TotalPop DESC;

  9. LIKE • Show all of the data for Products whose Description contains CD (i.e. CD, CD-ROM, CD-RW…) • SELECT * FROM ProductWHERE Description LIKE ‘CD%’; • % Multicharacter match (* in Access) • _ Single character match (? In Access)

  10. Soundex There is an “algorithm” that lets you search for words that sound alike. • Capitalize all letters in the word and drop all punctuation marks. Pad the word with rightmost blanks as needed during each procedure step. • Retain the first letter of the word. • Change all occurrence of the following letters to '0' (zero):  'A', E', 'I', 'O', 'U', 'H', 'W', 'Y'. • Change letters from the following sets into the digit given: • 1 = 'B', 'F', 'P', 'V' • 2 = 'C', 'G', 'J', 'K', 'Q', 'S', 'X', 'Z' • 3 = 'D','T' • 4 = 'L' • 5 = 'M','N' • 6 = 'R' • Remove all pairs of digits which occur beside each other from the string that resulted after step (4). • Remove all zeros from the string that results from step 5.0 (placed there in step 3) • Pad the string that resulted from step (6) with trailing zeros and return only the first four positions, which will be of the form <uppercase letter> <digit> <digit> <digit>.

  11. Converting Columns to Rows Test • Suppose you have a table that has Attributes for the various months and you now need to roll to another year. • SELECT Code, Jan FROM TestUNION ALL SELECT Code, Feb FROM TestUNION ALL SELECT Code, Mar FROM TestUNION ALL SELECT Code, Apr FROM Test;

  12. Example COLA COLB COLC COLD 1 2 3 4 5 6 7 8 Result COLX COLY A 1 A 5 B 2 B 6 C 3 C 7 D 4 D 8 SELECT 'A' as COLX, COLA as COLY from Example UNION ALL SELECT 'B' , COLB from Example UNION ALL SELECT 'C' , COLC from Example UNION ALL SELECT 'D' , COLD from Example order by COLX, COLY Another Example

  13. How to Build Something Useful

  14. The Issue • Businesses, Health Care facilities, Non-profits, the Military and Governmental agencies run on the movement of information. In other words… Paperwork

  15. The Business of IT • Very often you are given a bunch of seemingly unconnected “artifacts” and you need to make something out of them. • These can take the form of Databases, Files, Spreadsheets, Word Documents but most often pieces of paper.

  16. Paperwork • Modeling the structure and the flow of paper or now paper like objects is the nature of what IT people do. • Most of those pieces of paper end up as one or more tables in a relational database.

  17. Forms Examples • The Purchase Order • The Invoice • Intake logs • Test results • Etc…..

  18. Forms and Business Rules • Forms also encode many of our business rules • You may need to represent some of the business rules in your database. • Examples • Code sets • Numbering schemes • etc

  19. What is so difficult about this? • What are the “Facts” we find here?

  20. Facts about many things • Facts about the PO or Invoice itself • Facts about customers or suppliers • Facts about items sold or shipped • Facts about payment • Facts about shipping • Facts about business process • Misc. facts

  21. Lets build a model

  22. The Simplest VersionPhase 0 PurchaseOrder What is wrong with this approach?

  23. PO Table What are these facts about?

  24. PO Model phase 1 1 M Has POInfo Lines How much redundancy does this add?

  25. Results • You need a new Foreign Key in the Lines table that adds a link to the new Primary Key PONumber • This means you are going to duplicate the PONumber many times in the tables.

  26. What else can we do? • Look for other “redundancies” • What is the Cost/Benefit of adding new tables to address them?1 New FK FieldSize X N = %ofDuplicates X Total RecordSize X N New FK FieldSize = %ofDuplicates Total RecordSize 1. cost of redundancy only

  27. PO Model phase 2 1 N Has POInfo Line M 1 Has Vendor

  28. PartNum PO Model phase 3 PartDescr PONumber LineKey PartNum PONumber 1 M M 1 POInfo POLine Product M 1 VendNum Vendor VendNum VendAddr

  29. Where do you put? • Totals and subtotals? • Taxes? • Payment details?

  30. Data Dictionary

  31. Now Build the Tables

  32. Data Definition Commands CREATE TABLE POInfo (PO_Number FCHAR(5) NOT NULL UNIQUE,V_CODE FCHAR(3) NOT NULL,PRIMARY KEY (PO_Number); FOREIGN KEY (S_CODE) REFERENCES VENDOR ON DELETE RESTRICT ON UPDATE CASCADE));

  33. Data Definition Commands CREATE TABLE POLine(LineKey FCHAR(5) NOT NULL UNIQUE, PONumber VCHAR(35) NOT NULL, PartNum VCHAR(15) NOT NULL, PRIMARY KEY (LineKey)); FOREIGN KEY (PONumber) REFERENCES POInfo ON DELETE RESTRICT ON UPDATE CASCADE)); FOREIGN KEY (PartNum) REFERENCES Product ON DELETE RESTRICT ON UPDATE CASCADE));

  34. Data Definition Commands CREATE TABLE Vendor(V_CODE FCHAR(5) NOT NULL UNIQUE, V_NAME VCHAR(35) NOT NULL, V_CONTACT VCHAR(15) NOT NULL, V_AREACODE FCHAR(3) NOT NULL, V_PHONE FCHAR(3) NOT NULL, V_STATE FCHAR(2) NOT NULL, v_ORDER FCHAR(1) NOT NULL, PRIMARY KEY (V_CODE));

  35. Data Definition Commands CREATE TABLE PRODUCT(P_CODE VCHAR(10) NOT NULL UNIQUE, P_DESCRIPT VCHAR(35) NOT NULL, P_INDATE DATE NOT NULL, P_ONHAND SMALLINT NOT NULL, P_MIN SMALLINT NOT NULL, P_PRICE DECIMAL(8,2) NOT NULL, P_DISCOUNT DECIMAL(4,1) NOT NULL, V_CODE SMALLINT, PRIMARY KEY (P_CODE), FOREIGN KEY (S_CODE) REFERENCES VENDOR ON DELETE RESTRICT ON UPDATE CASCADE);

  36. Data Definition Commands • SQL Integrity Constraints • Entity Integrity • PRIMARY KEY • NOT NULL and UNIQUE • Referential Integrity • FOREIGN KEY • ON DELETE • ON UPDATE Remember the business rules!

  37. How About “Payment Type”? • How do you handle something like this? • What is it a “fact” about.

  38. How about a more Complex Example

  39. Sub Sections

  40. Modeling Issues • Keep the “Facts” right. • Look at the Cost/Benefit relationships • Make sure you get rid of all of the M x N relationships. • KISS = Keep It Simple Students.

  41. So now that you built it • We need to query it. • We need to manage it.

  42. How would we recreate the Purchase Order in SQL? • It depends on what the Model looks like.

  43. Phase 0 revisited Simple SQL based on One table.

  44. Listing the Table Contents SELECT * FROM PurchaseOrder; SELECT P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND, P_MIN, P-PRICE, P_DISCOUNT, S_CODE FROM PurchaseOrder; But we said this One Table approach was too costly!

  45. PartNum PO Model phase 3 PartDescr PONumber LineKey PartNum PONumber 1 M M 1 POInfo POLine Parts M VendNum Vendor VendNum VendAddr

  46. The Key Question • What do you want to know?

  47. Bringing it Together • We now need to get information from more than one table. • This is done with the SQL “Join”. • Joins use the Primary and Foreign Keys to link the tables in the SQL Command together. Remember!!! WHERE TABLE1.PRIMARY=TABLE2.FOREIGN

  48. More Complex Queries SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_PRICE, VENDOR.V_NAME, VENDOR.V_CONTACT, VENDOR.V_AREACODE, VENDOR.V_PHONEFROM PRODUCT, VENDORWHERE PRODUCT.V_CODE = VENDOR.V_CODE AND VENDOR.V_CODE=1234;

  49. Another Cost • When you join two tables you incur a cost due to the Join process. • A Join builds an intermediate file that this the combination of all of the entity instances of the one table combined with all of the entity instances of the other table. • These tables can get very large.

More Related