HW2: 6 points For this hw, you will add (your own) data to tables you'd create, based on your database design from HW1, then write SQL queries to run on those tables :) You'll be using 'Oracle Live SQL' to do your work, and submit links to - so you don't need to install anything, or directly submit any code on D2L! You'll simply be submitting links...[eg: https://livesql.oracle.com/apex/livesql/s/c1v3mbw9y8jnivrr0fb85cjxq] Here are a couple of pages to get you started on Live SQL: https://www.thatjeffsmith.com/archive/2016/03/a-place-to-learn-oracle-no-setup- required/ and https://csveda.com/structured-query-language/sql-using-oracle-live- sql/ Q1 (2 points). Given a time range (eg. Jan 1 to Jan 31, 2021), in that interval, what is the average amount a procedure cost, and how long did it take on the average? Q2 (2 points). Given a date, what is the income for that date, from all the procedures performed that day? The owners could run this daily, to chart income, cumulative income, etc. Q3 (1 point). The owners have asked you to create a new table, after you'd built the original db - this is a simple 'capabilities' table, with just two columns: EMPLOYEE ID, SKILL. With this table, they hope to have, in a single place, what each employee (staff, surgeons... everyone) can do, eg. throw a party, file taxes, administer injections, extract teeth, talk to the press... [each employee would pick from a giant list of tasks, one or more of the items - this is to prevent variations and typos]. Write a query that will pick out only the employees who can do every task in a table such as the one below: Why do this? The owners want to plan a 'light week' that is staffed with a limited number of employees who can do all the above, and rotate tasks - while giving the rest of the staff, paid time off (the ones who come to work get 1.5x pay for that week - cool!). Q4 (1 point). Come up with a(nother) reasonable query that the owners would want to make, using all the data in their db, and write SQL for it. Explain in your submission README, what the query is, in plain English, and also how your SQL code works (ie what it does to carry out the query). You can even include (create, and fill) new tables that were not asked for in HW1. Be creative, have fun with this 'roll your own' question! Submission checklist: • your four livesql.oracle.com/<> links (which you'd save as 'unlisted' so that it's not publicly available) - one each for Q1, Q2, Q3, Q4, placed in a README.txt • assumptions etc. clearly stated in the README You can post questions (and answer others' :)) on Piazza, under 'hw2'. Enjoy SQL coding! File taxes Meet the press Organize spring cleaning Do teeth cleaning Reorder inventory HW2
欢迎咨询51作业君
