CSDA5240_Assignment02
docx
keyboard_arrow_up
School
Webster University *
*We aren’t endorsed by this school
Course
CSDA 5210
Subject
Information Systems
Date
Apr 3, 2024
Type
docx
Pages
7
Uploaded by DukeHawkPerson951
Name: Madhukar Goud Kasani
CSDA 5240 Database Programming
Week 2 Assignment
Instructions
: Using the following tables from the Order Entry database, formulate SQL statements to support the data retrieval requirements for each problem statement. Write the answer to each question below each question. Consider question one below as an example. 1) Single table problems.
List the customer number, the name, and the balance of customers who reside in Colorado.
SELECT < column information>
FROM <table(s)>
WHERE <conditions>
SELECT CustNo, CustFirstName, CustLastName, CustBal
FROM CUSTOMER
WHERE CustState = ‘Colorado’;
List all columns of the OrderTbl table for Internet orders placed in January 2021. An Internet order does not have an associated employee.
SELECT < columns information>
FROM <table(s)>
WHERE <conditions>
SELECT *
FROM OrderTbl
WHERE year(OrdDate) = 2021
AND MONTH(OrdDate) = 1
AND EmpNo IS NULL;
CUSTOMER (CUSTNO
, CUSTFIRSTNAME, CUSTLASTNAME, CUSTSTREET, CUSTCITY, CUSTSTATE, CUSTZIP, CUSTBAL)
EMPLOYEE (EMPNO
, EMPFIRSTNAME, EMPLASTNAME, EMPPHONE, EMPEMAIL, SUPEMPNO, EMPCOMMRATE)
FK: SUPEMPNO
EMPLOYEE
PRODUCT (PRODNO
, PRODNAME, PRODMFG, PRODQOH, PRODPRICE, PRODNEXTSHIPDATE)
ORDERTBL (ORDNO
, ORDDATE, CUSTNO, EMPNO, ORDNAME, ORDSTREET, ORDCITY, ORDSTATE,
ORDZIP)
FK: CUSTNO
CUSTOMER
FK: EMPNO
EMPLOYEE
ORDLINE (ORDNO, PRODNO
, QTY)
FK: ORDNO
ORDERTBL
FK: PRODNO
PRODUCT
1.
Single table problems.
a.
List the customer number, the name, and the balance of customers who reside in Colorado.
SELECT CustNo, CustFirstName, CustLastNmae, CustBal
FROM CUSTOMER
WHERE CustState = ‘Colorado’;
b.
List all columns of the OrderTbl table for Internet orders placed in January 2021. An Internet order does not have an associated employee.
SELECT *
FROM OrderTbl
WHERE year(OrdDate) = 2021
AND MONTH(OrdDate) = 1
AND EmpNo IS NULL;
2.
Single table problems.
c.
List the customer number, the name, and the balance of customers who reside in Colorado.
SELECT CustNo, CustFirstName, CustLastNmae, CustBal
FROM CUSTOMER
WHERE CustState = ‘Colorado’;
d.
List all columns of the OrderTbl table for Internet orders placed in January 2021. An Internet order does not have an associated employee.
SELECT *
FROM OrderTbl
WHERE year(OrdDate) = 2021
AND MONTH(OrdDate) = 1
AND EmpNo IS NULL;
3.
Joining tables.
a.
List the order number, order date, customer number, and customer name of orders placed in January 2021 sent to Colorado recipients.
SELECT OrderTbl.OrdNo, OrderTbl.OrdDate, OrderTbl.CustNo, CONCAT(Customer.CustFirstName, “ ”, Customer.CustLastName) AS CustName
FROM CUSTOMER INNER JOIN ORDERTBL
ON CUSTOMER.CUSTNO = ORDERTBL.CUSTONO
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
WHERE MONTH(OrdDate) = 1 AND YEAR(OrdDate) = 2021 AND Customer.CustState = ‘Colorado’;
b.
List the product number, name, and price of products on order number “O3331222”.
SELECT Product.ProdNO, Product.ProdName, Product.ProdPrice
FROM PRODUCT LEFT JOIN ORDLINE
ON Product.ProdNO = OrdLine.ProdNo
WHERE OrdLine.OrdNo = “O3331222”;
4.
Summarizing tables.
a.
List the average balance of customers by city. Include only customers residing in Washington state.
SELECT CustCity, AVG(CustBal) AS AverageBal
FROM CUSTOMER
WHERE CustState = “Washington”
GROUP BY CustCity;
b.
List the product number, product name, sum of the quantity of products ordered, and total order amount (sum of the product price times the quantity). Only include products that have more than five products (sum of quantity) ordered. Sort the result by product number.
SELECT Product.ProdNo, ProdName, SUM(OrdLine.Qty), SUM(Product.ProdPrice * OrdLine.Qty) AS “Total Order Amount”
FROM PRODUCT INNER JOIN OrdLine
ON PRODUCT.ProdNo = OrdLine.Prodno
GROUP BY OrdLine.Qty, ProdName
HAVING SUM(OrdLine.Qty) > 5
ORDER BY Product.ProdNo;
5.
Identifying errors.
a.
The following statement should find customers who have a balance greater than $80 and live in either Denver or Seattle. Correct the statement and explain the error(s) in the statement.
SELECT CustNo, CustFirstName, CustLastName, CustCity, CustBal
FROM Customer
WHERE CustCity = 'Seattle' OR CustCity = 'Denver' AND CustBal > 50;
SELECT CustNo, CustFirstName, CustLastName, CustCity, CustBal
FROM Customer
WHERE (CustCity = 'Seattle' OR CustCity = 'Denver') AND CustBal > 80;
b.
The following statement should retrieve details about employees who took orders from customers
residing in Washington state with a balance greater than $300. Identify errors and correct the statement.
SELECT DISTINCT Employee.EmpNo, EmpFirstName, EmpLastName, EmpPhone
FROM OrderTbl, Customer, Employee, OrdLine
WHERE CustBal > 300 AND CustState = 'WA'
AND OrderTbl.CustNo = Customer.CustNo
AND OrderTbl.EmpNo = Employee.EmpNo
AND OrdLine.OrdNo = OrderTbl.OrdNo;
SELECT DISTINCT Employee.EmpNo, Employee.EmpFirstName, Employee.EmpLastName, Employee.EmpPhone
FROM OrderTbl
JOIN Customer ON OrderTbl.CustNo = Customer.CustNo
JOIN Employee ON OrderTbl.EmpNO = Employee.EmpNO
JOIN OrdLine ON OrdLine.OrdNo = OrderTbl.OrdNo
WHERE Cusomer.CustBal > 300 And Customer.CustState = ' Washington';
6.
Joining multiple tables.
a.
List the product number, name, and price of products ordered by customer number C0954327 in January 2021. Remove duplicate products in the result.
SELECT DISTINCT Product.ProdNo, Product.ProdName, ProdPrice
FROM Product INNER JOIN OrdLine ON Product.ProdNo = OrdLine.ProdNO
INNER JOIN OrderTbl ON OrdLine.OrdNo = OrderTbl.OrdNo
WHERE OrderTbl.CustNo = ‘C0954327’ AND (MONTH(OrderTbl.OrdDate) = 1 AND
YEAR(OrderTbl.OrdDate) = 2021);
b.
List the order number, order date, customer name, and total amount for orders placed on January 23, 2021. The total amount of an order is the sum of the quantity times the product price of each product on the order.
SELECT OrderTbl.OrdNo, OrderTbl.OrdDate, CONCAT(Customer.CustFirstName, “ “, Customer.CustLastName) AS CustName, SUM(Product.ProdPrice * OrdLine.Qty) AS TotalAmount
FROM OrderTbl
INNER JOIN OrdLine ON OrderTbl.OrdNo = OrdLine.OrdNo
INNER JOIN Customer ON OrderTbl.CustNo = Customer.CustNo
WHERE OrderTbl.OrdDate = ‘2021-01-23’
GROUP BY Product.ProdPrice, OrdLine.Qty
c.
List employees who have a commission rate greater than 75 percent of their supervisor’s commission rate. The result should contain employee details (employee number, name, & commission rate) and supervisor details (employee number, name, & commission rate).
SELECT E1.EmpNo, CONCAT(E1.EmpFirstName, ‘ ’ , E1.EmpLastName) AS EmpName, E1.EmpCommRate, E2.EmpNo AS SupNO, CONCAT(E2.EmpFirstName, ‘ ’ , E2.EmpLastName) AS SupName, E2.EmpCommRate AS SupCommRate
FROM Employee E1
JOIN Employee E2 ON E1. SupEmoNo = E2.EmpNo WHERE E1.EmpCommRate > 0.75 * E2.EmpCommRate;
d.
Identify poor coding practices in the following statement and rewrite the statement with good coding practices. The query should list information of employees who were involved in orders in January 2021 and customer balance is over $300.
SELECT DISTINCT Employee.EmpNo, EmpFirstName, EmpLastName, EmpPhone FROM OrderTbl INNER JOIN Customer ON OrderTbl.CustNo = Customer.CustNo, Employee WHERE CustBal > '300' AND OrdDate LIKE '1/%/2021'
AND OrderTbl.EmpNo = Employee.EmpNo;
SELECT
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
DISTINCT Employee.EmpNo, EmpFirstName, EmpLastName, EmpPhone
FROM OrderTbl
INNER JOIN Customer ON OrderTbl.CustNo = Customer.CustNO INNER JOIN Employee ON OrderTbl.EmpNo = Employee.EmpNo
WHERE Customer.CustBal > 300 AND OrdDate LIKE '1/%/2021';