Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

SQL > Advanced SQL > Flashcards

Advanced SQL Flashcards

1
Q

3 interpretations of null value

A

value unknown, value unavailable or withheld, not applicable attribute

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

each individual NULL value is considered to be

A

different from every other NULL value in the various database records

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

SQL uses a ______ logic with values ____

A

three valued

true false unknown

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Tuple combinations that evaluate to FALSE or UNKNOWN are

A

not selected

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Retrieve the names of all employees who do not have supervisors.

A

SELECT Fname, Lname
FROM EMPLOYEE
WHERE Super_ssn IS NULL;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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' );
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

If a nested query returns a single attribute and a single tuple, the query result will be a

A

single (scalar) value

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

in cases of single value results its permissible to

A

use = instead of IN for the comparison operator

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

nested query will return

A

table (relation), which is a set or multiset of tuples

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

SQL allows the use of tuples of values in comparisons by

A

placing them within parentheses.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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’ );
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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 >, >=,

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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 );
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

The rule is that a reference to an unqualified attribute refers to the relation declared in

A

innermost nested query

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

To refer to an attribute of the PROJECT relation specified in the outer query, we

A

specify and refer to an alias (tuple variable) for that relation

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

Retrieve the name of each employee who has a dependent with the same first name and is the same sex as the employee.

A 
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 );
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

Correlated nested query

A

Whenever a condition in the WHERE clause of a nested query references some attrib- ute of a relation declared in the outer query

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

the nested query is evaluated

A

once for each tuple (or combination of tuples) in the outer query

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q 
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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

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)

A

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;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

In general, a query written with nested select-from-where blocks and using the = or IN comparison operators can

A

always be expressed as a single block query

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

EXISTS

A

used to check whether the result of a correlated nested query is empty (contains no tuples) or not

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

result of EXISTS

A

Boolean value TRUE if the nested query result contains at least one tuple, or FALSE if the nested query result contains no tuples

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q 
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

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

EXISTS(Q) returns TRUE if

A

there is at least one tuple in the result of the nested query Q, and it returns FALSE otherwise

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

NOT EXISTS(Q) returns TRUE if

A

there are no tuples in the result of nested query Q, and it returns FALSE otherwise

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

Retrieve the names of employees who have no dependents. (Using exists)

A 
SELECT Fname, Lname 
FROM EMPLOYEE
WHERE NOT EXISTS ( SELECT *
      FROM DEPENDENT
      WHERE Ssn=Essn );
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q 
SELECT Fname, Lname 
FROM EMPLOYEE
WHERE NOT EXISTS ( SELECT *
      FROM DEPENDENT
      WHERE Ssn=Essn );

EXPLAIN

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

List the names of managers who have at least one dependent.

using exists

A 
SELECT Fname, Lname
FROM EMPLOYEE 
WHERE EXISTS ( SELECT *
             FROM DEPENDENT
             WHERE Ssn=Essn ) 
AND
EXISTS ( SELECT *
               FROM DEPARTMENT
               WHERE Ssn=Mgr_ssn );
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q 
SELECT Fname, Lname
FROM EMPLOYEE 
WHERE EXISTS ( SELECT *
             FROM DEPENDENT
             WHERE Ssn=Essn ) 
AND
EXISTS ( SELECT *
               FROM DEPARTMENT
               WHERE Ssn=Mgr_ssn );

Explain

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

Retrieve the Social Security numbers of all employees who work on project numbers 1, 2, or 3

use explicit set of values

A

SELECT DISTINCT Essn
FROM WORKS_ON
WHERE Pno IN (1, 2, 3);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

In SQL, it is possible to rename any attribute that appears in the result of a query by

A

adding the qualifier AS followed by the desired new name

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

AS construct can be used to alias both

A

attribute and relation names, and it can be used in both the SELECT and FROM clauses

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

retrieve the last name of each employee and his or her supervisor, while renaming the resulting attribute names as Employee_name and Supervisor_name.

A

SELECT E.Lname AS Employee_name, S.Lname AS Supervisor_name
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn=S.Ssn;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

joined table (or joined relation)

A

specify a table resulting from a join operation in the FROM clause of a query

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

retrieves the name and address of every employee who works for the ‘Research’ department
use join statement

A

SELECT Fname, Lname, Address
FROM (EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber)
WHERE Dname=‘Research’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

The attributes of a joined table are

A

all the attributes of the first table, EMPLOYEE, followed by all the attributes of the second table, DEPARTMENT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

NATURAL JOIN

A

Names of joining attribute same

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
50
Q

NATURAL JOIN EMPLOYEE TO DEPARTMENT

A

FROM (EMPLOYEE NATURAL JOIN DEPARTMENT)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q

INNER JOIN

