Question

MYSQL Applied Database

I just want to make sure that the syntax looks okay for my database SQL statements, any help would be much appreciated:

1.A query that asks for customer star review for Tony Stark on Opportunity Detail.

SELECT CONCAT (employee_first_name,' ',employee_last_name) AS    

                       employee_name, review_star_count,

FROM customer_reviews,

JOIN e employees ON employee_id =employee_id,

WHERE employee_id=4 AND opportunity_id=1;

2.A query that shows how many orders Additional Dot Twain License, in an order totaling over $900.

SELECT product_name, COUNT(o.OrderID) AS OrderCount,

total_price,

FROM Customers c

JOIN orders o ON c.customer_id = o.customer_id

JOIN order_items oi ON o.order_id = oi.order_id

WHERE ItemPrice > 900

GROUP BY product_name

HAVING COUNT(o.order_id) > 1

ORDER BY total_price DESC;

3.A query that produces a list of catering costs for the company that are over $150 between the dates of 1-1-2018 to 12-31-2018.

SELECT invoice_total, vendor_name

FROM invoices i

JOIN vendors v ON i.vendor_id=v.vendor_id,

WHERE invoice_total>150

BETWEEN 1-1-2018 AND 12-31-2018

GROUP BY vendor_name;

4.A query that shows which employees have been on more than one opportunity detail.

SELECT CONCAT(employee_first_name,' ', employee_last_name) AS employee_name, opprotunity_id

FROM employees e

JOIN opportunity o ON o.opprotunity_owner=e.employee_id

WHERE o.opprotunity_owner >1;

5.A query that shows when the company's intern was hired.

SELECT CONCAT (hire_month,' ', hire_year) AS employee_hire_date, CONCAT (employee_first_name,' ',employee_last_name)AS employee_name

FROM employee

WHERE employee_id=6;

6.A query that shows the total amount spent on rent for the year 2017

SELECT SUM(payment_total)

FROM invoice_archive

WHERE vendor_id=4;

7.A query that asks how many employees are in the sales department

SELECT department_name,

(SELECT COUNT(*),

FROM employees e,

WHERE e.department_id=department_id) AS employees_number,

FROM department;

8.A query that shows which customers have ordered more than one product (that's not specialized)?

SELECT customer_business_name

COUNT(DISTINCT oi.product_id) AS number_of_products

FROM customers c

JOIN orders o ON c.customer_id = o.customer_id

JOIN order_items oi ON o.order_id = oi.order_id

HAVING COUNT(DISTINCT oi.product_id) > 1

ORDER BY customer_business_name;

9.A query that shows the closing date for each opportunity

SELECT opprotuniyt_id, closing_date

FROM opportunity

GROUP BY opportunity_id;

10.A query that shows all products for Nelson and Murdock Law Offices

SELECT customer_business_name, product_name

FROM order_items oi

JOIN products p ON p.product_code=oi.product_code

JOIN orders o ON o.order_number=oi.order_number

JOIN customers c ON c.customer_account_number = o.customer_account_number

GROUP BY customer_business_name, product_name

HAVING COUNT(product_code);