DB Exam 2

What are the wildcard operators in a LIKE?

% - zero or more characters _ - exactly one character

What are the data quality characteristics?

1. Accuracy 2. Uniqueness 3. Completeness 4. Consistency 5. Timeliness 6. Conformity CUT CAC

What are the three meanings of NULL?

1. unknown value 2. unavailable/withheld value 3. not applicable attribute

What are the WHERE logical operators?

= > < >= <= != <> IS NULL

What is a trigger?

A rule written that is activated when a record is deleted, updated, or inserted

What additional commands can be used with ALTER TABLE?

ALTER TABLE tableName ADD (columnName datatype); ALTER TABLE table_name DROP COLUMN column_name; ALTER TABLE table_name RENAME new_table_name; ALTER TABLE table_name CHANGE column_name new_column_name new_column_datatype; ALTER TABLE table_name MODIFY column_name new_datatype; ALTER TABLE table_name ADD PRIMARY KEY (column_name); ALTER TABLE table_name ADD FOREIGN KEY (column_name) REFERENCES other_table_name (column_name);

How do you change the structure of a table?

ALTER TABLE tableName...;

What two options are available for ORDER BY?

ASC DESC

What are some aggregate functions?

COUNT SUM AVG MIN MAX

What is an example of an assertion?

CREATE ASSERTION profsadvisingupto10students CHECK ( (SELECT MAX(totaladvised) FROM ( SELECT count(*) AS totaladvised FROM student GROUP BY advisorid)) < 11);

How do you create a table?

CREATE TABLE <tableName> <columns> <primaryKey> <foreignKey(s)>; CREATE TABLE tablename ( columnName datatype nullOrNotNull, primary key (columnName), foreign key (columnName) references otherTableName(columnName)

How to specify delete and update contraints?

CREATE TABLE table_name ( ... FOREIGN KEY (key) REFERENCES table ON DELETE/UPDATE CASCADE/SET NULL)

What is an example of a check clause?

CREATE TABLE table_name (column_name datatype CHECK (column_name > 100), ...)

How do you create a view?

CREATE VIEW view_name AS <select statement>;

What does UNION do?

Combines two select statements, eliminates duplicate tows

How are DBs backed up?

DB updates are logged to the recovery log, at a checkpoint, the updates are written on the disk and the recovery log is cleared

What is the delete syntax?

DELETE FROM table_name WHERE condition;

How do you eliminate duplicate rows in a query result?

DISTINCT

How do you remove a table from a db?

DROP TABLE tableName;

How do you remove a view?

DROP VIEW view_name;

What is are the three types of commands in SQL?

Data Definition Language (DDL): commands that define a database - creating, altering, and dropping tables Data Manipulation Language (DML) - commands that maintain and query Data Control Language (DCL) - commands that control security of a database

How do you grant privileges to users?

GRANT privilege ON table_name TO user;

How do you insert a row into a table?

INSERT INTO table_name VALUES (column_1_value, column_2_value); INSERT INTO table_name (column_1, column_2_ VALUES (column_1_value, column_2_value);

How do you insert results from a query into a table?

INSERT INTO table_name {select query}

How to match a null value in a where clause?

IS NULL

What is ALL used for?

In a where clause, the value must satisfy logical operator for a set of values

What is IN used for?

In a where clause, will compare a value with a set of values. Also can use NOT IN

What is HAVING?

Only applies when GROUP BY is used, used to eliminate groups from query result

When do aggregate functions apply?

Only in SELECT or HAVING

Hw do you revoke privileges?

REVOKE privilege ON table_name FROM user;

What does MINUS do?

Returns every row from first select result that is not in second select result

What does INTERSECTION do?

Returns every row that appears in result of both select statements

What are the building blocks of a SELECT statement?

SELECT FROM WHERE GROUP BY HAVING ORDER BY;

What order to the SELECT keywords get executed?

SELECT 5 FROM 1 WHERE 2 GROUP BY 3 HAVING 4 ORDER BY 6;

What are the set operators?

UNION, INTERSECTION, MINUS used to combine two select statements, but only works if they are union compatible (same number and datatypes of columns)

What is the update syntax?

UPDATE table_name SET column_name = some_value WHERE condition;

How to do INTERSECTION in MySQL?

Use IN instead

How to do MINUS in MySQL?

Use NOT IN instead

What is LIKE used for?

Used in a WHERE clause to search for a specified pattern in a column

What is GROUP BY?

Used to aggregate groups of related data in tables

What is an assertion?

a mechanism for specifying user-defined constraints

What is a data dictionary?

a repository of the db metadata

What is a view?

a virtual table, every time it is invoked, it executes a query to retrieve data from the real tables

What are the common sql data types?

char(n) varchar(n) int numberic(x,y) - x digits, y of which after decimal point date

What is included in three tier architecture?

client, application server, database server

What is included in four tier architecture?

client, web server, application server, database server

What commands are used in DDL?

create alter drop

What are the delete options?

delete restrict delete cascade delete set null delete set default

What are the two types of views?

dynamic - no data actually stored materialized - data actually stored

What are roles/groups and how do you create them?

groups contain multiple users and are assigned access privileges CREATE ROLE role; GRANT privilege ON table TO role; GRANT role TO user;

What are the strategies for updating materialized views?

immediate update lazy update periodic update

What are the threats to data security?

losing data integrity losing privacy and confidentiality losing availability

What are the benefits of modularization?

performance interoperability balanced workloads

What are the three logics in client/server model?

presentation logic processing logic storage logic

What are the two types of data quality actions?

preventive and corrective

What is a dynamic page request?

requesting a web page that is generated by a server side program or script

What is a static page request?

requesting an html document on the web server

What are the benefits of n-tier architectures?

scalability cost reduction business needs react quickly to business changes

What are the benefits of using views?

simplify query commands improve performance increase security

What are some common security vulnerabilities with sql?

sql injection attack cross site scripting attack

What is the data catalog?

the data dictionary created by DBMS

What is the referential integrity contraint?

the value of a foreign key either: 1. matches a value in the primary key of the referred relation OR 2. is null

What is the result when NULL is used in comparison?

unknown

What are the update options?

update restrict update cascade update set null update set default

What is a check clause?

used to specify a constraint on a specific column

How do you secure a db?

views integrity controls (assertions and triggers) authentication access privileges encryption

Can check clauses also compare two clumns?

yes