Python function, PL-SQL Programming

Assignment Help:

This task involves developing some functions that extract data from an SQL database. The scenario is that a company which owns an online vehicle search website wants to generate some statistics about their car dataset so that they can have a better idea about its distribution. You are asked to write four Python functions to generate the required statistics.

1.  Write a Python function named top_N_models(make, N) that generates the number of occurrences of each model of a specified car make and prints the top N most frequent models for that car make together with their corresponding number of occurrences, one model per line.   This function has two parameters.    The first parameter is a string specifying a car make. The second parameter is an integer indicating the number of top models to be printed.   For example, for function call top_N_models('BMW', 2), if the top two most frequent models of  BMW are  M3 with frequency 30 and M5 with frequency 25, your function is expected to produce the following result:      

>>> top_N_models('BMW', 2) 

M3 30

M5 25

Note that if there are no details for the specified make of car in the database an appropriate error message must be printed.  (Hint: A non-existent row or field will be represented by the special Python value  None.)  See the unit tests in question file  top_N_models_Q.py for further detail.

2.  Write a Python function named top_N_expensive(N)  that prints the make, model, and price of the top  N most expensive cars, one car per line.   The output should be ordered  in  descending order  by price first, then  in ascending order by  car make, and finally  in  descending order by  car model.   Moreover, the output should not contain duplicates, i.e., any two lines in the output can't be exactly the same.   This function has only one parameter, N, which indicates the number of makes to be printed.

For example, if the top five most expensive cars are MERCEDES-BENZ CLK320 with price $99,990, BMW X5 with price $89,400, BMW M5 with price $89,400, LEXUS LS430 with price $85,000, and BMW Z4 with price $85,000, the output of your function should be as follows.

  >>> top_N_expensive(5) 

MERCEDES-BENZ CLK320 $99990

BMW X5 $89400

BMW M5 $89400

BMW Z4 $85000

LEXUS LS430 $85000

3.  Write a Python function named min_max(make)that prints the  range of prices of  the cars with the specified make.  For example, if the maximum price and minimum price of all Toyotas in the database are  $32,000 and  $3,000, respectively, your function should produce the following result.

>>> min_max('TOYOTA') 

TOYOTA ($3000-$32000)

 Notice that the string printed should be formatted so that it is easy to read.  Also, if there are no details for the specified make of car in the database an appropriate error message must be printed.  

(Hint: A non-existent row or field will be represented by the special Python value  None.)   See the unit tests in question file  min_max_Q.py for further detail.

Development hints

Before you can begin this task you must ensure that you have access to MySQL software and the MySQL Workbench so that you can create the database.  You must also have access to an appropriate MySQL-Python module so that you can call MySQL functions from Python code.  This will be either MySQLdb (for Windows users) or the MySQL Connector (for Mac users). 

You need to write four Python programs for this task to retrieve data from the database.  The four programs are independent from each other. You don't have  to start from the first program.  You can choose any one to start with.

Deliverables

The deliverables for this task are the four completed Python programs,

  1. min_max.py,
  2. top_N_expensive.py,
  3. top_N_makes.py and
  4. top_N_models.py.

Note that the "_Q" question suffixes have been removed from the file names.  These programs must pass all of the unit tests in the supplied program templates  without modification. You do not need to submit any SQL database scripts or dumps.  We will use our own SQL database to test your software.


Related Discussions:- Python function

Semidifference and not - sql, Semidifference and NOT - SQL In this sec...

Semidifference and NOT - SQL In this section first describe the relational difference operator, named MINUS. Example here shows SQL's closest counterpart of that operator.

Create a procedure that update the status, Create a procedure named STATUS_...

Create a procedure named STATUS_SHIP_SP that allows a company to employee in the Shipping Department to update the status of an order to add shipping information. The BB_BASKETSTAT

Update statement - syntax, UPDATE Statement   The UPDATE statement tra...

UPDATE Statement   The UPDATE statement transforms the values of the specified columns in one or more rows in the table or view. Syntax:

Block structure in pl/sql, Block Structure The PL/SQL is a block-struct...

Block Structure The PL/SQL is a block-structured language. That is, the fundamental units (procedures, anonymous blocks, and functions) that make up a PL/SQL program are logi

Return statement, RETURN Statement The RETURN statement instantly compl...

RETURN Statement The RETURN statement instantly completes the execution of a subprogram and returns control to the caller. The Execution then resumes with the statement below t

Example of when or then constraints - sql, Example of WHEN or THEN Constrai...

Example of WHEN or THEN Constraints A concrete example showing how SQL supports WHEN/THEN constraints CREATE TABLE SAL_HISTORY (EmpNo CHAR (6), Salary INTEGER NOT NULL,

Primary key, PRIMARY KEY: PRIMARY KEY  indicates that the table is sub...

PRIMARY KEY: PRIMARY KEY  indicates that the table is subject to a key constraint, in this case declaring that no two rows in the table assigned to ENROLMENT can ever have the

Dbms, DBMS: The answer to this question is of course given in of the t...

DBMS: The answer to this question is of course given in of the theory book. This book is concerned with SQL DBMSs and SQL databases in particular. Soon we will be looking a

Data types and representations, Data Types and Representations This ex...

Data Types and Representations This explains the concept possible representation, abbreviated possrep, and explains how these can be used in conjunction with constraints to de

Positional notation, Positional Notation The first procedure call uses...

Positional Notation The first procedure call uses the positional notation. The PL/SQL compiler relates the first actual parameter, account, with the first proper parameter, ac

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd