Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Needing help with my SQL ( for Postgres ) . Errors at end. Tasks: 1 . What is the full name, full address, title, and

Needing help with my SQL (for Postgres). Errors at end. Tasks:
1. What is the full name, full address, title, and phone number for all employees currently living in a US state that shares a border with the Pacific Ocean?
2. Give the same information for all employees not in the USA but are older than 50 years of age.
3. Which employees (last name, first name without repeats) have placed orders delivered to Norway?
4. What is the title and name of any employee that has sold at least two of either "Vegie-spread" or "Sir Rodneys Marmalade"?
5. hat are the Employee IDs for all employees who have sold more than 70 products?
6. List the last name of all employees that live on the same city as their managers.
7. List the product names of all products that were bought OR sold by people who live in London (use a UNION).
8.What is the average price of products for each category? Output just the average price as "average_price" and the category ID.
Script:
-- Question 1: Full name, full address, title, and phone number for employees living in a US state bordering the Pacific Ocean
SELECT CONCAT(first_name, '', last_name) AS full_name,
CONCAT(address,',', city, ',', region, '', postal_code) AS full_address,
title,
phone_number
WHERE country = 'USA'
AND region IN ('Washington', 'Oregon', 'California', 'Alaska', 'Hawaii');
-- Question 2: Full name, full address, title, and phone number for employees not in the USA but older than 50
SELECT CONCAT(first_name, '', last_name) AS full_name,
CONCAT(address,',', city, ',', region, '', postal_code) AS full_address,
title,
phone_number
FROM employees
WHERE country != 'USA'
AND age >50;
-- Question 3: Employees (last name, first name without repeats) who have placed orders delivered to Norway
SELECT DISTINCT last_name, first_name
FROM employees e
JOIN orders o ON e.employee_id = o.employee_id
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country = 'Norway';
-- Question 4: Title and name of employees who have sold at least two of "Vegie-spread" or "Sir Rodneys Marmalade"
SELECT title, CONCAT(first_name, '', last_name) AS employee_name
FROM employees e
JOIN orders o ON e.employee_id = o.employee_id
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
WHERE p.product_name IN ('Vegie-spread', 'Sir Rodneys Marmalade')
GROUP BY e.employee_id
HAVING COUNT(*)>=2;
-- Question 5: Employee IDs for employees who have sold more than 70 products
SELECT employee_id
FROM orders
GROUP BY employee_id
HAVING SUM(quantity)>70;
-- Question 6: Last name of employees living in the same city as their managers
SELECT e.last_name
FROM employees e
JOIN employees m ON e.reports_to = m.employee_id
WHERE e.city = m.city;
-- Question 7: Product names bought or sold by people who live in London (using UNION)
(SELECT p.product_name
FROM products p
JOIN order_details od ON p.product_id = od.product_id
JOIN orders o ON od.order_id = o.order_id
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.city = 'London')
UNION
(SELECT p.product_name
FROM products p
JOIN customer_demographics cd ON p.product_id = cd.product_id
JOIN customers c ON cd.customer_id = c.customer_id
WHERE c.city = 'London');
-- Question 8: Average price of products for each category
SELECT AVG(unit_price) AS average_price, category_id
FROM products
GROUP BY category_id;
Errors:
Unable to resolve column 'phone_number' :5
Unable to resolve column 'phone_number' :14
Unable to resolve column 'age' :17
Unable to resolve column 'quantity' :40
Unable to resolve column 'product_id' :58
Unable to resolve column 'customer_id' :59
Thanks in advance!

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Finance The Role Of Data Analytics In Manda Due Diligence

Authors: Ps Publishing

1st Edition

B0CR6SKTQG, 979-8873324675

More Books

Students also viewed these Databases questions