A

default join where a tuple is included in result only if a matching tuple exists in the other relation

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
52
Q

only employees who have a supervisor are included in the result; an EMPLOYEE tuple whose value for Super_ssn is NULL is excluded

A

SELECT Fname, Lname, Address
FROM (EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber)
WHERE Dname=‘Research’;

53
Q

If the user requires that all employees be included

what join

A

OUTER JOIN

54
Q

join EMPLOYEE and DEPARTMENT table so even unmatched employees are included

A

FROM (EMPLOYEE LEFT OUTER JOIN DEPARTMENT)

55
Q

INNER JOIN

A

only pairs of tuples that match the join condition are retrieved, same as JOIN

56
Q

LEFT OUTER JOIN

A

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
Q

RIGHT OUTER JOIN

A

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
Q

FULL OUTER JOIN

A

OUTER may be omitted

everything appears in joined table

59
Q

If the join attributes have the same name

A

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
Q

CROSS JOIN

A

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
Q

nest join specifications

join the joining of project and department to employee

A

FROM ((PROJECT JOIN DEPARTMENT ON Dnum=Dnumber)

JOIN EMPLOYEE ON Mgr_ssn=Ssn)

62
Q

Aggregate functions used to

A

used to summarize information from multiple tuples into a single-tuple summary

63
Q

Grouping is used to

A

create sub- groups of tuples before summarization

64
Q

built-in aggregate functions exist:

A

COUNT, SUM, MAX, MIN, and AVG

65
Q

COUNT

A

returns the number of tuples or values as specified in a query

66
Q

aggregate functions can be used in what clauses

A

SELECT clause or in a HAVING clause

67
Q

MAX and MIN can also be used with attributes that have

A

nonnumeric domains if the domain values have a total ordering among one another

68
Q

Find the sum of the salaries of all employees, the maximum salary, the minimum salary, and the average salary

A 
SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG (Salary) 
FROM EMPLOYEE;
69
Q

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.

A 
SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG (Salary) 
FROM (EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber)
WHERE Dname=‘Research’;
70
Q

Retrieve the total number of employees in the company

A 
SELECT COUNT (*)
FROM EMPLOYEE;
71
Q

number of employees in the ‘Research’ department

A 
SELECT COUNT (*)
FROM EMPLOYEE, DEPARTMENT
WHERE DNO=DNUMBER AND DNAME=‘Research’;
72
Q

Count the number of distinct salary values in the database.

A 
SELECT COUNT (DISTINCT Salary)
FROM EMPLOYEE;

if say could salary duplicates not eliminated

73
Q

NULL values are ______ when aggregate func-

tions are applied to a particular column (attribute).

A

discarded

74
Q

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.

A 
SELECT Lname, Fname 
FROM EMPLOYEE
WHERE ( SELECT COUNT (*) 
                FROM DEPENDENT
                WHERE Ssn=Essn ) >= 2;
75
Q

Want to find the average salary of employees in each department or the number of employees who work on each project…what to do

A

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
Q

GROUP BY clause

A

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
Q

For each department, retrieve the department number, the number of employees in the department, and their average salary.

A

SELECT Dno, COUNT (*), AVG (Salary)
FROM EMPLOYEE
GROUP BY Dno;

78
Q

If NULLs exist in the grouping attribute,

A

separate group is created for all tuples with a NULL value in the grouping attribute

79
Q

For each project, retrieve the project number, the project name, and the number of employees who work on that project.

A

SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE Pnumber=Pno
GROUP BY Pnumber, Pname;

80
Q

When joining relations, grouping applies when

A

after the joining of two relations

81
Q

Sometimes we want to retrieve the values of these functions only for groups that sat- isfy certain conditions. What do we use?

A

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
Q

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.

A 
SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON 
WHERE Pnumber=Pno
GROUP BY Pnumber, Pname
HAVING COUNT (*) > 2;
83
Q 
SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON 
WHERE Pnumber=Pno
GROUP BY Pnumber, Pname
HAVING COUNT (*) > 2;

The having clause serves to chose

A

whole groups

84
Q 
SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON 
WHERE Pnumber=Pno
GROUP BY Pnumber, Pname
HAVING COUNT (*) > 2;

The where clause limits the

A

tuples to which functions are applied

85
Q

For each project, retrieve the project number, the project name, and the number of employees from department 5 who work on the project.

A

SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE Pnumber=Pno AND Ssn=Essn AND Dno=5
GROUP BY Pnumber, Pname;

86
Q

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.

A 
SELECT Dnumber, COUNT (*)
FROM DEPARTMENT, EMPLOYEE
WHERE Dnumber=Dno AND Salary>40000 AND
           (SELECT Dno
            FROM EMPLOYEE
            GROUP BY Dno 
            HAVING COUNT (*) > 5)
