QUERYING THE DATABASE: QUERIES and VIEWS Show
Query: Statement that allows data retrieval View: A virtual table; a saved query (the SELECT statement, not the result) SELECT statement (DML) - retrieves a limited set of data from one or more tables using criteria specified in the WHERE clause - often used to perform calculations on the data selected - the result set is displayed as a table (columns and rows) Single-table example (review): Current Product List: all data comes from the Products table
SELECT column list FROM tablename WHERE criteria ORDER BY column list Select from two tables: Example Run the Orders Query (Orders Qry on the Query list): It lists all orders for all customers, without going into line items (order details), by retrieving related data from the
Orders and Customers tables.
Use the drop-down list next to the View button (circled above) to switch to SQL view. This is the SQL statement, separated into logical sections for ease of
interpretation: SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID; Note: The table names need not be repeated unless the same column names exist in both tables. The table names are only required in the FROM, JOIN, and ON clauses, and in the latter, only because the relating column, CustomerID, has the same name in both tables.
</> SELECT o.OrderID, o.EmployeeID, o.OrderDate, o.RequiredDate, o.ShippedDate, o.ShipVia, o.Freight, c.CompanyName, c.Address, c.City, c.Region, c.PostalCode, c.Country
JOIN OPERATOR The JOIN operator specifies how to relate tables in the query. The JOIN operator is one of the set operations available in relational databases. The following join types of join are available in most relational databases: INNER OUTER (LEFT. RIGHT, FULL) CROSS Joins may be represented as Venn diagrams, as shown below along with other common set operations: Result of applying these joins in a query: INNER JOIN: Select only those rows that have values in common in the columns specified in the ON clause. LEFT, RIGHT, or FULL OUTER JOIN: Select all rows from the table on the left (or right, or both) regardless of whether the other table has values in common and (usually) enter NULL where data is missing. (Note: FULL OUTER
JOIN not implemented in Access.) CROSS JOIN (not illustrated - not exactly a set operation): Select all possible combinations of rows and columns from both tables (Cartesian product). Not available in Access but can "happen" by not specifying relationships between tables or not setting up the appropriate joins in a query. (Not A Good Thing - the query may run for a very long time and produce a huge, not very useful result set.) Access uses the ANSI (American National Standards Institute) style, with the JOIN and ON keywords. Access, MySQL, and Oracle all use similar syntax, with more join types and options and other set operations in MySQL and Oracle (CROSS JOIN, FULL OUTER JOIN, INTERSECT, MINUS). Select from two tables: More examples
SELECT [Order Details].OrderID, [Order Details].ProductID, Products.ProductName, [Order Details].UnitPrice, [Order Details].Quantity, [Order
Details].Discount, FROM Products ORDER BY [Order Details].OrderID; Note: The calculation is not as complex as it may seem. It is
simply unit price * quantity * discount, formatted as currency.
City, Country (e.g., Montreal, Canada) with a column header such as ShippedTo or Shipped To. To do this, replace the City and Country columns with one calculated column (comma at the end to separate from the next column if necessary): City & ", " & Country AS ShippedTo,
or City & ", " & CountryAS [Shipped To], Note: Be sure to find the correct names for the City and Country columns - they are different in the two tables. (Result: 58 rows)
City, Region PostalCode (e.g.: Newark, DE19716) Can you sort on the calculated column in the SQL statement? (Result: 152 rows) SELECT FROM TWO TABLES: SYNTAX
SELECT column list FROM table1 &nb sp; INNER JOIN table2 &nb sp; ON table1.col1=table2.col2 WHERE criteria ORDER BY column list
SELECT column list FROM table1, table2 WHERE table1.col1=table2.col2 AND other criteria ORDER BY column list
- col1 in table1 is usually that table's primary key - col2 in table2 is a foreign key in that table - col1 and col2 must have the same data type and for certain data types, the same size MULTIPLE-TABLE SELECT Examples
Run the query - notice that there is one row per product. Then switch to SQL view:
FROM Categories WHERE (((Orders.OrderDate) Between #1/1/1997# And #12/31/1997#)) GROUP BY Categories.CategoryID, Categories.CategoryName, Products.ProductName ORDER BY Categories.CategoryName; Notes: </>The number of joins is equal to the total number of tables (or views) minus one. A join condition (ON table1.col1 = table2.col2) must be specified for each join. If the join is in the WHERE clause, the rules are the same - the minimum number of join criteria is equal to the number of tables (or views) minus one. The GROUP BY clause summarizes data in subsets, in this case giving one row per product. (Topic to be covered in detail in the third class) The order of clauses in the SQL statement is important: GROUP BY after WHERE (if present), ORDER BY last.
SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode,
Orders.ShipCountry, Orders.CustomerID, Orders.Freight FROM Shippers Note: Relationships among the six tables are not linear so it is harder to "see" them in the SQL statement. Exercises
Tip: To analyze or troubleshoot a query in the Access query window or in the command line utility in Oracle or MySQL, try breaking the statement as shown in the syntax diagram, with the keywords at the beginning of the lines; or copy and paste to a text editor (e.g., Notepad) and rearrange there.</> OUTER JOINS: Used to find data in one table that is missing related data from another, for example a supplier from whom we have no products, or a product that hasn't been categorized, or a customer who has not placed an order. Principle: Join the tables and find all the rows from one table whose corresponding rows in the other table have a null value (data missing or value unknown). Example/exercise List the company name, contact person, and phone number of customers who have not placed orders. Type the following statement in the SQL window: SELECT CompanyName, ContactName, Phone FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE Orders.CustomerID is null;
In MS Access, this statement is similar to the SQL generated by the Unmatched Query Wizard.
Queries using set operations UNION A UNION query brings together in one result set data from two or more unrelated tables or queries that have identical structure (same number of columns with same data types occurring in the same order; not necessarily same column headers). A UNION query cannot be built in the graphical query interface in Access. Example Customers and Suppliers by City: SELECT City, CompanyName, ContactName, "Customers" AS [Relationship] - Variable number of SELECT statements linked by the key word UNION - Columns must be named (important if they were calculated) - Optional additional column or columns to add information or to make table structures match - No duplicates unless UNION ALL is specified (not obvious from this example) - If the result set is to be sorted, only one ORDER BY clause at the end UNION: Exercises
SYNTAX
SELECT statement1 UNION SELECT statement2 UNION [...] SELECT statement-last ORDER BY column list
SELECT statement1 UNION ALL [...] SELECT statement-last ORDER BY column list Other set operators (Oracle): INTERSECT and MINUS PASS-THROUGH queries Used when linking one database to another through an ODBC (Open Database Connectivity) or JDBC (Java Database Connectivity or Sun Java standard) connection. The query written in the local database is "passed through" as is to the database on the server and processed by the remote database. Examples - Update values in a remote table
What object is used to store information when creating a database?A table is the database object that stores data organized in an arrangement of columns and rows. Each row is a record and each column is a field within each record.
What are programs that help a database to be maintained by creating editing and deleting data records and files?A database management system (DBMS) is system software for creating and managing databases. A DBMS makes it possible for end users to create, protect, read, update and delete data in a database.
Which of the following are advantages of using Microsoft Access to manage data quizlet?Access provides data-entry forms. Access allows more than one user to enter data at the same time. Access minimizes duplicate data.
What view is a spreadsheet like view of the data in a table?A datasheet is a useful way of viewing data in Access. Most importantly, datasheet view allows a user to view many table records at the same time. In datasheet view, information is displayed in rows and columns—similar to a spreadsheet.
|