You are the SQL programmer for an app that supports student group work. The app allows students to form groups and then share their hours of availability. Your job is to write SQL queries that allow the app to work. The data model is as follows:Figure 1: data modelHow the app will be usedFirst, the id and names of the university’s 10,000 students are entered in the system.Each individual student can then record their weekly hours-of availability to work on the project; i.e., when they are free each week, using the user interface on the left.The UI shows the days of the working week along the top and the work hours from 9am to 5pm down the left-hand side. The student clicks to highlight their free hours, which are recorded in the Availability table.Students can then form and name their groups, recording their group details in the system.Figure 2: User interface for entering freetimesThe Calendar table restricts the times of interest to the working week, and allows us to label some special times, e.g. “lunch”.Setup ScriptTo set up the database in your MySQL server, download the file asst2-2018s2-setup.sql from LMS and run it in Workbench. This script creates the database tables and populates them with test data.Note the comments near the start of the script. There are actually 2 versions of the script – it is different depending on whether you run it on the UniMelb server or your own server. If it is your own server, you will want to uncomment some lines near the top, so that you create a new schema to store the tables in. You can’t create a new schema on the UniMelb server.The SQL queries you must writeOver the page are listed 10 questions for you to answer. Write one SQL statement to answer each question. Do not use views or temporary tables to answer the questions.Beside each question is a maximum mark which reflects the difficulty of the question. Your total score will be scaled to 10% of your overall marks in the subject.Your SQL queries should use the same inputs that are used in the questions. For example, a question that mentions “lunch” should involve a search of the calendar for times marked “lunch”.You should answer “yes/no” questions by printing ‘yes’ or ‘no’, or ‘1’ or 0’.An example of a yes/no question is “Is student 10001 free at 10am on Wednesdays?” (MySQL prints True as ‘1’ and False as ‘0’. To see this, run the statement: SELECT 1 = 2; )Make sure your code and output are readable – this will affect your mark. Where it would improve readability, order your output and use aliases. Format large numbers and fractions appropriately.AssessmentYour work will be assessed according to three criteria:·Correctness of output(70%)·Simplicity of code(20%)·Correctness of formatting(10%)The “simplicity” requirement means that your code should not be unnecessarily complex. For example, a query that produces correct output, but say, joins more tables than is necessary, may not achieve full marks, even though it produces the right output.The Questions(marks are in brackets)1.What is the longest student name? (The length of a student’s name is the sum of the lengths of their given andfamilynames) (1)2.List the names of students who have not yet entered anyfreetimes. (1)3.Which students are free on Wednesday at 10am? (show id and name)(2)4.List each student s name. For those who are in a group, list also the name oftheirgroup. (2)5.For any groups that have more than 3 students, list the group’s id, name and numberofstudents (3)6.Is student “Alice Smith” free at lunchonWednesdays? (3)7.List all times when students 10001 and 10002 arebothfree. (4)8.For each group, list the group id and name of the student whose family name is alphabetically first inthegroup. (4)9.Which students are free on Wednesdays between 10am and 12 noon? Show their idsandnames. (5)10.Are the members of WeLoveDb all free on Wednesdayat10am? (5)SubmissionSubmit a single PDF showing your ten answers to LMS by midnight on the due date of Monday 10th September, at the start of week 8.Ensure that you place your student number at the top of every page of your submission. For each question, present an answer in the following format:·Show the question number and question in blacktext.·Show your answer (the SQL statement) in blue text (not a screenshot)·Show a screenshot from Workbench showing output of 10 or fewerlines.·Show how many rows were returned, in red textForexample:

所有的编程代写范围：essayghost为美国、加拿大、英国、澳洲的留学生提供C语言代写、代写C语言、C语言代做、代做C语言、数据库代写、代写数据库、数据库代做、代做数据库、Web作业代写、代写Web作业、Web作业代做、代做Web作业、Java代写、代写Java、Java代做、代做Java、Python代写、代写Python、Python代做、代做Python、C/C++代写、代写C/C++、C/C++代做、代做C/C++、数据结构代写、代写数据结构、数据结构代做、代做数据结构等留学生编程作业代写服务。