1 / 13

Mastering SQL: Practical Applications and Best Practices

Explore various ways to use SQL statements, including submission to DBMS, embedding in applications and web pages, reporting, and data extraction. Learn about SKU, comparisons, duplicates, SQL queries in Access, built-in functions, and query optimization.

etse
Download Presentation

Mastering SQL: Practical Applications and Best Practices

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. Ways to use SQL statements • You can directly submit them to the DBMS for processing • You can embed SQL statements into client/server application programs • You can embed SQL statements into Web pages • You can use them in reporting and data extraction programs • You can execute SQL statements directly from Visual Studio.NET and other development tools

  2. Sidebars • What does SKU stand for? • Stock keeping unit • OrderTotal in RETAIL_ORDER does not equal ExtendedPrice in ORDER_ITEM table. Why do you think this is so? • Because OrderTotal includes tax, shipping, and miscellaneous charges.

  3. Who uses SQL? • Knowledge workers • Application programmers • Database administrators

  4. More Sidebars • Tables (relations) appearing as the result of queries come with column headings • The order of the column names in the SELECT phrase determines the order of the columns in the results table

  5. More Sidebars • Why does SQL not automatically delete duplicate rows in results tables? • Time consuming because each row must be compared with every other row. • Suppose a results table had 10,000 rows. Roughly how many comparisons would be required? • 10,000 = 104; • 104 * 104 = 108 = 100,000,000 comparisons • How do you force the duplicates to be deleted? • Use keyword word DISTINCT

  6. More Sidebars • Remember that the SQL SELECT does a relational algebra project in that it chooses columns. • When the SELECT statement includes a WHERE with text or date data, the comparison values must be enclosed in single quotes.

  7. Ways to create a SQL query in Access

  8. By using Design View • click Queries -- at left • click new --at top • click ok -- to select Design View • click Close – on the Show Table dialog box • click View – on the Access menu • click SQL View – • enter a SQL statement in the blank window • click Query • click Run

  9. Created queries • can be altered • by clicking on View – on Access menu & then • by clicking on SQL view • can be saved • by using File/Save -- while the query window is active

  10. About SQL queries • What is the default order? • What do the IN and NOT IN operators replace? • What is the advantage of IN and NOT IN operators? • A row qualifies for an IN condition if the column is equal to any of the values in the parentheses. • A row qualifies for a NOT IN condition if it is not equal to all of the items in parentheses

  11. About Built-in functions • We want meaningful column names in our results table. • What if you don’t want to use an existing column name? • Use AS to create a name if you want a new one. • How is COUNT different from SUM? COUNT counts number of rows SUM finds the sum of the values in a column

  12. More about Built-in functions • You cannot combine a table column name with a built-in function (unless grouping involved). • You cannot used built-in functions in a WHERE clause.

  13. A few more points • When using a WHERE and a HAVING clause, WHERE is always applied before HAVING. • The number of decimal digits displayed, currency characters differ from DBMS to DBMS. Look under formatting results to learn more.

More Related