[PostgreSQL] Practice_1

연습문제

자료
문제

# 1번
SELECT * FROM cd.facilities

# 2번 
SELECT name,membercost FROM cd.facilities

# 3번
SELECT name,membercost FROM cd.facilities limit 5
# 정답
SELECT name,membercost FROM cd.facilities
WHERE membercost > 0
-- 문제를 잘못이해했음 , 결과만 보고 문제 품 --

# 4번
 SELECT facid, name, membercost, monthlymaintenance
 FROM cd.facilities
 WHERE membercost > 0 
 AND membercost < (monthlymaintenance/50.0)

# 5번
SELECT * FROM cd.facilities
WHERE name like '%Tennis%'

# 6번
SELECT * FROM cd.facilities
WHERE name like '%2'

# 7번
SELECT memid,surname,firstname,joindate FROM cd.members
WHERE EXTRACT(month FROM joindate) = 9
-- TO_CHAR 는 데이터 조작 불가한 듯
# 정답
SELECT memid,surname,firstname,joindate FROM cd.members
WHERE joindate >= '2012-09-01'
-- 그냥 이렇게 해도 됨!!

# 8번
SELECT DISTINCT(surname) FROM cd.members 
ORDER BY surname LIMIT 10 

# 9번
SELECT joindate as Result FROM cd.members 
ORDER BY joindate desc LIMIT 1 
-- 정확히 모르겠음 뭘 원하고 어떤 방법을 원하는지 모르겠음
--  MAX(joindate) 이런 식으로 써도 됨

# 10번
SELECT COUNT(*) FROM cd.facilities
where guestcost >= 10

# 11번
SELECT facid,sum(slots) FROM cd.bookings
WHERE EXTRACT(month from starttime) = 9
GROUP BY facid
ORDER BY sum
-- 이름 as 잘 못 하겠음
# 정답
SELECT facid,sum(slots) AS total_slots FROM cd.bookings
WHERE starttime >= '2012-09-01' 
AND starttime <= '2012-10-01'
GROUP BY facid
ORDER BY sum
--BETWEE을 사용시에 09-30 이 안된다면 10-01을 대신 사용

# 12번
SELECT facid,sum(slots) FROM cd.bookings
GROUP BY facid
HAVING sum(slots) > 1000
ORDER BY facid

# 13번
SELECT cd.bookings.starttime, cd.facilities.name  FROM cd.facilities
INNER JOIN cd.bookings on cd.facilities.facid = cd.bookings.facid
WHERE date_trunc('day',starttime) = '2012-09-21' and name like 'Tennis Court%'

#정답
SELECT cd.bookings.starttime, cd.facilities.name  FROM cd.facilities
INNER JOIN cd.bookings on cd.facilities.facid = cd.bookings.facid
WHERE cd.facilities.facid IN (0,1)
AND cd.bookings.starttime >= '2012-09-21'
AND cd.bookings.starttime < '2012-09-22'

# 14번
SELECT * FROM cd.bookings INNER JOIN cd.members 
on cd.bookings.memid = cd.members.memid
WHERE firstname = 'David' AND surname = 'Farrell'