mode 2 SQL

Question 1

What is SQL?

Answer 1

Structured Query Language

It’s a syntax used to communicate with the database (DBMS)

Question 2

Types of SQL Languages:

Answer 2

• PostgreSQL
• Oracle SQL (aka PL/SQL)
• MySQL
• SQL Server
• etc

Question 3

What…..are the sublanguages of core SQL?

Answer 3

DML, DDL, DQL, DCL, TCL

Question 4

What is DML?

Answer 4

> DML - Data Manipulation Language
»DML operates on records/data
»These are the CRUD methods: create, read, update, delete
»keywords: INSERT, SELECT, UPDATE, DELETE
(c) (r) (u) (d)
»DML needs to be commited, the other sublanguages are generally AUTOCOMMIT

Question 5

What is DDL?

Answer 5

> DDL - Data Definition Language
»DDL operates on schema
»The “schema” refers to your DB’s structures (aka its containers like tables, views, etc)
»keywords: CREATE, ALTER, DROP, TRUNCATE
(c) (u) (d)

Question 6

What is DQL?

Answer 6

> DQL - Data Query Language
»Request data from the table
»Some DBs consider select as its own sublanguage
»keywords: SELECT
»SELECT column1 FROM tableA WHERE attr1 = value

Question 7

What is DCL?

Answer 7

> DCL - Data Control Language
»DCL controls WHO has access to your data
»keywords: GRANT, REVOKE

Question 8

What is TCL?

Answer 8

TCL - Transaction Control Language
	>>TCL deals with creating "transactions" and persisting data
	>>keywords: COMMIT, SAVEPOINT, ROLLBACK
	>>COMMIT is like "git commit"
	>>ROLLBACK is like "git stash"

Question 9

What is a transaction?

Answer 9

it’s a commit; a change to the state of the database (or group of changes)
>example
insert (2record); delete (5records); update
(9records); commit;

how many transactions have occured?
>1 transaction has occurred

Question 10

what is rollback?

Answer 10

the rollback will delete changes back to the most RECENT commit

Question 11

what is savepoint?

Answer 11

Savepoint creates checkpoints that you can rollback to without rolling back EVERYTHIGN since the last commit

Question 12

What are the properties of a transaction?

Answer 12

The ACID properties

Question 13

The ACID properties?

Answer 13

Atomicity
»“all or nothing”; a transaction cannot be broken down any further into separate parts

> Consistency
»after a transaction the state of your schema is intact
»the data should LOGICALLY be consistent; there shouldn’t be a mix of old and new data.

> Isolation
»transactions cannot interfere with one another

> Durability
»changes you make to the state of the DB persists

Question 14

DATATYPES IN POSTGRESQL:

Answer 14

• Integer, varchar, timestamp, date, numeric, serial, bigserial, smallserial, bigint, smallint
• text, etc

Question 15

What are constraints?

Answer 15

CONSTRAINTS: they are restrictions on the data that can be entered into a column; or to think of it another way, they are conditions that have to be met

Question 16

What is a primary key(pk)?

Answer 16

PRIMARY KEY (PK) irepresents the official unique identifier for the record

Primary key is a combination of unique and not null
* >Additional note: you should probably NEVER change your PK, not because it isn’t possible but because it’s dangerous.

Question 17

What is Foreign Key(FK)?

Answer 17

FOREIGN KEY (FK) is a reference pointer to another table’s PK

FK does not have to be not-null and unique (but it does have to exist)

example syntax
	FOREIGN KEY (refAttr_from_THIS_table) REFERENCES other_table (PK_column_from_other_table)

Question 18

What is unique?

Answer 18

UNIQUE ensures that any two records will NOT have the same value in this column

Question 19

What is not null?

Answer 19

NOT NULL ensures that the record DOES have a value in this column (can’t be null)

Question 20

What is check?

Answer 20

CHECK ensures that the records meet a certain condition

example syntax
CONSTRAINT constraint_name CHECK (column_amount>15)