Database Design SQL Review &

Database Programming SQL Review

Name ______________________________ 1. SQL is the ________ standard language to access relational databases. 2. (Projection / Selection) is done by listing column names in a select list of a query. 3. To remove duplications within the result set, you should use which key word after the word select. a. b. c. d. DISTINCT UNIQUE Either of the above. NONE of the above.

4. Which statement(s) below would likely fail? a. SelectT * FROM employees; (two Ts in the select word) b. SELECT * FROM employees (unless employees created with double quotes) c. SELECT * FROM employees; d. Select * 5. Number columns returned in queries are normally displayed (Left, Right, Center) justified. 6. Which query(s) would return a result of 50? a. b. c. d. Select (100*2+50)/5 from dual; Select 100*2 +50/5 from dual; Select (100)*2+(50/5) from dual; Select ((100 *2)+50)/5 from dual;

7. Consider the statement:

SELECT salary + commission_pct as compensation, Commission is ||commission_pct as Commission FROM employees;

What is the value of the compensation and commission in the result set for a row where the value of the salary column is 1000 and the commission_pct column is null? (ignore double quotes) a. compensation: 1000 b. compensation: null
Oracle Academy

commission: null commission: null

1 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

c. compensation: null commission: Commission is d. compensation: 0 commission: Commission is 0 8. For a column alias to contain a space or retain case, it must (choose the best answer): a. b. c. d. Be enclosed within the ampersand symbol. Be enclosed with single quotes. Be enclosed within parenthesis Be enclosed with double quotes

9. A SQL query must have both a list of items following the keyword _________ and a data source following the keyword _____________. 10. (True / False) In the where clause of a query, the column name and value are interchangeable on either side of the operator, therefore department_id = 90 or 90 = department_id is interchangeable. 11. Literal values for text or dates must be enclosed within _______________. 12. Between 900 and 1100 would include how many integers? (199 / 200 / 201 ) (circle one) 13. A SQL condition Where manager id IN (100, 101, 201) would be equivalent to three compound conditions using the ( OR / AND ) operator. 14. In the LIKE operator, which symbol is used to represent a single text character or numeric digit? a. b. c. d. "_ " "/" "&" "%"

