- Which two statements about views are true? (Choose two.)
A. A view can be created as read only.
B. A view can be created as a join on two or more tables.
C. A view cannot have an ORDER BY clause in the SELECT statement.
D. A view cannot be created with a GROUP BY clause in the SELECT statement.
E. A view must have aliases defined for the column names in the SELECT statement.
Answer: A, B
Explanation: A view can be created as read only object.
However, it is possible to change data in the underlying table(s)
with some restrictions.A view also can be created as a join on two or more tables.
This type of view is called complex view. Complex views provide complicated data models where many base tables are drawn together into one virtual table.
Incorrect Answers
C: Query operations containing ORDER BY clause are also permitted, so long as the ORDER BY clause appears outside the parentheses.
The following is an example of what I mean: CREATE VIEW my_view AS (SELECT*FROM emp) ORDER BYempno.
D: A view can be created with a GROUP BY clause in the SELECT statement.
E: It is not required to have aliases defined for the column names in the SELECT statement.
2)
Which syntax turns an existing constraint on?
A. ALTER TABLE table_name ENABLE constraint_name;
B. ALTER TABLE table_name STATUS = ENABLE CONSTRAINT constraint_name;
C. ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
D. ALTER TABLE table_name STATUS ENABLE CONSTRAINT constraint_name;
E. ALTER TABLE table_name TURN ON CONSTRAINT constraint_name;
F. ALTER TABLE table_name TURN ON CONSTRAINT constraint_name;
Answer: C Explanation: ALTER TABLE statement with ENABLE CONSTRAINT keywords is correct answer to enable an existing constraint.
Incorrect Answers A: This statement is missing CONSTRAINT keyword.
B: “STATUS =” is incorrect syntax to enable constraint for the table.
D: There is no STATUS keyword in the command to enable constraint.
E: There is no TURN ON keywords in the command to enable constraint.
F: There is no TURN ON keywords in the command to enable constraint.
The table is currently empty. Which statement accomplishes this task?
A. ALTER TABLE students
ADD PRIMARY KEY student_id;
B. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);
C. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;
D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
E. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
4)1 SELECT a.emp_name, a.sal, a.dept_id, b.maxsal 2 FROM employees a, 3 (SELECT
dept_id, MAX(sal) maxsal 4.
FROM employees 5 GROUP BY dept_id) b 6
WHERE a.dept_id = b.dept_id 7
AND a.sal < b.maxsal;
What is the result of the statement?
A. The statement produces an error at line 1.
B. The statement produces an error at line 3.
C. The statement produces an error at line 6.
D. The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all departments
that pay less salary then the maximum salary paid in the company.
E. The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees
who earn less than the maximum salary in their department.
Answer: E Explanation: The statement returns the employee name, salary, department ID, and maximum salary earned in the department
of the employee for all employees who earn less than the maximum salary in their department.
This query is example of an inline view which is the sub-query in the FROM clause of the main query.
The sub-query can be a SELECT statement that utilizes joins, the GROUP BY clause, or the ORDER BY clause.
Incorrect Answers
A: The statement does not produce an error at line 1.
B: The statement does not produce an error at line 3.
C: The statement does not produce an error at line 6.
D: The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all EMPLOYEES, NOT DEPARTMENTS,
who earn less than the maximum salary in their department
5)Which three are DATETIME data types that can be used when specifying column definitions? (Choose three.)
A. TIMESTAMP B. INTERVAL MONTH TO DAY C. INTERVAL DAY TO SECOND D. INTERVAL YEAR TO MONTH E. TIMESTAMP WITH DATABASE TIMEZONE
Answer: A, C, D Explanation: TIMESTAMP, INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH can be used to specify column definition.
Incorrect Answers
B: The INTERVAL MONTH TO DAY data type cannot be used when specifying column definitions there are only INTERVAL DAY TO SECOND
and INTERVAL YEAR TO MONTH data types.
E: The TIMESTAMP WITH DATABASE TIMEZONE data type cannot be used when specifying column definitions, because there are only TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE data types
