1.A view is a virtual table based on a SELECT query.
True
2. Authentication is the process the DBMS uses to verify that only registered users access the database.
True
3. SQL supports the conditional execution of procedures (IF-THEN-ELSE statements) that are typically supported by a programming language.
False
4. When creating a table with a SELECT statement, the CREATE TABLE command statement represents the subquery.
False
5. A subquery will automatically overwrite an existing table if it has the same name.
False
6. Use the command ADD to change column characteristics.
False
7. You must first enter the command ALTER TABLE in order to issue a MODIFY command to change a columns datatype.
True
8. The SQL command, UPDATE, successfully removes a table row.
False
9. The column information must be provided immediately after entering the INSERT INTO command to insert a column.
False
10. Embedded SQL refers to SQL statements contained within an application programming language such as Java.
True
11. The RDBMS automatically creates the ____ tables in which to store metadata.
data dictionary
12. A schema is a logical group of database objects like ____ and indexes that are related to each other.
tables
13. The format SMALLINT is a common SQL data type of ____.
numeric
14. Required SQL command keywords are notated as ____.
capitals
15. All changes in the table structure start by entering the ____ command first, followed by a keyword that produces the specific change.
ALTER TABLE
16. The ALTER TABLE command does NOT use the ____ keyword option.
UPDATE
17. When DROPPing a column from a table, the database administrator will first issue the ____ command to reference the table.
ALTER TABLE
18. Use the command keyword ____ to change the V_CODE attribute’s data type, for example, in a table called PRODUCT.
MODIFY
19. Use the SQL statement ____ PRODUCT VALUES (value1, value2, value3, …, valuen) to add values to the PRODUCT table.
True
20. Use the value ____ to insert a row with some blank data.
NULL
21. The Oracle equivalent to an MS Access AutoNumber is a(n) _____.
sequence
22. Which statement describes a feature of Oracle sequences?
Dropping a sequence does not delete values assigned to table attributes; it deletes only the sequence object from the database.
23. The _____ pseudo-column is used to select the next value from a sequence.
NEXTVAL
24. In Oracle, _____ make(s) it possible to merge SQL and traditional programming constructs, such as variables, conditional processing (IF-THEN-ELSE), basic loops (FOR and WHILE loops,) and error trapping.
procedural language
25. In embedded SQL, all host variables are preceded by a ________.
colon (:)
26. How is it possible for a weak entity to delete associated data related to its foreign key when the related strong entity deletes some rows of data as well?
The action is triggered.
27. An administrator must use ____ to create secured, conditional statements in SQL in case fundamental processes or functions change in the business.
PSM
28. An administrator can resolve the requirement for gathering multiple values from a SELECT statement to evaluate complex problems, for example, by processing those SELECT statements with ____.
cursors
29. The database administrator must use a format of ____ to store values up to 2 decimal places and 9 digits long.
NUMERIC (9,2)
30. Knowing the PRODMASTER table and PROD_QOH (or product quantity on hand) column representing the master product list, and another table called PRODSALES and PS_QTY (or purchased quantity) the SQL statement ____ should update the master list to reflect actual product in stock.
SET PRODMASTER.PROD_QOH = PROD_QOH – PS_QTY
31. Use the SQL statement ____ when trying to reference two tables for which data columns must be updated to reflect the current list of a MASTERINVENTORY, for example.
WHERE MASTERINVENTORY.PROD_ID = PRODSALES.PROD_ID
32. If an administrator requires a view of PRODUCTs that are below $50, the ____ statement must be used to specify that range.
WHERE
33. If new data was added to a database after a revect VIEW called PRICEOVER100 was queried, a database administrator can ____ to view the latest data in PRICEOVER100 view.
run the same CREATE VIEW query statement again
34. When attempting to execute an embedded SQL after only updating the name to a variable it uses, the developer found the DELETE statement could not find the variable to delete because ____.
a colon was not preceding the variable
35. Working with tables PRODUCT, VENDOR, and CUSTOMER, the administrator must issue the SQL command ____ to delete the ID of a vendor that the company is no longer working with?
DELETE FROM VENDOR WHERE V_CODE = ‘DEN543’;
36. Inserted data will, in most cases, place a value that is meant for the primary key column into the ____ part of the VALUES statement.
first
37. Which command would be used to delete the table row where the P_CODE is ‘BRT-345’?
DELETE FROM PRODUCT
WHERE P_CODE = ‘BRT-345’;
38. A table needs to change its CUST_FNAM column to accommodate very long names. Which command would satisfy the requirement and update its data type?
MODIFY CUST_FNAM CHAR(40);
39. Working with a PART table that has columns PART_CODE, PART_DESCRIPT, PART_PRICE, and VENDOR_CODE, how would a user drop the vendor information from the table?
ALTER TABLE PART DROP COLUMN VENDOR_CODE;
40. The database has tables CUSTOMER, VENDOR, PRODUCT, PART, and EXTERNALVENDOR. After consolidating some data into other tables for improved performance, which command should the administrator run to remove other inefficiencies in the database?
DROP TABLE EXTERNALVENDOR;
41. An administrator needs to ALTER a table called PRODUCT so that its P_PRICE column can hold a value like common numbers on a price tag. Which command would the administrator most likely use to update this column’s data type?
MODIFY P_PRICE DECIMAL(9,2);
42. If a user only needs to insert only required values to a table such as PROD_CODE and PROD_DESCRIPT, which SQL command example would allow for that?
INSERT INTO PRODUCT(PROD_CODE, PROD_DESCRIPT) VALUES (‘BRT-345′,’Titanium drill bit’);
43. Which SQL command would a database administrator use to delete a whole row from a VENDOR table whose ID is BET-345?
DELETE FROM PRODUCT WHERE V_CODE = ‘BET-345’;
44. Which of the following is NOT required in terms of syntax when a user wants to copy data from one table to another?
Use the * command to select all values.
45. An administrator can define a ____ constraint if there is a desire to assign a value to a column when a new row is added, especially if the correct value is unknown, but can be filled in later.
DEFAULT
46. An administrator can add a ____ constraint during the creation of a new table if, for example, it is known that a PROD_PRICE column cannot have negative numbers.
CHECK
47. When creating a child table, the ____ SQL keyword should be added to properly connect it back to the parent table.
FOREIGN KEY
48. Database administrator must create another logical group of database objects for a new application called “APP03” that will also use database services from the same system. How can an administrator create this successfully without affecting existing tables and indexes?
Run command, CREATE SCHEMA AUTHORIZATION APP03;
49. Tables require proper column names so users understand where values are inputted. Creating a child table called PRESCRIPTION that is related to two parent tables called PATIENT and DOCTOR, an administrator would most likely create column names: ____ for the child table.
DOC_ID, PAT_ID, DRUG_CODE, PRES_DOSAGE, PRES_DATE
50. IF a SQL subquery included the statement that reads SELECT P_CODE AS PART_CODE, P_DESCRIPT AS PART_DESCRIPT, P_PRICE AS PART_PRICE, VENDOR_CODE, then the new columns would have been created for specific information from a more overall table that was most likely called ____.
PRODUCT
51. When creating a table with the statement CREATE TABLE [IF NOT EXISTS] PART AS SELECT P_CODE AS PART_CODE, P_DESCRIPT AS PART_DESCRIPT, P_PRICE AS PART_PRICE, V_CODE FROM PRODUCT; the new table will have columns (in order) ____.
PART_CODE, PART_DESCRIPT, PART_PRICE, V_CODE
52. The ALTER TABLE statement must be followed by a ____ keyword to create another column.
ADD
53. The database administrator must add a constraint to the PART table so a PART’s price is never entered as a negative number. The administrator can enter the SQL statement ____ to satisfy the constraint command.
ALTER TABLE PART ADD CHECK (PART_PRICE >= 0);
54. An administrator must connect the V_CODE (vendor code) column between tables PART and VENDOR, where the VENDOR has a one-to-many relationship with PART. The part table should be updated by running the SQL statement ____.
ALTER TABLE PART ADD FOREIGN KEY (V_CODE) REFERENCES VENDOR (V_CODE);
55. After creating a new table called PART which is based on the PRODUCT table, an admin must add the first integrity rule to PART by running the SQL statement ____.
ALTER TABLE PART ADD PRIMARY KEY (PART_CODE);
56. Users must change the data type of the V_CODE column to allow 15 characters in the PRODUCT table by running the SQL statement ____, for example.
ALTER TABLE PRODUCT MODIFY V_CODE CHAR(15);
57. A database administrator must add a P_DESCRIPTION column to the PRODUCT table to provide more information to products other than its name. The administrator should run the SQL statement ____ to complete this requirement.
ALTER TABLE PRODUCT ADD (P_DESCRIPTION CHAR(255));
58. The PRODUCT table has product entries in the P_CODE column with ‘1355’ (for a jigsaw) and ‘1356 (for a hammer). The hammer must be given a specific name as called by the vendor and can be updated by running the SQL statement ____.
UPDATE PRODUCT SET P_NAME = ‘Silver-tip Hammer’ WHERE P_CODE = ‘1356’;
59. A user has to separate a PRODUCT table by extracting part information and moving them into a new table called PART. The PART columns will be named differently to PART_CODE, PART_DESCRIPT, and PART_PRICE, while the V_CODE (or vendor code) column will be the same. To fulfill this change, the user would issue the SQL statement ____.
INSERT INTO PART (PART_CODE, PART_DESCRIPT, PART_PRICE, V_CODE) SELECT P_CODE, P_DESCRIPT, P_PRICE, V_CODE FROM PRODUCT;
60. A user can insert only required information for a new product for columns P_CODE, P_DESCRIPTION, P_PRICE by entering the SQL statement ____.
INSERT INTO PRODUCT(P_CODE, P_DESCRIPT) VALUES (‘BRT-345′,’Titanium drill bit’),35.99;
61. When issuing a SQL command: INSERT INTO PRODUCT VALUES (‘BRT-345′,’Titanium drill bit’,’18-Oct-21′, 75, 10, NOT NULL, 0.06, NULL); to enter product information but the associated V_CODE (or vendor code) is unknown, the entry still fails. The failure occurs because ____.
NOT NULL is not an acceptable entry to a specific column in PRODUCT
62. If inserting a new phone number into the PHONE table, an example command would be entered as ____.
INSERT INTO PHONE VALUES (21225,’615′,’223-3234′);
63. Database administrator should insert the new VENDOR information: Bryson, Inc., Suite 615, Smithson, TN, 21225, with phone number 615-223-3234 using the commands ____.
INSERT INTO VENDOR VALUES (21228,’Bryson, Inc.’,’Suite 615′,’Smithson’,’TN’,’615′,’223-3234′);
64. An administrator can create an implicit cursor to count the number (W_NUM) of products (or P_CODE) between $10 (or W_P1) and $50 (or W_P2) when gathering information from the P_PRICE column of the PRODUCT table the line ____, for example.
SELECT COUNT (P_CODE) INTO W_NUM FROM PRODUCT
65. The company is working with a new vendor to sell new products. The VENDOR tables record the name and address of the vendor, along with a vendor ID, while the PRODUCT records each vendors’ product information with associated vendor ID. VENDOR has a one to many relationship with PRODUCT. The first step administrators took was to add in one new product from the new vendor; however, the INSERT INTO command fails. Administrators realize the failure stems from ____.
not adding the information for the new vendor into the VENDOR table.
66. An administrator lost data on a new table called PRODUCT after making a few modifications on each product’s information. VENDOR and CUSTOMER were also scheduled for modification. A manager found that the data was lost because ____.
the WHERE command was not specified
67. Build a report about product shelf prices by vendors with the query ____.
CREATE VIEW GROUP_PRICES AS SELECT VEND_NAME, PROD_DESCRIP, PROD_PRICE FROM PRODUCT GROUP BY VEND_NAME;
68. A database administrator can create a basic table for customers starting with just first and last name information by running the SQL statement ____.
CREATE TABLE CUSTOMER (CUST_ID INTEGER,FNAME CHAR (30),LNAME CHAR(30),PRIMARY KEY (CUST_ID));
69. The database admin should consider reconfiguration ____ to resolve any errors due to the conditional execution of certal SQL processes in the system.
persistent storage modules
70. If an administrator needs to create a new table with different column names PROD_CODE, PROD_DESCRIPT, except for V_CODE, the SQL line ____ should be added to the overall query.
SELECT P_CODE AS PROD_CODE, P_DESCRIPT AS PROD_DESCRIPT, V_CODE
Other Links:
See other websites for quiz:
Check on QUIZLET