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