- Source: https://seeq.atlassian.net/wiki/spaces/KB/pages/443088907/SQL+Column+Names+and+Case+Sensitivity
- Oracle stores unquoted column names in uppercase.
- The returned column name has the case that was used in the
SELECT
statement.
- Be no longer than 30 characters.
- Begin with an alphabetical character.
- Contain only alphabetical characters, numbers, or one of the following special characters:
#
,$
,_
CHAR(n)
: A fixed length string.- Blank-Padded Comparison Semantics: If the two values have different lengths, then Oracle first adds blanks to the end of the shorter one so their lengths are equal. Oracle then compares the values character by character up to the first character that differs. The value with the greater character in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks.
VARCHAR(n)
: A variable length string.- Nonpadded Comparison Semantics: Oracle compares two values character by character up to the first character that differs. The value with the greater character in that position is considered greater. If two values of different length are identical up to the end of the shorter one, then the longer value is considered greater. If two values of equal length have no differing characters, then the values are considered equal.
VARCHAR2(n)
- There is no difference between
VAHRCHAR
andVARCHAR2
in Oracle. However, it is advised not to useVARCHAR
for storing data as it is reserved for future use for storing some other type of variable. Hence, always useVARCHAR2
in place ofVARCHAR
.
- There is no difference between
BOOL
: Zero is considered as false, nonzero values are considered as true.INT
FLOAT(n)
- A later date is considered greater than an earlier one.
DATE
: A date. Format:YYYY-MM-DD
.TIME
: A time. Format:hh:mm:ss
.DATETIME
: A date and time combination. Format:YYYY-MM-DD hh:mm:ss
.
FROM
->WHERE
->GROUP BY
->HAVING
->SELECT
->ORDER BY
->LIMIT
,TOP
orFETCH
Column <<Column>> is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
- Any calculation performed on the NULL value returns NULL.
- The result of concatenating two character strings is another character string.
- The
BETWEEN AND
operator selects values within a given range. The values can be numbers, text, or dates. - The
BETWEEN AND
operator is inclusive: begin and end values are included. - 앞에 오는 숫자가 뒤에 오는 숫자보다 작아야 합니다.
- Parentheses -> Arithmetic Operators(
*
,/
->+
,-
) -> Concatenation Operator(||
) -> Comparison Operators(=
,!=
,<
,<=
,>
,>=
) ->IS
(IS NULL
,IS NOT NULL
,IS EMPTY
,IS NOT EMPTY
),LIKE
,IN()
->BETWEEN
-> Logical Operatiors(NOT
->AND
->OR
)
- Pseudocolumns:
table_catalog
,table_schema
,table_name
,table_type
SELECT *
FROM INFORMATION_SCHEMA.TABLES;
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
[WHERE TABLE_NAME = <<Table>>];
SELCT *
FROM INFORMATION_SCHEMA.TALBE_CONTSTRAINTS
[WHERE TABLE_NAME = <<Table>>];
- Oracle provides you with the DUAL table which is a special table that belongs to the schema of the user SYS but it is accessible to all users.
- The
DUAL
table has one column namedDUMMY
whose data type isVARCHAR2()
and contains one row with a valueX
. - By using the
DUAL
table, you can execute queries that contain functions that do not involve any table - MS SQL Server doesn't need
FROM
clause.
- Oracle database implicitly commits the current transaction before and after every DDL statement.
- MS SQL Server는 Auto commit을 수행하지 않습니다.
CREATE
,ALTER
,DROP
,TRUNCATE
,RENAME
NOT NULL
: Ensures that a column cannot have NULL value.DEFAULT
: Provides a default value for a column when none is specified.UNIQUE
: Ensures that all values in a column are different.CHECK
: Ensures that all the values in a column satisfies certain conditions.PRIMARY KEY
: Uniquely identifies each record in a database table.FOREIGN KEY REFERENCES
: Uniquely identifies a record in any of the given database table.
CREATE TABLE <<Table>>(
<<Column1>> <<Data Type1>> [<<Constraint1>>],
<<Column2>> <<Data Type2>> [<<Constraint2>>],
...
)
- For defining a primary key on multiple columns, the statement should be like;
CREATE TABLE <<Table>>( PRIMARY KEY(<<Column1>>, <<Column2>>, ...), ...)
- Source: https://www.dba-oracle.com/t_foreign_key_on_delete_cascade.htm
- When you create a foreign key constraint, Oracle default to
ON DELETE RESTRICT
to ensure that a parent rows cannot be deleted while a child row still exists. However, you can also implementON DELETE CASCADE
to delete all child rows when a parent row is deleted.
- Source: https://www.programmerinterview.com/database-sql/database-roles/
- A database role is a collection of any number of permissions/privileges that can be assigned to one or more users. A database role is also given a name for that collection of privileges.
- The majority of today’s RDBMS’s come with predefined roles that can be assigned to any user. But, a database user can also create his/her own role if he or she has the
CREATE ROLE
privilege.
CREATE INDEX <<Index_name>>
ON <<Table>>(<<Column1>>, <<Column2>>, ...);
ALTER TABLE <<Table>>
ADD <<Column>> <<Data Type>> [<<Constraint>>];
ALTER TABLE MODIFY
(Oracle), ALTER TABLE ALTER COLUMN
(MS SQL Server), ALTER TABLE MODIFY COLUMN
(MySQL)
- Change the data type of a column in a table.
ALTER TABLE <<Table>>
ALTER COLUMN | MODIFY | MODIFY COLUMN <<Column>> <<Data Type>> [<<Constraint>>];
ALTER TABLE <<Table>>
ALTER COLUMN | MODIFY | MODIFY COLUMN(
<<Column1>> <<Data Type1>> [<<Constraint1>>],
<<Column2>> <<Data TYpe2>> [<<Constraint2>>],
...);
ALTER TABLE <<Table>>
DROP COLUMN <<Column>>;
ALTER TABLE <<Table Before>>
RENAME TO <<Table After>>
ALTER TABLE <<Table>>
RENAME COLUMN <<Column Before>> TO <<Column After>>
- With the help of
DROP
command we can drop (delete) the whole structure in one go. The existence of the whole table is finished. - The
DROP TABLE
statement does not result in space being released back to the tablespace for use by other objects.
- It removes all the rows exist in the table.
- It returns the freed space to the tablespace.
- It is comparatively faster than
DELETE
command.
RENAME TABLE old_table1 TO new_table1, old_table2 TO new_table2, old_table3 TO new_table3;
GRANT <<Privilege1>>, <<Privilege2>>, ...
ON <<Table>>
TO <<User>>
<<Privilege>>
:CONNECT
,RESOURCE
,DBA
, ...CONNECT
: DB 접속 권한.RESOURCE
: 테이블 등 생성 권한.
- Source: https://www.geeksforgeeks.org/difference-between-commit-and-rollback-in-sql/
COMMIT
is used to permanently save the changes done in the transaction in tables/databases. The database cannot regain its previous state after the execution of it.COMMIT
되지 않은 데이터는 나 자신은 볼 수 있지만 다른 사용자는 볼 수 없으며 변경할 수도 없습니다.
SAVEPOINT <<Savepoint Name>>
- Use the
SAVEPOINT
statement to identify a point in a transaction to which you can later roll back. - If you give two savepoints the same name, the earlier savepoint is erased.
ROLLBACK
is used to undo the transactions that have not been saved in database. The command is only be used to undo changes since the lastCOMMIT
.- Rolling back to a savepoint erases any savepoints marked after that savepoint. However, the savepoint to which you rollback is not erased.
ROLLBACK TO <<Savepoint Name>>
INSERT
(Create),SELECT
(Read),UPDATE
(Update),DELETE
(Delete)MERGE
(Oracle)
INSERT INTO <<Table>>(<<Column1>>, <<Column2>>, ...) VALUES(<<Value1>>, <<Value2>>, ...);
- If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query.
INSERT INTO <<Target Table>>
SELECT
FROM <<Source Table>>
- Source: https://www.w3schools.com/sql/sql_insert_into_select.asp
- The
INSERT INTO SELECT FROM
statement copies data from<<Source Table>>
and inserts it into<<Target Table>>
. - The
INSERT INTO SELECT FROM
statement requires that the data types in<<Source Table>>
and<<Tartget Table>>
match. - The existing records in the
<<Target Table>>
are unaffected.
- When using an unconditional
INSERT ALL
statement, each row produced by the driving query results in a new row in each of the tables listed in theINTO
clauses.
INSERT ALL
INTO pivot_dest(id, day, val) VALUES(id, 'mon', mon_val)
INTO pivot_dest(id, day, val) VALUES(id, 'tue', tue_val)
INTO pivot_dest(id, day, val) VALUES(id, 'wed', wed_val)
INTO pivot_dest(id, day, val) VALUES(id, 'thu', thu_val)
INTO pivot_dest(id, day, val) VALUES(id, 'fri', fri_val)
SELECT *
FROM pivot_source;
INSERT FIRST
WHEN id <= 3 THEN INTO dest_tab1(id, description) VALUES(id, description)
WHEN id <= 5 THEN INTO dest_tab2(id, description) VALUES(id, description)
ELSE INTO dest_tab3(id, description) VALUES(id, description)
SELECT id, description
FROM source_tab;
- Using
INSERT FIRST
makes the multitable insert work like aCASE
expression, so the conditions are tested until the first match is found, and no further conditions are tested.
SELECT TOP FROM
(MS SQL Server), SELECT FROM LIMIT
(MySQL), SELECT FROM FETCH FIRST ROWS ONLY
(Oracle)
SELECT TOP 1 months*salary, COUNT(employee_id)
FROM employee
GROUP BY months*salary
ORDER BY months*salary DESC;
SELECT *
FROM Customers
FETCH FIRST 3 ROWS ONLY;
ORDER BY
가 정렬을 하는 시점은 모든 실행이 끝난 후 데이터를 출력하기 바로 직전입니다.ORDER BY
다음에 여러 개의 컬럼이 오면 앞에서부터 순서대로 정렬 적용합니다.ORDER BY <<Number>>
<Number>>
stands for the column based on the number of columns defined in theSELECT
clause.
- Oracle: NULL values are larger than any non-NULL values.
- MS SQL Server: NULL values are smaller than any non-NULL values.
- For each row returned by a query, the
ROWNUM
pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has aROWNUM
of 1, the second has 2, and so on.
SELECT ROWNUM, pr.product_name, pr.standard_cost
FROM products;
- Execution order:
ROWNUM
->ORDER BY
HAVING
은GROUP BY
문의 조건절이므로GROUP BY
함수 없이 사용되면SELECT
문에서 어떤 행도 반환하지 않습니다.
- Source: https://www.w3schools.com/sql/sql_exists.asp
- The
EXISTS
operator is used to test for the existence of any record in a subquery. - The
EXISTS
operator returns TRUE if the subquery returns one or more records.
SELECT *
FROM customers
WHERE EXISTS(
SELECT *
FROM orders
WHERE orders.c_id = customers.c_id);
- The above statement returns TRUE and lists the customers who have ever ordered.
UPDATE emp
SET ename = 'jojo'
WHERE emp_no = 100;
- Here we can use the
ROLLBACK
command to restore the tuple.
DELETE [FROM] emp
WHERE emp_no = 100;
- Source: https://www.oracletutorial.com/oracle-basics/oracle-merge/
- The
MERGE
statement selects data from one or more source tables and updates or inserts it into a target table. TheMERGE
statement allows you to specify a condition to determine whether to update data from or insert data into the target table. - Because the
MERGE
is a deterministic statement, you cannot update the same row of the target table multiple times in the sameMERGE
statement.
MERGE INTO USING ON WHEN MATCHED THEN UPDATE SET WHERE [DELETE WHERE] WHEN NOT MATCHED THEN INSERT () VALUES() WHERE
MERGE INTO <<Target Table>>
USING <<Source Table>>
ON <<Search Condition>>
WHEN MATCHED THEN
UPDATE SET <<Column1>> = <<Value1>>, <<Column2>> = <<Value2>>, ...
WHERE <<Update Condition>>
[DELETE WHERE <<Delete Condition>>]
WHEN NOT MATCHED THEN
INSERT (<<Column1>>, <<Column2>>, ...)
VALUE(<<Value1>>, <<Value2>>, ...)
WHERE <<Insert Condition>>;
<<Search Condition>>
- For each row in the target table, Oracle evaluates the
<<Search Condition>>
- If the result is true, then Oracle updates the row with the corresponding data from the source table.
- In case the result is false for any rows, then Oracle inserts the corresponding row from the source table into the target table.
- For each row in the target table, Oracle evaluates the
<<Delete Condition>>
- You can add an optional
DELETE WHERE
clause to theMATCHED
clause to clean up after a merge operation. TheDELETE
clause deletes only the rows in the target table that match bothON
andDELETE WHERE
clauses. - Source: https://oracle-base.com/articles/10g/merge-enhancements-10g
- Only those rows in the destination table that match both the ON clause and the
DELETE WHERE
are deleted. If you add aWHERE
clause to the update in the matched clause, we can think of this as additional match criteria for the delete, as only rows that are touched by the update are available for theDELETE
clause to remove. Depending on which table theDELETE WHERE
references, it can target the rows prior or post update.
- You can add an optional
START WITH
specifies the root row(s) of the hierarchy.START WITH
다음에 조건을 명시할 수도 있다.CONNECT BY
specifies the relationship between parent rows and child rows of the hierarchy. It always joins a table to itself, not to another table.PRIOR
should occur exactly once in eachCONNECT BY
expression.PRIOR
can occur on either the left-hand side or the right-hand side of the expression, but not on both.- If the query contains a
WHERE
clause without a join, then Oracle eliminates all rows from the hierarchy that do not satisfy the condition of theWHERE
clause. Oracle evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition. - For each row returned by a hierarchical query, the
LEVEL
pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on - The
CONNECT_BY_ISLEAF
pseudocolumn returns 1 if the current row is a leaf of the tree defined by theCONNECT BY
condition, 0 otherwise.
CONNECT_BY_ROOT last_name AS boss
- The
CONNECT_BY_ROOT
pseudocolumn returns the root.
SELECT last_name, employee_id, manager_id, LEVEL
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;
- If you want to order rows of siblings of the same parent, then use the
ORDER SIBLINGS BY
clause.
SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle",
LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE level <= 3 AND department_id = 80
START WITH last_name = 'King'
CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4;
- The
NOCYCLE
parameter in theCONNECT BY
condition causes Oracle to return the rows in spite of the loop. - The
CONNECT_BY_ISCYCLE
pseudocolumn shows you which rows contain the cycle.
SYS_CONNECT_BY_PATH(<<Column>>, <<Character>>)
- The
SYS_CONNECT_BY_PATH
function returns the path of a<<Column>>
value from root to node, with column values separated by<<Character>>
for each row returned byCONNECT BY
condition.
- Source: https://en.wikipedia.org/wiki/Null_(SQL)
- A null value indicates a lack of a value, which is not the same thing as a value of zero. For example, consider the question "How many books does Adam own?" The answer may be "zero" (we know that he owns none) or "null" (we do not know how many he owns). In a database table, the column reporting this answer would start out with no value (marked by NULL), and it would not be updated with the value "zero" until we have ascertained that Adam owns no books.
- SQL null is a state, not a value.
- NULL means that the value is unknown.
NVL(<<Column to Test>>, <<Value if NULL>>)
- Return
<<Column to Test>>
if not NULL otherwise return<<Value if NULL>>
if NULL.
NVL2(<<Column to Test>>, <<Value if not NULL>>, <<Value if NULL>>)
- Return
<<Value if not NULL>>
if <> is not NULL otherwise return<<Value if NULL>>
if<<Column to Test>>
is NULL.
COALESCE(<<Expression1>>, <<Expression2>>, ...)
- Return the first non-NULL expression in the list. If all expressions evaluate to null, return NULL
NULLIF(<<Expression1>>, <<Expression2>>)
- Return NULL if
<<Expression1>>
and<<Expression2>>
are equal and return<<Expression1>>
if<<Expression1>>
and<<Expression2>>
are not equal.
SELECT animal_type, IFNULL(name, "No name"), sex_upon_intake
FROM animal_ins
ORDER BY animal_id
- Source: https://www.w3schools.com/sql/sql_null_values.asp
- It is not possible to test for NULL values with comparison operators(
=
,!=
,<
,<=
,>
,>=
). NULL IS NULL
is TRUE andNULL IS NOT NULL
is FALSE.- Because NULL represents a lack of data, a NULL cannot be equal or unequal to any value or to another NULL. However, Oracle considers two NULLs to be equal when evaluating a
DECODE()
function.(SoNULL IN(NULL, ...)
is FALSE)
- Search for conditions sequentially.
SELECT CASE WHEN (a >= b + c OR b >= c + a OR c >= a + b) THEN "Not A Triangle" WHEN (a = b AND b = c) THEN "Equilateral" WHEN (a = b OR b = c OR c = a) THEN "Isosceles" ELSE "Scalene" END
FROM triangles;
DECODE(<<Expression>>, <<Value to Be Compared1>>, <<Value If Equal to1>>, <<Value to Be Compared2>>, <<Value If Equal to2>>, ..., <<Default>>)
<<Default>>
: (Optional) It is used to specify the default value to be returned if no matches are found.
- Return TRUE if any of the subquery values meet the condition.
SELECT name
FROM usertbl
WHERE height > ANY(
SELECT height
FROM usertbl
WHERE addr = '서울');
- Return TRUE if all of the subquery values meet the condition.
- Used with
SELECT
,WHERE
andHAVING
statements.
SELECT name, addr
FROM usertbl
WHERE addr IN("서울", "경기", "충청");
- Returns the smallest(largest) integer value that is bigger(smaller) than or equal to a number.
ROUND(<<Target Number>>, [<<Decimal Place>>])
- If no <> is defined (Oracle) or equal to zero (MS SQL Server), then <> is rounded to zero places.
- If the <> specified is negative, then <> is rounded off to the left of the decimal point.
- If the <> specified is positive, then <> is rounded up to <> decimal place(s).
SELECT FORMAT(123456.123456, 4);
- 출력할 소수점 이하 자릿수 지정.
SELECT name, height
FROM sqlDB.usertbl
WHERE height BETWEEN 180 AND 183;
SELECT CAST(CEILING(AVG(CAST(salary AS FLOAT)) - AVG(CAST(REPLACE(CAST(salary AS FLOAT), "0", "") AS FLOAT))) AS INT)
FROM employees;
SELECT DISTINCT city
FROM station
WHERE RIGHT(city, 1) IN ("a", "e", "i", "o", "u");
SUBSTR(<<String>>, <<Start Position>>, [<<Length>>])
<<String>>
: The first position in the<<String>>
is always 1, the last -1.<<Length>>
: If this parameter is omitted, theSUBSTR
function will return the entire<<String>>
.
UPPER('eabc')
- The
LPAD()
function left-pads a string with another string, to a certain length.
LPAD(<<Original String>>, <<Target Length>>, [<<String to Pad>>])
<<Target Length>>
: The length of the string after it has been left-padded. Note that if the<<Target Length>>
is less than the length of the<<Original String>>
, thenLPAD()
function will shorten down the<<Original String>>
to the<<Target Length>>
without doing any padding.<<String to Pad>>
is equal to' '
if omitted.
TRIM(' tech ')
TRIM(' ' FROM ' tech ')
TRIM(LEADING '0' FROM '000123')
TRIM(TRAILING '1' FROM 'Tech1')
TRIM(BOTH '1' FROM '123Tech111')
SELECT CONCAT(CASE hdc_mbr.mbr_mst.mbr_sex WHEN '0' THEN '남자' WHEN '`' THEN '여자' END, '/', hdc_mbr.mbr_mst.mbr_birth)
CONCAT_WS(<<Separator>>, <<Expression1>>, <<Expression2>>, ...)
- The
CONCAT_WS()
function adds<<Expression1>>
,<<Expression2>>
, ... together with a<<Separator>>
.
SELECT DISTINCT city
FROM station
WHERE city LIKE "%a" OR city LIke "%e" OR city LIKE "%i" OR city LIKE "%o" OR city LIKE "%u";
- A wildcard character is used to substitute one or more characters in a string.
%
: Represents zero, one, or multiple characters._
: Represents one, single character.-
: Represents any single character within the specified range
SELECT *
FROM tbl
WHERE name LIKE '%@_%` ESCAPE '@';
- Used to convert a number or date to a string.
TO_CHAR(mbrmst.mbr_leaving_expected_date, "YYYY/MM/DD") AS mbr_leaving_expected_dt
SELECT HOUR(datetime) AS hour, COUNT(*)
FROM animal_outs
GROUP BY hour
HAVING hour BETWEEN 9 AND 19
ORDER BY hour;
- HOUR()는 일반조건이므로 (COUNT()와 달리) HAIVNG과 함께 쓸 수 없다.
## `SYSDATE` (Oracle), `CURDATE()` (MySQL), `GETDATE()` (MS SQL Server)
## `EXTRACT([YEAR | MONTH | DAY | HOUR | MINUTE | SECOND] FROM)`
- Returns a numeric value according to the parameter.
```sql
EXTRACT(MONTH FROM order_date)
CALENDAR_YEAR()
, CALENDAR_QUARTER()
, CALENDAR_MONTH()
, DAY_IN_MONTH()
, HOUR_IN_DAY()
(Salesforce Object Query Language)
DATEADD(<<Interval>>, <<Number>>, <<Date>>)
- <>: Required. The number of interval to add to <>.
- <>: Required. The date that will be modified.
DATEDIFF(YEAR, 0, GETDATE())
0
means the default date of1900-01-01 00:00:00.000
LAST_YEAR
, THIS_YEAR
, NEXT_YEAR
, LAST_MONTH
, THIS_MONTH
, NEXT_MONTH
, YESTERDAY
, TODAY
, TOMORROW
(Salesforce Object Query Language)
CAST(COUNT(occupation) AS CHAR)
TO_CHAR(1340.64, '9999.9')
TO_CHAR(sysdate, 'Month DD, YYYY')
- Source: https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/window-functions-usage.html
- Window functions are aggregates based on a set of rows, similar to an aggregate function like a
GROUP BY
, but in this case this aggregation of rows moves or slides across a number of rows so we have a sort of sliding window, thus the name window functions. - Window operations do not collapse groups of query rows to a single output row. Instead, they produce a result for each row.
GROUP BY
와 함께 사용할 수 없습니다.
- An empty
OVER
clause treats the entire set of query rows as a single partition. The window function thus produces a global sum, but does so for each row.
OVER(PARTITION BY)
clause partitions rows by a column, producing a sum per partition. The function produces this sum for each partition row.
- An
ORDER BY
clause indicates how to sort rows in each partition. IfORDER BY
is omitted, partition rows are unordered. - An
ORDER BY
in a window definition applies within individual partitions. To sort the result set as a whole, include anORDER BY
at the query top level.
OVER([PARTITION BY] [ORDER BY] [ROWS BETWEEN AND])
, OVER([PARTITION BY] [ORDER BY] [RANGE BETWEEN AND])
- Source: https://learnsql.com/blog/range-clause/
- The
RANGE
and theROW
clauses have the same purpose: to specify the starting and ending points within the partition, with the goal of limiting rows. However, each clause does it differently. TheROW
clause does it by specifying a fixed number of rows that precede or follow the current row. TheRANGE
clause, on the other hand, limits the rows logically; it specifies the range of values in relation to the value of the current row. - The default window frame without the
ORDER BY
is the whole partition. But when you use theORDER BY
, the default window frame isRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
(=UNBOUNDED PRECEDING
). BETWEEN <<Start Point>> AND <<End Point>>
<<Start Point>>
:UNBOUNDED PRECEDING
,n PRECEDING
,CURRENT ROW
<<End Point>>
:UNBOUNDED FOLLOWING
,m FOLLOWING
,CURRENT ROW
<<Start Point>>
- In this case,
<<End Point>>
is same asCURRENT ROW
(<<End Point>>
is omitted)
- In this case,
SELECT month, SUM(tot_sales) AS monthly_sales,
AVG(SUM(tot_sales)) OVER(ORDER BY month RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS rolling_avg
FROM orders
- NULL values are ignored.
COUNT(*)
,COUNT(1)
,COUNT(2)
, ...: Return the number of rows in the table.COUNT(<<Column>>)
: Return the number of non-NULL values in the column.COUNT(DISTINCT <<Column>>)
: Return the number of distinct non-NULL values in the column.
- Source: https://codingsight.com/similarities-and-differences-among-rank-dense_rank-and-row_number-functions/, https://www.sqlshack.com/overview-of-sql-rank-functions/
- If there is a tie between N previous records for the value in the
ORDER BY
column, theRANK()
function skips the next N-1 positions before incrementing the counter.(e.g., 1, 2, 2, 4, 4, 4, 7)
SELECT emp_no, emp_nm, sal,
RANK() OVER(ORDER BY salary DESC) ranking
FROM employee;
DENSE_RANK()
function does not skip any ranks if there is a tie between the ranks of the preceding records.(e.g., 1, 2, 2, 3, 3, 3, 4)
- Give a unique sequential number for each row in the specified data. It gives the rank one for the first row and then increments the value by one for each row. We get different ranks for the row having similar values as well.(e.g., 1, 2, 3, 4, 5, 6, 7)
ROW_NUMBER()
을 사용하면 Inline view를 사용하지 않고도 어떤 값의 순서대로 순번을 매길 수 있습니다.
- Source: https://www.sqltutorial.org/sql-window-functions/sql-ntile/
NTILE()
is a window function that allows you to break the result set into a specified number of approximately equal groups, or buckets. It assigns each group a bucket number starting from one. For each row in a group, theNTILE()
function assigns a bucket number representing the group to which the row belongs.- The
ORDER BY
clause specifies the order of rows in each partition to which theNTILE()
is applied. - Notice that if the number of rows is not divisible by buckets, the
NTILE()
function results in groups of two sizes with the difference by one. The larger groups always come before the smaller group in the order specified by theORDER BY
clause.
- The
LAG
function is used to access data from a previous row. - The
LEAD
function is used to return data from rows further down the result set.
RESPECT NULLS | IGNORE NULLS
: It is an optional parameter which is used to specify whether to include or ignore the NULL values in the calculation. The default value isRESPECT NULLS
.
SELECT DISTINCT FIRST_VALUE(marks)
OVER(ORDER BY marks DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS `Highest`
FROM students;
GROUP BY
treats NULL as valid values.- All NULL values are grouped into one value or bucket.
GROUP BY ROLLUP(<<Column1>>, <<Column2>>, ...)
is same asGROUP BY GROUPING SETS(<<Column1>>, (<<Column1>>, <<Column2>>), ..., ())
- 계층 구조로 계층 간 정렬이 가능합니다.
- Generate subtotals for all combinations of the dimensions specified.
GROUP BY CUBE(<<Column1>>, <<Column2>>, ...)
is same asGROUP BY GROUPING SETS(<<Column1>>, <<Column2>>, ..., (<<Column1>>, <<Column2>>, ...), ())
GROUP BY GROUPING SETS((<<Column1>>, <<Column2>>, ...))
GROUP BY <<Column1>>, <<Column2>>, ...
과 동일합니다.
GROUP BY GROUPING SETS(())
- 전체에 대한 집계.
- 인자가 여러 개인 경우 위 3가지 경우의 각 결과를
UNION ALL
한 것과 같음.
- The
UNION
operator eliminates duplicate selected rows. The sort is implicit in order to remove duplicates since.
- The
INTERSECT
operator returns only those rows returned by both queries. - The
INTERSECT
operator removes duplicate rows from the final result set. - The
INTERSECT ALL
operator does not remove duplicate rows from the final result set, but if a row appears X times in the first query and Y times in the second, it will appear min(X, Y) times in the result set.
- Return all rows in the first
SELECT
statement that are not returned by the secondSELECT
statement. - The
MINUS
(EXCEPT
) operator removes duplicate rows from the final result set. - The
EXCEPT ALL
operator does not remove duplicates, but if a row appears X times in the first query and Y times in the second, it will appear max(X - Y, 0) times in the result set.
SELECT 반정보, 과목, 점수
FROM dbo.성적 UNPIVOT (점수 FOR 과목 IN(국어, 수학, 영어)) AS UNPVT
PIVOT
transforms rows to columns.UNPIVOT
transforms columns to rows.
FULL OUTER JOIN
is same asLEFT OUTER JOIN UNION ALL RIGHT OUTER JOIN
.
- In Mathematics, given two sets
A
andB
, the Cartesian product ofAxB
is the set of all ordered pair(a, b)
, whicha
belongs toA
andb
belongs toB
. - Join key가 없을 때 발생합니다.
- The
CROSS JOIN
produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if noWHERE
clause is used along withCROSS JOIN
.This kind of result is called as Cartesian product. - If
WHERE
clause is used withCROSS JOIN
, it functions like anINNER JOIN
.
SELECT foods.item_name,foods.item_unit, company.company_name,company.company_city
FROM foods CROSS JOIN company;
- A
NATURAL JOIN
is a join operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables. <<Table>>.<<Column>>
과 같은 형태로 사용할 수 없습니다.
SELECT *
FROM countries NATURAL INNER JOIN cities;
SELECT *
FROM countries INNER JOIN cities
USING(country, country_iso_code)
- The above two statements are the same.
FROM A LEFT OUTER JOIN B ON A.<<Column1>> = B.<<Column2>>
FROM A, B
WHERE A.<<Column1>> = B.<<Column2>>(+)
- The above two statements are the same.
FROM A RIGHT OUTER JOIN B ON A.<<Column1>> = B.<<Column2>>
FROM A, B
WHERE A.<<Column1>>(+) = B.<<Column2>>
- The above two statements are the same.
- Source: https://www.w3resource.com/sql/joins/perform-an-equi-join.php
- Equi join performs a join against equality or matching column(s) values of the associated tables. An equal sign(
=
) is used as comparison operator in the where clause to refer equality. - Salesforce Object Query Language에서는 Equi join만 사용 가능합니다.
- Source: https://learnsql.com/blog/illustrated-guide-sql-non-equi-join/
!=
,<
,<=
,>
,>=
,BETWEEN AND
- Source: https://www.geeksforgeeks.org/sql-subquery/
- A subquery is a query within another query. The outer query is called as main query and inner query is called as subquery.
- The subquery generally executes first, and its output is used to complete the query condition for the main or outer query.
- Subquery must be enclosed in parentheses.
- Subqueries are on the right side of the comparison operator.
ORDER BY
cannot be used in a Subquery.GROUP BY
can be used to perform same function asORDER BY
.- Use single-row operators with single-row subqueries. Use multiple-row operators with multiple-row subqueries.
- Source: https://www.tutorialspoint.com/What-are-single-row-and-multiple-row-subqueries
- Single-Row Subquery: A single-row subquery is used when the outer query's results are based on a single, unknown value. Although this query type is formally called "single-row," the name implies that the query returns multiple columns-but only one row of results. However, a single-row subquery can return only one row of results consisting of only one column to the outer query.
=
,!=
,<
,<=
,>
,>=
SELECT first_name, salary, department_id FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);
- Multiple-Row Subquery: Multiple-row subqueries are nested queries that can return more than one row of results to the parent query
IN()
,ALL()
,ANY()
,EXISTS()
- A subquery can be placed in
SELECT
clause(Scala Subquery),FROM
clause(Inline View),WHERE
clause(Subquery),HAVING
clause.
- Inline view is a subquery in the
FROM
clause of aSELECT
statement.
SELECT hacker_id, name
FROM (
SELECT su.hacker_id, ha.name,
CASE WHEN su.score = di.score THEN 1 ELSE 0 END AS is_full_score
FROM submissions AS su LEFT OUTER JOIN challenges AS ch ON su.challenge_id = ch.challenge_id
LEFT OUTER JOIN difficulty AS di ON ch.difficulty_level = di.difficulty_level
LEFT OUTER JOIN hackers AS ha ON su.hacker_id = ha.hacker_id) AS A
GROUP BY hacker_id, name
HAVING SUM(is_full_score) > 1
ORDER BY SUM(is_full_score) DESC, hacker_id ASC;
- Scala subquery should be a single-row subquery.
- Source: https://myjamong.tistory.com/176
- 내부 Subquery에서 외부테이블의 값을 참조할 때 사용됩니다.
- Subquery와는 다르게 Inner Query 부터 Outer Query 순서대로 실행되는 것이 아니라 Outer Query에서 읽어온 행을 갖고 Inner쿼리를 실행하는 것을 반복하여 결과를 출력해줍니다.
- Outer Query와 Inner Query에서 같은 테이블을 참조한다면 Outer Query의 테이블에 Alias를 사용해서 구분해줍니다.