130 likes | 134 Views
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
E N D
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
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.
Who uses SQL? • Knowledge workers • Application programmers • Database administrators
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
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
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.
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
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
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
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
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.
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.