Assignment I
docx
keyboard_arrow_up
School
Toronto Metropolitan University *
*We aren’t endorsed by this school
Course
110
Subject
Information Systems
Date
Dec 6, 2023
Type
docx
Pages
5
Uploaded by MasterInternet12667
Part II: [Total Points: 70]
Notes
For each of the following 7 questions: Write your answer as a MySQL query statement, execute
it, and take a screenshot of the results. If the query result has more than 10 records, limit the
output to the top 10 before taking the screenshot. Then, paste the answer and the respective
screenshot into a Word or Google document file with the question number.
• Submission: Submit your answer sheet for this part either in PDF or DOCX file format.
1. [10 pts.] Find all flowers that are either red and bloom in the summer.
MySQL statement:
SELECT * FROM flowerdb.flowers
WHERE Color = 'Red' and Season = 'Summer';
2. [10 pts.] Find all shops that have red flowers in stock.
SELECT
s.*
FROM flowerdb.flowershopinventory fsi
inner join flowers f on f.FlowerID = fsi.FlowerID
inner join shops as s on s.ShopID = fsi.ShopID
where f.Color = 'Red' and fsi.QuantityInStock > 0
3. [10 pts.] Find the names of all shops that have in stock only two flowers from each family.
SELECT distinct s.ShopName
FROM flowerdb.flowershopinventory fsi
inner join shops as s on s.ShopID = fsi.ShopID
where
(select Count(*) FROM flowers WHERE FamilyID = fsi.FamilyID) = 2
AND fsi.QuantityInStock > 0
4. [10 pts.] List the names of all flowers along with their family names, excluding those in stock
in shops located in Toronto, Montreal, or Ottawa.
SELECT
fsi.QuantityInStock,f.FlowerName,fam.FamilyName, s.Location
FROM flowerdb.flowershopinventory fsi
inner join flowers f on f.FlowerID = fsi.FlowerID
inner join shops as s on s.ShopID = fsi.ShopID
inner join families as fam on fam.FamilyID = fsi.FamilyID
Where Location not in ('Toronto', 'Montreal', 'Ottawa') AND QuantityInStock > 0;
5. [10 pts.] Find the average quantity in stock for each flower available in shops located in Toronto.
SELECT fsi.FlowerID, s.Location, Avg(QuantityInStock) as Stock_avg
FROM flowerdb.flowershopinventory fsi
inner join shops as s on s.ShopID = fsi.ShopID
group by fsi.FlowerID
having Stock_avg > 0 AND Location = 'Toronto';
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
6. [10 pts.] Find the families that have more than 2 types of flowers. Return the Family ID and
the count of flower types.
SELECT
fam.FamilyID, count(FlowerID) as count_flowers_type
FROM flowers f
inner join families as fam on fam.FamilyID = f.FamilyID
group by f.FamilyID
having count_flowers_type > 2;
7. [10 pts.] Find the family ID with the highest average quantity of flowers in stock across all
shops.
SELECT fsi.FamilyID, AVG(QuantityInStock) as Average_Qty
FROM flowershopinventory as fsi
group by fsi.FamilyID
ORDER by Average_Qty DESC
limit 1
Here is a link for more details on how to submit your assignment to the course shell:
https://www.torontomu.ca/courses/students/tutorials/assignments/
This is the end of the assignment.