Tag: databases

Questions Related to databases

What does the TRUNCATE statement do?
technology databases

  1. Removes the table

  2. Removes all rows from a table

  3. shortens the table to 10 rows

  4. Removes all columns from a table

  5. Removes foreign keys from a table

Show answer
Correct Option: B

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

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

  1. A view can be created as read only

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

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

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

  5. A view must have aliases defined for the column names in the SELECT statement

Show answer
Correct Option: A,B
Explanation:

Explanation: To answer this question, we need to understand the concept of views in the context of databases.

A. A view can be created as read-only - This statement is true. A view can be created with the SELECT statement that restricts the columns and rows that are returned. This can be used to ensure a user only has read access to certain data.

B. A view can be created as a join on two or more tables - This statement is true. A view can be created as a SELECT statement that combines data from two or more tables. This can be used to simplify queries by combining data that is frequently used together.

C. A view cannot have an ORDER BY clause in the SELECT statement - This statement is false. A view can have an ORDER BY clause in the SELECT statement to sort the data returned by the view.

D. A view cannot be created with a GROUP BY clause in the SELECT statement - This statement is false. A view can be created with a GROUP BY clause in the SELECT statement to group the data returned by the view.

E. A view must have aliases defined for the column names in the SELECT statement - This statement is false. A view does not require aliases for the column names in the SELECT statement, although it can be useful to provide more meaningful names for the columns.

Therefore, options A and B are both true statements, making the correct answer B.

Management has asked you to calculate the value 12*salary*commission_pct for all the employees in the EMP table. The EMP table contains these columns: LAST NAME VARCHAR2(35) NOT NULL SALARY NUMBER(9,2) NOT NULL COMMISSION_PCT NUMBER(4,2) Which statement ensures that a value is displayed in the calculated column for all employees?
technology databases

  1. SELECT last_name, 12*salary*commission_pct FROM emp;

  2. SELECT last_name, 12*salary* (commission_pct,0) FROM emp;

  3. SELECT last_name, 12*salary*(nvl(commission_pct,0)) FROM emp

  4. SELECT last_name, 12*salary*(decode(commission_pct,0)) FROM emp;

Show answer
Correct Option: C

Examine the description of the STUDENTS table: STD_ID NUMBER(4) COURSE_ID VARCHAR2(10) START_DATE DATE END_DATE DATE Which two aggregate functions are valid on the START_DATE column? (Choose two.)

technology databases

  1. SUM(start_date)

  2. AVG(start_date)

  3. COUNT(start_date)

  4. AVG(start_date, end_date)

  5. MIN(start_date)

  6. MAXIMUM(start_date)

Show answer
Correct Option: C,E

AI Explanation

To determine which two aggregate functions are valid on the START_DATE column, let's go through each option:

A. SUM(start_date) - This option is incorrect because the SUM function is used to calculate the sum of numeric values, not dates.

B. AVG(start_date) - This option is incorrect because the AVG function is used to calculate the average of numeric values, not dates.

C. COUNT(start_date) - This option is correct because the COUNT function can be used to count the number of occurrences of a column, including dates.

D. AVG(start_date, end_date) - This option is incorrect because the AVG function can only be used on a single column, not multiple columns.

E. MIN(start_date) - This option is correct because the MIN function can be used to find the minimum value in a column, including dates.

F. MAXIMUM(start_date) - This option is incorrect because there is no MAXIMUM function in SQL. The correct function is MAX, which can be used to find the maximum value in a column, including dates.

Therefore, the two valid aggregate functions on the START_DATE column are:

C. COUNT(start_date) E. MIN(start_date)

The correct answer is C and E.

Evaluate the SQL statement: SELECT ROUND (TRUNC (MOD (1600, 10),-1), 2) FROM dual; What will be displayed?
technology databases

  1. 0

  2. 1

  3. 0.00

  4. An error statement

Show answer
Correct Option: A

Examine the description of the MARKS table: STD_ID NUMBER(4) STUDENT_NAME VARCHAR2(30) SUBJ1 NUMBER(3) SUBJ2 NUMBER(3) SUBJ1 and SUBJ2 indicate the marks obtained by a student in two subjects. Examine this SELECT statement based on the MARKS table: SELECT subj1+subj2 total_marks, std_id FROM marks WHERE subj1 > AVG(subj1) AND subj2 > AVG(subj2) ORDER BY total_marks; What is the result of the SELECT statement?

technology databases

  1. The statement executes successfully and returns the student ID and sum of all marks for each student who obtained more than the average mark in each subject.

  2. The statement returns an error at the SELECT clause.

  3. The statement returns an error at the WHERE clause.

  4. The statement returns an error at the ORDER BY clause.

Show answer
Correct Option: C

AI Explanation

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

Option A) The statement executes successfully and returns the student ID and sum of all marks for each student who obtained more than the average mark in each subject. This option is incorrect because the SELECT statement is calculating the total marks by adding subj1 and subj2, not the sum of all marks. Additionally, the WHERE clause is checking if each subject's mark is greater than the average mark for that subject, not the sum of all marks.

Option B) The statement returns an error at the SELECT clause. This option is incorrect because there is no error in the SELECT clause. The SELECT clause is calculating the total marks correctly by adding subj1 and subj2.

Option C) The statement returns an error at the WHERE clause. This option is correct. The WHERE clause is using the AVG function to calculate the average mark for each subject. However, you cannot use aggregate functions like AVG in the WHERE clause directly. To use aggregate functions in the WHERE clause, you need to use a subquery or a HAVING clause.

Option D) The statement returns an error at the ORDER BY clause. This option is incorrect because there is no error in the ORDER BY clause. The ORDER BY clause is ordering the result set based on the total_marks column.

The correct answer is C. The statement returns an error at the WHERE clause because you cannot use aggregate functions like AVG in the WHERE clause directly.

Which three SELECT statements display 2000 in the format "$2,000.00"? (Choose three.)
technology databases

  1. SELECT TO_CHAR(2000, '$#,###.##') FROM dual;

  2. SELECT TO_CHAR(2000, '$0,000.00') FROM dual;

  3. SELECT TO_CHAR(2000, '$9,999.00') FROM dual;

  4. SELECT TO_CHAR(2000, '$9,999.99') FROM dual;

  5. SELECT TO_CHAR(2000, '$2,000.00') FROM dual;

  6. SELECT TO_CHAR(2000, '$N,NNN.NN') FROM dual;

Show answer
Correct Option: B,C,D

What is necessary for your query on an existing view to execute successfully?
technology databases

  1. The underlying tables must have data.

  2. You need SELECT privileges on the view.

  3. The underlying tables must be in the same schema.

  4. You need SELECT privileges only on the underlying tables.

Show answer
Correct Option: B

You define a multiple-row subquery in the WHERE clause of an SQL query with a comparison operator "=". What happens when the main query is executed?
technology databases

  1. SELECT ENAME FROM EMP WHERE SYSDATE-HIRE_DATE > 5;

  2. SELECT ENAME FROM EMP WHERE HIRE_DATE-SYSDATE > 5;

  3. SELECT ENAME FROM EMP WHERE (SYSDATE-HIRE_DATE)/365 > 5;

  4. SELECT ENAME FROM EMP WHERE (SYSDATE-HIRE_DATE)* 365 > 5;

Show answer
Correct Option: C