DIFFERENT STRUCTURES IN DB

Question 1

DDL - Data Definition Language
Standard for commands that define the different structures in a database.

CREATE : Table, Size, Data Types
MODIFY : Alter Table - Name, Column, Data Type
DROP Table, View (can’t roll back)
REMOVE DB objects such as Tables, Indexes and User

CREATE
ALTER
DROP

Answer 1

DML - Data Manipulate Language
Use to work with data
ADD. MODIFY. UPDATE. QUERY
1. Select - Retrieve records from 1+ tables
2. Insert - Create
3. Update - Modify
4. Delete - Remove
5. Trigger - Instead, After
———————————
DCL - Data Control Language 
Use to control access to data
Store in DB GRANT, REVOKE
1. Grant - Privilege to user
2. Revoke - Removes Privileges

NULL = ‘Unavailable’,’Unassigned’. Not same as 0 or blank.

Question 2

DDL

Answer 2

Data Definition Language

Standard for commands that define the different structures in a database.

CREATE : Table, Size, Data Types
MODIFY : Alter Table - Name, Column, Data Type
DROP Table, View (can’t roll back)
REMOVE DB objects such as Tables, Indexes and User

Question 3

DDL COMMANDS

Answer 3

1. Create Command
   Trigger, Drop, Create, Alter
2. Alter Table
   Modify Table, Name, Column, Data Types
3. Drop Table
   Table, View, can’t rollback

Question 4

DML

Answer 4

Data Manipulation Language

Work with Data

Use to work with data
ADD. MODIFY. UPDATE. QUERY

Question 5

DML Commands

Answer 5

1. Select
    Retrieves records from 1+ tables
2. Insert - Create
3. Update - Modify
4. Delete - Remove
5. Trigger - Instead, After

Question 6

DCL

Answer 6

Data Control Language

Controls Access

Use to control access to data
Store in DB GRANT, REVOKE

Question 7

DCL Commands

Answer 7

1. Grant - Privilege to user.

2. Revoke - Removes Privileges

Question 8

NULL

Answer 8

Unavailable
Unassigned

Not same as 0 or Blank

Question 9

Examples of

DML Command Insert

Answer 9

Insert Into table-name(col 1, col 2)
Values(value 1, value 2);

————
Insert data from 1 table to another

Insert Into table-name
Select * from another-table : columns have to match

————-
Select from columns

Insert Into table-name (col 1, col 2)
Select (col 1, col 2) from another-table;

Question 10

Examples of

DML Command Modify

Answer 10

Modify

UPDATE table-name
SET Col1 = value
WHERE condition;

Question 11

Examples of

DML Command DELETE

Answer 11

DELETE

DELETE FROM table-name
WHERE condition;

Question 12

Examples of

DML Command MERGE

Answer 12

MERGE - combine data from multiple tables

MERGE INTO Table 1 USING Table 2
ON (condition) WHEN MATCHED THEN
UPDATE SET 
Col 1 = Value 1,
Col 2 = Value 2
WHEN NOT MATCHED THEN 
INSERT (C1,C2)
VALUES (V1,V2);