SQL practice

Posted on | In
chinook DB 이용 SQL문 작성 연습

스키마 제작

스크린샷 2021-09-13 15 36 30

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE Customer(
    customer_id INTEGER NOT NULL PRIMARY KEY,
    customer_name VARCHAR(32) NOT NULL,
    customer_age INTEGER
);

CREATE TABLE Package (
	package_id INTEGER NOT NULL PRIMARY KEY,
	package_name VARCHAR(32) NOT NULL,
	package_date DATE
);

CREATE TABLE Customer_Package (
	cp_id INTEGER PRIMARY KEY NOT NULL,
	customer_id INTEGER,
	package_id INTEGER,
	FOREIGN KEY(customer_id) REFERENCES Customer(customer_id),
	FOREIGN KEY(package_id) REFERENCES Package(package_id)
);

스크린샷 2021-09-13 15 38 40

SQL 연습

엔티티 관계도

스크린샷 2021-09-13 15 40 01

요구사항

  • 1. AlbumId 가 31 인 앨범의 Title 을 구합니다.
1
2
3
SELECT AlbumId, Title 
FROM albums a
WHERE AlbumId = 31;
AlbumId Title
31 Bongo Fury
  • 2. 아티스트 이름에 ‘the’ 가 들어간 앨범의 AlbumId를 전부 조회합니다.
1
2
3
4
5
SELECT a.AlbumId , a2.Name 
FROM albums a 
JOIN artists a2
ON a.ArtistId = a2.ArtistId
WHERE a2.Name LIKE '%the%';
AlbumId Name
209 The Black Crowes
210 The Black Crowes
211 The Clash
212 The Cult
213 The Cult
… …
  • 3. invoices 테이블에서 BillingCity 가 Stuttgart, Oslo, Redmond 인 InvoiceId 를 InvoiceId 에 따라 오름차순으로 전부 조회합니다.
1
2
3
4
5
6
SELECT i.InvoiceId, i.BillingCity
FROM invoices i
WHERE BillingCity = 'Stuttgart'
OR BillingCity = 'Oslo'
OR BillingCity = 'Redmond'
ORDER BY InvoiceId;
InvoiceId BillingCity
1 Stuttgart
2 Oslo
12 Stuttgart
14 Redmond
24 Oslo
… …
  • 4. tracks 테이블에서 트랙 Name 이 ‘The’ 로 시작하는 trackId들을 전부 조회합니다.
1
2
3
SELECT TrackId, Name 
FROM tracks t
WHERE Name LIKE 'The%';
InvoiceId BillingCity
1 Stuttgart
2 Oslo
12 Stuttgart
14 Redmond
24 Oslo
… …
  • 5. customers 테이블에서 Email 이 ‘gmail.com’ 인 CustomerId를 전부 조회합니다.
1
2
3
SELECT CustomerId , Email 
FROM customers c 
WHERE Email LIKE '%gmail.com';
CustomerId Email
3 ftremblay@gmail.com
6 hholy@gmail.com
22 hleacock@gmail.com
24 fralston@gmail.com
28 jubarnett@gmail.com
… …
  • 6. CustomerId 가 29, 30, 63 인 고객들의 주문금액이 $1.00 이상 $3.00 이하인 주문 (invoice)의 Id를 전부 조회합니다.
    • 고객의 주문 금액인 Total을 invoice 테이블에서 찾으세요
1
2
3
4
5
6
SELECT i.InvoiceId 
FROM customers c
JOIN invoices i 
ON c.CustomerId = i.CustomerId
WHERE c.CustomerId IN (29, 30, 63)
AND i.Total >= 1 AND i.Total <= 3;
InvoiceId Total
169 1.98
364 1.98
49 1.98
267 1.98
  • 7. 장르 (genre) 가 ‘Soundtrack’ 인 트랙 중 트랙의 길이 (Milliseconds) 가 300,000 이상 400,000 이하인 트랙들의 Id 들을 전부 조회합니다.
1
2
3
4
5
6
7
SELECT t.TrackId , t.Milliseconds , g.Name  
FROM genres g 
JOIN tracks t 
ON g.GenreId = t.GenreId
WHERE g.Name = 'Soundtrack'
AND t.Milliseconds >= 300000
AND t.Milliseconds < 400000;
TrackId Milliseconds Name
2125 330266 Soundtrack
2127 328228 Soundtrack
2128 383764 Soundtrack
2131 340767 Soundtrack
  • 8. 각 나라 (country) 별로 고객 (customer) 수를 구해봅니다.
1
2
3
SELECT COUNT(*) AS 'The_Num_of_customers_X_Country'
FROM customers c 
GROUP BY Country ;
Country The_Num_of_customers_X_Country
Argentina 1
Australia 1
Austria 1
Belgium 1
Brazil 5
… …
  • 9. 총 구매한 비용이 가장 많은 고객 (customer) 5 명의 고객 (customer)의 CustomerId를 조회합니다.
    • 특정 고객이 여러개의 invoice를 가지고 있을 수 있음
