-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathSQLTasks Tier 1.sql
105 lines (66 loc) · 4.11 KB
/
SQLTasks Tier 1.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
/* Welcome to the SQL mini project. You will carry out this project partly in
the PHPMyAdmin interface, and partly in Jupyter via a Python connection.
This is Tier 1 of the case study, which means that there'll be more guidance for you about how to
setup your local SQLite connection in PART 2 of the case study.
The questions in the case study are exactly the same as with Tier 2.
PART 1: PHPMyAdmin
You will complete questions 1-9 below in the PHPMyAdmin interface.
Log in by pasting the following URL into your browser, and
using the following Username and Password:
URL: https://sql.springboard.com/
Username: student
Password: learn_sql@springboard
The data you need is in the "country_club" database. This database
contains 3 tables:
i) the "Bookings" table,
ii) the "Facilities" table, and
iii) the "Members" table.
In this case study, you'll be asked a series of questions. You can
solve them using the platform, but for the final deliverable,
paste the code for each solution into this script, and upload it
to your GitHub.
Before starting with the questions, feel free to take your time,
exploring the data, and getting acquainted with the 3 tables. */
/* QUESTIONS
/* Q1: Some of the facilities charge a fee to members, but some do not.
Write a SQL query to produce a list of the names of the facilities that do. */
/* Q2: How many facilities do not charge a fee to members? */
/* Q3: Write an SQL query to show a list of facilities that charge a fee to members,
where the fee is less than 20% of the facility's monthly maintenance cost.
Return the facid, facility name, member cost, and monthly maintenance of the
facilities in question. */
/* Q4: Write an SQL query to retrieve the details of facilities with ID 1 and 5.
Try writing the query without using the OR operator. */
/* Q5: Produce a list of facilities, with each labelled as
'cheap' or 'expensive', depending on if their monthly maintenance cost is
more than $100. Return the name and monthly maintenance of the facilities
in question. */
/* Q6: You'd like to get the first and last name of the last member(s)
who signed up. Try not to use the LIMIT clause for your solution. */
/* Q7: Produce a list of all members who have used a tennis court.
Include in your output the name of the court, and the name of the member
formatted as a single column. Ensure no duplicate data, and order by
the member name. */
/* Q8: Produce a list of bookings on the day of 2012-09-14 which
will cost the member (or guest) more than $30. Remember that guests have
different costs to members (the listed costs are per half-hour 'slot'), and
the guest user's ID is always 0. Include in your output the name of the
facility, the name of the member formatted as a single column, and the cost.
Order by descending cost, and do not use any subqueries. */
/* Q9: This time, produce the same result as in Q8, but using a subquery. */
/* PART 2: SQLite
/* We now want you to jump over to a local instance of the database on your machine.
Copy and paste the LocalSQLConnection.py script into an empty Jupyter notebook, and run it.
Make sure that the SQLFiles folder containing thes files is in your working directory, and
that you haven't changed the name of the .db file from 'sqlite\db\pythonsqlite'.
You should see the output from the initial query 'SELECT * FROM FACILITIES'.
Complete the remaining tasks in the Jupyter interface. If you struggle, feel free to go back
to the PHPMyAdmin interface as and when you need to.
You'll need to paste your query into value of the 'query1' variable and run the code block again to get an output.
QUESTIONS:
/* Q10: Produce a list of facilities with a total revenue less than 1000.
The output of facility name and total revenue, sorted by revenue. Remember
that there's a different cost for guests and members! */
/* Q11: Produce a report of members and who recommended them in alphabetic surname,firstname order */
/* Q12: Find the facilities with their usage by member, but not guests */
/* Q13: Find the facilities usage by month, but not guests */