COMP2420/COMP6420 - 2020 Sample - Q2 Database Theory - SQL

This question has been solved

Engage in a Conversation

Q2 Database Theory

b) SQL [9 Marks]

Now that you have finished the basics for which SQL is built upon, time to do the real thing. In psql, you have been provided a dataset for a set of banknotes that have the potental to be fake. The following schema applies: CourseNana.COM

Table: Banknotes

Name	Description
Variance	Variance of Wavelet Transformed Image (continous value)
Skewness	Skewness of Wavelet Transformed Image (continous value)
Curtosis	Curtosis of Wavelet Transformed image (continous value)
Entropy	Entropy of Image (continous value)
Class	Classification (Binary value)

For each of the following questions, provide the SQL query you used to get the answer, and the answer you received. CourseNana.COM

# THIS IS YOUR CONNECTION BLOCK, DO NOT MODIFY THIS. # OTHERWISE, YOU WILL NOT BE ABLE TO READ THE DATABASEdef create_connection(db_file):    """ Connect to the specified SQLite database, if not exist, create a new one (in memory);    :db_file: location of db to connect to    :return: Connection object or None    """    conn = None    try:        conn = sqlite3.connect(db_file)        print("Connection established!")    except Error as e:        print("Error Connecting to Database")        raise(e)    return conn​dbfile_banknote = "./data/banknote.db"conn = create_connection(dbfile_banknote) cur = conn.cursor()# remember to close the connection when everything is done

1) What is the total number of banknotes in the dataset?[1 mark] CourseNana.COM

2) What is the highest entropy of a banknote?[1 mark] CourseNana.COM

3) How many banknotes have a variance of higher than 0.5?[1 mark] CourseNana.COM

4) Find the range of the Curtosis column (where range is smallest_value subtracted from the largest_value). Provide the result.[3 marks] CourseNana.COM

5) Find the top 5 banknotes that have the highest combined total of skewness and entropy. Display the banknote's class and the combined value. CourseNana.COM

Get the Solution to This Question

WeChat (微信)

Last: COMP2420/COMP6420 - 2020 Sample - Q2 Database Theory

Next: COMP2420/COMP6420 - 2020 Sample - Q2 Database Theory - XML