When Using the Query Designer, Where Is the Generated Sql Statement Displayed?
QUERYING THE DATABASE: QUERIES and VIEWS
Query: Statement that allows data retrieval
Horizon: A virtual table; a saved query (the SELECT program line, not the result)
SELECT program line (DML)
- retrieves a limited set of information from one operating theater more tables using criteria specified in the WHERE clause
- often used to execute calculations on the data selected
- the result set is displayed equally a hold over (columns and rows)
Unmated-table example (review):
Current Product List: all data comes from the Products remit
Sentence structure
Blue-ribbon column name
FROM tablename
WHERE criteria
ORDER BY column list
Select from 2 tables: Example
Run the Orders Query (Orders Qry on the Query heel): It lists all orders for entirely customers, without going into describe items (fiat details), by retrieving related information from the Orders and Customers tables.
Note the come of rows and columns; several columns are repeated more oft than strictly required.
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.Cover, Customers.City, Customers.Region, Customers.PostalCode, Customers.Area
FROM Customers
Inmost JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
Note: The table names need not be repeated unless the equal column name calling exist in some tables. The table names are only required in the FROM, JOIN, and ON clauses, and in the latter, just because the relating column, CustomerID, has the homophonic mention in some tables.
The enquiry syntax shown above follows ANSI (American National Standards Plant) rules and should work in the current versions of all relative databases. Aged syntax includes the join condition in the WHERE clause (theta elan). Preeminence the number of rows and columns in the lead set for the Orders Query and try the same example (with fewer columns), using the older style and table aliases, as follows:
Choose o.OrderID, o.EmployeeID, o.OrderDate, o.RequiredDate, o.ShippedDate, o.ShipVia, o.Freight, c.CompanyName, c.Address, c.Urban center, c.Region, c.PostalCode, c.Area
FROM Customers c, Orders o
WHERE c.CustomerID = o.CustomerID;
JOIN OPERATOR
The JOIN operator specifies how to relate tables in the query. The JOIN wheeler dealer 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-wing. RIGHT, FULL)
Grumpy
Joins may comprise depicted as Venn diagrams, American Samoa shown below along with other common set ahead trading operations:
INNER JOIN: Select only those rows that have values in common in the columns specified in the ON clause.
LEFT, RIGHT, operating theatre FULL OUTER JOIN: Choose all rows from the table on the left (or suitable, or both) regardless of whether the other table has values in common and (ordinarily) enter Void where information is missing. (Note: FULL OUTER Sum non implemented in Access.)
CROSS JOIN (not illustrated - not incisively a set surgical procedure): Select all possible combinations of rows and columns from both tables (Cartesian product). Not accessible in Access simply can "happen" by not specifying relationships between tables Oregon not background up the appropriate joins in a query. (Non A Operative Thing - the query may run for a identical foresighted time and produce a huge, non very useful ensue set.)
Entree uses the ANSI (American English General Standards Bring) expressive style, with the JOIN and ON keywords. Access, MySQL, and Oracle all use similar phrase structure, with more join types and options and strange set operations in MySQL and Oracle (CROSS JOIN, FULL Outside JOIN, INTERSECT, MINUS).
Choice from two tables: More examples
- Alphabetical Name of Products: Lists products that take up not been out of print and the product category, victimisation every last columns from Products (Products.*) and one from Categories:
SELECT Products.*, Categories.CategoryName
FROM Categories
Inmost JOIN Products
ON Categories.CategoryID=Products.CategoryID
WHERE (((Products.Discontinued)=No));
- Order Inside information Extended: Calculates the Extended Price, the discounted total for each line item (decree detail) in all orders, using information from the Order Details and Products tables :
Prize
[Order Inside information].OrderID, [Club Inside information].ProductID, Products.ProductName, [Club Inside information].UnitPrice, [Order Details].Quantity, [Order Details].Brush aside,
CCur([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice
FROM Products
INNER Fall in [Order Details]
ON Products.ProductID=[Club Details].ProductID
Gild BY [Order Details].OrderID;
Select from deuce tables: Exercises
- Modify the Orders inquiry to show only customers from Oregon and list each client's figure and address once only (i.e., remove redundant columns). Use CompanyName from the Customers prorogue, not from the Orders table (to avoid an Access-special lookup). Sort past customer (company) name. (Result: 28 rows)
- Text use: Alter the previous query to list customers from Mexico and Canada, showing city and country in this format:
Metropolis, Country (e.g., Montreal, Canada)
with a column header much as ShippedTo operating theatre Shipped To. To get along this, replace the City and Country columns with one calculated column (comma at the end to separate from the next column if necessary):
Urban center & ", " &ere; Country AS ShippedTo,
or City &adenosine monophosphate; ", " &adenylic acid;
Note: Be sure to find the correct names for the City and Country columns - they are different in the ii tables. (Result: 58 rows)
- Likewise: Tilt
Urban center, Region PostalCode (e.g.:
Tin can you assort on the calculated column in the SQL statement? (Result: 152 rows)
SELECT FROM TWO TABLES: SYNTAX
(Recommended, ANSI-style)
SELECT column inclination
FROM table1
&N.B. sp; INNER JOIN table2
&niobium sp; ON table1.col1=table2.col2
WHERE criteria
Arrange Aside column list
(Older, theta-style)
SELECT editorial list
FROM table1, table2
WHERE table1.col1=table2.col2
AND other criteria
ORDER BY chromatography column name
Note:
- col1 in table1 is usually that postpone's first-string key
- col2 in table2 is a foreign keystone in that table
- col1 and col2 must have the identical data type and for sure data types, the synoptical size
MULTIPLE-Board SELECT
Examples
- The Sales past Class query summarizes gross revenue data ($ figures) for all products, sized by family, using data from
FROM Categories WHERE (((Orders.OrderDate) Between #1/1/1997# And #12/31/1997#)) GROUP Away Categories.CategoryID, Categories.CategoryName, Products.ProductName Set up BY Categories.CategoryName;
SELECT Categories.CategoryID, Categories.CategoryName, Products.ProductName, Sum([Order Inside information Extended].ExtendedPrice) AS ProductSales
INNER JOIN (Products
INNER JOIN (Orders
Intrinsic JOIN [Order Inside information Extended]
ON Orders.OrderID=[Order Details Extended].OrderID)
ON Products.ProductID=[Order Details Lengthy].ProductID)
ON Categories.CategoryID=Products.CategoryID
A join condition (ON table1.col1 = table2.col2) must beryllium specified for each join.
If the join is in the WHERE article, the rules are the synoptical - the marginal number of join criteria is equal to the numeral of tables (or views) subtraction one.
The GROUP BY clause summarizes data in subsets, in this suit bounteous 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 subsequently WHERE (if present), ORDER BY senior.
- The Invoices query pulls together data from altogether tables exclude Categories and Suppliers. Bunk the query, past go to SQL view:
SELECT
Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Orders.CustomerID,
Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country, [FirstName] & " " & [LastName] Eastern Samoa Sales representative,
Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate,
Shippers.CompanyName,
[Order Inside information].ProductID,
Products.ProductName,
[Order Details].UnitPrice, [Order Details].Quantity, [Social club Details].Discount,
CCur([Order Details].UnitPrice*[Amount]*(1-[Discount])/100)*100 AS ExtendedPrice,
Orders.Freightage
FROM Shippers
INNER JOIN (Products
INNER JOIN ((Employees
& nbsp; INNER JOIN (Customers
& nbsp; INNER JOIN Orders
&adenylic acid; nbsp; ON Customers.CustomerID=Orders.CustomerID)
&ere; nbsp; ON Employees.EmployeeID=Orders.EmployeeID)
& nbsp; INNER JOIN [Order Details]
& nbsp; ON Orders.OrderID=[Social club Details].OrderID)
ON Products.ProductID=[Order Details].ProductID)
Connected Shippers.ShipperID=Orders.ShipVia;
Exercises
- Create a list of products that shows the category name for each and the contact mention of the supplier (77 rows)
- Re-create and save up either sample query higher up with a different name and rewrite with the join criteria in the WHERE clause (theta dash).
Tip: To break down or troubleshoot a query in the Access query window or in the command line utility in Oracle or MySQL, try breaking the program line as shown in the syntax diagram, with the keywords at the beginning of the lines; or replicate and library paste to a copy editor (e.g., Notepad) and rearrange there.
Outermost JOINS :
Used to find data in one table that is missing related data from some other, for case a supplier from whom we have no products, or a product that hasn't been categorized, or a customer who has non placed an order.
Rationale: Conjoin the tables and incu all the rows from one table whose corresponding rows in the another set back have a null value (data missing or apprais unknown).
Example/exercise
List the company gens, contact person, and phone bi of customers who stimulate not placed orders. Type the following financial statement in the SQL windowpane:
Blue-ribbon CompanyName, ContactName, Phone
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.CustomerID is aught;
The result should comprise 2 rows.
In MS Entree, this statement is similar to the SQL generated aside the One and only Query Wizard.
Note the emphasis connected "related data" above. See whether an outer conjoin is requisite to make a list like the single in the former example of customers whose orders hold non been shipped. Why or wherefore not?
Queries using correct trading operations
A UNION query brings together in one result do data from two or more unrelated tables or queries that have identical structure (said number of columns with same data types occurring in the duplicate parliamentary law; not necessarily same column headers).
A UNION query cannot comprise shapely in the graphical query interface in Access code.
Good example
Customers and Suppliers aside City:
Superior City, CompanyName, ContactName, "Customers" AS [Relationship]
FROM Customers
UNION SELECT City, CompanyName, ContactName, "Suppliers"
FROM Suppliers
Range Away City, CompanyName;
- Variable number of SELECT statements connected by the Key word
- Columns must be called (probative if they were calculated)
- Optional additional column or columns to minimal brain dysfunction information or to make tabular array structures fit
- No duplicates unless North ALL is specified (non obvious from this deterrent example)
- If the result set is to be sorted, only one Regulate BY article at the end
- Create an address list for all employees, customer contacts, and provider contacts, sized aside name. For this exercise, use employee's last name entirely.
- Modify the previous enquiry to economic consumption the employee's sunset name and first name. (More than one way to do this.)
- Change one of the previous queries to include an additional pillar (call it Role) where from each one person is selected as Employee, Client, or Supplier.
SYNTAX
- Without duplication:
SELECT statement1
SELECT statement2
[...]
Take statement-last
Ordering BY column inclination
- To include entirely rows, regardless of gemination:
Pick out statement1
Union ALL
[...]
SELECT statement-closing
ORDER BY column lean
Other located operators (Oracle): Cross and MINUS
PASS-Through and through queries
Ill-used when linking one database to other through an ODBC (Open Database Connectivity) or JDBC (Java Database Connectivity or Sunlight Coffee received) connection. The query written in the local database is "passed through with" as is to the database happening the server and refined aside the remote database.
Examples
- Update values in a remote mesa
- Find the following sequence number for a postpone (Oracle - uses the ancillary remit Plural in the remote database)
- Run a interrogation using a function or calculation that cannot make up performed in the local anesthetic database
When Using the Query Designer, Where Is the Generated Sql Statement Displayed?
Source: https://www1.udel.edu/evelyn/SQL-Class2/SQLclass2All.html
0 Response to "When Using the Query Designer, Where Is the Generated Sql Statement Displayed?"
Post a Comment