Section 2: SQL Theory

Question 1

4 Different types of programming

Answer 1

1. Procedural (Imperative)
2. Object-Oriented
3. Declarative (outcome)
4. Functional

Question 2

4 Main components of Data SQL Syntax and purpose

Answer 2

1. Data Definition Language (DDL) Creation of Data
2. Data Manipulation Language (DML) Manipulation of Data
3. Data Control Language (DCL) Assignment and removal of permission to use data
4. Transaction Control Language (TCL) Saving and restoring changes to a database

Question 3

What is DDL

Answer 3

• Data Definition Language
• A syntax not a language
• A set of statements that allow the user to define or modify data structures and objects, such as tables.

Question 4

DDL Purpose

Answer 4

Used for creating entire databases and database objects as tables

Question 5

Create Statement

Answer 5

Syntax used for creating entire databases and database objects as tables

Question 6

Alter Statement (3 uses)

Answer 6

Syntax used when altering existing objects
1. Add
2. Remove
3. Rename
one of the columns in the table

Question 7

Drop Statement

Answer 7

Syntax used to delete entire table

Question 8

Rename Statement

Answer 8

Syntax used to rename a table

Question 9

Truncate Statement

Answer 9

Syntax used to remove data in a table but keep the code/formula

Question 10

DDL 5 Syntaxes

Answer 10

Data Definition Language

1. Create
2. Alter
3. Drop
4. Rename
5. Truncate

Question 11

Keyword

Answer 11

• Reserved words
• Keywords in SQL cannot be variable names.
• Keywords that cannot be used when naming objects

Excel formula names

Question 12

DML

Answer 12

Data manipulation Language

Question 13

Select Statement

Answer 13

Syntax that will select information in a table.
- Useful in getting micro view of large data
u

Question 14

Insert Statement

Answer 14

Syntax used to insert data into the tables

Question 15

Update Statement

Answer 15

Syntax used to update information in a table

Question 16

Delete Statement

Answer 16

Syntax similar to truncate that allows you to specify precisely what you would like to be removed

Question 17

Truncate V. Delete

Answer 17

1. Truncate removes all data

2. delete removes specific data in a table

Question 18

DML 4 main statements and syntax

Answer 18

1. Select… From…
2. Insert… Into… Values
3. Update… Set… Where….
4. Delete… from… Where

Question 19

DCL

Answer 19

Data control Language
which allows to manage the rights users have in a database
1. Grant
2. Revoke

Question 20

Grant Statement

Answer 20

Gives (grants) certain permissions to users.

Question 21

Database Administrators

Answer 21

People who have complete rights to a database, they can grant access to users or revoke it

Question 22

Revoke Statements

Answer 22

Used to revoke permissions and privileges of database users

Question 23

TCL

Answer 23

Transaction Control Language

Question 24

Commit Statement

Answer 24

1. Save changes made
2. Relates to Insert, Delete, Update
3. Will let other users have access to the modified version of the database

Question 25

Rollback Clause

Answer 25

Allows to undo any changes made that do not wish to save permanently.. Rollback reverts to the last non-committed state