Question

Each member of the group will develop 20 problem statements (propositions) and develop the solution.

use WideWorldImpostersDW;

All queries must use the ANSI 92 standard for queries with the type safe "on".

1. All queries2 will have relational output as well as a formatted JSON component.

a. Simple query should have up to 2 tables joined. The tables can be physical tables or views.
b. Medium query should have from 2 to 3 tables joined and use built-in SQL functions and group by

summarization. It should include combinations of subqueries or CTE or virtual tables.
c. Complex query should have from 3 or more tables joined, custom scalar function and use built-in SQL functions and group by summarization. It should include combinations of subqueries or CTE

or virtual tables.

Answer 1

The problem statements for the 20 queries will be :

Simple Queries: Find the total number of imposters in the database. List all imposters who have been active in the past two weeks.

Medium Queries: Find the average number of missions completed per imposter. List the top 10 imposters with the most successful missions.

Complex Queries: Find the average time it takes for imposters to complete a mission, grouped by mission type. Identify the imposter with the highest success rate in eliminating crewmates.

What are the queries for the problem statements?

1. sql

SELECT

customers.customer_id,

customers.customer_name,

orders.order_id,

orders.order_date

FROM

WideWorldImpostersDW.customers

JOIN

WideWorldImpostersDW.orders ON customers.customer_id = orders.customer_id;