Customer table CustomerID (PK). number FirstName (NOT NULL). varchar2 LastName (
ID: 3917129 • Letter: C
Question
Customer table
CustomerID (PK). number
FirstName (NOT NULL). varchar2
LastName (NOT NULL). varchar2
Address. varchar2
City. varchar2
State. varchar2
Zip. number
Country. varchar2
Phone. number
Email (UNIQUE and NOT NULL). varchar2
Username (UNIQUE and NOT NULL). varchar2
Password (NOT NULL). varchar2
Order table
OrderID (PK). number
CustomerID (FK). number
OrderDate. timestamp
ShipDate. timestamp
OrderDetail table
OrderDetailID (PK). varchar2
OrderID (FK). number
ProductID (FK). varchar2
Price. number
Quantity (CHECK > 0). number
Product Table
ProductID (PK). varchar2
CategoryID (FK). number
ProductName. varchar2
ProductDescription. varchar2
UnitPrice. number
Picture. blob
Category Table
CategoryID (PK). number
CategoryName. varchar2
Description. varchar2
ABOVE IS ALL THE TABLES IN COLUMN IN THE DATABASE
For this assignment, complete the following SQL statements:
A list of all customers ordered by last name, first name with the first name and last name concatenated together with a space in between.
A list of all orders with the date ordered output as MONTH DD, YYYY, HH:MI:SS.
A list of all products using the INITCAP on the product name.
A list of all orders that have been ordered in the current month.
A list of products with their prices rounded to the nearest dollar.
A list of the total price of each line item based on the price and quantity.
A list of all customers with the first name that starts with a specific letter (your choice).
A list of products with the cost more than $10.
A list of all of the customers with their only the first 4 characters with the last 4 padded with the * character.
A list of products with the picture path removing the full path and only including the filename.
Five additional queries that use single-row functions from each of the categories listed below. In addition to the query, explain what the query is meant to do as part of a SQL comment prior to the query:
Character Function.
Numeric Function.
DateTime Function.
Conversion Function.
NVL Function (you may need to remove data to make this work.)
Explanation / Answer
Answer)
SQL statements:
A list of all customers ordered by last name, first name with the first name and last name concatenated together with a space in between.
Answer)
select FirstName||' '||LastName from Customer order by FirstName, LastName;
A list of all orders with the date ordered output as MONTH DD, YYYY, HH:MI:SS.
Answer)
select OrderID, CustomerID, TO_CHAR(OrderDate, 'MONTH DD, YYYY, HH24:MI:SS'), ShipDate, OrderDetail, OrderDetailID, OrderID, ProductID, Price, Quantity from Order;
A list of all products using the INITCAP on the product name.
Answer)
select INITCAP(ProductName) from Product;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.