Answer Happy

You are provided below a design of a relational database for a non-profit organization that is concerned with the welfare of poor people in their community. It accepts donations from people. Donations could be either money or products. It processes the items and sell them to the local people who are interested in them. Part of the donations is used to cover the expenses to run the organization and the rest is used to help poor people in the community. The database contains information about Branches, Donors, Donations, Products, and Sales. Branches is the relation that holds information about each branch of the organization. Every branch has an ID, name, address, city, postalCode, province, phone, and email. Donors is the relation that holds information about the people who donate to the organization. Every donor has an ID, first-name, last-name, middle-initial, date-of-birth, gender, social security number, address, city, postalCode, province, phone, and email. Donations is the relation that holds information about each donation. Every donation has a branch ID, an ID that is an incremental number for each branch, the donor ID, date of the donation, type of donation, and amount of donation. Products is the relation that holds information about every item donated. Every product has a branch ID, an ID that is an incremental number for each branch, the description of the product, the donation date of the product, and the selling price of the product. The branch ID indicates at which branch the product has been donated. Sales is the relation that holds the information about the items sold. Every sale has branch ID, an ID that is an incremental number for each branch, date of the sale, and amount of the sale. Some information about how this organization runs: The organization has many branches. Each donation is done at a specific branch.
A donation type can be either money or products. If donation at a branch is products, then the estimated selling price of all the donated products at the branch is registered. A donor can have many donations throughout the year. A donor can donate at one or many branches. • One sale in each branch can include one or many products. • The total amount of all products sold in one sale in each branch is registered. Every item that is sold in each branch is removed from the products relation. Each branch has its own inventory. Each branch has its own sales. The database schema is as follows, where the underlined attribute(s) in each relation collectively form the primary key of that relation: 1. Branches (bID, bName, address*, city, postalCode, province, phone, email) 2. Donors (donorID, firstName, lastName, middleInitial, dateOfBirth, gender, SSN, address*, city, postalCode, province, phone, email) 3. Donations (bID, dID, donorID, date, type, amount) 4. Products (bID, pID, description, date, price) 5. Sales (bID, SID, date, amount) * Address consists of civic number.
Express the following queries in SQL: a) List the information of all the Branches of the organization that are located in the province of Québec. Information includes branch ID, branch name, address, city, postal code, and phone. b) For all the Donors who lives in the province of Québec and have at least five donations in 2022, give the total amount of donations they donated in 2022. Result should be displayed in ascending order of total amount donated. c) Give a report of the donations done at all the branches in 2021. The report includes branch name, branch ID, city, total donations at the branch in 2021. The report should be displayed in decreasing order of the total donations in each branch. d) Give a monthly report of sales for all branches of the city of laval for 2021. The report includes for every month in 2021, the total sales of the month. e) Give a list of all the products that have been donated for more than one year and are not sold at every branch in the city of Montréal. The list should include the branch name, the product ID, the description, the date of the donation, and the sales amount of the product. The result should be displayed in increasing order of branch name, then product ID.