what would you use to find a customer who purchased the same item twice sql

QUERYING THE DATABASE: QUERIES and VIEWS

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 ane or more tables using criteria specified in the WHERE clause

- frequently used to perform calculations on the data selected

- the result prepare is displayed as a table (columns and rows)

Single-tabular array instance (review):

Current Product List: all data comes from the Products table

SYNTAX

SELECT column list

FROM tablename

WHERE criteria

ORDER BY column listing

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), past retrieving related data from the Orders and Customers tables.

Annotation the number of rows and columns; several columns are repeated more ofttimes than strictly necessary.

Utilise the drib-down listing next to the View push (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;

Annotation:  The table names demand not be repeated unless the aforementioned cavalcade names exist in both tables.  The table names are but required in the FROM, JOIN, and ON clauses, and in the latter, only because the relating column, CustomerID, has the same proper name in both tables.

The query syntax shown above follows ANSI (American National Standards Institute) rules and should work in the latest versions of all relational databases.  Older syntax includes the bring together condition in the WHERE clause (theta style).  Note the number of rows and columns in the consequence prepare for the Orders Query and try the same case (with fewer columns), using the older style and table aliases, as follows:

SELECT 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.Country

FROM Customers c, Orders o

WHERE c.CustomerID = o.CustomerID;

Note for MS Admission users: Compare this query in design view with the ANSI fashion query. MS Access runs the query correctly but cannot represent it in the usual way In the graphical query interface.

JOIN OPERATOR

The JOIN operator specifies how to chronicle tables in the query.  The JOIN operator is one of the set up operations available in relational databases.

The following join types of join are available in nearly relational databases:

INNER

OUTER (LEFT. Right, FULL)

CROSS

Joins may be represented as Venn diagrams, as shown below forth with other mutual set operations:

Effect of applying these joins in a query:

INNER Bring together: 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 tabular array on the left (or right, or both) regardless of whether the other tabular array has values in mutual and (usually) enter NULL where data is missing.  (Notation:  FULL OUTER JOIN not implemented in Admission.)

CROSS Join (not illustrated - not exactly a set functioning): Select all possible combinations of rows and columns from both tables (Cartesian product). Not bachelor in Access only tin "happen" by not specifying relationships between tables or non setting upward the appropriate joins in a query.  (Non A Expert Affair - the query may run for a very long time and produce a huge, not very useful issue set.)

Access uses the ANSI (American National Standards Institute) style, with the JOIN and ON keywords. Access, MySQL, and Oracle all employ 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 ii tables:  More examples

•  Alphabetical List of Products:    Lists products that accept not been discontinued and the product category, using all columns from Products (Products.*) and i from Categories:
  

SELECT Products.*, Categories.CategoryName

FROM Categories
INNER Bring together Products
ON Categories.CategoryID=Products.CategoryID

WHERE (((Products.Discontinued)=No));

• Order Details Extended:   Calculates the Extended Price, the discounted total for each line item (order detail) in all orders, using information from the Order Details and Products tables :
  

SELECT

 [Gild Details].OrderID, [Order Details].ProductID, Products.ProductName, [Order Details].UnitPrice, [Order Details].Quantity, [Society Details].Discount,
CCur([Order Details].UnitPrice*[Quantity]*(one-[Discount])/100)*100 Every bit ExtendedPrice

FROM Products
    INNER Join [Lodge Details]
    ON Products.ProductID=[Order Details].ProductID

Guild BY [Order Details].OrderID;

Note:  The calculation is non as complex as information technology may seem.  It is simply unit of measurement price * quantity * discount, formatted as currency.

Select from two tables: Exercises

•  Change the Orders query  to show only customers from Oregon and list each client's name  and address in one case only (i.e., remove redundant columns).   Use CompanyName from the Customers tabular array, not from the Orders table (to avoid an Admission-specific lookup).   Sort by customer (visitor) proper noun.  (Result: 28 rows)

• Text manipulation: Modify the previous query to listing customers from Mexico and Canada, showing metropolis and country in this format:

City, Country      (e.one thousand.,  Montreal, Canada)

        with a column header such as ShippedTo or Shipped To. To do this, replace the Urban center and Land columns with ane calculated cavalcade (comma at the end to carve up from the next column if necessary):

City & ", " & Country Equally ShippedTo,

or Metropolis & ", " & Country As [Shipped To],

Annotation:  Exist certain to find the right names for the City and Country columns - they are unlike in the ii tables.  (Result: 58 rows)

• Similarly: List US and Canadian customer addresses forth with their orders (number and society appointment), with urban center, state, and postal code in one column, with the header CityStateZip:

City, Region PostalCode (e.g.: Newark , DE 19716 )

         Can y'all sort on the calculated cavalcade in the SQL statement?  (Result:  152 rows)

SELECT FROM TWO TABLES: SYNTAX

(Recommended, ANSI-style)

SELECT column listing

FROM table1

                     &nb sp; INNER JOIN table2

                     &nb sp; ON table1.col1=table2.col2

WHERE criteria

ORDER BY column list

(Older, theta-style)

SELECT column list

FROM table1, table2

WHERE table1.col1=table2.col2

AND other criteria

Lodge BY cavalcade listing

Note:

- col1 in table1 is normally that tabular array'south chief cardinal

- col2 in table2 is a foreign key in that table

- col1 and col2 must have the same data type and for certain information types, the same size

MULTIPLE-Tabular array SELECT

Examples

• The Sales past Category query summarizes sales data ($ figures) for all products, sorted by category, using data from three tables (Products, Orders, and Order Details) and the Order Details Extended query (equivalent to a view).
  

   Run the query - find that there is one row per product.   Then switch to SQL view:

SELECT Categories.CategoryID, Categories.CategoryName, Products.ProductName, Sum([Society Details Extended].ExtendedPrice) AS ProductSales

FROM Categories
     INNER Bring together (Products
          INNER JOIN (Orders
               INNER Join [Order Details Extended]
               ON Orders.OrderID=[Order Details Extended].OrderID)
          ON Products.ProductID=[Society Details Extended].ProductID)
     ON Categories.CategoryID=Products.CategoryID

WHERE (((Orders.OrderDate) Betwixt #ane/ane/1997# And #12/31/1997#))

Group Past Categories.CategoryID, Categories.CategoryName, Products.ProductName

Lodge BY Categories.CategoryName;

Notes:
The number of joins is equal to the total number of tables (or views) minus ane.
A bring together condition (ON table1.col1 = table2.col2) must be specified for each join.

If the join is in the WHERE clause, the rules are the aforementioned - the minimum number of join criteria is equal to the number of tables (or views) minus ane.

The GROUP Past clause summarizes data in subsets, in this example giving 1 row per product.  (Topic to exist covered in detail in the third form)

The order of clauses in the SQL statement is of import:  GROUP BY afterwards WHERE (if present), Lodge Past last.

• The Invoices query pulls together data from all tables except Categories and Suppliers.  Run the query, then 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] AS Salesperson,
Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate,
Shippers.CompanyName,
[Order Details].ProductID,
Products.ProductName,
[Order Details].UnitPrice, [Order Details].Quantity, [Club Details].Discount,
CCur([Social club Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100 Every bit ExtendedPrice,

Orders.Freight

FROM Shippers
     INNER Bring together  (Products
               INNER Bring together ((Employees
                 & nbsp;       INNER Bring together (Customers
                 & nbsp;            INNER JOIN Orders
                 & nbsp;            ON Customers.CustomerID=Orders.CustomerID)
                 & nbsp;            ON Employees.EmployeeID=Orders.EmployeeID)
                 & nbsp;                  INNER JOIN [Club Details]
                 & nbsp;                  ON Orders.OrderID=[Club Details].OrderID)
     ON Products.ProductID=[Order Details].ProductID)
     ON Shippers.ShipperID=Orders.ShipVia;

 Note:  Relationships among the 6 tables are not linear so information technology is harder to "see" them in the SQL statement.

Exercises

• Create a list of products that shows the category name for each and the contact name of the supplier  (77 rows)

• Re-create and salvage either sample query in a higher place with a different name and rewrite with the join criteria in the WHERE clause (theta style).
  

Tip: To analyze or troubleshoot a query in the Admission query window or in the command line utility in Oracle or MySQL, try breaking the argument as shown in the syntax diagram, with the keywords at the get-go of the lines; or copy and paste to a text editor (e.g., Notepad) and rearrange there.

OUTER JOINS :

Used to find information in one tabular array 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 client who has not placed an social club.

Principle: Bring together the tables and find all the rows from one table whose corresponding rows in the other table have a nil value (data missing or value unknown).

Case/exercise

Listing the company proper name, contact person, and phone number of customers who accept 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;

The consequence should exist 2 rows.

In MS Access, this argument is similar to the SQL generated past the Unmatched Query Magician.

Note the emphasis on "related data" above.   See whether an outer bring together is necessary to create a listing like the one in the previous example of customers whose orders accept non been shipped.  Why or why non?

Queries using set operations

Marriage

A Spousal relationship query brings together in 1 result set data from two or more than unrelated tables or queries that have identical structure (aforementioned number of columns with same information types occurring in the aforementioned lodge; not necessarily same column headers).

A UNION query cannot be built in the graphical query interface in Access.

Example

Customers and Suppliers past City:

SELECT Urban center, CompanyName, ContactName, "Customers" As [Relationship]
FROM Customers
UNION SELECT City, CompanyName, ContactName, "Suppliers"
FROM Suppliers
Guild BY City, CompanyName;

- Variable number of SELECT statements linked by the key word UNION

- Columns must be named (of import if they were calculated)

- Optional boosted column or columns to add together information or to brand table structures lucifer

- No duplicates unless UNION ALL is specified (non obvious from this example)

- If the result gear up is to exist sorted, just i Order BY clause at the end

UNION : Exercises

• Create an accost list for all employees, customer contacts, and supplier contacts, sorted past proper name. For this exercise, use employee's last name simply.

• Change the previous query to apply the employee's terminal name and first name. (More than than one way to practice this.)

• Modify one of the previous queries to include an additional cavalcade (telephone call it Role) where each person is designated as Employee, Client, or Supplier.

SYNTAX

• Without duplication:

SELECT statement1

UNION

SELECT statement2

UNION

[...]

SELECT statement-last

ORDER Past cavalcade listing

• To include all rows, regardless of duplication:

SELECT statement1

UNION ALL

[...]

SELECT statement-last

ORDER Past column listing

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 (Coffee Database Connectivity  or Dominicus Java standard) connection. The query written in the local database is "passed through" as is to the database on the server and processed past the remote database.

Examples

- Update values in a remote table

- Observe the next sequence number for a table (Oracle - uses the auxiliary table DUAL in the remote database)

- Run a query using a function or calculation that cannot exist performed in the local database

blackstockweng1992.blogspot.com

Source: https://www1.udel.edu/evelyn/SQL-Class2/SQLclass2All.html

Share this post