Accounting
Anthropology
Archaeology
Art History
Banking
Biology & Life Science
Business
Business Communication
Business Development
Business Ethics
Business Law
Chemistry
Communication
Computer Science
Counseling
Criminal Law
Curriculum & Instruction
Design
Earth Science
Economic
Education
Engineering
Finance
History & Theory
Humanities
Human Resource
International Business
Investments & Securities
Journalism
Law
Management
Marketing
Medicine
Medicine & Health Science
Nursing
Philosophy
Physic
Psychology
Real Estate
Science
Social Science
Sociology
Special Education
Speech
Visual Arts
Humanities
Q:
A dependency graph should include all of the following except ________.
A) tables
B) views
C) triggers
D) DEFAULT values
Q:
Because of the need to know the functional dependencies in a database, it is a good idea to create a(n) ________.
A) conceptual schema
B) internal schema
C) dependency graph
D) table-relationship diagram
Q:
The data model produced by reverse engineering is a(n) ________.
A) conceptual schema
B) internal schema
C) dependency graph
D) table-relationship diagram
Q:
The data model produced by reverse engineering is not truly a logical model because it will contain ________.
A) tables for strong entities
B) supertype/subtype tables
C) tables for ID-dependent entities
D) intersection tables
Q:
The process of reading an actual database schema and producing a data model from that schema is called ________.
A) data modeling
B) data engineering
C) reverse engineering
D) schema modeling
Q:
In a doubly nested set of NOT EXISTS SELECT statements, ________.
A) if a row does not not match any row, then it matches every row
B) if a row matches any row, then it matches every row
C) if a row does not match any row, then it does not match every row
D) if a row matches any row, then it does not match every row
Q:
A doubly nested set of NOT EXISTS SELECT statements is ________.
A) a famous pattern in SQL
B) regularly used
C) rarely used to nonexistent in the real world
D) Both A and B are correct
Q:
When running an SQL query that uses NOT EXISTS, the NOT EXISTS keyword will be true if ________.
A) any row in the subquery meets the condition
B) all rows in the subquery meet the condition
C) no row in the subquery meets the condition
D) any row in the subquery fails to meet the condition
Q:
When running an SQL query that uses EXISTS, the EXISTS keyword will be true if ________.
A) any row in the subquery meets the condition
B) all rows in the subquery meet the condition
C) no row in the subquery meets the condition
D) any row in the subquery fails to meet the condition
Q:
SQL queries that use EXISTS and NOT EXISTS are ________.
A) normal subqueries
B) correlated subqueries
C) uncorrelated subqueries
D) constraint dependent subqueries
Q:
Which of the following SQL statements is a correctly stated correlated subquery?
A) SELECT C1.CustName, C1.SalesRepNo
FROM CUSTOMER C1
WHERE C1.SalesRepNo IN
(SELECT S1.SalesRepNo
FROM SALESREP S1
WHERE S1.RepName = 'Smith');
B) SELECT C1.CustName, C1.SalesRepNo
FROM CUSTOMER C1
WHERE C1.SalesRepNo IN
(SELECT S1.SaleRepNo
FROM SALESREP S1
WHERE S1.RepName = 'Smith')
AND C1.SalesRepNo=S1.SalesRepNo);
C) SELECT C1.CustName, C1.SalesRepNo
FROM CUSTOMER C1
WHERE C1.SalesRepNo IN
(SELECT S1.SaleRepNo
FROM SALESREP S1
WHERE S1.RepName = 'Smith')
AND C1.SalesRepNo<>S1.SalesRepNo);
D) SELECT C1.CustName, C1.SalesRepNo
FROM CUSTOMER C1
WHERE C1.SalesRepNo IN
(SELECT C2.SaleRepNo
FROM CUSTOMER C2
WHERE C1.SalesRepNo=C2.SalesRepNo);
AND C1.OrderNo<>C2.OrderNo);
Q:
When running a correlated subquery, the DBMS always uses ________.
A) regular processing
B) nested processing
C) "quick and dirty" processing
D) SQL-92 processing
Q:
When running a correlated subquery, the DBMS ________.
A) runs the lower SELECT statement by itself and then sends the results to the upper SELECT statement.
B) runs the upper SELECT statement by itself and then sends the results to the lower SELECT statement.
C) alternates running the lower SELECT statement with running the upper SELECT statement based on each result of the lower SELECT statement
D) Either A or B may be used depending on the query.
Q:
In the SQL statements
SELECT C1.CustName, C1.SalesRepNo
FROM CUSTOMER C1;
the "C1" is called a(n) ________.
A) term
B) alias
C) convention
D) label
Q:
In a correlated subquery of a database that has tables TableOne and TableTwo, if table TableOne is used in the upper SELECT statement, then which table is used in the lower SELECT statement?
A) TableOne
B) TableTwo
C) Both TableOne and TableTwo
D) Either TableOne or TableTwo
Q:
In the database redesign process, an SQL tool that is useful for testing whether or not certain conditions or assumptions are valid is ________.
A) UPDATE
B) DROP TABLE
C) CREATE TRIGGER
D) NOT EXISTS
Q:
In the database redesign process, before proceeding with the redesign it is often useful to ________.
A) check whether certain conditions or assumptions about the data are valid
B) find out why the design was not done properly the first time
C) stop information systems and users from influencing each other
D) set standards for user behavior
Q:
Which of the following is not a possible step in the database redesign process?
A) Checking whether certain conditions or assumptions about the data are valid
B) Reverse engineering the data model
C) Revising user account name policies
D) Maintaining backups of the existing database
Q:
Database redesign is fairly easy when ________.
A) information systems and organizations influence each other
B) the design was done correctly the first time
C) there is no data in the database
D) good backups of the database are available
Q:
Deleting tables and relationships is basically a matter of dropping foreign key constraints and then dropping the tables.
Q:
Adding new tables and relationships to a database is difficult.
Q:
When decreasing maximum cardinalities, there will always be data loss.
Q:
When increasing cardinalities from 1:N to N:M, we basically create a new intersection table, fill it with data and drop the old foreign key.
Q:
What are SQL stored procedures and how are they used?
Q:
What are SQL triggers and how are they used?
Q:
What is embedded SQL, and what considerations are necessary when using it in an application?
Q:
The following database will be used in this question:
GENERAL SALES DATABASE:
SALESREP SalesRepNo
RepName
HireDate 654
Jones
01/02/2005 734
Smith
02/03/2007 345
Chen
01/25/2004 434
Johnson
11/23/2004 CUSTOMER CustNo
CustName
Balance
SalesRepNo 9870
Winston
500
345 8590
Gonzales
350
434 7840
Harris
800
654 4870
Miles
100
345 What is an SQL view, and what is it used for? Include an example based on the CUSTOMER table of the General Sales Database.
Q:
The following database will be used in this question:
GENERAL SALES DATABASE:
SALESREP SalesRepNo
RepName
HireDate 654
Jones
01/02/2005 734
Smith
02/03/2007 345
Chen
01/25/2004 434
Johnson
11/23/2004 CUSTOMER CustNo
CustName
Balance
SalesRepNo 9870
Winston
500
345 8590
Gonzales
350
434 7840
Harris
800
654 4870
Miles
100
345 Explain the use of the SQL statement CREATE TABLE. Do NOT discuss the ALTER statement in your answer, but DO include an example based on the SALESREP table in the General Sales database.
Q:
Distinguish between Char and VarChar data types. Include examples and tradeoffs.
Q:
Discuss what is meant by a data type of "Numeric (10,3)." Include at least one example.
Q:
Discuss SQL data types.
Q:
Explain how relationships are created using SQL. Include an example.
Q:
Explain the essential format of the CREATE TABLE statement. Include an example.
Q:
Based on the table below, a function to compute a 10% late penalty on a customer's balance would have how many input parameters?
CUSTOMER CustNo
CustName
Balance
SalesRepNo 9870
Winston
500
345 8590
Gonzales
350
434 7840
Harris
800
654 4870
Miles
100
345 A) 0
B) 1
C) 2
D) it doesn't matter
Q:
Which of the following does not describe a user-defined function?
A) It computes a numeric value
B) It can be called by name from within an SQL statement
C) It may have input parameters passed to it
D) It returns an output value to the calling statement
Q:
If you have a foreign key in a CUSTOMER table that references the primary key in a SALESREP table, the ON DELETE CASCADE syntax means that ________.
A) when a CUSTOMER is deleted, that CUSTOMER's SALESREP is also deleted
B) when a CUSTOMER is deleted, all SALESREPS are also deleted
C) when a SALESREP is deleted, all CUSTOMERs of that SALESREP are also deleted
D) when the SALESREP table is dropped, all CUSTOMERs must be deleted
Q:
Referential integrity constraints are implemented in SQL using the ________ syntax.
A) PRIMARY KEY
B) FOREIGN KEY
C) DEFAULT
D) UNIQUE
Q:
Because SQL stored procedures allow and encourage code sharing among developers, stored procedures give database application developers all these advantages except ________.
A) less work
B) standardized processing
C) specialization among developers
D) faster query response times
Q:
Stored procedures have all these advantages except ________.
A) greater security
B) faster development
C) SQL optimized by the DBMS compiler
D) code sharing
Q:
A stored program that is attached to the database is called ________.
A) a view
B) embedded SQL
C) a trigger
D) a stored procedure
Q:
If the values in an SQL view are not changeable through the view itself, you may still be able to update the view by using unique application logic. In this case, the specific logic is placed in ________.
A) a BEFORE trigger
B) an INSTEAD OF trigger
C) an AFTER trigger
D) Depending on the specific logic, either A or B can be used.
Q:
To set a column value to an initial value that is selected according to some business logic, you would use ________.
A) the SQL DEFAULT constraint with the CREATE TABLE command
B) an SQL view
C) embedded SQL
D) an SQL trigger
Q:
SQL triggers are created using ________.
A) the SQL CREATE TRIGGER statement
B) the SQL ADD TRIGGER statement
C) the SQL TRIGGER statement
D) the SQL ADD CONSTRAINT TRIGGER statement
Q:
When a trigger is fired, the DBMS makes the appropriate data available to ________.
A) the SQL interpreter
B) the application code
C) the embedded SQL code
D) the trigger code
Q:
SQL triggers are not used for ________.
A) validity checking
B) providing default values
C) updating views
D) creating tables
Q:
SQL triggers can be used when the DBMS receives a(n) ________ request.
A) INSERT
B) SELECT
C) ALTER
D) CREATE
Q:
Which of the following is an SQL trigger Microsoft SQL Server supports?
A) BEFORE
B) INSTEAD OF
C) AFTER
D) Both B and C are correct
Q:
Which of the following is not an SQL trigger Oracle supports?
A) BEFORE
B) INSTEAD OF
C) AFTER
D) DURING
Q:
Which of the following is not an ANSI SQL trigger?
A) BEFORE UPDATE
B) INSTEAD OF UPDATE
C) AFTER INSERT
D) INSTEAD OF CONSTRAINT
Q:
A stored program that is attached to a table or view is called ________.
A) a CHECK constraint
B) a view
C) embedded SQL
D) a trigger
Q:
Because SQL statements are table-oriented, whereas programs are element-oriented, the results of SQL statements used in programs are accessed using ________.
A) standard programming tools
B) custom written programming tools
C) an SQL cursor
D) an SQL trigger
Q:
Because SQL statements are set-oriented, whereas programs are element-oriented, the results of SQL statements used in programs are treated as ________.
A) tables
B) rows
C) files
D) pseudofiles
Q:
A set of SQL statements stored in an application written in a standard programming language is called ________.
A) a stored procedure
B) a view
C) embedded SQL
D) a trigger
Q:
SQL views are always updatable when ________.
A) the view is based on a single table with no computed columns, and all non-null columns are present in the view
B) the view is based on any number of tables, with or without computed columns, and the INSTEAD OF trigger is defined for the view
C) the view is based on multiple tables, the update is being done on the most subordinate table, and the rows of that table can be uniquely identified
D) Both A and B are correct
Q:
If the values in an SQL view are changeable through the view itself, which SQL statement is used to change the values?
A) CREATE
B) DELETE
C) INSERT
D) UPDATE
Q:
SQL views are not used ________.
A) to hide columns
B) to show results of computed columns
C) to hide complicated SQL statements
D) to enforce primary key constraints
Q:
Which SQL statement is used to retrieve view instances?
A) CREATE
B) DELETE
C) INSERT
D) SELECT
Q:
According to the SQL-92 standard, statements used to construct views cannot contain ________.
A) the SELECT clause
B) the FROM clause
C) the WHERE clause
D) the ORDER BY clause
Q:
SQL views are constructed from ________.
A) CREATE statements
B) INSERT statements
C) UPDATE statements
D) SELECT statements
Q:
The SQL command used to create a virtual table is ________.
A) CREATE VTABLE
B) CREATE VIEW
C) VTABLE
D) VIEW
Q:
An SQL virtual table is called ________.
A) a CHECK constraint
B) a view
C) embedded SQL
D) a trigger
Q:
Based on the tables below, which of the following SQL statements would increase the balance of the Gonzales account by $100 to a total of $450?
GENERAL SALES DATABASE:
SALESREP SalesRepNo
RepName
HireDate 654
Jones
01/02/2005 734
Smith
02/03/2007 345
Chen
01/25/2004 434
Johnson
11/23/2004 CUSTOMER CustNo
CustName
Balance
SalesRepNo 9870
Winston
500
345 8590
Gonzales
350
434 7840
Harris
800
654 4870
Miles
100
345 A) SELECT Gonzales
FROM CUSTOMER
INSERT VALUES PLUS (100) INTO Balance;
B) SELECT Gonzales
FROM CUSTOMER
INSERT VALUES (450) INTO Balance;
C) INSERT INTO CUSTOMER VALUES (450)
SELECT Balance
WHERE CustName = 'Gonzales';
D) UPDATE CUSTOMER
SET Balance = 450
WHERE CustName = 'Gonzales';
Q:
Which keyword is used to remove one or more rows from a table?
A) DELETE
B) INSERT
C) ERASE
D) SET
Q:
Which SQL keyword is used to change the values of an entire column?
A) CHANGE
B) INSERT
C) SELECT
D) SET
Q:
Which SQL keyword is used to change one or more rows in a table?
A) MODIFY
B) INSERT
C) SELECT
D) UPDATE
Q:
Which SQL keyword is used to add one or more rows of data to a table?
A) DELETE
B) INSERT
C) SELECT
D) UPDATE
Q:
When the correct SQL command is used to delete a table's structure, what happens to the data in the table?
A) If the deleted table was a parent table, the data is added to the appropriate rows of the child table.
B) If the deleted table was a child table, the data is added to the appropriate rows of the parent table.
C) The data in the table is also deleted.
D) Nothing because there was no data in the table since only an empty table can be deleted.
Q:
Which SQL keyword is used to delete a table's structure?
A) DELETE
B) DROP
C) DISPOSE
D) ALTER
Q:
Which SQL keyword is used to change the structure, properties or constraints of a table?
A) SET
B) CREATE
C) SELECT
D) ALTER
Q:
The SQL keyword used to limit column values to specific values is ________.
A) CONSTRAINT
B) CHECK
C) NOT NULL
D) UNIQUE
Q:
Given the SQL statement
CREATE TABLE SALESREP (
SalesRepNo int NOT NULL,
RepName char(35) NOT NULL,
HireDate date NOT NULL,
CONSTRAINT SalesRepPK PRIMARY KEY (SalesRepNo),
CONSTRAINT SalesRepAK1 UNIQUE (RepName)
);
we know that ________.
A) RepName is the primary key
B) RepName is a foreign key
C) RepName is a candidate key
D) RepName is a surrogate key
Q:
Which of the following illustrates the authors' preferred style of defining a primary key?
A) CREATE TABLE CUSTOMER (
CustomerID Integer Primary Key
LastName Char(35) Not Null
First Name Char(25) Null
);
B) CREATE TABLE CUSTOMER (
CustomerID Integer Not Null
LastName Char(35) Not Null
First Name Char(25) Null
CONSTRAINT CustomerPK PRIMARY KEY (CustomerID)
);
C) CREATE TABLE CUSTOMER (
CustomerID Integer Not Null
LastName Char(35) Not Null
First Name Char(25) Null
);
ALTER TABLE CUSTOMER
ADD CONSTRAINT CustomerPK PRIMARY KEY (CustomerID);
D) Both B and C are correct
Q:
One advantage of using the CONSTRAINT phrase to define a primary key is that the database designer controls the ________.
A) name of the table
B) name of the foreign key field
C) name of the constraint
D) name of the primary key field
Q:
Which SQL keyword is used to impose restrictions on a table, data or relationship?
A) SET
B) CREATE
C) SELECT
D) CONSTRAINT
Q:
If the table PRODUCT has a column PRICE that has the data type Numeric (8,2), the value 12345 will be displayed by the DBMS as ________.
A) 123.45
B) 12345
C) 12345.00
D) 123450.00
Q:
Which SQL keyword is used to name a new table and describe the table's columns?
A) SET
B) CREATE
C) SELECT
D) ALTER
Q:
Because SQL stored procedures allow and encourage code sharing among developers, stored procedures give database application developers the advantages of less work, standardized processing, and specialization among developers.
Q:
Unlike application code, stored procedures are never distributed to the client computers.
Q:
Stored procedures have the advantage of greater security, decreased network traffic, SQL optimized by the DBMS compiler, and code sharing.
Q:
A stored program that is stored within the database and compiled when used is called a trigger.