DATABASE SQL
II. Queries involving more than one relations
In the database “ProductOrders” created in the previous exercise, write the following queries:
1. Find OrderID, Date, CustomerID, CustomerName, Address, Phone, Email of all orders.
2. List all orders of all customers including CustomerID, CustomerName, OrderID, Date. Order the result by descending order of CustomerName
(Note that we need to know also the customer who have no order). Try to use left outer join for this question.
3. Same as Question 2 but using right outer join for this exercise.
4. List CustomerID of all customers who have never bought any product.
5. Do the intersect between the list of CutomerID from Customers and the list of CustomerID from Orders? How many rows in the output?
6. Do the union between the list of CutomerID from Customers and the list of CustomerID from Orders? How many rows in the output?
7. Do the union all between the list of CutomerID from Customers and the list of CustomerID from Orders? How many rows in the output?
Homework Answers
Request Answer!
We need at least 9 more requests to produce the answer.
1 / 10 have requested this problem solution
The more requests, the faster the answer.
Below is the database schema for a company. Primary keys are underlined. Customers (CustomerID, CompanyName, Phone,...
Below is the database schema for a company. Primary keys are underlined. Customers (CustomerID, CompanyName, Phone, BalanceDue) Oders (Orderid, orderdate, customerid, freightamount) Orderdetails (oderid, productid, qtyordered, price) Products (productid, productname, qtyinstock, suggestedprice, minstocklevel) Given this SQL statement: Select * From Customers Inner Join Orders on Orders.CustomerID=Customers.CustomerID Where Orders.CustomerID is Null; Which of the following statement is correct? It is a SQL select statement that will list the ProductName for all products where the Qtyinstock is less than the total QtyOrdered...
Create the following SQL Server queries that access the Northwind database Same as problem 3 but,...
Create the following SQL Server queries that access the
Northwind database
Same as problem 3 but, limit the list to all customers who
placed 3 or more orders.
The product id, product name, last date that the product was
ordered for all items in the Grains/Cereals category. (Hint: Use
MAX)
The product ID, product name, and number of distinct customers
who ordered that product in 1996
Thank you.
Categories Customers Employees Order Details Orders Products Shippers 9 CategoryID CategoryName Description...
The Northwind database created by Microsoft contains the sales data for a fictitious company called Northwind...
The Northwind database created by Microsoft contains the sales data for a fictitious company called Northwind Traders, which imports and exports specialty foods from around the world. Here is the schema of the database: Products (ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued); Suppliers (SupplierID, CompanyName, ContactName , ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax, HomePage); Categories (CategoryID, CategoryName, Description, Picture); Orders (OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName,ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry); Order_details (ID, OrderID,...
Using Microsoft SQL Server 2 2020-05-29 Describe how SQL queries work USE Adventure Works2012 example no...
Using Microsoft SQL Server
2 2020-05-29 Describe how SQL queries work USE Adventure Works2012 example no 1 USE Adventure Works2012 GO SELECT AVG(TotalDue) as AVG_TotalDue FROM [Sales) (SalesOrder Header) example no 2 SELECT SalesOrderID Order Date. TotalDue st Name AS TerritoryName. SELECT AVG(TotalDue) FROM (Sales) (SalesOrder Header) ) AS AVG_TotalDue FROM [Sales) (SalesOrderHeader) soh inner join (Sales] [Sales Territory) st ON soh TerritoryID = st.TerritoryID WHERE st Country RegionCode="GB' and Order Date between '2004-06-01' and '2012-06-30 and TotalDue > SELECT...
Customer (CustomerId, CustomerName) Employee (EmployeeId, EmployeeName, Salary, SupervisorId) Product(ProductId, ProductName, ListPrice) Orders (OrderId, OrderDate, CustomerId, EmployeeId,...
Customer (CustomerId, CustomerName) Employee (EmployeeId, EmployeeName, Salary, SupervisorId) Product(ProductId, ProductName, ListPrice) Orders (OrderId, OrderDate, CustomerId, EmployeeId, Total) OrderedProduct (OrderId, ProductId, Quantity, Price) Write the code to complete the methods in OrderJDBC.java (look for TODO items). <---**IN BOLD** throughout code. /* OrderJDBC.java - A JDBC program for accessing and updating an order database on MySQL. */ import java.io.File; import java.math.BigDecimal; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.Scanner; /** * An application for...
Exercise 3 [12.5 marks] Given the Restaurant management system database that contains the following tables (primary...
Exercise 3 [12.5 marks] Given the Restaurant management system database that contains the following tables (primary keys are underlined and foreign keys are preceded with #): Customer (customerID,customerFirstName,customerLastName,customerAddress) Oreder (orderID,orderDate, #customerID,#menuItemID,#staffID) MenuItem(menuItemID, menuItemName,ingredients,type,availability) Staff(staffID, staffName, staffPhoneNumber, staffRole ) OrderPayment(paymentID,paymentAmount,#orderID,#staffID) 1) Without using DISTINCT, write the SQL query equivalent to the following one:[1.5 marks] SELECT DISTINCT menuItemName FROM MenuItem WHERE type = ‘Vegetarian’ OR availability= ‘Yes’; 2) Express the following queries in SQL: a) Find the number of orders placed by...
A database used for a toy store that keeps track of the inventory and purchase orders...
A database used for a toy store that keeps track of the inventory and purchase orders from the store. All clients will have their membership ID card to shop. This database will help the store to keep track of their orders. It will also help the store to know how many products are available in the inventory. It is necessary to have a membership to purchase a product. The customer can also bring coupons to get discount. Key attributes: Salesman:...
A database used for a toy store that keeps track of the inventory and purchase orders...
A database used for a toy store that keeps track of the inventory and purchase orders from the store. All clients will have their membership ID card to shop. This database will help the store to keep track of their orders. It will also help the store to know how many products are available in the inventory. It is necessary to have a membership to purchase a product. The customer can also bring coupons to get discount. Key attributes: Salesman:...
In using a database created from this model, which of the following SQL queries would give...
In using a database created from this model, which of the
following SQL queries would give us a list of customers who have
daily deposit transactions (deposit is a
transaction type) totaling $10,000 or more across all of
their accounts (i.e., this is a total across all accounts of a
customer, not per account). Include in this list the Customer ID
and name, and the dates and their respective daily total.
SELECT c.cust_id, c.cust_name, DATE(t.tran_date), SUM(t.tran_amount) AS daily_total
FROM Customers...
Display all customers. If a customer has placed any orders, also display the highest..... Tables: CREATE TABLE Sales.Cu...
Display all customers. If a customer has placed any orders, also
display the highest.....
Tables:
CREATE TABLE Sales.Customers
(
CustomerId
INT
NOT NULL
IDENTITY,
CustomerName
NVARCHAR(50)
NOT NULL,
StreetAddress NVARCHAR(50) NULL,
City
NVARCHAR(20)
NULL,
[State]
NVARCHAR(20)
NULL,
PostalCode
NVARCHAR(10)
NULL,
Country
NVARCHAR(20)
NULL,
Contact
NVARCHAR(50)
NULL,
Email
NVARCHAR(50)
NULL,
CONSTRAINT PK_Customers PRIMARY KEY(CustomerId)
);
CREATE TABLE HR.Employees
(
EmployeeId
INT
NOT NULL
IDENTITY,
FirstName
NVARCHAR(50) NOT
NULL,
LastName
NVARCHAR(50)
NOT NULL,
BirthDate DATE
NOT NULL,
HireDate
DATE
NOT NULL,
HomeAddress...
Create the following SQL Server queries that access the
Northwind database
Same as problem 3 but, limit the list to all customers who
placed 3 or more orders.
The product id, product name, last date that the product was
ordered for all items in the Grains/Cereals category. (Hint: Use
MAX)
The product ID, product name, and number of distinct customers
who ordered that product in 1996
Thank you.
Categories Customers Employees Order Details Orders Products Shippers 9 CategoryID CategoryName Description...
Using Microsoft SQL Server
2 2020-05-29 Describe how SQL queries work USE Adventure Works2012 example no 1 USE Adventure Works2012 GO SELECT AVG(TotalDue) as AVG_TotalDue FROM [Sales) (SalesOrder Header) example no 2 SELECT SalesOrderID Order Date. TotalDue st Name AS TerritoryName. SELECT AVG(TotalDue) FROM (Sales) (SalesOrder Header) ) AS AVG_TotalDue FROM [Sales) (SalesOrderHeader) soh inner join (Sales] [Sales Territory) st ON soh TerritoryID = st.TerritoryID WHERE st Country RegionCode="GB' and Order Date between '2004-06-01' and '2012-06-30 and TotalDue > SELECT...
In using a database created from this model, which of the
following SQL queries would give us a list of customers who have
daily deposit transactions (deposit is a
transaction type) totaling $10,000 or more across all of
their accounts (i.e., this is a total across all accounts of a
customer, not per account). Include in this list the Customer ID
and name, and the dates and their respective daily total.
SELECT c.cust_id, c.cust_name, DATE(t.tran_date), SUM(t.tran_amount) AS daily_total
FROM Customers...
Display all customers. If a customer has placed any orders, also
display the highest.....
Tables:
CREATE TABLE Sales.Customers
(
CustomerId
INT
NOT NULL
IDENTITY,
CustomerName
NVARCHAR(50)
NOT NULL,
StreetAddress NVARCHAR(50) NULL,
City
NVARCHAR(20)
NULL,
[State]
NVARCHAR(20)
NULL,
PostalCode
NVARCHAR(10)
NULL,
Country
NVARCHAR(20)
NULL,
Contact
NVARCHAR(50)
NULL,
Email
NVARCHAR(50)
NULL,
CONSTRAINT PK_Customers PRIMARY KEY(CustomerId)
);
CREATE TABLE HR.Employees
(
EmployeeId
INT
NOT NULL
IDENTITY,
FirstName
NVARCHAR(50) NOT
NULL,
LastName
NVARCHAR(50)
NOT NULL,
BirthDate DATE
NOT NULL,
HireDate
DATE
NOT NULL,
HomeAddress...
Most questions answered within 3 hours.
-
Based on the range, which of the following sets of scores has
the greatest variability? 3,...
asked 35 minutes ago -
Ripples in a pond travel at a velocity of 3 m/s with one peak
passing a...
asked 26 minutes ago -
A man stands on the roof of a building of height 13.0 mm and
throws a...
asked 32 minutes ago -
The extent to which assets are financed by borrowed funds and
other liabilities is indicated by:...
asked 1 hour ago -
Explain in detail
Germany is the fifth largest economy
explain what goods and services Germany specializes...
asked 1 hour ago -
The density of platinum is 21.45 g/mL. If a cube of platinum
with a mass of...
asked 1 hour ago -
Accounts Receivable
Sales
A/R Posting
Extended Sales Invoice
Packing Slip
Compare invoice to packing slip 2...
asked 1 hour ago -
Michaella, age 23, is a full-time law student and is claimed by
her parents as a...
asked 1 hour ago -
Why are polymers not typically casted into products?
asked 2 hours ago -
When rolling a die 129 times, what is the probability of rolling
a 6 no more...
asked 2 hours ago -
4. A call option currently sells for $7.75. It has a strike
price of $85 and...
asked 2 hours ago -
1.
You need to prepare 10.0 liters of an acid aqueous solution with a
pH of...
asked 2 hours ago