Database Principles Fundamentals of Design Implementation and Management International Edition 10th Edition by Carlos Coronel - Test Bank

Database Principles Fundamentals of Design Implementation and Management International Edition 10th Edition by Carlos Coronel – Test Bank

 

Instant Download – Complete Test Bank With Answers

 

 

Sample Questions Are Posted Below

 

Chapter 5: Beginning Structured Query Language (SQL)

 

TRUE/FALSE

 

1. A database language enables the user to create database and table structures to perform basic data management chores.

 

ANS:  T                    PTS:   1

 

2. A database language enables the user to perform complex queries designed to transform the raw data into useful information.

 

ANS:  T                    PTS:   1

 

3. The ANSI prescribes a standard SQL–the current fully approved version is known as SQL-07.

 

ANS:  F                    PTS:   1

 

4. The ANSI SQL standards are also accepted by the ISO.

 

ANS:  T                    PTS:   1

 

5. SQL is considered difficult to learn; its command set has a vocabulary of more than 300 words.

 

ANS:  F                    PTS:   1

 

6. Data type selection is usually dictated by the nature of the data and by the intended use.

 

ANS:  T                    PTS:   1

 

7. Only numeric data types can be added and subtracted in SQL.

 

ANS:  F                    PTS:   1

 

8. Entity integrity is enforced automatically when the primary key is specified in the CREATE TABLE command sequence.

 

ANS:  T                    PTS:   1

 

9. The CHECK constraint is used to define a condition for the values that the attribute domain cannot have.

 

ANS:  F                    PTS:   1

 

10. SQL requires the use of the ADD command to enter data into a table.

 

ANS:  F                    PTS:   1

 

 

11. You cannot insert a row containing a null attribute value using SQL.

 

ANS:  F                    PTS:   1

 

12. To list the contents of a table, you must use the DISPLAY command.

 

ANS:  F                    PTS:   1

 

13. Any changes made to the contents of a table are not physically saved on disk until you use the SAVE <table name> command.

 

ANS:  F                    PTS:   1

 

14. The COMMIT command does not permanently save all changes. In order to do that, you must use SAVE.

 

ANS:  F                    PTS:   1

 

15. If you have not yet used the COMMIT command to store the changes permanently in the database, you can restore the database to its previous condition with the ROLLBACK command.

 

ANS:  T                    PTS:   1

 

16. All SQL commands must be issued on a single line.

 

ANS:  F                    PTS:   1

 

17. Although SQL commands can be grouped together on a single line, complex command sequences are best shown on separate lines, with space between the SQL command and the command’s components.

 

ANS:  T                    PTS:   1

 

18. You can select partial table contents by naming the desired fields and by placing restrictions on the rows to be included in the output.

 

ANS:  T                    PTS:   1

 

19. Oracle users can use the Access QBE (query by example) query generator.

 

ANS:  F                    PTS:   1

 

20. Since computers identify all characters by their numeric codes, mathematical operators cannot be used to place restrictions on character-based attributes.

 

ANS:  F                    PTS:   1

 

21. String comparisons are made from left to right.

 

ANS:  T                    PTS:   1

 

22. Date procedures are often more software-specific than other SQL procedures.

 

ANS:  T                    PTS:   1

 

23. SQL allows the use of logical restrictions on its inquiries such as OR, AND, and NOT.

 

ANS:  T                    PTS:   1

 

24. ANSI-standard SQL allows the use of special operators in conjunction with the WHERE clause.

 

ANS:  T                    PTS:   1

 

25. The conditional LIKE must be used in conjunction with wildcard characters.

 

ANS:  T                    PTS:   1

 

26. Most SQL implementations yield case-insensitive searches.

 

ANS:  F                    PTS:   1

 

27. Some RDBMSs, such as Microsoft Access, automatically make the necessary conversions to eliminate case sensitivity.

 

ANS:  T                    PTS:   1

 

28. The COUNT function is designed to tally the number of non-null “values” of an attribute, and is often used in conjunction with the DISTINCT clause.

 

ANS:  T                    PTS:   1

 

29. To join tables, simply enumerate the tables in the FROM clause of the SELECT statement. The DBMS will create a Cartesian product of every table in the FROM clause. To get the correct results, you need to select the rows in which the common attribute values do not match.

 

ANS:  F                    PTS:   1

 

30. When joining three or more tables, you need to specify a join condition for one pair of tables.

 

ANS:  F                    PTS:   1

 

MULTIPLE CHOICE

 

1. The most recent fully approved version of standard SQL prescribed by the ANSI is ____.

