Tag: databases

Questions Related to databases

Examine the structure of the EMPLOYEES and DEPARTMENTS tables: Evaluate this SQL statement: SELECT employee_id, e.department_id, department_name, salary FROM employees e, departments d WHERE e.department_id = d.department_id; Which SQL statement is equivalent to the above SQL statement?

technology databases

  1. SELECT employee_id, department_id, department_name, Salary FROM employees WHERE department_id IN (SELECT department_id FROM departments);

  2. SELECT employee_id, department_id, department_name, salary FROM employees NATURAL JOIN departments;

  3. SELECT employee_id, d.department_id, department_name, salary FROM employees e JOIN departments d ON e.department_id = d.department_id;

  4. SELECT employee_id, department_id, department_name, salary FROM employees JOIN departments USING (e.department_id, d.department_id);

Show answer
Correct Option: C

In which scenario would an index be most useful?
technology databases

  1. The indexed column is declared as NOT NULL.

  2. The indexed columns are used in the FROM clause.

  3. The indexed columns are part of an expression.

  4. The indexed column contains a wide range of values.

Show answer
Correct Option: D

When should you create a role? (Choose two.)

technology databases

  1. To simplify the process of creating new users using the CREATE USER xxx IDENTIFIED by yyy statement

  2. to grant a group of related privileges to a user

  3. when the number of people using the database is very high

  4. to simplify the process of granting and revoking privileges

  5. to simplify profile maintenance for a user who is constantly traveling

Show answer
Correct Option: B,D

Which two statements about sequences are true? (Choose two.)
technology databases

  1. You use a NEXTVAL pseudo column to look at the next possible value that would be generated from a sequence, without actually retrieving the value.

  2. You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence.

  3. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value from the sequence.

  4. You use a CURRVAL pseudo column to generate a value from a sequence that would be used for a specified database column.

  5. If a sequence starting from a value 100 and incremented by 1 is used by more than one application, then all of these applications could have a value of 105 assigned to their column whose value is being generated by the sequence.

  6. You use a REUSE clause when creating a sequence to restart the sequence once it generates the maximum value defined for the sequence.

Show answer
Correct Option: A,B

Which operator can be used with a multiple-row subquery?
technology databases

  1. =

  2. LIKE

  3. BETWEEN

  4. NOT IN

  5. IS

  6. <>

Show answer
Correct Option: D

You created a view called EMP_DEPT_VU that contains three columns from the EMPLOYEES and DEPARTMENTS tables: EMPLOYEE_ID, EMPLOYEE_NAME AND DEPARTMENT_NAME. The DEPARTMENT_ID column of the EMPLOYEES table is the foreign key to the primary key DEPARTMENT_ID column of the DEPARTMENTS table. You want to modify the view by adding a fourth column, MANAGER_ID of NUMBER data type from the EMPLOYEES table. How can you accomplish this task?
technology databases

  1. ALTER VIEW emp_dept_vu (ADD manager_id NUMBER);

  2. MODIFY VIEW emp_dept_vu (ADD manager_id NUMBER);

  3. ALTER VIEW emp_dept_vu AS SELECT employee_id,employee_name, department_name,manager_id FROM employees e,departments d WHERE e.department_id = d.department_id;

  4. MODIFY VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;

  5. CREATE OR REPLACE VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id=d.department_id;

Show answer
Correct Option: E

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: EMPLOYEES EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE NEW EMPLOYEES EMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2(60) Which UPDATE statement is valid?

technology databases

  1. UPDATE new_employees SET name = (SELECT last_name|| first_ name FROM employees WHERE employee_id =180) WHERE employee_id =180;

  2. UPDATE new_employees SET name = (SELECT last_name ||first_name FROM employees ) WHERE employee_ id =180;

  3. UPDATE new_employees SET name = (SELECT last_name || first_name FROM employees WHERE employee_id =180) WHERE employee_id =(SELECT employee_id FROM new employees);

  4. UPDATE new_employees SET name = (SELECT last name || first_name FROM employees WHERE employee_id = (SELECT employee_id FROM new_employees)) WHERE employee_id =180;

Show answer
Correct Option: A

Which substitution variable would you use if you want to reuse the variable value without prompting the user each time?
technology databases

  1. "&"

  2. ACCEPT

  3. PROMPT

  4. "&&"

Show answer
Correct Option: D

Click the Exhibit button and examine the data in the EMPLOYEES table. Which three sub queries work? (Choose three.)

technology databases

  1. SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id);

  2. SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);

  3. SELECT distinct department_id FROM employees WHERE salary > ANY(SELECT AVG(salary) FROM employees GROUP BY department_id);

  4. SELECT department_id FROM employees WHERE salary > ALL(SELECT AVG(salary) FROM employees GROUP BY department_id);

  5. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));

Show answer
Correct Option: C,D,E

Which SQL statement retrieves the order ID, customer ID, and order total for the orders that are placed on the same day that Martin placed his orders?

technology databases

  1. SELECT ord_id, cust_id, ord_total FROM orders, customers WHERE cust_name='Martin' AND ord_date IN ('18-JUL-2000','21-JUL-2000');

  2. SELECT ord_id, cust_id, ord_total FROM orders WHERE ord_date IN (SELECT ord_date FROM orders WHERE cust_id = (SELECT cust_id FROM customers WHERE cust_name = 'Martin'));

  3. SELECT ord_id, cust_id, ord_total FROM orders WHERE ord_date IN (SELECT ord_date FROM orders, customers WHERE cust_name = 'Martin');

  4. SELECT ord_ id, cust_id, ord_total FROM orders WHERE cust_id IN (SELECT cust_id FROM customers WHERE cust name = 'Martin');

Show answer
Correct Option: B
Explanation:

To solve this question, the user needs to know SQL syntax and how to retrieve data from a database table. The user must select the appropriate columns from the orders table where the order date is the same as the order date for Martin's orders.

Option A: This option is incorrect because it joins the orders and customers tables but does not specify a relationship between them. Also, it selects orders on two specific dates (18-JUL-2000 and 21-JUL-2000) which may not be the dates Martin placed his orders.

Option B: This option is correct. It selects the order ID, customer ID, and order total from the orders table where the order date is the same as the order date for Martin's orders. It uses subqueries to find the order date and customer ID for Martin and then selects orders with the same order date.

Option C: This option joins the orders and customers tables but does not specify a relationship between them, so it is incorrect. Also, it selects orders on the same dates as Martin's orders, which may not be the correct dates.

Option D: This option selects orders where the customer ID is the same as Martin's customer ID, but it does not specify a relationship between the orders and customers tables. Therefore, it may not retrieve the correct orders.

The Answer is: B. SELECT ord_id, cust_id, ord_total FROM orders WHERE ord_date IN (SELECT ord_date FROM orders WHERE cust_id = (SELECT cust_id FROM customers WHERE cust_name = 'Martin'));