SQL Statements - DML, DDL,DCL

SQL Server > SQL Statements

SQL Statements are used for querying with database

Data Manipulation Language (DML)

• Access and manipulate data in existing schema objects
• Example: SELECT, INSERT, UPDATE, DELETE, WITH

Data Definition Language (DDL)

• Setup, change, remove data structures
• CREATE, ALTER, DROP, RENAME

Data Control Language (DCL)

• Grants or remove rights to database and structures within it

ACID SQL

ACID is an acronym for atomicity, consistency, isolation, and durability.

Atomicity

One transaction is atomic. An atomic transaction is either fully completed, or is not begun at all.  If for any reason an error occurs and the transaction is unable to complete all of its steps, the then system is returned to the state it was in before the transaction was started.

Consistency

A transaction enforces consistency in the system state by ensuring that at the end of any transaction the system is in a valid state. If the transaction completes successfully, then all changes to the system will have been properly made, and the system will be in a valid state. If any error occurs in a transaction, then any changes already made will be automatically rolled back.

Isolation

When a transaction runs in isolation, it appears to be the only action that the system is carrying out at one time. If there are two transactions that are both performing the same function and are running at the same time, transaction isolation will ensure that each transaction thinks it has exclusive use of the system.

Durability

A transaction is durable in that once it has been successfully completed, all of the changes it made to the system are permanent. There are safeguards that will prevent the loss of information, even in the case of system failure.

SQL COUNT

SQL Server > Built-in Functions > COUNT

COUNT returns the number of rows that matches a specified criteria.
The NULL values will not be count.

Example

SELECT COUNT(column_name) FROM table_name
SELECT COUNT(*) FROM table_name
SELECT COUNT(DISTINCT column_name) FROM table_name