15. Which of the following has the highest precedence? (AND , NOT, OR , all are equal must use parenthesis 16. By default in an ORDER BY clause, where would rows with a null value appear? (beginning of result set, end of result set, not listed in result set) 17. To reverse the default order of a sorting operation in the ORDER BY clause we use which word __DESC_________. 18. A column may be sorted in an ORDER BY clause by: (circle all true answers) a column name or expression in the select list. a. a column alias in the select list. b. a number representing the column position in the select list. c. a column found in the data source but not in the select list.
Oracle Academy 2 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

19. Row functions return ( a value for each row in the data source, a value for each row in the result set, always only a single value). 20. To always return capitalized text, use the function _____________. 21. The result of SELECT SUBSTR(abcdefg, 3,1) FROM dual; is a. b. c. d. Abc c d none of the above

22. The result of SELECT TRIM(x from xxAxx) FROM dual; is a. b. c. d. Axx xxA A xAx

23. Which function(s) would result in a value of 46? a. Select substr(46.567, 1, 2) from dual; (works because of implicit conversion-not if in doubles quotes) b. Select round( 46.567, -1) from dual; c. Select round(46.567) from dual; d. Select trunc(46.567) from dual; 24. To add one hour to the current time, we should use: a. b. c. d. sysdate + 1 to_char(sysdate, HH:MI:SS)+1 sysdate + 1/24 to_char(sysdate) + 1/24

25. Which format model was used to convert 25-MAY-04 to May Twenty-Fifth, Two Thousand Four? a. b. c. d. TO_CHAR('25-MAY-04','DD-MON-YY'), 'Month Ddspth, Year' TO_CHAR(TO_DATE('25-MAY-04','DD-MON-YY'), 'Month Dd, Year') TO_DATE(TO_CHAR('25-MAY-04','Dd-Month-YYYY'), 'Month Ddspth, YYYY') TO_CHAR(TO_DATE('25-MAY-04','DD-MON-YY'), 'Month Ddspth, Year')

26. In the arithmetic expression: salary*12 - 400, which operation will be evaluated first?
Oracle Academy 3 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

27. In date formats to use Oracles built in algorithm for determining the century digits of the year when only providing the last two digits, we should use which format element? (HH, CC, RR, YY) 28. (Projection / Selection) is done by using a WHERE clause in a SQL statement 29. Which of the following can be used in the SELECT statement to return all columns of data in a table? a. b. c. d. ALL Columns * DISTINCT

30. Which of the following is the Order of Precedence for arithmetic expressions? a. b. c. d. addition, subtraction, multiplication, division subtraction, multiplication, addition, division division , multiplication, addition, subtraction multiplication, division, addition, subtraction

31. Mr. /Ms. Steven King is an employee of our company. hich statement below will return a list of employees in the following format? a. SELECT 'Mr./Ms. '||first_name||' '||last_name ||' '||'is an employee of our company.' AS "Employees" FROM employees; b. SELECT "Mr./Ms."||first_name||' '||last_name 'is an employee of our company.' AS "Employees" FROM employees; c. SELECT Mr./Ms. ||first_name||' '||last_name ||' '||"is an employee of our company." AS "Employees" FROM employees; d. SELECT 'Mr./Ms. 'first_name,last_name ||' '||'is an employee of our company.' FROM employees; 32. For a column alias to contain a space or retain the proper case, it must: a. b. c. d. Be enclosed with single quotes. Be enclosed within parentheses. Be enclosed with double quotes Be enclosed within the ampersand symbol

33. If any column value in an arithmetic expression is null, a. The query will create an error message. b. The result will be zero.
Oracle Academy 4 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

c. The result is null. d. The SQL interpreter can not process the query. 34. Which query will return three columns each with UPPER CASE column headings? a. SELECT "Department_id", "Last_name", "First_name" FROM employees; b. SELECT DEPARTMENT_ID, LAST_NAME, FIRST_NAME FROM employees; c. SELECT department_id, last_name, first_name AS UPPER CASE FROM employees; d. SELECT department_id, last_name, first_name FROM employees; 35. When used in a WHERE clause, which logical condition operator will return TRUE, only if both conditions are TRUE? a. b. c. d. OR NOT AND BETWEEN

36. In this database, product_id values are stored like XY01, XY02 while quantity values are stored as numbers. After executing this query, which statement below is TRUE?
SELECT quantity, product_id FROM products ORDER BY quantity and product_id

a. b. c. d.

The results are sorted numerically. The results are sorted first numerically then alphabetically. The results are sorted first alphabetically then numerically. The results are sorted alphabetically.

37. The following query will return which result?

SELECT last_name AS "Employee Name", job_id, code_number, hire_date FROM employees ORDER BY code_number ASC;

a. Results for the hire_date column will be displayed from smallest to largest date. b. All column results will be ordered from smallest to largest value. c. Results for the code_number column will be displayed from smallest number to largest number. d. Only the code_number column will be returned.

Oracle Academy

5 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

38. You want to produce query results that display the last_name, first_name, department_id and salary of all employees. Display the first_name, last_name and department _id in ACS but for employees in the same department display the salary results in descending order. Also, if two employees have the same last_name, you want the first names to be displayed in ascending order. a. b. c. d. ORDER BY department_id, salary ASC, last_name, first_name DESC; ORDER BY last_name, first_name,department_id, salary DESC ORDER BY department_id, salary, last_name, first_name DESC; ORDER BY department_id DESC, salary , last_name||' '||first_name ASC

39. In the following query, what will be evaluated first?

SELECT job_id, salary, hire_date FROM employees WHERE salary = 4000 OR job_id = 'AD_PRES' AND hire_date LIKE '03-JUN-04';

a. b. c. d.

job_id = 'AD_PRES' AND hire_date LIKE '03-JUN-04'; salary = 4000 OR job_id = 'AD_PRES salary = 4000 LIKE '03-JUN-04'

40. In the LIKE operator, which symbols can be used? a. b. c. d. & and % % and _ $ and / * and _

41. The following query will return what values?

SELECT employee_id "number", salary "pay" FROM employees WHERE employee_id = 103 OR salary = 4000;

a. All employees plus those with employee id's of 103 whose salary is 4000. b. Only employees with employee id's of 103 whose salary is 4000. c. Any employee with an employee_id of 103 and also any employee whose salary is 4000. d. Employees with an employee_id equal to " number" whose salary values are also equal to "pay".

Oracle Academy

6 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

42. What value(s) could be displayed?

SELECT prefix FROM phone WHERE prefix BETWEEN 360 AND 425 OR prefix IN(515, 206, 253) AND BETWEEN 555 AND 904);

