Chapter 2, Problem 2AYK

1. Apply ID Codes in the ID Code column (Insert random ID code that is accepted by the validation rule), except in column A5, A10 and A11. Inputs in the following cells: A5 – TYC89123 A10 – Z90865 A11 – BD1209

Create a table of your courses for this semester with fictitious grades for Test 1, Test 2, and Test 3 with appropriate weighting. The next column should determine the grade using the 'if' function, with plus and minus. The grade cell should show a discrete color scheme with below C- in red; above B in green and, yellow for the rest. This should be done using conditional formatting of cells.

The population of the five most populous Australian cities at the last three census is recorded in the following cell. Piot a side- by-side bar chart to show the change in the populations, The chart should be grouped by each city and show each census in different colours. The legend should explain the colours for the census. Your plot should include an appropriate title. In [ J: cities - ['sydney', Melbourne', 'Brisbane', Perth', 'Adelaide') Pop_2016 - (4321535,4196198,2054614,1874578,11656321 Pop_2011 - [3908642,3707530,1874427,1627576,11039791 Pop_2006 - [3641422,3371888,1676389,1256035,10407191 In [ ]:

1- Display contact First name and contact Last name for customers after sorting the customersby their contact Last name in ascending order.2- selects the order line items (orderNumber, orderlinenumber) from the “orderdetails” table.Also, display the subtotal (quantityOrdered * priceEach) for each line item and sorts the resultset based on the subtotal descending .3- Display unique last names from employees table ordered by last name.4- Display customerNumber, customerName and creditLimit for getting the top five customerswho have the highest creditLimit.5- Find customer Name, customer country, sales representative employee number fromcustomers who do not have a sales representative.6- For each order status, display status and count of orders have this status.7- For Each order number in order details table, Display order number, sum of quantity ordered(itemsCount) and sum of (priceEach * quantityOrdered) as total. With condition “total”greater than 1000.8- Display product code, product…

2. Based on Table 1 and Table 2, write Transact-SQL queries to answer the following questions. Provide the output of each query. (Hint: Use Microsoft SQL Server to write query) Table 1: PAINTING Painting No 2018 2023 2041 2942 2045 2103 Table 2: PAINTER Painter No 10014 10015 10016 Title Dawn Thunder The Starry Night Sunrise The Night Cafe The Gardener Sunset Title Mr Ms Mr Name Johan Anne Michael Painter_No 10016 10015 10015 10014 10015 10014 Phone 844-2573 894-1238 Null Gallery No Price (RM) 1000 1600 900 7000 5600 2000 18 18 23 24 18 24 Email johan@gmail.com anne@gmail.com michael@gmail.com

Apply a data validation for the Price column that will only accept decimal input.

Output the information in a formatted table. The title is right justified with a minimum of 33 characters. Column 1 is left justified with a minimum of 20 characters. Column 2 is right justified with a minimum of 23 characters. Ex: Number of Novels Authored Author name | Number of novels -------------------------------------------- Jane Austen | 6 Charles Dickens | 20 Ernest Hemingway | 9 Jack Kerouac | 22 F. Scott Fitzgerald | 8 Mary Shelley | 7 Charlotte Bronte | 5 Mark Twain | 11 Agatha Christie | 73 Ian Flemming | 14 J.K. Rowling | 14 Stephen King | 54 Oscar Wilde | 1

Cell C5 contains a formula that calculates how many days late an assignment was submitted, based on the dates on C3 and C4. Provide a correct formula for cell C5. Your formula should work if the dates in C3 and C4 were to change. Do not include spaces in your answer. 1 445 WN 3 6 A B Used Assignment Late Days Due Date Date of Submission Number of Late Days C A/ 2021-02-22 2021-02-25 3 D

In cell C6, enter a formula that multiplies the value in C5 for the month of January by the value in cell B6. Use a mixed reference for the cell B6 so that only column B does not change when copied. Use relative reference for all other cell references