DBS$311$ Advanced Data Services Last update: Summer $2024$Advanced Databae Services Assignment $1$SubmissionYour submission will be a single text$-$based SQL file $(.$sql$)$ with appropriate header and commenting. Please ensure your file runs when the entire file is executed in SQL Developer. A submission format file$(.$sql$)$ is provided, you can fill your code in this format file. Name your submission file as A$1\_$Group#$.$sql$.$Make sure only one submission per group.Style GuideYour SQL should be written using the standard coding style: all keywords are to be upper case, all user$-$defined names are to be lower case, $($example: table and field names$)$ there should be a carriage return before each major part of the SQL statements $($i$.$e$.$ before SELECT,FROM, WHERE and ORDER BY$)$ See the following sample:SELECT columnsFROM tablesWHERE conditionsORDER BY column$1,$column$2$;Submission ChecklistUse the following checklist, to make sure you have completed the assignment successfully. Tasks to be completedYou have read the assignment group submission and completion policies and all instructions provided in the assignment document and have not missed a word. Student information and the assignment information have been added to the header of the submission. $($Same as the template provided in the assignment documents$)$All questions are answered in a text file $($a SQL worksheet$)$ and are saved as a $.$sql file.Comments are included. $($questions definition or any additional explanation$)$ All SQL statements are executed successfully without errors. $($Use "Run Script" to execute all statements together.$)$Group WorkYes No This assignment is to be completed in groups of $3.$ Please only one submission per group. The comment header MUST have all students name and student number.It is suggested that you ALL do it individually and then meet to compare answers. Those not doing the work may be barred from your group resulting in a zero and incomplete on the assignment. $1|$PageDBS$311$ Advanced Data ServicesLast update: Summer $2024$ Assignment Marking Scheme Question Weight Question $110621073108410951010$ Weight$1010101010$ VERY IMPORTANT:Being part of a group is the same as being a part of a team for these assignments. When you submitted your work as part of a group, you are saying that: You understood what was submitted and that you fully participated with ALL the group members. It does not mean letting others do your work for you. It does not mean watching the others do the work. For your full participation, you get a mark equal to all the others in the group. If on the test, which is very much like the assignment, you cannot answer it strongly indicates that you did not participate and understand the assignment but depended on others for the mark you received. That is very much like submitting their work and claiming it is your work.Example Submission $($a submission format file is provided$)--***********************--$ Student$1$ Name: member Name Student$1$ ID: ######### $--$ Student$2$ Name: member Name Student$2$ ID: ######### $--$ Student$3$ Name: member Name Student$3$ ID: ######### $--$ Date: The date of assignment completion$--$ Purpose: Assignment $1-$ DBS$311--***********************--$ Question $1$ write a brief note about what the question is asking $--$ Q$1$ SOLUTION $--$SELECT order$\_$id AS "order id$",$ COUNT$($item$\_$id$)$ AS "number of items" FROM order$\_$itemsWHERE order$\_$id $<5$GROUP BY order$\_$idORDER BY order$\_$id;order id number of items $---------------------------------1132938482|$PageDBS$311$ Advanced Data Services Last update: Summer $2024$ TasksFor each question, the columns title and the format of the output result must match the sample output columns given in that question.In your $.$sql file, include the SQL query and the output result for each question. Put your output result in a comment block$\mathrm{.}1.$ HiredWrite a query to display employee ID$,$ first name, last name, and hire date for employees who have been hired on weekends$($Saturday and Sunday$)$ and after the $30$th week of $2016.$Hint: remember to avoid the format difference brought by date language settings. The query returns $8$ rows.See the output columns:EMPLOYEE$\_$ID FIRST$\_$NAME LAST$\_$NAME HIRE$\_$DATE $----------------------------------------2.$ Only One EmployeeDisplay manager ID for managers who has only one direct employee. Answer this question use Set Operators, do not use the COUNT$()$ function.Sort the result by manager ID$.$The query returns $3$ rows. See the output columns: Manager ID $----------3.$ Indirect EmployeeDisplay manager ID for managers who have indirect employees. For example, the manager of employee $107$ is employee $106,$ whose manager is employee $2,$ then employee $2$ has indirect employee$($s$).$Sort the result by manager ID$.$The query returns $3$ rows. See the output columns: Manager ID $----------3|$PageDBS$311$ Advanced Data Services Last update: Summer $20244.$ SubscriptionIf a customer bought the same product again on the $100$th days later, we consider this as subscription. Write a query to find customers who have subscription products. Display the customer id and product id$.$ Sort the result by customer id$.$The query returns $2$ rows. See the output columns:Customer ID Product ID $---------------------5.$ Frequent Ordered ProductsWrite a SQL query to display products that have been ordered multiple times $($in different orders$)$ on the same day in $2016.$Display product ID$,$ order date, and the number of times the product has been ordered on that day.Sort the result by order date and product ID$.$The query returns $2$ rows. See the following output columns:Product ID Order Date Number of orders $------------------------------------6.$ PreferredIf a customer buys over $6$ different products from the same category, we consider it is a preferred category to this customer.Write a query to display customer ID and customer name for customers who have exactly $3$ preferred categories.Sort the result by customer ID$.$The query returns $5$ rows. See the following output columns: CUSTOMER ID NAME $---------------7.$ FavouriteThe category from which the customer bought the most kinds of products is called the favourite category of that customer. Write a query to find the customers who have more than one favourite categories. Display the customer ID and the category ID only for those who bought over $5$ kinds of product.The query returns $3$ row. See the following output: Month Number$------------472454|$PageDBS$311$ Advanced Data Services Last update: Summer $20248.$ Order AmountWrite a query to display the month number, month name, year, total number of orders, and total sales amount for each month in $2016.$Sort the result according to month number.The query returns $9$ rows. See the output result as follows. Month Number Month Year Total Number of Orders Sales Amount $-----------------------------------------------------------------2$ February $20165$ May $20166$ June $20167$ July $20168$ August $20169$ September $201610$ October $201611$ November $201612$ December $20163996895\mathrm{.}7121264918\mathrm{.}973334935\mathrm{.}141616763\mathrm{.}1953665979\mathrm{.}49103776557\mathrm{.}1292700781\mathrm{.}7852148981\mathrm{.}0282983793\mathrm{.}759.$ Monthly AverageWrite a query to display month number, month name, and average sales amount $($per order$)$ for each month in $2016$ where the average sales amount is greater than the average sales amount $($per order$)$ for the entire year.Round the average amount to two decimal places.Sort the result by the month number.HINT: In this query, you will calculate the average sales amount for each month in $2016$ and compare it tothe overall average sales amount for the entire year. Using a WITH clause will simplify your code greatly. The query returns $5$ rows. See the output result as follows: Month Number Month Average Sales Amount $-----------------------------------------5$ May $6$ June $7$ July $8$ August$11$ November$632459\mathrm{.}45476419\mathrm{.}31616763\mathrm{.}19733195\mathrm{.}9429796\mathrm{.}25|$PageDBS$311$ Advanced Data Services Last update: Summer $202410.$ CalculationWrite a query to generate the following output with the calculated values filled in$.$Average order amount is the average amount during salesman. While calculating the average order amount, you should exclude the orders that without a salesman.Hint: Using a WITH clause will simplify your code.GOOD LUCK! OUTPUT $---------------------------------------------------------------------------------$ The number of employees with total order amount over average order amount: xThe number of employees with total number of orders greater than $10$: xThe number of employees with no order: xxThe number of employees with orders: x $6|$Page