a.	SQL-99	c.	SQL-4
b.	SQL-2003	d.	SQL-07

 

 

ANS:  B                    PTS:   1

 

2. The SQL character data format(s) is(are) ____.

a.	CHAR and VARCHAR	c.	Alphanumeric
b.	VARCHAR only	d.	CHAR only

 

 

ANS:  A                    PTS:   1

 

3. The SQL command that lets you insert rows into a table is ____.

a.	INSERT	c.	COMMIT
b.	SELECT	d.	UPDATE

 

 

ANS:  A                    PTS:   1

 

4. The SQL command that lets you permanently save data changes is ____.

a.	INSERT	c.	COMMIT
b.	SELECT	d.	UPDATE

 

 

ANS:  C                    PTS:   1

 

5. The SQL command that lets you select attributes from rows in one or more tables is ____.

a.	INSERT	c.	COMMIT
b.	SELECT	d.	UPDATE

 

 

ANS:  B                    PTS:   1

 

6. To list all the contents of the PRODUCT table, you would use ____.

a.	LIST * FROM PRODUCT;	c.	DISPLAY * FROM PRODUCT;
b.	SELECT * FROM PRODUCT;	d.	SELECT ALL FROM PRODUCT;

 

 

ANS:  B                    PTS:   1

 

7. In Oracle, the ____ command is used to change the display for a column, for example, to place a $ in front of a numeric value.

a.	DISPLAY	c.	CHAR
b.	FORMAT	d.	CONVERT

 

 

ANS:  B                    PTS:   1

 

8. The SQL command that modifies an attribute’s values in one or more table’s rows is ____.

a.	INSERT	c.	COMMIT
b.	SELECT	d.	UPDATE

 

 

ANS:  D                    PTS:   1

 

9. UPDATE tablename

*****

[WHERE conditionlist];

 

The ____ command replaces the ***** in the syntax of the UPDATE command, shown above.

a.	SET columnname = expression	c.	expression = columnname
b.	columnname = expression	d.	LET columnname = expression

 

 

ANS:  A                    PTS:   1

 

 

10. An example of a command you would use when making changes to a PRODUCT table is ____.

a.	CHANGE PRODUCT SET P_INDATE = ’18-JAN-2004′ WHERE P_CODE = ’13-Q2/P2′;
b.	ROLLBACK PRODUCT SET P_INDATE = ’18-JAN-2004′ WHERE P_CODE = ’13-Q2/P2′;
c.	EDIT PRODUCT SET P_INDATE = ’18-JAN-2004′ WHERE P_CODE = ’13-Q2/P2′;
d.	UPDATE PRODUCT SET P_INDATE = ’18-JAN-2004′ WHERE P_CODE = ’13-Q2/P2′;

 

 

ANS:  D                    PTS:   1

 

11. The ____ command is used to restore the table’s contents to their previous values.

a.	COMMIT; RESTORE;	c.	COMMIT; ROLLBACK;
b.	COMMIT; BACKUP;	d.	ROLLBACK;

 

 

ANS:  D                    PTS:   1

 

12. Some RDBMSs, such as Oracle, automatically ____ data changes when issuing data definition commands.

a.	COMMIT	c.	UNSAVE
b.	ROLLBACK	d.	UPDATE

 

 

ANS:  A                    PTS:   1

 

13. To delete a row from the PRODUCT table, use the ____ command.

a.	COMMIT	c.	ERASE
b.	DELETE	d.	KILL

 

 

ANS:  B                    PTS:   1

 

14. When you issue the DELETE FROM tablename command without specifying a WHERE condition, ____.

a.	no rows will be deleted	c.	the last row will be deleted
b.	the first row will be deleted	d.	all rows will be deleted

 

 

ANS:  D                    PTS:   1

 

 

15. Which of the following is used to select partial table contents?

a.	SELECT <column(s)> FROM <Table name> BY <Conditions>;
b.	LIST <column(s)> FROM <Table name> BY <Conditions>;
c.	SELECT <column(s)> FROM <Table name> WHERE <Conditions>;
d.	LIST<column(s)> FROM <Table name> WHERE <Conditions>;

 

 

ANS:  C                    PTS:   1

 

16. The ____ command would be used to delete the table row where the P_CODE  is ‘BRT-345’.

a.	DELETE FROM PRODUCT WHERE P_CODE = ‘BRT-345’;
b.	REMOVE FROM PRODUCT WHERE P_CODE = ‘BRT-345’;
c.	ERASE FROM PRODUCT WHERE P_CODE = ‘BRT-345’;
d.	ROLLBACK FROM PRODUCT WHERE P_CODE = ‘BRT-345’;

 

 