a. b. c. d.

625 902 410 499

43. Which query will display of all employees whose last names start with "S" and have an 'ae' anywhere in their last name? a. SELECT last_name FROM employees WHERE last_name LIKE '_S%ae%'; b. SELECT last_name FROM employees WHERE last_name LIKE 'S_ae%'; c. SELECT last_name FROM employees WHERE last_name LIKE 'S&ae&'; d. SELECT last_name FROM employees WHERE last_name LIKE 'S%ae%'; 44. A column may be sorted in an ORDER BY clause by: (choose all true answers) a. b. c. d. column name or expression in the SELECT list. A column alias in the SELECT list. Placing the ORDER BY clause before the SELECT statement. Putting a column number in the SELECT list.

45. The following query will return which result?

SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY 4 DESC;

a. Only column 4 will be displayed. b. All 4 columns will be displayed in descending order. c. 4 columns will be displayed with hire_dates displayed with the most recent dates listed first. d. The hire_date column
Oracle Academy 7 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

46. If hire_date is 13-May-04, write the format for each example below: TRUNC(hire_date) = _____________ TRUNC(hire_date, 'MONTH') = ___________ ROUND(hire_date, 'YEAR') = ____________ 47. Make "rein" out of "reindeer"? __________________ 48. How do I take the "O" off of "Oracle"? ________________ 49. How can I turn 'Snowman' into ****Snowman****?

Oracle Academy

8 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

Database Programming Part II

1. Which query below needs to qualify one or more columns in the select list with the table name or a table alias? a. Select first_name, last_name, department_name, employee_id from departments, employees where employees.department_id = departments.department_id; b. Select manager_id, department_id, job_id, department_name from departments, employees where employees.department_id = departments.department_id; c. both of the above d. none of the above 2. To avoid a Cartesian product, if there are four tables in the FROM clause, what is the minimum joins that must be specified? a. b. c. d. one two three four

3. A join between tables where the result set finds matching values in both tables but does not return any rows where a match is NOT found could include a (circle all possible answers) a. b. c. d. e. f. g. 4. a. b. c. d. equijoin self join nonequijoin simple join natural join full outer join inner join Nonequijoins are normally used with (circle all true answers) ranges of numbers ranges of text ranges of dates ranges of rowids

Oracle Academy

9 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

5. Which query below will return all the customers even if they do not have a sales representative but will not return employees who do not service a customer? a. b. c. d. e. f. Select c.name, e.name from customers c, emp e where c.sales_rep = e.id; Select c.name, e.name from customers c, emp e where c.sales_rep (+) = e.id; Select c.name, e.name from customers c, emp e where c.sales_rep = e.id(+) Select c.name, e.name from customers left outer join emp; Select c.name, e.name from customers right outer join emp; Select c.name, e.name from customers full outer join emp;

6. To create a single join between two tables that have columns in each table that matches in name and datatype we could use ANSI 99 syntax that includes the key words (along with table names): (choose all correct answers) a. b. c. d. natural join natural join using join using join on

7. Group functions return a value for (each row / a row set) and (include / ignore) null values in their computations. They can avoid computations involving duplicate values by including the key word ___________________ argument. 8. Which group functions below act on text, number and date datatypes? a. b. c. d. e. SUM MAX MIN AVG COUNT

9. Not all employees in Department 50 earn a commission. Which SELECT statement will display 1% commission for these employees as "Commission" as well as the employee's name? a. SELECT first_name, last_name, NVL(commission_pct, 1.0) AS Commission FROM employees WHERE department_id IN (50, 80); b. SELECT first_name, last_name, NVL(commission_pct 1.0) AS Commission FROM employees WHERE department_id=80; c. SELECT first_name, last_name NVL(comm._pct 1.0) AS Commission FROM employees WHERE department_ID IN(50, 80); d. SELECT first_name, last_name, NLV(commission_pct 1.0) AS Commission FROM employees WHERE department_ID IN(80);

Oracle Academy

10 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

10. Which statements are true about the query shown below?
SELECT last_name, department_id, salary, CASE department_id WHEN 10 THEN salary*1.25 WHEN 90 THEN salary*1.50 WHEN 130 THEN salary*1.75 ELSE salary END As "No Change" FROM employees;

