def test_select_all_above_average_sales(self):
sql_runner = SQLRunner()
cursor = sql_runner.execute_seed_file()
result = [
(3, 3, '2018-04-22', 1, 60), (7, 7, '2018-04-23', 1, 80),
(8, 8, '2018-04-21', 2, 90), (9, 9, '2018-04-22', 2, 80),
(10, 10, '2018-04-22', 2, 80), (11, 11, '2018-04-22', 2, 70),
(12, 12, '2018-04-23', 2, 90), (13, 13, '2018-04-23', 2, 80),
(24, 24, '2018-04-21', 5, 100), (26, 26, '2018-04-22', 5, 75),
(28, 8, '2018-04-21', 2, 90), (29, 8, '2018-04-21', 2, 90),
(30, 8, '2018-04-21', 2, 90), (32, 24, '2018-04-21', 5, 100),
(33, 24, '2018-04-21', 5, 100), (34, 24, '2018-04-21', 5, 100)
]
self.assertEqual(
cursor.execute(select_all_above_average_sales()).fetchall(),
result)
def test_cte_name(fn):
query_str = fn()
if "average_sales" in query_str:
return True
else:
return False
self.assertTrue(test_cte_name(use_cte_to_determine_average_sale),
"CTE should be called 'average_sales'")
def test_cte_deletes_duplicates(self):
sql_runner = SQLRunner()
cursor = sql_runner.execute_seed_file()
cursor = sql_runner.execute_cte_deletes_duplicates()
result = [(27, )]
self.assertEqual(
cursor.execute("SELECT COUNT(*) FROM sales;").fetchall(), result)
def test_correct_above_avg_sales(self):
sql_runner = SQLRunner()
cursor = sql_runner.execute_seed_file()
cursor = sql_runner.execute_cte_deletes_duplicates()
result = [
('Manhattan', '2018-04-22', 60), ('Manhattan', '2018-04-23', 80),
('Brooklyn', '2018-04-21', 90), ('Brooklyn', '2018-04-22', 80),
('Brooklyn', '2018-04-22', 80), ('Brooklyn', '2018-04-22', 70),
('Brooklyn', '2018-04-23', 90), ('Brooklyn', '2018-04-23', 80),
('London', '2018-04-21', 100), ('London', '2018-04-22', 75)
]
self.assertEqual(
cursor.execute(correct_above_avg_sales()).fetchall(), result)
def test_use_cte_to_determine_average_sale(self):
sql_runner = SQLRunner()
cursor = sql_runner.execute_seed_file()
def test_range(result, val1, val2):
if val1 < result < val2:
return True
else:
return False
result = cursor.execute(
use_cte_to_determine_average_sale()).fetchall()[0][0]
self.assertTrue(test_range(result, 57.2, 57.3))
def test_cte_name(fn):
query_str = fn()
if "average_sales" in query_str:
return True
else:
return False
self.assertTrue(test_cte_name(use_cte_to_determine_average_sale),
"CTE should be called 'average_sales'")
import unittest
from sql_runner import SQLRunner
import sys
sys.path.insert(0, '..')
from select import *
sql_runner = SQLRunner()
table = sql_runner.execute_create_file()
table = sql_runner.execute_seed_file()
class TestSQLSelectingData(unittest.TestCase):
def test_select_all_columns_and_rows(self):
result = [(1, 'Mercury', 'gray', 0, 0.55, 0.0),
(2, 'Venus', 'yellow', 0, 0.82, 0.0),
(3, 'Earth', 'blue', 1, 1.0, 0.0),
(4, 'Mars', 'red', 2, 0.11, 0.0),
(5, 'Jupiter', 'orange', 67, 317.9, 0.0),
(6, 'Saturn', 'hazel', 62, 95.19, 1.0),
(7, 'Uranus', 'light blue', 27, 14.54, 1.0),
(8, 'Neptune', 'dark blue', 14, 17.15, 1.0)]
self.assertEqual(
table.execute(select_all_columns_and_rows()).fetchall(), result)
def test_select_name_and_color_of_all_planets(self):
result = [('Mercury', 'gray'), ('Venus', 'yellow'), ('Earth', 'blue'),
('Mars', 'red'), ('Jupiter', 'orange'), ('Saturn', 'hazel'),
('Uranus', 'light blue'), ('Neptune', 'dark blue')]
self.assertEqual(
table.execute(select_name_and_color_of_all_planets()).fetchall(),
import unittest, sys
from sql_runner import SQLRunner
sys.path.insert(0, '..')
from queries import *
sql_runner = SQLRunner()
cursor = sql_runner.execute_create_file()
cursor = sql_runner.execute_seed_file()
class TestSQLQueries(unittest.TestCase):
def test_select_name_of_player_with_shortest_name(self):
result = 'Pavel Bure'
self.assertEqual(cursor.execute(select_name_of_player_with_shortest_name()).fetchone()[0], result)
def test_select_all_new_york_players_names(self):
result = [('Brian Leetch',), ('Jeff Beukeboom',), ('Mark Messier',), ('Adam Graves',), ('Alexei Kovalev',), ('Mike Richter',), ('Patrick Ewing',), ('Charles Oakley',), ('John Starks',), ('Anthony Mason',), ('Derek Harper',)]
self.assertEqual(cursor.execute(select_all_new_york_players_names()).fetchall(), result)
def test_select_team_name_and_total_goals_scored_for_new_york_rangers(self):
result = [('New York Rangers', 21)]
self.assertEqual(cursor.execute(select_team_name_and_total_goals_scored_for_new_york_rangers()).fetchall(), result)
def test_select_all_games_date_and_info_teams_name_and_score_for_teams_in_nhl(self):
result = [('1994-05-31', 'Stanley Cup Finals - Game 1', 'New York Rangers', 2), ('1994-05-31', 'Stanley Cup Finals - Game 1', 'Vancouver Canucks', 3), ('1994-06-02', 'Stanley Cup Finals - Game 2', 'New York Rangers', 3), ('1994-06-02', 'Stanley Cup Finals - Game 2', 'Vancouver Canucks', 1), ('1994-06-04', 'Stanley Cup Finals - Game 3', 'New York Rangers', 5), ('1994-06-04', 'Stanley Cup Finals - Game 3', 'Vancouver Canucks', 1), ('1994-06-07', 'Stanley Cup Finals - Game 4', 'New York Rangers', 4), ('1994-06-07', 'Stanley Cup Finals - Game 4', 'Vancouver Canucks', 2), ('1994-06-09', 'Stanley Cup Finals - Game 5', 'New York Rangers', 3), ('1994-06-09', 'Stanley Cup Finals - Game 5', 'Vancouver Canucks', 6), ('1994-06-11', 'Stanley Cup Finals - Game 6', 'New York Rangers', 1), ('1994-06-11', 'Stanley Cup Finals - Game 6', 'Vancouver Canucks', 4), ('1994-06-14', 'Stanley Cup Finals - Game 7', 'New York Rangers', 3), ('1994-06-14', 'Stanley Cup Finals - Game 7', 'Vancouver Canucks', 2)]
self.assertEqual(cursor.execute(select_all_games_date_and_info_teams_name_and_score_for_teams_in_nhl()).fetchall(), result)
def test_select_date_info_and_total_points_for_highest_scoring_nba_game(self):
result = [('1994-06-12', 'NBA Finals - Game 3', 182)]
self.assertEqual(cursor.execute(select_date_info_and_total_points_for_highest_scoring_nba_game()).fetchall(), result)