Tarea SQL en R
Jose_Montalvo
4/8/2024
true
Creacion de tablas e ingreso de datos en SAP Hana studio en ambiente QB1 dentro de mi usuario A843931
Insertando evidencia tabla Facilities
Insertando evidencia tabla Members
Insertando evidencia tabla Booking
library(TSstudio)## Warning: package 'TSstudio' was built under R version 4.2.3library(RODBC)
library(DBI)
library(DBI)
con <- dbConnect(odbc::odbc(), "HANA_QB1", timeout = 10)
Q1 <- dbGetQuery(con, "select * from A843931.FACILITIES")
Q2 <- dbGetQuery(con, "select name, membercost from A843931.FACILITIES")
Q3 <- dbGetQuery(con, "select * from A843931.FACILITIES
where membercost > 0")
Q4 <- dbGetQuery(con, "select facid, name, membercost, monthlymaintenance
from A843931.FACILITIES
where membercost > 0 and
(membercost < monthlymaintenance/50.0)")
Q5 <- dbGetQuery(con, "select * from A843931.FACILITIES
where
name like '%Tennis%'")
Q6 <- dbGetQuery(con, "select * from A843931.FACILITIES
where
facid in (1,5)")
Q7 <- dbGetQuery(con, "select name,
case when (monthlymaintenance > 100) then
'expensive'
else
'cheap'
end as cost from A843931.FACILITIES")
Q8 <- dbGetQuery(con, "select memid, surname, firstname, joindate from A843931.MEMBERS
where joindate >= '2012-09-01'")
Q9 <- dbGetQuery(con, "select distinct surname from A843931.MEMBERS
order by surname
limit 10")
Q10 <- dbGetQuery(con, "select surname
from A843931.MEMBERS
union
select name
from A843931.FACILITIES")
Q11 <- dbGetQuery(con, "select max(joindate) as latest from A843931.MEMBERS")
Q12 <- dbGetQuery(con, "select firstname, surname, joindate
from A843931.MEMBERS
where joindate =
(select max(joindate)
from A843931.MEMBERS)")Resultados de querys basic
Cada Q# es un ejercicio del programa
## FACID NAME MEMBERCOST GUESTCOST INITIALOUTLAY MONTHLYMAINTENANCE
## 1 2 Badminton Court 0.0 15.5 10000 50
## 2 3 Table Tennis 0.0 5.0 10000 10
## 3 4 Massage Room 1 35.0 80.0 10000 3000
## 4 5 Massage Room 2 35.0 80.0 10000 3000
## 5 6 Squash Court 3.5 17.5 10000 80
## 6 7 Snooker Table 0.0 5.0 10000 15## 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## FACID NAME MEMBERCOST GUESTCOST INITIALOUTLAY MONTHLYMAINTENANCE
## 1 4 Massage Room 1 35.0 80.0 10000 3000
## 2 5 Massage Room 2 35.0 80.0 10000 3000
## 3 6 Squash Court 3.5 17.5 10000 80
## 4 9 Spa 20.0 30.0 10000 800
## 5 10 Squash Court 2 3.5 17.5 10000 80
## 6 11 Spa 20.0 30.0 10000 800## FACID NAME MEMBERCOST MONTHLYMAINTENANCE
## 1 4 Massage Room 1 35 3000
## 2 5 Massage Room 2 35 3000## FACID NAME MEMBERCOST GUESTCOST INITIALOUTLAY MONTHLYMAINTENANCE
## 1 3 Table Tennis 0.0 5 10000 10
## 2 0 Tennis Court 1 6.0 30 10000 200
## 3 1 Tennis Court 2 6.6 33 10000 200## FACID NAME MEMBERCOST GUESTCOST INITIALOUTLAY MONTHLYMAINTENANCE
## 1 5 Massage Room 2 35.0 80 10000 3000
## 2 1 Tennis Court 2 6.6 33 10000 200## 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## 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## SURNAME
## 1 Bader
## 2 Baker
## 3 Boothe
## 4 Butters
## 5 Coplin
## 6 Crumpet## SURNAME
## 1 Worthington-Smyth
## 2 Genting
## 3 Tracy
## 4 Sarwin
## 5 GUEST
## 6 Massage Room 1## LATEST
## 1 2012-09-22 08:36:38## FIRSTNAME SURNAME JOINDATE
## 1 Erica Crumpet 2012-09-22 08:36:38ejercicios de Joins and Subqueries
Q13 <- dbGetQuery(con, "SELECT bks.starttime
FROM
A843931.BOOKINGS as bks
INNER JOIN A843931.MEMBERS as mems ON mems.memid = bks.memid
WHERE
mems.firstname = 'David'
AND mems.surname = 'Farrell'")
Q14 <- dbGetQuery(con, "SELECT bks.starttime AS start_time, facs.name AS name
FROM
A843931.FACILITIES as facs
INNER JOIN A843931.BOOKINGS as 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")
Q15 <- dbGetQuery(con, "SELECT DISTINCT recs.firstname AS firstname,
recs.surname AS surname
FROM
A843931.MEMBERS mems
INNER JOIN A843931.MEMBERS recs ON recs.memid = mems.recommendedby
ORDER BY
surname,
firstname")
Q16 <- dbGetQuery(con, "SELECT mems.firstname AS memfname, mems.surname AS memsname,
recs.firstname AS recfname,
recs.surname AS recsname
FROM
A843931.MEMBERS mems
LEFT OUTER JOIN A843931.MEMBERS recs ON recs.memid = mems.recommendedby
ORDER BY
memsname,
memfname")
Q17 <- dbGetQuery(con, "SELECT DISTINCT
mems.firstname || ' ' || mems.surname AS member,
facs.name AS facility
FROM
A843931.MEMBERS mems
INNER JOIN A843931.BOOKINGS bks ON mems.memid = bks.memid
INNER JOIN A843931.FACILITIES facs ON bks.facid = facs.facid
WHERE
facs.name IN ('Tennis Court 2','Tennis Court 1')
ORDER BY
member,
facility")
Q18 <- 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
A843931.MEMBERS mems
INNER JOIN A843931.BOOKINGS bks ON mems.memid = bks.memid
INNER JOIN A843931.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")
Q19 <- dbGetQuery(con, "SELECT DISTINCT mems.firstname || ' ' || mems.surname as member,
(SELECT recs.firstname || ' ' || recs.surname as recommender
FROM A843931.MEMBERS recs
WHERE recs.memid = mems.recommendedby
)
FROM
A843931.MEMBERS mems
ORDER BY
member")
Q20 <- 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
A843931.MEMBERS mems
INNER JOIN A843931.BOOKINGS bks ON mems.memid = bks.memid
INNER JOIN A843931.FACILITIES facs ON bks.facid = facs.facid
WHERE
bks.starttime >= '2012-09-14' AND
bks.starttime < '2012-09-15' AND
(CASE
WHEN mems.memid = 0 THEN bks.slots * facs.guestcost
ELSE bks.slots * facs.membercost
END) > 30
ORDER BY
cost DESC")head(Q13)## 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:00head(Q14)## START_TIME NAME
## 1 2012-09-21 08:00:00 Tennis Court 2
## 2 2012-09-21 08:00:00 Tennis Court 1
## 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 1head(Q15)## FIRSTNAME SURNAME
## 1 Florence Bader
## 2 Timothy Baker
## 3 Gerald Butters
## 4 Jemima Farrell
## 5 Matthew Genting
## 6 David Joneshead(Q16)## 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 Bakerhead(Q17)## 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 2head(Q18)## MEMBER FACILITY COST
## 1 GUEST GUEST Massage Room 2 320
## 2 GUEST GUEST Tennis Court 2 198
## 3 GUEST GUEST Massage Room 1 160
## 4 GUEST GUEST Massage Room 1 160
## 5 GUEST GUEST Massage Room 1 160
## 6 Jemima Farrell Massage Room 1 140head(Q19)## MEMBER
## 1 Anna Mackenzie
## 2 Anne Baker
## 3 Burton Tracy
## 4 Charles Owen
## 5 Darren Smith
## 6 David Farrell
## (SELECTRECS.FIRSTNAME||''||RECS.SURNAMEASRECOMMENDERFROMA843931.MEMBERSRECSWHERERECS.MEMID=MEMS.RECOMMENDEDBY)
## 1 Darren Smith
## 2 Ponder Stibbons
## 3 <NA>
## 4 Darren Smith
## 5 <NA>
## 6 <NA>head(Q20)## MEMBER FACILITY COST
## 1 GUEST GUEST Massage Room 2 320
## 2 GUEST GUEST Tennis Court 2 198
## 3 GUEST GUEST Massage Room 1 160
## 4 GUEST GUEST Massage Room 1 160
## 5 GUEST GUEST Massage Room 1 160
## 6 Jemima Farrell Massage Room 1 140#Modifying data
Q21 <- dbGetQuery(con, "INSERT INTO A843931.FACILITIES
(FACID, NAME, MEMBERCOST, GUESTCOST, INITIALOUTLAY, MONTHLYMAINTENANCE)
SELECT 9, 'Spa', 20, 30, 100000, 800 FROM DUMMY
WHERE NOT EXISTS (SELECT 1 FROM A843931.FACILITIES WHERE FACID = 9)")
Q22 <- dbGetQuery(con, "INSERT INTO A843931.FACILITIES
(FACID, NAME, MEMBERCOST, GUESTCOST, INITIALOUTLAY, MONTHLYMAINTENANCE)
SELECT 9, 'Spa', 20, 30, 100000, 800 FROM DUMMY
WHERE NOT EXISTS (SELECT 1 FROM A843931.FACILITIES WHERE FACID = 9)
UNION ALL
SELECT 10, 'Squash Court 2', 3.5, 17.5, 5000, 80 FROM DUMMY
WHERE NOT EXISTS (SELECT 1 FROM A843931.FACILITIES WHERE FACID = 10)")
Q23 <- dbGetQuery(con, "INSERT INTO A843931.FACILITIES
(FACID, NAME, MEMBERCOST, GUESTCOST, INITIALOUTLAY, MONTHLYMAINTENANCE)
SELECT (SELECT MAX(facid) FROM A843931.FACILITIES) + 1, 'Spa', 20, 30, 100000, 800 FROM DUMMY")
Q24 <- dbGetQuery(con, "UPDATE A843931.FACILITIES
SET initialoutlay = 10000;")
Q25 <- dbGetQuery(con, "UPDATE A843931.FACILITIES
SET
membercost = 6,
guestcost = 30
WHERE facid IN (0, 1)")
Q26 <- dbGetQuery(con, "UPDATE A843931.FACILITIES
SET
MEMBERCOST =
(SELECT MEMBERCOST * 1.1 FROM A843931.FACILITIES WHERE FACID = 0),
GUESTCOST =
(SELECT GUESTCOST * 1.1 FROM A843931.FACILITIES WHERE FACID = 0)
WHERE
FACID = 1")
Q27 <- dbGetQuery(con, "DELETE FROM A843931.MEMBERS WHERE memid = 37;")
Q28 <- dbGetQuery(con, "DELETE FROM A843931.MEMBERS WHERE NOT EXISTS
(SELECT 1 FROM A843931.BOOKINGS WHERE A843931.MEMBERS.memid
= A843931.BOOKINGS.memid);")head(Q21)## data frame with 0 columns and 0 rowshead(Q22)## data frame with 0 columns and 0 rowshead(Q23)## data frame with 0 columns and 0 rowshead(Q24)## data frame with 0 columns and 0 rowshead(Q25)## data frame with 0 columns and 0 rowshead(Q26)## data frame with 0 columns and 0 rowshead(Q27)## data frame with 0 columns and 0 rowshead(Q28)## data frame with 0 columns and 0 rows#String Operations
Q29 <- dbGetQuery(con, "SELECT SURNAME || ', ' || FIRSTNAME AS NAME FROM A843931.MEMBERS")
Q30 <- dbGetQuery(con, "SELECT * FROM A843931.FACILITIES WHERE NAME LIKE 'Tennis%'")
Q31 <- dbGetQuery(con, "SELECT * FROM A843931.FACILITIES WHERE UPPER(NAME) LIKE 'TENNIS%'")
Q32 <- dbGetQuery(con, "SELECT MEMID, TELEPHONE
FROM A843931.MEMBERS
WHERE TELEPHONE LIKE '%(%' OR TELEPHONE LIKE '%)%'")
Q33 <- dbGetQuery(con, "SELECT LPAD(CAST(ZIPCODE AS CHAR(5)), 5, '0') AS ZIP
FROM A843931.MEMBERS ORDER BY ZIP")
Q34 <- dbGetQuery(con, "SELECT SUBSTR(MEMS.SURNAME, 1, 1) AS LETTER, COUNT(*) AS COUNT
FROM A843931.MEMBERS MEMS
GROUP BY MEMS.SURNAME
ORDER BY MEMS.SURNAME")
Q35 <- dbGetQuery(con, "SELECT MEMID,
REPLACE(REPLACE(REPLACE(TELEPHONE, '-', ''), '(', ''), ')', '') AS TELEPHONE
FROM A843931.MEMBERS
ORDER BY MEMID")Resultados de los Querys
head(Q29)## NAME
## 1 GUEST, GUEST
## 2 Smith, Darren
## 3 Smith, Tracy
## 4 Rownam, Tim
## 5 Joplette, Janice
## 6 Butters, Geraldhead(Q30)## 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 200head(Q31)## 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 200head(Q32)## 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-4001head(Q33)## ZIP
## 1 00000
## 2 00234
## 3 00234
## 4 04321
## 5 04321
## 6 10383head(Q34)## LETTER COUNT
## 1 B 1
## 2 B 2
## 3 B 1
## 4 B 1
## 5 C 1
## 6 C 1head(Q35)## MEMID TELEPHONE
## 1 0 000 0000000
## 2 1 5555555555
## 3 2 5555555555
## 4 3 844 6930723
## 5 4 833 9424710
## 6 5 844 0784130