오전에 데이터베이스 속성 교육을 마친 뒤 SQLite3를 이용한 DB 실습을 진행했다.
DB 개론은 취업 준비하면서 이래저래 공부해와서 한 번쯤 들어봤던 내용이 많았다.
그 중 CAP이론은 처음 듣는 개념이었는데 간단히 정리해보고 넘어가겠다.
CAP 정리란?
CAP 정리에 따르면 분산 시스템은 일관성(consistency), 가용성(availability), 및 파티션 허용(partition tolerance)(CAP의 'C', 'A', 'P')이라는 세 가지 특성 중 두 가지 특성만 제공할 수 있습니다.
CAP 정리가 말하는 분산 시스템의 세 가지 특성을 자세히 살펴보겠습니다.
일관성
일관성이란 어떤 노드에 연결하든 모든 클라이언트가 동시에 동일한 데이터를 볼 수 있음을 의미합니다. 이렇게 하려면 데이터가 한 노드에 기록될 때마다 쓰기가 '성공'된 것으로 간주되기 전에 시스템의 다른 모든 노드에 데이터를 즉시 전달하거나 복제해야 합니다.
가용성
가용성은 데이터를 요청하는 모든 클라이언트가 하나 이상의 노드가 다운된 경우에도 응답을 받는다는 것을 의미합니다. 즉, 분산 시스템의 모든 작업 노드가 예외 없이 모든 요청에 대해 유효한 응답을 반환합니다.
분할 내성
분할은 분산 시스템 내의 통신 중단으로, 두 노드 간의 연결이 끊어지거나 일시적으로 지연됩니다. 분할 내성은 시스템의 노드 간 통신 중단이 아무리 많아도 클러스터가 계속 작동해야 함을 의미합니다.
SQLite3 연습 문제
강사님이 숙제로 풀어보라고 내 준 유명한 문제들.
구글에 문제를 붙여 넣으면 누군가 풀어놓은 답안이 나오지만 이게 정답이다! 싶은 쿼리는 안 보인다.
정말 못 풀겠다 싶은 건 GPT와 구글링을 활용했고, 대부분 내가 풀어서 정답을 장담할 수 없다.
- non_usa_customers.sql: Provide a query showing Customers (just their full names, customer ID and country) who are not in the US.
답
SELECT FirstName||' '||LastName AS 'Full Name', CustomerId, Country
FROM customers
WHERE country != 'USA';SELECT FirstName||' '||LastName AS 'Full Name', CustomerId, Country
FROM customers
WHERE country != 'USA';
- brazil_customers.sql: Provide a query only showing the Customers from Brazil.
답
SELECT * FROM customers WHERE Country = 'Brazil';
- brazil_customers_invoices.sql: Provide a query showing the Invoices of customers who are from Brazil. The resultant table should show the customer's full name, Invoice ID, Date of the invoice and billing country.
SELECT FirstName||''||LastName AS 'Full Name', InvoiceId, InvoiceDate, BillingCountry
FROM customers C INNER JOIN invoices I ON I.CustomerID = C.CustomerId
WHERE C.country = 'Brazil'
ORDER BY 'Full Name';
- sales_agents.sql: Provide a query showing only the Employees who are Sales Agents.
SELECT * FROM employees WHERE Title = 'Sales Support Agent';
- unique_invoice_countries.sql: Provide a query showing a unique/distinct list of billing countries from the Invoice table.
SELECT DISTINCT BillingCountry FROM invoices;
- sales_agent_invoices.sql: Provide a query that shows the invoices associated with each sales agent. The resultant table should include the Sales Agent's full name.
SELECT e.FirstName||''||e.LastName AS 'Full Name', InvoiceId, c.CustomerId, InvoiceDate, BillingAddress, BillingCity, BillingState, BillingCountry, BillingPostalCode, Total
FROM customers c
INNER JOIN invoices i
ON c.CustomerId = i.CustomerId
INNER JOIN employees e
ON c.SupportRepId = e.EmployeeId
WHERE e.Title = 'Sales Support Agent';
- invoice_totals.sql: Provide a query that shows the Invoice Total, Customer name, Country and Sale Agent name for all invoices and customers.
SELECT i.Total, c.FirstName||''||c.LastName AS 'Customer name', i.BillingCountry, c.Country, e.FirstName ||''|| e.LastName AS 'Sale Agent name'
FROM invoices i
INNER JOIN customers c
ON i.CustomerId = c.CustomerId
INNER JOIN employees e
ON c.SupportRepId = e.EmployeeId
WHERE e.Title = 'Sales Support Agent';
- totalinvoices{year}.sql: How many Invoices were there in 2009 and 2011?
SELECT COUNT(*)
FROM invoices
WHERE strftime('%Y', InvoiceDate) IN ('2009', '2011');- totalsales{year}.sql: What are the respective total sales for each of those years?
select strftime('%Y', i.InvoiceDate) as "Year", Sum(i.Total) as "Total Sales"
from invoices i
GROUP BY strftime('%Y', i.InvoiceDate);- invoice_37_line_item_count.sql: Looking at the InvoiceLine table, provide a query that COUNTs the number of line items for Invoice ID 37.
SELECT COUNT(*)
FROM invoice_items ii
WHERE ii.InvoiceLineId = 37;- line_items_per_invoice.sql: Looking at the InvoiceLine table, provide a query that COUNTs the number of line items for each Invoice. HINT: GROUP BY
SELECT InvoiceLineId, COUNT(InvoiceLineId)
FROM invoice_items ii
GROUP BY ii.InvoiceId;- line_item_track.sql: Provide a query that includes the purchased track name with each invoice line item.
SELECT ii.InvoiceLineId, Name
FROM tracks t
JOIN invoice_items ii
ON t.TrackId = ii.TrackId
ORDER BY t.Name- line_item_track_artist.sql: Provide a query that includes the purchased track name AND artist name with each invoice line item.
SELECT ii.InvoiceLineId, t.Name AS TrackName, a.Name AS ArtistName
FROM invoice_items ii
JOIN tracks t
ON ii.TrackId = t.TrackId
JOIN albums al
ON t.AlbumId = al.AlbumId
JOIN artists a
ON al.ArtistId =a.ArtistId- country_invoices.sql: Provide a query that shows the # of invoices per country. HINT: GROUP BY
SELECT BillingCountry, COUNT(InvoiceId)
FROM invoices
GROUP BY BillingCountry;- playlists_track_count.sql: Provide a query that shows the total number of tracks in each playlist. The Playlist name should be include on the resulant table.
SELECT p.Name, Count(t.TrackId)
FROM playlists p
JOIN playlist_track pt
ON p.PlaylistId = pt.PlaylistId
JOIN tracks t
ON pt.TrackId = t.TrackId
GROUP BY p.Name- tracks_no_id.sql: Provide a query that shows all the Tracks, but displays no IDs. The result should include the Album name, Media type and Genre.
SELECT t.Name AS TrackName, al.Title, m.Name AS MediaTypeName, g.Name AS GenreName
FROM tracks t, albums al, media_types m, genres g
WHERE t.AlbumId = al.AlbumId AND
t.MediaTypeId = m.MediaTypeId AND
t.GenreId = g.GenreId;- invoices_line_item_count.sql: Provide a query that shows all Invoices but includes the # of invoice line items.
SELECT i.InvoiceId, COUNT(ii.InvoiceLineId) AS "Number of invoice line items"
FROM invoices i, invoice_items ii
WHERE i.InvoiceId = ii.InvoiceId
GROUP BY i.InvoiceId- sales_agent_total_sales.sql: Provide a query that shows total sales made by each sales agent.
SELECT SUM(i.Total) AS "total sales", e.Title AS "sales agent", e.EmployeeId
FROM employees e, customers c, invoices i
WHERE e.EmployeeId = c.SupportRepId AND
c.CustomerId = i.CustomerId
GROUP BY e.EmployeeId;- top_2009_agent.sql: Which sales agent made the most in sales in 2009?
Hint: Use the MAX function on a subquery.
SELECT e.FirstName || ' ' || e.LastName AS "Sales Agent", total_sales
FROM (
SELECT e.EmployeeId, SUM(i.Total) AS total_sales
FROM employees e
JOIN customers c ON e.EmployeeId = c.SupportRepId
JOIN invoices i ON c.CustomerId = i.CustomerId
WHERE strftime('%Y', i.InvoiceDate) = '2009'
GROUP BY e.EmployeeId
) AS sales_by_agent
JOIN employees e ON e.EmployeeId = sales_by_agent.EmployeeId
WHERE total_sales = (
SELECT MAX(total_sales)
FROM (
SELECT SUM(i.Total) AS total_sales
FROM employees e
JOIN customers c ON e.EmployeeId = c.SupportRepId
JOIN invoices i ON c.CustomerId = i.CustomerId
WHERE strftime('%Y', i.InvoiceDate) = '2009'
GROUP BY e.EmployeeId
)
)
아니 이게 튜토리얼??- top_agent.sql: Which sales agent made the most in sales over all?
SELECT e.EmployeeId, SUM(i.Total)
FROM employees e, customers c, invoices i
WHERE e.EmployeeId = c.SupportRepId AND
c.CustomerId = i.CustomerId
GROUP BY e.EmployeeId
ORDER BY SUM(i.Total) desc
Limit 1;- sales_agent_customer_count.sql: Provide a query that shows the count of customers assigned to each sales agent.
SELECT COUNT(c.CustomerId), e.EmployeeId
FROM employees e, customers c
WHERE e.EmployeeId = c.SupportRepId
GROUP BY e.EmployeeId;- sales_per_country.sql: Provide a query that shows the total sales per country.
SELECT i.BillingCountry, SUM(i.Total)
FROM invoices i
GROUP BY i.BillingCountry;- top_country.sql: Which country's customers spent the most?
SELECT c.CustomerId, c.Country, SUM(i.Total)
FROM customers c, invoices i
WHERE c.CustomerId = i.CustomerId
GROUP BY c.CustomerId
ORDER BY SUM(i.Total) DESC
LIMIT 1;- top_2013_track.sql: Provide a query that shows the most purchased track of 2013.
SELECT t.Name, SUM(i.InvoiceId)
FROM tracks t, invoice_items ii, invoices i
WHERE t.TrackId = ii.TrackId AND
ii.InvoiceId = i.InvoiceId AND
strftime('%Y', i.InvoiceDate) = '2013'
GROUP BY i.InvoiceId
ORDER BY SUM(i.InvoiceId) DESC
LIMIT 1;- top_5_tracks.sql: Provide a query that shows the top 5 most purchased songs.
SELECT al.Title, SUM(i.InvoiceId)
FROM albums al, tracks t, invoice_items ii, invoices i
WHERE al.AlbumId =t.AlbumId AND
t.TrackId = ii.TrackId AND
ii.InvoiceId = i.InvoiceId
GROUP BY al.AlbumId
ORDER BY SUM(i.InvoiceId) DESC
LIMIT 5;
- top_3_artists.sql: Provide a query that shows the top 3 best selling artists.
SELECT a.Name, SUM(i.InvoiceId)
FROM artists a , albums al, tracks t, invoice_items ii, invoices i
WHERE a.ArtistId = al.ArtistId AND
al.AlbumId = t.AlbumId AND
t.TrackId = ii.TrackId AND
ii.InvoiceId = i.InvoiceId
GROUP BY i.InvoiceId
ORDER BY SUM(i.InvoiceId) DESC
LIMIT 3;
- top_media_type.sql: Provide a query that shows the most purchased Media Type.
SELECt m.MediaTypeId, m.Name, SUM(i.InvoiceId )
FROM media_types m, tracks t, invoice_items ii , invoices i
WHERE m.MediaTypeId = t.MediaTypeId AND
t.TrackId = ii.TrackId AND
ii.InvoiceId = i.InvoiceId
GROUP BY i.InvoiceId
ORDER BY SUM(i.InvoiceId) DESC;
짤막한 하루 회고
강의가 거듭될수록 난이도가 올라가는 게 체감된다.
특히 강사님의 타이핑 속도가 빨라져 맨 앞에 앉았는데도 보고 따라치기가 어려울 정도다.
내가 타이핑한 코드가 오류 나서 놓쳤는데, 그 다음부터는 강사님의 코딩쇼를 보며 설명을 머리에 집어 넣어야 했다.
개인 공부 시간 확보의 중요성을 느꼈다...!
참고
https://www.ibm.com/kr-ko/topics/cap-theorem
https://www.geeksforgeeks.org/the-cap-theorem-in-dbms/
https://medium.com/@detoxicdev/cap-theorem-an-impossible-choice-c04482b8a36c
