1 / 13

SQL

SQL. The example SQL command uses the CARS database. The Relationships diagram is as follows:. SQL COMMAND STRUCTURES SELECT MANUFACTURER,MODEL,YEAR FROM CARS. Partial output of the previous SQL command. SELECT MANUFACTURER,MODEL,YEAR FROM CARS ORDER BY MANUFACTURER.

cally-rosales
Download Presentation

SQL

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. SQL The example SQL command uses the CARS database. The Relationships diagram is as follows: • SQL COMMAND STRUCTURES • SELECT MANUFACTURER,MODEL,YEAR • FROM CARS Partial output of the previous SQL command SELECT MANUFACTURER,MODEL,YEAR FROM CARS ORDER BY MANUFACTURER Use "ORDER BY" to sort the list.

  2. SELECT * FROM CARS WHERE Year=2003 ORDER BY MANUFACTURER The asterisk, *, means to include all the columns in the table in the selected list. • GENERAL NOTES about ACCESS SQL: • Use an "*" after a "SELECT" to list all the fields in a table. ACCESS does not support the more common "SELECT ALL". • Use "DESC" to indicate a descending sort of the output. For instance, in the first example use "ORDER BY Cost DESC" if you want to sort the output in descending order of the car’s cost. The default sort is ascending order, but the suffix "ASC" may be used. • To print an SQL command on the printer, merely copy it into an opened WORD document and print it from WORD.

  3. More SELECT Command Examples: SELECT MANUFACTURER, MODEL, YEAR, [SELLING PRICE]-COST FROM CARS WHERE Cost <=20000 ORDER BY MANUFACTURER,MODEL SELECT MANUFACTURER, MODEL, YEAR,[SELLING PRICE]-COST AS PROFIT FROM CARS WHERE Cost <=20000 ORDER BY [SELLING PRICE]-COST DESC SELECT MANUFACTURER, MODEL, YEAR,[SELLING PRICE]-COST AS PROFIT FROM CARS WHERE Cost <=20000 AND Manufacturer LIKE "Ford" ORDER BY [SELLING PRICE]-COST DESC If a field or field caption is designated and it has a blank space in the name, it must be enclosed in square brackets.

  4. SELECT MANUFACTURER, MODEL, YEAR, [SELLING PRICE]-COST AS PROFIT FROM CARS WHERE Manufacturer LIKE "Ford" OR Model LIKE "Fleetwood" ORDER BY [SELLING PRICE]-COST DESC; SELECT MANUFACTURER, MODEL, YEAR, [SELLING PRICE]-COST AS PROFIT FROM CARS WHERE Manufacturer LIKE "Ford" OR Model LIKE "SC*“ ORDER BY [SELLING PRICE]-COST DESC; Use of the wildcard character.

  5. SELECT MANUFACTURER, MODEL, YEAR, AcqDate AS AQUIRED FROM CARS WHERE AcqDate >= #1/1/2005# ORDER BY AcqDate Handling dates in SQL. SELECT MANUFACTURER, MODEL, YEAR, [SELLING PRICE]-COST AS PROFIT FROM CARS WHERE NOT (Manufacturer LIKE "Lexus") ORDER BY [SELLING PRICE]-COST DESC; (All the cars except "Lexus" will be in this select list.)

  6. Text-type fields can be concatenated to appear in one column of the listing. SELECT [Vehicle ID]&" "&Manufacturer&" "&Model AS Auto, [Selling Price]-Cost AS Profit FROM CARS WHERE Manufacturer="Ford"; SELECT MANUFACTURER, MODEL, YEAR, [SELLING PRICE]-COST AS PROFIT FROM CARS WHERE Manufacturer IN ( "Ford","Chevrolet","Oldsmobile") ORDER BY [SELLING PRICE]-COST DESC; The "IN" predicate is used to specify filter the output. The results from this query are shown on the next slide. (To create a list that negates the IN predicate, use NOT IN instead of IN.)

  7. SELECT MANUFACTURER, MODEL, YEAR, AcqDate AS AQUIRED FROM CARS WHERE AcqDateBetween #10/1/2010# AND #10/31/2010# ORDER BY AcqDate The "BETWEEN" predicate is easier to type when there is a range of values to filter. The alternate to BETWEEN in the SQL command above is as follows… WHERE AcqDate >=#10/1/2010# AND AcqDate<=#10/31/2010#

  8. USING BUILT-IN AGGREGATE FUNCTIONS: SELECT SUM(COST), AVG(COST) FROM CARS The same command with captions added: SELECT SUM(COST) AS [TOTAL VALUE],AVG(COST) AS [AVERAGE VALUE] FROM CARS SELECT SUM(COST) AS [TOTAL VALUE], AVG(COST) AS [AVERAGE VALUE] FROM CARS WHERE Manufacturer="Ford";

  9. Using an Aggregate Function and "Group By": SELECT Manufacturer, SUM(COST) AS [TOTAL VALUE] FROM CARS GROUP BY Manufacturer ORDER BY Manufacturer

  10. To specify a condition on a grouping, use "HAVING": SELECT Manufacturer, SUM(COST) AS [TOTAL VALUE] FROM CARS GROUP BY Manufacturer HAVING SUM(COST) < 5000 ORDER BY Manufacturer

  11. USING SQL TO EXTRACT INFORMATION FROM TWO TABLES: SELECT Manufacturer, Model, [Class Description] FROM CARS, CLASSES WHERE CLASSES.[Class Type] = CARS.[Class Type] ORDER BY Manufacturer, Model; This is a partial listing of the output from the above SQL command….

  12. To eliminate the repeating of a particular combination use the DISTINCT predicate SELECT DISTINCT Manufacturer, Model, [Class Description] FROM CARS, CLASSES WHERE CLASSES.[Class Type] = CARS.[Class Type] ORDER BY Manufacturer, Model; As generated by Microsoft Access: SELECT Manufacturer, Model, [Class Description] FROM CARS INNER JOIN CLASSES ON CLASSES.[Class Type] = CARS.[Class Type] ORDER BY Manufacturer, Model;

More Related