Code
library(DBI)Warning: package 'DBI' was built under R version 4.2.3Code
library(RPostgreSQL)Warning: package 'RPostgreSQL' was built under R version 4.2.3TareaRSQL
Code
con <- dbConnect(RPostgres::Postgres(),
dbname = 'tareardb2',
host = 'ep-calm-recipe-a5yufidh.us-east-2.aws.neon.tech',
port = 5432,
user = 'tareardb2_owner',
password = 'I3rq4uNkmysa')Creacion de DB
dbExecute(con, “CREATE TABLE bookings ( bookid integer NOT NULL, facid integer NOT NULL, memid integer NOT NULL, starttime timestamp without time zone NOT NULL, slots integer NOT NULL );”)
dbExecute(con,“CREATE TABLE facilities ( facid integer NOT NULL, name character varying(100) NOT NULL, membercost numeric NOT NULL, guestcost numeric NOT NULL, initialoutlay numeric NOT NULL, monthlymaintenance numeric NOT NULL );”)
dbExecute(con, “CREATE TABLE members ( memid integer NOT NULL, surname character varying(200) NOT NULL, firstname character varying(200) NOT NULL, address character varying(300) NOT NULL, zipcode integer NOT NULL, telephone character varying(20) NOT NULL, recommendedby integer, joindate timestamp without time zone NOT NULL );”)
dbExecute(con,“INSERT INTO members (memid, surname, firstname, address, zipcode, telephone, recommendedby, joindate) VALUES (0, ‘GUEST’, ‘GUEST’, ‘GUEST’, 0, ‘(000) 000-0000’, NULL, ‘2012-07-01 00:00:00’), (1, ‘Smith’, ‘Darren’, ‘8 Bloomsbury Close, Boston’, 4321, ‘555-555-5555’, NULL, ‘2012-07-02 12:02:05’), (2, ‘Smith’, ‘Tracy’, ‘8 Bloomsbury Close, New York’, 4321, ‘555-555-5555’, NULL, ‘2012-07-02 12:08:23’), (3, ‘Rownam’, ‘Tim’, ‘23 Highway Way, Boston’, 23423, ‘(844) 693-0723’, NULL, ‘2012-07-03 09:32:15’), (4, ‘Joplette’, ‘Janice’, ‘20 Crossing Road, New York’, 234, ‘(833) 942-4710’, 1, ‘2012-07-03 10:25:05’), (5, ‘Butters’, ‘Gerald’, ‘1065 Huntingdon Avenue, Boston’, 56754, ‘(844) 078-4130’, 1, ‘2012-07-09 10:44:09’), (6, ‘Tracy’, ‘Burton’, ‘3 Tunisia Drive, Boston’, 45678, ‘(822) 354-9973’, NULL, ‘2012-07-15 08:52:55’), (7, ‘Dare’, ‘Nancy’, ‘6 Hunting Lodge Way, Boston’, 10383, ‘(833) 776-4001’, 4, ‘2012-07-25 08:59:12’), (8, ‘Boothe’, ‘Tim’, ‘3 Bloomsbury Close, Reading, 00234’, 234, ‘(811) 433-2547’, 3, ‘2012-07-25 16:02:35’), (9, ‘Stibbons’, ‘Ponder’, ‘5 Dragons Way, Winchester’, 87630, ‘(833) 160-3900’, 6, ‘2012-07-25 17:09:05’), (10, ‘Owen’, ‘Charles’, ‘52 Cheshire Grove, Winchester, 28563’, 28563, ‘(855) 542-5251’, 1, ‘2012-08-03 19:42:37’), (11, ‘Jones’, ‘David’, ‘976 Gnats Close, Reading’, 33862, ‘(844) 536-8036’, 4, ‘2012-08-06 16:32:55’), (12, ‘Baker’, ‘Anne’, ‘55 Powdery Street, Boston’, 80743, ‘844-076-5141’, 9, ‘2012-08-10 14:23:22’), (13, ‘Farrell’, ‘Jemima’, ‘103 Firth Avenue, North Reading’, 57392, ‘(855) 016-0163’, NULL, ‘2012-08-10 14:28:01’), (14, ‘Smith’, ‘Jack’, ‘252 Binkington Way, Boston’, 69302, ‘(822) 163-3254’, 1, ‘2012-08-10 16:22:05’), (15, ‘Bader’, ‘Florence’, ‘264 Ursula Drive, Westford’, 84923, ‘(833) 499-3527’, 9, ‘2012-08-10 17:52:03’), (16, ‘Baker’, ‘Timothy’, ‘329 James Street, Reading’, 58393, ‘833-941-0824’, 13, ‘2012-08-15 10:34:25’), (17, ‘Pinker’, ‘David’, ‘5 Impreza Road, Boston’, 65332, ‘811 409-6734’, 13, ‘2012-08-16 11:32:47’), (20, ‘Genting’, ‘Matthew’, ‘4 Nunnington Place, Wingfield, Boston’, 52365, ‘(811) 972-1377’, 5, ‘2012-08-19 14:55:55’), (21, ‘Mackenzie’, ‘Anna’, ‘64 Perkington Lane, Reading’, 64577, ‘(822) 661-2898’, 1, ‘2012-08-26 09:32:05’), (22, ‘Coplin’, ‘Joan’, ‘85 Bard Street, Bloomington, Boston’, 43533, ‘(822) 499-2232’, 16, ‘2012-08-29 08:32:41’), (24, ‘Sarwin’, ‘Ramnaresh’, ‘12 Bullington Lane, Boston’, 65464, ‘(822) 413-1470’, 15, ‘2012-09-01 08:44:42’), (26, ‘Jones’, ‘Douglas’, ‘976 Gnats Close, Reading’, 11986, ‘844 536-8036’, 11, ‘2012-09-02 18:43:05’), (27, ‘Rumney’, ‘Henrietta’, ‘3 Burkington Plaza, Boston’, 78533, ‘(822) 989-8876’, 20, ‘2012-09-05 08:42:35’), (28, ‘Farrell’, ‘David’, ‘437 Granite Farm Road, Westford’, 43532, ‘(855) 755-9876’, NULL, ‘2012-09-15 08:22:05’), (29, ‘Worthington-Smyth’, ‘Henry’, ‘55 Jagbi Way, North Reading’, 97676, ‘(855) 894-3758’, 2, ‘2012-09-17 12:27:15’), (30, ‘Purview’, ‘Millicent’, ‘641 Drudgery Close, Burnington, Boston’, 34232, ‘(855) 941-9786’, 2, ‘2012-09-18 19:04:01’), (33, ‘Tupperware’, ‘Hyacinth’, ‘33 Cheerful Plaza, Drake Road, Westford’, 68666, ‘(822) 665-5327’, NULL, ‘2012-09-18 19:32:05’), (35, ‘Hunt’, ‘John’, ‘5 Bullington Lane, Boston’, 54333, ‘(899) 720-6978’, 30, ‘2012-09-19 11:32:45’), (36, ‘Crumpet’, ‘Erica’, ‘Crimson Road, North Reading’, 75655, ‘(811) 732-4816’, 2, ‘2012-09-22 08:36:38’), (37, ‘Smith’, ‘Darren’, ‘3 Funktown, Denzington, Boston’, 66796, ‘(822) 577-3541’, NULL, ‘2012-09-26 18:08:45’);”)
dbExecute(con,"INSERT INTO facilities (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance) VALUES
(0, 'Tennis Court 1', 5, 25, 10000, 200),
(1, 'Tennis Court 2', 5, 25, 8000, 200),
(2, 'Badminton Court', 0, 15.5, 4000, 50),
(3, 'Table Tennis', 0, 5, 320, 10),
(4, 'Massage Room 1', 35, 80, 4000, 3000),
(5, 'Massage Room 2', 35, 80, 4000, 3000),
(6, 'Squash Court', 3.5, 17.5, 5000, 80),
(7, 'Snooker Table', 0, 5, 450, 15),
(8, 'Pool Table', 0, 5, 400, 15);")
dbExecute(con,"INSERT INTO bookings (bookid, facid, memid, starttime, slots) VALUES
(0, 3, 1, '2012-07-03 11:00:00', 2),
(1, 4, 1, '2012-07-03 08:00:00', 2),
(2, 6, 0, '2012-07-03 18:00:00', 2),
(3, 7, 1, '2012-07-03 19:00:00', 2),
(4, 8, 1, '2012-07-03 10:00:00', 1),
(5, 8, 1, '2012-07-03 15:00:00', 1),
(6, 0, 2, '2012-07-04 09:00:00', 3),
(7, 0, 2, '2012-07-04 15:00:00', 3),
(8, 4, 3, '2012-07-04 13:30:00', 2),
(9, 4, 0, '2012-07-04 15:00:00', 2),
(10, 4, 0, '2012-07-04 17:30:00', 2),
(11, 6, 0, '2012-07-04 12:30:00', 2),
(12, 6, 0, '2012-07-04 14:00:00', 2),
(13, 6, 1, '2012-07-04 15:30:00', 2),
(14, 7, 2, '2012-07-04 14:00:00', 2),
(15, 8, 2, '2012-07-04 12:00:00', 1),
(16, 8, 3, '2012-07-04 18:00:00', 1),
(17, 1, 0, '2012-07-05 17:30:00', 3),
(18, 2, 1, '2012-07-05 09:30:00', 3),
(etc));")Basic
Code
dbGetQuery(con, "SELECT * FROM facilities;") facid name membercost guestcost initialoutlay monthlymaintenance
1 2 Badminton Court 0.0 15.5 4000 50
2 3 Table Tennis 0.0 5.0 320 10
3 4 Massage Room 1 35.0 80.0 4000 3000
4 5 Massage Room 2 35.0 80.0 4000 3000
5 6 Squash Court 3.5 17.5 5000 80
6 7 Snooker Table 0.0 5.0 450 15
7 8 Pool Table 0.0 5.0 400 15
8 9 Spa 20.0 30.0 100000 800
9 9 Spa 20.0 30.0 100000 800
10 10 Squash Court 2 3.5 17.5 5000 80
11 11 Spa 20.0 30.0 100000 800
12 9 Spa 20.0 30.0 100000 800
13 9 Spa 20.0 30.0 100000 800
14 10 Squash Court 2 3.5 17.5 5000 80
15 12 Spa 20.0 30.0 100000 800
16 9 Spa 20.0 30.0 100000 800
17 9 Spa 20.0 30.0 100000 800
18 10 Squash Court 2 3.5 17.5 5000 80
19 13 Spa 20.0 30.0 100000 800
20 9 Spa 20.0 30.0 100000 800
21 9 Spa 20.0 30.0 100000 800
22 10 Squash Court 2 3.5 17.5 5000 80
23 14 Spa 20.0 30.0 100000 800
24 0 Tennis Court 1 6.0 30.0 10000 200
25 1 Tennis Court 2 6.6 33.0 10000 200Code
dbGetQuery(con,"select name,membercost from facilities") name membercost
1 Badminton Court 0.0
2 Table Tennis 0.0
3 Massage Room 1 35.0
4 Massage Room 2 35.0
5 Squash Court 3.5
6 Snooker Table 0.0
7 Pool Table 0.0
8 Spa 20.0
9 Spa 20.0
10 Squash Court 2 3.5
11 Spa 20.0
12 Spa 20.0
13 Spa 20.0
14 Squash Court 2 3.5
15 Spa 20.0
16 Spa 20.0
17 Spa 20.0
18 Squash Court 2 3.5
19 Spa 20.0
20 Spa 20.0
21 Spa 20.0
22 Squash Court 2 3.5
23 Spa 20.0
24 Tennis Court 1 6.0
25 Tennis Court 2 6.6Code
dbGetQuery(con,"SELECT * FROM facilities WHERE membercost>0") facid name membercost guestcost initialoutlay monthlymaintenance
1 4 Massage Room 1 35.0 80.0 4e+03 3000
2 5 Massage Room 2 35.0 80.0 4e+03 3000
3 6 Squash Court 3.5 17.5 5e+03 80
4 9 Spa 20.0 30.0 1e+05 800
5 9 Spa 20.0 30.0 1e+05 800
6 10 Squash Court 2 3.5 17.5 5e+03 80
7 11 Spa 20.0 30.0 1e+05 800
8 9 Spa 20.0 30.0 1e+05 800
9 9 Spa 20.0 30.0 1e+05 800
10 10 Squash Court 2 3.5 17.5 5e+03 80
11 12 Spa 20.0 30.0 1e+05 800
12 9 Spa 20.0 30.0 1e+05 800
13 9 Spa 20.0 30.0 1e+05 800
14 10 Squash Court 2 3.5 17.5 5e+03 80
15 13 Spa 20.0 30.0 1e+05 800
16 9 Spa 20.0 30.0 1e+05 800
17 9 Spa 20.0 30.0 1e+05 800
18 10 Squash Court 2 3.5 17.5 5e+03 80
19 14 Spa 20.0 30.0 1e+05 800
20 0 Tennis Court 1 6.0 30.0 1e+04 200
21 1 Tennis Court 2 6.6 33.0 1e+04 200Code
dbGetQuery(con,"SELECT
facid,
name,
membercost,
monthlymaintenance
FROM
facilities
WHERE
membercost > 0
AND membercost < monthlymaintenance / 50;") facid name membercost monthlymaintenance
1 4 Massage Room 1 35 3000
2 5 Massage Room 2 35 3000Code
dbGetQuery(con,"SELECT *
FROM facilities
WHERE name LIKE '%Tennis%';") facid name membercost guestcost initialoutlay monthlymaintenance
1 3 Table Tennis 0.0 5 320 10
2 0 Tennis Court 1 6.0 30 10000 200
3 1 Tennis Court 2 6.6 33 10000 200Code
dbGetQuery(con,"SELECT *
FROM facilities
WHERE facid IN (1, 5);") facid name membercost guestcost initialoutlay monthlymaintenance
1 5 Massage Room 2 35.0 80 4000 3000
2 1 Tennis Court 2 6.6 33 10000 200Code
dbGetQuery(con,"SELECT
name,
CASE
WHEN monthlymaintenance > 100 THEN 'expensive'
ELSE 'cheap'
END AS cost
FROM
facilities;") name cost
1 Badminton Court cheap
2 Table Tennis cheap
3 Massage Room 1 expensive
4 Massage Room 2 expensive
5 Squash Court cheap
6 Snooker Table cheap
7 Pool Table cheap
8 Spa expensive
9 Spa expensive
10 Squash Court 2 cheap
11 Spa expensive
12 Spa expensive
13 Spa expensive
14 Squash Court 2 cheap
15 Spa expensive
16 Spa expensive
17 Spa expensive
18 Squash Court 2 cheap
19 Spa expensive
20 Spa expensive
21 Spa expensive
22 Squash Court 2 cheap
23 Spa expensive
24 Tennis Court 1 expensive
25 Tennis Court 2 expensiveCode
dbGetQuery(con,"SELECT
memid,
surname,
firstname,
joindate
FROM
members
WHERE
joindate >= '2012-09-01';") memid surname firstname joindate
1 24 Sarwin Ramnaresh 2012-09-01 08:44:42
2 26 Jones Douglas 2012-09-02 18:43:05
3 27 Rumney Henrietta 2012-09-05 08:42:35
4 28 Farrell David 2012-09-15 08:22:05
5 29 Worthington-Smyth Henry 2012-09-17 12:27:15
6 30 Purview Millicent 2012-09-18 19:04:01
7 33 Tupperware Hyacinth 2012-09-18 19:32:05
8 35 Hunt John 2012-09-19 11:32:45
9 36 Crumpet Erica 2012-09-22 08:36:38
10 37 Smith Darren 2012-09-26 18:08:45Code
dbGetQuery(con,"SELECT DISTINCT surname
FROM members
ORDER BY surname
LIMIT 10;") surname
1 Bader
2 Baker
3 Boothe
4 Butters
5 Coplin
6 Crumpet
7 Dare
8 Farrell
9 GUEST
10 GentingCode
dbGetQuery(con,"")Warning: Don't need to call dbFetch() for statements, only for queriesdata frame with 0 columns and 0 rowsCode
dbGetQuery(con,"SELECT surname FROM members
UNION
SELECT name FROM facilities;") surname
1 Table Tennis
2 Tupperware
3 Massage Room 2
4 Jones
5 Boothe
6 Massage Room 1
7 Smith
8 Owen
9 Purview
10 Snooker Table
11 Joplette
12 Hunt
13 GUEST
14 Badminton Court
15 Butters
16 Coplin
17 Genting
18 Bader
19 Worthington-Smyth
20 Pinker
21 Farrell
22 Dare
23 Tennis Court 1
24 Squash Court
25 Sarwin
26 Baker
27 Spa
28 Tennis Court 2
29 Rownam
30 Pool Table
31 Stibbons
32 Rumney
33 Tracy
34 Crumpet
35 Mackenzie
36 Squash Court 2Code
dbGetQuery(con,"SELECT MAX(joindate) AS latest
FROM members;") latest
1 2012-09-26 18:08:45Code
dbGetQuery(con,"SELECT
firstname,
surname,
joindate
FROM
members
WHERE
joindate = (SELECT MAX(joindate) FROM members);") firstname surname joindate
1 Darren Smith 2012-09-26 18:08:45Joins and Subqueries
Code
dbGetQuery(con,"SELECT
bookings.starttime
FROM
bookings
INNER JOIN
members
ON members.memid = bookings.memid
WHERE
members.firstname = 'David'
AND members.surname = 'Farrell';") starttime
1 2012-09-18 09:00:00
2 2012-09-18 17:30:00
3 2012-09-18 13:30:00
4 2012-09-18 20:00:00
5 2012-09-19 09:30:00
6 2012-09-19 15:00:00
7 2012-09-19 12:00:00
8 2012-09-20 15:30:00
9 2012-09-20 11:30:00
10 2012-09-20 14:00:00
11 2012-09-21 10:30:00
12 2012-09-21 14:00:00
13 2012-09-22 08:30:00
14 2012-09-22 17:00:00
15 2012-09-23 08:30:00
16 2012-09-23 17:30:00
17 2012-09-23 19:00:00
18 2012-09-24 08:00:00
19 2012-09-24 16:30:00
20 2012-09-24 12:30:00
21 2012-09-25 15:30:00
22 2012-09-25 17:00:00
23 2012-09-26 13:00:00
24 2012-09-26 17:00:00
25 2012-09-27 08:00:00
26 2012-09-28 11:30:00
27 2012-09-28 09:30:00
28 2012-09-28 13:00:00
29 2012-09-29 16:00:00
30 2012-09-29 10:30:00
31 2012-09-29 13:30:00
32 2012-09-29 14:30:00
33 2012-09-29 17:30:00
34 2012-09-30 14:30:00Code
dbGetQuery(con,"SELECT
bks.starttime AS start,
fac.name
FROM
bookings AS bks
INNER JOIN
facilities AS fac
ON bks.facid = fac.facid
WHERE
fac.name LIKE 'Tennis Court %'
AND bks.starttime BETWEEN '2012-09-21' AND '2012-09-22'
ORDER BY
bks.starttime;") start name
1 2012-09-21 08:00:00 Tennis Court 1
2 2012-09-21 08:00:00 Tennis Court 2
3 2012-09-21 09:30:00 Tennis Court 1
4 2012-09-21 10:00:00 Tennis Court 2
5 2012-09-21 11:30:00 Tennis Court 2
6 2012-09-21 12:00:00 Tennis Court 1
7 2012-09-21 13:30:00 Tennis Court 1
8 2012-09-21 14:00:00 Tennis Court 2
9 2012-09-21 15:30:00 Tennis Court 1
10 2012-09-21 16:00:00 Tennis Court 2
11 2012-09-21 17:00:00 Tennis Court 1
12 2012-09-21 18:00:00 Tennis Court 2Code
dbGetQuery(con,"select distinct recs.firstname as firstname, recs.surname as surname
from
members mems
inner join members recs
on recs.memid = mems.recommendedby
order by surname, firstname; ") firstname surname
1 Florence Bader
2 Timothy Baker
3 Gerald Butters
4 Jemima Farrell
5 Matthew Genting
6 David Jones
7 Janice Joplette
8 Millicent Purview
9 Tim Rownam
10 Darren Smith
11 Tracy Smith
12 Ponder Stibbons
13 Burton TracyCode
dbGetQuery(con,"select mems.firstname as memfname, mems.surname as memsname, recs.firstname as recfname, recs.surname as recsname
from
members mems
left outer join members recs
on recs.memid = mems.recommendedby
order by memsname, memfname; ") memfname memsname recfname recsname
1 Florence Bader Ponder Stibbons
2 Anne Baker Ponder Stibbons
3 Timothy Baker Jemima Farrell
4 Tim Boothe Tim Rownam
5 Gerald Butters Darren Smith
6 Joan Coplin Timothy Baker
7 Erica Crumpet Tracy Smith
8 Nancy Dare Janice Joplette
9 David Farrell <NA> <NA>
10 Jemima Farrell <NA> <NA>
11 GUEST GUEST <NA> <NA>
12 Matthew Genting Gerald Butters
13 John Hunt Millicent Purview
14 David Jones Janice Joplette
15 Douglas Jones David Jones
16 Janice Joplette Darren Smith
17 Anna Mackenzie Darren Smith
18 Charles Owen Darren Smith
19 David Pinker Jemima Farrell
20 Millicent Purview Tracy Smith
21 Tim Rownam <NA> <NA>
22 Henrietta Rumney Matthew Genting
23 Ramnaresh Sarwin Florence Bader
24 Darren Smith <NA> <NA>
25 Darren Smith <NA> <NA>
26 Jack Smith Darren Smith
27 Tracy Smith <NA> <NA>
28 Ponder Stibbons Burton Tracy
29 Burton Tracy <NA> <NA>
30 Hyacinth Tupperware <NA> <NA>
31 Henry Worthington-Smyth Tracy SmithCode
dbGetQuery(con,"select distinct mems.firstname || ' ' || mems.surname as member, facs.name as facility
from
members mems
inner join bookings bks
on mems.memid = bks.memid
inner join facilities facs
on bks.facid = facs.facid
where
facs.name in ('Tennis Court 2','Tennis Court 1')
order by member, facility ") member facility
1 Anne Baker Tennis Court 1
2 Anne Baker Tennis Court 2
3 Burton Tracy Tennis Court 1
4 Burton Tracy Tennis Court 2
5 Charles Owen Tennis Court 1
6 Charles Owen Tennis Court 2
7 Darren Smith Tennis Court 2
8 David Farrell Tennis Court 1
9 David Farrell Tennis Court 2
10 David Jones Tennis Court 1
11 David Jones Tennis Court 2
12 David Pinker Tennis Court 1
13 Douglas Jones Tennis Court 1
14 Erica Crumpet Tennis Court 1
15 Florence Bader Tennis Court 1
16 Florence Bader Tennis Court 2
17 GUEST GUEST Tennis Court 1
18 GUEST GUEST Tennis Court 2
19 Gerald Butters Tennis Court 1
20 Gerald Butters Tennis Court 2
21 Henrietta Rumney Tennis Court 2
22 Jack Smith Tennis Court 1
23 Jack Smith Tennis Court 2
24 Janice Joplette Tennis Court 1
25 Janice Joplette Tennis Court 2
26 Jemima Farrell Tennis Court 1
27 Jemima Farrell Tennis Court 2
28 Joan Coplin Tennis Court 1
29 John Hunt Tennis Court 1
30 John Hunt Tennis Court 2
31 Matthew Genting Tennis Court 1
32 Millicent Purview Tennis Court 2
33 Nancy Dare Tennis Court 1
34 Nancy Dare Tennis Court 2
35 Ponder Stibbons Tennis Court 1
36 Ponder Stibbons Tennis Court 2
37 Ramnaresh Sarwin Tennis Court 1
38 Ramnaresh Sarwin Tennis Court 2
39 Tim Boothe Tennis Court 1
40 Tim Boothe Tennis Court 2
41 Tim Rownam Tennis Court 1
42 Tim Rownam Tennis Court 2
43 Timothy Baker Tennis Court 1
44 Timothy Baker Tennis Court 2
45 Tracy Smith Tennis Court 1
46 Tracy Smith Tennis Court 2Code
dbGetQuery(con,"select mems.firstname || ' ' || mems.surname as member,
facs.name as facility,
case
when mems.memid = 0 then
bks.slots*facs.guestcost
else
bks.slots*facs.membercost
end as cost
from
members mems
inner join bookings bks
on mems.memid = bks.memid
inner join facilities facs
on bks.facid = facs.facid
where
bks.starttime >= '2012-09-14' and
bks.starttime < '2012-09-15' and (
(mems.memid = 0 and bks.slots*facs.guestcost > 30) or
(mems.memid != 0 and bks.slots*facs.membercost > 30)
)
order by cost desc; ") member facility cost
1 GUEST GUEST Massage Room 2 320.0
2 GUEST GUEST Tennis Court 2 198.0
3 GUEST GUEST Massage Room 1 160.0
4 GUEST GUEST Massage Room 1 160.0
5 GUEST GUEST Massage Room 1 160.0
6 Jemima Farrell Massage Room 1 140.0
7 GUEST GUEST Tennis Court 2 99.0
8 GUEST GUEST Tennis Court 1 90.0
9 GUEST GUEST Tennis Court 1 90.0
10 Ponder Stibbons Massage Room 1 70.0
11 Jemima Farrell Massage Room 1 70.0
12 Jack Smith Massage Room 1 70.0
13 Burton Tracy Massage Room 1 70.0
14 Matthew Genting Massage Room 1 70.0
15 Florence Bader Massage Room 2 70.0
16 GUEST GUEST Squash Court 70.0
17 Tim Boothe Tennis Court 2 39.6
18 David Jones Tennis Court 2 39.6
19 GUEST GUEST Squash Court 35.0
20 GUEST GUEST Squash Court 35.0Code
dbGetQuery(con,"SELECT DISTINCT
mems.firstname || ' ' || mems.surname AS member,
(
SELECT recs.firstname || ' ' || recs.surname
FROM members AS recs
WHERE mems.recommendedby = recs.memid
) AS recommender
FROM
members AS mems
ORDER BY
member;") member recommender
1 Anna Mackenzie Darren Smith
2 Anne Baker Ponder Stibbons
3 Burton Tracy <NA>
4 Charles Owen Darren Smith
5 Darren Smith <NA>
6 David Farrell <NA>
7 David Jones Janice Joplette
8 David Pinker Jemima Farrell
9 Douglas Jones David Jones
10 Erica Crumpet Tracy Smith
11 Florence Bader Ponder Stibbons
12 GUEST GUEST <NA>
13 Gerald Butters Darren Smith
14 Henrietta Rumney Matthew Genting
15 Henry Worthington-Smyth Tracy Smith
16 Hyacinth Tupperware <NA>
17 Jack Smith Darren Smith
18 Janice Joplette Darren Smith
19 Jemima Farrell <NA>
20 Joan Coplin Timothy Baker
21 John Hunt Millicent Purview
22 Matthew Genting Gerald Butters
23 Millicent Purview Tracy Smith
24 Nancy Dare Janice Joplette
25 Ponder Stibbons Burton Tracy
26 Ramnaresh Sarwin Florence Bader
27 Tim Boothe Tim Rownam
28 Tim Rownam <NA>
29 Timothy Baker Jemima Farrell
30 Tracy Smith <NA>Code
dbGetQuery(con,"select member, facility, cost from (
select
mems.firstname || ' ' || mems.surname as member,
facs.name as facility,
case
when mems.memid = 0 then
bks.slots*facs.guestcost
else
bks.slots*facs.membercost
end as cost
from
members mems
inner join bookings bks
on mems.memid = bks.memid
inner join facilities facs
on bks.facid = facs.facid
where
bks.starttime >= '2012-09-14' and
bks.starttime < '2012-09-15'
) as bookings
where cost > 30
order by cost desc; ") member facility cost
1 GUEST GUEST Massage Room 2 320.0
2 GUEST GUEST Tennis Court 2 198.0
3 GUEST GUEST Massage Room 1 160.0
4 GUEST GUEST Massage Room 1 160.0
5 GUEST GUEST Massage Room 1 160.0
6 Jemima Farrell Massage Room 1 140.0
7 GUEST GUEST Tennis Court 2 99.0
8 GUEST GUEST Tennis Court 1 90.0
9 GUEST GUEST Tennis Court 1 90.0
10 Ponder Stibbons Massage Room 1 70.0
11 Jemima Farrell Massage Room 1 70.0
12 Jack Smith Massage Room 1 70.0
13 Burton Tracy Massage Room 1 70.0
14 Matthew Genting Massage Room 1 70.0
15 Florence Bader Massage Room 2 70.0
16 GUEST GUEST Squash Court 70.0
17 Tim Boothe Tennis Court 2 39.6
18 David Jones Tennis Court 2 39.6
19 GUEST GUEST Squash Court 35.0
20 GUEST GUEST Squash Court 35.0Modifying data
Code
dbExecute(con,"INSERT INTO facilities
VALUES (9, 'Spa', 20, 30, 100000, 800);")[1] 1Code
dbExecute(con,"INSERT INTO facilities
VALUES (9, 'Spa', 20, 30, 100000, 800),(10, 'Squash Court 2', 3.5, 17.5, 5000, 80);")[1] 2Code
dbExecute(con,"
INSERT INTO
facilities
SELECT
MAX(facid) + 1 AS facid,
'Spa' AS name,
20 AS membercost,
30 AS guestcost,
100000 AS initialoutlay,
800 AS monthlymaintenance
FROM
facilities;")[1] 1Code
dbExecute(con,"
UPDATE facilities
SET initialoutlay = 10000
WHERE name = 'Tennis Court 2';")[1] 1Code
dbGetQuery(con,"
UPDATE
facilities
SET
membercost = 6,
guestcost = 30
WHERE
name LIKE 'Tennis Court %';")Warning: Don't need to call dbFetch() for statements, only for queriesdata frame with 0 columns and 0 rowsCode
dbExecute(con,"
UPDATE facilities facs
SET
membercost = (select membercost * 1.1 from facilities where facid = 0),
guestcost = (select guestcost * 1.1 from facilities where facid = 0)
WHERE facs.facid = 1; ")[1] 1dbGetQuery(con,” DELETE FROM bookings;“)
solo se muestra el codigo para no borrar la db y poder continuar el codigo al renderizarlo
dbExecute(con,” DELETE FROM members WHERE memid = 37;“)
solo se muestra el codigo para no borrar la db y poder continuar el codigo al renderizarlo
dbGetQuery(con,” DELETE FROM members WHERE memid NOT IN (SELECT memid FROM bookings);“) solo se muestra el codigo para no borrar la db y poder continuar el codigo al renderizarlo
Aggregation
Code
dbGetQuery(con,"
SELECT
count(*)
FROM facilities;") count
1 29Code
dbGetQuery(con,"
SELECT COUNT(*)
FROM facilities
WHERE guestcost >= 10;") count
1 26Code
dbGetQuery(con,"
SELECT
recommendedby,
COUNT(*)
FROM
members
WHERE
recommendedby IS NOT NULL
GROUP BY
recommendedby
ORDER BY
recommendedby;") recommendedby count
1 1 5
2 2 3
3 3 1
4 4 2
5 5 1
6 6 1
7 9 2
8 11 1
9 13 2
10 15 1
11 16 1
12 20 1
13 30 1Code
dbGetQuery(con,"
SELECT
facid,
SUM(slots) AS Total_Slots
FROM
bookings
GROUP BY
facid
ORDER BY
facid;") facid total_slots
1 0 1320
2 1 1278
3 2 1209
4 3 830
5 4 1404
6 5 228
7 6 1104
8 7 908
9 8 911Code
dbGetQuery(con,"
SELECT
facid,
SUM(slots) AS Total_Slots
FROM
bookings
WHERE
starttime BETWEEN '2012-09-01' AND '2012-10-01'
GROUP BY
facid
ORDER BY
Total_Slots;") facid total_slots
1 5 122
2 3 422
3 7 426
4 8 471
5 6 540
6 2 570
7 1 588
8 0 591
9 4 648Code
dbGetQuery(con,"
SELECT
facid,
EXTRACT(MONTH FROM starttime) AS month,
SUM(slots) AS Total_Slots
FROM
bookings
WHERE
starttime BETWEEN '2012-01-01' AND '2012-12-31'
GROUP BY
facid,
month
ORDER BY
facid,
month;") facid month total_slots
1 0 7 270
2 0 8 459
3 0 9 591
4 1 7 207
5 1 8 483
6 1 9 588
7 2 7 180
8 2 8 459
9 2 9 570
10 3 7 104
11 3 8 304
12 3 9 422
13 4 7 264
14 4 8 492
15 4 9 648
16 5 7 24
17 5 8 82
18 5 9 122
19 6 7 164
20 6 8 400
21 6 9 540
22 7 7 156
23 7 8 326
24 7 9 426
25 8 7 117
26 8 8 322
27 8 9 471Code
dbGetQuery(con,"
SELECT COUNT(DISTINCT memid)
FROM bookings;") count
1 30Code
dbGetQuery(con,"
SELECT
facid,
SUM(slots) AS Total_Slots
FROM
bookings
GROUP BY
facid
HAVING
SUM(slots) > 1000
ORDER BY
facid;") facid total_slots
1 0 1320
2 1 1278
3 2 1209
4 4 1404
5 6 1104Code
dbGetQuery(con,"
SELECT facs.name, SUM(slots * case
WHEN memid = 0 THEN facs.guestcost
ELSE facs.membercost
end) AS revenue
from bookings bks
INNER JOIN facilities facs
ON bks.facid = facs.facid
GROUP BY facs.name
ORDER BY revenue; ") name revenue
1 Table Tennis 180.0
2 Snooker Table 240.0
3 Pool Table 270.0
4 Badminton Court 1906.5
5 Squash Court 13468.0
6 Massage Room 2 15810.0
7 Tennis Court 1 16632.0
8 Tennis Court 2 18889.2
9 Massage Room 1 72540.0Code
dbGetQuery(con,"
WITH booking_costs AS (
SELECT
facs.name,
CASE bks.memid
WHEN 0 THEN bks.slots * facs.guestcost
ELSE bks.slots * facs.membercost
END AS cte_cost
FROM
bookings AS bks
INNER JOIN
facilities AS facs
ON bks.facid = facs.facid
)
SELECT
name,
SUM(cte_cost) AS revenue
FROM
booking_costs
GROUP BY
name
HAVING
SUM(cte_cost) < 1000
ORDER BY
revenue;") name revenue
1 Table Tennis 180
2 Snooker Table 240
3 Pool Table 270Code
dbGetQuery(con,"WITH SUM AS (SELECT facid, sum(slots) as Total_Slots
FROM bookings
GROUP BY facid
)
SELECT facid, Total_Slots
FROM SUM
WHERE Total_Slots = (SELECT max(Total_Slots) from sum);
") facid total_slots
1 4 1404Code
dbGetQuery(con,"
SELECT
facid,
EXTRACT(MONTH FROM starttime) AS month,
SUM(slots) AS slots
FROM
bookings
WHERE
starttime BETWEEN '2012-01-01' AND '2012-12-31'
GROUP BY
ROLLUP(facid, month)
ORDER BY
facid,
month;") facid month slots
1 0 7 270
2 0 8 459
3 0 9 591
4 0 NA 1320
5 1 7 207
6 1 8 483
7 1 9 588
8 1 NA 1278
9 2 7 180
10 2 8 459
11 2 9 570
12 2 NA 1209
13 3 7 104
14 3 8 304
15 3 9 422
16 3 NA 830
17 4 7 264
18 4 8 492
19 4 9 648
20 4 NA 1404
21 5 7 24
22 5 8 82
23 5 9 122
24 5 NA 228
25 6 7 164
26 6 8 400
27 6 9 540
28 6 NA 1104
29 7 7 156
30 7 8 326
31 7 9 426
32 7 NA 908
33 8 7 117
34 8 8 322
35 8 9 471
36 8 NA 910
37 NA NA 9191Code
dbGetQuery(con,"
SELECT fa.facid, fa.name,
TRIM(to_char(SUM(bk.slots)/2.0, '9999999999999999D99')) as Total_Hours
FROM bookings AS bk
INNER JOIN facilities AS fa
ON fa.facid = bk.facid
GROUP BY fa.facid, fa.name
ORDER BY fa.facid; ") facid name total_hours
1 0 Tennis Court 1 660.00
2 1 Tennis Court 2 639.00
3 2 Badminton Court 604.50
4 3 Table Tennis 415.00
5 4 Massage Room 1 702.00
6 5 Massage Room 2 114.00
7 6 Squash Court 552.00
8 7 Snooker Table 454.00
9 8 Pool Table 455.50Code
dbGetQuery(con,"
SELECT
me.surname,
me.firstname,
me.memid,
MIN(bk.starttime) AS starttime
FROM
members AS me
INNER JOIN
bookings AS bk
ON me.memid = bk.memid
WHERE
bk.starttime >= '2012-09-01'
GROUP BY
me.surname,
me.firstname,
me.memid
ORDER BY
me.memid;") surname firstname memid starttime
1 GUEST GUEST 0 2012-09-01 08:00:00
2 Smith Darren 1 2012-09-01 09:00:00
3 Smith Tracy 2 2012-09-01 11:30:00
4 Rownam Tim 3 2012-09-01 16:00:00
5 Joplette Janice 4 2012-09-01 15:00:00
6 Butters Gerald 5 2012-09-02 12:30:00
7 Tracy Burton 6 2012-09-01 15:00:00
8 Dare Nancy 7 2012-09-01 12:30:00
9 Boothe Tim 8 2012-09-01 08:30:00
10 Stibbons Ponder 9 2012-09-01 11:00:00
11 Owen Charles 10 2012-09-01 11:00:00
12 Jones David 11 2012-09-01 09:30:00
13 Baker Anne 12 2012-09-01 14:30:00
14 Farrell Jemima 13 2012-09-01 09:30:00
15 Smith Jack 14 2012-09-01 11:00:00
16 Bader Florence 15 2012-09-01 10:30:00
17 Baker Timothy 16 2012-09-01 15:00:00
18 Pinker David 17 2012-09-01 08:30:00
19 Genting Matthew 20 2012-09-01 18:00:00
20 Mackenzie Anna 21 2012-09-01 08:30:00
21 Coplin Joan 22 2012-09-02 11:30:00
22 Sarwin Ramnaresh 24 2012-09-04 11:00:00
23 Jones Douglas 26 2012-09-08 13:00:00
24 Rumney Henrietta 27 2012-09-16 13:30:00
25 Farrell David 28 2012-09-18 09:00:00
26 Worthington-Smyth Henry 29 2012-09-19 09:30:00
27 Purview Millicent 30 2012-09-19 11:30:00
28 Tupperware Hyacinth 33 2012-09-20 08:00:00
29 Hunt John 35 2012-09-23 14:00:00
30 Crumpet Erica 36 2012-09-27 11:30:00Code
dbGetQuery(con,"
SELECT
COUNT(*) OVER(),
firstname,
surname
FROM
members
ORDER BY
joindate;") count firstname surname
1 31 GUEST GUEST
2 31 Darren Smith
3 31 Tracy Smith
4 31 Tim Rownam
5 31 Janice Joplette
6 31 Gerald Butters
7 31 Burton Tracy
8 31 Nancy Dare
9 31 Tim Boothe
10 31 Ponder Stibbons
11 31 Charles Owen
12 31 David Jones
13 31 Anne Baker
14 31 Jemima Farrell
15 31 Jack Smith
16 31 Florence Bader
17 31 Timothy Baker
18 31 David Pinker
19 31 Matthew Genting
20 31 Anna Mackenzie
21 31 Joan Coplin
22 31 Ramnaresh Sarwin
23 31 Douglas Jones
24 31 Henrietta Rumney
25 31 David Farrell
26 31 Henry Worthington-Smyth
27 31 Millicent Purview
28 31 Hyacinth Tupperware
29 31 John Hunt
30 31 Erica Crumpet
31 31 Darren SmithCode
dbGetQuery(con,"
SELECT row_number()
OVER(ORDER BY joindate), firstname, surname
FROM members
ORDER BY joindate ") row_number firstname surname
1 1 GUEST GUEST
2 2 Darren Smith
3 3 Tracy Smith
4 4 Tim Rownam
5 5 Janice Joplette
6 6 Gerald Butters
7 7 Burton Tracy
8 8 Nancy Dare
9 9 Tim Boothe
10 10 Ponder Stibbons
11 11 Charles Owen
12 12 David Jones
13 13 Anne Baker
14 14 Jemima Farrell
15 15 Jack Smith
16 16 Florence Bader
17 17 Timothy Baker
18 18 David Pinker
19 19 Matthew Genting
20 20 Anna Mackenzie
21 21 Joan Coplin
22 22 Ramnaresh Sarwin
23 23 Douglas Jones
24 24 Henrietta Rumney
25 25 David Farrell
26 26 Henry Worthington-Smyth
27 27 Millicent Purview
28 28 Hyacinth Tupperware
29 29 John Hunt
30 30 Erica Crumpet
31 31 Darren SmithCode
dbGetQuery(con,"
SELECT facid, Total_Sums
FROM(
SELECT facid,SUM(slots) Total_Sums, rank() OVER (ORDER BY SUM(slots) DESC) rank
FROM bookings
GROUP BY facid
)
AS ranked
WHERE rank=1
") facid total_sums
1 4 1404Code
dbGetQuery(con,"select firstname, surname,
((sum(bks.slots)+10)/20)*10 as hours,
rank() over (order by ((sum(bks.slots)+10)/20)*10 desc) as rank
from bookings bks
inner join members mems
on bks.memid = mems.memid
group by mems.memid, firstname,surname
order by rank, surname, firstname; ") firstname surname hours rank
1 GUEST GUEST 1200 1
2 Darren Smith 340 2
3 Tim Rownam 330 3
4 Tim Boothe 220 4
5 Tracy Smith 220 4
6 Gerald Butters 210 6
7 Burton Tracy 180 7
8 Charles Owen 170 8
9 Janice Joplette 160 9
10 Anne Baker 150 10
11 Timothy Baker 150 10
12 David Jones 150 10
13 Nancy Dare 130 13
14 Florence Bader 120 14
15 Anna Mackenzie 120 14
16 Ponder Stibbons 120 14
17 Jack Smith 110 17
18 Jemima Farrell 90 18
19 David Pinker 80 19
20 Ramnaresh Sarwin 80 19
21 Matthew Genting 70 21
22 Joan Coplin 50 22
23 David Farrell 30 23
24 Henry Worthington-Smyth 30 23
25 John Hunt 20 25
26 Douglas Jones 20 25
27 Millicent Purview 20 25
28 Henrietta Rumney 20 25
29 Erica Crumpet 10 29
30 Hyacinth Tupperware 10 29Code
dbGetQuery(con,"
select name, rank from (
select facs.name as name, rank() over (order by sum(case
when memid = 0 then slots * facs.guestcost
else slots * membercost
end) desc) as rank
from bookings bks
inner join facilities facs
on bks.facid = facs.facid
group by facs.name
) as subq
where rank <= 3
order by rank; ") name rank
1 Massage Room 1 1
2 Tennis Court 2 2
3 Tennis Court 1 3Code
dbGetQuery(con,"
select name, case when class=1 then 'high'
when class=2 then 'average'
else 'low'
end revenue
from (
select facs.name as name, ntile(3) over (order by sum(case
when memid = 0 then slots * facs.guestcost
else slots * membercost
end) desc) as class
from bookings bks
inner join facilities facs
on bks.facid = facs.facid
group by facs.name
) as subq
order by class, name;") name revenue
1 Massage Room 1 high
2 Tennis Court 1 high
3 Tennis Court 2 high
4 Badminton Court average
5 Massage Room 2 average
6 Squash Court average
7 Pool Table low
8 Snooker Table low
9 Table Tennis lowCode
dbGetQuery(con,"select facs.name as name,
facs.initialoutlay/((sum(case
when memid = 0 then slots * facs.guestcost
else slots * membercost
end)/3) - facs.monthlymaintenance) as months
from bookings bks
inner join facilities facs
on bks.facid = facs.facid
group by facs.facid,facs.name,facs.monthlymaintenance,facs.initialoutlay
order by name; ") name months
1 Badminton Court 6.8317677
2 Massage Room 1 0.1888574
3 Massage Room 2 1.7621145
4 Pool Table 5.3333333
5 Snooker Table 6.9230769
6 Squash Court 1.1339583
7 Table Tennis 6.4000000
8 Tennis Court 1 1.8712575
9 Tennis Court 2 1.6403123Code
dbGetQuery(con,"select dategen.date,
(
-- correlated subquery that, for each day fed into it,
-- finds the average revenue for the last 15 days
select sum(case
when memid = 0 then slots * facs.guestcost
else slots * membercost
end) as rev
from bookings bks
inner join facilities facs
on bks.facid = facs.facid
where bks.starttime > dategen.date - interval '14 days'
and bks.starttime < dategen.date + interval '1 day'
)/15 as revenue
from
(
-- generates a list of days in august
select cast(generate_series(timestamp '2012-08-01',
'2012-08-31','1 day') as date) as date
) as dategen
order by dategen.date; ") date revenue
1 2012-08-01 1191.793
2 2012-08-02 1219.320
3 2012-08-03 1229.460
4 2012-08-04 1245.207
5 2012-08-05 1228.933
6 2012-08-06 1254.360
7 2012-08-07 1249.187
8 2012-08-08 1238.640
9 2012-08-09 1218.507
10 2012-08-10 1240.673
11 2012-08-11 1245.593
12 2012-08-12 1266.427
13 2012-08-13 1290.640
14 2012-08-14 1324.867
15 2012-08-15 1352.180
16 2012-08-16 1364.113
17 2012-08-17 1409.947
18 2012-08-18 1458.933
19 2012-08-19 1490.587
20 2012-08-20 1512.627
21 2012-08-21 1534.013
22 2012-08-22 1629.380
23 2012-08-23 1656.540
24 2012-08-24 1683.333
25 2012-08-25 1710.393
26 2012-08-26 1723.480
27 2012-08-27 1751.673
28 2012-08-28 1775.800
29 2012-08-29 1771.993
30 2012-08-30 1746.053
31 2012-08-31 1791.840Working with Timestamps
Code
dbGetQuery(con,"
SELECT TIMESTAMP '2012-08-31 01:00:00';") timestamp
1 2012-08-31 01:00:00Code
dbGetQuery(con,"SELECT
(
TIMESTAMP '2012-08-31 01:00:00' - TIMESTAMP '2012-07-30 01:00:00'
) AS interval;") interval
1 32 daysCode
dbGetQuery(con,"
SELECT
generate_series(
TIMESTAMP '2012-10-01',
TIMESTAMP '2012-10-31',
INTERVAL '1 day'
) AS ts;") ts
1 2012-10-01
2 2012-10-02
3 2012-10-03
4 2012-10-04
5 2012-10-05
6 2012-10-06
7 2012-10-07
8 2012-10-08
9 2012-10-09
10 2012-10-10
11 2012-10-11
12 2012-10-12
13 2012-10-13
14 2012-10-14
15 2012-10-15
16 2012-10-16
17 2012-10-17
18 2012-10-18
19 2012-10-19
20 2012-10-20
21 2012-10-21
22 2012-10-22
23 2012-10-23
24 2012-10-24
25 2012-10-25
26 2012-10-26
27 2012-10-27
28 2012-10-28
29 2012-10-29
30 2012-10-30
31 2012-10-31Code
dbGetQuery(con,"SELECT EXTRACT(DAY FROM TIMESTAMP '2012-08-31');") extract
1 31Code
dbGetQuery(con,"SELECT ROUND(
EXTRACT(
EPOCH FROM (
TIMESTAMP '2012-09-02 00:00:00' - TIMESTAMP '2012-08-31 01:00:00'
)
)
) AS date_part;") date_part
1 169200Code
dbGetQuery(con,"SELECT
EXTRACT(MONTH FROM gen) AS month,
((gen + INTERVAL '1 month') - gen) AS length
FROM
generate_series(
DATE '2012-01-01',
DATE '2012-12-31',
INTERVAL '1 month'
) AS gen;") month length
1 1 31 days
2 2 29 days
3 3 31 days
4 4 30 days
5 5 31 days
6 6 30 days
7 7 31 days
8 8 31 days
9 9 30 days
10 10 31 days
11 11 30 days
12 12 31 daysCode
dbGetQuery(con,"select (date_trunc('month',ts.testts) + interval '1 month')
- date_trunc('day', ts.testts) as remaining
from (select timestamp '2012-02-11 01:00:00' as testts) ts ") remaining
1 19 daysCode
dbGetQuery(con,"select starttime, starttime + slots*(interval '30 minutes') endtime
from bookings
order by endtime desc, starttime desc
limit 10 ") starttime endtime
1 2013-01-01 15:30:00 2013-01-01 16:00:00
2 2012-09-30 19:30:00 2012-09-30 20:30:00
3 2012-09-30 19:00:00 2012-09-30 20:30:00
4 2012-09-30 19:30:00 2012-09-30 20:00:00
5 2012-09-30 19:00:00 2012-09-30 20:00:00
6 2012-09-30 19:00:00 2012-09-30 20:00:00
7 2012-09-30 18:30:00 2012-09-30 20:00:00
8 2012-09-30 18:30:00 2012-09-30 20:00:00
9 2012-09-30 19:00:00 2012-09-30 19:30:00
10 2012-09-30 18:30:00 2012-09-30 19:30:00Code
dbGetQuery(con,"select date_trunc('month', starttime) as month, count(*)
from bookings
group by month
order by month ") month count
1 2012-07-01 658
2 2012-08-01 1472
3 2012-09-01 1913
4 2013-01-01 1Code
dbGetQuery(con,"select name, month,
round((100*slots)/
cast(
25*(cast((month + interval '1 month') as date)
- cast (month as date)) as numeric),1) as utilisation
from (
select facs.name as name, date_trunc('month', starttime) as month, sum(slots) as slots
from bookings bks
inner join facilities facs
on bks.facid = facs.facid
group by facs.facid, month,facs.name
) as inn
order by name, month ") name month utilisation
1 Badminton Court 2012-07-01 23.2
2 Badminton Court 2012-08-01 59.2
3 Badminton Court 2012-09-01 76.0
4 Massage Room 1 2012-07-01 34.1
5 Massage Room 1 2012-08-01 63.5
6 Massage Room 1 2012-09-01 86.4
7 Massage Room 2 2012-07-01 3.1
8 Massage Room 2 2012-08-01 10.6
9 Massage Room 2 2012-09-01 16.3
10 Pool Table 2012-07-01 15.1
11 Pool Table 2012-08-01 41.5
12 Pool Table 2012-09-01 62.8
13 Pool Table 2013-01-01 0.1
14 Snooker Table 2012-07-01 20.1
15 Snooker Table 2012-08-01 42.1
16 Snooker Table 2012-09-01 56.8
17 Squash Court 2012-07-01 21.2
18 Squash Court 2012-08-01 51.6
19 Squash Court 2012-09-01 72.0
20 Table Tennis 2012-07-01 13.4
21 Table Tennis 2012-08-01 39.2
22 Table Tennis 2012-09-01 56.3
23 Tennis Court 1 2012-07-01 34.8
24 Tennis Court 1 2012-08-01 59.2
25 Tennis Court 1 2012-09-01 78.8
26 Tennis Court 2 2012-07-01 26.7
27 Tennis Court 2 2012-08-01 62.3
28 Tennis Court 2 2012-09-01 78.4String Operations
Code
dbGetQuery(con,"SELECT (surname || ', ' || firstname) AS name
FROM members;") name
1 GUEST, GUEST
2 Smith, Darren
3 Smith, Tracy
4 Rownam, Tim
5 Joplette, Janice
6 Butters, Gerald
7 Tracy, Burton
8 Dare, Nancy
9 Boothe, Tim
10 Stibbons, Ponder
11 Owen, Charles
12 Jones, David
13 Baker, Anne
14 Farrell, Jemima
15 Smith, Jack
16 Bader, Florence
17 Baker, Timothy
18 Pinker, David
19 Genting, Matthew
20 Mackenzie, Anna
21 Coplin, Joan
22 Sarwin, Ramnaresh
23 Jones, Douglas
24 Rumney, Henrietta
25 Farrell, David
26 Worthington-Smyth, Henry
27 Purview, Millicent
28 Tupperware, Hyacinth
29 Hunt, John
30 Crumpet, Erica
31 Smith, DarrenCode
dbGetQuery(con,"SELECT *
FROM facilities
WHERE name LIKE 'Tennis%';") facid name membercost guestcost initialoutlay monthlymaintenance
1 0 Tennis Court 1 6.0 30 10000 200
2 1 Tennis Court 2 6.6 33 10000 200Code
dbGetQuery(con,"SELECT *
FROM facilities
WHERE name ILIKE 'tennis%';") facid name membercost guestcost initialoutlay monthlymaintenance
1 0 Tennis Court 1 6.0 30 10000 200
2 1 Tennis Court 2 6.6 33 10000 200Code
dbGetQuery(con,"SELECT
memid,
telephone
FROM
members
WHERE
telephone ~ '[()]';") memid telephone
1 0 (000) 000-0000
2 3 (844) 693-0723
3 4 (833) 942-4710
4 5 (844) 078-4130
5 6 (822) 354-9973
6 7 (833) 776-4001
7 8 (811) 433-2547
8 9 (833) 160-3900
9 10 (855) 542-5251
10 11 (844) 536-8036
11 13 (855) 016-0163
12 14 (822) 163-3254
13 15 (833) 499-3527
14 20 (811) 972-1377
15 21 (822) 661-2898
16 22 (822) 499-2232
17 24 (822) 413-1470
18 27 (822) 989-8876
19 28 (855) 755-9876
20 29 (855) 894-3758
21 30 (855) 941-9786
22 33 (822) 665-5327
23 35 (899) 720-6978
24 36 (811) 732-4816
25 37 (822) 577-3541Code
dbGetQuery(con,"
SELECT lpad(cast(zipcode as char(5)),5,'0') zip
FROM members
ORDER BY zip;") zip
1 00000
2 00234
3 00234
4 04321
5 04321
6 10383
7 11986
8 23423
9 28563
10 33862
11 34232
12 43532
13 43533
14 45678
15 52365
16 54333
17 56754
18 57392
19 58393
20 64577
21 65332
22 65464
23 66796
24 68666
25 69302
26 75655
27 78533
28 80743
29 84923
30 87630
31 97676Code
dbGetQuery(con,"
SELECT
substr (members.surname,1,1) AS letter,
COUNT(*) AS count
FROM
members
GROUP BY
letter
ORDER BY
letter;") letter count
1 B 5
2 C 2
3 D 1
4 F 2
5 G 2
6 H 1
7 J 3
8 M 1
9 O 1
10 P 2
11 R 2
12 S 6
13 T 2
14 W 1Code
dbGetQuery(con,"
SELECT
memid,
TRANSLATE(telephone, '-() ', '') AS telephone
FROM
members
ORDER BY
memid;") memid telephone
1 0 0000000000
2 1 5555555555
3 2 5555555555
4 3 8446930723
5 4 8339424710
6 5 8440784130
7 6 8223549973
8 7 8337764001
9 8 8114332547
10 9 8331603900
11 10 8555425251
12 11 8445368036
13 12 8440765141
14 13 8550160163
15 14 8221633254
16 15 8334993527
17 16 8339410824
18 17 8114096734
19 20 8119721377
20 21 8226612898
21 22 8224992232
22 24 8224131470
23 26 8445368036
24 27 8229898876
25 28 8557559876
26 29 8558943758
27 30 8559419786
28 33 8226655327
29 35 8997206978
30 36 8117324816
31 37 8225773541Recursive Queries
Code
dbGetQuery(con,"with recursive recommenders(recommender) as (
select recommendedby from members where memid = 27
union all
select mems.recommendedby
from recommenders recs
inner join members mems
on mems.memid = recs.recommender
)
select recs.recommender, mems.firstname, mems.surname
from recommenders recs
inner join members mems
on recs.recommender = mems.memid
order by memid desc ") recommender firstname surname
1 20 Matthew Genting
2 5 Gerald Butters
3 1 Darren SmithCode
dbGetQuery(con,"with recursive recommendeds(memid) as (
select memid from members where recommendedby = 1
union all
select mems.memid
from recommendeds recs
inner join members mems
on mems.recommendedby = recs.memid
)
select recs.memid, mems.firstname, mems.surname
from recommendeds recs
inner join members mems
on recs.memid = mems.memid
order by memid") memid firstname surname
1 4 Janice Joplette
2 5 Gerald Butters
3 7 Nancy Dare
4 10 Charles Owen
5 11 David Jones
6 14 Jack Smith
7 20 Matthew Genting
8 21 Anna Mackenzie
9 26 Douglas Jones
10 27 Henrietta RumneyCode
dbGetQuery(con,"with recursive recommenders(recommender, member) as (
select recommendedby, memid
from members
union all
select mems.recommendedby, recs.member
from recommenders recs
inner join members mems
on mems.memid = recs.recommender
)
select recs.member member, recs.recommender, mems.firstname, mems.surname
from recommenders recs
inner join members mems
on recs.recommender = mems.memid
where recs.member = 22 or recs.member = 12
order by recs.member asc, recs.recommender desc ") member recommender firstname surname
1 12 9 Ponder Stibbons
2 12 6 Burton Tracy
3 22 16 Timothy Baker
4 22 13 Jemima Farrell