MISC#1 Integer Range, TRUNCATE, Functions, Referential Integrity, SIGNED/UNSIGNED

Question 1

5 INT types

Answer 1

• BIG INT
• INT
• MEDIUMINT
• SMALLINT
• TINYINT

Question 2

• How bytes of storage does BIGINT have
• Unsigned range for BIGINT
• Signed Range for BIGINT

Answer 2

• 8 bytes of storage
• Unsigned : 0 to 2^62-1
• Signed: -2^63 to

Question 3

• How bytes of storage does INT have
• Unsigned range for INT
• Signed Range for INT

Answer 3

• 4 bytes of storage
• Unsigned : 0 to 4.3 Billion
• Signed: -2.1 billion to 2.1 billion

Question 4

• How bytes of storage does MEDIUMINT have
• Unsigned range for MEDIUMINT
• Signed Range for MEDIUMINT

Answer 4

• 3 bytes of storage
• Unsigned : 0 to 16.7 million
• Signed: -8.4 million to 8.4 million

Question 5

• How bytes of storage does SMALLINT have
• Unsigned range for SMALLINT
• Signed Range for SMALLINT

Answer 5

• 2 bytes of storage
• Unsigned : 0 to 65,535
• Signed: -32,798 to 32,767

Question 6

• How bytes of storage does TINYINT have
• Unsigned range for TINYINT
• Signed Range for TINYINT

Answer 6

• 1 bytes of storage
• Unsigned : 0 to 255
• Signed: -128 to 127

Question 7

What is referential integrity?

Answer 7

Ensures consistency/correctness of relationships between tables by enforcing foreign key constraints

Question 8

What are the four types of referntial integrity?

Answer 8

• RESTRICT
• SET NULL
• SET DEFAULT
• CASCADE

Question 9

RESTRICT

Answer 9

Rejects whatever if violating the ref integrity

Question 10

SET NULL

Answer 10

Sets foreign key values to NULL, when records are deleted or updated

Question 11

SET DEFAULT

Answer 11

sets foreign key values to the default value when records are deleted or updated

Question 12

function: CONCAT()

Answer 12

used for concatenating multiple strings together into a single string.

EX: SELECT CONCAT(‘Bean’, ‘ ‘, ‘Toast’)

• This produces a result of: Bean Toast
• you specify the string in quotes with any spaces denoted by an empty space in quotes

Question 13

CASCADE

Answer 13

propagates changes to related records

Question 14

function: CONV()

Answer 14

used for converting from one number base to another number base

EX: SELECT CONV(‘10’,2,10)

• this produces a result of 2
• You first specifiy in quotes the current number you want to convert, then the current base of the numberm then the base you want the number you want to be converted to

Question 15

function: SUBSTRING()

Answer 15

used for extracting a substring from a string.
ex:

Question 16

TRUNCATE

Answer 16

for deleting all rows from a table
(Youre deleting the content inside of the table, not the structure (columns) of the table
TRUNCATE deletes everything at once, DELETE goes ROW by ROW

Question 17

SIGNED

Answer 17

default value, allows both pos and neg numbers

Question 18

UNSIGNED

Answer 18

attribute for ensuring that an INTEGER only can store non negative values