Last active
November 20, 2020 14:05
-
-
Save kingabzpro/a6784ac31ea5113919b6d787e025efe5 to your computer and use it in GitHub Desktop.
Using SQL on Jupyter Notebook Using postgres local server Assessment test
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "metadata": { | |
| "language_info": { | |
| "codemirror_mode": { | |
| "name": "ipython", | |
| "version": 3 | |
| }, | |
| "file_extension": ".py", | |
| "mimetype": "text/x-python", | |
| "name": "python", | |
| "nbconvert_exporter": "python", | |
| "pygments_lexer": "ipython3", | |
| "version": "3.8.3-final" | |
| }, | |
| "orig_nbformat": 2, | |
| "kernelspec": { | |
| "name": "python3", | |
| "display_name": "Python 3" | |
| } | |
| }, | |
| "nbformat": 4, | |
| "nbformat_minor": 2, | |
| "cells": [ | |
| { | |
| "source": [ | |
| "# SQLAssessmentTest2" | |
| ], | |
| "cell_type": "markdown", | |
| "metadata": {} | |
| }, | |
| { | |
| "source": [ | |
| "## 1) How can you retrieve all the information from the cd.facilities table?" | |
| ], | |
| "cell_type": "markdown", | |
| "metadata": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 23, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "output_type": "stream", | |
| "name": "stdout", | |
| "text": [ | |
| " * postgres://postgres:***@localhost:5432/exercise\n9 rows affected.\n" | |
| ] | |
| }, | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "[(0, 'Tennis Court 1', Decimal('5'), Decimal('25'), Decimal('10000'), Decimal('200')),\n", | |
| " (1, 'Tennis Court 2', Decimal('5'), Decimal('25'), Decimal('8000'), Decimal('200')),\n", | |
| " (2, 'Badminton Court', Decimal('0'), Decimal('15.5'), Decimal('4000'), Decimal('50')),\n", | |
| " (3, 'Table Tennis', Decimal('0'), Decimal('5'), Decimal('320'), Decimal('10')),\n", | |
| " (4, 'Massage Room 1', Decimal('35'), Decimal('80'), Decimal('4000'), Decimal('3000')),\n", | |
| " (5, 'Massage Room 2', Decimal('35'), Decimal('80'), Decimal('4000'), Decimal('3000')),\n", | |
| " (6, 'Squash Court', Decimal('3.5'), Decimal('17.5'), Decimal('5000'), Decimal('80')),\n", | |
| " (7, 'Snooker Table', Decimal('0'), Decimal('5'), Decimal('450'), Decimal('15')),\n", | |
| " (8, 'Pool Table', Decimal('0'), Decimal('5'), Decimal('400'), Decimal('15'))]" | |
| ], | |
| "text/html": "<table>\n <tr>\n <th>facid</th>\n <th>name</th>\n <th>membercost</th>\n <th>guestcost</th>\n <th>initialoutlay</th>\n <th>monthlymaintenance</th>\n </tr>\n <tr>\n <td>0</td>\n <td>Tennis Court 1</td>\n <td>5</td>\n <td>25</td>\n <td>10000</td>\n <td>200</td>\n </tr>\n <tr>\n <td>1</td>\n <td>Tennis Court 2</td>\n <td>5</td>\n <td>25</td>\n <td>8000</td>\n <td>200</td>\n </tr>\n <tr>\n <td>2</td>\n <td>Badminton Court</td>\n <td>0</td>\n <td>15.5</td>\n <td>4000</td>\n <td>50</td>\n </tr>\n <tr>\n <td>3</td>\n <td>Table Tennis</td>\n <td>0</td>\n <td>5</td>\n <td>320</td>\n <td>10</td>\n </tr>\n <tr>\n <td>4</td>\n <td>Massage Room 1</td>\n <td>35</td>\n <td>80</td>\n <td>4000</td>\n <td>3000</td>\n </tr>\n <tr>\n <td>5</td>\n <td>Massage Room 2</td>\n <td>35</td>\n <td>80</td>\n <td>4000</td>\n <td>3000</td>\n </tr>\n <tr>\n <td>6</td>\n <td>Squash Court</td>\n <td>3.5</td>\n <td>17.5</td>\n <td>5000</td>\n <td>80</td>\n </tr>\n <tr>\n <td>7</td>\n <td>Snooker Table</td>\n <td>0</td>\n <td>5</td>\n <td>450</td>\n <td>15</td>\n </tr>\n <tr>\n <td>8</td>\n <td>Pool Table</td>\n <td>0</td>\n <td>5</td>\n <td>400</td>\n <td>15</td>\n </tr>\n</table>" | |
| }, | |
| "metadata": {}, | |
| "execution_count": 23 | |
| } | |
| ], | |
| "source": [ | |
| "%sql select * from cd.facilities;" | |
| ] | |
| }, | |
| { | |
| "source": [ | |
| "## 2) You want to print out a list of all of the facilities and their cost to members. How would you retrieve a list of only facility names and costs?\n" | |
| ], | |
| "cell_type": "markdown", | |
| "metadata": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 24, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "output_type": "stream", | |
| "name": "stdout", | |
| "text": [ | |
| " * postgres://postgres:***@localhost:5432/exercise\n", | |
| "9 rows affected.\n" | |
| ] | |
| }, | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "[('Tennis Court 1', Decimal('5')),\n", | |
| " ('Tennis Court 2', Decimal('5')),\n", | |
| " ('Badminton Court', Decimal('0')),\n", | |
| " ('Table Tennis', Decimal('0')),\n", | |
| " ('Massage Room 1', Decimal('35')),\n", | |
| " ('Massage Room 2', Decimal('35')),\n", | |
| " ('Squash Court', Decimal('3.5')),\n", | |
| " ('Snooker Table', Decimal('0')),\n", | |
| " ('Pool Table', Decimal('0'))]" | |
| ], | |
| "text/html": "<table>\n <tr>\n <th>name</th>\n <th>membercost</th>\n </tr>\n <tr>\n <td>Tennis Court 1</td>\n <td>5</td>\n </tr>\n <tr>\n <td>Tennis Court 2</td>\n <td>5</td>\n </tr>\n <tr>\n <td>Badminton Court</td>\n <td>0</td>\n </tr>\n <tr>\n <td>Table Tennis</td>\n <td>0</td>\n </tr>\n <tr>\n <td>Massage Room 1</td>\n <td>35</td>\n </tr>\n <tr>\n <td>Massage Room 2</td>\n <td>35</td>\n </tr>\n <tr>\n <td>Squash Court</td>\n <td>3.5</td>\n </tr>\n <tr>\n <td>Snooker Table</td>\n <td>0</td>\n </tr>\n <tr>\n <td>Pool Table</td>\n <td>0</td>\n </tr>\n</table>" | |
| }, | |
| "metadata": {}, | |
| "execution_count": 24 | |
| } | |
| ], | |
| "source": [ | |
| "%sql select name,membercost from cd.facilities;" | |
| ] | |
| }, | |
| { | |
| "source": [ | |
| "## 3) How can you produce a list of facilities that charge a fee to members?" | |
| ], | |
| "cell_type": "markdown", | |
| "metadata": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 25, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "output_type": "stream", | |
| "name": "stdout", | |
| "text": [ | |
| " * postgres://postgres:***@localhost:5432/exercise\n5 rows affected.\n" | |
| ] | |
| }, | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "[(0, 'Tennis Court 1', Decimal('5'), Decimal('25'), Decimal('10000'), Decimal('200')),\n", | |
| " (1, 'Tennis Court 2', Decimal('5'), Decimal('25'), Decimal('8000'), Decimal('200')),\n", | |
| " (4, 'Massage Room 1', Decimal('35'), Decimal('80'), Decimal('4000'), Decimal('3000')),\n", | |
| " (5, 'Massage Room 2', Decimal('35'), Decimal('80'), Decimal('4000'), Decimal('3000')),\n", | |
| " (6, 'Squash Court', Decimal('3.5'), Decimal('17.5'), Decimal('5000'), Decimal('80'))]" | |
| ], | |
| "text/html": "<table>\n <tr>\n <th>facid</th>\n <th>name</th>\n <th>membercost</th>\n <th>guestcost</th>\n <th>initialoutlay</th>\n <th>monthlymaintenance</th>\n </tr>\n <tr>\n <td>0</td>\n <td>Tennis Court 1</td>\n <td>5</td>\n <td>25</td>\n <td>10000</td>\n <td>200</td>\n </tr>\n <tr>\n <td>1</td>\n <td>Tennis Court 2</td>\n <td>5</td>\n <td>25</td>\n <td>8000</td>\n <td>200</td>\n </tr>\n <tr>\n <td>4</td>\n <td>Massage Room 1</td>\n <td>35</td>\n <td>80</td>\n <td>4000</td>\n <td>3000</td>\n </tr>\n <tr>\n <td>5</td>\n <td>Massage Room 2</td>\n <td>35</td>\n <td>80</td>\n <td>4000</td>\n <td>3000</td>\n </tr>\n <tr>\n <td>6</td>\n <td>Squash Court</td>\n <td>3.5</td>\n <td>17.5</td>\n <td>5000</td>\n <td>80</td>\n </tr>\n</table>" | |
| }, | |
| "metadata": {}, | |
| "execution_count": 25 | |
| } | |
| ], | |
| "source": [ | |
| "%sql select * from cd.facilities where membercost > 0;" | |
| ] | |
| }, | |
| { | |
| "source": [ | |
| "## 4) How can you produce a list of facilities that charge a fee to members,and that fee is less than 1/50th of the monthly maintenance cost? Return the facid, facility name, member cost, and monthly maintenance of the facilities in question." | |
| ], | |
| "cell_type": "markdown", | |
| "metadata": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 26, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "output_type": "stream", | |
| "name": "stdout", | |
| "text": [ | |
| " * postgres://postgres:***@localhost:5432/exercise\n2 rows affected.\n" | |
| ] | |
| }, | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "[(4, 'Massage Room 1', Decimal('35'), Decimal('3000')),\n", | |
| " (5, 'Massage Room 2', Decimal('35'), Decimal('3000'))]" | |
| ], | |
| "text/html": "<table>\n <tr>\n <th>facid</th>\n <th>name</th>\n <th>membercost</th>\n <th>monthlymaintenance</th>\n </tr>\n <tr>\n <td>4</td>\n <td>Massage Room 1</td>\n <td>35</td>\n <td>3000</td>\n </tr>\n <tr>\n <td>5</td>\n <td>Massage Room 2</td>\n <td>35</td>\n <td>3000</td>\n </tr>\n</table>" | |
| }, | |
| "metadata": {}, | |
| "execution_count": 26 | |
| } | |
| ], | |
| "source": [ | |
| "%sql select facid,name,membercost,monthlymaintenance from cd.facilities where membercost > 0 and membercost< (monthlymaintenance/50);" | |
| ] | |
| }, | |
| { | |
| "source": [ | |
| "## 5) How can you produce a list of all facilities with the word 'Tennis' in theirname?" | |
| ], | |
| "cell_type": "markdown", | |
| "metadata": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 27, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "output_type": "stream", | |
| "name": "stdout", | |
| "text": [ | |
| " * postgres://postgres:***@localhost:5432/exercise\n3 rows affected.\n" | |
| ] | |
| }, | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "[(0, 'Tennis Court 1', Decimal('5'), Decimal('25'), Decimal('10000'), Decimal('200')),\n", | |
| " (1, 'Tennis Court 2', Decimal('5'), Decimal('25'), Decimal('8000'), Decimal('200')),\n", | |
| " (3, 'Table Tennis', Decimal('0'), Decimal('5'), Decimal('320'), Decimal('10'))]" | |
| ], | |
| "text/html": "<table>\n <tr>\n <th>facid</th>\n <th>name</th>\n <th>membercost</th>\n <th>guestcost</th>\n <th>initialoutlay</th>\n <th>monthlymaintenance</th>\n </tr>\n <tr>\n <td>0</td>\n <td>Tennis Court 1</td>\n <td>5</td>\n <td>25</td>\n <td>10000</td>\n <td>200</td>\n </tr>\n <tr>\n <td>1</td>\n <td>Tennis Court 2</td>\n <td>5</td>\n <td>25</td>\n <td>8000</td>\n <td>200</td>\n </tr>\n <tr>\n <td>3</td>\n <td>Table Tennis</td>\n <td>0</td>\n <td>5</td>\n <td>320</td>\n <td>10</td>\n </tr>\n</table>" | |
| }, | |
| "metadata": {}, | |
| "execution_count": 27 | |
| } | |
| ], | |
| "source": [ | |
| "%sql select * from cd.facilities where name like '%Tennis%';" | |
| ] | |
| }, | |
| { | |
| "source": [ | |
| "## 6) How can you retrieve the details of facilities with ID 1 and 5? Try to do it without using the OR operator." | |
| ], | |
| "cell_type": "markdown", | |
| "metadata": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 28, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "output_type": "stream", | |
| "name": "stdout", | |
| "text": [ | |
| " * postgres://postgres:***@localhost:5432/exercise\n2 rows affected.\n" | |
| ] | |
| }, | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "[(1, 'Tennis Court 2', Decimal('5'), Decimal('25'), Decimal('8000'), Decimal('200')),\n", | |
| " (5, 'Massage Room 2', Decimal('35'), Decimal('80'), Decimal('4000'), Decimal('3000'))]" | |
| ], | |
| "text/html": "<table>\n <tr>\n <th>facid</th>\n <th>name</th>\n <th>membercost</th>\n <th>guestcost</th>\n <th>initialoutlay</th>\n <th>monthlymaintenance</th>\n </tr>\n <tr>\n <td>1</td>\n <td>Tennis Court 2</td>\n <td>5</td>\n <td>25</td>\n <td>8000</td>\n <td>200</td>\n </tr>\n <tr>\n <td>5</td>\n <td>Massage Room 2</td>\n <td>35</td>\n <td>80</td>\n <td>4000</td>\n <td>3000</td>\n </tr>\n</table>" | |
| }, | |
| "metadata": {}, | |
| "execution_count": 28 | |
| } | |
| ], | |
| "source": [ | |
| "%sql select * from cd.facilities where facid in (1,5);" | |
| ] | |
| }, | |
| { | |
| "source": [ | |
| "## 7) How can you produce a list of members who joined after the start of September 2012? Return the memid, surname, firstname, and joindate of the members in question" | |
| ], | |
| "cell_type": "markdown", | |
| "metadata": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 31, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "output_type": "stream", | |
| "name": "stdout", | |
| "text": [ | |
| " * postgres://postgres:***@localhost:5432/exercise\n10 rows affected.\n" | |
| ] | |
| }, | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "[(24, 'Sarwin', 'Ramnaresh', datetime.datetime(2012, 9, 1, 8, 44, 42)),\n", | |
| " (26, 'Jones', 'Douglas', datetime.datetime(2012, 9, 2, 18, 43, 5)),\n", | |
| " (27, 'Rumney', 'Henrietta', datetime.datetime(2012, 9, 5, 8, 42, 35)),\n", | |
| " (28, 'Farrell', 'David', datetime.datetime(2012, 9, 15, 8, 22, 5)),\n", | |
| " (29, 'Worthington-Smyth', 'Henry', datetime.datetime(2012, 9, 17, 12, 27, 15)),\n", | |
| " (30, 'Purview', 'Millicent', datetime.datetime(2012, 9, 18, 19, 4, 1)),\n", | |
| " (33, 'Tupperware', 'Hyacinth', datetime.datetime(2012, 9, 18, 19, 32, 5)),\n", | |
| " (35, 'Hunt', 'John', datetime.datetime(2012, 9, 19, 11, 32, 45)),\n", | |
| " (36, 'Crumpet', 'Erica', datetime.datetime(2012, 9, 22, 8, 36, 38)),\n", | |
| " (37, 'Smith', 'Darren', datetime.datetime(2012, 9, 26, 18, 8, 45))]" | |
| ], | |
| "text/html": "<table>\n <tr>\n <th>memid</th>\n <th>surname</th>\n <th>firstname</th>\n <th>joindate</th>\n </tr>\n <tr>\n <td>24</td>\n <td>Sarwin</td>\n <td>Ramnaresh</td>\n <td>2012-09-01 08:44:42</td>\n </tr>\n <tr>\n <td>26</td>\n <td>Jones</td>\n <td>Douglas</td>\n <td>2012-09-02 18:43:05</td>\n </tr>\n <tr>\n <td>27</td>\n <td>Rumney</td>\n <td>Henrietta</td>\n <td>2012-09-05 08:42:35</td>\n </tr>\n <tr>\n <td>28</td>\n <td>Farrell</td>\n <td>David</td>\n <td>2012-09-15 08:22:05</td>\n </tr>\n <tr>\n <td>29</td>\n <td>Worthington-Smyth</td>\n <td>Henry</td>\n <td>2012-09-17 12:27:15</td>\n </tr>\n <tr>\n <td>30</td>\n <td>Purview</td>\n <td>Millicent</td>\n <td>2012-09-18 19:04:01</td>\n </tr>\n <tr>\n <td>33</td>\n <td>Tupperware</td>\n <td>Hyacinth</td>\n <td>2012-09-18 19:32:05</td>\n </tr>\n <tr>\n <td>35</td>\n <td>Hunt</td>\n <td>John</td>\n <td>2012-09-19 11:32:45</td>\n </tr>\n <tr>\n <td>36</td>\n <td>Crumpet</td>\n <td>Erica</td>\n <td>2012-09-22 08:36:38</td>\n </tr>\n <tr>\n <td>37</td>\n <td>Smith</td>\n <td>Darren</td>\n <td>2012-09-26 18:08:45</td>\n </tr>\n</table>" | |
| }, | |
| "metadata": {}, | |
| "execution_count": 31 | |
| } | |
| ], | |
| "source": [ | |
| "%sql SELECT memid, surname, firstname, joindate FROM cd.members where joindate > '2012-09-01 00:00:00';" | |
| ] | |
| }, | |
| { | |
| "source": [ | |
| "## 8) How can you produce an ordered list of the first 10 surnames in the members table? The list must not contain duplicates." | |
| ], | |
| "cell_type": "markdown", | |
| "metadata": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 33, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "output_type": "stream", | |
| "name": "stdout", | |
| "text": [ | |
| " * postgres://postgres:***@localhost:5432/exercise\n10 rows affected.\n" | |
| ] | |
| }, | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "[('Bader',),\n", | |
| " ('Baker',),\n", | |
| " ('Boothe',),\n", | |
| " ('Butters',),\n", | |
| " ('Coplin',),\n", | |
| " ('Crumpet',),\n", | |
| " ('Dare',),\n", | |
| " ('Farrell',),\n", | |
| " ('Genting',),\n", | |
| " ('GUEST',)]" | |
| ], | |
| "text/html": "<table>\n <tr>\n <th>surname</th>\n </tr>\n <tr>\n <td>Bader</td>\n </tr>\n <tr>\n <td>Baker</td>\n </tr>\n <tr>\n <td>Boothe</td>\n </tr>\n <tr>\n <td>Butters</td>\n </tr>\n <tr>\n <td>Coplin</td>\n </tr>\n <tr>\n <td>Crumpet</td>\n </tr>\n <tr>\n <td>Dare</td>\n </tr>\n <tr>\n <td>Farrell</td>\n </tr>\n <tr>\n <td>Genting</td>\n </tr>\n <tr>\n <td>GUEST</td>\n </tr>\n</table>" | |
| }, | |
| "metadata": {}, | |
| "execution_count": 33 | |
| } | |
| ], | |
| "source": [ | |
| "%sql SELECT DISTINCT surname from cd.members ORDER BY 1 LIMIT 10; " | |
| ] | |
| }, | |
| { | |
| "source": [ | |
| "## 9) You'd like to get the signup date of your last member. How can youre trieve this information?" | |
| ], | |
| "cell_type": "markdown", | |
| "metadata": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 42, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "output_type": "stream", | |
| "name": "stdout", | |
| "text": [ | |
| " * postgres://postgres:***@localhost:5432/exercise\n1 rows affected.\n" | |
| ] | |
| }, | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "[(datetime.datetime(2012, 9, 26, 18, 8, 45),)]" | |
| ], | |
| "text/html": "<table>\n <tr>\n <th>latest_signup</th>\n </tr>\n <tr>\n <td>2012-09-26 18:08:45</td>\n </tr>\n</table>" | |
| }, | |
| "metadata": {}, | |
| "execution_count": 42 | |
| } | |
| ], | |
| "source": [ | |
| "%sql select joindate as latest_signup from cd.members order by joindate desc limit 1; " | |
| ] | |
| }, | |
| { | |
| "source": [ | |
| "## 10) Produce a count of the number of facilities that have a cost to guests of 10 or more." | |
| ], | |
| "cell_type": "markdown", | |
| "metadata": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 41, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "output_type": "stream", | |
| "name": "stdout", | |
| "text": [ | |
| " * postgres://postgres:***@localhost:5432/exercise\n1 rows affected.\n" | |
| ] | |
| }, | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "[(6,)]" | |
| ], | |
| "text/html": "<table>\n <tr>\n <th>count</th>\n </tr>\n <tr>\n <td>6</td>\n </tr>\n</table>" | |
| }, | |
| "metadata": {}, | |
| "execution_count": 41 | |
| } | |
| ], | |
| "source": [ | |
| "%sql select count(*) from cd.facilities where guestcost >= 10;" | |
| ] | |
| }, | |
| { | |
| "source": [ | |
| "## 11) Produce a list of the total number of slots booked per facility in the month of September 2012. Produce an output table consisting of facility id and slots, sorted by the number of slots." | |
| ], | |
| "cell_type": "markdown", | |
| "metadata": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": null, | |
| "metadata": {}, | |
| "outputs": [], | |
| "source": [ | |
| "%sql select facid,sum(slots) from cd.bookings \n", | |
| "where starttime > '2012-09-01' AND starttime < '2012-10-01' group by facid ORDER BY SUM(slots) ;" | |
| ] | |
| }, | |
| { | |
| "source": [ | |
| "## 12) Produce a list of facilities with more than 1000 slots booked. Produce an output table consisting of facility id and total slots, sorted by facility id." | |
| ], | |
| "cell_type": "markdown", | |
| "metadata": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": null, | |
| "metadata": {}, | |
| "outputs": [], | |
| "source": [ | |
| "%sql select facid,sum(slots) as total_slots from cd.bookings \n", | |
| "group by facid having sum(slots)>1000 order by facid;" | |
| ] | |
| }, | |
| { | |
| "source": [ | |
| "## 13) How can you produce a list of the start times for bookings for tennis courts, for the date '2012-09-21'? Return a list of start time and facility name pairings, ordered by the time." | |
| ], | |
| "cell_type": "markdown", | |
| "metadata": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 37, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "output_type": "stream", | |
| "name": "stdout", | |
| "text": [ | |
| " * postgres://postgres:***@localhost:5432/exercise\n21 rows affected.\n" | |
| ] | |
| }, | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "[(datetime.datetime(2012, 9, 21, 8, 0), 'Tennis Court 2'),\n", | |
| " (datetime.datetime(2012, 9, 21, 8, 0), 'Tennis Court 1'),\n", | |
| " (datetime.datetime(2012, 9, 21, 9, 0), 'Table Tennis'),\n", | |
| " (datetime.datetime(2012, 9, 21, 9, 30), 'Tennis Court 1'),\n", | |
| " (datetime.datetime(2012, 9, 21, 10, 0), 'Table Tennis'),\n", | |
| " (datetime.datetime(2012, 9, 21, 10, 0), 'Tennis Court 2'),\n", | |
| " (datetime.datetime(2012, 9, 21, 11, 0), 'Table Tennis'),\n", | |
| " (datetime.datetime(2012, 9, 21, 11, 30), 'Tennis Court 2'),\n", | |
| " (datetime.datetime(2012, 9, 21, 12, 0), 'Tennis Court 1'),\n", | |
| " (datetime.datetime(2012, 9, 21, 13, 0), 'Table Tennis'),\n", | |
| " (datetime.datetime(2012, 9, 21, 13, 30), 'Tennis Court 1'),\n", | |
| " (datetime.datetime(2012, 9, 21, 14, 0), 'Table Tennis'),\n", | |
| " (datetime.datetime(2012, 9, 21, 14, 0), 'Tennis Court 2'),\n", | |
| " (datetime.datetime(2012, 9, 21, 15, 30), 'Table Tennis'),\n", | |
| " (datetime.datetime(2012, 9, 21, 15, 30), 'Tennis Court 1'),\n", | |
| " (datetime.datetime(2012, 9, 21, 16, 0), 'Tennis Court 2'),\n", | |
| " (datetime.datetime(2012, 9, 21, 16, 30), 'Table Tennis'),\n", | |
| " (datetime.datetime(2012, 9, 21, 17, 0), 'Tennis Court 1'),\n", | |
| " (datetime.datetime(2012, 9, 21, 18, 0), 'Tennis Court 2'),\n", | |
| " (datetime.datetime(2012, 9, 21, 18, 30), 'Table Tennis'),\n", | |
| " (datetime.datetime(2012, 9, 21, 19, 30), 'Table Tennis')]" | |
| ], | |
| "text/html": "<table>\n <tr>\n <th>start</th>\n <th>name</th>\n </tr>\n <tr>\n <td>2012-09-21 08:00:00</td>\n <td>Tennis Court 2</td>\n </tr>\n <tr>\n <td>2012-09-21 08:00:00</td>\n <td>Tennis Court 1</td>\n </tr>\n <tr>\n <td>2012-09-21 09:00:00</td>\n <td>Table Tennis</td>\n </tr>\n <tr>\n <td>2012-09-21 09:30:00</td>\n <td>Tennis Court 1</td>\n </tr>\n <tr>\n <td>2012-09-21 10:00:00</td>\n <td>Table Tennis</td>\n </tr>\n <tr>\n <td>2012-09-21 10:00:00</td>\n <td>Tennis Court 2</td>\n </tr>\n <tr>\n <td>2012-09-21 11:00:00</td>\n <td>Table Tennis</td>\n </tr>\n <tr>\n <td>2012-09-21 11:30:00</td>\n <td>Tennis Court 2</td>\n </tr>\n <tr>\n <td>2012-09-21 12:00:00</td>\n <td>Tennis Court 1</td>\n </tr>\n <tr>\n <td>2012-09-21 13:00:00</td>\n <td>Table Tennis</td>\n </tr>\n <tr>\n <td>2012-09-21 13:30:00</td>\n <td>Tennis Court 1</td>\n </tr>\n <tr>\n <td>2012-09-21 14:00:00</td>\n <td>Table Tennis</td>\n </tr>\n <tr>\n <td>2012-09-21 14:00:00</td>\n <td>Tennis Court 2</td>\n </tr>\n <tr>\n <td>2012-09-21 15:30:00</td>\n <td>Table Tennis</td>\n </tr>\n <tr>\n <td>2012-09-21 15:30:00</td>\n <td>Tennis Court 1</td>\n </tr>\n <tr>\n <td>2012-09-21 16:00:00</td>\n <td>Tennis Court 2</td>\n </tr>\n <tr>\n <td>2012-09-21 16:30:00</td>\n <td>Table Tennis</td>\n </tr>\n <tr>\n <td>2012-09-21 17:00:00</td>\n <td>Tennis Court 1</td>\n </tr>\n <tr>\n <td>2012-09-21 18:00:00</td>\n <td>Tennis Court 2</td>\n </tr>\n <tr>\n <td>2012-09-21 18:30:00</td>\n <td>Table Tennis</td>\n </tr>\n <tr>\n <td>2012-09-21 19:30:00</td>\n <td>Table Tennis</td>\n </tr>\n</table>" | |
| }, | |
| "metadata": {}, | |
| "execution_count": 37 | |
| } | |
| ], | |
| "source": [ | |
| "%sql select starttime as start ,name from cd.bookings left join cd.facilities on cd.facilities.facid=cd.bookings.facid where cd.facilities.name like '%Tennis%' and cd.bookings.starttime between'2012-09-21 00:00:00' and '2012-09-21 24:00:00' order by start;" | |
| ] | |
| }, | |
| { | |
| "source": [ | |
| "## 14) How can you produce a list of the start times for bookings by membersnamed 'David Farrell'?" | |
| ], | |
| "cell_type": "markdown", | |
| "metadata": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": 40, | |
| "metadata": {}, | |
| "outputs": [ | |
| { | |
| "output_type": "stream", | |
| "name": "stdout", | |
| "text": [ | |
| " * postgres://postgres:***@localhost:5432/exercise\n34 rows affected.\n" | |
| ] | |
| }, | |
| { | |
| "output_type": "execute_result", | |
| "data": { | |
| "text/plain": [ | |
| "[(datetime.datetime(2012, 9, 18, 9, 0),),\n", | |
| " (datetime.datetime(2012, 9, 18, 13, 30),),\n", | |
| " (datetime.datetime(2012, 9, 18, 17, 30),),\n", | |
| " (datetime.datetime(2012, 9, 18, 20, 0),),\n", | |
| " (datetime.datetime(2012, 9, 19, 9, 30),),\n", | |
| " (datetime.datetime(2012, 9, 19, 12, 0),),\n", | |
| " (datetime.datetime(2012, 9, 19, 15, 0),),\n", | |
| " (datetime.datetime(2012, 9, 20, 11, 30),),\n", | |
| " (datetime.datetime(2012, 9, 20, 14, 0),),\n", | |
| " (datetime.datetime(2012, 9, 20, 15, 30),),\n", | |
| " (datetime.datetime(2012, 9, 21, 10, 30),),\n", | |
| " (datetime.datetime(2012, 9, 21, 14, 0),),\n", | |
| " (datetime.datetime(2012, 9, 22, 8, 30),),\n", | |
| " (datetime.datetime(2012, 9, 22, 17, 0),),\n", | |
| " (datetime.datetime(2012, 9, 23, 8, 30),),\n", | |
| " (datetime.datetime(2012, 9, 23, 17, 30),),\n", | |
| " (datetime.datetime(2012, 9, 23, 19, 0),),\n", | |
| " (datetime.datetime(2012, 9, 24, 8, 0),),\n", | |
| " (datetime.datetime(2012, 9, 24, 12, 30),),\n", | |
| " (datetime.datetime(2012, 9, 24, 16, 30),),\n", | |
| " (datetime.datetime(2012, 9, 25, 15, 30),),\n", | |
| " (datetime.datetime(2012, 9, 25, 17, 0),),\n", | |
| " (datetime.datetime(2012, 9, 26, 13, 0),),\n", | |
| " (datetime.datetime(2012, 9, 26, 17, 0),),\n", | |
| " (datetime.datetime(2012, 9, 27, 8, 0),),\n", | |
| " (datetime.datetime(2012, 9, 28, 9, 30),),\n", | |
| " (datetime.datetime(2012, 9, 28, 11, 30),),\n", | |
| " (datetime.datetime(2012, 9, 28, 13, 0),),\n", | |
| " (datetime.datetime(2012, 9, 29, 10, 30),),\n", | |
| " (datetime.datetime(2012, 9, 29, 13, 30),),\n", | |
| " (datetime.datetime(2012, 9, 29, 14, 30),),\n", | |
| " (datetime.datetime(2012, 9, 29, 16, 0),),\n", | |
| " (datetime.datetime(2012, 9, 29, 17, 30),),\n", | |
| " (datetime.datetime(2012, 9, 30, 14, 30),)]" | |
| ], | |
| "text/html": "<table>\n <tr>\n <th>starttime</th>\n </tr>\n <tr>\n <td>2012-09-18 09:00:00</td>\n </tr>\n <tr>\n <td>2012-09-18 13:30:00</td>\n </tr>\n <tr>\n <td>2012-09-18 17:30:00</td>\n </tr>\n <tr>\n <td>2012-09-18 20:00:00</td>\n </tr>\n <tr>\n <td>2012-09-19 09:30:00</td>\n </tr>\n <tr>\n <td>2012-09-19 12:00:00</td>\n </tr>\n <tr>\n <td>2012-09-19 15:00:00</td>\n </tr>\n <tr>\n <td>2012-09-20 11:30:00</td>\n </tr>\n <tr>\n <td>2012-09-20 14:00:00</td>\n </tr>\n <tr>\n <td>2012-09-20 15:30:00</td>\n </tr>\n <tr>\n <td>2012-09-21 10:30:00</td>\n </tr>\n <tr>\n <td>2012-09-21 14:00:00</td>\n </tr>\n <tr>\n <td>2012-09-22 08:30:00</td>\n </tr>\n <tr>\n <td>2012-09-22 17:00:00</td>\n </tr>\n <tr>\n <td>2012-09-23 08:30:00</td>\n </tr>\n <tr>\n <td>2012-09-23 17:30:00</td>\n </tr>\n <tr>\n <td>2012-09-23 19:00:00</td>\n </tr>\n <tr>\n <td>2012-09-24 08:00:00</td>\n </tr>\n <tr>\n <td>2012-09-24 12:30:00</td>\n </tr>\n <tr>\n <td>2012-09-24 16:30:00</td>\n </tr>\n <tr>\n <td>2012-09-25 15:30:00</td>\n </tr>\n <tr>\n <td>2012-09-25 17:00:00</td>\n </tr>\n <tr>\n <td>2012-09-26 13:00:00</td>\n </tr>\n <tr>\n <td>2012-09-26 17:00:00</td>\n </tr>\n <tr>\n <td>2012-09-27 08:00:00</td>\n </tr>\n <tr>\n <td>2012-09-28 09:30:00</td>\n </tr>\n <tr>\n <td>2012-09-28 11:30:00</td>\n </tr>\n <tr>\n <td>2012-09-28 13:00:00</td>\n </tr>\n <tr>\n <td>2012-09-29 10:30:00</td>\n </tr>\n <tr>\n <td>2012-09-29 13:30:00</td>\n </tr>\n <tr>\n <td>2012-09-29 14:30:00</td>\n </tr>\n <tr>\n <td>2012-09-29 16:00:00</td>\n </tr>\n <tr>\n <td>2012-09-29 17:30:00</td>\n </tr>\n <tr>\n <td>2012-09-30 14:30:00</td>\n </tr>\n</table>" | |
| }, | |
| "metadata": {}, | |
| "execution_count": 40 | |
| } | |
| ], | |
| "source": [ | |
| "%sql select starttime from cd.bookings left join cd.members on cd.members.memid=cd.bookings.memid where firstname like 'David' and surname like 'Farrell';\n" | |
| ] | |
| }, | |
| { | |
| "cell_type": "code", | |
| "execution_count": null, | |
| "metadata": {}, | |
| "outputs": [], | |
| "source": [] | |
| } | |
| ] | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment