DBMS Chapter 7

24. The ____ operator is used to define a range limit. a. BETWEEN b. NULL c. LIKE d. IN

a. BETWEEN

6. ____ is/are SQL character data type(s). a. CHAR & VARCHAR2 b. VARCHAR2 only c. ALPHANUMERIC d. CHAR only

a. CHAR & VARCHAR2

33. The basic SQL aggregate function that gives the number of rows containing non-null values for the given column is ____. a. COUNT b. MIN c. MAX d. SUM

a. COUNT

11. Which command would be used to delete the table row where the P_CODE = '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';

a. DELETE FROM PRODUCT WHERE P_CODE = 'BRT-345';

28. A table can be deleted from the database by using the ____ command. a. DROP b. DELETE c. MODIFY d. ERASE

a. DROP

43. To join tables, you simply list the tables in the ____ clause of the SELECT statement. a. FROM b. JOIN c. WHERE d. TABLES

a. FROM

1. The SQL command that lets you insert data into a table is ____. a. INSERT b. SELECT c. COMMIT d. UPDATE

a. INSERT

36. The ____ function is used to find the highest value in a table column. a. MAX b. TOTAL c. SUM d. TOP

a. MAX

18. Which query would be used to output the table contents where the value of the character field P_CODE is 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};

a. SELECT P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE FROM PRODUCT WHERE P-CODE = '1558-QW1';

16. Which query would be used to output the table contents where 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;

a. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <> 21344;

20. Which command uses columns and column aliases to determine the total value of each of the products 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;

a. SELECT P_DESCRIPT, P_QOH, P_PRICE,P_QOH*P_PRICE AS TOTVALUE FROM PRODUCT;

5. The current fully approved version of standard SQL prescribed by the American National Standards Institute is ____. a. SQL-2003 b. SQL-99 c. SQL-4 d. SQL2

a. SQL-2003

21. A(n) ____ is an alternate name given to a column or table in any SQL statement. a. alias b. data type c. stored function d. trigger

a. alias

39. The ORDER BY clause is especially useful when the ____ is important to you. a. listing order b. frequency c. date d. price

a. listing order

37. What is wrong with the following query? SELECT V_CODE, P_CODE, P_DESCRIPT, P_PRICE FROM PRODUCT GROUP BY V_CODE; a. no aggregate function is used b. no where clause is specified c. no table name is specified d. nothing is wrong

a. no aggregate function is used The GROUP BY clause is valid only when used in conjunction with one of the SQL aggregate functions, such as COUNT, MIN, MAX, AVG, and SUM.

35. The basic SQL aggregate function that gives the arithmetic mean for the specific column is ____. a. COUNT b. AVG c. MAX d. SUM

b. AVG

12. Some RDBMSs (like Oracle) will automatically ____ data changes when issuing data definition commands. a. ROLLBACK b. COMMIT c. UPDATE d. INVOKE

b. COMMIT

25. The ____ operator is used to check whether an attribute has a value. a. BETWEEN b. EXISTS c. LIKE d. IN

b. EXISTS

8. In Oracle, the ____ command is used to place a $ in front of a numeric value. a. DISPLAY b. FORMAT c. CHAR d. CONVERT

b. FORMAT

38. When using GROUP BY, ____ operates like the WHERE clause in the SELECT statement. a. WHERE GROUP b. HAVING c. LIKE d. DISTINCT

b. HAVING

27. Many queries that would require the use of the logical OR can be more easily handled with the help of the special operator ____. a. LIKE b. IN c. BETWEEN d. NOT

b. IN

23. The ____ operator is used to check whether an attribute value is null. a. BETWEEN b. IS NULL c. LIKE d. IN

b. IS NULL

3. The SQL command used to list the contents of a table is ____. a. INSERT b. SELECT c. COMMIT d. UPDATE

b. SELECT

7. To list all the contents of the PRODUCT table you would use ____. a. LIST * FROM PRODUCT; b. SELECT * FROM PRODUCT; c. DISPLAY * FROM PRODUCT; d. SELECT ALL FROM PRODUCT;

b. SELECT * FROM PRODUCT;

30. What is the SQL command to output the contents of the Employee table sorted by last name, first name, and initial? 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;

b. SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE ORDER BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;

19. Which MS Access query command will list all the rows in which the inventory stock dates occur on or after January 20, 2006? a. SELECT P_DESCRIPT, P_HAND, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE >= '01/20/2008'; b. SELECT P_DESCRIPT, P_HAND, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE >= #20-JAN-08#; c. SELECT P_DESCRIPT, P_HAND, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE >= '20-JAN-2008'; d. SELECT P_DESCRIPT, P_HAND, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE >= {01-20-2008};

