SQL 구문 정리 with W3Sschools Tutorial

Exercise v3.0 (w3schools.com)

SELECT

#1
SELECT * FROM Customers

#2
SELECT City from Customers

#3
SELECT DISTINCT Country FROM Customer

WHERE

#1
select * from Customers
where City = "Berlin"

#2
select * from Customers
where CustomerID = 32

ORDER BY

#1
Select * From Customers
Order by City

#2
select * from Customers
order by City desc

#3
select * from Customers
order by Country, City

AND OR NOT

#1 
Select * From Customers
where City = "Berlin" and PostalCode = '12209'

#2
Select * From Customers
where City = 'Berlin' or City = "London"

#3
Select * where not City = "Berlin"

INSERT

#기본문
INSERT INTO TABLE_NAME(COL1, COL2, ---)
VALUES (VAL1, VAL2, ---)

#1
INSERT INTO Customers (CustomerName, Address, City, PostalCode, Country)
VALUES ("Hekkan Burger", "Gateveien 15", "sandnes", 4306, "Norway")

NULL

#1
SELECT * From Customers
Where PostalCode is Null

#2
SELECT * From Customers
Where PostalCode is NOT Null

UPDATE

#Update the City column of all recores in the Customers table
Update Customers
Set City = 'Oslo'

#Set the value of the City columns to 'Oslo', 
#but only the ones where the Country column has the value "Norway".
Update Customers
Set City = 'Oslo'
where Country = 'Norway'

#Update the City value and the Country value.
Update Customers
set City = "Oslo", Country = "Norway"
where CustomerID = 32

DELETE

#Delete all the records from the Customers table where the Country value is 'Norway'.
Delete From Customers
where country = 'Norway'

#Delete all the records from the Customers table.
Delete From Customers

Functions

#Use the MIN function to select the record with the smallest value of the Price column.
Select min(Price)
From Products

#Use an SQL function to select the record with the highest value of the Price column.
select max(Price)
From Products

#Use the correct function to return the number of records 
#that have the Price value set to 18.
select count(*) from Products
where Price = 18

#Use an SQL function to calculate the average Price of all products.
select avg(Price)
from Product

#Use an SQL function to calculate the sum of all the Price column values
#in the Products table.
select sum(Price)
from Product

LIKE

#Select all records where the value of the City column starts with the letter "a".
Select * From Customers
Where City Like 'a%'

#Select all records where the value of the City column ends with the letter "a".
select * from Customers
Where City Like '%a'

#Select all records where the value of the City column contains the letter "a".
select * from Customers
Where City Like '%a%'

#Select all records where the value of the City column 
#starts with letter "a" and ends with the letter "b".
Select * From Customers
Where City Like 'a%b'

#Select all records where the value of the City column
# does NOT start with the letter "a".
Select * From Customers
Where City not Like 'a%'

Wildcards

#Select all records where the second letter of the City is an "a".
Select * From Customers
Where City Like '_a%'

#Select all records where the first letter of the City is an "a" or a "c" or an "s".
Select * From Customers
Where City Like '[acs]%'

#Select all records where the first letter of the City starts with anything from an "a" to an "f".
Select * From Customers
Where City Like '[a-f]%'

#Select all records where the first letter of the City is NOT an "a" or a "c" or an "f".
Select * From Customers
Where City Like '[!acf]%'

In, BETWEEN

#Use the IN operator to select all the records where Country is 
#either "Norway" or "France".
Select * From Customers
Where Country In ("Norway", "France")

#Use the IN operator to select all the records where Country is
# NOT "Norway" and NOT "France".
Select * From Customers
Where Country Not In ("Norway", "France")

#Use the BETWEEN operator to select all the records 
#where the value of the Price column is between 10 and 20.
Select * From Products
Where Price between 10 and 20

#Use the BETWEEN operator to select all the records 
#where the value of the Price column is NOT between 10 and 20.
Select * From Products
Where Price Not Between 10 and 20

