SQL

Question 1

WHAT IS CRUD

Answer 1

CREATE
READ
UPDATE
DELETE

Question 2

crud CREATE

Answer 2

MAKES NEW DATA

Question 3

crud READ

Answer 3

ACCESS DATA

Question 4

crud UPDATE

Answer 4

MAKE CHANGES TO DATA

Question 5

crud DELETE

Answer 5

REMOVE DATA

Question 6

SUB LANGUAGES OF SQL

Answer 6

DDL - DATA DEFINITION LANGUAGE
DML- DATA MANIPULATION LANGUAGE 
DQL - DATA QUERY LANGUAGE
DCL - DATA CONTROL LANGUAGE
TCL TRANSACTION CONTROL LANGUAGE

Question 7

DDL - DATA DEFINITION LANGUAGE

Answer 7

THE STRUCTURE OF THE DATABASE.
AUTO COMMIT, CAN NOT BE ROLLED BACK

COMMANDS:
CREATE
ALTER
DROP
TRUNCATE

Question 8

DML- DATA MANIPULATION LANGUAGE

Answer 8

THE DATA ENTRIES THEMSELVES
NOT AUTO COMMIT.

COMMANDS
INSERT
UPDATE
DELETE

Question 9

DQL - DATA QUERY LANGUAGE

Answer 9

USED TO MAKE QUERIES IN DATABASES AND INFORMATION SYSTEMS.

COMMAND
SELECT

Question 10

DCL - DATA CONTROL LANGUAGE

Answer 10

CONTROLS ACCESS OF WHO CAN SEE / USE DATA

COMMANDS
GRANT
REVOKE

Question 11

TCL TRANSACTION CONTROL LANGUAGE

Answer 11

CONTROLS TRANSACTIONS

COMMANDS
COMMIT
SAVEPOINT
ROLLBACK

Question 12

DDL CREATE

Answer 12

A PART OF DDL
CREATES/ADDS A TABLE TO THE DATABASE

CREATE TABLE BEAR(
BEAR_ID INTEGER PRIMARY KEY);

Question 13

DDL ALTER

Answer 13

PART OF DDL
MAKES CHANGES TO THE TABLE

ALTER TABLE BEAR, DROP COLUMN D;

Question 14

DDL DROP

Answer 14

PART OF DDL
DELETES A TABLE FROM THE DATABASE
DROP TABLE BEAR;

Question 15

DDL TRUNCATE

Answer 15

PART OF DDL
DELETES DATA W/O DELETING THE TABLE

TRUNCATE TABLE BEAR_TABLE;

Question 16

DML INSERT

Answer 16

PART OF DML
INSERTING A ROW (ROW DATA ENTRY)

INSERT INTO BEAR_TYPE VALUES (1, ‘BROWN’);

INSERT DATA FROM ANOTHER TABLE
INSERT INTO BEAR_TYPE VALUES (1, 'BROWN')
[(COLUMN1, COLUMN2)]
SELECT {('COLUMN1', 'COLUMN2')]
FROM TABLE_NAME2
[WHERE CONDITIONS]

Question 17

DML UPDATE

Answer 17

PART OF DML
MODIFY A ROW (DATA MODIFICATION)

UPDATE BEAR BEAR_NAME = DALE ‘DALE’ WHERE NAME = ‘BOB’;

Question 18

DML DELETE

Answer 18

PART OF DML
DELETE A ROW OR MULTIPLE ROWS

DELETE FROM BEAR WHERE NAME =’BOB’

Question 19

SELECT

Answer 19

PART OF DQL
GRABS DATA FROM THE DATABASE

SELECT * FROM* - WILL GRAB EVERYTHING
SELECT BEAR_NAME FROM BEAR - WILL JUST GRAB THE NAME.

Question 20

COMMIT

Answer 20

PART OF TCL

PERMANENT SAVE - NO GOING BACK

Question 21

SAVEPOINT

Answer 21

PART OF TCL
SAVES A POINT LOCALLY TO YOUR COMPUTER NOT TO THE SERVER

SAVEPOINT(NAME OF SAVEPOINT)

Question 22

ROLLBACK

Answer 22

PART OF TCL
LETS YOU REVERT BACK TO LAST COMMIT OR SPECIFIED SAVEPOINT.

ROLLBACK(NAME OF SAVEPOINT)

Question 23

CAN YOU HAVE NON RELATIONAL DATABASES?

Answer 23

YES

Question 24

PRIMARY ID

Answer 24

A UNIQUE # ASSIGNED TO A COLUMN IN DATABASE TABLE.
MUST BE UNIQUE AND NOT NULL
YOU NEVER WANT TO CHANGE PRIMARY KEY AS IT CAN BREAK REFERENTIAL INTEGRITY

Question 25

FOREIGN KEY

Answer 25

IS A CONSTRAINT THAT'S ADDED TO A TABLE. IT ALLOWS YOU TO SPECIFY THAT A COLUMN IN A TABLE REFERS TO THE PRIMARY KEY OF ANOTHER TABLE. THEY ARE UNIQUE AND NOT NULL

Question 26

REFERENTIAL INTEGRITY

Answer 26

Referential integrity ensures that the relationship between two tables remains synchronized during updates and deletes.

Question 27

referential integrity will prevent users from:

Answer 27

Adding records to a related table if there is no associated record in the primary table. Changing values in a primary table that result in orphaned records in a related table. Deleting records from a primary table if there are matching related records.

Question 28

COMPOSITE KEY

Answer 28

IS A COMBINATION OF TWO OR MORE COLUMNS IN A TABLE THAT CAN BE USED TO UNIQUELY IDENTIFY EACH ROW IN THE TABLE WHEN THE COLUMNS ARE COMBINED.

Question 29

SURROGATE KEY

Answer 29

ANY COLUMN OR SET OF COLUMNS THAT CAN BE DECLARED AS THE PRIMARY KEY, INSTEAD OF A REAL KEY. Value generated solely for the purpose of becoming a primary key.

Question 30

NATURAL KEY

Answer 30

A key that is formed of attributes that already exist in the real world ex SSN or Drivers License Value that already exists in our table being used as a primary key.

Question 31

FIRST NORMAL FORM

Answer 31

THE INFORMATION IS STORED IN A RELATIONAL TABLE. EACH COLUMN CONTAINS ATOMIC VALUES. THERE ARE NO REPEATING COLUMNS

Question 32

SECOND NORMAL FORM

Answer 32

THE TABLE IS IN THE FIRST NORMAL FORM AND ALL THE COLUMNS DEPEND ON THE PRIMARY KEY

Question 33

THIRD NORMAL FORM

Answer 33

THE TABLE IS IN THE SECOND FORM AND ALL OF ITS COLUMNS ARE NOT TRANSITIVELY DEPENDENT ON THE PRIMARY KEY

Question 34

ATOMIC

Answer 34

MAKING SURE EACH COLUMN HOLDS THE SMALLEST AMOUNT OF INFORMATION EX INSTEAD FIRST AND LAST NAME AS 1 COLUMN IT SHOULD BE COLUMN: FIRST NAME, COLUMN: LAST NAME.

Question 35

RULES FOR THIRD NORMAL FORM

Answer 35

THERE ARE NO INTERNAL DEPENDENCIES WITH THE COLUMNS. | MUST MAKE A NEW TABLE IF THERE'S AN INTERNAL DEPENDENCY, EVEN IF THEY ARE RELATED TO THE PRIMARY KEY.8

Question 36

DEFAULT CONTRAINT

Answer 36

IF YOU DON'T SPECIFY A VALUE THE TABLE WOULD DEFAULT TO THE NUMBER ASSIGNED EX. BEAR_WEIGHT INTEGER DEFAULT 200

Question 37

Constraints

Answer 37

Constraints are the rules enforced on the data columns of a table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. Constraints could be either on a column level or a table level. The column level constraints are applied only to one column, whereas the table level constraints are applied to the whole table.

Question 38

UNIQUE Constraint

Answer 38

Ensures that all values in a column are different.

Question 39

NOT NULL Constraint

Answer 39

Ensures that a column cannot have NULL value.

Question 40

CHECK Constraint

Answer 40

The CHECK constraint ensures that all the values in a column satisfies certain conditions.

Question 41

INDEX

Answer 41

Used to create and retrieve data from the database very quickly.

Question 42

CARDINALITY

Answer 42

how many elements are in a set.

Question 43

MULTIPLICITY

Answer 43

is made up of a lower and an upper cardinality

Question 44

Relational databases

Answer 44

databases are structured. You have tables and these tables may have dependencies on each other, or relationship ``` Popular Relational SQL Database Systems Microsoft SQL Server Oracle MySQL / MariaDB PostgreSQL Microsoft Azure SQL ```

Question 45

Non-Relational databases

Answer 45

databases are document-oriented. This so called document type storage allows multiple 'categories' of data to be stored in one construct or Document. ``` Popular Non-Relational No-SQL Databases MongoDB Oracle NoSQL Apache CouchDB Redis ```

Question 46

What are some special types of primary keys?

Answer 46

composite key natural key surrogate key

Question 47

What is an orphan record?

Answer 47

An orphan record refers to a record whose foreign key references a deleted/nonexistant record. In Oracle SQL, we can't have orphan records because it enforces referential integrity

Question 48

What is a cascade delete?

Answer 48

A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted.

Question 49

What is normalization?

Answer 49

Normalization is the process of organizing data in a database. We use normalization to eliminate redundancy and inconsistent dependencies. There are six normal forms, though many organizations only use the first three.

Question 50

What is a transaction?

Answer 50

A transaction is a change made to your database. Essentially, every time we you see a commit after 1 or more SQL statements.

Question 51

What is GRANT and REVOKE?

Answer 51

GRANT and REVOKE deal with read and write privileges for a user within the DB. GRANT privileges ON object TO user; REVOKE privileges ON object FROM user;