b. SELECT P_DESCRIPT, P_HAND, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE >= #20-JAN-08#; (# is date delimiter for Access. Not needed in Oracle.)

17. Which query would be used to output the table contents where the value of V_CODE is less than or 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;

b. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <=21344;

41. What is the command 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? 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;

b. SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE;

40. The GROUP BY clause is valid only when used in conjunction with one of the ____ functions, such as COUNT, MIN, MAX, AVG, and SUM. a. V code b. SQL aggregate c. wildcard character d. recursive query

b. SQL aggregate

31. A multilevel ordered sequence is known as a ____ order sequence. a. layered b. cascading c. 2-tier d. 3-tier

b. cascading

2. The SQL command that lets you save your work to disk is ____. a. INSERT b. SELECT c. COMMIT d. UPDATE

c. COMMIT

26. The ____ operator is used to find a character string that matches a given string pattern. a. BETWEEN b. IS NULL c. LIKE d. IN

c. LIKE

29. What is the command used to list the P_CODE, P_DESCRIPT, P_INDATE, and P_PRICE fields from the PRODUCT table in ascending order by P_PRICE? 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;

c. SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT ORDER BY P_PRICE;

15. Which query would be used to output the table contents where 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;

c. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344;

14. Which command is used to select partial table contents? a. UPDATE columnlist FROM tablelist [WHERE conditionlist]; b. VIEW columnlist FROM tablelist [WHERE conditionlist]; c. SELECT columnlist FROM tablelist [WHERE conditionlist]; d. LIST columnlist FROM tablelist [WHERE conditionlist];

c. SELECT columnlist FROM tablelist [WHERE conditionlist];

10. Which command is used to restore the table contents? a. COMMIT; RESTORE; b. COMMIT; BACKUP; c. COMMIT; ROLLBACK; d. ROLLBACK;

d. ROLLBACK;

32. What command is used to list a unique value for Vendor Code (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;

d. SELECT DISTINCT V_CODE FROM PRODUCT;

44. What is an example of a recursive query? a. SELECT CUS_LNAME, INV_NUMBER, INV_DATE, P_DESCRIPT FROM CUSTOMER, INVOICE, LINE, PRODUCT WHERE CUSTOMER.CUS_CODE = INVOICE.CUS_CODE AND INVOICE.INV_NUMBER = LINE.INV_NUMBER AND LINE.P_CODE = PRODUCT.P_CODE AND CUSTOMER.CUS_CODE = 10014 ORDER BY INV_NUMBER; b. SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT P, VENDOR V WHERE P.V_CODE = V.V_CODE ORDER BY P_PRICE; c. SELECT P_CODE, VENDOR.V_CODE, V_NAME FROM VENDOR LEFT JOIN PRODUCT ON VENDOR.V_CODE = PRODUCT.V_CODE; d. SELECT E.EMP_MGR, M.EMP_LNAME, E.EMP_NUM, E.EMP_LNAME FROM EMP E, EMP M WHERE E.EMP_MGR=M.EMP_NUM ORDER BY E.EMP_MGR;

d. SELECT E.EMP_MGR, M.EMP_LNAME, E.EMP_NUM, E.EMP_LNAME FROM EMP E, EMP M WHERE E.EMP_MGR=M.EMP_NUM ORDER BY E.EMP_MGR;

22. What is the 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;

d. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE = 24288;

42. What is the command 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 P_PRICE? 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;

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;

34. The basic SQL aggregate function that gives the total of all values for a selected attribute in a given column is ____. a. COUNT b. MIN c. MAX d. SUM

d. SUM

4. The SQL command that enables you to make changes in the data is ____. a. INSERT b. SELECT c. COMMIT d. UPDATE

d. UPDATE

9. Which command would you use when changing the date in the PRODUCT table? a. CHANGE PRODUCT SET P_INDATE = '18-JAN-2008' WHERE P_CODE = '13-Q2/P2'; b. ROLLBACK PRODUCT SET P_INDATE = '18-JAN-2008' WHERE P_CODE = '13-Q2/P2'; c. EDIT PRODUCT SET P_INDATE = '18-JAN-2008' WHERE P_CODE = '13-Q2/P2'; d. UPDATE PRODUCT SET P_INDATE = '18-JAN-2008' WHERE P_CODE = '13-Q2/P2';

d. UPDATE PRODUCT SET P_INDATE = '18-JAN-2008' WHERE P_CODE = '13-Q2/P2';

13. What happens when you issue the DELETE FROM tablename command without specifying a WHERE condition? a. no rows will be deleted b. the first row will be deleted c. the last row will be deleted d. all rows will be deleted

d. all rows will be deleted