a. b. c. d.

Employees in department 130 whose salary is 3000 will now earn 5250 Employees in department 20 will not receive a raise Employees in department 10 receive the largest raise All of the above are true.

11. Sue Doe's salary is 6.75 per hour, what will be returned for her in the following statement?
SELECT first_name, last_name, salary, NULLIF(salary,10) FROM f_staffs WHERE last_name IN ('Doe', 'Miller','Tuttle')

a. salary b. 10 c. 6.75 d. null 12. Since Monique Tuttle is the manager of Global Fast Foods, she is not assigned a manager_id. Which statement is TRUE about the following query?
SELECT last_name, COALESCE(manager_id, salary) info FROM f_staffs WHERE employee_id = 19;

a. The results set of this query will return a manager_id and salary for all employees who have been assigned a manager. b. The results set of this query will return null for Monique Tuttle in the manager_id column c. The results set of this query will return either manager_id or salary whichever is the first non-null expression d. The results set of this query will return only persons whose manager_id is null. 13. a. b. c. d. Which statement below is correct? NVL(email,'unlisted') NVL(salary, 1000) NVL(termination_date, '01-JAN-07') All of the above are correct

Oracle Academy

11 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

14. Which statement(s) below will return null if the value of v_sal is 50? a. b. c. d. SELECT nvl(v_sal, 50) from dual; SELECT nvl2(v_sal, 50) from dual; SELECT nullif(v_sal, 50) from dual; (all others return 50) SELECT coalesce (v_sal, Null, 50) from dual;

15. What would be the result of the following statement on a row where the job_id is Teacher? _______________________ Select job_ID|| CASE job_id WHEN Pres THEN Overpaid WHEN Programmer THEN Well paid ELSE Underpaid END FROM EMPLOYEES; 16. Why not use decode with the query in the above question since it is less code? _____________________________________________
17. What kind of join is shown in the example below? SELECT e.employee_id, e.last_name, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id);

a. Outer Join b. Inner Join c. Self Join d. Cross Join 18. a. b. c. d. Which statement below is correct? NVL2(hire_date, '4250', 'not known') NVL2(salary, 'none','05-JAN-05') NVL2(auth_expense_amt,'30000','not due') All of the above are correct

19. In a DECODE function, what will be returned in no default value is specified? a. b. c. d. the search value expression the second expression a null value a default value must be specified to avoid a syntax error

Oracle Academy

12 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

20. In the following query, what value will be returned for the Disk Jockey if his auth_expense_amt is currently null?
SELECT last_name, specialty, auth_expense_amt, DECODE(partner_type, 'Wedding Coordinator', 1.10*auth_expense_amt, 'Manager' , 1.50*auth_expense_amt, 'Disk Jockey', 1.10*auth_expense_amt, 'non data') EXPENSE AMOUNT FROM d_partners;

a. 'non data' b. zero c. null d. 1.10 21. If a join condition is omitted or is invalid, what results set is produced? a. null value b. outer join c. natural join d. Cartesian product 22. When data from more than one table in the database is required, which of the following conditions must be met? a. b. c. d. a equijoin can be used an outer join can be used an inner join can be used all of the above can be used

23. True/False If two tables have columns with the same name, the column names must be prefixed with the table name. 24. Which type of join frequently involves primary and foreign key complements? a. b. c. d. Equijoin Cartesian product Primary key join Referential join

25. Which of the following are TRUE about table aliases used in join statements? a. Table aliases can be of any length as long as they are enclosed in single quotes b. If a table alias is used in the FROM clause, then it must be substituted for the table name throughout the SELECT statement c. Table aliases, once used, are valid for all SELECT statements involving the same tables d. Table aliases must be written in UPPER CASE
Oracle Academy 13 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

