SQLD CH.3 SQL 기본

E.F CODD는 어떤 이론에 따라 관계형 데이터베이스 만들었누?

정규화 이론

어떤 문제가 있었는가

데이터의 일관성 문제를 근본적으로 해결한 데이터베이스 시스템이 관계형 데이터베이스이다

관계형 데이터베이스 테이블 구성요소

필드, 레코드, 스키마

DDL 뭐임?

Data definition language (데이터 정의 언어)
데이트의 구조 즉 스키마를 정의하는 명령어

DML 뭐임?

Data manipulation language(데이터 조작 언어)
조회, 입력, 수정, 삭제 하는 명령어

DCL 뭐임

사용자 접근 권한과 같이 보안과 제어를 다루는 명렁어 ex. GRANT, REVOKE )

DROP

Deletes entire table (table structure + data)
No rollback and cannot be undone

TRUNCATE

Deletes all rows but keeps the table (only removes data)
Rollback X Cannot be undone

DELETE

Removes specific rows from a table based on a condition. Rollback is possible if not yet committed.

What is the execution order/ query flow order?

FROM, WHERE, GROUP, HAVING, SELECT, ORDER BY

FROM

Specifies the table to retrieve the data from

WHERE

Filters rows based on a condition

GROUP BY

Grioups rows with the same values into summary rows

HAVING

Filters grouped records (used with GROUP BY)

ORDER BY

Sorts the result set in ascending ASC- default order or descending DSC order

단일행 함수

하나의 행에 대해서 연산을 수행한 후에 결과를 반환하는 함수

다중행 함수

여러 행에 대해서 연산을 수행하여 결과를 반환하는 함수. 여러 입력 인자에 대해 단일값을 반환한다는 점은 단일행 함수와 같다.

LOWER (arg)

Turns it into lowercase

UPPER (arg)

Turns it into uppercase

CHR

Returns the character that corresponds to a given ASCII code

TRIM

Removes spaces or specific characters from a string.

TRIM (LEADING)

앞쪽 공백 or 캐릭터 제거

TRIM (TAILING)

뒤쪽 공백 or 캐릭터 뺴기

TRIM (BOTH)

양쪽 공백 or 캐릭터 뺴기

LTRIM

왼쪽 끝에서 공백 또는 지정된 문자열 제거한다

RTRIM

오른쪽 끝에서 공백 또는 지정된 문자열 제거한다

SUBSTR ()

Removes part of a string based on a provided starting position and an optional length.
SUBSTR (string, start-position, length)

ex) SELECT SUBSTR (‘Good Morning’, 1,4)
+ Good (selected characters 1-4 from the provided string)

REPLACE

Replaces something within a string. If a replacement value is not provided, it will be deleted.

SELECT REPLACE (‘Good Morning’, ‘Morning’, ‘Afternoon’') FROM DUAL;

= Good Afternoon

ABS

절대값을 반환한다
used to remove negative signs in calculations

Ex. SELECT ABS(-2.3) FROM DUAL;
= 2.3

MOD

Returns the remainder of a division operation

ex MOD (10,3) FROM DUAL;
= 1
Since 10/3= 3 with 1 left

ROUND

Rounds to a specified decimal place (if the number of decimal places is not specified, it rounds to a digit)

ex)

ROUND(2.67, 1) FROM DUAL;
=2.7 (round to 1 decimal place)

ex) ROUND(2.67)
= 3

TRUNC

Does the same as ROUND but doesn’t round based on numerical value, just cuts of the number at a specified point.
If no value is provided, it gets rid of the decimal places

Ex)
TRUNC(2.37, 1) FROM DUAL;
= 2.3

Ex)
TRUNC(2.37) FROM DUAL;
=2

CEIL

CEILING
Returns the smallest integer greater than or equal to the given number

Ex) SELECT CEIL (4.3) FROM DUAL;
= 5
5 is the smallest integer that is smaller than or equal to 4.3

FLOOR

Same as CEIL but the opposite. Finds the largest integer that is less than or equal to the given number

Ex)
SELECT CEIL (4.3) FROM DUAL;
=4

SYSDATE

Provides the current date and time
Returns this based on the database server where the query is executed.

EXTRACT

Extracts the year, month or day from the date

Ex)
It is the 19th of Feb, 2024

SELECT EXTRACT (YEAR FROM SYSDATE) AS YEAR FROM DUAL;
=2024

변환함수 (Conversion Functions)

Used to convert between different data types such as strings, dates and numbers in SQL

TO_NUMBER

Convers string values or date values to numeric values (integers or decimals)

Ex)
SELECT TO_NUMBER (‘1001’) AS MEMBER_ID FROM DUAL;

= 1001

TO_CHAR

Converts a number or date value to a character string

TO_DATE

Converts the data type to a date

NULL 관련 함수

Provides ways to return specific values when encountering NULL

NVL()

Replaces NULL with a default value.
If the expression is NULL, it will return the specified replacement value. If it is not, it will return the expression’s value.

Ex

SELECT NVL (Salary, 0) FROM employees;
If salary is NULL will return as 0
If not null, the actual salary will pop up

HELPS CHECK FOR NULLS

NULLIF ()

Compares two expressions and returns NULL if they are equal. If they are not equal, it will return the first expression.

Ex

SELECT EMPNO, ENAME, MGR, NULLIF (MGR,7698) AS NM FROM EMP;

If MGR equals 7698 then it will return as NULL

If they aren’t equal the value of MGR will be returned

COALESCE

Returns the first non-NULL value in the list of expressions
It is useful when you wan to select a value from a list and one is NULL, it will return the next one

Ex
COALESCE (NULL,NULL,50,10)
= 50

CASE

Used to perform conditional logic. It allows you to return different values based on different/certain conditions.

Ex
SELECT MOVIE_CODE
CASE
WHEN MOVIE_CODE = ‘01’ THEN ‘COMEDY’
WHEN MOVIE_CODE = ‘02’ THEN ‘SF’
WHEN MOVIE_CODE= ‘03’ THEN ‘ACTION’
ELSE ‘etc’
END AS MOVIE_GENRE
FROM MOVIE;

Corresponding movie codes will be assigned a genre and if not will be labeled ‘etc’.

CAN ALSO USE ‘THEN’ INSTEAD OF ‘=’

단일행 부정 연산자

Compare a single value against a condition (negatively) / it isn’t smtg
Operators: !=, <>, NOT BETWEEN A AND B, NOT LIKE

Ex.
SELECT ename, sal 

FROM emp 

WHERE sal != 3000; OR sal <> 3000 

Retrieves employees whose salary is NOT 3000 

다중행 부정 연산자

Compares multiple values returned by a subquery

Operators: NOT IN, <> ALL

Ex. NOT IN 

SELECT ename, deptno

FROM emp 

WHERE deptno NOT IN (10, 20, 30) 

Retrieves employees who are not in departments 10, 20 or 30 

Logical Operators (논리 연산자)

Operators: AND, OR, NOT 

Ex. AND  (Both conditions must be true) 

SELECT ename, sal, deptno 

FROM emp 

WHERE sal > 2000 AND deptno = 10 ; 

Retrieves employees whose salary is greater than 2000 and who work in department 10 

GROUP BY

Used to group rows that have the same values and perform aggregate functions (집계 함수) on each group

Ex. Find the avg salar per dpt 

SELECT deptno, AVG (sal) 

From emp

GROUP BY deptno ; 

Retrieves the average salary for each department

Aggregate Functions (집계 함수)

Functions that perform calculations on multiple rows at once 

COUNT (*) - Returns the total number of rows 

SUM (column) - Returns the sum of a column 

AVG (column) - Returns the average value 

MIN (column) - Returns the minimum value 

MAX (column) - Returns the maximum value

HAVING

Filtering Grouped Results

AGGREGATED FUNCTIONS REQUIRE HAVING not WHERE

Unlike WHERE, which filters individual rows, HAVING filters GROUPEDDD results (already grouped) 

Ex. Find depts where the average salary is greater than 3000 

SELECT deptno, AVG (sal) 

FROM emp 

GROUP BY deptno 

HAVING AVG (sal) >= 3000 ; 

Retrieves departments where the average salary is greater than 3000 

Single-Row Negative Comparison Operators / simp

!=, <>, NOT BETWEEN, NOT LIKE 

Compare a single value (negatively)

Multi-Row Negative Comparison/ simp

NOT IN, <> ALL 

Compare against multiple values 

Negative Operators/ simp

NOT 

Apply negation to conditions 

Logical Operators/ simp

AND, OR, NOT 

Combine conditions logically 

GROUP BY/ simp

Group rows for aggregate functions

Aggregate functions/ simp

COUNT, SUM, AVG, MIN, MAX 

Summarises data 

HAVING/ simp

Filters grouped data