How to code subqueries, Write a SELECT statement that

How to code subqueries

Assignment Help Management Information Sys

Reference no: EM131541851

Question: How to code subqueries

Exercises: 1. Write a SELECT statement that returns the same result set as this SELECT statement, but don't use a join. Instead, use a subquery in a WHERE clause that uses the IN keyword.

SELECT DISTINCT category_name

FROM categories c JOIN products p

ON c.category_id = p.category_id

ORDER BY category_name

2. Write a SELECT statement that answers this question: Which products have a list price that's greater than the average list price for all products?

Return the product_name and list_price columns for each product.

Sort the results by the list_price column in descending sequence.

3. Write a SELECT statement that returns the category_name column from the Categories table.
Return one row for each category that has never been assigned to any product in the Products table. To do that, use a subquery introduced with the NOT EXISTS operator.

4. Write a SELECT statement that returns three columns: email_address, order_id, and the order total for each customer. To do this, you can group the result set by the email_address and order_id columns. In addition, you must calculate the order total from the columns in the Order_Items table.

Write a second SELECT statement that uses the first SELECT statement in its FROM clause. The main query should return two columns: the customer's email address and the largest order for that customer. To do this, you can group the result set by the email_address.

5. Write a SELECT statement that returns the name and discount percent of each product that has a unique discount percent. In other words, don't include products that have the same discount percent as another product.

Sort the results by the product_name column.

6. Use a correlated subquery to return one row per customer, representing the customer's oldest order (the one with the earliest date). Each row should include these three columns: email_address, order_id, and order_date.

Reference no: EM131541851

Questions Cloud

Describe the labor market unemployment : During the last recession, people left the labor market - describe the labor market unemployment is described as a surplus

Has your organization conducted an ethics survey : Has your organization conducted an ethics survey? What actions were taken to improve the ethical climate in the organization as a result of it?

Discussion-communication and leadership : Analyze your own skills on the job and explain if you are readily open to feedback and criticism at work. Give a recent example of how you took criticism.

Write a research problem that would be best studied : Discuss why this research problem is best studied using a probability sample, and what type of probability sample you would likely use.

How to code subqueries : Write a SELECT statement that returns the same result set as this SELECT statement, but don't use a join. Instead, use a subquery in a WHERE clause.

Brief explanation of how the two project outlines differ : Brief explanation of how the two project outlines differ. Be sure to address how Agile addresses complexity and uncertainty.

Write a research paper on macra macra : Write a research paper on MACRA MACRA is the Medicare Access and CHIP Reauthorization Act. The paper should be 17 pages in length.

Corporate strategy play in an organization : What roles or purposes does corporate strategy play in an organization? Is a corporate strategy even necessary for a firm to be successful?

The training of police recruits : Prepare a presentation with your recommendations of the important points that should be addressed in the training of police recruits.

User Account

All Pages