Professor Week 4

Question 1

What are the two main types of data integrity checks?

Answer 1

atomic that looks at the proposed field value – not null, CHECK
relative checks that compare the proposed field value to other value – unique, check (if validation refers other attributes)

Question 2

How to use Select

Answer 2

SELECT * | attributes to include
FROM tableName
WHERE condition(s);

Don’t have to use WHERE;

Question 3

Logical Operators

Answer 3

Not – reverses the result
AND – both conditions must eval to true
OR – one or the other
BETWEEN – will include values that are greater or equal to min and less than or equal to the max

Question 4

Relational Operators

Answer 4

Relational Operators
> Greater Than
>= Greater Than or Equal To
< Less Than
<= Less Than or Equal To
= Equal To
<> Not Equal To
!= Not Equal To
IN (list) Contained in comma-separated list
LIKE string Matches string pattern

Question 5

LIKE

Answer 5

to specifiy our pattern we use characters and wildcards
characters must be present
wildcards are placeholders
_ means exactly one char
% means 0 or more char

Question 6

Calculations

Answer 6

Calculations can be performed in the SELECT clause of a SELECT statement

Question 7

DML: UPDATE

Answer 7

modifies existing user data

UPDATE tableName
SET field1 = value1, fieldN = valueN
WHERE condition;

Question 8

DML: Delete

Answer 8

Removes record(s) from the table

DELETE
FROM tableName
WHERE condition(s);

Question 9

ALTER TABLE

Answer 9

to change the structure, instead of dropping it and creating it again
Add/Drop a primary key constraint
Add/Modify/Drop a column
Add/Drop/Modify a default value

cannot modify a table level constraint

Question 10

ADD | DROP Columns

Answer 10

Alter TABLE tablename
ADD | DROP COLUMN columnName;

Question 11

Altering defaults

Answer 11

Only “property” that you can alter
ALTER TABLE tableName
ALTER description SET DEFAULT default;

ALTER TABLE tableName
ALTER description DROP DEFAULT;

Question 12

ALTER TABLE Modify Column

Answer 12

Through the ALTER TABLE … MODIFY COLUMN statement, you can edit an existing
attribute’s specification, including changing the data type or adding/removing a
NOT NULL constraint or DEFAULT value
ALTER TABLE tableName
MODIFY COLUMN attribute DATATYPE [NOT NULL] [DEFAULT];