[SQL] 5. 2차 평가

🖥 시작하며

1. How can you retrieve all the information from the cd.facilities table?

SELECT * FROM cd.facilities

1. 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?

SELECT name, membercost 
FROM cd.facilities

1. How can you produce a list of facilities that charge a fee to members?

SELECT *
FROM cd.facilities
WHERE membercost != 0

1. 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.

SELECT facid, name, membercost, monthlymaintenance
FROM cd.facilities
WHERE monthlymaintenance / 50 > membercost and membercost != 0

1. How can you produce a list of all facilities with the word 'Tennis' in their name?

SELECT *
FROM cd.facilities
WHERE name LIKE '%Tennis%'

1. How can you retrieve the details of facilities with ID 1 and 5? Try to do it without using the OR operator.

SELECT *
FROM cd.facilities
WHERE facid IN (1,5);

1. 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.

SELECT memid, surname, firstname, joindate
FROM cd.members
WHERE joindate >= '2012-09-01'

1. How can you produce an ordered list of the first 10 surnames in the members table? The list must not contain duplicates.

SELECT DISTINCT(surname)
FROM cd.members
ORDER BY surname ASC
LIMIT 10

1. You'd like to get the signup date of your last member. How can you retrieve this information?

SELECT joindate
FROM cd.members
ORDER BY joindate DESC
LIMIT 1

SELECT MAX(joindate) AS latest_signup 
FROM cd.members;

1. Produce a count of the number of facilities that have a cost to guests of 10 or more.

SELECT COUNT(*)
FROM cd.facilities
WHERE guestcost >= 10

1. Produce a list of the total number of slots booked per facility in the month of September 2012. Produce an output table consisting of facility id and slots, sorted by the number of slots.
2012년 9월에 시설별로 예약된 슬롯 총 수 목록을 작성하세요. 시설 ID와 슬롯으로 구성된 출력 표를 작성하고 슬롯 수를 기준으로 정렬하세요.

SELECT cd.bookings.facid, SUM(slots) AS Total_Slots
FROM cd.bookings
WHERE starttime >= '2012-09-01' and starttime < '2012-10-01' 
GROUP BY cd.bookings.facid
ORDER BY SUM(slots) ASC

1. Produce a list of facilities with more than 1000 slots booked. Produce an output table consisting of facility id and total slots, sorted by facility id.
1000개 이상의 슬롯이 예약된 시설 목록을 생성합니다. 시설 ID와 총 슬롯으로 구성된 출력 테이블을 생성하고, 시설 ID로 정렬합니다.

SELECT facid, SUM(slots) 
FROM cd.bookings
GROUP BY facid
HAVING SUM(slots) >= 1000
ORDER BY facid ASC

1. 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.
'2012-09-21' 날짜에 테니스장 예약 시작 시간 목록을 어떻게 생성할 수 있습니까? 시간순으로 정렬된 시작 시간과 시설 이름 쌍 목록을 반환합니다.

SELECT starttime AS start, name
FROM cd.facilities
INNER JOIN cd.bookings
ON cd.facilities.facid = cd.bookings.facid
WHERE name LIKE 'Tennis%' 
	and TO_CHAR(starttime, 'yyyy-mm-dd') = '2012-09-21'
ORDER BY starttime ASC

1. How can you produce a list of the start times for bookings by members named 'David Farrell'?
'데이비드 패럴'이라는 이름의 회원이 예약한 시작 시간 목록을 어떻게 만들 수 있습니까?

SELECT cd.bookings.starttime
FROM cd.bookings
INNER JOIN cd.members
ON cd.bookings.memid = cd.members.memid
WHERE cd.members.firstname = 'David' and cd.members.surname = 'Farrell'