26. Mark T for the statements that are True. ____a. A join is a type of query that gets data from more than one table based on columns with the same name. ____b. In order to join tables, there must be a common column in both tables and that column is usually a primary key in one of the tables. ____c. A Cartesian product occurs because the query does not specify a WHERE clause ____d. Table aliases are required to create a join condition ____e. If a table alias is used for a table name in the FROM clause, it must be substituted for the table name throughout the SELECT statement ____f. Table alias must be only one character in length ____g. A simple join or inner join is the same as an equijoin 27. Which join would you use to find which employees have salaries between the lowest salary of 1000 and the highest salary of 2000 per month? a. natural join b. nonequijoin c. equijoin d. right outer join 28. True/False In an outer join, the (+) is placed on the side of the join that you want data returned. 29. True/False A cross join produces a Cartesian product. 30. Which of the following are true about Natural Joins? a. The join is based on all columns in the two tables that have the same name b. The join selects rows from the two tables that have equal values in all matched columns c. If the columns having the same name have different datatypes, the join will return only one column d. The join may require a datatype change to join two columns 31. True/False A NATURAL JOIN must have a USING clause if the columns names are different. 32. In the following FROM clause. Which table contains data that we want returned even though there may be not matching values in the other table?
FROM d_clients c RIGHT OUTER JOIN d_events

a. d_clients b. d_events c. both tables d. neither table

Oracle Academy

14 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

33. Which statements are valid using MIN and MAX? a. SELECT MIN(termination_date) b. SELECT MIN(first_name) c. SELECT MIN(salary) d. SELECT MIN( song_id) 34. Which statement will return the number of students who have a first name of 'Amy'? a. COUNT(*) WHERE first_name LIKE 'Amy' b. COUNT('Amy') c. COUNT (DISTINCT first_name) WHERE first_name LIKE 'Amy'

Oracle Academy

15 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

Database Programming Part II

1. The CTAS (Create Table As Select) syntax can be used to: (circle all correct responses) a. Create a new table without data based on the structure and column names of an existing table. b. Create a new table containing the same structure but different column names as an existing table. c. Create a new table containing the structure and data of an existing table. d. Create a set of pseudocolumns that query and display part of an existing table. 2. SYSDATE and USER are not permitted to be referenced in a. the values clause of an insert statement b. efault values for column definitions c. check constraints d. none of the above 3. Metadata (information about the database structures) is stored in the schema ___________and can be viewed through a set of views known collectively as the ___________ dictionary. 4. Data Dictionary Views that contain information about all schemas in the database start with: a. USER_ b. ALL_ c. DBA_ d. V$ 5. A column that will be used to store text data with a size of 4000 bytes or larger should be defined as which datatype? a. varchar2 b. CLOB c. LONG d. CHAR 6. To store time with fractions of seconds, which datatype should be used for a table column? a. date b. datetime c. timestamp d. interval day to second 7. The datatype TIMESTAMP WITH TIME ZONE stores: a. The current date, time (including fractions of seconds) and timezone b. The current date, time (without fractions of seconds) and timezone c. The current date, time (including fractions of seconds) and offset from UTC
Oracle Academy 16 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

d. The current date, time (including fractions of seconds) and offset from the database time. 8. To keep a column from being accessed without the performance penalty of rewriting all the table datablocks you can: a. alter table modify column b. alter table drop column c. alter table set unused d. drop column columname 9. If a select list contains both a column as well as a group function then what clause is required? a. having clause b. join clause c. order by clause d. group by clause 1. True / False) Both the order by and group by clauses can contain columns not found in the select list of their query.

21. Indicate why the query below will not execute successfully. (There may be more than one reason). Select * from employees where job_id = Select job_id from employees where department_id = 90 and (select salary from employees where emp_id = 101) > salary; 22. Indicate why the following query will not execute successfully. (There may be more than one reason.) Select job_id, avg(salary) from employees where avg(salary) > (select avg(salary) from employees group by department_id) group by job_id; 23. In using the INSERT statement, if a column list is not provided then what values must be included? a. a value for any column defined as not null. b. a value for any column that does not have an explicit default value. c. a value for every column in the table in the correct order. d. only values for column(s) that make up the primary key.

Oracle Academy

17 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

24. You wish change an existing department_id to a new department_id that does not exist. There is a foreign key for department_id on your employees table that references the primary key in the departments table. What DML operations are needed? a. an insert to departments, an update on the employees, a delete on the departments b. an insert to employees, a update on departments, a delete on employees. c. an update to the departments table followed by a merge to the employees table. d. an update to the departments table and a update to the employees table.

