Intro of SQL | #1 Basic Keyword

이지수·2022년 2월 8일
0

SQL

목록 보기
1/3
post-thumbnail

Meaning of Keyword
: Reserved words that significance in SQL
: Normally write in Capitals for distinguising with Query

This post is based on PostgreSQL syntax

1. Basic Keyword

KEYWORD - BASICDESCRIPTION
SELECTList of columns or expressions that must be returned
ASAdjust column name as following words
FROMTable to get the data
WHEREFilter based on both text & numeric values
Always show up after 'FROM' Must use single quotes with 'WHERE'
WHERE cluase only filter by specifying the exact text
in PostgreSQL must use single quotes ; with WHERE
NOT IN ( )Allows specifying multiple values in WHERE
Always with WHERE
LIMITExtract Limited value
DISTINCTExtract Unique value
ANDCombining multiple conditions
ORSort multiple conditions
BETWEENInclusive, meaning the beginning and end values are included
Always with AND
INTOBuild New table
IS NULLNull Check (IS NOT NULL)
With WHERE query
SELECT COUNT(DISTINCT due_date)
FROM people;


SELECT title
FROM films
WHERE 
	year IN (2015, 2020)
	AND (certification = 'A' OR certification = 'B');
-- be sure to enclose the individual clauses in parentheses


SELECT human_name AS name
FROM people
WHERE due_date IS NOT NULL;


-- SELECT every columns data
SELECT *
FROM people;


-- USING INTO
SELECT country_code, size,
  CASE WHEN size > 50000000
            THEN 'large'
       WHEN size > 1000000
            THEN 'medium'
       ELSE 'small' END
       AS popsize_group
INTO pop_plus       
FROM populations
WHERE year = 2015;



2. BY Keyword

KEYWORD - BY DESCRIPTION
ORDER BY DESCBY default ORDER BY will sort in ascending order
If want descending order put in DESC
ORDER BY clause at the end of the query
GROUP BYGrouping by following words
GROUP BY always goes after the FROM clause, not FROM
HAVINGAggregate functions can't be used in WHERE clauses
Always use with GROUP BY

SELECT birthdate, name
FROM people
ORDER BY birthdate, name

SELECT release_year
FROM films
GROUP BY release_year
WHERE COUNT(title) > 10
-- Above code occurring error

SELECT release_year
FROM films
GROUP BY release_year
HAVING COUNT(title) > 10
-- This works


3. Aggregate Function

Aggregate Function DESCRIPTION
COUNT(*)Num of rows in one or more columns
AVGCalculate Average 'SELECT' value
MAXExtract Maximum 'SELECT' value
MINExtract Minimum 'SELECT' value



4. LIKE Keyword

KEYWORD - LIKE DESCRIPTION
NOT LIKEWHERE clause only filter by specifying the exact text
LIKE operator can be used in a WHERE clause
HAVINGAggregate functions can't be used in WHERE clauses
%Will match zero, one, or many characters in text
Use with LIKE
_Will match single character
Use with LIKE
SELECT company_name
FROM companies
WHERE company_name LIKE 'SAMSUNG%';
-- Will extract SAMSUNG, SUMSUNG1, SAMSUNG_Electronics and on..

SELECT company_name
FROM companies
WHERE company_name LIKE 'SAMS_NG';
-- _ : will match single character : 'SAMSUNG', 'SAMSONG', 'SAMSING'

SELECT human_name
FROM people
WHERE human_name LIKE'_r%
-- human_names have 'r' as the second letter

5. Filter

****DESCRIPTION
`.`
=equal
<>not equal
<less than
>greater than
<=less than or equal to
>=greater than or equal to


6. Arithmetic

Arithmetic DESCRIPTION
/devide
SELECT (4/3)
-- Conclusion 1 
-- SQL assuumes that if divide an integer by an integer, 
-- user want to get an integer back

SELECT (4.0 / 3.0) AS result


SELECT p1.country_code,
       p1.size AS size2010, 
       p2.size AS size2015,
       (p2.size - p1.size) / p1.size * 100.0 AS growth_perc
FROM populations AS p1
  INNER JOIN populations AS p2
    ON p1.country_code = p2.country_code
        AND p1.year = p2.year - 5;


7. ETC

FormatDESCRIPTION
ISO Date1927-11-11
/* */multi line comments
--single line comment

0개의 댓글