Question

1. Write a SELECT statement to count SKUs for each department, i.e., a table of two columns, departments and their SKU counts.

use cape_codd;
select department, count(*)
from sku_data
group by department
;

2. List buyers who is responsible for at least two SKUs, in three columns, department, buyer, sku count. Sort buyers by their department names in ascending order.

select department, buyer, count(sku)
from sku_data
group by buyer
;

3. Write a SQL statement which returns all the orders. For each order, display OrderNumber and the total SKUs in the order. (Hint: there are three order numbers: 1000,2000,3000)

select ordernumber, count(sku)
from order_item
where ordernumber
group by ordernumber
;

4. Write a SQL Join to retrieve total sales for the department "Water Sports". Use explicit JOIN ON syntax. (No credit if you use implicit syntax)

The expected result will be a table of two columns and one row:

department sum(extendedprice)

Water Sports 750

select department, sum(ExtendedPrice)
from order_item join sku_data
;

Answer 1

1. First question states that
Write a SELECT statement to count SKUs for each department, i.e., a table of two columns, departments and their SKU counts.

so that means we need to show each department with sku numbers in it.

so to form a query we need to select it from the table sku_datafrom which we need to select the nuber of skus in each departments

so if we think logically then we need to form a section where each department with sku counts

generally if we group the departnames and then take the count of each sku in it, it would be done easily

so to group each deparment in sql we have group by section

if you think it logially how db server works that first from table then group by the column then select the columns. but syntactically speeking :-

the query would look like:
use cape_codd;// use the dattabase to run a query on the database table
select department, count(*) as 'sku count' // selecting the department and number of columns associated with the department
from sku_data // from table
group by department // group by with department so that we can have number of columns in which the depart name are same

2.  List buyers who is responsible for at least two SKUs, in three columns, department, buyer, sku count. Sort buyers by their department names in ascending order.

there are multiple problems in it.

so first is listing buyers with sku count
for that we use group by

second is at least two sku counts:

in order to do that in addition to group by we can use having

next arrange it with department names in ascending order
for this we can use order by

so if we form a query then
select department, buyer, count(sku)
from sku_data
group by buyer
having count(sku)>=2
order by department ASC;

3rd Question: Write a SQL statement which returns all the orders. For each order, display OrderNumber and the total SKUs in the order. (Hint: there are three order numbers: 1000,2000,3000)

here also we need to show order numbers with sku counts also that means here also we can group by order numbers for sku count which is same as first question

select ordernumber, count(sku)
from order_item
group by ordernumber

4. Write a SQL Join to retrieve total sales for the department "Water Sports".

here we need to find the total sales for a single specific department
this question is also also formed with multiple question
1. we need to find total number of records in the sales table for department "Water Sports"
So, here as sku_data has department but the sales record is in order_item table so we need to join two tables to find out the number of records associated with it and there is only one column(sku) which is common in both the tables so we will use that column to join both the tables

2. total sales record
so that means we need to perform a sum operation orver the price coumn of department  "Water Sports"

so if we form the query, it would look like below

select department, sum(ExtendedPrice)
from order_item inner join sku_data on order_item.sku=sku_data.sku
where order_item.department='Water Sports';