• databases Online Quiz - 194
Back
0

databases Online Quiz - 194

Description: databases Online Quiz - 194
Number of Questions: 20
Created by:
Tags: databases
Start the Quiz
Attempted 0/20 Correct 0 Score 0
‹
›

In NPS, SPU means Snippet processing Unit

technology databases

  1. True

  2. False

Show answer
Correct Option: A

The database administrator of your company created a public synonum called HR for the HUMAN_RESOURCES table of the GENERAL schema, because many users frequently use this table. As a user of the database, you created a table called HR in your schema. What happens when you execute this query?

technology databases

  1. You obtain the results retrieved from the public synonym HR created by the DBA.

  2. You obtain the results retrieved from the HR table that belongs to you schema.

  3. You get an error message because you cannot retrieve from a table that has the same name as a public synonym.

  4. You obtain the results retrieved from both the publich synonym HR and the HR table that belongs to your schema, as a Cartesian product.

Show answer
Correct Option: B

Which two statements about views are true?

technology databases

  1. A view can be created as read only.

  2. A view can be created as a join of two or more tables.

  3. A view can not have an ORDER BY clause in the SELECT statement.

  4. A view can not be created with a GROUP BY clause in the SELECT statement.

Show answer
Correct Option: A,B

Which three statements about SubQueries are true?

technology databases

  1. A main query can have mroe than one subquery.

  2. A subquery can have more than one main query.

  3. The sub query and main query must retrieve data from the same table.

  4. The subquery and main query can retrieve data from different tables.

  5. Only one column or expression can be compared between the subquery and main query.

Show answer
Correct Option: A,D,E

For which action can you use the TO_DATE function?

technology databases

  1. Convert any date literal to a date.

  2. Convert any numeric literal to a date.

  3. Convert any character literal to a date.

  4. Convert any date literal to a character literal

  5. Format '10-JAN-99' to 'January 10 1999'.

Show answer
Correct Option: C

What is true about sequences?

technology databases

  1. The start value of the sequence is always 1.

  2. The sequence always increments by 1.

  3. The minimum value of an ascending sequence defaults to 1.

  4. The maximum value of descending sequence defaults to 1.

Show answer
Correct Option: C

SCOTT user has grants on HR schema objects. You are connected to SCOTT schema and issued following command:Create synonym emp for hr.employees;What happens when you issue this statement?

technology databases

  1. An error is generated.

  2. A public synonym is created for employees table.

  3. You create an alternative name for the employees table of HR schema in HR schema

  4. You create an alternative name for the employees table of HR schema in SCOTT schema

Show answer
Correct Option: D

AI Explanation

To answer this question, you need to understand the concept of synonyms in Oracle databases.

A synonym is an alternative name for a database object, such as a table, view, sequence, or procedure. It allows you to access the object using a different name, providing a level of abstraction and simplifying the way you refer to the object.

In this scenario, the statement "CREATE SYNONYM emp FOR hr.employees;" is issued while connected to the SCOTT schema. Let's go through each option to understand why it is correct or incorrect:

Option A) An error is generated - This option is incorrect. The statement is syntactically correct, so it will not generate an error.

Option B) A public synonym is created for the employees table - This option is incorrect. The statement does not specify the "PUBLIC" keyword, so it creates a private synonym, not a public synonym.

Option C) You create an alternative name for the employees table of HR schema in HR schema - This option is incorrect. The synonym is created in the SCOTT schema, not the HR schema.

Option D) You create an alternative name for the employees table of HR schema in SCOTT schema - This option is correct. The statement creates a synonym named "emp" in the SCOTT schema, which refers to the "employees" table in the HR schema. This allows you to refer to the "employees" table using the "emp" synonym in the SCOTT schema.

Therefore, the correct answer is D. You create an alternative name for the employees table of HR schema in the SCOTT schema.

Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) Which three statements inserts a row into the table? (Choose three)

technology databases

  1. INSERT INTO employees VALUES (NULL, 'JOHN','Smith');

  2. . INSERT INTO employees( first_name, last_name) VALUES ('JOHN','Smith');

  3. INSERT INTO employees VALUES ('1000','JOHN','NULL');

  4. INSERT INTO employees(first_name,last_name, employee_id) VALUES ('1000, 'john','Smith');

  5. . INSERT INTO employees (employee_id) VALUES (1000);

  6. INSERT INTO employees (employee_id, first_name, last_name) VALUES ( 1000, 'john',");

Show answer
Correct Option: C,E,F

You need to give the MANAGER role the ability to select from, insert into, and modify existing rows in the STUDENT_GRADES table. Anyone given this MANAGER role should be able to pass those privileges on to others. Which statement accomplishes this

technology databases

  1. GRANT select, insert, update ON student_grades TO manager

  2. GRANT select, insert, update ON student_grades TO ROLE manager

  3. GRANT select, insert, modify ON student_grades TO manager WITH GRANT OPTION;

  4. GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION;

Show answer
Correct Option: D

AI Explanation

To answer this question, you need to understand how to grant privileges in SQL. Let's go through each option to understand why it is correct or incorrect:

Option A) GRANT select, insert, update ON student_grades TO manager - This option grants the select, insert, and update privileges on the STUDENT_GRADES table to the manager role. However, it does not include the ability to pass on these privileges to others, which is a requirement in the question.

Option B) GRANT select, insert, update ON student_grades TO ROLE manager - This option grants the select, insert, and update privileges on the STUDENT_GRADES table to the ROLE manager. However, it also does not include the ability to pass on these privileges to others.

