Databases - Test 2, Quiz 3 - SQL
What is the indicator that a union should be used?
"or"
What two reserved characters are used in conjunction with LIKE?
% (or *) and _
What arithmetic operators are defined for SQL?
+, -, *, /
How do you specify what an attribute value should default to?
<attributeName>... DEFAULT <value>
What is the syntax of ALIAS?
<tableName>.<attributeName>
Define a nested query
A complete SELECT query specified in the WHERE clause of another query
Define correlated query
A nested query whose WHERE clause references a an attribute of a relation declared in the outer query
When creating a table in SQL, how do designate that an attribute should be unique?
Attribute declaration ... UNIQUE(<attributeName>)
When creating a table in SQL, how do you designate how many characters an attribute may have?
CHAR(num)
What are the aggregate functions in SQL?
COUNT, SUM, MAX, MIN, AVG
In SQL, what is the syntax for creating a table?
CREATE <tableName> (<attributeName varType (NOT NULL), .. )
What are the 3 essential utilities of SQL?
CREATE, DROP, ALTER
What does EXISTS do?
Checks whether the result of a correlated nested query is empty
What does LIKE do?
Compares partial strings.
Do a COUNT practice problem.
Cool
Do a LIKE practice problem with % or *
Cool
Do a * practice problem.
Cool.
Do a DELETE PROBLEM.
Cool.
Do a GROUP BY practice problem.
Cool.
Do a LIKE practice problem with _
Cool.
Do a MAX practice problem.
Cool.
Do a MIN practice problem.
Cool.
Do a NULL value practice problem.
Cool.
Do a SUM practice problem.
Cool.
Do a practice problem involving CONTAINS.
Cool.
Do a practice problem involving a nested query.
Cool.
Do a practice problem involving nested queries.
Cool.
Do an AVG practice problem.
Cool.
Do an EXISTS practice problem
Cool.
Do an INSERT practice problem
Cool.
Do an UPDATE practice problem.
Cool.
Do an arithmetic operator practice problem.
Cool.
Do an explicit set practice problem.
Cool.
Which relational algebra operator does CONTAINS work similarly to?
DIVIDE
What does DROP SCHEMA <tableName> CASCADE mean?
Drop everything
What does DROP TABLE DEPENDENT RESTRICT mean?
Dropped if this table is not referenced by any other constraints such as foreign key or views.
What does DROP SCHEMA <tableName> RESTRICT
Drops table if not referenced by any other constrains.
How do you designate a foreign key when creating a table in SQL?
FOREIGN KEY (<attributeName) REFERENCES <tableName>(<attributeName>)
What clause is the GROUP BY clause placed after?
FROM or WHERE
How do you specify that an attribute should not be able to have a NULL value?
Follow that with a NOT NULL
What does GROUP BY do?
Groups tuples together that have the same value for a specified attribute.
What are the SQL commands to modify the database?
INSERT, DELETE, MODIFY
What should be noted regarding NULL values and join conditions?
If a join condition is specified, tuples with NULL values for the join attributes are not specified in the result.
What does INSERT do?
Inserts one or more tuples into a table.
What relational algebra operation does FROM <tableName>, ..., <tableName> represent?
JOIN
Define SELECT <attribute list>
Like PROJECT
What does UPDATE do?
Modifies attribute values of one or more selected tuples.
What does SQL stand for?
Structured query language
What is an explicit set?
An enumerated set of values in the WHERE clause.
Do a HAVING practice problem.
cOOL.
The result of a correlated nested query is ___
different for each tuple of the relation(s) in the outer query
Each NULL is ___
different.
Define FROM <table list>
specifies tables to query against
Define WHERE <condition>
specifies the conditions that
The number of tuples deleted by DELETE depends on ___
the number of tuples that match the WHERE condition
When using ORDER BY, what is the default ordering?
ASC
What are the two keywords used in conjunction with ORDER BY?
ASC and DESC
What does ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12) do?
Adds another column to table.
What does DROP TABLE DEPENDENT CASCADE mean?
All related views are also dropped.
What is the use of ALIAS?
Allows specification of which table attributes with the same name belong to.
Define a correlated query
Nested query references an attribute of a relation declared in the outer query
Define independent query.
Nested query which does not reference an attribute of a relation declared in the outer query
What is important to note in regards to constraints specified in DDL commands and updates applied?
Only the constraints specified in the DDL commands are automatically enforced by the DBMS when updates are applied to the database.
How do you designate a primary key when creating a table?
PRIMARY KEY(<attribute>)
What does DELETE do?
Removes tuples from a table.
In the context of LIKE, what does _ do?
Replaces a single arbitrary character
In the context of LIKE, what does % or * do?
Replaces an arbitrary number of characters
What does NOT do?
Returns the opposite boolean value.
What is the basic SQL statement for retrieving information from a database?
SELECT
What are the only two mandatory clauses in SQL?
SELECT, FROM
What are the 3 important SQL constructs?
SELECT, FROM, WHERE
What are the six SQL clauses allowable?
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
What is a * used for?
Selecting all attributes in a selected tuple.
What does the IN keyword do in ...WHERE DNO IN (... ?
Selects a tuple if it contains the DNO value
What does ORDER BY do?
Sort tuples in a query result based on value of specified tuple(s)
Define outer query
The query in whose WHERE clause another query is nested
What does the "structured" mean in SQL?
The syntax must be specific.
Which set operations exist in SQL?
Union, minus, intersect
What does HAVING do?
Used in conjunction with GROUP BY; retrieves only those subgroups which meet a condition.
DELETE is used in conjunction with what?
WHERE
How do you check whether a value is NULL in the WHERE clause?
WHERE <attributeName> IS NULL
What clauses is UPDATE used in conjunction with?
WHERE, SET
Can you name constraints?
Yes
Can INSERT specify a NULL value for an attribute?
Yes.