[AI SCHOOL 5기] SQL 프로그래밍 실습 - SQL CRUD

1. SELECT

SELECT 10 / 5, 2 * 4;

SELECT trackid, name FROM tracks;

SELECT * FROM tracks;

---

2. INSERT

INSERT INTO artists (name) VALUES('Bud Powell');

script = """
INSERT INTO artists (name) VALUES ("?");
"""

data = [
    ("Buddy Rich"),
    ("Candido"),
    ("Charlie Byrd")
]

cur.executemany(script, data)

SELECT
    ArtistId,
    Name
FROM
    Artists
ORDER BY
    ArtistId DESC;

---

3. UPDATE

UPDATE employees SET lastname = 'Smith' WHERE employeeid = 3;

UPDATE employees
SET city = 'Toronto', 
    state = 'ON',
    postalcode = 'M5P 2N7'
WHERE
    employeeid = 4;

UPDATE employees
SET email = UPPER(firstname || "." || lastname || "@corp.co.kr");

---

4. Sorting

SELECT 
    TrackId, 
    Name, 
    Composer 
FROM 
   tracks
ORDER BY 
   Composer;

• NULL Data인 None은 SQLite3에서 가장 작은 값으로 인식

---

5. Filtering

DISTINCT

SELECT DISTINCT city FROM customers;

• NULL을 포함한 중복값을 하나만 남기고 제외

SELECT DISTINCT city, country FROM customers;

• 2개 열의 값이 모두 동일한 행들을 제외

WHERE

SELECT
   name,
   milliseconds,
   bytes,
   albumid
FROM
   tracks
WHERE
   (albumid = 10) AND (milliseconds > 250000);

WHERE & LIKE

SELECT
    trackid,
    name
FROM
    tracks
WHERE
    name LIKE 'Wild%';

WHERE & IN

SELECT
    TrackId,
    Name,
    MediaTypeId
FROM
    Tracks
WHERE
    MediaTypeId IN (1, 2)

WHERE & LIMIT/OFFSET

SELECT
    trackId,
    name
FROM
    tracks
LIMIT 10 OFFSET 7;

• LIMIT: 불러오는 값의 수
• OFFSET: OFFSET에 해당하는 수만큼 떼어내 그 이후의 데이터 불러옴

WHERE & BETWEEN

SELECT
    InvoiceId,
    BillingAddress,
    Total
FROM
    invoices
WHERE
    Total BETWEEN 14.91 AND 18.86
ORDER BY
    Total; 

WHERE & IS NULL

SELECT
    Name, 
    Composer
FROM
    tracks
WHERE
    Composer IS NULL
ORDER BY 
    Name;