5. Write a SELECT statement that returns the product_name and list_price columns

Question

5. Write a SELECT statement that returns the product_name and list_price columns from the Products table.

Return one row for each product that has the same list price as another product.

Hint: Use a self-join to check that the product_id columns aren’t equal but the list_price

columns are equal.

Sort the result set by product_name.

6. Write a SELECT statement that returns these two columns:

category_name       The category_name column from the Categories table product_id              The product_id column from the Products table

Return one row for each category that has never been used. Hint: Use an outer join and only return rows where the product_id column contains a null value.

7. Use the UNION operator to generate a result set consisting of three columns from the Orders table:

ship_status              A calculated column that contains a value of SHIPPED or NOT SHIPPED

order_id                  The order_id column order_date              The order_date column

If the order has a value in the ship_date column, the ship_status column should contain a value of SHIPPED. Otherwise, it should contain a value of NOT SHIPPED.

Sort the final result set by order_date.

Explanation / Answer

SELECT department_id, AVG(salary)

FROM employees

WHERE job_id <> 69879

GROUP BY job_id, department_id

HAVING AVG(salary) > 35000

ORDER BY department_id;

Which clauses restricts the result?

WHERE job_id <> 69879

HAVING AVG(salary) > 35000

Related Questions

Navigate

• Browse (All)
• Browse 5
• Subjects

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