Assignment$-1($Database Management System$)$ DBAS$-32100$ MM:$25$

Each query is of $5$ marks

SI database ER diagram;$-$

Use SI database and answer following queries;$-$

$1.$ Use Analytical function approach to display the details of car with highest list price in each make.

$2.$ Write Query using Analytical function to display name of customer who bought the most expensive car among all customers.

$3.$ Write Query to print number of cars in each carmake and caryear, which are still left to be sold. Make sure to display subtotal for every possible combinations of carMake and carYear as well as overall subtotal using Advanced grouping criteria. $[$Simple group by query will result in $0$ grades make sure to use advanced grouping criteria posted in Week$-10$ content$]$

HR database ER diagram;$-$

Use HR database and answer following Queries;$-$

$1.$ Using Hierarchical queries print who is directly and indirectly reporting to Neena Kochhar.

$2.$ Write query using rank or dense rank function to print latest joinee information in each department.

Once done paste Queries and their respective screenshots on word document and save it with your name and upload it$.$ Without queries grades would be zero, without output screenshots grades deduction would be $50\%$ in each question.

Important; $-$ Execute following Queries and Make sure to paste Query and its output screenshot as well. In case output screenshot is missing, marks would be deducted for each query missing output screenshot as per marks distribution given. If a query is producing a large number of rows of result, please make sure to take screenshot of up to first $10$ rows of results.

Late submission $$ Due dates are posted on slate, Please respect the due dates. After due date every late day will give you $(-10\%)$ for up to $3$ late days i$.$e$.(-30\%)$ in total and after $3$ late days assignment would be graded $0.$

Academic Integrity $$ Assignment is an individual work, Make sure not to copy or not to give solution to anyone, if you do so both parties would be equally responsible for Academic Integrity breach and will be given penalty based on Sheridan$$s Guidelines for academic Integrity Breach.

Advise for Assignment $$ Execute

Submission guidelines $$ Once all queries are done, create word document with Question, answer Query and output screenshot and save it with yourSlateId$\_$DBAS$32100\_$YourStudentNumber.docx and upload it$.$

$$ Include as comments at the top of each Query:

$1.$ the script's file name

$2.$ your name

$3.$ your Oracle user name

$4.$ the question$/$problem that you are solvingb