SQL & RDB Basic

Question 1

DBMS

Answer 1

program that controls creation, maintenance and use of DB

Question 2

RDBMS

Answer 2

store data in collection of tables which is related by common fields between columns

Question 3

SQL

Answer 3

structured query language, use to communicate with a database

Question 4

Table

Answer 4

set of data organized in columns a rows

Question 5

Field

Answer 5

column, single piece of info from a record

Question 6

PK

Answer 6

combination of fields which uniquely specify a row, implicit NOT NULL

Question 7

Unique Key

Answer 7

uniquely identify each record in database

Question 8

FK

Answer 8

a column or group of column that provide link between data in two tables

Question 9

Inner Join

Answer 9

matching values in both tables

Question 10

Outer Join

Answer 10

union of two tables

Question 11

Natural join

Answer 11

columns with the same name of associated table will appear only once (same data type)

Question 12

Right join

Answer 12

everything from the right table and everything in both table

Question 13

Left join

Answer 13

everything from the left table and everything in both table

Question 14

Full join

Answer 14

all row of joined table whether the same or different

Question 15

Self join

Answer 15

table joined itself

Question 16

Normalization

Answer 16

process of minimizing redundancy and dependency by organizing fields and tables of a database

Question 17

View

Answer 17

virtual table consists of subset of data contained in a table

Question 18

Index

Answer 18

performance tuning method of allowing faster retrieval of records

Question 19

Unique index

Answer 19

indexing does not allow field to have duplicate values if the column is unique indexed (e.g. pk column)

Question 20

Clustered index

Answer 20

reorder the physical order of the table and search based on key values

Question 21

Non clustered index

Answer 21

does not alter physical order of the table and maintains logical order of data

Question 22

Stored Procedure

Answer 22

function consists of many SQL statement to access DB system

Question 23

Trigger

Answer 23

code/program that automatically runs with response to event on a table or view

Question 24

SQL vs NoSQL

Answer 24

SQL

• Tables with fixed rows and columns
• Schemas are Rigid
• Requires Join between table
• Requires Object relation mapping
• used when data validity is super important
• vertically scalable

NoSQL

• Document: JSON documents, Key-value: key-value pairs, Wide-column: tables with rows and dynamic columns, Graph: nodes and edges
• Schemas are flexible
• Usually dont require join
• Usually faster than SQL
• Use when it’s more important to have fast data than correct data
• Horizontally Scalable

Question 25

Benefit of SQL

Answer 25

- Data integrity - Data accuracy - An ideal choice for the complex query intensive environment - Cross-platform support, Secure and free

Question 26

Benefit of NoSQL

Answer 26

Flexible data models Horizontal scaling Fast queries Easy to use, High performance, and Flexible tool

Question 27

Disadvantage of SQL

Answer 27

not suitable for hierarchical data storage

Question 28

Disadvantage of NoSQL

Answer 28

don’t support ACID (atomicity, consistency, isolation, durability) transactions across multiple documents

Question 29

To remove a table

Answer 29

DROP

Question 30

To remove a record

Answer 30

DELETE

Question 31

TRUNCATE

Answer 31

Delete all rows from a table, cannot rollback, faster than DELETE

Question 32

What is Normalization and what are the advantages of it?

Answer 32

the process of organizing data to avoid duplication and redundancy

Question 33

First Normal Form (1NF)

Answer 33

No repeating groups within rows

Question 34

Second Normal Form (2NF)

Answer 34

Every non-key (supporting) column value is dependent on the whole primary key.

Question 35

Third Normal Form (3NF)

Answer 35

Dependent solely on the primary key and no other non-key (supporting) column value. in 2NF and not contain any transitive partial dependency.

Question 36

ACID Property

Answer 36

Atomicity, Consistency, Isolation, Durability

Question 37

Atomicity

Answer 37

It means if one part of any transaction fails, the entire transaction fails and the database state is left unchanged

Question 38

Consistency

Answer 38

Consistency ensures that the data must meet all the validation rules.

Question 39

Isolation

Answer 39

The main goal of isolation is concurrency control.

Question 40

Durability

Answer 40

Durability means that if a transaction has been committed, it will occur whatever may come in between such as power loss, crash or any sort of error.

Question 41

What is a Database?

Answer 41

Database is nothing but an organized form of data for easy access, storing, retrieval and managing of data. This is also known as structured form of data which can be accessed in many ways.