87
Q

SQL Query clauses

A 
SELECT 
 FROM 
[ WHERE  ]
[ GROUP BY  ]
 [ HAVING  ]
[ ORDER BY  ];
88
Q

CREATE ASSERTION

A

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
Q

CREATE TRIGGER

A

used to specify automatic actions that the database system will perform when certain events and conditions occur

90
Q

Example of when to create trigger

A

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
Q

view in SQL terminology

A

single table that is derived from other tables (base tables or previously defined tables which tuples are always stored physically in database)

92
Q

view is considered what kind of table

A

virtual table - not existent in physical form

93
Q

We can think of a view as a way of specifying a table that we need to

A

reference fre- quently, even though it may not exist physically

94
Q

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

A

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
Q

If define a view that is specified as a result of three joined tables we call the joined tables the

A

defining tables of the view

96
Q

Command to specify a view is

A

CREATE VIEW

97
Q

The view is given

A

table name (or view name), a list of attribute names, and a query to specify the contents of the view.

98
Q

view that pulls fname, lname, pname, hours

A

CREATE VIEW WORKS_ON1
AS SELECT Fname, Lname, Pname, Hours
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE Ssn=Essn AND Pno=Pnumber;

99
Q

Can specify new attribute names. create view that pulls out department name, number of employees, and total salary, grouped by department name

A

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
Q

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

A

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
Q

A view is supposed to be always up-to-date;

A

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
Q

If we do not need a view any more

A

Use the DROP VIEW command to dispose of it

103
Q

to get rid of the view V1

A

DROP VIEW WORKS_ON1;

104
Q

Generally, a view update is feasible when only

A

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
Q

A view with a single defining table is updatable if the view attributes contain

A

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
Q

Views that are not updatable

A

Views defined on multiple tables using joins

Views defined using grouping and aggregate functions

107
Q

alter a schema by

A

adding or dropping tables, attributes, constraints, and other schema elements

108
Q

The DROP command

A

can be used to drop named schema elements, such as tables, domains, or constraints. One can also drop a schema

109
Q

There are two drop behavior options:

A

CASCADE and RESTRICT.

110
Q

to remove the COMPANY database schema and all its tables, domains, and other elements

A

DROP SCHEMA COMPANY CASCADE;

111
Q

If the RESTRICT option is chosen in place of CASCADE, the schema is dropped only

A

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
Q

if we no longer wish to keep track of dependents of employees in the COMPANY database

A

DROP TABLE DEPENDENT CASCADE;

113
Q

DROP TABLE DEPENDENT CASCADE;

If the RESTRICT option is chosen instead of CASCADE

A

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
Q

DROP TABLE DEPENDENT CASCADE;

what does the cascade option do

A

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
Q

Notice that the DROP TABLE command not only deletes all the records in the table if successful, but also removes

A

the table definition from the catalog

116
Q

If it is desired to delete only the records but to leave the table definition for future use, then

A

DELETE command should be used instead of DROP TABLE.

117
Q

ALTER command.

A

definition of a base table or of other named schema elements can be changed

118
Q

For base tables, the possible alter table actions include

A

adding or dropping a column (attribute), changing a column definition, and adding or dropping table constraints

119
Q

to add an attribute for keeping track of jobs of employees to the EMPLOYEE base relation in the COMPANY schema

A

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
Q

To drop a column, we must choose either _____ or ____ for drop behavior

A

CASCADE or RESTRICT

121
Q

IF CASCADE is chosen when dropping a column

A

all constraints and views that reference the column are dropped automatically from the schema, along with the column.

122
Q

if RESTRICT is chosen when dropping column

A

command is successful only if no views or constraints (or other schema elements) reference the column.

123
Q

the following command removes the attribute Address from the EMPLOYEE base table

A

ALTER TABLE COMPANY.EMPLOYEE DROP COLUMN Address CASCADE;

124
Q

possible to alter a column definition by dropping an existing default clause

A

ALTER TABLE COMPANY.DEPARTMENT ALTER COLUMN Mgr_ssn DROP DEFAULT;

125
Q

possible to alter a column definition by defining a new default clause

A

ALTER TABLE COMPANY.DEPARTMENT ALTER COLUMN Mgr_ssn SET DEFAULT ‘333445555’;

126
Q

One can also change the constraints specified on a table by

A

adding or dropping a named constraint

127
Q

To be dropped, a constraint must

A

have been given a name when it was specified

128
Q

to drop the constraint named EMPSUPERFK

A

DROP CONSTRAINT EMPSUPERFK CASCADE;

129
Q

Adding constraint to table

A

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.

SQL (2 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions