[SQLD] 1. SQL basics

· Tech· Cloud
Certification

SQL command

Data Manipulation Language (DML): SELETE, INSERT, UPDATE, DELETE

Data Definition Language (DDL): CREATE, ALTER, DROP, RENAME

Data Control Language (DCL): GRANT, REVOKE

Transaction Control Language (TCL): COMMIT, ROLLBACK

Types of Constraints

PRIMARY KEY Primary key >> 1 per table, duplicate values ​​and NULL are not allowed.

FOREIGN KEY foreign key

UNIQUE KEY Unique key >> There are no duplicate values ​​in the table.

NOT NULL

CHECK

ALTER TABLE table name ADD CONSTRAINT STU_PK PRIMARY KEY(STU_NO);

ALTER TABLE table name DROP COLUMN column name;

RENAME TABLE table name TO table name to change;

Referential Integrity Rules

This is a regulation that defines how the Child Table value will change when the Parent Table is changed.

Delete/Modify Action: Cascade, Set Null, Set Default, Restrict

>> Cascade: When deleting the value of the Parent Table, the value of the Child Table is also deleted.

>> Restrict: Parent Table deletion/modification is allowed only when there is no PK value in the Child Table.

>> No Action: Deletion/modification that violates referential integrity is not permitted.

This is a regulation that changes the value of the Child Table depending on the PK value of the Parent Table.

Insert Action: Automatic, Set Null, Set Default, Dependent, No Action

>> Automatic: Allows entry of the Child Table after creating the PK value of the Parent Table.

>> Dependent: Child Table input is allowed only when a PK value exists in the Parent table.

>> No Action: Do not allow input that violates referential integrity.

SELETE [ALL/ DISTINCT ] column name FROM table name;

DISTINCT >> If there is duplicate data, it is processed and output as one case.

__________ TABLE table name; >> Command when deleting a table

TRUNCATE

DDL, Auto Commit

Delete table data and return space. The table itself is not deleted, and the index is also TRUNCATEd. Deletion method that leaves no logs.

Because it does not create data for UNDO, it is faster than DELETE when deleting the same amount of data.

DELETE

DML, User Commit

The entire table is deleted, and the index is also deleted. The space on the disk is taken as is.

DROP

DDL, Auto Commit

The entire table is deleted, and the index is also deleted. Starting with Oracle 10g, the table is not deleted, but is renamed BIN$ and temporarily deleted. Deletion method that leaves no logs. Return all space on the disk!

Transaction Features

Atomicity

All operations defined in a transaction must either be executed successfully or remain not executed at all.

Consistency

If there are no errors in the database contents before the transaction is executed, there should be no errors in the database contents after the transaction is executed.

Isolation

While a transaction is being executed, it must not be influenced by other transactions and produce incorrect results.

Durability

When a transaction is successfully performed, the database contents updated by the transaction are permanently stored.

Differences between Oracle and SQL Server

Oracle

After executing DDL, COMMIT is automatically performed.

When looking for a value inserted with '', you can find it using the IS NULL condition.

SQL Server

COMMIT is not automatically performed after DDL execution.

When looking for a value inserted with '', you can find it using the IS NULL condition.

Dirty Read

This refers to reading data that has been modified by another transaction but has not yet been committed.

Null

NVL(Expression1, Expression2), ISNULL(Expression1, Expression2)

When the value of expression 1 is NULL, expression 2 is output.

NULLIF(expression1, expression2)

If the value of expression 1 is not equal to the value of expression 2, expression 1 is output. If they are equal, NULL is output.

COALESCE(expression1, expression2, ...)

NULL is output only when all expressions in ( ) are NULL. If it is not NULL, the value of the non-expression is output.

SUM and AVG output the sum and average, excluding NULL values.

COUNT(*) outputs including NULL values.

COUNT(expression) Outputs the expression except that it is NULL.

알라딘: SQL 자격검정 실전문제

https://www.aladin.co.kr/shop/wproduct.aspx?ItemId=97612848&start=pgooglemc

Comments

No comments yet. Be the first!

    164 posts in 테크

    1–5 / 164