SECTION A (MULTIPLE CHOICE)

Please enter your marks in the MCQ card that has been provided.

Questions 1- 10 are on Python (2.5 marks per question)

Questions 11-20 are on SQL (2.5 marks per question)

There are 50 marks in total available for this section.

Choose the one alternative that best completes the statement or answers the question.

A1)     (Python) Which of the following statements in relation to list objects in Python is correct?

A  Lists are not iterables

B  Lists cannot contain different types of item

C  Lists are immutable

D  Lists can contain different types of item

A2)     (Python) Which of the following is a dict?

A  {'a':1, 'b':2, 'c':3 }

B  {1, 2, 3}

C  (1, 2, 3)

D  [1, 2, 3]

A3)     (Python) Which of the following prints the individual numbers 0 to 10 successfully?

A                   numbers = (0,1,2,3,4,5,6,7,8,9,10)

for item in numbers:

print( item )

B                   numbers = [0,1,2,3,4,5,6,7,8,9,10]

for item in numbers

print( item )

C                   numbers = [0,1,2,3,4,5,6,7,8,9,10]

for item in numbers:

print( numbers )

D                   numbers = [0,1,2,3,4,5,6,7,8,9,10]

for items in numbers:

print( item )

A4)     (Python) Which of the following statements in relation to “object” is correct?

object = “Python”

A  object is a Python “character” type

B  object is a Python “text” type

C  len(object) returns 5

D  len(object) returns 6

A5)     (Python) Which of the following produces a numerical value for

utility_value?

A                   def: utility( x , y , a=0.5 , b=0.5 ):

u = (x**a) * (y**b)

print(u)

utility_value = utility( 1, 2, a=0.5 , b=0.5  )

B                    def utility( x , y , a=0.5 , b=0.5 ):

return (x**a) * (y**b)

utility_value = utility( 1, 2, a=0.2)

C                   def: utility( x , y )

u == (x**a) * (y**b)

return u

utility_value = utility( 1, 2 )

D                    def: utility( x , y , a=0.5 , b=0.5 ):

u = (x**a) * (y**b)

return

utility_value = utility( a=0.5 , b=0.5, 1, 2 )

A6)     (Python) Which of the following concatenates the lists list1 and list2?

A        list1.concat(list2)

B        list1 * list2

C        list1.extend(list2)

D        list1.append(list2)

A7)     (Python) Given a dict named Country, which of the following will not produce an error?

A        Country.info()

B        Country.keys()

C        Country.text()

D        Country.plot()

A8)     (Python) What is the difference between iloc and loc?

A  iloc selects individual rows with one or more columns, whereas loc does not have this restriction

B  iloc only selects using numerical indexing, while loc uses labels

C  iloc is the Apple Mac version of loc

D  iloc is used for Series, while loc is used for DataFrames

A9)     (Python)  Which of the following is a function available in the pyplot module in

matplotlib?

A  subplot2grid()

B  add_subplot()

C  plt()

D  plots()

A10)   (Python) Which of the following statements about “stack()” is correct?

A  stack() is a pandas function for making a column index level into a row index level

B  stack() is a pandas method for making a column index level into a row index level

C  stack() is a pandas function for making a row index level into a column index level

D  stack() is a pandas method for making a row index level into a column index level

A11)   (SQL)  Consider the following query against the OrderStatus table, which is incomplete:

             FROM OrderStatus;

Which of the following returns data for the StatusCode and StatusName columns?

A        SEL StatusCode, StatusName

B        SELECT StatusCode

JOIN StatusName ON OrderStatus

C        SELECT[StatusCode, StatusName]

D        SELECT StatusCode, StatusName

A12)   (SQL)  If the field CustomerID in the Customers table has unique values,

while the field CustomerID in the Orders table does not have unique values, which of the following is possible?

A  CustomerID is the foreign key in the Customers table and a prime key in the Orders table

B  CustomerID is the primary key in the Customers table and a format key in the Orders table

C  CustomerID is the primary key in the Customers table and a foreign key in the Orders table

D  CustomerID is the format key in the Customers table and a prime key in the Orders table

A13)   (SQL)  Which of the following is correct?

A  SQL stands for Standard Query Lexicon

B  “field” and “record” mean the same in a SQL table

C  “record” and “row” mean the same in a SQL table

D  None of the above

A14)   (SQL)  What is the purpose of the “LIKE” operator in a SELECT query?

A  It is used to find fields that are similar to each other in tables

B  It is used to find fields that are similar to each other in results records

C  It is used to find rows that are similar to each other in each record

D  It is used to compare values in fields with a pattern