Option C) GRANT select, insert, modify ON student_grades TO manager WITH GRANT OPTION - This option includes the correct privileges (select, insert, and update) for the MANAGER role on the STUDENT_GRADES table. However, the correct keyword to grant update privileges in SQL is "update," not "modify." Therefore, this option is incorrect.

Option D) GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION - This option grants the select, insert, and update privileges on the STUDENT_GRADES table to the MANAGER role. Additionally, it includes the WITH GRANT OPTION clause, which allows the MANAGER role to pass on these privileges to others. Therefore, this option is correct.

The correct answer is Option D. This option is correct because it grants the necessary privileges to the MANAGER role and includes the ability to pass on these privileges to others.

Which are true about aggregate functions? (Choose all that apply)

technology databases

  1. You can use aggregate functions in any caluse of the SELECT statement.

  2. You can user aggregate funcitons only in column list of select clause and where clause of select statement.

  3. You can mix single row functions with aggregate functions in the column list of a SELECT statement by grouping on single row columns.

  4. You can pass column names, expressions, constants, or functions as parameter to an aggregate function.

Show answer
Correct Option: A,D

AI Explanation

To answer this question, let's go through each option to understand why it is correct or incorrect:

Option A) You can use aggregate functions in any clause of the SELECT statement. - This option is correct. Aggregate functions can be used in various clauses of the SELECT statement, such as the SELECT clause, WHERE clause, HAVING clause, and ORDER BY clause.

Option B) You can use aggregate functions only in the column list of the SELECT clause and WHERE clause of the SELECT statement. - This option is incorrect. Aggregate functions can be used in multiple clauses of the SELECT statement, not just the column list and WHERE clause.

Option C) You can mix single row functions with aggregate functions in the column list of a SELECT statement by grouping on single row columns. - This option is incorrect. Single row functions and aggregate functions cannot be mixed in the column list without proper grouping. If you want to use both, you need to group the single row columns appropriately.

Option D) You can pass column names, expressions, constants, or functions as parameters to an aggregate function. - This option is correct. Aggregate functions can accept various types of parameters, including column names, expressions, constants, and functions.

The correct answers are A and D. These options are correct because aggregate functions can be used in any clause of the SELECT statement and can accept different types of parameters.

Therefore, the correct answer is A and D.

Which one is a system privilege?

technology databases

  1. SELECT

  2. DELETE

  3. EXECUTE

  4. ALTER TABLE

  5. CREATE TABLE

Show answer
Correct Option: E

The database administrator of your company created a public synonym called HR for the HUMAN_RESOURCES table of the GENERAL schema, because many users frequently use this table. As a user of the database, you created a table called HR in your schema. What happens when you execute this query? SELECT * FROM HR;

technology databases

  1. You obtain the results retrieved from the public synonym HR created by the database administrator.

  2. You obtain the results retrieved from the HR table that belongs to your schema

  3. You get an error message because you cannot retrieve from a table that has the same name as a public synonym

  4. You obtain the results retrieved from both the public synonym HR and the HR table that belongs to your schema, as a Cartesian product.

Show answer
Correct Option: B

Evaluate the SQL statement: 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. asl < b. maxsal; What is the result of the statement?

technology databases

  1. The statement produces an error at line 1.

  2. The statement produces an error at line 3

  3. The statement produces an error at line 6.

  4. 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

  5. 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.

Show answer
Correct Option: E
Explanation:

