Topic:    Challenge Set 9 Part III
Subject:  SQL
Date:     02/20/2017
Name:     Prashant Tatineni



In [2]:

    
import sqlite3



In [3]:

    
db = sqlite3.connect('database.sqlite')



In [5]:

    
cursor = db.cursor()



In [8]:

    
cursor.execute("select name from sqlite_master where type='table'")









    Out[8]:





<sqlite3.Cursor at 0x106a1cd50>



In [9]:

    
print(cursor.fetchall())









    



[(u'sqlite_sequence',), (u'Player_Attributes',), (u'Player',), (u'Match',), (u'League',), (u'Country',), (u'Team',), (u'Team_Attributes',)]



In [11]:

    
cursor.execute("select * from Player_attributes limit 10;")
print(cursor.fetchall())









    



[(1, 218353, 505942, u'2016-02-18 00:00:00', 67, 71, u'right', u'medium', u'medium', 49, 44, 71, 61, 44, 51, 45, 39, 64, 49, 60, 64, 59, 47, 65, 55, 58, 54, 76, 35, 71, 70, 45, 54, 48, 65, 69, 69, 6, 11, 10, 8, 8), (2, 218353, 505942, u'2015-11-19 00:00:00', 67, 71, u'right', u'medium', u'medium', 49, 44, 71, 61, 44, 51, 45, 39, 64, 49, 60, 64, 59, 47, 65, 55, 58, 54, 76, 35, 71, 70, 45, 54, 48, 65, 69, 69, 6, 11, 10, 8, 8), (3, 218353, 505942, u'2015-09-21 00:00:00', 62, 66, u'right', u'medium', u'medium', 49, 44, 71, 61, 44, 51, 45, 39, 64, 49, 60, 64, 59, 47, 65, 55, 58, 54, 76, 35, 63, 41, 45, 54, 48, 65, 66, 69, 6, 11, 10, 8, 8), (4, 218353, 505942, u'2015-03-20 00:00:00', 61, 65, u'right', u'medium', u'medium', 48, 43, 70, 60, 43, 50, 44, 38, 63, 48, 60, 64, 59, 46, 65, 54, 58, 54, 76, 34, 62, 40, 44, 53, 47, 62, 63, 66, 5, 10, 9, 7, 7), (5, 218353, 505942, u'2007-02-22 00:00:00', 61, 65, u'right', u'medium', u'medium', 48, 43, 70, 60, 43, 50, 44, 38, 63, 48, 60, 64, 59, 46, 65, 54, 58, 54, 76, 34, 62, 40, 44, 53, 47, 62, 63, 66, 5, 10, 9, 7, 7), (6, 189615, 155782, u'2016-04-21 00:00:00', 74, 76, u'left', u'high', u'medium', 80, 53, 58, 71, 40, 73, 70, 69, 68, 71, 79, 78, 78, 67, 90, 71, 85, 79, 56, 62, 68, 67, 60, 66, 59, 76, 75, 78, 14, 7, 9, 9, 12), (7, 189615, 155782, u'2016-04-07 00:00:00', 74, 76, u'left', u'high', u'medium', 80, 53, 58, 71, 32, 73, 70, 69, 68, 71, 79, 78, 78, 67, 90, 71, 85, 79, 56, 60, 68, 67, 60, 66, 59, 76, 75, 78, 14, 7, 9, 9, 12), (8, 189615, 155782, u'2016-01-07 00:00:00', 73, 75, u'left', u'high', u'medium', 79, 52, 57, 70, 29, 71, 68, 69, 68, 70, 79, 78, 78, 67, 90, 71, 84, 79, 56, 59, 67, 66, 58, 65, 59, 76, 75, 78, 14, 7, 9, 9, 12), (9, 189615, 155782, u'2015-12-24 00:00:00', 73, 75, u'left', u'high', u'medium', 79, 51, 57, 70, 29, 71, 68, 69, 68, 70, 79, 78, 78, 67, 90, 71, 84, 79, 56, 58, 67, 66, 58, 65, 59, 76, 75, 78, 14, 7, 9, 9, 12), (10, 189615, 155782, u'2015-12-17 00:00:00', 73, 75, u'left', u'high', u'medium', 79, 51, 57, 70, 29, 71, 68, 69, 68, 70, 79, 78, 78, 67, 90, 71, 84, 79, 56, 58, 67, 66, 58, 65, 59, 76, 75, 78, 14, 7, 9, 9, 12)]

1. Which team scored the most points when playing at home?



In [ ]:



In [ ]:



In [ ]:

2. Did this team also score the most points when playing away?



In [ ]:



In [ ]:



In [ ]:



In [ ]:

3. How many matches resulted in a tie?



In [ ]:



In [ ]:



In [ ]:



In [ ]:

4. How many players have Smith for their last name? How many have 'smith' anywhere in their name?



In [ ]:



In [ ]:



In [ ]:



In [ ]:

5. What was the median tie score? Use the value determined in the previous question for the number of tie games. Hint: PostgreSQL does not have a median function. Instead, think about the steps required to calculate a median and use the WITH command to store stepwise results as a table and then operate on these results.



In [ ]:



In [ ]:



In [ ]:

6. What percentage of players prefer their left or right foot? Hint: Calculate either the right or left foot, whichever is easier based on how you setup the problem.



In [ ]:



In [ ]:



In [ ]: