Lesson 2 (MIDTERM)

Question 1

What does SQL stands for?

Answer 1

Structured Query Language

Question 2

is a computer language for storing, manipulating and retrieving data stored in relational database.

Answer 2

SQL or Structured Query Language

Question 3

What does RDBMS stands for?

Answer 3

Relational Database Management System.

Question 4

SQL is the standard language for _______ _______ ______

Answer 4

Relational Database Management System.

Question 5

like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language.

Answer 5

Relational Database Management System (RDBMS)

Question 6

• can execute queries against a database
• can retrieve data from a database
• can insert records in a database
• can update records in a database
• can delete records from a database
• can create new databases
• can create new tables in a database
• can create stored procedures in a database
• can create views in a database
• can set permissions on tables, procedures, and views

Answer 6

SQL

Question 7

is basically just a software application which we can use to create and manage different databases.

Answer 7

Relational Database Management System.

Question 8

is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access

Answer 8

Relational Database Management System.

Question 9

What are the components of RDBMS

Answer 9

• Tables
• Relations
• Keys
  – Primary Key
  – Foreign Key
• Normalization
• ACID properties (Atomicity, Consistency, Isolation, Durability)
• SQL

Question 10

What are the numeric data types

Answer 10

TINYINT
SMALLINT
MEDIUMINT(size)
INT
BIGINT
DECIMAL(x,y)

Question 11

can store numbers from -128 to 127

Answer 11

TINYINT

Question 12

can store numbers from -32768 to 32767

Answer 12

SMALLINT

Question 13

can store numbers from -8,388,608 to 8,388,607

Answer 13

MEDIUMINT(size)

Question 14

can store numbers from -2,147,483,648 to 2,147,483,647

Answer 14

INT

Question 15

can store numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

Answer 15

BIGINT

Question 16

can store decimal number of total x digits (max up to 65) of which y digits (max up to 30) are allocated after the decimal point

Answer 16

DECIMAL(x,y)

Question 17

What are the string data types

Answer 17

VARCHAR(x)
BINARY(x)
TINYTEXT
MEDIUMTEXT
LONGTEXT

Question 18

can store characters up to given length (max 8000 characters)

Answer 18

VARCHAR(x)

Question 19

can store binary strings of fixed length

Answer 19

BINARY(x)

Question 20

can store up to 255 characters TEXT(x) can store characters up to the given limit (max 65,535 bytes)

Answer 20

TINYTEXT

Question 21

can store characters up to 16,777,215 characters

Answer 21

MEDIUM TEXT

Question 22

can store characters up to 4,294,967,295 characters

Answer 22

LONGTEXT

Question 23

What are the date and time data types

Answer 23

TIMESTAMP(fsp)
DATE
TIME(fsp)
Year

Question 24

can store date and time Format: YYYY-MM-DD hh:mm:ss. (‘1970-01-01 00:00:00’ UTC)

Answer 24

TIMESTAMP(fsp)

Question 25

can store only date Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31'

Answer 25

DATE

Question 26

can store only time. Format: hh:mm:ss. The supported range is from '-838:59:59' to '838:59:59'

Answer 26

TIME(fsp)

Question 27

A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and 0000.

Answer 27

Year

Question 28

in a table is a value in a field that appears to be blank, which means a field with a _______ is a field with no value.

Answer 28

NULL

Question 29

are the rules enforced on data columns on table.

Answer 29

Constraints

Question 30

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.

Answer 30

Constraints

Question 31

Ensures that a column cannot have NULL value. By default, a column can hold NULL values. If you do not want a column to have a NULL value, then you need to define such constraint on this column specifying that NULL is now not allowed for that column. A NULL is not the same as no data, rather, it represents unknown data.

Answer 31

NOT NULL

Question 32

Provides a default value for a column when none is specified. The ________ constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value.

Answer 32

DEFAULT Constraint

Question 33

Ensures that all values in a column are different.

Answer 33

UNIQUE Constraint

Question 34

prevents two records from having identical values in a particular column. In the CUSTOMERS table, for example, you might want to prevent two or more people from having identical age.

Answer 34

UNIQUE Constraint

Question 35

Uniquely identified each rows/records in a database table.

Answer 35

Primary Key

Question 36

is a field in a table which uniquely identifies each row/record in a database table.

Answer 36

Primary Key

Question 37

must contain unique values. A ______ column cannot have NULL values.

Answer 37

Primary Key

Question 38

Uniquely identified a rows/records in any another database table

Answer 38

Foreign Key

Question 39

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

Answer 39

Index

Question 40

Technique of organizing data in a database, a systematic approach that aims to reduce data redundancy and anomalies (such as insert, update, delete anomalies) by breaking down large table into smaller tables.

Answer 40

Normalization

Question 41

- Every column should only have 1 value each row. - Each row should be unique. - Not mandatory to have primary key.

Answer 41

1st Normal Form

Question 42

- Builds on 1NF and eliminates partial dependencies. - If a non-key attribute is partial dependent on a candidate key then split them into a separate table and be fully dependent on the primary key.

Answer 42

2nd Normal Form

Question 43

- Build on 2nd . - Avoid Transitive dependencies, meaning no non-key column should depend on another non-key column.

Answer 43

3rd Normal Form