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 conndbfile_banknote = "./data/banknote.db"conn = create_connection(dbfile_banknote) cur = conn.cursor()# remember to close the connection when everything is done1) 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
