Databases - Test 2, Quiz 3 - SQL

Réussis tes devoirs et examens dès maintenant avec Quizwiz!

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.


Ensembles d'études connexes

Chapter 25: Growth and Development of the Preschool Child

View Set

Part 05: You Make The Decision: Leading

View Set

Latin America Physical and Political Features

View Set