25. When the where clause is missing in a delete statement then: (choose all true answers) a. all rows will be deleted from the table. b. the table will be removed from the database. c. rollback will restore the rows if a commit has not been issued. d. an error message will be displayed indicating incorrect syntax. 26. (True / False) A check option in a subquery is a constraint that restricts DML based on the where clause condition. 27. Implicit defaults will be used in the INSERT clause for columns not found in the _ ____________ list. To assign a default value explicitly for columns in the values clause, you must use the key word ___________. 28. To synchronize a history table with the latest changes, instead of using insert and update, we can use a new conditional DML statement called ___________. 29. (True / False) Multiple subqueries may be used in SELECT, INSERT and UPDATE statments but not DELETE statements. 30. Which of the following are true about the GROUP BY clause? a. If you use SUM in a SELECT clause and any other individual columns, each individual column must also appear in the GROUP BY clause. b. You can use a column alias in the GROUP BY clause c. The WHERE clause includes rows before they are divided into groups d. A GROUP BY clause cannot be used in a SELECT statement without having a group function in the SELECT statement . 31. Which statements are True about subqueries? a. place the subquery on the right side of the comparison condition b. enclose the subquery in single quotes c. use relational operators such as = > or < d. the inner and outer queries must get values from the same table

Oracle Academy

18 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

32. The HAVING clause is similar to which clause? a. SELECT b. FROM c. WHERE d. GROUP BY 33. What will happen if the inner query returns a null value to the outer query in a single-row subquery statement? a. A zero is returned to the outer query b. The query crashes and exits c. No rows will be returned, WHERE can not compare to null d. Values equal to null will be returned 34. If the inner query returns the values 2500, 4200, 6000 7000, which of the following values could the outer query below return? SELECT salary FROM employees WHERE salary > ANY 8000 2400 6500 4750

a. b. c. d.

35. True/False In a multiple-row subquery, if one of the values returned by the inner query is null, the entire query returns no rows. 36. What is the implicit method of adding a null value? a. Add null to the column list. b. Add a zero to the column list c. Omit the column from the column list. d. You can't implicitly add a null value 37. What is the explicit method of adding a null value? a. Specify a zero in the VALUES clause b. Add the NULL keyword in the VALUES clause c. Omit the value and it will be added automatically d. You can't expicitly add a null value 38. When a table is deleted, what happens to the columns in the table? a. The columns and the entire table structure is deleted b. The table structure is set to null c. The table structure remains, only the rows are deleted d. The table can't be deleted if it contains rows

Oracle Academy

19 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