#Use the BETWEEN operator to select all the records 
#where the value of the ProductName column is alphabetically between 'Geitost' and 'Pavlova'.
Select* From Products
Where ProductName Between 'Geitost' and 'Pavlova'

Alias

#When displaying the Customers table, make an ALIAS of the PostalCode column, 
#the column should be called Pno instead.
Select CustomerName, Address, PostalCode as Pno
From Customers
                                                            # as생략가능
Select CustomerName, Address, PostalCode Pno
From Customers

#When displaying the Customers table, refer to the table as Consumers instead of Customers.
Select *
From Customers as Consumers
																														# as생략가능
Select *
From Customers Consumers

Join

데이터베이스 안의 모든 테이블은 하나의 주제(Single Theme)만 가져야 한다.

Join 참고 SQL Joins Visualizer (leopard.in.ua)

# A
SELECT * FROM TableA A
LEFT JOIN TableB B ON
A.key = B.key

# B
SELECT * FROM TableA A
RIGHT JOIN TableB B ON
A.key = B.key

# A ∩ B
SELECT * FROM TableA A
INNER JOIN TableB B ON
A.key = B.key

# A - A ∩ B
SELECT * FROM TableA A
LEFT JOIN TableB B ON
A.key = B.key WHERE B.key IS NULL

# B - A ∩ B
SELECT * FROM TableA A
RIGHT JOIN TableB B ON
A.key = B.key WHERE A.key IS NULL

# A ∪ B
SELECT * FROM TableA A
FULL OUTER JOIN TableB B ON
A.key = B.key

# A ∪ B - A ∩ B
SELECT * FROM TableA A
FULL OUTER JOIN TableB B ON
A.key = B.key WHERE A.key IS NULL
OR B.key IS NULL

LEFT JOIN : SQL 쿼리문에서 좌측에 있는 쿼리를 기준으로 JOIN 실행

INNER JOIN : A테이블과 B테이블의 교집합

FULL OUTER JOIN : A테이블과 B테이블의 합집합

SELECT * FROM topic FULL OUTER JOIN author ON topic.author_id = author.aid

위아래는 같은 결과를 가져옴

SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.aid
UNION
SELECT * FROM topic RIGHT JOIN author ON topic.author_id = author.aid

#Insert the missing parts in the JOIN clause to join the two tables Orders and Customers, 
#using the CustomerID field in both tables as the relationship between the two tables.
SELECT * FROM Orders
LEFT JOIN Customers On Orders.CustomerID = Customers.CustomerID

#Choose the correct JOIN clause to select all records from the two tables where there is a match in both tables.
SELECT * FROM Orders
Inner JOIN Customers On Orders.CustomerID = Customers.CustomerID

#Choose the correct JOIN clause to select all the records from the Customers table plus all the matches in the Orders table.
SELECT * FROM Orders
RIGHT JOIN Customers On Orders.CustomerID = Customers.CustomerID

Group By

#List the number of customers in each country.
SELECT COUNT(CustomerID), Country
FROM Customer
Group by Country

#List the number of customers in each country, 
#ordered by the country with the most customers first.
select count(CustomerID), Country From Customers
Group by Country
Order by count(CustomerID) desc

Exists

#예제
#Supplers 테이블과 Products 테이블이 있을때 가격이 5미만인 Product의 Supplier name을 반환
#1 EXISTS
select SupplierName
from Suppliers
where exists
(select * from Products where Suppliers.SupplierID = Products.SupplierID and Price < 5 order by Price)

#2 LEFT JOIN
select SupplierName from Products as A left join Suppliers as B on A.SupplierID = B.SupplierID where A.Price < 5

#차이점
#EXISTS는 데이터 존재 여부만 확인하고 LEFTJOIN은 실제 데이터를 가져온다.
#따라서 결과 집합이 큰 경우 LEFT JOIN보다 더 빠른 성능이 필요할때 EXISTS를 사