ANS:  A                    PTS:   1

 

17. A(n) ____ is a query that is embedded (or nested) inside another query.

a.	alias	c.	subquery
b.	operator	d.	view

 

 

ANS:  C                    PTS:   1

 

18. Which query will output the table contents when the value of V_CODE is equal to 21344?

a.	SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <> 21344;
b.	SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <= 21344;
c.	SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344;
d.	SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE => 21344;

 

 

ANS:  C                    PTS:   1

 

 

19. Which query will output the table contents when the value of V_CODE is not equal to 21344?

a.	SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <> 21344;
b.	SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <= 21344;
c.	SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344;
d.	SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE => 21344;

 

 

ANS:  A                    PTS:   1

 

20. Which query will output the table contents when the value of P_PRICE is less than or equal to 10?

a.	SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE P_PRICE <> 10;
b.	SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE P_PRICE <= 10;
c.	SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE P_PRICE => 10;
d.	SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE P_PRICE = 10;

 

 

ANS:  B                    PTS:   1

 

21. Which query will output the table contents when the value of the character field P_CODE is alphabetically less than 1558-QW1?

a.	SELECT P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE FROM PRODUCT WHERE P_CODE <‘1558-QW1’;
b.	SELECT P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE FROM PRODUCT WHERE P_CODE = [1558-QW1]
c.	SELECT P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE FROM PRODUCT WHERE P_CODE = (1558-QW1)
d.	SELECT P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE FROM PRODUCT WHERE P_CODE = {1558-QW1}

 

 

ANS:  A                    PTS:   1

 

 

22. Which query will list all the rows in which the inventory stock dates occur on or after January 20, 2010?

a.	SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE >= ’20-JAN-2010′
b.	SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE >= $20-JAN-2010$
c.	SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE <= ’20-JAN-2010′
d.	SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE >= {20-JAN-2010}

 

 

ANS:  A                    PTS:   1

 

23. Which query will use the given columns and column aliases from the PRODUCT table to determine the total value of inventory held on hand?

a.	SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH/P_PRICE FROM PRODUCT;
b.	SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH=P_PRICE FROM PRODUCT;
c.	SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH*P_PRICE FROM PRODUCT;
d.	SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH-P_PRICE FROM PRODUCT;

 

 

ANS:  C                    PTS:   1

 

24. A(n) ____ is an alternate name given to a column or table in any SQL statement.

a.	alias	c.	stored function
b.	data type	d.	trigger

 

 

ANS:  A                    PTS:   1

 

25. Which query will use the given columns and column aliases from the PRODUCT table to determine the total value of inventory held on hand and display the results in a column labeled TOTVALUE?

a.	SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH*P_PRICE AS TOTVALUE FROM PRODUCT;
b.	SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH=P_PRICE AS TOTVALUE FROM PRODUCT;
c.	SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH/P_PRICE AS TOTVALUE FROM PRODUCT;
d.	SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH-P_PRICE AS TOTVALUE FROM PRODUCT;

 

 

ANS:  A                    PTS:   1

 

 

26. Which query uses the correct SQL syntax to list the table contents for either V_CODE = 21344 or V_CODE = 24288?

a.	SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE <= 24288
b.	SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE => 24288
c.	SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE > 24288
d.	SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE = 24288

 

 

ANS:  D                    PTS:   1

 

27. The special operator used to check whether an attribute value is within a range of values is ____.

a.	BETWEEN	c.	LIKE
b.	NULL	d.	IN

 

 

ANS:  A                    PTS:   1

 

28. The ____ special operator is used to check whether an attribute value is null.

a.	BETWEEN	c.	LIKE
b.	IS NULL	d.	IN

 

 

ANS:  B                    PTS:   1

 

29. The special operator used to check for similar character strings is ____.

a.	BETWEEN	c.	LIKE
b.	IS NULL	d.	IN

 

 

ANS:  C                    PTS:   1

 

30. The special operator used to check whether a subquery returns any rows is ____.

a.	BETWEEN	c.	LIKE
b.	EXISTS	d.	IN

 

 

ANS:  B                    PTS:   1

 

31. The ____ command is used with the ALTER TABLE command to modify the table by deleting a column.

a.	DROP	c.	DELETE
b.	REMOVE	d.	ERASE

 

 

ANS:  A                    PTS:   1

 

32. A table can be deleted from the database by using the ____ command.

a.	DROP TABLE	c.	MODIFY TABLE
b.	DELETE TABLE	d.	ERASE TABLE

 

 

ANS:  A                    PTS:   1

 

