SQL 2์ผ์ฐจ
์ค๋์ ๋จ์ผ ๋ฐ์ดํฐ๊ฐ ์๋ ์ธ๋ถ๋ฐ์ดํฐ์ JOIN, ์๋ฆฌ์ฐ์ฐ,๋ฌธ์์ด ํจ์, sub query ๋ฑ์ ํ์ตํ์๋ค.
INNER JOIN
๋ ํ ์ด๋ธ์ ์กฐ์ธํ ๋, ๋ ํ ์ด๋ธ์ ๋ชจ๋ ์ง์ ํ ์ด์ ๋ฐ์ดํฐ๊ฐ ์์ด์ผ ํ๋ค.
โ๏ธ๋ ํ ์ด๋ธ ๋ชจ๋ ์กด์ฌํ๊ธฐ ๋๋ฌธ์, ์์ ์๊ด์์ด ๋์ผํ ๊ฒฐ๊ณผ๊ฐ ์ถ๋ ฅ๋๋ค.
SELECT *
FROM Registrations //1๋ฒ์งธ ํ
์ด๋ธ
INNER JOIN Logins //2๋ฒ์งธ ํ
์ด๋ธ
ON Registrations.name=Logins.name //์กฐ์ธ์กฐ๊ฑด
OUTER JOIN
๋ ํ ์ด๋ธ์ ์กฐ์ธํ ๋, 1๊ฐ์ ํ ์ด๋ธ์๋ง ๋ฐ์ดํฐ๊ฐ ์์ด๋ ๊ฒฐ๊ณผ๊ฐ ๋์จ๋ค.
FULL OUTER JOIN
: ์ผ์ชฝ ๋๋ ์ค๋ฅธ์ชฝ ํ ์ด๋ธ์ ๋ชจ๋ ๊ฐ์ด ์ถ๋ ฅ๋๋ ์กฐ์ธ
SELECT * FROM instructor I FULL OUTER JOIN teaches T ON I.id=T.id
LEFT OUTER JOIN
: ์ผ์ชฝ ํ ์ด๋ธ์ ๋ชจ๋ ๊ฐ์ด ์ถ๋ ฅ๋๋ ์กฐ์ธ
SELECT * FROM instructor I LEFT OUTER JOIN teaches T ON I.id=T.id
with WHERE
where๋ฌธ๊ณผ ๊ฐ์ด ์ฌ์ฉํ์ฌ, ์ด์ ๊ฐ์ด Aํ ์ด๋ธ๋ง์ ๊ณ ์ ํ ํ ์ด๋ธ์ ๊ตฌํ ์ ์๋ค.
RIGHT OUTER JOIN
: ์ค๋ฅธ์ชฝ ํ ์ด๋ธ์ ๋ชจ๋ ๊ฐ์ด ์ถ๋ ฅ๋๋ ์กฐ์ธ
SELECT * FROM instructor I RIGHT OUTER JOIN teaches T ON I.id=T.id
with WHERE
where๋ฌธ ์กฐ๊ฑด์ null์ ์ฌ์ฉํ์ฌ, Bํ ์ด๋ธ๋ง์ ๊ณ ์ ํ ํ ์ด๋ธ์ ๊ตฌํ ์ ์๋ค.
์ฐธ์กฐ
SELF JOIN
๊ฐ์ํ์ ๋ ๋ณต์ฌ๋ณธ์ ํฉ์ฒ๋ผ ๋ณด์ผ์ ์๋ค.
SELECT tableA.col, table.B col
FROM table AS tableA
JOIN table AS tableB
ON tableA.some_col=tableB.other_col
๊ฐ์ ํ ์ด๋ธ์ ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ง๊ณ , ๋ณต์ฌ๋ณธ์ ๋ง๋ค์ด ์กฐ์ธํ๋ค.
UNION
ํ๋์ ํ ์ด๋ธ์์ ๋ ์ฝ๋๋ฅผ ๊ฒฐํฉํ๋ ๋ฐ ์ฌ์ฉ๋๋ ์ฐ์ฐ์ ์ค ํ๋๋ก์จ, UNION์ ์ฌ์ฉํ๋ฉด ๋ ๊ฐ ์ด์์ SELECT ๋ฌธ์ ๊ฒฐ๊ณผ๋ฅผ ๊ฒฐํฉํ์ฌ ์ค๋ณต์ ์ ๊ฑฐํ ๊ฒฐ๊ณผ๋ฅผ ์ป์ ์ ์์ต๋๋ค.
๋ฐ๋ก ์ค๋ณต๋๋ ๋ถ๋ถ์ ํ๋์ row๋ก ์ถ๋ ฅ๋๋ค. UNION ์ฐ์ฐ์์ UNION ALL ์ฐ์ฐ์๋ ๋ ๊ฐ์ด์์ SQL ์ฟผ๋ฆฌ๋ฅผ ํ๋๋ก ๋ง๋ค์ด์ฃผ์ง๋ง ์ฐจ์ด๋๋ ๋ถ๋ถ์ ์ค๋ณต์ฌ๋ถ์ด๋ค. ๋ํ UNION ์ฐ์ฐ์๋ฅผ ์ฌ์ฉํ ์์๋ ๊ฐ ์ฟผ๋ฆฌ์ ์ปฌ๋ผ ๊ฐฏ์ ๋ฐ ๋ช ์นญ์ ๋ง์ถฐ์ค์ผ ๋๋ฉฐ ๊ทธ๋ ์ง ์์ผ๋ฉด ์ค๋ฅ๊ฐ ๋ฐ์ (ex. ์ด๋ฆ๊ธฐ์ค์ผ๋ก ์๋ฃ ์ ๋ ฌํ๊ฑฐ๋ ๋ถ๋ฅํ ๋ ์ฌ์ฉ)
sub query
์ฟผ๋ฆฌ ์์ ์๋ธ ์ฟผ๋ฆฌ๋ก, ํ์ ์ฟผ๋ฆฌ์ ๊ฒฐ๊ณผ๋ฅผ ์์ ์ฟผ๋ฆฌ์์ ์ฌ์ฉํด์ ๊ฐ๋จํ๊ฒ ์์ฑํ ์ ์๋ค.
SELECT student, grade
FROM test_scores
WHERE grade>(SELECT AVG(grade))
FROM test_scores
โ๏ธ๊ดํธ ์์ ์๋ธ ์ฟผ๋ฆฌ๊ฐ ๊ฐ์ฅ ๋จผ์ ์๋๋๊ณ , ์ด๋ฅผ ๊ธฐ๋ฐ์ผ๋ก where ์กฐ๊ฑด๋ฌธ์ด ์คํ๋๋ค.
ํ๊ฐ ์ํ2: ๋ฌธ์ ํ์ด
1. How can you retrieve all the information from the cd.facilities table?
SELECT * FROM cd.facilites
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?
SELECT name,membercost FROM cd.facilites
3. How can you produce a list of facilities that charge a fee to members?
SELECT * FROM cd.facilites WHERE membercost>0
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.
SELECT facid, name, membercost, monthlymaintenance
FROM cd.facilites
WHERE membercost>0 AND (membercost < monthlymaintenance/50.0)
5. How can you produce a list of all facilities with the word 'Tennis' in their name?
SELECT *
FROM cd.facilites
WHERE name LIKE '%Tennis%'
6. 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)
7. 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'
2012๋ 9์ ์ดํ ์ถ๋ ฅ
8. 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
LIMIT 10
9. You'd like to get the signup date of your last member. How can you retrieve this information?
SELECT MAX(joindate) AS lasted FROM cd.members
10. Produce a count of the number of facilities that have a cost to guests of 10 or more.
SELECT COUNT(*) FROM cd.facilites
WHERE guestcost>=10
11. 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.
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(slots)
9์ ๋์์ slot์ด๋ฏ๋ก, 09-01<=date<10-01
SELECT facid, SUM(slots) AS "Total Slots"
FROM cd.bookings
WHERE starttime BETWEEN '2012-09-01' AND '2012-09-30'
GROUP BY facid
between์ 09-01~09-30
12. 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.
SELECT facid, SUM(slots) AS total_slots
FROM cd.bookings
GROUP BY facid
HAVING SUM(slots)>1000
ORDER BY facid
SUM(์ง๊ณํจ์)์ ๋ํ ์กฐ๊ฑด์ where์ด ์๋ having์ ์์ ์์ฑํ๋ค.
13. 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.
SELECT cd.bookings.starttime AS start , cd.facilities.name AS 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'
ORDER BY cd.bookings.starttime
facid๋ฅผ ๊ธฐ์ค์ผ๋ก facilites,bookings์ ํ ์ด๋ธ์ inner join (๋ ํ ์ด๋ธ ๋ชจ๋ ์ค๋ณต๋๋)ํ ํ,
1. tennis courts ์กฐ๊ฑด : cd.facilities.facid IN (0,1)
2. 22/09/21์ ์์ฝ : cd.bookings.starttime >= '2012-09-21' AND cd.bookings.starttime < '2012-09-22'
14. How can you produce a list of the start times for bookings by members named 'David Farrell'?
SELECT cd.bookings.starttime FROM cd.boookings
INNER JOIN cd.members
ON cd.members.memid=cd.bookings.memid
WHERE cd.members.firstname='David' AND cd.members.surname='Farrell'