1) Show customer name and contact name for all the customers from Germany. Show

Question

1) Show customer name and contact name for all the customers from Germany. Show me the results in sort order of contact name.

2) Show me the Order ID and Order Date for the orders that were shipped by “Speedy Express” (ShipperID = 1)

3) Show me the names of the products that are supplied either by “Exotic Liquid” (SupplierID = 1) or “Grandma Kelly's Homestead” (SupplierID = 3)

4) Show me the name and city of customers with names starting with letter “B”
5) Between the countries Germany and Mexico, where do we have more number of customers?
6) Show me the names of the Products that are of category “Beverages”

Customers CustomerId CustomerName ContactName Address PostalCode Country Shippers ShipperID ShipperName Phone Orders Order Id Customer Employeeld OrderDate ShipperID OrderDetails OrderDetailID Order ID ProductID Quantity Suppliers Supplier ID SupplierName Contact Name Address City PostalCode Country Phone Products ProductID ProductName upplierID Category Unit Price

Explanation / Answer

Hello,

I would show you how to achieve the above required queries with use of SQL Joins. Please see below,

1. For the 1st query to show customer name and contact name for all the customers from Germany sorted on contact name.

Select c.CustomerName as Customer Name, c.ContactName as Contact Name from Customers c where c.Country = 'Germany' Order by c.ContactName

2. For 2nd query to show the Order ID and Order Date for the orders that were shipped by “Speedy Express” (i.e. ShipperID = 1)

Select o.OrderId as Order Id, o.OrderDate as Order Date from Orders o where o.ShipperID = 1

3. For 3rd query to show the names of the products that are supplied either by “Exotic Liquid” (SupplierID = 1) or “Grandma Kelly's Homestead” (SupplierID = 3)

Select p.ProductName as Product Name from Products p where p.SupplierID = 1 or 3

4. For 4th query

Select c.CustomerName, c.City from Customers c where c.CustomerName LIKE 'B%'

5. For 5th query

SELECT CASE

WHEN Count (*) from Customers c WHERE c.Country = 'Germany' > Count (*) FROM Customers c WHERE c.Country = 'Mexico' THEN 1

ELSE 0

6. For 6 th query

Select p.ProductName as Product Name from Products p where p.CategoryId = 1 (Considering Category ID as '1' for Beverages)

Hope this was useful and please share the feedback on same.

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

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