33. The query used to list the P_CODE, P_DESCRIPT, P_INDATE, and P_PRICE fields from the PRODUCT table in ascending order by P_PRICE is ____.

a.	SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT SEQUENCE BY P_PRICE;
b.	SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT LIST BY P_PRICE;
c.	SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT ORDER BY P_PRICE;
d.	SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT ASCENDING BY P_PRICE;

 

 

ANS:  C                    PTS:   1

 

34. The SQL query to output the contents of the EMPLOYEE table sorted by last name, first name, and initial is ____.

a.	SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE LIST BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;
b.	SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE ORDER BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;
c.	SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE DISPLAY BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;
d.	SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE SEQUENCE BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;

 

 

ANS:  B                    PTS:   1

 

 

35. Which query is used to list a unique value for V_CODE, where the list will produce only a list of those values that are different from one another?

a.	SELECT ONLY V_CODE FROM PRODUCT;
b.	SELECT UNIQUE V_CODE FROM PRODUCT;
c.	SELECT DIFFERENT V_CODE FROM PRODUCT;
d.	SELECT DISTINCT V_CODE FROM PRODUCT;

 

 

ANS:  D                    PTS:   1

 

36. The SQL aggregate function that gives the number of rows containing non-null values for the given column is ____.

a.	COUNT	c.	MAX
b.	MIN	d.	SUM

 

 

ANS:  A                    PTS:   1

 

37. The SQL aggregate function that gives the total of all values for a selected attribute in a given column is ____.

a.	COUNT	c.	MAX
b.	MIN	d.	SUM

 

 

ANS:  D                    PTS:   1

 

38. The SQL aggregate function that gives the average for the specific column is ____.

a.	COUNT	c.	MAX
b.	AVG	d.	SUM

 

 

ANS:  B                    PTS:   1

 

39. The query to join the P_DESCRIPT and P_PRICE fields from the PRODUCT table and the V_NAME, V_AREACODE, V_PHONE, and V_CONTACT fields from the VENDOR table where the values of V_CODE match is ____.

a.	SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE <> VENDOR.V_CODE;
b.	SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE;
c.	SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE <= VENDOR.V_CODE;
d.	SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE => VENDOR.V_CODE;

 

 

ANS:  B                    PTS:   1

 

40. The query to join the P_DESCRIPT and P_PRICE fields from the PRODUCT table and the V_NAME, V_AREACODE, V_PHONE and V_CONTACT fields from the VENDOR table, where the values of V_CODE match and the output is ordered by the price is ____.

a.	SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE <> VENDOR.V_CODE; ORDER BY P_PRICE;
b.	SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE => VENDOR.V_CODE; ORDER BY P_PRICE;
c.	SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE <= VENDOR.V_CODE; ORDER BY P_PRICE;
d.	SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE; ORDER BY P_PRICE;

 

 

ANS:  D                    PTS:   1

 

COMPLETION

 

1. In the SQL environment, the word ____________________ covers both questions and actions.

 

ANS:  query

 

PTS:   1

 

2. The basic SQL vocabulary has fewer than ____________________ words.

 

ANS:

100

one hundred

a hundred

 

PTS:   1

 

3. A(n) ____________________ is a logical group of database objects, such as tables and indexes, that are related to each other.

 

ANS:  schema

 

PTS:   1

 

4. With the exception of the database ____________________ process, most RDBMS vendors use SQL that deviates little from the ANSI standard SQL.

 

ANS:

creation

creating

 

PTS:   1

 

5. U.S. state abbreviations are always two characters, so ____________________(2) is a logical choice for the data type representing a state column.

 

ANS:  CHAR

 

PTS:   1

 

6. DATE() and SYSDATE are special functions that return today’s date in MS Access and ____________________, respectively.

 

ANS:  Oracle

 

PTS:   1

 

7. If your integer values are relatively small, use ____________________ instead of INT.

 

ANS:  SMALLINT

 

PTS:   1

 

8. In a 1:M relationship, you must always create the table for the ____________________ side first.

 

ANS:

1

one

 

PTS:   1

 

9. ____________________ words are words used by SQL to perform specific functions.

 

ANS:  Reserved

 

PTS:   1

 

10. Using the ____________________ command, SQL indexes can be created on the basis of any selected attribute.

 

ANS:  CREATE INDEX

 

PTS:   1

 

11. A common practice is to create a(n) ____________________ on any field that is used as a search key, in comparison operations in a conditional expression, or when you want to list rows in a specific order.

 

ANS:  index

 

PTS:   1

 

12. To delete an index, use the ____________________ command.

 

