Advanced SQL Flashcards
(129 cards)
1
Q
3 interpretations of null value
A
value unknown, value unavailable or withheld, not applicable attribute
2
Q
unknown value
A
exists but is not known
A person’s date of birth is not known, so it is represented by NULL in the database.
3
Q
Unavailable or withheld value
A
exists but is purposely withheld.A person has a home phone but does not want it to be listed, so it is withheld and represented as NULL in the database.
4
Q
Not applicable attribute
A
An attribute LastCollegeDegree would be NULL for a person who has no college degrees because it does not apply to that person.
5
Q
sql and meanings of NULL
A
It is often not possible to determine which of the meanings is intended; for example, a NULL for the home phone of a person can have any of the three meanings. Hence, SQL does not distinguish between the different meanings of NULL.
6
Q
each individual NULL value is considered to be
A
different from every other NULL value in the various database records
7
Q
When a NULL is involved in a compari- son operation, the result is considered to be
A
UNKNOWN (it may be TRUE or it may be FALSE)
8
Q
SQL uses a ______ logic with values ____
A
three valued
true false unknown
9
Q
In select-project-join queries, the general rule is that only those combinations of tuples that evaluate the logical expression in the WHERE clause of the query to ___ are selected
A
true
10
Q
Tuple combinations that evaluate to FALSE or UNKNOWN are
A
not selected
11
Q
SQL allows queries that check whether an attribute value is NULL. Rather than using = or to compare an attribute value to NULL, SQL uses
A
comparison operators IS or IS NOT
because SQL considers each NULL value as being distinct from every other NULL value, so equality comparison is not appropriate.
12
Q
Retrieve the names of all employees who do not have supervisors.
A
SELECT Fname, Lname
FROM EMPLOYEE
WHERE Super_ssn IS NULL;
13
Q
nested query
A
queries require that existing values in the database be fetched and then used in a comparison condition
complete select-from-where blocks within the WHERE clause of another query (outer query)
14
Q
comparison operator IN
A
compares a value v with a set (or multiset) of values V and evaluates to TRUE if v is one of the elements in V.
15
Q
The first nested query selects the project numbers of projects that have an employee with last name ‘Smith’ involved as manager, while the second nested query selects the project numbers of projects that have an employee with last name ‘Smith’ involved as worker. In the outer query, we use the OR logical connective to retrieve a PROJECT tuple if the PNUMBER value of that tuple is in the result of either nested query.
A
SELECT DISTINCT Pnumber
FROM PROJECT
WHERE Pnumber IN
(SELECT Pnumber
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND
Lname = 'Smith' )
OR
Pnumber IN
(SELECT Pno
FROM WORKS_ON, EMPLOYEE
WHERE Essn = Ssn AND Lname = 'Smith' );
16
Q
If a nested query returns a single attribute and a single tuple, the query result will be a
A
single (scalar) value
17
Q
in cases of single value results its permissible to
A
use = instead of IN for the comparison operator
18
Q
nested query will return
A
table (relation), which is a set or multiset of tuples
19
Q
SQL allows the use of tuples of values in comparisons by
A
placing them within parentheses.
20
Q
query will select the Essns of all employees who work the same (project, hours) combination on some project that employee ‘John Smith’ (whose Ssn = ‘123456789’) works on
A
SELECT DISTINCT Essn
FROM WORKS_ON
WHERE (Pno, Hours) IN
( SELECT
FROM WHERE
Pno, Hours WORKS_ON Essn=‘123456789’ );
21
Q
In addition to the IN operator, a number of other comparison operators can be used to compare a single value v (typically an attribute name) to a set or multiset v (typ- ically a nested query)
A
=ANY (or =SOME)
ALL
22
Q
= ANY (or = SOME) operator
A
returns TRUE if the value v is equal to some value in the set V and is hence equivalent to IN. The two keywords ANY and SOME have the same effect. Other operators that can be combined with ANY (or SOME) include >, >=,
23
Q
keyword ALL
A
can also be combined with each of these operators. For example, the comparison condition (v > ALL V) returns TRUE if the value v is greater than all the values in the set (or multiset) V
24
Q
returns the names of employees whose salary is greater than the salary of all the employees in department 5
A
SELECT Lname, Fname
FROM EMPLOYEE
WHERE Salary > ALL
( SELECT
FROM
WHERE Salary EMPLOYEE Dno=5 );
25
The rule is that a reference to an unqualified attribute refers to the relation declared in
innermost nested query
26
To refer to an attribute of the PROJECT relation specified in the outer query, we
specify and refer to an alias (tuple variable) for that relation
27
Retrieve the name of each employee who has a dependent with the same first name and is the same sex as the employee.
```
SELECT E.Fname, E.Lname
FROM EMPLOYEE AS E
WHERE E.Ssn IN ( SELECT Essn
FROM DEPENDENT AS D
WHERE E.Fname=D.Dependent_name AND E.Sex=D.Sex );
```
28
Correlated nested query
Whenever a condition in the WHERE clause of a nested query references some attrib- ute of a relation declared in the outer query
29
the nested query is evaluated
once for each tuple (or combination of tuples) in the outer query
30
```
SELECT E.Fname, E.Lname
FROM EMPLOYEE AS E
WHERE E.Ssn IN ( SELECT Essn
FROM DEPENDENT AS D
WHERE E.Fname=D.Dependent_name AND E.Sex=D.Sex );
```
Explain thought process
For each EMPLOYEE tuple, evaluate the nested query, which retrieves the Essn values for all DEPENDENT tuples with the same sex and name as that EMPLOYEE tuple; if the Ssn value of the EMPLOYEE tuple is in the result of the nested query, then select that EMPLOYEE tuple
31
Retrieve the name of each employee who has a dependent with the same first name and is the same sex as the employee. (without nesting)
SELECT E.Fname, E.Lname
FROM EMPLOYEE AS E, DEPENDENT AS D
WHERE E.Ssn=D.Essn AND E.Sex=D.Sex
AND E.Fname=D.Dependent_name;
32
In general, a query written with nested select-from-where blocks and using the = or IN comparison operators can
always be expressed as a single block query
33
EXISTS
used to check whether the result of a correlated nested query is empty (contains no tuples) or not
34
result of EXISTS
Boolean value TRUE if the nested query result contains at least one tuple, or FALSE if the nested query result contains no tuples
35
```
SELECT E.Fname, E.Lname
FROM EMPLOYEE AS E
WHERE EXISTS ( SELECT *
FROM DEPENDENT AS D
WHERE E.Ssn=D.Essn AND E.Sex=D.Sex AND E.Fname=D.Dependent_name);
```
EXPLAIN THOUGHT PROCESS
For each EMPLOYEE tuple, evaluate the nested query, which retrieves all DEPENDENT tuples with the same Essn, Sex, and Dependent_name as the EMPLOYEE tuple; if at least one tuple EXISTS in the result of the nested query, then select that EMPLOYEE tuple.
36
EXISTS(Q) returns TRUE if
there is at least one tuple in the result of the nested query Q, and it returns FALSE otherwise
37
NOT EXISTS(Q) returns TRUE if
there are no tuples in the result of nested query Q, and it returns FALSE otherwise
38
Retrieve the names of employees who have no dependents. (Using exists)
```
SELECT Fname, Lname
FROM EMPLOYEE
WHERE NOT EXISTS ( SELECT *
FROM DEPENDENT
WHERE Ssn=Essn );
```
39
```
SELECT Fname, Lname
FROM EMPLOYEE
WHERE NOT EXISTS ( SELECT *
FROM DEPENDENT
WHERE Ssn=Essn );
```
EXPLAIN
or each EMPLOYEE tuple, the correlated nested query selects all DEPENDENT tuples whose Essn value matches the EMPLOYEE Ssn; if the result is empty, no dependents are related to the employee, so we select that EMPLOYEE
tuple and retrieve its Fname and Lname.
40
List the names of managers who have at least one dependent.
using exists
```
SELECT Fname, Lname
FROM EMPLOYEE
WHERE EXISTS ( SELECT *
FROM DEPENDENT
WHERE Ssn=Essn )
AND
EXISTS ( SELECT *
FROM DEPARTMENT
WHERE Ssn=Mgr_ssn );
```
41
```
SELECT Fname, Lname
FROM EMPLOYEE
WHERE EXISTS ( SELECT *
FROM DEPENDENT
WHERE Ssn=Essn )
AND
EXISTS ( SELECT *
FROM DEPARTMENT
WHERE Ssn=Mgr_ssn );
```
Explain
the first selects all DEPENDENT tuples related to an EMPLOYEE, and the sec- ond selects all DEPARTMENT tuples managed by the EMPLOYEE. If at least one of the first and at least one of the second exists, we select the EMPLOYEE tuple
42
Retrieve the Social Security numbers of all employees who work on project numbers 1, 2, or 3
use explicit set of values
SELECT DISTINCT Essn
FROM WORKS_ON
WHERE Pno IN (1, 2, 3);
43
In SQL, it is possible to rename any attribute that appears in the result of a query by
adding the qualifier AS followed by the desired new name
44
AS construct can be used to alias both
attribute and relation names, and it can be used in both the SELECT and FROM clauses
45
retrieve the last name of each employee and his or her supervisor, while renaming the resulting attribute names as Employee_name and Supervisor_name.
SELECT E.Lname AS Employee_name, S.Lname AS Supervisor_name
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn=S.Ssn;
46
joined table (or joined relation)
specify a table resulting from a join operation in the FROM clause of a query
47
retrieves the name and address of every employee who works for the ‘Research’ department
use join statement
SELECT Fname, Lname, Address
FROM (EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber)
WHERE Dname=‘Research’;
48
The attributes of a joined table are
all the attributes of the first table, EMPLOYEE, followed by all the attributes of the second table, DEPARTMENT
49
NATURAL JOIN
Names of joining attribute same
50
NATURAL JOIN EMPLOYEE TO DEPARTMENT
FROM (EMPLOYEE NATURAL JOIN DEPARTMENT)
51
INNER JOIN
default join where a tuple is included in result only if a matching tuple exists in the other relation
52
only employees who have a supervisor are included in the result; an EMPLOYEE tuple whose value for Super_ssn is NULL is excluded
SELECT Fname, Lname, Address
FROM (EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber)
WHERE Dname=‘Research’;
53
If the user requires that all employees be included
| what join
OUTER JOIN
54
join EMPLOYEE and DEPARTMENT table so even unmatched employees are included
FROM (EMPLOYEE LEFT OUTER JOIN DEPARTMENT)
55
INNER JOIN
only pairs of tuples that match the join condition are retrieved, same as JOIN
56
LEFT OUTER JOIN
every tuple in the left table must appear in the result; if it does not have a matching tuple, it is padded with NULL values for the attributes of the right table
57
RIGHT OUTER JOIN
every tuple in the right table must appear in the result; if it does not have a matching tuple, it is padded with NULL values for the attributes of the left table
58
FULL OUTER JOIN
OUTER may be omitted
| everything appears in joined table
59
If the join attributes have the same name
one can also specify the natural join variation of outer joins by using the keyword NATURAL before the operation (for example, NATURAL LEFT OUTER JOIN)
60
CROSS JOIN
CARTESIAN PRODUCT operation (see Section 6.2.2), although this should be used only with the utmost care because it generates all possible tuple combinations
61
nest join specifications
join the joining of project and department to employee
FROM ((PROJECT JOIN DEPARTMENT ON Dnum=Dnumber)
| JOIN EMPLOYEE ON Mgr_ssn=Ssn)
62
Aggregate functions used to
used to summarize information from multiple tuples into a single-tuple summary
63
Grouping is used to
create sub- groups of tuples before summarization
64
built-in aggregate functions exist:
COUNT, SUM, MAX, MIN, and AVG
65
COUNT
returns the number of tuples or values as specified in a query
66
aggregate functions can be used in what clauses
SELECT clause or in a HAVING clause
67
MAX and MIN can also be used with attributes that have
nonnumeric domains if the domain values have a total ordering among one another
68
Find the sum of the salaries of all employees, the maximum salary, the minimum salary, and the average salary
```
SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG (Salary)
FROM EMPLOYEE;
```
69
Find the sum of the salaries of all employees of the ‘Research’ department, as well as the maximum salary, the minimum salary, and the aver- age salary in this department.
```
SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG (Salary)
FROM (EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber)
WHERE Dname=‘Research’;
```
70
Retrieve the total number of employees in the company
```
SELECT COUNT (*)
FROM EMPLOYEE;
```
71
number of employees in the ‘Research’ department
```
SELECT COUNT (*)
FROM EMPLOYEE, DEPARTMENT
WHERE DNO=DNUMBER AND DNAME=‘Research’;
```
72
Count the number of distinct salary values in the database.
```
SELECT COUNT (DISTINCT Salary)
FROM EMPLOYEE;
```
if say could salary duplicates not eliminated
73
NULL values are ______ when aggregate func-
| tions are applied to a particular column (attribute).
discarded
74
The correlated nested query counts the number of dependents that each employee
has; if this is greater than or equal to two, the employee tuple is selected.
```
SELECT Lname, Fname
FROM EMPLOYEE
WHERE ( SELECT COUNT (*)
FROM DEPENDENT
WHERE Ssn=Essn ) >= 2;
```
75
Want to find the average salary of employees in each department or the number of employees who work on each project...what to do
need to partition the rela- tion into nonoverlapping subsets (or groups) of tuples. Each group (partition) will consist of the tuples that have the same value of some attribute(s), called the grouping attribute(s)
76
GROUP BY clause
specifies the grouping attributes, which should also appear in the SELECT clause, so that the value resulting from applying each aggregate function to a group of tuples appears along with the value of the grouping attribute(s).
77
For each department, retrieve the department number, the number of employees in the department, and their average salary.
SELECT Dno, COUNT (*), AVG (Salary)
FROM EMPLOYEE
GROUP BY Dno;
78
If NULLs exist in the grouping attribute,
separate group is created for all tuples with a NULL value in the grouping attribute
79
For each project, retrieve the project number, the project name, and the number of employees who work on that project.
SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE Pnumber=Pno
GROUP BY Pnumber, Pname;
80
When joining relations, grouping applies when
after the joining of two relations
81
Sometimes we want to retrieve the values of these functions only for groups that sat- isfy certain conditions. What do we use?
HAVING provides a condition on the summary information regarding the group of tuples associated with each value of the grouping attributes. Only the groups that satisfy the condition are retrieved in the result of the query.
82
For each project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on the project.
```
SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE Pnumber=Pno
GROUP BY Pnumber, Pname
HAVING COUNT (*) > 2;
```
83
```
SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE Pnumber=Pno
GROUP BY Pnumber, Pname
HAVING COUNT (*) > 2;
```
The having clause serves to chose
whole groups
84
```
SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE Pnumber=Pno
GROUP BY Pnumber, Pname
HAVING COUNT (*) > 2;
```
The where clause limits the
tuples to which functions are applied
85
For each project, retrieve the project number, the project name, and the number of employees from department 5 who work on the project.
SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE Pnumber=Pno AND Ssn=Essn AND Dno=5
GROUP BY Pnumber, Pname;
86
For each department that has more than five employees, retrieve the department number and the number of its employees who are making more than $40,000.
```
SELECT Dnumber, COUNT (*)
FROM DEPARTMENT, EMPLOYEE
WHERE Dnumber=Dno AND Salary>40000 AND
(SELECT Dno
FROM EMPLOYEE
GROUP BY Dno
HAVING COUNT (*) > 5)
```
87
SQL Query clauses
```
SELECT
FROM
[ WHERE ]
[ GROUP BY ]
[ HAVING ]
[ ORDER BY ];
```
88
CREATE ASSERTION
can be used to specify additional types of constraints that are outside the scope of the built-in relational model constraints (primary and unique keys, entity integrity, and referential integrity)
89
CREATE TRIGGER
used to specify automatic actions that the database system will perform when certain events and conditions occur
90
Example of when to create trigger
A manager may want to be informed if an employee’s travel expenses exceed a certain limit by receiving a message whenever this occurs. The action that the DBMS must take in this case is to send an appropriate message to that user. The condition is thus used to monitor the database.
91
view in SQL terminology
single table that is derived from other tables (base tables or previously defined tables which tuples are always stored physically in database)
92
view is considered what kind of table
virtual table - not existent in physical form
93
We can think of a view as a way of specifying a table that we need to
reference fre- quently, even though it may not exist physically
94
or example, referring to the COMPANY database in Figure 3.5 we may frequently issue queries that retrieve the employee name and the project names that the employee works on. Rather than having to specify the join of the three tables EMPLOYEE, WORKS_ON, and PROJECT every time we issue this query, we can
define a view that is specified as the result of these joins. Then we can issue queries on the view, which are specified as single- table retrievals rather than as retrievals involving two joins on three tables.
95
If define a view that is specified as a result of three joined tables we call the joined tables the
defining tables of the view
96
Command to specify a view is
CREATE VIEW
97
The view is given
table name (or view name), a list of attribute names, and a query to specify the contents of the view.
98
view that pulls fname, lname, pname, hours
CREATE VIEW WORKS_ON1
AS SELECT Fname, Lname, Pname, Hours
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE Ssn=Essn AND Pno=Pnumber;
99
Can specify new attribute names. create view that pulls out department name, number of employees, and total salary, grouped by department name
CREATE VIEW DEPT_INFO(Dept_name, No_of_emps, Total_sal)
AS SELECT Dname, COUNT (*), SUM (Salary)
FROM DEPARTMENT, EMPLOYEE
WHERE Dnumber=Dno
GROUP BY Dname;
100
to retrieve the last name and first name of all employees who work on the ‘ProductX’ project, we can utilize the WORKS_ON1 view and specify the query
SELECT Fname, Lname
FROM WORKS_ON1
WHERE Pname=‘ProductX’;
The same query would require the specification of two joins if specified on the base relations directly; one of the main advantages of a view is to simplify the specifica- tion of certain queries.
101
A view is supposed to be always up-to-date;
If we modify the tuples in the base tables on which the view is defined, the view must automatically reflect these changes. Hence, the view is not realized or materialized at the time of view definition but rather at the time when we specify a query on the view.
102
If we do not need a view any more
Use the DROP VIEW command to dispose of it
103
to get rid of the view V1
DROP VIEW WORKS_ON1;
104
Generally, a view update is feasible when only
one possible update on the base rela- tions can accomplish the desired update effect on the view. Whenever an update on the view can be mapped to more than one update on the underlying base relations, we must have a certain procedure for choosing one of the possible updates as the most likely one.
105
A view with a single defining table is updatable if the view attributes contain
the primary key of the base relation, as well as all attributes with the NOT NULL constraint that do not have default values specified.
106
Views that are not updatable
Views defined on multiple tables using joins
| Views defined using grouping and aggregate functions
107
alter a schema by
adding or dropping tables, attributes, constraints, and other schema elements
108
The DROP command
can be used to drop named schema elements, such as tables, domains, or constraints. One can also drop a schema
109
There are two drop behavior options:
CASCADE and RESTRICT.
110
to remove the COMPANY database schema and all its tables, domains, and other elements
DROP SCHEMA COMPANY CASCADE;
111
If the RESTRICT option is chosen in place of CASCADE, the schema is dropped only
if it has no elements in it; otherwise, the DROP command will not be executed. To use the RESTRICT option, the user must first individually drop each element in the schema, then drop the schema itself.
112
if we no longer wish to keep track of dependents of employees in the COMPANY database
DROP TABLE DEPENDENT CASCADE;
113
DROP TABLE DEPENDENT CASCADE;
| If the RESTRICT option is chosen instead of CASCADE
a table is dropped only if it is not referenced in any constraints (for example, by foreign key definitions in another relation) or views or by any other elements.
114
DROP TABLE DEPENDENT CASCADE;
what does the cascade option do
With the CASCADE option, all such constraints, views, and other elements that reference the table being dropped are also dropped automatically from the schema, along with the table itself.
115
Notice that the DROP TABLE command not only deletes all the records in the table if successful, but also removes
the table definition from the catalog
116
If it is desired to delete only the records but to leave the table definition for future use, then
DELETE command should be used instead of DROP TABLE.
117
ALTER command.
definition of a base table or of other named schema elements can be changed
118
For base tables, the possible alter table actions include
adding or dropping a column (attribute), changing a column definition, and adding or dropping table constraints
119
to add an attribute for keeping track of jobs of employees to the EMPLOYEE base relation in the COMPANY schema
ALTER TABLE COMPANY.EMPLOYEE ADD COLUMN Job VARCHAR(12);
If no default clause is spec- ified, the new attribute will have NULLs in all the tuples of the relation immediately after the command is executed; hence, the NOT NULL constraint is not allowed in this case.
120
To drop a column, we must choose either _____ or ____ for drop behavior
CASCADE or RESTRICT
121
IF CASCADE is chosen when dropping a column
all constraints and views that reference the column are dropped automatically from the schema, along with the column.
122
if RESTRICT is chosen when dropping column
command is successful only if no views or constraints (or other schema elements) reference the column.
123
the following command removes the attribute Address from the EMPLOYEE base table
ALTER TABLE COMPANY.EMPLOYEE DROP COLUMN Address CASCADE;
124
possible to alter a column definition by dropping an existing default clause
ALTER TABLE COMPANY.DEPARTMENT ALTER COLUMN Mgr_ssn DROP DEFAULT;
125
possible to alter a column definition by defining a new default clause
ALTER TABLE COMPANY.DEPARTMENT ALTER COLUMN Mgr_ssn SET DEFAULT ‘333445555’;
126
One can also change the constraints specified on a table by
adding or dropping a named constraint
127
To be dropped, a constraint must
have been given a name when it was specified
128
to drop the constraint named EMPSUPERFK
DROP CONSTRAINT EMPSUPERFK CASCADE;
129
Adding constraint to table
using the ADD keyword in the ALTER TABLE statement followed by the new constraint, which can be named or unnamed and can be of any of the table constraint types discussed.
