TareaBD
Kadir Ortega - Elianna Gomez
2024-04-08
TAREA PostgreSQL
Crear Tabla
Creación de tablas:
console.neon.tech
A continuación código R para crear tabla en el servidor de base de datos PostgreSQL NEON:
library(DBI)
library(RPostgreSQL)
con <- dbConnect(RPostgres::Postgres(),
dbname = "myname_db",
host = "ep-shrill-bar-a565wxee.us-east-2.aws.neon.tech",
port = 5432,
user = "myname_db_owner",
password = "vh7Dtorgc0nj")
CreaTabla <- "
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, CreaTabla)
dbDisconnect(con)Insertar Datos:
Código R:
library(DBI)
library(RPostgreSQL)
con <- dbConnect(RPostgres::Postgres(),
dbname = "myname_db",
host = "ep-shrill-bar-a565wxee.us-east-2.aws.neon.tech",
port = 5432,
user = "myname_db_owner",
password = "vh7Dtorgc0nj")
InsertarData <- "
INSERT INTO cd.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, InsertarData)
dbDisconnect(con)Question 1
How can you retrieve all the information from the cd.facilities table?
ConsultaTabla <- "SELECT * FROM cd.facilities"
Question 1
Question 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?
ConsultaTabla <- "select name, membercost from cd.facilities; "
Question 2
Question 3
How can you produce a list of facilities that charge a fee to members?
ConsultaTabla <- "select * from cd.facilities where membercost > 0;"
Question 3
Question 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?
ConsultaTabla <- "
select facid, name, membercost, monthlymaintenance
from cd.facilities
where
membercost > 0 and
(membercost < monthlymaintenance/50.0);
"
Question 4
Question 5
How can you produce a list of all facilities with the word ‘Tennis’ in their name?
ConsultaTabla <- "
select *
from cd.facilities
where
name like '%Tennis%';
"
Question 5
Question 6
How can you retrieve the details of facilities with ID 1 and 5? Try to do it without using the OR operator.
ConsultaTabla <- "
select *
from cd.facilities
where
facid in (1,5);
"
Question 6
Question 7
How can you 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.
ConsultaTabla <- "
select name,
case when (monthlymaintenance > 100) then
'expensive'
else
'cheap'
end as cost
from cd.facilities;
"
Question 7
Question 8
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.
ConsultaTabla <- "
select memid, surname, firstname, joindate
from cd.members
where joindate >= '2012-09-01';
"
Question 8
Question 9
You, for some reason, want a combined list of all surnames and all facility names. Yes, this is a contrived example :-). Produce that list!
ConsultaTabla <- "
select surname
from cd.members
union
select name
from cd.facilities;
"
Question 9
Question 10
You’d like to get the signup date of your last member. How can you retrieve this information?
ConsultaTabla <- "
select max(joindate) as latest
from cd.members;
"
Question 10
Joins and Subqueries
Question 1
How can you produce a list of the start times for bookings by members named ‘David Farrell’?
ConsultaTabla <- "
select bks.starttime as start, facs.name as name
from
cd.facilities facs
inner join cd.bookings bks
on facs.facid = bks.facid
where
facs.name in ('Tennis Court 2','Tennis Court 1') and
bks.starttime >= '2012-09-21' and
bks.starttime < '2012-09-22'
order by bks.starttime;
"
Question 1
Question 2
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.
ConsultaTabla <- "
select bks.starttime as start, facs.name as name
from
cd.facilities facs
inner join cd.bookings bks
on facs.facid = bks.facid
where
facs.name in ('Tennis Court 2','Tennis Court 1') and
bks.starttime >= '2012-09-21' and
bks.starttime < '2012-09-22'
order by bks.starttime;
"
Question 2
Question 3
How can you output a list of all members who have recommended another member? Ensure that there are no duplicates in the list, and that results are ordered by (surname, firstname).
ConsultaTabla <- "
select distinct recs.firstname as firstname, recs.surname as surname
from
cd.members mems
inner join cd.members recs
on recs.memid = mems.recommendedby
order by surname, firstname;
"
Question 3
Modifying data
Question 1
Let’s try adding the spa to the facilities table again. This time, though, we want to automatically generate the value for the next facid, rather than specifying it as a constant. Use the following values for everything else:
Name: ‘Spa’, membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800.
InsertarData <- "
insert into cd.facilities
(facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
select (select max(facid) from cd.facilities)+1, 'Spa', 20, 30, 100000, 800;
"
Question 1
Question 2
We want to increase the price of the tennis courts for both members and guests. Update the costs to be 6 for members, and 30 for guests.
InsertarData <- "
update cd.facilities
set
membercost = 6,
guestcost = 30
where facid in (0,1);
"
Question 2
Aggregation
Question 1
For our first foray into aggregates, we’re going to stick to something simple. We want to know how many facilities exist - simply produce a total count.
ConsultaTabla <- "
select count(*) from cd.facilities;
"
Question 1
Question 2
Produce a count of the number of recommendations each member has made. Order by member ID.
ConsultaTabla <- "
select recommendedby, count(*)
from cd.members
where recommendedby is not null
group by recommendedby
order by recommendedby;
"
Question 2
Question 3
Produce a list of the total number of slots booked per facility. For now, just produce an output table consisting of facility id and slots, sorted by facility id.
ConsultaTabla <- "
select facid, sum(slots) as \"Total Slots\"
from cd.bookings
group by facid;
"
Question 3
Question 4
Produce a list of members (including guests), along with the number of hours they’ve booked in facilities, rounded to the nearest ten hours. Rank them by this rounded figure, producing output of first name, surname, rounded hours, rank. Sort by rank, surname, and first name.
ConsultaTabla <- "
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 cd.bookings bks
inner join cd.members mems
on bks.memid = mems.memid
group by mems.memid
order by rank, surname, firstname;
"
Question 4
Question 5
Based on the 3 complete months of data so far, calculate the amount of time each facility will take to repay its cost of ownership. Remember to take into account ongoing monthly maintenance. Output facility name and payback time in months, order by facility name. Don’t worry about differences in month lengths, we’re only looking for a rough value here!
ConsultaTabla <- "
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 cd.bookings bks
inner join cd.facilities facs
on bks.facid = facs.facid
group by facs.facid
order by name;
"
Question 5
Working with Timestamps
Question 1
Produce a timestamp for 1 a.m. on the 31st of August 2012.
ConsultaTabla <- "
select timestamp '2012-08-31 01:00:00';
"
Question 1
Question 2
Find the result of subtracting the timestamp ‘2012-07-30 01:00:00’ from the timestamp ‘2012-08-31 01:00:00’
ConsultaTabla <- "
select timestamp '2012-08-31 01:00:00' - timestamp '2012-07-30 01:00:00' as interval;
"
Question 2
Question 3
For each month of the year in 2012, output the number of days in that month. Format the output as an integer column containing the month of the year, and a second column containing an interval data type.
ConsultaTabla <- "
select extract(month from cal.month) as month,
(cal.month + interval '1 month') - cal.month as length
from
(
select generate_series(timestamp '2012-01-01', timestamp '2012-12-01', interval '1 month') as month
) cal
order by month;
"
Question 3
Question 4
Return a list of the start and end time of the last 10 bookings (ordered by the time at which they end, followed by the time at which they start) in the system.
ConsultaTabla <- "
select starttime, starttime + slots*(interval '30 minutes') endtime
from cd.bookings
order by endtime desc, starttime desc
limit 10
" 
Question 4
Question 5
Work out the utilisation percentage for each facility by month, sorted by name and month, rounded to 1 decimal place. Opening time is 8am, closing time is 8.30pm. You can treat every month as a full month, regardless of if there were some dates the club was not open.
ConsultaTabla <- "
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 cd.bookings bks
inner join cd.facilities facs
on bks.facid = facs.facid
group by facs.facid, month
) as inn
order by name, month
"
Question 5
String Operations
Question 1
Output the names of all members, formatted as ‘Surname, Firstname’
ConsultaTabla <- "
select surname || ', ' || firstname as name from cd.members
"
Question 1
Question 2
Find all facilities whose name begins with ‘Tennis’. Retrieve all columns.
ConsultaTabla <- "
select * from cd.facilities where name like 'Tennis%';
"
Question 2
Question 3
You’ve noticed that the club’s member table has telephone numbers with very inconsistent formatting. You’d like to find all the telephone numbers that contain parentheses, returning the member ID and telephone number sorted by member ID.
ConsultaTabla <- "
select memid, telephone from cd.members where telephone ~ '[()]';
"
Question 3
Question 4
You’d like to produce a count of how many members you have whose surname starts with each letter of the alphabet. Sort by the letter, and don’t worry about printing out a letter if the count is 0.
ConsultaTabla <- "
select substr (mems.surname,1,1) as letter, count(*) as count
from cd.members mems
group by letter
order by letter
"
Question 4
Recursive Queries
Question 1
Find the upward recommendation chain for member ID 27: that is, the member who recommended them, and the member who recommended that member, and so on. Return member ID, first name, and surname. Order by descending member id.
ConsultaTabla <- "
with recursive recommenders(recommender) as (
select recommendedby from cd.members where memid = 27
union all
select mems.recommendedby
from recommenders recs
inner join cd.members mems
on mems.memid = recs.recommender
)
select recs.recommender, mems.firstname, mems.surname
from recommenders recs
inner join cd.members mems
on recs.recommender = mems.memid
order by memid desc
"
Question 1
Question 2
Find the downward recommendation chain for member ID 1: that is, the members they recommended, the members those members recommended, and so on. Return member ID and name, and order by ascending member id.
ConsultaTabla <- "
with recursive recommendeds(memid) as (
select memid from cd.members where recommendedby = 1
union all
select mems.memid
from recommendeds recs
inner join cd.members mems
on mems.recommendedby = recs.memid
)
select recs.memid, mems.firstname, mems.surname
from recommendeds recs
inner join cd.members mems
on recs.memid = mems.memid
order by memid
"
Question 2
Question 3
Produce a CTE that can return the upward recommendation chain for any member. You should be able to select recommender from recommenders where member=x. Demonstrate it by getting the chains for members 12 and 22. Results table should have member and recommender, ordered by member ascending, recommender descending.
ConsultaTabla <- "
with recursive recommenders(recommender, member) as (
select recommendedby, memid
from cd.members
union all
select mems.recommendedby, recs.member
from recommenders recs
inner join cd.members mems
on mems.memid = recs.recommender
)
select recs.member member, recs.recommender, mems.firstname, mems.surname
from recommenders recs
inner join cd.members mems
on recs.recommender = mems.memid
where recs.member = 22 or recs.member = 12
order by recs.member asc, recs.recommender desc
"
Question 3