ANS:  DROP INDEX

 

PTS:   1

 

13. In an INSERT command, you can indicate just the attributes that have required values by listing the ____________________ inside parentheses after the table name.

 

ANS:

attribute names

names

 

PTS:   1

 

14. A(n) ____________________ character is a symbol that can be used as a general substitute for other characters or commands.

 

ANS:

wildcard

wild card

 

PTS:   1

 

15. A(n) ____________________, also known as a nested query or an inner query, is a query that is embedded (or nested) inside another query.

 

ANS:  subquery

 

PTS:   1

 

16. The ____________________ command, coupled with appropriate search conditions, is an incredibly powerful tool that enables you to transform data into information.

 

ANS:  SELECT

 

PTS:   1

 

17. In SQL, all ____________________ expressions evaluate to true or false.

 

ANS:  conditional

 

PTS:   1

 

18. A specialty field in mathematics, known as ____________________ algebra, is dedicated to the use of logical operators.

 

ANS:  Boolean

 

PTS:   1

 

19. If you add a new column to a table that already has rows, the existing rows will default to a value of ____________________ for the new column.

 

ANS:  null

 

PTS:   1

 

20. A table can be deleted from the database by using the ____________________ command.

 

ANS:  DROP TABLE

 

PTS:   1

 

21. A(n) ____________________ order sequence is a multilevel ordered sequence that can be created easily by listing several attributes, separated by commas, after the ORDER BY clause.

 

ANS:  cascading

 

PTS:   1

 

22. Rows can be grouped into smaller collections quickly and easily using the SQL ____________________ clause.

 

ANS:  GROUP BY

 

PTS:   1

 

23. The ____________________ clause of the GROUP BY statement operates very much like the WHERE clause in the SELECT statement.

 

ANS:  HAVING

 

PTS:   1

 

24. A(n) ____________________ is performed when data is retrieved from more than one table at a time.

 

ANS:  join

 

PTS:   1

 

 

25. An alias is especially useful when a table must be joined to itself in a(n) ____________________ query.

 

ANS:  recursive

 

PTS:   1

 

ESSAY

 

1. What is a schema? How many schemas can be used in one database?

 

ANS:

In the SQL environment, a schema is a logical group of database objects—such as tables and indexes—that are related to each other. Usually, the schema belongs to a single user or application. A single database can hold multiple schemas that belong to different users or applications. Schemas are useful in that they group tables by owner (or function) and enforce a first level of security by allowing each user to see only the tables that belong to that user.

 

PTS:   1

 

2. What command is used to save changes to the database? What is the syntax for this command?

 

ANS:

Any changes made to the table contents are not saved on disk until you close the database, close the program you are using, or use the COMMIT command. If the database is open and a power outage or some other interruption occurs before you issue the COMMIT command, your changes will be lost and only the original table contents will be retained. The syntax for the COMMIT command is:

COMMIT [WORK]

The COMMIT command permanently saves all changes—such as rows added, attributes modified, and rows deleted— made to any table in the database

 

PTS:   1

 

3. What is a subquery? When is it used? Does the RDBMS deal with subqueries any differently from normal queries?

 

ANS:

A subquery, also known as a nested query or an inner query, is a query that is embedded (or nested) inside another query. The inner query is always executed first by the RDBMS. Given the previous SQL statement, the INSERT portion represents the outer query, and the SELECT portion represents the subquery. You can nest queries (place queries inside queries) many levels deep; in every case, the output of the inner query is used as the input for the outer (higher-level) query.

 

PTS:   1

 

 

4. What are the wildcard characters that are used with the LIKE command? Provide one or more examples of each.

 

ANS:

% means any and all following or preceding characters are eligible. For example:

‘J%’ includes Johnson, Jones, Jernigan, July, and J-231Q.

‘Jo%’ includes Johnson and Jones.

‘%n’ includes Johnson and Jernigan.

_ means any one character may be substituted for the underscore. For example:

‘_23-456-6789’ includes 123-456-6789, 223-456-6789, and 323-456-6789.

‘_23-_56-678_’ includes 123-156-6781, 123-256-6782, and 823-956-6788.

‘_o_es’ includes Jones, Cones, Cokes, totes, and roles.

 

PTS:   1

 

5. How do you delete a table from the database? Provide an example.

 

ANS:

A table can be deleted from the database using the DROP TABLE command. For example, you can delete the PART table with the following command:

DROP TABLE PART;

You can drop a table only if it is not the “one” side of any relationship. If you try to drop a table otherwise, the RDBMS will generate an error message indicating that a foreign key integrity violation has occurred.

 

PTS:   1