SQL: A Commercial Database Language Flashcards
The name SQL is derived from
Structured Query Language
Sequel was originally called ______.
SEQUEL ( for Structure English QUEry Language).
What does it mean that SQL is a comprehensive database language?
It has statements for data defintion, query and update (in this sense it is both a DDL and a DML). In addiotion, it has facitilities for defining views, for specifying security anduthorization, for defining integrity constaints, and for specifying transaction controls. It also has rules for embedding SQL statements into a general-purpose programming language.
The SQL language provides a high-level declarative language interface meaning
The SQL language provides a high-level declarative language interface meaning the user specifies what the result is to be and leaves the decisions on how to execute the query to the DBMS.
The terms table, row, and column are used in SQL in documents for ____, ____ and ____ respectively.
table = relation
row = tuple
column = attribute
is SQL case sensitive?
No. It treats upper and lower case letters as the same letter. Only inside quotes does SQL make a distinction between upper and lower case letters.
The concept of ____ is used in SQL to group together constructs that belong to the same database application
schema
A schema includes ______ such as tables constraints, view, domains, authorization grants and other constructs.
schema elements
A SQL schema is identified by a schema name and includes an _______ to indicate the user who owns the scema. In addition, it can include _______ for every element in the schema.
authorization identifier ; descriptors
Write a SQL statement that creates a schema called COMPANY owned by a user with authorization identifier JSMITH
CREATE SCHEMA COMPANY AUTHORIZATION JSMITH;
The concept of a ______ is used in SQL to denote a named collection of schemas.
catalog
A catalog always contains a special schema called _____. This schema provides information on all the schemas in the catalog and all the element descriptors in these schemas.
INFORMATION_SCHEMA
True or False: Some elements (e.g. domain definitions) can be shared by schemas in the same catalog.
Integrity constraints can be defined between relations only if they do not exist in schedmas of the same catalog
True.
False. Integrity constraints can be defined between relations only if they exist in schemas of the same catalog.
What data types are included in SQL?
numerics, character-string, bit-string, data and time.
What are the numeric types in SQL and how are they specified?
What are the character string data types in SQL and how are they specified?
What are the bit string data types in SQL and how are they specified?
What are the data and time data types in SQL and how are they specified?
How do you create a domain in SQL on SSN_TYPE to be a character data type consisting of 9 characters?
CREATE DOMAIN SSN_TYPE AS CHAR(9);
A create domain statement can have an optional _______ and an optional
_____________.
.
default specification ; list of constraints
What does the CREATE TABLE command do?
The CREATE TABLE command is used to specify a new relation, its attributes, their data types and constraints.
The key, entity integrity and referential integrity constraints can be specified within a CREATE TABLE statement or later using the ALTER TABLE command.
Create a relation named department with attributes: DNAME, DNUMBER, MGRSSN, MGRSTARTDATE. The primary key is DNUMBER and the MGRSSN and EMPLOYEE are foreign keys. DNAME is a secondary key (unique). Write a command to create this relation.
A constraint can be given a name following the keyword ________. Giving names to constraints, is:
A) optional but is a good practice to follow.
B) necessary and a bad practice to follow.
C) unnecessary but a bad practice to follow.
D) necessary and a good practice to follow.
CONSTRAINT;
A) optional but is a good practice to follow.
True or False: The names of some of the constraints within a particular can match (not unique).
False. The names of all constraints withing a particular schema must be unique. A constraint name can be used to drop a constraint (and replace it later by another constraint).
What is SQL’s default policy for any modificiations that violate the referential integrity constraint?
Any modifications violating the referential integrity constraints is rejected by the system.
In SQL, the schema designer can specify the action of a referenced primary key value, by attaching a referential triggered action clause to a foreign key constraint.
What are three options?
SET NULL, CASCADE, SET DEFAULT
In SQL, the schema designer can specify the action of a referenced primary key value, by attaching a referential triggered action clause to a foreign key constraint.
The options include SET NULL, CASCADE, SET DEFAULT.
They can be qualified on either ______ or ______.
.
ON DELETE ; ON UPDATE
How would you specify a constraint called EMPPK that enforces for the EMPLOYEE relation’s primary key is SSN?
How would you specify a constraint called EMPSUPERFK that enforces for the EMPLOYEE relation’s primary foreign key is SUPERSSN (references relation EMPLOYEE), sets the value to null on delete, and cascades the changes on update?
How would you specify a constraint called EMPDEPTFK that enforces for the EMPLOYEE foreign key references DEPARTMENT, on delete sets the value to the default value of 4, and on update cascades the changes?
How would you specify a constraint called EMPDEPTFK that enforces for the EMPLOYEE foreign key references DEPARTMENT, on delete sets the value to the default value of 4, and on update cascades the changes?
Can the name of a schema be explicitly specified in the definition of a table?
Yes
CREATE TABLE COMPANY.DEPARTMENT….
A schema can be deleted using the _______ command. It has two options: _____ and _____.
DROP SCHEMA; CASCADE; RESTRICT
For DROP SCHEMA, what happens if RESTRICT is specified ?
The operation will fail if the schema if nonempty; otherwise it will be succeed and the schema will be destroyed.
For DROP SCHEMA, what happens if CASCADE is specified ?
If CASCADE is spedicifed the operation will always succeed.
DROP SCHEMA COMPANY CASCADE;
A table can be deleted using the _________ command. Two options: _____ and _____.
DROP TABLE, CASCADE, RESTRICT
For DROP TABLE, what happens if RESTRICT is specified ?
If RESTRICT is specified, the operation will succeed only if the table is not referenced in any constraints or views.
For DROP TABLE, what happens if CASCADE is specified ?
If CASCADE is specified, the operation will always succeed and propogate to all constraints and views that reference the table.
DROP TABLE DEPENDENT CASCADE;
How do you add a column to relation EMPLOYEE with attribute name JOB and domain of variable char of length 12?
ALTER TABLE COMPANY.EMPLOYEE ADD JOB VARCHAR(12);
ALTER TABLE COMPANY.EMPLOYEE ADD JOB VARCHAR(12) NOT NULL;
What does this command do?
This command creates a new attribute for relation EMPLOYEE called JOB which is a character string with variable length up to 12 NOT NULL forces this attribute to have a value. A DEFAULT clause can also be specified. If no such clause is specified the new attribute will have NULLs in all the tuples of the relation after the command is executed.
How to drop a column called ADDRESS from relation EMPLOYEE with so that the deletion is always successful and propogates to all constraints and views that referencee the column?
ALTER TABLE COMPANY.EMPLOYEE DROP ADDRESS CASCADE;
When dropping a column, what is the difference between CASCADE and RESTRICT?
The CASCADE option: all constraints and views that reference the column are dropped from the schema.
The RESTRICT option. the command is successful only if no views or constraints reference the column.
How would you drop a default clause in a column called MGRSSN for relation DEPARTMENT in the COMPANY SCHEMA?
ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN DROP DEFAULT;
How would you add a default clause to a column called MGRSSN in relation DEPARTMENT in the COMPANY schema such that the default MGRSSN is now ‘33344555’?
Adding a default clause in a column:
ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN SET DEFAULT ‘33344555’;
What must a constraint have in order to be dropped?
To be dropped a constraint must have a name.
Write the command that drops the constrain from relation EMPLOYEE called EMPSUPERFK such that all foreign key definitions that reference the candidate key will be dropped too.
ALTER TABLE COMPANY.EMPLOYEE DROP CONSTRAINT EMPSUPERFK CASCADE;
When dropping a CONSTRAINT, CASCADE and RESTRICT have meaning only if _____________.
the constraint is specified, all the foreign key definitions that reference the candidate key will be dropped too.
Write a command that adds a constraint called EMPSUPERFK that enforces the foreign key SUPERSSN references an EMPLOYEE.
ALTER TABLE COMPANY.EMPLOYEE ADD CONSTRAINT EMPSUPERFK FOREIGN KEY SUPERSSN REFERENCES EMPLOYEE;
True or False: When adding a constraing using ALTER TABLE, the constraint to be created can be named or unnamed.
True.
What is the problem with these table definitions?
The problem is taht either EMPLOYEE or DEPARTMENT table has to be defined first.
In both cases, the system will issue an error message.
What is a possible solution to the problem in this code?
The chicken and egg problem in this code can be solves by postponing the foreign-key constraint in the first table. For instance, if CREATE TABLE EMPLOYEE is executed first, we should not have the foreign key clause FOREIGN KEY (DNO) … in the statement.
After CREATE TABLE DEPARTMENT has been process, we can add the desired constraint to employee using the ALTER TABLE command:
If we want to populate our COMPANY database, we face another chicken and egg problem because insertions of EMPLOYEE’s with non NULL DNO’s will be rejected because it violates the foreign key constraint:
MRGSSN references EMPLOYEE(SSN). What is a solution to this problem?
One solution is to initially replace DNO component in all tuples to be inserted in the EMPLOYEE relation with NULL. Then, when DEPARTMENT is populated with appropriate tuples, we can scan relation EMPLOYEE and replace NULLs with valid department number. This solution is awkward and error-prone. A better solution is to use transactions and deferred checking of integrity constraints.
SQL has one basic statement for retrieving information from a database; the _______.
SELECT statement
The SELECT statement in SQL is (same/ not same) as the SELECT operation of relational algebra.
Not the same. The Important distinction between SQL and the formal relational model : SQL allow a table (relation) to have two or more tuples that are identical in all their attribute values.
A SQL relation (table) is a multi-set (sometimes called a _____) of tuples; it is not a set of tuples.
bag
SQL relations can be constrained to be sets by specifying PRIMARY KEY or UNIQUE attributes in a table definition or by USING _____ option in a query.
DISTINCT
In a SELECT-FROM-WHERE block, SELECT takes an <attribute> as an argument. what does attribute list refer to ?</attribute>
attibute list is a list of attribute names whose values are to be retrieved by the query
In a SELECT-FROM-WHERE block, FROM takes a
as an argument. what does table list refer to ?
table list is a list of the relation names required to process the query.
In a SELECT-FROM-WHERE block, WHERE takes a as an argument. What does condition refer to ?
cition is a conditional coolean expression that identifies the tuples to be retrieved by the query.
What is a SELECT-FROM-WHERE block?
SELECT <attribute></attribute>
FROM
WHERE <condition></condition>
Write the SQL query that retreives the birthdate and address of the employee(s) whose name is ‘John Smith’.
SELECT BDATE, ADDRESS
FROM EMPLOYEE
WHERE FNAME=’John’ AND
LNAME=’Smith’;
Compare SELECT-PROJECT pair of relation algebra operations to SQL equivalent.
THE SELECT-clause specifies the projection attributes and the WHERE-CLAUSE specifies the selection condition. However the result of a SQL SFW query may contain duplicate tuples.
Write the query that retrieves the name and address of all employees who work for the ‘Research’ department.
SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME=’Research’ AND DNUMBER=DNO;
The WHERE-clause specifies the selection condition (corresponding to the selection operation in relational algebra) and a join condition (corresponding to the join operation in relational algebra).
In SQL, we can use the same name for two (or more) attributes as long as the attributes are in _______.
different relations
A query that refers to two or more attributes with the same name must qualify the attrivute name with the relation name by _____________.
prefixing the relation name the the attribute name.
Write the SQLquery that lists the department names and their locations.
SELECT DEPARTMENT.DNAME, DEPT_LOCATIONS.DLOCATION
FROM DEPARTMENT, DEPT_LOCATIONS
WHERE DEPARTMENT.DNUMBER=DEPT_LOCATIONS.DNUMBER;
Some queries need refer to the same relation twice. In this case, _____ are given to the relation name.
aliases
Write the Query: For each employee, retrieve the employee’s name, and the name of his or her immediate supervisor.
SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.SUPERSSN=S.SSN
It is possible to rename any attrivute that appear in the result of a query by adding the qualifier _____ followed by the desired new name.
AS
The _____ construct can be used to alias both attribute and relation names, and it can appear in both the SELECT and FROM clause of a query.
AS
What does a missing WHERE-clause indicate?
A missing WHERE-clause indicates no condition; hence, all tuples of the relations in the FROM-clause are selected. This is quivalent to a TRUE condition in the WHERE clause.
Write the SQL query: Retrieve the SSN values for all employees.
SELECT SSN
FROM EMPLOYEE
If more than one relations is specified in the FROM-clause and there is no join condition, then the ______ of tuples is selected.
CARTESIAN PRODUCT
Write the SQL query that retrieves all combinations of an EMPLOYEE SSN and a DEPARTMENT name.
SELECT SSN, DNAME
FROM EMPLOYEE, DEPARTMENT
To retrieve all the attribute values of the selected tuples, a ____ is used, which stands for all the attributes.
*
Write the query that retrieves all the attribute values of the EMPLOYEE tuples for employees who work in department number 5.
SELECT *
FROM EMPLOYEE
WHERE DNO=5;
Write the query that retrieves all the attributes of an EMPLOYEE and all the attributes of the DEPARTMENT he/she works in for every employee of the ‘Research’ department.
SELECT *
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME=’Research’ AND DNO=DNUMBER;
Write the query that computes the CROSS PRODUCT of the EMPLOYEE and DEPARTMENT relations.
SELECT *
FROM EMPLOYEE, DEPARTMENT
To eliminate duplicate tuples in a query result, the keyword _____ in the _____ clause is used.
DISTINCT; SELECT
a query with SELECT ______ eliminates deuplicates, whereas a query with SELECT ______ (which is equivalent to a simple SELECT) does not
DISTINCT; ALL
Write the query to retrieve the salary of every employee
SELECT SALARY
FROM EMPLOYEE
Write a query to retrieve every distinct salary value of an employee
SELECT DISTINCT SALARY
FROM EMPLOYEE
What are the 3 set operations that SQL includes:
UNION - union operator
EXCEPT - set difference
INTERSECT - set intersection
The resulting relations of these set operations are sets of typles; duplicate tuples are eliminated from the result.
The set operations apply only to _________.
union compatible relations
union compatible relations
the two relations must have the same number of attributes and corresponding attributes must be of compatible data types.
The ____ comparison operator is used to compare partial strings.
LIKE
Two reserved characters are used:’%’ replaces and arbitrary number of characters, and ‘_’ replaces a single arbitrary character.
Query: Retrieve all employee na,es whose addresss is in Houseton, Texas. here, the value of the ADDRESS attribute must contain the substring ‘Houston, TX’.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE ADDRESS LIKE‘%Houston, TX%’;
Query: Retrieve all employee names who were born during the 1950s.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE BDATE LIKE‘_ _ _ _ _ _ _ 5 _ ‘;
Here, 5 must be the 9th character of the string (according to our format for date), so the BDATE value is ‘_ _ _ _ _ _ _ 5 _’, with each underscore as a placeholder for a single arbitrary character. (no spaces between).
The ____ operator allows use to get around the face that each value is considered atomic and indivisible; hende, in SQL, character string attribute values are not atomic.
LIKE
The standard arithmetic operators ( __, __, __, __) can be applied to numeric values or attributes with numeric domains.
+, -, *, /
Query: Show the effect of giving all employee salaries of employees who work on the ‘ProductX’ project a 10% raise.
SELECT FNAME, LNAME, 1.1 * SALARY
FROM EMPLOYEE, WORKS_ON, PROJECT
WHERE SSN = ESSN AND PNO = PNUMBER AND PNAME = ‘ProductX’;
For string data types, the ____________ can be used in a query to append two string values.
concatenate operator (||)
Query: Retrieve the names of employees in the form:
first_name/blank_space/last_name
SELECT FNAME || ‘ ‘ || LNAME AS NAME
FROM EMPLOYEE
A comparison operator that can be used for convenience is
BETWEEN
Query: Retrieve all employees in department number 5 whose salary is between $30,000 and $40,000.
SELECT *
FROM EMPLOYEE
WHERE DNO = 5 AND (SALARY BETWEEN 30000 AND 40000);
The _________ clause is used to sort the tuples in a query result based on the values of some attribute(s).
ORDER BY
Query: Retrieve a list of employees and the projects each works in, ordered by the employee’s department name, and within each department ordered alphabetically by employee last name, first name.
SELECT DNAME, LNAME, FNAME, PNAME
FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT
WHERE DNUMBER=DNO AND SSN=ESSN AND PNO=PNUMBER
ORDER BY DNAME,LNAME,FNAME;
The default order is in _______ of values
ascending order
To specify a descending order we use the keyword ______.
DESC
The keyword ____ can be used to explicity specify ascending order, even though it is the default.
ASC
Query: Retrieve a list of employees and the projects each works in, ordered by the empoyee’s department name in descending order, and within each department ordered alphabetically by employee last name, first name.
SELECT DNAME, LNAME, FNAME, PNAME
FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT
WHERE DNUMBER = DNO AND SSN = ESSN AND PNO = PNUMBER
ORDER BY DNAME DESC, LNAME ASC, FNAME ASC;
In SQL it is possible to use an explicit (enumerated) set of values in the Where clause of a query. Such a set is enclosed in _______ and the keyword ___ is used.
parentheses, IN
Query: Retrieve the social security number of employees who work on project number 1,2, or 3.
SELECT DISTINCT ESSN
FROM WORKS_ON
WHERE PNO IN (1, 2, 3);
_____ in databases are used to represent a missing value.
NULLs
A NULL for an attribute A in a tuple t can have different meaning. List 3:
- The value is unknown
- The value exists but is not available.
- Attribute A does not apply to tuple t.
SQL (does/ does not) distinguish between the different meanings of NULL.
does not. SQL considers each NULL value is distinct from other NULL values.
SQL allows queries that check whether a value is NULL. SQL uses ____ or ____ (instead of using = and <>) to compare NULLs. Since in SQL each NULL value is distinct from other NULL values, equality comparison is not appropriate.
IS; IS NOT
Query: Retrieve the names of all employees who do not have supervisors.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE SUPERSSN IS NULL;
SQL uses a 3-valued logic with values _____, _____, and ______ instead of the standard 2 valued logic with values TRUE and FALSE. When a NULL is involved in an atomic comparison in SQL, the result is considered to be ______.
TRUE (T); FALSE (F); UNKNOWN (U); UNKNOWN (U)
Truth table: AND for 3-valued logic.
Truth table: OR for 3-valued logic.
Truth table: NOT for 3-valued logic.
When a join condition is specified, tuples with null values for the join attributes are (included/not included) in the result. This rule does not apploy to OUTER JOINs.
not included
The concept of a joined table allows users to specify a “joined relation” in the ______ of a query.
FROM-clause
What are some of the different types of joins a user can specify in the FROM-clause?
regular “theta” JOIN, NATURAL JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, CROSS JOIN
Query: Retrieve the first names, last names, and addresses of employees in the ‘Research’ department using joins.
SELECT FNAME, LNAME, ADDRESS
FROM (EMPLOYEE JOIN DEPARTMENT ON DNUMBER = DNO)
WHERE DNAME = ‘Research’;
In ______ no join condition is specified. The implicit equijoin condition involves each pair of attributes with the same names in both relations.
natural join
Query: Retrieve the employees’ first names, last names, and addresses who work in the Research department using natural join.
SELECT FNAME, LNAME, ADDRESS
FROM (EMPLOYEE NATURAL JOIN DEPARTMENT AS DEPT(DNAME, DNO, MSSN, MSDATE))
WHERE DNAME=’Research’;
Query: Retrieve the names of employees and their supervisors including the employees who have no supervisor using left outer join.
SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME
FROM (EMPLOYEE E LEFT OUTER JOIN EMPLOYEE S ON E.SUPERSSN=S.SSN);
Query: Use nested join specifications to retrieve the addresses of the managers that manage projects in ‘Stafford’.
SELECT ADDRESS
FROM ((PROJECT JOIN DEPARTMENT ON DNUM=DNUMBER) JOIN EMPLOYEE ON MGRSSN=SSN)
WHERE PLOCATION=’Stafford’;
A complete SELECT query, called a _________ can be specified within the WHERE-clause of another query, called the ______.
nested query; outer query
Query: Retreive the name and address of all employees who work for the ‘Research’ department using nested queries.
SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE
WHERE DNO IN (
SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME=’Research’);
The nested query selects the number of the ‘Research’ department.
The outer query selects an EMPLOYEE typle if its DNO value is in the result of the nested query.
The comparison operator IN compares a value v with a set (or multi-set) of values V, and evaluates to TRUE if v is one of the elements in V.
Query: Make a list of all project numbers for projects that involve an employee whose last name is ‘Smith’ as a worker or as a manager of the department that controls the project.
SELECT DISTINCT PNUMBER
FROM PROJECT
WHERE PNUMBER IN
(SELECT PNUMBER
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM=DNUMBER AND MGRSSN=SSN AND‘Smith’)
OR PNUMBER IN
(SELECT PNO
FROM WORKS_ON, EMPLOYEE
WHERE ESSN=SSN AND LNAME=’Smith’);
To compare a tuple of values using IN comparison, place the values in _____.
parentheses
Query: Select the social security number of all employees who work the same (project, hours) combination on a project that the employee whose SSN is ‘123456789’ works on using tuple comparison and IN.
SELECT DISTINCT ESSN
FROM WORKS_ON
WHERE (PNO, HOURS) IN
(SELECT PNO, HOURS
FROM WORKS_ON
WHERE ESSN = ‘123456789’);
Query: retrieve the names of the employees whose salary is greater than the salary of all the employees in department number 5.
SELECT LNAME, FNAME
FROM EMPLOYEE
WHERE SALARY > ALL (SELECT SALARY
FROM EMPLOYEE
WHERE DNO=5);
Abiguity can arist among attribute names if the same attribute exists in relations in the FROM clauses of both the outer query and a nested query. What are the two rules we use to deal with this ambiguity?
- A reference to an unqualified attribute refers to the relation declared in the innermost nested query.
- To refer to an attribute of a relation specified in an outer query, we can specify and refer to an alias for that relation.
Attributes SALARY and DNO in the nested query refer to which relation EMPLOYEE ?
Attributes SALARY and DNO in the nested query refer to the EMPLOYEE relation declared in the nested query.
If a condition in the WHERE-clause of a nested query references an attribute of a relation declare in the outer query, the two queries are said to be ______.
correlated
The result of a correlated ______ is different for each tuple (of combination of tuples) of the relation(s) of the _____.
nested query; outer query
We can understand a correlated query by considering that the nested query is evalued ____ for each tuple (or combination of tuples) of the out query.
once
Query: Retrieve the name of each employee who has a dependent with the same first name and sex as the employee.

In general, can a query with nested SELECT-FROM-WHERE blocks that uses the IN comparison operator be expressed using a single query block?
Yes. Example: Retrieve the name of each employee who has a dependent with the same first name and sex as the employee.

The _____ function in SQL is used to check whether the result of a correlated nested query is empty (contains no tuples) or not.
EXISTS
Query: Retrieve the name of each employee who has a dependent with the same first name and sex as the employee using EXISTS..
The function ________ can also be used. EXISTS(Q) returns ___ if there is at least one tuple in the result of Q, and ____ otherwise.
______ returns TRUE if there are no tuples in the result of Q, and FALSE otherwise.
NOT EXISTS; TRUE; FALSE; NOT EXISTS(Q);
Query: Retrieve the names of employees who have no dependents using NOT EXISTS.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE NOT EXISTS (SELECT *
FROM DEPENDENT
WHERE SSN=ESSN);
Query: List the names of managers who have at least one dependent using EXISTS.

Query: Retrieve the names of each employee who works on all the projects using NOT EXISTS and EXCEPT.

The SQL function _______ returns TRUE is there are no duplicates in the result of a query Q; otherwise it returns FALSE.
UNIQUE(Q)