Construct an sql statement to retrieve a list of all product

Assignment Help Database Management System

Reference no: EM133745353

Assessment 1:

In this tutorial, we will focus on using SQL statements to perform data selection within the Northwind database, which was provided in Tutorial 3.

Task 1

Construct an SQL statement to find the total number of products in stock for each category

Task 2

Construct an SQL statement to retrieve a list of all product names along with the corresponding suppliers, this list is sorted by product names in ascending order.

Task 3

Construct an SQL statement to display a list of city names along with the number of customers in each city, sorted in descending order based on the number of customers.

Task 4

Construct an SQL statement to find the products that have the highest price within their categories. Display the product name, category name, and unit price.

Task 5

Construct an SQL statement to display the employee ID, first name, last name, and the number of territories he/she manages, with the results sorted by the number of territories in descending order.

Assessment 2:

Task 1

Generate sample data for the EliteVideo database, each table contains a minimum of 5 rows of data.

Make sure that one row in the ‘memberShip' table has your student ID (numeric part only) as the primary key and your full name as the name.

Make sure that there is a movie titled ‘Attack of the Killer Tomatoes' and another one named ‘Goosebumps'.

Make sure that there is a rental transaction for the member with your student ID on 15/08/2023 for ‘Attack of the Killer Tomatoes', ‘Goosebumps' and one other movie.

Make sure that the pricing structure includes a variety of price categories (such as ‘Overnight,' ‘2-Day Rental,' ‘3-Day Rental,' ‘Weekly,' etc.) and the cost of renting each movie will fall into one of these categories.

Task 2

Construct a SQL statement that retrieves the movie titles along with the count of rentals for each movie.

Task 3

Construct a SQL statement to display name, email, phone number and address of the members who have rented movies released after 01/01/2023.

Task 4

Construct a nested SQL statement using the EXISTS function to display members (memberID, name, address) who have rented at least one movie.

Task 5

Construct a nested SQL statement using the EXISTS function to display members (memberID, name, address) who have rented movies with a rating higher than 4 (Assuming that the scale of rating is from 1 to 5).

Reference no: EM133745353

Questions Cloud

Identify various sources of ideas for new ventures : Identify various sources of ideas for new ventures. Explain opportunity recognition and the need to change thinking about opportunities.

How is it possible to have an empowered workforce : How is it possible to have an empowered workforce, while at the same time mandating that all employees follow the same established policies and procedures?

Identify a problem related to your topic and state it : Identify a problem related to your topic and state it. The problem statement should be specific and indicate the focus of your final paper.

Do these products align with a strict gender binary system : Do these products play into any stereotypes about men and women? Do these products align with a strict gender binary system?

Construct an sql statement to retrieve a list of all product : HI5033 Database Design, Holmes Institute - Construct an SQL statement to retrieve a list of all product names along with the corresponding suppliers

Describe six specific things you learn from the three source : PSY 4190 Nashville State Community College- In complete, grammatical sentences, describe 6 specific things you learned from these three sources.

What is the impact of each sentencing structure : What is the impact of each sentencing structure when you are trying to achieve the ideology or purpose you have identified?

Does upbringing-influence their view on capital punishment : Is this a good exploratory research question? Does one's upbringing, influence their view on capital punishment?

Discuss the evolution of the securities markets : BUS 629 university of arizona global campus- Discuss the evolution of the securities markets, including the impact of NASDAQ, CME, ECNs and foreign exchanges.

User Account

All Pages