1
2
3
4
5
6
7
8
SELECT c.CustomerId
FROM customers c
JOIN invoices i 
ON c.CustomerId = i.CustomerId
GROUP BY c.CustomerId
HAVING SUM(i.Total)
ORDER BY SUM(i.Total) DESC
LIMIT 5;
CustomerId SUM(i.Total)
6 49.620000000000005
26 47.620000000000005
57 46.62
45 45.62
46 45.62
  • 10. 각 장르 (genre) 마다 트랙을 구매한 고객의 id 의 수를 구해봅니다.
1
2
3
4
5
6
SELECT DISTINCT g.Name AS 'genre_name', COUNT(i.CustomerId) AS 'The Number of customer_ID'
FROM invoice_items ii 
JOIN invoices i ON ii.InvoiceId = i.InvoiceId 
JOIN tracks t ON ii.TrackId = t.TrackId 
JOIN genres g ON t.GenreId = g.GenreId
GROUP BY g.Name;
genre_name The Number of customer_ID
Alternative 14
Alternative & Punk 244
Blues 61
Bossa Nova 15
Classical 41
… …
  • 11. customers 테이블에서 각 고객의 ‘CustomerId’ 칼럼과 고객의 도시와 나라를 대문자로 합친 문자열 칼럼을 조회해야 합니다.
    • 도시와 나라 사이에는 한 칸을 띄웁니다. 예시 도시가 ‘Seoul’ 이고 나라가 ‘South Korea’ 인 경우에는 ‘SEOUL SOUTH KOREA’ 로 합칩니다.
1
2
SELECT CustomerId , UPPER(City || ' ' || Country) AS 'CC'
FROM customers c;
CustomerId CC
1 SãO JOSé DOS CAMPOS BRAZIL
2 STUTTGART GERMANY
3 MONTRéAL CANADA
4 OSLO NORWAY
5 PRAGUE CZECH REPUBLIC
6 PRAGUE CZECH REPUBLIC
  • 12. 새로운 customer 아이디를 만들어봅니다. 새로운 아이디는 customer의 FirstName 의 첫 4 글자와 LastName 의 첫 2 글자를 합친 소문자입니다.
    • 예시 FirstName 이 ‘Mark’ 이고 LastName 이 ‘Zonzales’ 인 경우에는 ‘markzo’ 가 됩니다.
1
2
SELECT LOWER((SUBSTRING(FirstName, 1, 4) || SUBSTRING(LastName, 1, 2))) AS 'New_Id'
FROM customers c;
New_Id
luísgo
leonkö
frantr
bjørha
franwi
…
  • 13. 직원 (employee) 중에서 회사에서 2020년 1월 1일 기준으로 7년 넘게 (>) 근무한 직원들의 EmployeeId 를 조회해야 합니다. 조회된 결과는 LastName 을 기준으로 오름차순으로 정렬합니다.
1
2
3
4
SELECT EmployeeId, HireDate 
FROM employees e
WHERE HireDate < Datetime('2013-01-01 00:00:00')
ORDER BY LastName;
EmployeeId HireDate
1 2002-08-14 00:00:00
8 2004-03-04 00:00:00
2 2002-05-01 00:00:00
5 2003-10-17 00:00:00
7 2004-01-02 00:00:00
6 2003-10-17 00:00:00
  • 14. 새로운 고객 주문 번호를 만들어 봅니다. 새로운 주문 번호는 각 고객의 FirstName과 LastName과 InvoiceId 를 합쳐서 만듭니다. 각 고객의 새로운 주문 번호를 다음 기준으로 순차적으로 오름차순 정렬합니다
1
2
3
4
SELECT FirstName || LastName || i.InvoiceId AS 'new_number' 
FROM customers c 
JOIN invoices i ON c.CustomerId = i.CustomerId
ORDER BY FirstName, LastName, i.InvoiceId;
new_number
AaronMitchell50
AaronMitchell61
AaronMitchell116
AaronMitchell245
AaronMitchell268
AaronMitchell290
  • 15. FirstName, LastName, InvoiceId 순으로 오름차순 정렬하세요
    • 예를 들어 FirstName 이 ‘Sponge’, LastName 이 ‘Bob’ InvoiceId 가 ‘24’ 인 경우 ‘SpongeBob24’ 가 됩니다.
    • 서브쿼리를 이용해서 앨범타이틀이 ‘Unplugged’ 이거나 ‘Outbreak’ 인 Track의 Name을 모두 출력하세요
1
2
3
4
5
6
SELECT t.Name , AlbumId 
FROM tracks t
WHERE AlbumId IN (
SELECT AlbumId 
FROM albums a
WHERE Title = 'Unplugged' OR Title = 'Outbreak');
Name AlbumId
Roll Call 68
Otay 68
Groovus Interruptus 68
Paris On Mine 68
In Time 68
Plan B 68
0%