I have finished my homework and I need someone to check my answers. Plus the las

Question

I have finished my homework and I need someone to check my answers. Plus the last question I know is wrong I do need help with that one for sure. My answers are below the homework questions. I have a link for the test database we use for Acess 2013 put out by my University. http://apcxcs3.apsu.edu/cs4400/nwind.zip

Query Name

Instructions

Query1

The customer ID, contact name, and city for all customers from London.

Query2

The product name, supplier name, and category name for all products that have been shipped to the USA.    Place the list in ascending order by product name.

Query3

The product ID, product name, and quantity ordered for all products ordered by a customer from London.   Products may be repeated on the list. Place the list is ascending order by product ID.

Query4

The order ID, customer ID, and destination city for all orders shipped by a shipper with ID 2.   The query should work if the ID is changed to some other value. Do not accept keyboard input.

Query5

The product ID, units in stock, and name for all products that were supplied by a supplier with ID 2. Place the list in descending order by units in stock.

Query6

The company name for all customers that purchased an item supplied by a supplier with ID 2.   List each name only once and place the names in alphabetical order.

Query 1

SELECT customerid, contactname, city
FROM customers
WHERE city = 'London';

Queery 2

SELECT p.productname, s.contactname, c.categoryname, s.country
FROM products AS p, suppliers AS s, categories AS c
WHERE s.country ='USA'
ORDER BY p.productname;

Query3

SELECT p.productid, p.productname, p.quantityperunit, c.city
FROM products AS p, customers AS c
WHERE c.city = 'London'
ORDER BY p.productid;

Query 4

SELECT O.OrderID, O.CustomerID, O.ShipCity
FROM Orders AS O
WHERE O.ShipCity =[Enter ID];

Query 5

SELECT P.ProductID, P.UnitsInStock, P.ProductName
FROM Products AS P
ORDER BY P.UnitsInStock DESC;

Query 6 this one i know is wrong

SELECT Suppliers.CompanyName, Suppliers.SupplierID, Orders.OrderDate
FROM Suppliers, Orders
ORDER BY Suppliers.CompanyName;

Query Name

Instructions

Query1

The customer ID, contact name, and city for all customers from London.

Query2

The product name, supplier name, and category name for all products that have been shipped to the USA.    Place the list in ascending order by product name.

Query3

The product ID, product name, and quantity ordered for all products ordered by a customer from London.   Products may be repeated on the list. Place the list is ascending order by product ID.

Query4

The order ID, customer ID, and destination city for all orders shipped by a shipper with ID 2.   The query should work if the ID is changed to some other value. Do not accept keyboard input.

Query5

The product ID, units in stock, and name for all products that were supplied by a supplier with ID 2. Place the list in descending order by units in stock.

Query6

The company name for all customers that purchased an item supplied by a supplier with ID 2.   List each name only once and place the names in alphabetical order.

Explanation / Answer

All Sql queries are correct and working properly.

Query 6 is also working but it show duplicate rows for that you can use DISTINCT keyword Like

SELECT DISTINCT Suppliers.CompanyName, Suppliers.SupplierID, Orders.OrderDate
FROM Suppliers, Orders
ORDER BY Suppliers.CompanyName;

But if you only need to see 'SupplierId' and 'CompanyName' then it will show correct result that i think you required. for this sql query is

SELECT DISTINCT Suppliers.CompanyName, Suppliers.SupplierID FROM Suppliers, Orders ORDER BY Suppliers.CompanyName;

Thanks.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.