3.4 Use subqueries to create a query that determine which customers placed orders for the least...
3.4 Use subqueries to create a query that determine which customers placed orders for the least expensive book (in terms of regular retail price) carried by JustLee Books.
Homework Answers
Tables for JustLee Books database:
Books
|
ISBN |
TITLE |
PUBLISH_DATE |
PUBLISH_ID |
COST |
RETAIL |
CATEGORY |
Author
|
AUTHOR_ID |
AUTHOR_FNAME |
AUTHOR_LNAME |
Book_ Author
|
ISBN |
AUTHOR_ID |
Customers
|
CUSTOMER_NO |
FNAME |
LNAME |
|
ADDRESS |
CITY |
STATE |
ZIP |
REFERRED |
REGION |
Orders
|
ORDER_NO |
CUSTOMER_NO |
ORDER_DATE |
SHIP_DATE |
S_CITY |
S_STATE |
S_ZIP |
SHIP_COST |
Order_Items
|
ORDER_NO |
ITEM_NO |
ISBN |
QUANTITY |
PAID |
Inorder to determine the customers who placed orders for the least expensive book carried by JustLee Books join Customers, Orders, Order_Items, Books tables. Then get the minimum retail price.
Query to determine which customers placed orders for the least expensive book carried by JustLee Books:
SELECT CUSTOMER_NO FROM Customers INNER JOIN Orders USING( CUSTOMER_NO) INNER JOIN Order_Items USING( ORDER_NO) INNER JOIN Books USING( ISBN) WHERE RETAIL=(SELECT MIN( RETAIL)FROM Books)
Add Answer to:
3.4 Use subqueries to create a query that determine which
customers placed orders for the least...
I need to create a SQL query that displays the names of the customers who purchased...
I need to create a SQL query that displays the names of the customers who purchased the book with the highest retail price in the database. Also I need to capitalize the first and last names. CUSTOMERS Table: CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL ORDERS Table: ORDER#, CUSTOMER#, ORDERDATE, SHIPDATE, SHIPSTREET, SHIPCITY, SHIPSTATE, SHIPZIP, SHIPCOST ORDERITEMS Table: ORDER#, ITEM#, ISBN, QUANTITY, PAIDEACH
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...
can someone solve these quick please and thank you! sql chapter 4 Query #1: List the...
can someone solve these quick
please and thank you!
sql chapter 4
Query #1: List the company name, contact name, contact title and
the phone number for customers who HAVE NOT put in an order. Use an
outer join.
Query #2: Create a listing displaying the employee first name,
last name and the full name (First name space Last Name) of the
person they report to (Supervisor). This is a self-join. If an
employee does not report to anyone then...
1. First create the books object array with data for 20 books(Don't use meaningless data like...
1. First create the books object array with data for 20 books(Don't use meaningless data like "abc" etc). Make sure there are books from Canada, USA, UK and maybe a few other countries. (define this in global scope) Make sure that you have the following information for the books in the books object: name, author, price & origin Origin will be the name of the country from which the book was published. So examples are "US", "Canada", "UK" etc. 3....
484 Core Concepts of Accounting Information Systems a. Using Access, create an employee table sim- ilar...
484 Core Concepts of Accounting Information Systems a. Using Access, create an employee table sim- ilar to the one shown in Figure 15-4. Create created. (Hint You can use the aforemen- tioned expression for the department code as a validation rule.) at least one record for this table b. Create validation rules for the listed restric tions and an accompanying validation text for each. Document your work by provid- ing a table outlining exactly what rules you c. Test each...
Oracle 12c SQL Chapter 12 Determine which orders were shipped to the same state as order...
Oracle 12c SQL Chapter 12 Determine which orders were shipped to the same state as order 1014. CREATE TABLE Orders (Order# NUMBER(4), Customer# NUMBER(4), OrderDate DATE NOT NULL, ShipDate DATE, ShipStreet VARCHAR2(18), ShipCity VARCHAR2(15), ShipState VARCHAR2(2), ShipZip VARCHAR2(5), ShipCost NUMBER(4,2), CONSTRAINT orders_order#_pk PRIMARY KEY(order#), CONSTRAINT orders_customer#_fk FOREIGN KEY (customer#) REFERENCES customers(customer#)); INSERT INTO ORDERS VALUES (1000,1005,TO_DATE('31-MAR-09','DD-MON-YY'),TO_DATE('02-APR-09','DD-MON-YY'),'1201 ORANGE AVE', 'SEATTLE', 'WA', '98114' , 2.00); INSERT INTO ORDERS VALUES (1001,1010,TO_DATE('31-MAR-09','DD-MON-YY'),TO_DATE('01-APR-09','DD-MON-YY'), '114 EAST SAVANNAH', 'ATLANTA', 'GA', '30314', 3.00); INSERT INTO ORDERS VALUES (1002,1011,TO_DATE('31-MAR-09','DD-MON-YY'),TO_DATE('01-APR-09','DD-MON-YY'),'58...
If possible use Microsoft Visio or anything BUT handwritten diagrams for questions 6-10, please. Thank you!...
If possible use Microsoft Visio or anything BUT handwritten diagrams for questions 6-10, please. Thank you! This is for my Oracle class. 1. Which tables and fields would you access to determine which book titles have been purchased by a customer and when the order shipped? 2. How would you determine which orders have not yet been shipped to the customer? 3. If management needed to determine which book category generated the most sales in April 2009, which tables and...
use workbench please Task 1: Create a database and name it practical2DB. (2 marks) Task 2:...
use workbench please
Task 1: Create a database and name it practical2DB. (2
marks)
Task 2: Create all the four tables according to the relational
scheme in Figure 1. (8 marks)
Task 3: Insert 5 records to into each table. (5 marks)
Task 4: Write a query to list alphabetically ordered names,
addresses, and IDs of all the customers whose postcode is in
(40150, 40400, 47500).
(10 marks)
Task 5: Delete 1 record from Products table using their primary
keys....
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...
Presented below are the components in Veasy Company’s income statement. Determine the missing amounts. Sales Revenue...
Presented below are the components in Veasy Company’s income statement. Determine the missing amounts. Sales Revenue Cost of goods Sold Gross profit Operating expenses Net Income a. $ 75,000 a. ? a. $ 28,000 a. ? a. $ 9,800 b. $ 108,000 b. $70,000 b. ? b. ? b. $29,500 c. ? c. $ 83,900 c. $ 79,600 c. $ 39,500 c. ? Compute missing amounts in determining net income. Cha Company buys merchandise on account from Wirtz Company. The...
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...
can someone solve these quick
please and thank you!
sql chapter 4
Query #1: List the company name, contact name, contact title and
the phone number for customers who HAVE NOT put in an order. Use an
outer join.
Query #2: Create a listing displaying the employee first name,
last name and the full name (First name space Last Name) of the
person they report to (Supervisor). This is a self-join. If an
employee does not report to anyone then...
484 Core Concepts of Accounting Information Systems a. Using Access, create an employee table sim- ilar to the one shown in Figure 15-4. Create created. (Hint You can use the aforemen- tioned expression for the department code as a validation rule.) at least one record for this table b. Create validation rules for the listed restric tions and an accompanying validation text for each. Document your work by provid- ing a table outlining exactly what rules you c. Test each...
use workbench please
Task 1: Create a database and name it practical2DB. (2
marks)
Task 2: Create all the four tables according to the relational
scheme in Figure 1. (8 marks)
Task 3: Insert 5 records to into each table. (5 marks)
Task 4: Write a query to list alphabetically ordered names,
addresses, and IDs of all the customers whose postcode is in
(40150, 40400, 47500).
(10 marks)
Task 5: Delete 1 record from Products table using their primary
keys....
Most questions answered within 3 hours.
-
Economists believe that there are many nonmonetary gains from
further trade opening. Which of the following...
asked 8 minutes ago -
For message exchange over the Internet, the specific processes
that send and receive packets are identified...
asked 1 minute ago -
When exposed to sunlight, a hole in a sheet of copper expands in
diameter by 0.160...
asked 6 minutes ago -
How would
you create a group that promotes multicultural
understanding?
Should a
session conducted with youth...
asked 8 minutes ago -
A 2-kg block sits on a horizontal table. There is a hole in the
table directly...
asked 21 minutes ago -
If the rate law for the reaction A ⟶ Products is rate = k[A]2 ,
calculate...
asked 25 minutes ago -
A
projectile is shot horizontally at 23.4 m/s from the roof of a
building 55 m...
asked 39 minutes ago -
Which of the following ratios would be considered a measurement
of short-term liquidity?
Select one:
a....
asked 41 minutes ago -
Nitrogen dioxide is one of the many oxides of nitrogen (often
collectively called "NOx") that are...
asked 50 minutes ago -
1/ Using ________________ as they are intended is the best
guarantee for consistency and forward compatibility....
asked 1 hour ago -
Portfolio theory
Given the following information about firm A and the
market:δ
E (Rm)= 12 per...
asked 57 minutes ago -
Albert files his income tax return (showing a total tax of
$23,000) 5½ months after the...
asked 1 hour ago