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 “object” is correct?

object = (1, 2, ‘a’, 3, 4)

A object is a list, and object[3] selects 'a'

B object is a tuple, and object[0] returns an error

C object is a tuple, and object[2] selects 'a'

D object is a list, and object[2] selects 'a'

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

Object = {'a':0, 'b':1, 'c':(2,3) }

A object[0] selects 'a':0

B object[1] selects 'a':0

C object[‘a’] selects the value 0

D object[1] selects 'b'

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

object = (1, 1.5, 2, 2.5)

A object is a list

B object does not have an append() method

C object(2) returns 2

D object(3) selects 2

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

object = [1, 2, 3, (‘a’, ‘b’, ‘c’)]

A object[4] is a tuple

B object’s type is “object”, because it contains different types of object.

C The last item in object is a string

D len(object) returns 4

A5)     (Python) Which of the following statements in relation to functions in Python is correct?

A A function definition must have a “return” statement

B A function cannot return a list

C The first line of a function definition ends with a semi-colon, “;” .

D None of the above

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

A list1.add(list2)

B list1 + list2

C list1.concatenate(list2)

D list1.append(list2)

A7)     (Python) Given a dict named Country, what is the way to access the value associated with the key “Inflation”?

A Country.key(“Inflation”)

B Country.value(“Inflation”)

C Country(“Inflation”)

D Country[“Inflation”]

A8)     (Python) What is the purpose of loc when using a DataFrame in pandas?

A To access a group of rows and columns by labels or a boolean array.

B To locate the row and column labels of a value in the DataFrame

C To locate the DataFrame in memory

D To find the location of missing values in the DataFrame.

A9)     (Python) What does the “describe()” method for a pandas DataFrame provide?

A Details about the index and column data types.

B A concise summary of the DataFrame, including data types and non null values.

C Information about the memory usage of the DataFrame.

D A statistical summary of the DataFrame’s numerical columns.

A10)   (Python) What is the purpose of “groupby()” in pandas?

A To filter the DataFrame according to a condition.

B To group the data based on one or more columns and perform aggregate functions.

C To sort the data according to a particular column or according to a number of columns

D None of the above

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

FROM Customers;

Which of the following returns data for the City column?

A SELECT(City)

B SELECT: City

C SEL City

D SELECT City

A12)   (SQL) Suppose the Products table has the following three columns:

ProductCode, ProductID, and ProductName.  Which of the following SQL queries returns the data for these columns?

A SELECT ProductCode

JOIN ProductID

JOIN ProductName

FROM Products;

B ProductCode AND ProductID AND ProductName

FROM Products

C ProductCode, ProductID, ProductName

FROM Products

D None of the above

A13)   (SQL) Which of the following is another name for a row within a SQL table?

A A record

B A field

C A list

D None of the above

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

A It specifies the columns to be selected

B It joins two or more tables together

C It filters the rows based on a specifed condition

D It orders the results table based on the specified column

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 an “alias”?

A o

B Orders

C OrderID

D AS

A19)   (SQL) Which of the following are wildcards for single and ‘zero or more’ characters, respectively?

A % and _

B _ and +

C _ and %

D None of the above.

A20)   (SQL) What does a “JOIN” do?

A Groups rows of a table based on the values of one or more columns

B Orders the results table in ascending or descending order

C Retrieves a subset of rows from a table based on a specified condition

D Combines rows from two of more tables using key columns.

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)  Given the following code defining Sam’s age, can you write code using an f-string to print “Sam is 20 years old”?

age = 20

(5 marks)

(ii)  Given the following code defining Sam’s age and number of pets in the object

Sam_info, can you write code using either an f-string or string format method to print “Sam is 20 years old and has 2 pets”?

Sam_info = { ‘age’ :20, ‘pets’ :2}

(5 marks)

(iii)  Given a DataFrame df, can you write code to select the rows “a” and “b” and the columns “x” and “y”?

(5 marks)

(iv) Write a line of code to read a csv file “mydata.csv” into a DataFrame df, assuming that the first row of the csv file contains the column names.

Write code to plot the first column of df against the second column using the plot function from pyplot (only partial credit will be given for other approaches, even if the approach is superior).

(10 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 Customers table in Source 1, write a SQL query to return all of the records, ordered by CustomerName.

(5 marks)

(ii)  Given the Products table in Source 1, write a SQL query to return the records for the columns ProductName and UnitPrice, but only returning the records for products with a price greater than 50, with the cheapest products at the top.

(5 marks)

(iii)  Given the OrderDetails and Products tables in Source 1, write a SQL query to return the records for the columns OrderID, ProductCode and Quantity and

UnitPrice.  Moreover, the records should be ordered by OrderID.   Aliases “od” and “ p” should be used for the OrderDetails and Products tables.

(5 marks)

(iv)  Given the OrderDetails and Products tables in Source 1, write a SQL query to return three columns: the product ID, product name and total quantity (total quantity

of each product across all orders).  Moreover, the following should also be incorporated into the query:

•   The alias “TotalQuantity” should be used for the total quantity per product ID, and aliases “OD” and “ P” should be used for the Order Details and Products  tables.

•   The results should be ordered by product name, with products beginning with the letter “A” at the top.

•   Only results for products with total quantity greater than 10 should be included.

(10 marks)