To evaluate the SQL statement, let's go through each line and understand its purpose:

  1. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal: This line selects the employee name, salary, department ID from the "employees" table, and also selects the maximum salary for each department from the subquery aliased as "b".

  2. FROM employees a: This line specifies the "employees" table and aliases it as "a".

  3. (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b: This line is a subquery that selects the department ID and maximum salary for each department from the "employees" table. It is aliased as "b".

  4. WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal: This line applies a condition to join the main query with the subquery. It matches the department IDs and ensures that the salary of an employee in department "a" is less than the maximum salary in that department as obtained from the subquery "b".

Now, let's go through the options:

A. The statement produces an error at line 1: This option is incorrect because there is no apparent error in line 1.

B. The statement produces an error at line 3: This option is incorrect because there is no apparent error in line 3.

C. The statement produces an error at line 6: This option is incorrect because there is no line 6 in the SQL statement.

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 than the maximum salary paid in the company: This option is incorrect because the statement does not retrieve the maximum salary paid in the company. It only retrieves the maximum salary for each department.

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: This option is correct. The statement retrieves the employee name, salary, department ID, and the maximum salary earned in the department for all employees who earn less than the maximum salary in their department.

Therefore, the correct answer is option E.

The EMP table contains these columns: LAST NAME VARCHAR2(25) SALARY NUMBER(6,2) DEPARTMENT_ID NUMBER(6) You need to display the employees who have not been assigned to any department. You write the SELECT statement: SELECT LAST_NAME, SALARY, DEPARTMENT_ID FROM EMP WHERE DEPARMENT_ID = NULL; What is true about this SQL statement?

technology databases

  1. The SQL statement displays the desired results

  2. The column in the WHERE clause should be changed to display the desired results

  3. The operator in the WHERE clause should be changed to display the desired results

  4. The WHERE clause should be changed to use an outer join to display the desired results

Show answer
Correct Option: C

AI Explanation

To answer this question, let's analyze the given SQL statement:

SELECT LAST_NAME, SALARY, DEPARTMENT_ID
FROM EMP
WHERE DEPARMENT_ID = NULL;

The SQL statement is attempting to display the employees who have not been assigned to any department. However, the statement contains an error in the WHERE clause.

The correct operator to compare a column to a NULL value is the "IS NULL" operator, not the "=" operator.

Therefore, the correct answer is:

C. The operator in the WHERE clause should be changed to display the desired results

For which two constraints does the Oracle Server implicitly create a unique index? (Choose two.)

technology databases

  1. NOT NULL

  2. PRIMARY KEY

  3. FOREIGN KEY

  4. CHECK

  5. UNIQUE

Show answer
Correct Option: B,E

Which two statements about subqueries are true? (Choose two.)

technology databases

  1. A single row subquery can retrieve data from only one table

  2. A SQL query statement cannot display data from table B that is referred to in its subquery, unless table B is included in the main query's FROM clause.

  3. A SQL query statement can display data from table B that is referred to in its subquery, without including table B in its own FROM clause.

  4. A single row subquery can retrieve data from more than one table

  5. A single row subquery cannot be used in a condition where the LIKE operator is used for comparison

  6. A multiple-row subquery cannot be used in a condition where the LIKE operator is used for comparison.

Show answer
Correct Option: B,D

CREATE TABLE dept(deptno NUMBER(2),dname VARCNAR2(14),1oc VARCNAR2 (13));ROLLBACK;DESCRIBE DEPTWhat is true about the set?

technology databases

  1. The DESCRIBE DEPT statement displays the structure of the DEPT table

  2. The ROLLBACK statement frees the storage space occupies by the DEPT table

  3. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist

  4. The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.

Show answer
Correct Option: A

You added a PHONE_NUMBER column of NUMBER data type to an existing EMPLOYEES table. The EMPLOYEES table already contains records of 100 employees. Now, you want to enter the phone numbers of each of the 100 employees into the table. Some of the employees may not have a phone number available. Which data manipulation operation do you perform?

technology databases

  1. MERGE

  2. INSERT

  3. UPDATE

  4. ADD

  5. ENTER

Show answer
Correct Option: C

In which case would you use a FULL OUTER JOIN?

technology databases

  1. Both tables have NULL values

  2. You want all unmatched data from one table

  3. You want all matched data from both tables

  4. You want all unmatched data from both tables

  5. One of the tables has more data than the other

  6. You want all matched and unmatched data from only one table

Show answer
Correct Option: D

AI Explanation

To answer this question, you need to understand the purpose of a FULL OUTER JOIN.

A FULL OUTER JOIN returns all records from both tables, including matched and unmatched data. It combines the results of both LEFT JOIN and RIGHT JOIN.

Let's go through each option to understand why it is correct or incorrect:

Option A) Both tables have NULL values - This option is not a specific condition for using a FULL OUTER JOIN. A FULL OUTER JOIN can be used even if both tables do not have NULL values.

Option B) You want all unmatched data from one table - This option is not a specific condition for using a FULL OUTER JOIN. A FULL OUTER JOIN returns all unmatched data from both tables.

Option C) You want all matched data from both tables - This option is not correct. A FULL OUTER JOIN returns all matched and unmatched data from both tables.

Option D) You want all unmatched data from both tables - This option is correct. A FULL OUTER JOIN returns all unmatched data from both tables.

Option E) One of the tables has more data than the other - This option is not a specific condition for using a FULL OUTER JOIN. A FULL OUTER JOIN can be used regardless of the size of the tables.

Option F) You want all matched and unmatched data from only one table - This option is not correct. A FULL OUTER JOIN returns all matched and unmatched data from both tables.

The correct answer is option D. This option is correct because a FULL OUTER JOIN is used when you want to retrieve all unmatched data from both tables.

Which type of record is the fastest to access?

technology databases

  1. Index Sequential

  2. Via Set

  3. Calc

  4. Direct

Show answer
Correct Option: D
- Hide questions
ADVERTISEMENT

Find more quizzes from top tags

general knowledge
3615 Quizzes
119858 Questions
 technology
307 Quizzes
36705 Questions
 sports
963 Quizzes
19148 Questions
 history
1187 Quizzes
13475 Questions
 physics
598 Quizzes
13441 Questions
 biology
1160 Quizzes
12174 Questions
 science & technology
551 Quizzes
11015 Questions
 chemistry
490 Quizzes
9892 Questions
 maths
518 Quizzes
9324 Questions
 softskills
0 Quizzes
7131 Questions