SQL Basic

 

SQL Basic

최소한

 

 

---

 

1. SELECT Queries

SELECT * FROM movies;

SELECT title FROM movies;

SELECT title, director FROM movies;

 

 

2. Queries with constraints

SELECT * FROM movies where id=6;

SELECT * FROM movies 
where year BETWEEN 2000 AND 2010;

SELECT * FROM movies 
where year NOT BETWEEN 2000 AND 2010;

SELECT * FROM movies
where id < 6;

SELECT * FROM movies
where title LIKE "toy%";

SELECT * FROM movies
where director !="John Lasseter";

SELECT * FROM movies
where title LIKE "WALL-_";

 

 

3. Filtering and sorting Query results

SELECT DISTINCT director FROM movies
ORDER BY director;

SELECT * FROM movies
ORDER BY year DESC
LIMIT 4;

SELECT * FROM movies
ORDER BY title
LIMIT 5 OFFSET 5;

 

 

4. Review

SELECT * FROM north_american_cities
WHERE country = "Canada";

SELECT * FROM north_american_cities
WHERE country LIKE "United%"
ORDER BY latitude DESC;

SELECT * FROM north_american_cities
ORDER BY longitude
LIMIT 6;

SELECT * FROM north_american_cities
WHERE country = "Mexico"
ORDER BY population DESC
LIMIT 2;

SELECT * FROM north_american_cities
WHERE country LIKE "United%"
ORDER BY population DESC
LIMIT 2 OFFSET 2;

 

 

5. Join

https://preview.redd.it/dyqnzpuddxk21.png?width=960&crop=smart&auto=webp&s=24dae560ca81af335f80bf0d887268b06809ef1f

/*INNER JOIN*/
SELECT * FROM movies
INNER JOIN boxoffice
    ON movies.Id = Movie_id;
    
SELECT * FROM movies
INNER JOIN boxoffice
    ON movies.Id = Movie_id
WHERE domestic_sales<international_sales;

SELECT * FROM movies
INNER JOIN boxoffice
    ON movies.Id = Movie_id
ORDER BY rating DESC;

/*OUTER JOIN*/
SELECT DISTINCT building FROM employees;

SELECT * FROM employees
LEFT JOIN Buildings 
    ON employees.Building = Buildings.Building_name;
    
SELECT DISTINCT building_name, role FROM buildings 
LEFT JOIN employees
  	ON building_name = building;

 

 

6. NULL

SELECT name, role FROM employees
WHERE building IS NULL;

SELECT DISTINCT building_name
FROM buildings 
  LEFT JOIN employees
    ON building_name = building
WHERE role IS NULL;

 

 

7. Queries

/*with expression*/
SELECT title, (domestic_sales + international_sales) / 1000000 AS millions
FROM movies
  JOIN boxoffice
    ON movies.id = boxoffice.movie_id;
    
SELECT title, rating * 10 AS rating_percent
FROM movies
  JOIN boxoffice
    ON movies.id = boxoffice.movie_id;
    
SELECT title, year
FROM movies
WHERE year % 2 = 0;

/*with aggregates*/
SELECT MAX(years_employed) as Max_years_employed
FROM employees;

SELECT role, AVG(years_employed) as Average_years_employed
FROM employees
GROUP BY role;

SELECT building, SUM(years_employed) as Sum_years_employed
FROM employees
GROUP BY building;

SELECT building, SUM(years_employed) as Sum_years_employed
FROM employees
GROUP BY building;

SELECT role, COUNT(*) as Number_of_artists
FROM employees
WHERE role = "Artist";

SELECT role, SUM(years_employed)
FROM employees
GROUP BY role
HAVING role = "Engineer"; /*Having은 그룹 컨디션*/

 

 

8. Order of execution of a Query

1. FROM and JOINs
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT / OFFSET

 

9. Handling Rows

/*Inserting rows*/
INSERT INTO movies 
VALUES (4, "Toy Story 4", "El Directore", 2015, 90);

/*Updating rows*/
UPDATE movies
SET year = 1999
WHERE id = 3;

UPDATE movies
SET title = "Toy Story 3", director = "Lee Unkrich"
WHERE id = 11;


/*Deleting rows*/
DELETE FROM movies
where year < 2005;

 

 

10.  Table

/*Create a new table named Database with the following columns*/
CREATE TABLE Database (
    Name TEXT,
    Version FLOAT,
    Download_count INTEGER
);

/*Add new columns*/
ALTER TABLE Movies
  ADD COLUMN Aspect_ratio FLOAT DEFAULT 2.39;
  
ALTER TABLE Movies
  ADD COLUMN Language TEXT DEFAULT "English";
  
/*Drop the table*/
DROP TABLE movies;

 

10.  정리

• Select
• Where
• And, Or, Not
• Order By
• Insert Into
• Null Values
• Update
• Delete
• Count
• Like
• Wildcards
• Aliases
• Joins
  • Inner Join
  • Left Join
  • Right Join
• Group By

• SQL Create DB
• SQL Drop DB
• SQL Create Table
• SQL Drop Table
• SQL Alter Table
• SQL Not Null
• SQL Unique
• SQL Primary Key
• SQL Foreign Key
• SQL Default
• SQL Auto Increment
• SQL Dates

 

 

sqlbolt.com/lesson/introduction

SQLBolt - Learn SQL - Introduction to SQL