You want to issue the following command on a database that includes your company's inventory information:
(4) The column named COLOR in the table named PRODUCTS will not be returned in subsequent reads of the table by Oracle, as is has been deleted logically.
Your supervisor has asked you to modify the AMOUNT column in the ORDERS table. He wants the column to be configured to accept a default value of 250. The table contains data that you need to keep. Which statement should you issue to accomplish this task?
You need to change the name of the EMPLOYEES table to the EMP table. Which statement should you use?
Which command could you use to quickly remove all data from the rows in a table without deleting the table itself?
You need to remove all the data in the SCHEDULE table, the structure of the table, and the indexes associated with the table. Which statement should you use?
The previous administrator created a table named CONTACTS, which contains outdated data. You want to remove the table and its data from the database. Which statement should you issue?
You need to truncate the EMPLOYEES table. The EMPLOYEES table is not in your schema. Which privilege must you have to truncate the table?
You need to store the HIRE_DATE value with a time zone displacement value and allow data to be returned in the user's local session time zone. Which data type should you use?
You are designing a table for the Human Resources department. This table must include a column that contains each employee's hire date. Which data type should you specify for this column?
You are designing a table for the Sales department. You need to include a column that contains each sales total. Which data type should you specify for this column?
You are creating the EMPLOYEES table. This table should contain the COMMISSION_PCT column and use a value of 10 percent if no commission value is provided when a record is inserted. Which line should you include in the CREATE TABLE statement to accomplish this task?
You want to create a table named TRAVEL that is a child of the EMPLOYEES table. Which of the following statements should you issue?
Which of the following SQL statements will create a table called Birthdays with three columns for storing employee number, name and date of birth?
(2) If no schema is explicitly included in a CREATE TABLE statement, the table is created in the current user's schema.
You need to ensure that the LAST_NAME column only contains certain character values. No numbers or special characters are allowed.
Which statement should you use to add a FOREIGN KEY constraint to the DEPARTMENT_ID column in the EMPLOYEES table to refer to the DEPARTMENT_ID column in the DEPARTMENTS table?
You need to add a NOT NULL constraint to the EMAIL column in the EMPLOYEES table. Which clause should you use?
You want to disable the FOREIGN KEY constraint that is defined in the EMPLOYEES table on the DEPARTMENT_ID column. The constraint is referenced by the name FK_DEPT_ID_01. Which statement should you issue?
You can view the columns used in a constraint defined for a specific table by looking at which data dictionary table?
You need to display the names and definitions of constraints only in your schema. Which data dictionary view should you query?
A table has a column: RESPONSE_TIME. This is used to store the difference between the time the problem was reported and the time the problem was resolved. Data in the RESPONSE_TIME column needs to be stored in days, hours, minutes and seconds. Which data type should you use?
A column that will be used to store binary data up to 4 Gigabyes in size should be defined as which datatype?
You need to remove the EMPLOYEE_ID column from the EMPLOYEES table. Which statement could you use to accomplish this task?
(3) Prevents data in the FAX column from being displayed, by performing a logical drop of the column.
Which statement will permanently remove all the data in the EMPLOYEES table, but will retain the table's structure and storage space?
When dropping a constraint, which keyword(s) specifies that all the referential integrity constraints that refer to the primary and unique keys defined on the dropped columns are dropped as well?
You need to create a composite primary key constraint on the EMPLOYEES table. Which statement is true?
You need to enforce a relationship between the LOC_ID column in the FACILITY table and the same column in the MANUFACTURER table. Which type of constraint should you define on the LOC_ID column?
When creating the EMPLOYEES table, which clause could you use to ensure that salary values are 1000.00 or more?
You administer an Oracle database, which contains a table named EMPLOYEES. Luke, a database user, must create a report that includes the names and addresses of all employees. You do not want to grant Luke access to the EMPLOYEES table because it contains sensitive data. Which of the following actions should you perform first?
Views must be used to select data from a table. As soon as a view is created on a table, you can no longer select directly from the table. True or False?
You have been asked to compile a report that identifies all adjunct professors who will be teaching classes in the upcoming term. You want to create a view that will simplify the creation of this report. Which CREATE VIEW statements will accomplish this task?
Your manager has just asked you to create a report that illustrates the salary range of all the employees at your company. Which of the following SQL statements will create a view called SALARY_VU based on the employee last names, department names, salaries, and salary grades for all employees? Use the EMPLOYEES, DEPARTMENTS, and JOB_GRADES tables. Label the columns Employee, Department, Salary, and Grade, respectively.
You administer an Oracle database. Jack manages the Sales department. He and his employees often find it necessary to query the database to identify customers and their orders. He has asked you to create a view that will simplify this procedure for himself and his staff. The view should not accept INSERT, UPDATE or DELETE operations. Which of the following statements should you issue?
(4) To make sure no rows are updated through the view that will hinder those rows from being returned by the view.
You must create a view that when queried will display the name, customer identification number, new balance, finance charge and credit limit of all customers. You issue this statement:
You want to create a view based on the SALESREP table. You plan to grant access to this view to members of the Sales department. You want Sales employees to be able to update the SALESREP table through the view, which you plan to name SALESREP_VIEW. What should not be specified in your CREATE VIEW statement?
You need to grant user BOB SELECT privileges on the EMPLOYEES table. You want to allow BOB to grant this privileges to other users. Which statement should you use?
(1) It allows a user's session to read from the table but only so that foreign key constraints can be checked.
Regular expressions are a method of describing both simple and complex patterns for searching and manipulating. True or False?
You want to grant user BOB the ability to change other users' passwords. Which privilege should you grant to BOB?
Which of the following privileges must be assigned to a user account in order for that user to connect to an Oracle database?
You are the database administrator. You want to create a new user JONES with a password of MARK, and allow this user to create his own tables. Which of the following should you execute?
User Kate wants to create indexes on tables in her schema. What privilege must be granted to Kate so that she can do this?
User JAMES has created a CUSTOMERS table and wants to allow all other users to SELECT from it. Which command should JAMES use to do this?
User BOB's CUSTOMERS table contains 20 rows. BOB inserts two more rows into the table but does not COMMIT his changes. User JANE now executes:
Using Oracle Proprietary join syntax, which operator would you use after one of the column names in the WHERE clause when creating an outer join?
You have two tables named EMPLOYEES and SALES. You want to identify the sales representatives who have generated at least $100,000 in revenue.
You need to create a report that lists all employees in department 10 (Sales) whose salary is not equal to $25,000 per year. Which query should you issue to accomplish this task?
Unit testing may be a composite of many different possible cases, or approaches, a user would opt to execute a transaction. True or False?
You need to remove all the rows from the SALES_HIST table. You want to release the storage space, but do not want to remove the table structure. Which statement should you use?
You need to reduce the precision of the AMOUNT_PLEDGED column to 5 with a scale of 2 and ensure that when inserting a row into the DONATIONS table without a value for the AMOUNT_PLEDGED column, a price of $10.00 will automatically be inserted. The DONATIONS table currently contains NO records. Which statement is true?
You want to create a database table that will contain information regarding products that your company released during 2001. Which name can you assign to the table that you create
(3) To disable the PRIMARY KEY and any FOREIGN KEY constraints that are dependent on the PO_NUM column
You successfully create a table named SALARY in your company's database. Now, you want to establish a parent/child relationship between the EMPLOYEES table and the SALARY table by adding a FOREIGN KEY constraint to the SALARY table that references its matching column in the EMPLOYEES table. You have not added any data to the SALARY table. Which of the following statements should you issue?
To do a logical delete of a column without the performance penalty of rewriting all the table datablocks you can issue the following command:
You need to add a NOT NULL constraint to the COST column in the PART table. Which statement should you use to complete this task?
(2) Add a FOREIGN KEY constraint to the EMPLOYEES table indicating that a manager must already be an employee.
The database administrator wants to allow user Marco to create new tables in his own schema. Which privilege should be granted to Marco?
When granting an object privilege, which option would you include to allow the grantee to grant the privilege to another user
A transaction makes several successive changes to a table. If required, you want to be able to rollback the later changes while keeping the earlier changes. What must you include in your code to do this
You are creating the EMPLOYEE table. This table should contain the COMMISSION column and use a value of 10 percent if no commission value is provided when a record is inserted. Which line should you include in the CREATE TABLE statement to accomplish this task?
(4) to activate the previously disabled constraint on the EMP_ID column while creating a PRIMARY KEY index
A view can be used to keep a history record of old data from the underlying tables, so even if a row is deleted from a table, you can still select the row through the view. True or False?
You need to create a view that when queried will display the name, employee identification number, first and last name, salary, and department identification number. When queried, the display should be sorted by salary from lowest to highest, then by last name and first name alphabetically. The view definition should be created regardless of the existence of the EMPLOYEE table. No DML may be performed when using this view. Evaluate these statements:
(1) A complex view is created that returns the sum of salaries per department, sorted by department name.
You create a CUSTOMERS table in which CUSTOMER_ID is designated as a primary key. You want the values that are entered into the CUSTOMER_ID column to be generated automatically. Which of the following actions should you perform?
(1) The CURRVAL virtual column will display the integer that was most recently supplied by a sequence.
You need to determine the table name and column name(s) on which the SALES_IDX index is defined. Which data dictionary view would you query
You create a view named EMPLOYEES_VIEW on a subset of the EMPLOYEES table. User AUDREY needs to use this view to create reports. Only you and Audrey should have access to this view. Which of the following actions should you perform?
When creating an index on one or more columns of a table, which of the following statements are true?
(1) You should create an index if the table is large and most queries are expected to retrieve less than 2 to 4 percent of the rows.
User Mary's schema contains an EMP table. Mary has Database Administrator privileges and executes the following statement:
User Susan now needs to SELECT from Mary's EMP table. Which of the following SQL statements can she use?
The CUSTOMERS table exists in user Mary's schema. Which statement should you use to create a synonym for all database users on the CUSTOMERS table?
Which statement should you use to increase the LAST_NAME column length to 35 if the column currently contains 200 records?
For a View created using the WITH CHECK OPTION keywords, which of the following statements are true?
You need to create a new view on the EMPLOYEES table to update salary information for employees in Department 50. You need to ensure that DML operations through the view do not change the result set of the view. Which clause should include in the CREATE VIEW statement?
You need to create a view on the SALES table, but the SALES table has not yet been created. Which statement is true?
You created a Top-n query report that displays the account numbers and new balance of the 800 accounts that have the highest new balance value. The results are sorted by payments value from highest to lowest. Which SELECT statement clause is included in your query?
Which SELECT statement could be used to display the 10 lowest paid clerks that belong to department 70?
(4) The sequence will continue to generate values after the maximum sequence value has been generated.
User Mary's schema contains an EMPLOYEES table. Mary has Database Administrator privileges and executes the following statement:
User Susan now needs to SELECT from Mary's EMPLOYEES table. Which of the following SQL statements can she use?
User SUSAN creates an EMPLOYEES table, and then creates a view EMP_VIEW which shows only the FIRST_NAME and LAST_NAME columns of EMPLOYEES. User RUDI needs to be able to access employees' names but no other data from EMPLOYEES. Which statement should SUSAN execute to allow this?
User CHANG has been granted SELECT, UPDATE, INSERT and DELETE privileges on the EMPLOYEES table. You now want to prevent Chang from adding or deleting rows from the table, while still allowing him to read and modify existing rows. Which statement should you use to do this?
(2) Views provide data independence for infrequent users and application programs. One view can be used to retrieve data from several tables. Views can be used to provide data security.
(2) The statement will not necessarily return the 25 highest new balance values, as the inline view has no ORDER BY.
A gap can occur in a sequence because a user generated a number from the sequence and then rolled back the transaction. True or False?
User CRAIG creates a view named INVENTORY_V, which is based on the INVENTORY table. CRAIG wants to make this view available for querying to all database users. Which of the following actions should CRAIG perform?
_________________ are special characters that have a special meaning, such as a wildcard character, a repeating character, a non-matching character, or a range of characters. You can use several of these symbols in pattern matching.
You have been asked to create a report that lists all corporate customers and all orders that they have placed. The customers should be listed alphabetically beginning with the letter 'A', and their corresponding order totals should be sorted from the highest amount to the lowest amount.
Using Oracle Proprietary join syntax, which two operators can be used in an outer join condition using the outer join operator (+)?
Which keyword(s) would you include in a CREATE VIEW statement to create the view regardless of whether or not the base table exists?
Which statement would you use to modify the EMP_ID_SEQ sequence used to populate the EMPLOYEE_ID column in the EMPLOYEES table?
The EMPLOYEES table has an index named LN_IDX on the LAST_NAME column. You want to change this index so that it is on the FIRST_NAME column instead. Which SQL statement will do this?
Regular expressions used as check constraints are another way to ensure data is formatted correctly prior to being written into the database table. True or False?
You want to grant privileges to user CHAN that will allow CHAN to update the data in the EMPLOYEES table. Which type of privileges will you grant to CHAN?
You need to join the EMPLOYEES table and the SCHEDULES table, but the two tables do not have any corresponding columns. Which type of join will you create?
You need to provide a list of the first and last names of all employees who work in the Sales department who earned a bonus and had sales over $50,000. The company president would like the sales listed starting with the highest amount first. The EMPLOYEES table and the SALES_DEPT table contain the following columns:
Which statement about the join syntax of an Oracle Proprietary join syntax SELECT statement is true?
Unit testing is done prior to a database going into production to ensure a random number of business requirements functions properly. True or False?
Steven King's row in the EMPLOYEES table has EMPLOYEE_ID = 100 and SALARY = 24000. A user issues the following statements in the order shown:
You want to determine the amount of each employee's bonus as a calculation of salary times bonus. Which of the following queries should you issue?