1 / 42

S Q L

S Q L. STRUCTRED QUERY LANGUAGE DDL AND DML COMPONENTS RELATIONAL COMPLETE ANSI / ISO STANDARD + DIALECTS (e.g., Microsoft Jet SQL) SQL DATABASES ODBC - OPEN DATA BASE CONNECTIVITY. SELECT STATEMENT. SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY.

osric
Download Presentation

S Q L

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. S Q L • STRUCTRED QUERY LANGUAGE • DDL AND DML COMPONENTS • RELATIONAL COMPLETE • ANSI / ISO STANDARD + DIALECTS (e.g., Microsoft Jet SQL) • SQL DATABASES • ODBC - OPEN DATA BASE CONNECTIVITY S Q L

  2. SELECT STATEMENT SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... • SELECT STATEMENT WORKS ON TABLE(S) AND GENERATES A TABLE OF OUTPUT • RESULT COULD BE NULL S Q L

  3. Relational Operations Project Select Select & Project S Q L

  4. Set Operations UNION A È B A B A Ç B INTERSECT SUBTRACT A - B A A B B S Q L

  5. Join Operations (1) SELECT * FROM A, B WHERE Join Criteria is true Most common Join: A is Parent and B is Child. Join the two tables together to find out who is related to who. SELECT * FROM A, B WHERE A.[PRIMARY KEY] = B.[FOREIGN KEY] S Q L

  6. Join Operation (2) • The above is an example of an EQUI JOIN. • Assume A has m rows and B has n rows. • A Cartesian Product ( A x B ) of the two tables is formed first resulting in a ( m x n ) table (this is for illustration only). • The Join Criteria is applied to the table and only those that satisfy the Criteria are selected. S Q L

  7. Join Operation (3) • Those columns specified will be Projected. • The GROUP BY, ORDER BY and HAVING operations will then be applied to the result table to produce the final result table. S Q L

  8. Different Types of Joins (1) • EQUI Joins or Inner Joins • SELF Joins • OUTER Joins Sample Schema S Q L

  9. Different Types of Joins (2) Sample Tables Employee Project Assignment Project S Q L

  10. Different Types of Joins (3) • List Employees and the Projects they are working on. • Inner Join SELECT Name as [Employee], ProjectName as [Project], Format(PercentTime,"##0.00%") AS [Percent of Time] FROM Employee, [Project Assignment], Project WHERE Employee.[SSN] = [Project Assignment].[SSN] And [Project Assignment].[ProjectID] = Project.[ProjectID]; S Q L

  11. Different Types of Joins (3a) • List Employees and the Projects they are working on. • Inner Join Results S Q L

  12. Different Types of Joins (4) • List all Employees and the Projects they are working on (or blank). • Left Outer Join (MS Access dialect here) SELECT Name as [Employee], ProjectName as [Project], Format(PercentTime,"##0.00%") AS [Percent of Time] FROM Employee LEFT JOIN [Project + Assignment] ON Employee.[SSN] = [Project + Assignment].[SSN]; SELECT Project.ProjectID, ProjectName, SSN, PercentTime FROM Project INNER JOIN [Project Assignment] ON Project.ProjectID = [Project Assignment].ProjectID; S Q L

  13. Different Types of Joins (4a) • List all Employees and the Projects they are working on (or blank). • Left Outer Join results S Q L

  14. Different Types of Joins (5) • List all Employees and their Managers • Self Join SELECT A.Name AS Employee, B.Name AS Manager FROM Employee AS A, Employee AS B WHERE A.ManagerSSN = B.SSN ORDER BY A.Name; S Q L

  15. Different Types of Joins (5a) • List all Employees and their Managers • Self Join result S Q L

  16. Different Types of Joins (6) • List all Managers and their Employees • Self Join SELECT A.Name AS Manager, B.Name AS Employee FROM Employee AS A, Employee AS B WHERE A.SSN = B.ManagerSSN ORDER BY A.Name; S Q L

  17. Different Types of Joins (6a) • List all Managers and their Employees • Self Join results S Q L

  18. CROSSTAB QUERIES (1) ** MS Access Dialect ** TRANSFORMaggregate function AS [The Value] SELECTrow item, aggregate function FROMsome query GROUP BYrow item PIVOTcolumn item; S Q L

  19. CROSSTAB QUERIES (2) • NEED EMPLOYEE SALES BY … QUERY • START WITH AN ENCOMPASSING QUERY: [EMPLOYEE SALES QUERY] SELECT * FROM EMPLOYEE,SALE,COMPSALE,INV WHERE EMPLOYEE.[EMPLOYEE ID NO] = SALE.[EMPLOYEE ID NO] AND SALE.[SALE ID NO] = COMPSALE.[SALE ID NO] AND INV.[ITEM NO] = COMPSALE.[ITEM NO]; S Q L

  20. CROSSTAB QUERIES (3) • EMPLOYEESALES BY INV ITEMS TRANSFORM COUNT(COMPSALE.[ITEM NO]) AS [The Value] SELECT [EMPLOYEE LAST NAME]&", "&[EMPLOYEE FIRST NAME] AS EMPLOYEE, COUNT(COMPSALE.[ITEM NO]) AS COUNT FROM [EMPLOYEE SALES QUERY] GROUP BY [EMPLOYEE LAST NAME]&", "&[EMPLOYEE FIRST NAME] PIVOT[ITEM DESCRIPTION] ; S Q L

  21. CROSSTAB QUERIES (4) • EMPLOYEE SALES BY INV ITEMS QUERY RESULTS (MORE COLUMNS NOT SHOWN): S Q L

  22. CROSSTAB QUERIES (5) • EMPLOYEE SALES BY MONTH TRANSFORM COUNT(COMPSALE.[ITEM NO]) AS [The Value] SELECT [EMPLOYEE LAST NAME]&", "&[EMPLOYEE FIRST NAME] AS EMPLOYEE, COUNT(COMPSALE.[ITEM NO]) AS COUNT FROM [EMPLOYEE SALES QUERY] GROUP BY [EMPLOYEE LAST NAME]&", "&[EMPLOYEE FIRST NAME] PIVOTMONTH([SALE DATE]) ; S Q L

  23. CROSSTAB QUERIES (6) • EMPLOYEE SALES BY MONTH QUERY RESULTS: S Q L

  24. CROSSTAB QUERIES (7) • EMPLOYEE SALES BY YEAR MONTH TRANSFORM COUNT(COMPSALE.[ITEM NO]) AS [The Value] SELECT [EMPLOYEE LAST NAME]&", "&[EMPLOYEE FIRST NAME] AS EMPLOYEE, YEAR([SALE DATE]) AS YEAR, COUNT(COMPSALE.[ITEM NO]) AS COUNT FROM [EMPLOYEE SALES QUERY] GROUP BY [EMPLOYEE LAST NAME]&", "&[EMPLOYEE FIRST NAME], YEAR([SALE DATE]) PIVOT MONTH([SALE DATE]); S Q L

  25. CROSSTAB QUERIES (8) • EMPLOYEE SALES BY YEAR MONTH QUERY RESULTS: S Q L

  26. CROSSTAB QUERIES (9) • EMPLOYEE SALES $ BY MONTH TRANSFORM SUM(COMPSALE.[QUANTITY SOLD]*[SALES PRICE]) AS [The Value] SELECT [EMPLOYEE LAST NAME]&", "&[EMPLOYEE FIRST NAME] AS EMPLOYEE, SUM([QUANTITY SOLD]*[SALES PRICE]) AS SALES FROM [EMPLOYEE SALES QUERY] GROUP BY [EMPLOYEE LAST NAME]&", "&[EMPLOYEE FIRST NAME] PIVOT MONTH([SALE DATE]); S Q L

  27. CROSSTAB QUERIES (10) • EMPLOYEE SALES $ BY MONTH QUERY RESULTS: S Q L

  28. CROSSTAB QUERIES (11) • ITEM SALES BY EMPLOYEES TRANSFORM COUNT(COMPSALE.[ITEM NO]) AS [The Value] SELECT [ITEM DESCRIPTION] AS ITEM, COUNT(COMPSALE.[ITEM NO]) AS COUNT FROM [EMPLOYEE SALES QUERY] GROUP BY [ITEM DESCRIPTION] PIVOT [EMPLOYEE LAST NAME]&", "&[EMPLOYEE FIRST NAME]; S Q L

  29. CROSSTAB QUERIES (12) • ITEM SALES BY EMPLOYEES QUERY RESULTS (SOME ROWS & COLUMNS NOT SHOWN): S Q L

  30. CROSSTAB QUERIES (13) • ITEM SALES BY MONTH QUERY RESULTS: S Q L

  31. CROSSTAB QUERIES (14) • ITEM SALES BY YEARMONTH QUERY RESULTS: S Q L

  32. CROSSTAB QUERIES (15) • ITEM SALES BY STATE QUERY RESULTS (NEED CUSTOMER IN QUERY): S Q L

  33. UNION QUERIES (1) JOIN TWO TABLES UNION TWO TABLES S Q L

  34. UNION QUERIES (2) • THE TABLES MUST BE UNION COMPATIBLE • SAME NUMBER OF COLUMNS • CORRESPONDING COLUMNS MUST HAVE THE SAME DOMAIN • NO NEED FOR SAME COLUMN NAME • NOT SORTED SELECT … UNION SELECT ... S Q L

  35. UNION QUERIES (3) TABLES • LIST TOP 2 EMPLOYEES BY SALES AMOUNT AND TOP 4 BY VOLUME FOR RECEIVING INCENTIVE AWARDS • NEED SEPARATE QUERIES TO FIND THOSE EMPLOYEES AND UNION THE TWO RESULTS SETS UNION A È B A B S Q L

  36. UNION QUERIES (4) • QUERY [EMPLOYEE TOTAL SALES]: • QUERY [EMPLOYEE TOTAL VOLUME]: SELECT EMPLOYEE.[EMPLOYEE ID NO], SUM([QUANTITY SOLD]*[SALES PRICE]) AS [TOTAL SALES] FROM EMPLOYEE, SALE, COMPSALE WHERE EMPLOYEE.[EMPLOYEE ID NO]=SALE.[EMPLOYEE ID NO] AND SALE.[SALE ID NO]=COMPSALE.[SALE ID NO] GROUP BY EMPLOYEE.[EMPLOYEE ID NO] ORDER BY SUM([QUANTITY SOLD]*[SALES PRICE]) DESC; SELECT EMPLOYEE.[EMPLOYEE ID NO], COUNT([QUANTITY SOLD]) AS [TOTAL VOLUME] FROM EMPLOYEE, SALE, COMPSALE WHERE EMPLOYEE.[EMPLOYEE ID NO]=SALE.[EMPLOYEE ID NO] AND SALE.[SALE ID NO]=COMPSALE.[SALE ID NO] GROUP BY EMPLOYEE.[EMPLOYEE ID NO] ORDER BY COUNT([QUANTITY SOLD]) DESC; S Q L

  37. UNION QUERIES (5) • QUERY [EMPLOYEE AWARDS] AND RESULTS (DUPLICATES ELIMINATED): SELECT TOP 2 [EMPLOYEE ID NO] FROM [EMPLOYEE TOTAL SALES] UNION SELECT TOP 4 [EMPLOYEE ID NO] FROM [EMPLOYEE TOTAL VOLUME]; S Q L

  38. UNION QUERIES (6) • QUERY [EMPLOYEE AWARDS] AND RESULTS (DUPLICATES INCLUDED): SELECT TOP 2 [EMPLOYEE ID NO] FROM [EMPLOYEE TOTAL SALES] UNION ALL SELECT TOP 4 [EMPLOYEE ID NO] FROM [EMPLOYEE TOTAL VOLUME]; S Q L

  39. UNION QUERIES (7) • To get additional information about the Employees from the query [EMPLOYEE AWARDS]: SELECT EMPLOYEE.[EMPLOYEE ID NO], [EMPLOYEE FIRST NAME]&" "&[EMPLOYEE LAST NAME] AS [EMPLOYEES] FROM EMPLOYEE, [EMPLOYEE AWARDS] WHERE EMPLOYEE.[EMPLOYEE ID NO]=[EMPLOYEE AWARDS].[EMPLOYEE ID NO]; ? How do you show who won which award (sales or volume) ? S Q L

  40. SQL UPDATES (1) INSERTION OF A SINGLE ROW (TUPLE) INTO A TABLE INSERT INTO CUSTOMER ( [CUSTOMER ID NO], [CUSTOMER FIRST NAME], [CUSTOMER LAST NAME], [ADDRESS LINE ONE], [ADDRESS LINE TWO], CITY, STATE, [ZIP CODE], [AREA CODE], PHONE ) VALUES( '235568', 'JOHN', 'BENSON-SMITH', '23 WESTERN AVE', '', 'SAN JOSE', 'CA', '92113', '408', '2154552’); ** MS Access changes VALUES(...) to SELECT … ** S Q L

  41. SQL UPDATES (2) DELETION OF ROW(S) FROM A TABLE DELETE FROM CUSTOMER WHERE [CUSTOMER ID NO] = ‘235568’; DELETE FROM CUSTOMER WHERE [STATE] = “WA”; S Q L

  42. SQL UPDATES (3) UPDATE ROW(S) IN A TABLE UPDATE EMPLOYEE SET [SALARY REVIEW DATE] = #1/30/98# WHERE YEAR([SALARY REVIEW DATE]) = 1990; UPDATE INV SET [PRICE] = [PRICE] * 1.025 WHERE [ITEM DESCRIPTION] LIKE “*SONY*”; S Q L

More Related