A15)   (SQL)  Consider the following query (using the Northwind Traders ERD, though this should not be relevant):

SELECT o.OrderID, o.StatusID, os.StatusCode

FROM Orders AS o

LEFT JOIN OrderStatus AS os

ON o.StatusID = os.StatusCode

Which of the following is generally optional in the above query?

A  AS

B  SELECT

C  FROM

D  ON

A18)   (SQL)  Suppose that you would like to obtain aggregated results for each order ID number in a column called OrderID.  Which of the following lines might appear in a SQL query doing this?

A  GROUPBY OrderID

B  GROUP_BY OrderID

C  GROUPBY(OrderID)

D  GROUP BY OrderID

A19)   (SQL)  Consider the following wildcard pattern:

‘_%  a  %’

Which of the following strings are consistent with this pattern?

A  Band

B  Handy

C  That

D  None of the above.

A20)   (SQL)  Consider the Entity Relationship Diagraph in Source 1.  Which of the following statements is correct with respect to the Customers and Orders tables?

A  OrderID is a FOREIGN key in the Customers table and the PRIMARY key in the Orders table

B  OrderID is a PRIMARY key in the Customers table and the FOREIGN key in the Orders table

C  CustomerID is a FOREIGN key in the Customers table and the PRIMARY key in the Orders table

D  None of the above.

SECTION B

Questions 1: Python (25 marks)

Questions 2: SQL (25 marks) (requires use of Source 1 at the end of this booklet)

There are 50 marks in total available for this section.

Answers need to be written clearly.  Code answers should be printed clearly.

Question B1 (Python)

(i)  The cities of Manchester and Liverpool have populations of approximately

553,000 and 500,500, respectively.  Write a line of code to create a dict variable city_populations that contains this information. (5 marks)

(ii)  Given the following, write a for loop to fill numbers2 (currently empty) with the numbers 11 to 15.  Morever, how would you do this with a list comprehension instead?

numbers1 = [1,2,3,4,5]

numbers2 = [ ]                                                          (5 marks)

(iii)  Given the following data for inflation and the corresponding years, write code to plot Inflation against Year, and describe any objects, functions, methods or modules that you use as part of this:

Inflation = [1.2, 1.4, 1.5, 1.8, 2.7, 4.1, 6.7]

Year = [2016, 2017, 2018, 2019, 2020, 2021, 2022]                                        (5 marks)

(iv)  Suppose a DataFrame hours_worked_df contains survey data for the number  of hours worked by individuals living in different regions of a country.    In particular, it has a column “region” and a column “ hours worked” .

Can you write a line of code that groups the data by region and produces a DataFrame giving the average hours worked in each region?        (5 marks)

(v) Objects of type Economist print messages reminding the user about important economic ideas or principles.  For example, an object Keynes of type Economist may have a message such as “Stimulate demand in the economy!”, while a Hayek object may have a message such as “Leave the economy alone!” .

Economist objects are constructed as follows:

virtual_Keynes_economist = Economist( “ Keynes” , “Stimulate demand!”)

virtual_Hayek_economist = Economist( “Hayek”, “Leave the economy alone!”)

Write an Economist type (i.e. a class) that requires two strings to be entered when creating its objects, as in the examples above.  These should be called model and message.

The Economist type should have two methods: speak() and shout().

Both methods should simply print the text stored in message, but shout() converts the text to upper case.  [Hint: recall the string method upper()]              (5 marks)

Question B2 (SQL)

These questions requires use of the Entity Relationship Diagram in Source 1, which is at the end of this booklet.

(i)  Given the Products table in Source 1, write a SQL query to return the field ProductName, ordered by UnitPrice.  (5 marks)

(ii)  Given the OrderStatus table in Source 1, write a SQL query to return the records for the columns StatusID and StatusName, but only returning the records where the entry for StatusName is “Shipped” or “Invoiced” .       (5 marks)

(iii)  Given the Employees and Orders tables in Source 1, write a query to return the following three columns: EmployeeID, JobTitle, and OrderID.  Moreover, the results should be ordered by EmployeeID, and the aliases "e" and "o" should be used for the Employees and Orders tables, respectively.           (5 marks)

(iv)  Given the Employees and Orders tables in Source 1, write a query to return the total number of orders placed by each Northwind Traders employee, and then modify it to return just the record for the employee with the most orders.  Moreover, the following should also be incorporated into the query:

•   The alias “TotalOrders” should be used for the total number or orders by each employee, and aliases “ E” and “O” should be used for the Employees and Orders tables.

•   There should be just the following two columns: EmployeeID and TotalOrders. (10 marks)