Question

Write SQL queries below for each of the following:

1. List the names and cities of all customers

2. List the different states the vendors come from (unique values only, no duplicates)

3. Find the number of customers in California

4. List product names and category descriptions for all products supplied by vendor Proformance

5. List names of all employees who have sold to customer Rachel Patterson

Answer 1

Let us assume that the we have table 1 named "customer" with attributes such as, customer_name, cutomer_city, employee_name.

Let us assume that the we have table 2 named "product" with attributes such as, product_name, product_descrption, vendor_name, vendor_state.

Sample Table : customer

customer_name	cutomer_city	employee_name
Rachel Patterson	California	Robert Downey
Johny Depp	Ohio	Chris Hemsworth
Phil Coulson	New York	Clint Barton
Chris Evans	Miami	Edwin Jarvis
Bruce Banner	Los Angels	Tom Hiddleston

Sample Table : product

product_name	product_descrption	vendor_name	vendor_state
Item 1	Laptop	Proformance	Boston
Item 2	Tablet	Logtech	Memphis
Item 3	Mobile Phone	Tele World	Chicago
Item 4	Smart Watch	Apple	Kansas
Item 5	External Hard Disk	Seagate	Seattle

1. List the names and cities of all customers.

Query:

SELECT customer.customer_name, customer.customer_city FROM customer;

customer_name	cutomer_city
Rachel Patterson	California
Johny Depp	Ohio
Phil Coulson	New York
Chris Evans	Miami
Bruce Banner	Los Angels

2. List the different states the vendors come from.

Query:

SELECT product.vendor_state FROM product;

vendor_state

Boston

Memphis

Chicago

Kansas

Seattle

3. Find the number of customers in California.

Query:

SELECT COUNT(customer_city) FROM customer WHERE customer.customer_city='California';

COUNT(customer_city)

1

4. List product names and category descriptions for all products supplied by vendor Proformance.

Query:

SELECT product.product_name, product.product_description FROM product WHERE product.vendor_name='Proformance';

product_name	product_descrption	vendor_name
Item 1	Laptop	Proformance

5. List names of all employees who have sold to customer Rachel Patterson.

Query:

SELECT customer.employee_name from customer WHERE customer.customer_name='Rachel Patterson';

employee_name

Robert Downey