-----------------------------------------------------------------------------------------
Group Functions
1. What would the following SQL statement return?
SELECT MAX(hire_date)
FROM employees;
The hire date of the longest serving employee
The hire date of the newest employee (*)
The hire dates of all employees in ascending order
The hire dates of all employees
2. What two group functions can be used with any datatype?
STDDEV, VARIANCE
SUM, AVG
COUNT, SUM
MIN, MAX (*)
3. You can use GROUP functions in all clauses of a SELECT statement. True or False?
True
False (*)
4. Given the following data in the employees table (employee_id, salary, commission_pct)
DATA: (143, 2600, null
144, 2500, null
149, 10500, .2
174, 11000, .3
176, 8600, .2
178, 7000, .15)
What is the result of the following statement:
SELECT AVG(commission_pct)
FROM employees
WHERE employee_id IN( 143,144,149,174,176,178)
1.2125
This statement is invalid
0.2125 (*)
0.0425
5. The following statement will work even though it uses the same column with different GROUP functions:
SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
FROM employees;
True or False?
True (*)
False
6. Given the following data in the employees table (employee_id, salary, commission_pct)
DATA: (143, 2600, null
144, 2500, null
149, 10500, .2
174, 11000, .3
176, 8600, .2
178, 7000, .15)
What is the result of the following statement:
SELECT SUM(commission_pct), COUNT(commission_pct)
FROM employees
WHERE employee_id IN( 143,144,149,174,176,178)
SUM = .85 and COUNT = 6
SUM = 1.85 and COUNT = 6
SUM = .85 and COUNT = 4 (*)
SUM = 1.85 and COUNT = 4
-----------------------------------------------------------------------------------------
Test: Quiz: Count, Distinct, NVL
Count, Distinct, NVL
1. To include null values in the calculations of a group function, you must:
Precede the group function name with NULL
Count the number of null values in that column using COUNT
Convert the null to a value using the NVL( ) function (*)
Group functions can never use null values
2. What would the following SQL statement return?
SELECT COUNT(DISTINCT salary)
FROM employees;
A listing of all unique salaries in the employees table
The total number of rows in the employees table
The total amount of salaries in the employees table
The number of unique salaries in the employees table (*)
3. Using your existing knowledge of the employees table, would the following two statements produce the same result?
SELECT COUNT(*)
FROM employees;
SELECT COUNT(commission_pct)
FROM employees;
The first statement is invalid
Yes
No (*)
The second statement is invalid
4. What would the following SQL statement return?
SELECT COUNT(first_name)
FROM employees;
A listing of all non-null first names in the employees table
The total number of non-null first names in the employees table (*)
The total number of rows in the employees table
A listing of all unique first names in the employees table
marți, 15 martie 2011
section 3
Test: Quiz: Cross joins and Natural Joins
-----------------------------------------------------------------------------------------
Cross joins and Natural Joins
1. What happens when you create a Cartesian product?
All rows from one table are joined to all rows of another table (*)
No rows are returned as you entered wrong join-criteria
The table is joined to itself, one column to the next column, exhausting all possibilities
All rows that do not match in the WHERE clause are displayed
2. A NATURAL JOIN is based on:
Columns with the same name and datatype (*)
Columns with the same name
Columns with the same datatype and width
Tables with the same structure
3. The ___________ join is the ANSI-standard syntax used to generate a Cartesian product.
NATURAL
ALL
FULL
CROSS (*)
4. The join column must be included in the select statement when you use the NATURAL JOIN clause. True or False?
True
False (*)
Join Clauses
1. The following is a valid SQL statement.
SELECT e.employee_id, e.last_name, d.location_id, department_id
FROM employees e JOIN departments d
USING (department_id) ; True or False?
True (*)
False
2. You can do nonequi-joins with ANSI-Syntax. True or False?
True (*)
False
3. The keywords JOIN _____________ should be used to join tables with the same column names but different datatypes.
NATURAL ON
ON
WHEN
USING (*)
4. The primary advantage of using JOIN ON is:
The join happens automatically based on matching column names and data types
It will display rows that do not meet the join condition
It easily produces a Cartesian product between the tables in the statement
It permits columns that don?t have matching data types to be joined
It permits columns with different names to be joined (*)
5. Table aliases MUST be used with columns referenced in the JOIN USING clause. True or False?
True
False (*)
Inner versus Outer Joins
1. Given the following descriptions of the employees and jobs tables, which of the following scripts will display each employee?s possible minimum and maximum salaries based on their job title?
EMPLOYEES Table:
Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER (6)
FIRST_NAME VARCHAR2 (20)
LAST_NAME NOT NULL VARCHAR2 (25)
EMAIL NOT NULL VARCHAR2 (25)
PHONE_NUMBER VARCHAR2 (20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2 (10)
SALARY NUMBER (8,2)
COMMISSION_PCT NUMBER (2,2)
MANAGER_ID NUMBER (6)
DEPARTMENT_ID NUMBER (4)
JOBS Table:
Name Null? Type
JOB_ID NOT NULL VARCHAR2 (10)
JOB_TITLE NOT NULL VARCHAR2 (35)
MIN_SALARY NUMBER (6)
MAX_SALARY NUMBER (6)
SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
FROM employees e
NATURAL JOIN jobs j
USING (job_id);
SELECT first_name, last_name, job_id, min_salary, max_salary
FROM employees
NATURAL JOIN jobs; (*)
SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
FROM employees e
NATURAL JOIN jobs j;
SELECT first_name, last_name, job_id, min_salary, max_salary
FROM employees e
FULL JOIN jobs j (job_id);
SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
FROM employees e
NATURAL JOIN jobs j ON (e.job_title = j.job_title);
2. EMPLOYEES Table:
Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
DEPARTMENT_ID NUMBER (4)
DEPARTMENTS Table:
Name Null? Type
DEPARTMENT_ID NOT NULL NUMBER 4
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER (6)
A query is needed to display each department and its manager name from the above tables. However, not all departments have a manager but we want
departments returned in all cases. Which of the following SQL: 1999 syntax scripts will accomplish the task?
SELECT d.department_id, e.first_name, e.last_name
FROM employees e
LEFT OUTER JOIN departments d
WHERE (e.department_id = d.department_id);
SELECT d.department_id, e.first_name, e.last_name
FROM employees e
RIGHT OUTER JOIN departments d ON (e.employee_id = d.manager_id); (*)
SELECT d.department_id, e.first_name, e.last_name
FROM employees e
FULL OUTER JOIN departments d ON (e.employee_id = d.manager_id);
SELECT d.department_id, e.first_name, e.last_name
FROM employees e, departments d
WHERE e.employee_id RIGHT OUTER JOIN d.manager_id;
3. The following statement is an example of what kind of join?
SELECT car.vehicle_id, driver.name
FROM car
LEFT OUTER JOIN driver ON (driver_id) ;
Inner Join
Outer Join (*)
Equijoin
Optimal Join
4. What is another name for a simple join or an inner join?
Nonequijoin
Equijoin (*)
Self Join
Outer Join
5. For which of the following tables will all the values be retrieved even if there is no match in the other?
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);
employees (*)
department
both
Neither. the LEFT OUTER JOIN limits the value to the matching department id's.
6. If you select rows from two tables (employees and departments) using an outer join, what will you get? Use the code below to arrive at your answer:
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);
All employees that do not have a department_id assigned to them
All employees including those that do not have a department_id assigned to them (*)
No employees as the statement will fail
None of the above
7. Which syntax would be used to retrieve all rows in both the EMPLOYEES and DEPARTMENTS tables, even when there is no match?
FULL OUTER JOIN (*)
LEFT OUTER JOIN AND RIGHT OUTER JOIN
FULL INNER JOIN
Use any equijoin syntax
Self Joins and Hierarchical Queries
1. Hierarchical queries can walk both Top-Down and Bottom-Up. True or False?
True (*)
False
2. Hierarchical queries MUST use the LEVEL pseudo column. True or False?
True
False (*)
3. Which of the following database design concepts is implemented with a self join?
Non-Transferability
Recursive Relationship (*)
Supertype
Arc
4. Which of the following database design concepts do you need in your tables to write Hierarchical queries?
Non-Transferability
Recursive Relationship (*)
Supertype
Arc
5. Which SELECT statement implements a self join?
SELECT e.employee_id, m.manager_id
FROM employees e NATURAL JOIN employees m;
SELECT e.employee_id, m.manager_id
FROM employees e, employees m
WHERE m.employee_id = e.manager_id; (*)
SELECT e.employee_id, m.manager_id
FROM employees e, manager m
WHERE e.employee_id = m.manager_id;
SELECT e.employee_id, m.manager_id
FROM employees e, departments m
WHERE e.employee_id = m.manager_id;
6. Which select statement will return the last name and hire data of an employee and his/ her manager for employees that started in the company
before their managers?
SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
FROM employees w , employees m
WHERE w.manager_id = m.employee_id AND w.hire_date < m.hire_date (*)
SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
FROM employees w , employees m
WHERE w.manager_id != m.employee_id AND w.hire_date < m.hire_date
SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
FROM employees w , employees w
WHERE w.manager_id = w.employee_id
AND w.hire_date < w.hire_date
SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
FROM employees w , employees m
WHERE w.manager_id = m.employee_id AND w.hire_date > m.hire_date
-----------------------------------------------------------------------------------------
Cross joins and Natural Joins
1. What happens when you create a Cartesian product?
All rows from one table are joined to all rows of another table (*)
No rows are returned as you entered wrong join-criteria
The table is joined to itself, one column to the next column, exhausting all possibilities
All rows that do not match in the WHERE clause are displayed
2. A NATURAL JOIN is based on:
Columns with the same name and datatype (*)
Columns with the same name
Columns with the same datatype and width
Tables with the same structure
3. The ___________ join is the ANSI-standard syntax used to generate a Cartesian product.
NATURAL
ALL
FULL
CROSS (*)
4. The join column must be included in the select statement when you use the NATURAL JOIN clause. True or False?
True
False (*)
-----------------------------------------------------------------------------------------
Test: Quiz: Join ClausesJoin Clauses
1. The following is a valid SQL statement.
SELECT e.employee_id, e.last_name, d.location_id, department_id
FROM employees e JOIN departments d
USING (department_id) ; True or False?
True (*)
False
2. You can do nonequi-joins with ANSI-Syntax. True or False?
True (*)
False
3. The keywords JOIN _____________ should be used to join tables with the same column names but different datatypes.
NATURAL ON
ON
WHEN
USING (*)
4. The primary advantage of using JOIN ON is:
The join happens automatically based on matching column names and data types
It will display rows that do not meet the join condition
It easily produces a Cartesian product between the tables in the statement
It permits columns that don?t have matching data types to be joined
It permits columns with different names to be joined (*)
5. Table aliases MUST be used with columns referenced in the JOIN USING clause. True or False?
True
False (*)
-----------------------------------------------------------------------------------------
Test: Quiz: Inner versus Outer JoinsInner versus Outer Joins
1. Given the following descriptions of the employees and jobs tables, which of the following scripts will display each employee?s possible minimum and maximum salaries based on their job title?
EMPLOYEES Table:
Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER (6)
FIRST_NAME VARCHAR2 (20)
LAST_NAME NOT NULL VARCHAR2 (25)
EMAIL NOT NULL VARCHAR2 (25)
PHONE_NUMBER VARCHAR2 (20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2 (10)
SALARY NUMBER (8,2)
COMMISSION_PCT NUMBER (2,2)
MANAGER_ID NUMBER (6)
DEPARTMENT_ID NUMBER (4)
JOBS Table:
Name Null? Type
JOB_ID NOT NULL VARCHAR2 (10)
JOB_TITLE NOT NULL VARCHAR2 (35)
MIN_SALARY NUMBER (6)
MAX_SALARY NUMBER (6)
SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
FROM employees e
NATURAL JOIN jobs j
USING (job_id);
SELECT first_name, last_name, job_id, min_salary, max_salary
FROM employees
NATURAL JOIN jobs; (*)
SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
FROM employees e
NATURAL JOIN jobs j;
SELECT first_name, last_name, job_id, min_salary, max_salary
FROM employees e
FULL JOIN jobs j (job_id);
SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
FROM employees e
NATURAL JOIN jobs j ON (e.job_title = j.job_title);
2. EMPLOYEES Table:
Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
DEPARTMENT_ID NUMBER (4)
DEPARTMENTS Table:
Name Null? Type
DEPARTMENT_ID NOT NULL NUMBER 4
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER (6)
A query is needed to display each department and its manager name from the above tables. However, not all departments have a manager but we want
departments returned in all cases. Which of the following SQL: 1999 syntax scripts will accomplish the task?
SELECT d.department_id, e.first_name, e.last_name
FROM employees e
LEFT OUTER JOIN departments d
WHERE (e.department_id = d.department_id);
SELECT d.department_id, e.first_name, e.last_name
FROM employees e
RIGHT OUTER JOIN departments d ON (e.employee_id = d.manager_id); (*)
SELECT d.department_id, e.first_name, e.last_name
FROM employees e
FULL OUTER JOIN departments d ON (e.employee_id = d.manager_id);
SELECT d.department_id, e.first_name, e.last_name
FROM employees e, departments d
WHERE e.employee_id RIGHT OUTER JOIN d.manager_id;
3. The following statement is an example of what kind of join?
SELECT car.vehicle_id, driver.name
FROM car
LEFT OUTER JOIN driver ON (driver_id) ;
Inner Join
Outer Join (*)
Equijoin
Optimal Join
4. What is another name for a simple join or an inner join?
Nonequijoin
Equijoin (*)
Self Join
Outer Join
5. For which of the following tables will all the values be retrieved even if there is no match in the other?
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);
employees (*)
department
both
Neither. the LEFT OUTER JOIN limits the value to the matching department id's.
6. If you select rows from two tables (employees and departments) using an outer join, what will you get? Use the code below to arrive at your answer:
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);
All employees that do not have a department_id assigned to them
All employees including those that do not have a department_id assigned to them (*)
No employees as the statement will fail
None of the above
7. Which syntax would be used to retrieve all rows in both the EMPLOYEES and DEPARTMENTS tables, even when there is no match?
FULL OUTER JOIN (*)
LEFT OUTER JOIN AND RIGHT OUTER JOIN
FULL INNER JOIN
Use any equijoin syntax
-----------------------------------------------------------------------------------------
Test: Quiz: Self Joins and Hierarchical QueriesSelf Joins and Hierarchical Queries
1. Hierarchical queries can walk both Top-Down and Bottom-Up. True or False?
True (*)
False
2. Hierarchical queries MUST use the LEVEL pseudo column. True or False?
True
False (*)
3. Which of the following database design concepts is implemented with a self join?
Non-Transferability
Recursive Relationship (*)
Supertype
Arc
4. Which of the following database design concepts do you need in your tables to write Hierarchical queries?
Non-Transferability
Recursive Relationship (*)
Supertype
Arc
5. Which SELECT statement implements a self join?
SELECT e.employee_id, m.manager_id
FROM employees e NATURAL JOIN employees m;
SELECT e.employee_id, m.manager_id
FROM employees e, employees m
WHERE m.employee_id = e.manager_id; (*)
SELECT e.employee_id, m.manager_id
FROM employees e, manager m
WHERE e.employee_id = m.manager_id;
SELECT e.employee_id, m.manager_id
FROM employees e, departments m
WHERE e.employee_id = m.manager_id;
6. Which select statement will return the last name and hire data of an employee and his/ her manager for employees that started in the company
before their managers?
SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
FROM employees w , employees m
WHERE w.manager_id = m.employee_id AND w.hire_date < m.hire_date (*)
SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
FROM employees w , employees m
WHERE w.manager_id != m.employee_id AND w.hire_date < m.hire_date
SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
FROM employees w , employees w
WHERE w.manager_id = w.employee_id
AND w.hire_date < w.hire_date
SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
FROM employees w , employees m
WHERE w.manager_id = m.employee_id AND w.hire_date > m.hire_date
section 6
Test : Fundaments of subquery probabil -> http://paulierco.ro/wp-content/uploads/2008/03/quizuridelasectiunea1panalasectiunea7/index.html
-------------------------------------------------------------------------------------
Test: Quiz: Subqueries
Subqueries
1.Subqueries can only be placed in the WHERE clause. True or False?
True
False(*)
2. What will the following statement return:
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
Nothing. It is an invalid statement. (*)
A list of last_names and salaries of employees
A list of first_names and salaries of employees in Department 50
A list of last_names and salaries of employees grouped by department_id.
3. Which of the following statements is a true guideline for using
subqueries?
Do not enclose the subquery in parentheses.
Place the subquery on the left side of the comparison condition.
The outer and inner queries can reference than one table. They can get data from different tables. (*)
Only one WHERE clause can be used for a SELECT statement, and if specified, it must be the outer query.
4. What will the following statement return:
SELECT last_name, salary
FROM employees
WHERE salary < (SELECT salary
FROM employees
WHERE employee_id = 103)
A list of last_names and salaries of employees that makes more than employee 103
A list of last_names and salaries of employees that makes less than employee 103 (*)
A list of first_names and salaries of employees making less than employee
Nothing. It is an invalid statement.
----------------------------------------------------------------------------------------------
Test: Quiz: Single-Row Subqueries
Single-Row Subqueries
1. Single row subqueries may not include this operator:
ALL (*)
=
<>
>
2. If the subquery returns no rows will the outer query return any values?
No, because you are not allowed to not return any rows from a subquery
Yes. It will just run and ignore the subquery
No, because the subquery will be treated like a null value. (*)
Yes, Oracle will find the nearest value and rewrite your statement implicitly when you run it
3. In a non-correlated subquery, the outer query always executes prior to the inner query's execution. True or False?
True
False (*)
4. Subqueries are limited to four per SQL transaction. True or False?
True
False (*)
5. The result of this statement will be:
SELECT last_name, job_id, salary, department_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141) AND department_id =
(SELECT department_id
FROM departments
WHERE location_id =1500)
All employees from Location 1500 will be displayed
An error since you can?t get data from two tables in the same subquery
All employees with the department id of 141
Only the employees whose job id matches employee 141 and who work in location 1500 (*)
------------------------------------------------------------------------------------
Test: Quiz: Multiple-Row Subqueries
Multiple-Row Subqueries
1. Multiple-row subqueries must have NOT, IN or ANY in the WHERE clause of the inner query. True or False?
True
False (*)
2. There can be more than one subquery returning information to the outer query. True or False?
True (*)
False
3. The salary column of the f_staffs table contains the following values:
4000
5050
6000
11000
23000
Which of the following statements will return the last_name and first_name of those employees who earn more than 5000.
SELECT last_name, first_name
FROM f_staffs
WHERE salary = (SELECT salary FROM f_staffs WHERE salary > 5000);
SELECT last_name, first_name
FROM f_staffs
WHERE salary = (SELECT salary FROM f_staffs WHERE salary < 5000); <p>
SELECT last_name, first_name
FROM f_staffs
WHERE salary IN (SELECT salary FROM f_staffs WHERE salary > 5000); (*)
SELECT last_name, first_name
FROM f_staffs
WHERE salary IN
(SELECT last_name, first_name FROM f_staffs WHERE salary < 5000);
4. Group functions can be used in subqueries even though they may return many rows. True or False?
True (*)
False
5. The SQL multiple-row subquery extends the capability of the single-row syntax through the use of what three comparison operators?
IN, ANY and EQUAL
IN, ANY and ALL (*)
IN, ANY and EVERY
IN, ALL and EVERY
6. In a subquery the ALL operator compares a value to every value returned by the inner query. True or False?
True (*)
False
7. Group functions, such as HAVING and GROUP BY can be used in multiple-row subqueries. True or False?
True (*)
False
8. When a multiple-row subquery uses the NOT IN (<>ALL) operator, if one of the values returned by the inner query is a null value,
the entire query returns:
A list of Nulls
All rows that were selected by the inner query including the null value(s)
All rows, minus the null value(s), that were selected by the inner query
No rows returned (*)
-----------------------------------------------------------------------------------------------
Test: Quiz: Correlated Subqueries
Correlated Subqueries
1. The WITH-clause is a way of creating extra tables in the database? (True or False)
True
False (*)
2. Table aliases must be used when you are writing correlated subqueries? (True or false)
True (*)
False
3. Correlated Subqueries must work on the same tables in both the inner and outer query? (True or False)
True
False (*)
4. In a correlated subquery the outer and inner query are joined on one or more columns? (True or False)
True (*)
False
section 8
-----------------------------------------------------------------------------------------
Creating Tables
1.Once they are created, external tables are accessed with normal SQL statements? (True or False)
True (*)
False
2.Given this employee table: (employee_id NUMBER(10) NOT NULL,
first_name VARCHAR2(25) NOT NULL,
last_name VARCHAR2(30) NOT NULL,
hire_date DATE DEFAULT sysdate)
What will be the result in the hire_date column following this insert statement:
INSERT INTO employees VALUES (10, 'Natacha', 'Hansen', DEFAULT);
Statement will fail, as you must list the columns into which you are inserting.
Statement will work and the hire_date column will have the value of the date when the statement was run. (*)
The character string SYSDATE.
The column for hire_date will be null.
3.CREATE TABLE bioclass
(hire_date DATE DEFAULT SYSDATE,
first_name varchar2(15),
last_name varchar2(15)); The above CREATE TABLE statement is acceptable, and will create a Table named bioclass that contains a hire_date,
first_name and last_name column. True or False?
True (*)
False
4.When creating a new table, which of the following naming rules apply: (Choose three)
Must begin with a letter (*)
Can have the same name as another object owned by the same user
Must contain ONLY A - Z, a - z, 0 - 9, _ (underscore), $, and # (*)
Must be an Oracle reserved word
Must be between 1 and 30 characters long (*)
5.CREATE TABLE student_table
(id NUMBER(6),
lname VARCHAR(20),
fname VARCHAR(20),
lunch_num NUMBER(4)); Which of the following statements best describes the above SQL statement:
creates a table named student_table with four columns: lname, fname, lunch, num
creates a table named student with four columns: id, lname, fname, lunch_num
creates a table named student_table with four columns: id, lname, fname, lunch_num (*)
creates a table named student_table with four columns: lname, fname, lunch, num
6. Examine this CREATE TABLE statement: CREATE TABLE emp_load
(employee_number CHAR(5),
employee_dob CHAR(20),
employee_last_name CHAR(20),
employee_first_name CHAR(15),
employee_middle_name CHAR(15),
employee_hire_date DATE)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY def_dir1
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS (employee_number CHAR(2),
employee_dob CHAR(20),
employee_last_name CHAR(18),
employee_first_name CHAR(11),
employee_middle_name CHAR(11),
employee_hire_date CHAR(10) date_format DATE mask "mm/dd/yyyy“))
LOCATION ('info.dat'));
What kind of table is created here?
An external table with the data stored in a file outside the database. (*)
A View.
An external table with the data stored in a file inside the database.
None. This is in invalid statement.
7. I have a table named School_Friends in my schema. You want to build a table in your schema named School_Friends.
This is ______________, because ____________________________________.
possible; my schema is separate from yours, and it is okay for us to have like-named tables in our separate schemas. (*)
possible; our data will merge into one table, and we can more easily access our mutual friends information.
impossible; no matter what, there can never be two tables with the same name, even if they are in separate schemas.
impossible; School_Friends is a reserved term in SQL.
8. DCL, which is the acronym for Data Control Language, allows:
the ALTER command to be used.
a Database Administrator the ability to grant privileges to users. (*)
the TRUNCATE command to be used.
the CONTROL TRANSACTION statement can be used.
9. It is possible to create a table by using the CREATE TABLE command in conjunction with a subquery. True or False?
True (*)
False
-----------------------------------------------------------------------------------------
Using Data Types
1. To store large amounts of text you should simply create a series of VARCHAR2 columns in a table. True or False?
True
False (*)
2. Which of the following are valid Oracle datatypes?
DATE, BLOB, LOB, VARCHAR2
DATE, TIMESTAMP WITH LOCAL TIMEZONE, BLOB (*)
TIMESTAMP, LOB, VARCHAR2, NUMBER
SYSDATE, TIMESTAMP, DATE, LOCAL TIMEZONE
3.The BLOB datatype can hold a maximum of 128 Terabytes of data. True or False?
True (*)
4.INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. True or False?
True (*)
False
-----------------------------------------------------------------------------------------
Modify a table
1.To completely get rid of a table, its contents, its structure, AND release the storage space the keyword is:
DROP (*)
DELETE
TRUNCATE
KILL
2. The following code creates a table named student_table with four columns: id, lname, fname, lunch_num CREATE TABLE student_table
(id NUMBER(6),
lname VARCHAR(20),
fname VARCHAR(20),
lunch_num NUMBER(4));
The lunch_num column in the above table has been marked as UNUSED. Which of the following is the best statement you can use if you wish to remove the UNUSED column from the student_table?
DROP column
ALTER TABLE DELETE UNUSED COLUMNS
ALTER TABLE DROP UNUSED COLUMNS (*)
ALTER TABLE DELETE ALL COLUMNS
3. You can use DROP COLUMN to drop all columns in a table, leaving a table structure with no columns. True or False?
True
False (*)
4.Which of the following will correctly change the name of the LOCATIONS table to NEW_LOCATIONS?
ALTER TABLE LOCATIONS RENAME NEW_LOCATIONS
MODIFY TABLE LOCATIONS RENAME NEW_LOCATIONS
RENAME LOCATIONS TO NEW_LOCATIONS (*)
None of the above; you cannot rename a table, you can only CREATE, ALTER and DROP a table.
5.When should you use the SET UNUSED command?
Never, there is no SET UNUSED command
You should use it if you think the column may be needed again later
You should use it when the system is being heavily used (*)
You should only use this command if you want the column to still be visible when you DESCRIBE the table
6. Comments can be added to a table by using the COMMENT ON TABLE statement. The comments being added are enclosed in:
Double quotes " "
Single quotes ' ' (*)
Parentheses ( )
Brackets { }
7. You can use the ALTER TABLE statement to:
Add a new column
Modify an existing column
Drop a column
All of the above (*)
8. When you use ALTER TABLE to add a column, the new column:
Becomes the first column in the table
Becomes the last column in the table (*)
Can be placed by adding a GROUP BY clause
Will not be created because you cannot add a column after the table is created
9. ALTER TABLE table_name RENAME can be used to:
Rename a row.
Rename a column.
Rename a table. (*)
All of the above.
10. The FLASHBACK TABLE to BEFORE DROP can restore only the table structure, but not its data back to before the table was dropped. True or False?
True
False (*)
11. After issuing a SET UNUSED command on a column, another column with the same name can be added using an ALTER TABLE statement. True or False?
True (*)
False
12. The FLASHBACK QUERY statement can restore data back to a point in time before the last COMMIT. True or False?
True
False (*)
13.A column?s data type can always be changed from NUMBER to VARCHAR2 but not from VARCHAR2 to NUMBER, provided the table is empty. True or False?
True
False (*)
14. The data type of a column can never be changed once it has been created. True or False?
True
False (*)
Creating Tables
1.Once they are created, external tables are accessed with normal SQL statements? (True or False)
True (*)
False
2.Given this employee table: (employee_id NUMBER(10) NOT NULL,
first_name VARCHAR2(25) NOT NULL,
last_name VARCHAR2(30) NOT NULL,
hire_date DATE DEFAULT sysdate)
What will be the result in the hire_date column following this insert statement:
INSERT INTO employees VALUES (10, 'Natacha', 'Hansen', DEFAULT);
Statement will fail, as you must list the columns into which you are inserting.
Statement will work and the hire_date column will have the value of the date when the statement was run. (*)
The character string SYSDATE.
The column for hire_date will be null.
3.CREATE TABLE bioclass
(hire_date DATE DEFAULT SYSDATE,
first_name varchar2(15),
last_name varchar2(15)); The above CREATE TABLE statement is acceptable, and will create a Table named bioclass that contains a hire_date,
first_name and last_name column. True or False?
True (*)
False
4.When creating a new table, which of the following naming rules apply: (Choose three)
Must begin with a letter (*)
Can have the same name as another object owned by the same user
Must contain ONLY A - Z, a - z, 0 - 9, _ (underscore), $, and # (*)
Must be an Oracle reserved word
Must be between 1 and 30 characters long (*)
5.CREATE TABLE student_table
(id NUMBER(6),
lname VARCHAR(20),
fname VARCHAR(20),
lunch_num NUMBER(4)); Which of the following statements best describes the above SQL statement:
creates a table named student_table with four columns: lname, fname, lunch, num
creates a table named student with four columns: id, lname, fname, lunch_num
creates a table named student_table with four columns: id, lname, fname, lunch_num (*)
creates a table named student_table with four columns: lname, fname, lunch, num
6. Examine this CREATE TABLE statement: CREATE TABLE emp_load
(employee_number CHAR(5),
employee_dob CHAR(20),
employee_last_name CHAR(20),
employee_first_name CHAR(15),
employee_middle_name CHAR(15),
employee_hire_date DATE)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY def_dir1
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS (employee_number CHAR(2),
employee_dob CHAR(20),
employee_last_name CHAR(18),
employee_first_name CHAR(11),
employee_middle_name CHAR(11),
employee_hire_date CHAR(10) date_format DATE mask "mm/dd/yyyy“))
LOCATION ('info.dat'));
What kind of table is created here?
An external table with the data stored in a file outside the database. (*)
A View.
An external table with the data stored in a file inside the database.
None. This is in invalid statement.
7. I have a table named School_Friends in my schema. You want to build a table in your schema named School_Friends.
This is ______________, because ____________________________________.
possible; my schema is separate from yours, and it is okay for us to have like-named tables in our separate schemas. (*)
possible; our data will merge into one table, and we can more easily access our mutual friends information.
impossible; no matter what, there can never be two tables with the same name, even if they are in separate schemas.
impossible; School_Friends is a reserved term in SQL.
8. DCL, which is the acronym for Data Control Language, allows:
the ALTER command to be used.
a Database Administrator the ability to grant privileges to users. (*)
the TRUNCATE command to be used.
the CONTROL TRANSACTION statement can be used.
9. It is possible to create a table by using the CREATE TABLE command in conjunction with a subquery. True or False?
True (*)
False
-----------------------------------------------------------------------------------------
Using Data Types
1. To store large amounts of text you should simply create a series of VARCHAR2 columns in a table. True or False?
True
False (*)
2. Which of the following are valid Oracle datatypes?
DATE, BLOB, LOB, VARCHAR2
DATE, TIMESTAMP WITH LOCAL TIMEZONE, BLOB (*)
TIMESTAMP, LOB, VARCHAR2, NUMBER
SYSDATE, TIMESTAMP, DATE, LOCAL TIMEZONE
3.The BLOB datatype can hold a maximum of 128 Terabytes of data. True or False?
True (*)
4.INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. True or False?
True (*)
False
-----------------------------------------------------------------------------------------
Modify a table
1.To completely get rid of a table, its contents, its structure, AND release the storage space the keyword is:
DROP (*)
DELETE
TRUNCATE
KILL
2. The following code creates a table named student_table with four columns: id, lname, fname, lunch_num CREATE TABLE student_table
(id NUMBER(6),
lname VARCHAR(20),
fname VARCHAR(20),
lunch_num NUMBER(4));
The lunch_num column in the above table has been marked as UNUSED. Which of the following is the best statement you can use if you wish to remove the UNUSED column from the student_table?
DROP column
ALTER TABLE DELETE UNUSED COLUMNS
ALTER TABLE DROP UNUSED COLUMNS (*)
ALTER TABLE DELETE ALL COLUMNS
3. You can use DROP COLUMN to drop all columns in a table, leaving a table structure with no columns. True or False?
True
False (*)
4.Which of the following will correctly change the name of the LOCATIONS table to NEW_LOCATIONS?
ALTER TABLE LOCATIONS RENAME NEW_LOCATIONS
MODIFY TABLE LOCATIONS RENAME NEW_LOCATIONS
RENAME LOCATIONS TO NEW_LOCATIONS (*)
None of the above; you cannot rename a table, you can only CREATE, ALTER and DROP a table.
5.When should you use the SET UNUSED command?
Never, there is no SET UNUSED command
You should use it if you think the column may be needed again later
You should use it when the system is being heavily used (*)
You should only use this command if you want the column to still be visible when you DESCRIBE the table
6. Comments can be added to a table by using the COMMENT ON TABLE statement. The comments being added are enclosed in:
Double quotes " "
Single quotes ' ' (*)
Parentheses ( )
Brackets { }
7. You can use the ALTER TABLE statement to:
Add a new column
Modify an existing column
Drop a column
All of the above (*)
8. When you use ALTER TABLE to add a column, the new column:
Becomes the first column in the table
Becomes the last column in the table (*)
Can be placed by adding a GROUP BY clause
Will not be created because you cannot add a column after the table is created
9. ALTER TABLE table_name RENAME can be used to:
Rename a row.
Rename a column.
Rename a table. (*)
All of the above.
10. The FLASHBACK TABLE to BEFORE DROP can restore only the table structure, but not its data back to before the table was dropped. True or False?
True
False (*)
11. After issuing a SET UNUSED command on a column, another column with the same name can be added using an ALTER TABLE statement. True or False?
True (*)
False
12. The FLASHBACK QUERY statement can restore data back to a point in time before the last COMMIT. True or False?
True
False (*)
13.A column?s data type can always be changed from NUMBER to VARCHAR2 but not from VARCHAR2 to NUMBER, provided the table is empty. True or False?
True
False (*)
14. The data type of a column can never be changed once it has been created. True or False?
True
False (*)
Abonați-vă la:
Postări (Atom)