39. Which of the following are valid table names? a. _8Eight b. 8_Eight c. Eight8_8 d. E$_ight 40. Which one of the following is not an ALTER TABLE function? a. add a new column b. modify an existing column c. define a default value for the new column d. modifying existing rows 41. Why would you TRUNCATE a table rather than simply using DELETE? a. When a table is truncated the rows remain b. When a table is truncated it can be restored easily c. When a table is truncated it releases storage space d. When a table is truncated the primary keys are not deleted 42. Which of the following is the function of the DESCRIBE command? a. Returns the data in each row of the table b. Returns a description of the tablename, column and datatypes of the columns c. Returns non duplicate values in the rows d. Returns a description of the data dictionary tables in a schema 43. Which statement perfoms and update and insert at the same time? a. DESCRIBE b. INSERT c. MERGE d. UPDATE 44. True/False The following statement is valid? INSERT INTO students VALUES (114, 'Don', 'Rogers', 'DROGERS', '515.127.4561', TO_DATE('February 3, 2004', 'Month DD, RRRR'),

Oracle Academy

20 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

Database Programming Part III

1. Which identifiers listed below are invalid names within the Oracle database? Rename them. a. a table named: Long_table_name_for_storing_data name exceeds 30 characters b. a sequence named: 4generatingUniqueNumbers cannot start with a number c. a column named: Primary_Key$Column d. a view named: My&ViewOfData & is not a valid symbol for names 2. SYSDATE and USER are not permitted to be referenced in a. the values clause of an insert statement b. default values for column definitions c. check constraints d. none of the above 3. What will be the column names resulting from the following view definition: Create or Replace View Name_Vu (Person_Name, Title, Pay) as Select last_name as name, job_id position, salary as compensation from employees; a. LAST_NAME, JOB_ID, POSITION b. PERSON_NAME, TITLE, PAY c. NAME, POSITION, COMPENSATION d. none of the above 4. Metadata (information about the database structures) is stored in the schema ______ and can be viewed through a set of views known collectively as the ___________ dictionary 5. Data Dictionary Views that contain information about all schemas in the database start with: a. USER_ b. ALL_ c. DBA_ d. V$ 6. A column that will be used to store character or text data with a size of 4000 bytes or larger should be defined as which datatype? a. varchar2 b. CLOB c. LONG d. CHAR 7. To store time with fractions of seconds, which datatype should be used for a table column? a. date b. datetime c. timestamp d. interval day to second
Oracle Academy 21 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

8. To remove all the rows from the table without generating undo or rollback we should use the command: a. drop rows b. delete rows c. delete table d. truncate table 9. Comments on tables and columns can be stored for documentation by: a. embedding /* comment */ within the definition of the table. b. using the ALTER TABLE CREATE COMMENT syntax c. using the COMMENT ON TABLE or COMMENT on COLUMN d. using an UPDATE statement on the USER_COMMENTS table 10. To permit the deletion of a parent (primary key) record while implicitly removing only the foreign key column value, we can define a referential constraint using: a. on delete restrict b. on delete set null c. on delete cascade d. on delete delete 11. (True / False) Multicolumn constraints can be defined at the column or table level. 12. (True / False) To give a constraint a meaningful name it must follow the key word constraint. 13. (True / False) All integrity constraints are created implicitly in a new table if they are found in the source table. 14. By default, unique indexes are created when which constraints are enabled? a. primary key b. foreign key c. not null d. check e. unique 15. Constraints can be: (circle all true answers) a. enabled or disabled b. created or dropped c. changed through alter constraint d. viewed in user_constaints 16. (True / False) Like tables, to change a view definition, use the alter view syntax. 17. (True / False) To use a view, a person must have security privileges on the tables that the view contains. 18. (True / False) Insert, update, and delete are never permitted on a view created with the with check option clause.
Oracle Academy 22 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

19. An inline view is an unnamed select statement found: a. in the user_views data dictionary view b. in a special database column of a users table c. enclosed in parenthesis within the select list of a surrounding query d. enclosed in parenthesis within the from clause of a surrounding query 20. TOP-N analysis makes use of a sorted inline view in the from clause and (in the outer query) a. the rowid pseudocolumn in the where clause b. the level pseudocolumn in the where clause c. the rownum column in the order by clause d. the rownum column in the where clause 21. Using the following SQL Statement, What are the first 5 numbers created by the sequence? 22. CREATE SEQUENCE sample_seq
23. 24. 25. 26. 27. 28. INCREMENT BY 50 START WITH 50 MAXVALUE 99999 NOMINVALUE CYCLE CACHE 4;

29. 0,50,100,150,200 30. 50,100,150,200,250 31. 50,51,52,53,54,55 32. 50,250,450,640,850 33. Sequences can be used to: (choose all correct responses) a. ensure primary key values will be unique and consecutive b. ensure numbers will be unique even though gaps may exist c. use a range of numbers and optionally cycled through them again d. set a fixed interval for successive number. 34. The ALTER SEQUENCE syntax can be used to: (choose all correct responses) a. change the start with of an existing sequence b. reset the max value to a lower number than was last used c. change the name of the sequence d. change the interval of the sequence 35. (True / False) Indexes may speed up access to rows in a table. 36. (True / False) To use an index you must name it in the FROM clause of your query. 37. A function-based (or functional) index is an index based on: a. a rowid and the column(s) key value(s)
Oracle Academy 23 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

b. expressions c. a method d. a bitmap for a range 38. An shareable alias for a database object (such as a table or view) that can also contain a schema name or even a database link is called a __________ 39. What will be the column names resulting from the following view definition: Create or Replace View Name_Vu (Person_Name, Title, Pay) as Select last_name as name, job_id position, salary as compensation from employees; a. LAST_NAME, JOB_ID, POSITION b. PERSON_NAME, TITLE, PAY c. NAME, POSITION, COMPENSATION d. none of the above 40. What command(s) release row locks assigned to a table because of DML statements? 41. (True / False) A rollback to a savepoint removes the savepoint. 42. (True / False) A statement failing due to a violation of an integrity constraint causes all other pending changes in the transaction to fail. 43. Which of the following best describes a simple view? a. Simple views consist of three or fewer columns b. Simple views derive data from only one table c. Simple views can only use the AVG and SUM in the GROUP BY clause d. Simple views can not be used with DML operations 44. Which statement is FALSE about NOT NULL constraints? a. NOT NULL constraints must be defined at the column level b. NOT NULL constraints ensure that a column contains NO null values c. NOT NULL constraints that are not named will be given a name by the Oracle Server d. NOT NULL constraints can be added to a column using the ALTER TABLE statement 45. Which type of constraint would ensure that no salary values were entered into a table that contained negative values? a. NON NULL b. PRIMARY KEY c. FOREIGN KEY d. CHECK 46. Which statement is TRUE about foreign key constraints? a. To add a foreign key constraint on a column, a corresponding column value must exist in the same table or another table. b. To add a foreign key constraint use the MODIFY TABLE statement c. To add a foreign key constraint reference the foreign key table using REFERENCES
Oracle Academy 24 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

d. To add a foreign key define it only at the table level 47. You have created a VIEW for a co-worker, however, you do not want any changes to be made to the underlying tables. Which of the following will prevent changes to the tables? a. Specify WITH CHECK OPTION b. Specify WITH NO UPDATE OPTION c. Specify WITH READ ONLY d. Specify WITH SELECT ONLY 48. When defining a CHECK constraint, which of the following will cause an error? a. CONSTRAINT hire_date CHECK (SYSDATE) b. CONSTRAINT department_id CHECK (department_id = 10) c. CONSTRAINT employee_id CHECK (NEXTVAL) d. CONSTRAINT location CHECK (location = 20) 49. For which column would you create an INDEX? a. A column that is small to make finding data faster b. A column that is updated often to make sure you know what has been changed c. A column containing a wide range of values d. A column that has only a few null values to speed up retrieval 50. The LINE_ITEM table contains these columns
LINE_ITEM --------------------LINE_ITEMID NUMBER(9) ORDER_ID NUMBER(9) PRODUCT_ID VARCHAR2(9) QUANTITY NUMBER(5) You created a sequence called LINE_ITEMID_SEQ to generate sequential values for the LINE_ITEMID column. Evaluate this SELECT statement: SELECT line_itemid_seq.CURRVAL FROM dual;

Which task will this statement accomplish? a. Increments the LINE_ITEMID column b. Displays the next value of the LINE_ITEMID_SEQ sequence c. Displays the current value of the LINE_ITEMID_SEQ sequence d. Populates the LINE_ITEMID_SEQ sequence with the next value 51. If a user wants to cancel previously granted privileges, which keyword is used? a. CANCEL b. DENY c. REMOVE d. REVOKE
Oracle Academy 25 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

52. Object privileges can be granted for all of the following except: a. tables b. views c. sequences d. indexes 53. When granting TABLE privileges WITH GRANT OPTION, the following occurs: a. The user granted the privilege can pass on the privilege to others b. The DBA cannot later revoke the privilege c. All users automatically inherit the privilege d. The user granted the privilege cannot GRANT PUBLIC on the privilege 54. What effect would the following statement have: GRANT ALTER ON employees TO PUBLIC a. Anyone logged into the database would be able to view the employees table b. Anyone logged into the database would be able to drop the employees table c. Anyone logged into the database would be able to update employee table records d. None of these choices is correct 55. What is the purpose of the PUBLIC option in the CREATE SYNONYM syntax? a. To allow users to lengthen object names. b. To allow DBAs to make the specified object accessible to all users c. To reveal the table structure i. To allow users to view tables without connecting to the database 56. What is the proper syntax for creating an index on the email column of the employees table a. CREATE INDEX emp_email_index ON employees (email); b. CREATE OR REPLACE emp_email_index FROM employees (email); c. CREATE INDEX emp_email_index ON employees, COLUMN = email; d. ADD INDEX emp_email_index INTO employees (email); 57. Which of the following in NOT a type of constraint? a. NOT NULL b. DISABLE c. PRIMARY d. CHECK 58. What constraint syntax option should be added in order to affect dependent integrity constraints? a. REFERENCES b. DROP c. CASCADE d. CHECK

Oracle Academy

26 Database Programming with SQL Copyright 2011, Oracle. All rights reserved

59. True/False The most efficient way to change column constraints is to drop the table, create new columns and re-enter each constraint. 60. Which of the following are functions of constraints? a. Specifying a condition that must be true. b. Enforcing rules at the table level. c. Preventing the deletion of a table if there are dependencies. d. All are functions of constraints 61. What data dictionary view contains the sequences created by a schema? a. ALL_SEQUENCES b. USER_SEQUENCES c. SEQUENCE_NAMES d. USER_VIEWS

Oracle Academy

27 Database Programming with SQL Copyright 2011, Oracle. All rights reserved