1 / 19

Database Queries and Structured Query Language (SQL)

Database Queries and Structured Query Language (SQL). J.G. Zheng May 16 th 2008. Overview. SQL Language Query with SQL All examples can be used with the “AmazonBook” database Download it from the course website. Introduction/Review. What is SQL Review: Database and DBMS (chapter 1)

corina
Télécharger la présentation

Database Queries and Structured Query Language (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. Database Queries and Structured Query Language(SQL) J.G. Zheng May 16th 2008

  2. Overview • SQL Language • Query with SQL • All examples can be used with the “AmazonBook” database • Download it from the course website

  3. Introduction/Review • What is SQL • Review: Database and DBMS (chapter 1) • SQL is a standard language accepted by relational DBMS to perform database operations • Some facts about SQL • SQL 92 is the most commonly supported version • English-like (not programming) • Case insensitive • Venders have different implementations

  4. Concepts Review • Relational model concepts review Relation : Table Row : Record Column : Field (Attribute)

  5. Common Data Types • Numeric • INTEGER, SMALLINT • DECIMAL(i,j), NUMBER(i,j) • Character/String • CHAR(n), VARCHAR(n) • Date • DATE, DATETIME • See textbook page 42-43 for data types implemented in Access

  6. SELECT Statement • SELECT statement retrieves data from database (query) • The result is usually another table • We will learn • Defining selection criteria • Sorting • Calculation • Grouping

  7. SELECT Statement Syntax SELECT Column(s) or other expressions FROM Table(s) [WHERE …] [ORDER BY ColumnName]

  8. Simple SELECT Statement • Syntax • SELECT * (or a list of columns) FROM TableName • Wild card: * • Example • SELECT * FROM Books • SELECT BookTitle, ListPrice FROM Books

  9. Use WHERE Clause • Use WHERE clause to specify selection criteria • Example • SELECT * FROM Books WHERE ListPrice = 29.99 • SELECT BookTitle, ListPrice FROM Books WHERE ListPrice < 20 • Comparison Operators • “=“, “>”, “<“, “>=“, “<=“, “<>”

  10. More Comparison Operators • IN (value list) • SELECT * FROM Books WHERE ListPrice IN (19.99, 29.99, 39.99) • BETWEEN min AND max • SELECT * FROM Books WHERE ListPrice BETWEEN 9.99 AND 19.99

  11. String Pattern Match • Fuzzy query using LIKE • _ (underscore): single character wildcard • ? in Access • % (percentage): multiple character wildcard • * in Access • Example • SELECT * FROM Books WHERE BookTitle LIKE '*information systems*'

  12. Compound Conditions • Use logical operators to connect multiple conditions • AND: an intersection of the data sets • OR: a union of the data sets • Examples • SELECT * FROM Books WHERE ListPrice <= 19.99 AND ListPrice >= 9.99 • SELECT * FROM Books WHERE PubDate=#10/1/2003# OR PubDate=#10/1/2004# • SELECT * from Books WHERE Publisher = 'Que' AND Binding = 'Paperback'

  13. Sorting • Syntax • ORDER BY Column(s) [ASC/DESC] • Examples • SELECT * FROM Books ORDER BY PubDate • SELECT * FROM Books ORDER BY Publisher DESC, PubDate

  14. Calculation • Calculating columns • Calculated columns are not designed directly into the table • Using +, -, *, / with columns and numbers • Example • SELECT BookTitle, ListPrice, ListPrice * 0.1 AS Discount FROM Books WHERE ListPrice * 0.1 >= 15

  15. Built-in Functions • Using these functions to do statistics • MIN • MAX • COUNT • AVG • SUM • Example • SELECT COUNT(*) FROM Books • SELECT AVG(ListPrice) FROM Books WHERE Publisher = 'Prentice Hall'

  16. Grouping • GROUP BY: doing math with groups • SELECT COUNT(*) FROM Books WHERE Publisher = 'Prentice Hall';SELECT COUNT(*) FROM Books WHERE Publisher = 'The MIT Press';… • Or: SELECT Publisher, COUNT(*) FROM Books GROUP BY Publisher

  17. A Complete Query SELECT ISBN, BookTitle, ListPrice, Publisher FROM Books WHERE BookTitle like '*Information Systems*' AND PubDate > #1/1/2002# AND ListPrice < 100 ORDER BY ListPrice

  18. Exercise Using the “AmazonBook” database, use SQL or QBE to answer the following questions. • Which book is the most expensive? • How many books are under $100 of list price? • I am looking for a “database” book, no more than 50 dollars, and published after 1/1/2003; do you have any recommendations? I need the book title and its price. • What is the price per page for the books published by Que? List the book title, price, number of pages and price for page, and sort by the price per page, descending

  19. Good Resources • SQL Online Tutorial • http://sqlcourse.com/ • http://sqlcourse2.com/

More Related