SQL Database Quiz Questions

1. Operator John needs to search for text data in a column, but he only remembers part of the string. Which of the following SQL operations allows the use of wildcard comparisons (choose one)?

BETWEEN

IN

LIKE

EXISTS

The LIKE operator allows the use of wildcard comparisons in SQL. With the LIKE operator, John can search for text data in a column by using wildcard characters such as % (percent sign) to represent any number of characters or _ (underscore) to represent a single character. This allows John to search for data even if he only remembers part of the string.

Explanation

The LIKE operator allows the use of wildcard comparisons in SQL. With the LIKE operator, John can search for text data in a column by using wildcard characters such as % (percent sign) to represent any number of characters or _ (underscore) to represent a single character. This allows John to search for data even if he only remembers part of the string.

2. Which SQL statement is used to return only different values?

SELECT UNIQUE

SELECT DIFFERENT

SELECT DISTINCT

None of the above

The SQL statement "SELECT DISTINCT" is used to return only different values. It eliminates duplicate rows from the result set and only displays unique values. This is useful when you want to retrieve distinct values from a column in a table.

Explanation

The SQL statement "SELECT DISTINCT" is used to return only different values. It eliminates duplicate rows from the result set and only displays unique values. This is useful when you want to retrieve distinct values from a column in a table.

3. The name of the only column in the DUAL table is

X

C

DUMMY

None of these

The correct answer is DUMMY. The DUAL table is a special one-row, one-column table in Oracle database. It is often used for selecting a constant value or performing calculations. The only column in the DUAL table is named DUMMY.

Explanation

The correct answer is DUMMY. The DUAL table is a special one-row, one-column table in Oracle database. It is often used for selecting a constant value or performing calculations. The only column in the DUAL table is named DUMMY.

4. With SQL, how do you select all the records from a table named "Persons" where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?

SELECT * FROM Persons WHERE LastName>'Hansen' AND LastName

SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'

SELECT LastName>'Hansen' AND LastName

The correct answer is "SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'". This query will select all the records from the table "Persons" where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen".

Explanation

The correct answer is "SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'". This query will select all the records from the table "Persons" where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen".

5. What will be the output select INSTR('HELLOWORLD','W') from dual; ?

5

4

6

0

The INSTR function in SQL is used to find the position of a substring within a string. In this case, the substring is 'W' and the string is 'HELLOWORLD'. The function will return the position of the first occurrence of the substring within the string. Since 'W' is the sixth character in 'HELLOWORLD', the output will be 6.

Explanation

The INSTR function in SQL is used to find the position of a substring within a string. In this case, the substring is 'W' and the string is 'HELLOWORLD'. The function will return the position of the first occurrence of the substring within the string. Since 'W' is the sixth character in 'HELLOWORLD', the output will be 6.

6. The following SQL statement is illegal because: (choose one) SELECT deptno, AVG(sal) FROM emp WHERE AVG(sal)> 2000 GROUP BY deptno;

It requires data from more than one table, yet only one table is listed.

Sal is not a legal column name

Instead of a WHERE clause, a HAVING clause must be used to restrict groups

The GROUP BY clause must contain AVG(sal)

This SELECT statement is perfectly legal

The given SQL statement is illegal because it uses a WHERE clause to restrict groups instead of using a HAVING clause. In SQL, the WHERE clause is used to filter individual rows before they are grouped, while the HAVING clause is used to filter groups after they have been created. Since the statement is trying to restrict groups based on the average salary (AVG(sal)), it should use a HAVING clause instead of a WHERE clause.

Explanation

The given SQL statement is illegal because it uses a WHERE clause to restrict groups instead of using a HAVING clause. In SQL, the WHERE clause is used to filter individual rows before they are grouped, while the HAVING clause is used to filter groups after they have been created. Since the statement is trying to restrict groups based on the average salary (AVG(sal)), it should use a HAVING clause instead of a WHERE clause.

7. ) What operator would you choose to prevent this Oracle error message? (choose one) ORA-01427:single -row subquery returns more than one row

Use the IN operator

. Use the >= operator

Use the CAN EXIST operator

Use the = operator

Use the

The ORA-01427 error message occurs when a subquery returns more than one row, which is not allowed in certain situations. To prevent this error, the IN operator can be used. The IN operator allows for multiple values to be compared against a single value, ensuring that the subquery returns only one row. By using the IN operator, the query will be able to handle cases where the subquery returns multiple rows without causing the error.

Explanation

The ORA-01427 error message occurs when a subquery returns more than one row, which is not allowed in certain situations. To prevent this error, the IN operator can be used. The IN operator allows for multiple values to be compared against a single value, ensuring that the subquery returns only one row. By using the IN operator, the query will be able to handle cases where the subquery returns multiple rows without causing the error.

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

SELECT last_name, 12*salary* commission_pct FROM emp;

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

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

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

The correct answer is SELECT last_name, 12*salary*(nvl(commission_pct,0)) FROM emp; because the NVL function is used to substitute a null value with a specified value, in this case, 0. This ensures that even if the commission_pct column has null values, the calculation will still be performed and a value will be displayed in the calculated column for all employees.

Explanation

The correct answer is SELECT last_name, 12*salary*(nvl(commission_pct,0)) FROM emp; because the NVL function is used to substitute a null value with a specified value, in this case, 0. This ensures that even if the commission_pct column has null values, the calculation will still be performed and a value will be displayed in the calculated column for all employees.

9. Examine the data from the ORDERS and CUSTOMERS table. ORDERS ORD_ID ORD_DATE CUST_ID ORD_TOTAL 100 12-JAN-2000 15 180000 101 09-MAR-2000 40 12500 102 09-MAR-2000 35 12000 103 15-MAR-2000 15 6000 104 25-JUN-2000 15 5000 105 18-JUL-2000 20 7000 106 18-JUL-2000 35 6500 107 21-JUL-2000 20 8000 108 04-AUG-2000 10 CUSTOMERS CUST_ID CUST_NAME CITY 10 Smith Los Angeles 15 Bob San Francisco 20 Martin Chicago 25 Mary New York 30 Rina Chicago 35 Smith New York 40 Linda New York Which SQL statement retrieves the order ID, customer ID, and order total for the orders that are placed on the same day that Martin places his orders?

SELECT ord_id, cust_id, ord_total FROM orders, customersWHERE cust_name=’Mating’ AND ord_date IN (’18-JUL-2000’,’21-JUL-2000’);

The correct answer is the second option. This SQL statement retrieves the order ID, customer ID, and order total for the orders that are placed on the same day that Martin places his orders. It uses subqueries to first find the customer ID of Martin and then find the order dates for that customer. The main query then selects the order ID, customer ID, and order total from the orders table where the order date is in the list of order dates for Martin.

Explanation

The correct answer is the second option. This SQL statement retrieves the order ID, customer ID, and order total for the orders that are placed on the same day that Martin places his orders. It uses subqueries to first find the customer ID of Martin and then find the order dates for that customer. The main query then selects the order ID, customer ID, and order total from the orders table where the order date is in the list of order dates for Martin.

10. Which query is used to find maximum of the average salary of employees?

A) select avg(max(sal)) from emp;

B) select max(avg(sal)) from emp group by dept_id;

C) select maximum sal(avg) from emp;

D) none

The correct answer is b) select max(avg(sal)) from emp group by dept_id. This query uses the MAX function to find the maximum value of the average salary of employees. It also uses the AVG function to calculate the average salary for each department by grouping the results using the GROUP BY clause.

Explanation

The correct answer is b) select max(avg(sal)) from emp group by dept_id. This query uses the MAX function to find the maximum value of the average salary of employees. It also uses the AVG function to calculate the average salary for each department by grouping the results using the GROUP BY clause.

11. ) How many columns are presented after executing this query: SELECT address1||','||address2||','||address2 "Adress" FROM employee;

0

1

2

3

4

The query is selecting three columns: address1, address2, and address2 (aliased as "Address"). The concatenation operator (||) is used to combine the values of address1, a comma, address2, a comma, and address2 again. However, since the alias "Address" is used for the third column, it appears as if there are only two distinct columns in the result. Therefore, the correct answer is 1.

Explanation

The query is selecting three columns: address1, address2, and address2 (aliased as "Address"). The concatenation operator (||) is used to combine the values of address1, a comma, address2, a comma, and address2 again. However, since the alias "Address" is used for the third column, it appears as if there are only two distinct columns in the result. Therefore, the correct answer is 1.

12. You query the database with this command: **SELECT id_number, (quantity – 100 / 0.15 – 35 * 20) FROM inventory ; Which expression is evaluated first (choose one)?**

Quantity – 100

0.15-35

35*20

100/0.15

The expression 100/0.15 is evaluated first because it is inside parentheses and according to the order of operations, expressions inside parentheses are evaluated first.

Explanation

The expression 100/0.15 is evaluated first because it is inside parentheses and according to the order of operations, expressions inside parentheses are evaluated first.

13. How would you display a listing of the sums of employee salaries for those employees not making a commission, for each job type, including only those sums greater than 2500? (choose one)

Select job, sum(sal) from emp where sum(sal) > 2500 and comm is null;

Select job, sum(sal) from emp where comm is null group by job having sum(sal) > 2500;

Select job, sum(sal) from emp where sum(sal) > 2500 and comm is null group by job;

Select job, sum(sal) from emp group by job having sum(sal) > 2500 and comm is not null;

None of the above

The correct answer is to select the job and the sum of salaries from the employee table, where the commission is null. Then, group the results by job and filter out only those sums that are greater than 2500. This query will display a listing of the sums of employee salaries for those employees not making a commission, for each job type, including only those sums greater than 2500.

Explanation

The correct answer is to select the job and the sum of salaries from the employee table, where the commission is null. Then, group the results by job and filter out only those sums that are greater than 2500. This query will display a listing of the sums of employee salaries for those employees not making a commission, for each job type, including only those sums greater than 2500.

14. With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" is "Peter"?

SELECT [all] FROM Persons WHERE FirstName='Peter'

SELECT * FROM Persons WHERE FirstName='Peter'

SELECT * FROM Persons WHERE FirstName LIKE 'Peter'

SELECT [all] FROM Persons WHERE FirstName LIKE 'Peter'

The correct answer is "SELECT * FROM Persons WHERE FirstName='Peter'". This query uses the SELECT statement to retrieve all columns (*) from the table "Persons" where the value of the column "FirstName" is "Peter".

Explanation

The correct answer is "SELECT * FROM Persons WHERE FirstName='Peter'". This query uses the SELECT statement to retrieve all columns (*) from the table "Persons" where the value of the column "FirstName" is "Peter".

15. What TRUNCATE and DELETE commands does?

To retrieve rows in a table

To remove some part of a table

To get rid of all rows in a table

To recover all the rows deleted in a table

The TRUNCATE and DELETE commands are used to remove data from a table. However, the key difference is that TRUNCATE removes all rows from a table, while DELETE allows for selective removal of specific rows based on specified conditions. Therefore, the correct answer is "To get rid of all rows in a table."

Explanation

The TRUNCATE and DELETE commands are used to remove data from a table. However, the key difference is that TRUNCATE removes all rows from a table, while DELETE allows for selective removal of specific rows based on specified conditions. Therefore, the correct answer is "To get rid of all rows in a table."

16. ) Evaluate the set of SQL statements: CREATE TABLE dept (deptno NUMBER(2), dname VARCNAR2(14), loc VARCNAR2(13)); ROLLBACK; DESCRIBE DEPT What is true about the set?

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

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

The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does notexist.

The DESCRIBE DEPT statement is used to display the structure of the DEPT table. It provides information about the columns and their data types in the table.

Explanation

The DESCRIBE DEPT statement is used to display the structure of the DEPT table. It provides information about the columns and their data types in the table.

17. The EMPLOYEE tables has these columns: LAST_NAME VARCHAR2(35) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(5,2) You want to display the name and annual salary multiplied by the commission_pct for all employees. For records that have a NULL commission_pct, a zero must be displayed against the calculated column. Which SQL statement displays the desired results?

SELECT last_name, (salary * 12) * commission_pct FROM EMPLOYEES;

SELECT last_name, (salary * 12) * IFNULL(commission_pct, 0) FROM EMPLOYEES;

SELECT last_name, (salary * 12) * NVL2(commission_pct, 0) FROM EMPLOYEES;

SELECT last_name, (salary * 12) * NVL(commission_pct, 0) FROM EMPLOYEES;

The correct answer is "SELECT last_name, (salary * 12) * NVL(commission_pct, 0) FROM EMPLOYEES;". This statement uses the NVL function to replace NULL values in the commission_pct column with 0. It then multiplies the annual salary (salary * 12) by the commission_pct to calculate the desired result.

Explanation

The correct answer is "SELECT last_name, (salary * 12) * NVL(commission_pct, 0) FROM EMPLOYEES;". This statement uses the NVL function to replace NULL values in the commission_pct column with 0. It then multiplies the annual salary (salary * 12) by the commission_pct to calculate the desired result.

18. Which of the following queries would show the salaries of all employees (not the boss) who have the same name as the boss (the only employee without a manager (mgr)) (choose one)?

Select sal from emp where ename same as (ename where mgr is NULL);

Select sal from emp where ename like (select ename from emp where mgr is NULL) and mgr is not NULL;

Select sal from emp where mgr != NULL and ename = (select ename from emp where mgr = NULL);

All of the above

None of the above

The correct answer is the second option: select sal from emp where ename like (select ename from emp where mgr is NULL) and mgr is not NULL. This query selects the salaries from the emp table where the employee name is the same as the boss (the only employee without a manager) and the manager is not NULL. This ensures that only employees who have the same name as the boss and are not the boss themselves are included in the result.

Explanation

The correct answer is the second option: select sal from emp where ename like (select ename from emp where mgr is NULL) and mgr is not NULL. This query selects the salaries from the emp table where the employee name is the same as the boss (the only employee without a manager) and the manager is not NULL. This ensures that only employees who have the same name as the boss and are not the boss themselves are included in the result.

19. Examine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2) JOB_CAT VARCHARD2(30) SALARY NUMBER(8,2) Which statement shows the maximum salary paid in each job category of each department?

SELECT dept_id, job_cat, MAX(salary) FROM employees WHERE salary > MAX(salary);

SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id, job_cat;

SELECT dept_id, job_cat, MAX(salary) FROM employees;

SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id;

SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id, job_cat, salary;

The correct answer is "SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id, job_cat;". This statement uses the GROUP BY clause to group the data by department and job category. Then, it uses the MAX() function to find the maximum salary within each group. This will give the maximum salary paid in each job category of each department.

Explanation

The correct answer is "SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id, job_cat;". This statement uses the GROUP BY clause to group the data by department and job category. Then, it uses the MAX() function to find the maximum salary within each group. This will give the maximum salary paid in each job category of each department.

20. All of the following can ONLY be used with numeric datatypes except: (choose one)

AVG

COUNT

SUM

STDDEV

VARIANCE

COUNT is the only function among the options that can be used with both numeric and non-numeric datatypes. It counts the number of rows in a table or the number of occurrences of a specific value. AVG, SUM, STDDEV, and VARIANCE are all functions that specifically operate on numeric datatypes and perform mathematical calculations.

Explanation

COUNT is the only function among the options that can be used with both numeric and non-numeric datatypes. It counts the number of rows in a table or the number of occurrences of a specific value. AVG, SUM, STDDEV, and VARIANCE are all functions that specifically operate on numeric datatypes and perform mathematical calculations.

21. Which Oracle access method is the fastest way for Oracle to retrieve a single row?

Primary key access

Access via unique index

Table access by ROWID

Full table scan

Table access by ROWID is the fastest way for Oracle to retrieve a single row because it directly accesses the row using the unique identifier known as ROWID. This method does not require any additional lookups or index scans, making it the most efficient way to retrieve a specific row from a table. Primary key access and access via unique index also involve index lookups, while a full table scan reads all the rows in the table, making them slower compared to table access by ROWID.

Explanation

Table access by ROWID is the fastest way for Oracle to retrieve a single row because it directly accesses the row using the unique identifier known as ROWID. This method does not require any additional lookups or index scans, making it the most efficient way to retrieve a specific row from a table. Primary key access and access via unique index also involve index lookups, while a full table scan reads all the rows in the table, making them slower compared to table access by ROWID.

22. Which of the following query display the last name,hire date and the day of the week on which the employee started with column label as DAY and ordered by the day of the week starting with Monday.

SELECT last_name, hire_date, TO_CHAR(hire_date,'DAY') AS DAYFROM employeesORDER BY hire_date, DAY;

. SELECT last_name, hire_date, TO_CHAR(hire_date,'DD') AS DAYFROM employeesORDER BY hire_date, DAY;

SELECT last_name, hire_date, TO_CHAR(hire_date,'DY') AS DAYFROM employeesORDER BY hire_date, DAY;

All the above.

The correct answer is the first option: SELECT last_name, hire_date, TO_CHAR(hire_date,'DAY') AS DAY
FROM employees
ORDER BY hire_date, DAY;

This query selects the last name, hire date, and the day of the week on which the employee started. The TO_CHAR function is used to convert the hire date to the day of the week format. The column label for the day of the week is set as "DAY". The result is ordered by the hire date and the day of the week, starting with Monday.

Explanation

The correct answer is the first option: SELECT last_name, hire_date, TO_CHAR(hire_date,'DAY') AS DAY
FROM employees
ORDER BY hire_date, DAY;

This query selects the last name, hire date, and the day of the week on which the employee started. The TO_CHAR function is used to convert the hire date to the day of the week format. The column label for the day of the week is set as "DAY". The result is ordered by the hire date and the day of the week, starting with Monday.

23. Which clause should you use to exclude GROUP results ?

WHERE

RESTRICT

HAVING

GROUP BY

The HAVING clause is used to exclude GROUP results in a SQL query. It is typically used in combination with the GROUP BY clause to filter the results based on conditions that are applied to the grouped data. This allows you to specify criteria for the groups that should be included or excluded in the final result set. The HAVING clause is different from the WHERE clause, which is used to filter individual rows before they are grouped.

Explanation

The HAVING clause is used to exclude GROUP results in a SQL query. It is typically used in combination with the GROUP BY clause to filter the results based on conditions that are applied to the grouped data. This allows you to specify criteria for the groups that should be included or excluded in the final result set. The HAVING clause is different from the WHERE clause, which is used to filter individual rows before they are grouped.

24. Substitution variable can be used with which of the following clauses?

Select

From

Where

Group by

All the above

Substitution variables can be used with all of the mentioned clauses (Select, From, Where, Group by). Substitution variables are placeholders that can be used to dynamically substitute values in a SQL statement. They are commonly used to make the SQL statement more flexible and reusable by allowing users to input different values at runtime. By using substitution variables, the same SQL statement can be executed with different values without the need to modify the statement itself.

Explanation

Substitution variables can be used with all of the mentioned clauses (Select, From, Where, Group by). Substitution variables are placeholders that can be used to dynamically substitute values in a SQL statement. They are commonly used to make the SQL statement more flexible and reusable by allowing users to input different values at runtime. By using substitution variables, the same SQL statement can be executed with different values without the need to modify the statement itself.

25. What will be the output ? SELECT ROUND(TRUNC(MOD(1600,10),-1),2) from dual;

Error

0

1

0.00

The given SQL statement calculates the modulus of 1600 divided by 10, which results in 0. The TRUNC function is then used to truncate this value to the nearest multiple of 10, which is also 0. Finally, the ROUND function is applied to round this value to 2 decimal places, which is still 0. Therefore, the output of the statement will be 0.

Explanation

The given SQL statement calculates the modulus of 1600 divided by 10, which results in 0. The TRUNC function is then used to truncate this value to the nearest multiple of 10, which is also 0. Finally, the ROUND function is applied to round this value to 2 decimal places, which is still 0. Therefore, the output of the statement will be 0.

26. What output will the follwing statement produce? Select NVL2(NULL,'NOT NULL', NULL) from dual;

NULL

NOT NULL

Function NVL2 is not defined

None of the above

The NVL2 function in Oracle is used to determine the value to be returned based on the condition. In this case, the condition is NULL. If the condition is true, the second argument ('NOT NULL') is returned. If the condition is false or NULL, the third argument (NULL) is returned. Since the condition is NULL, the third argument is returned, resulting in the output of NULL.

Explanation

The NVL2 function in Oracle is used to determine the value to be returned based on the condition. In this case, the condition is NULL. If the condition is true, the second argument ('NOT NULL') is returned. If the condition is false or NULL, the third argument (NULL) is returned. Since the condition is NULL, the third argument is returned, resulting in the output of NULL.

27. Which of the following statements are true (choose two)?

'NOT IN' is equivalent to != ALL

'IN' is equivalent to != ALL

'NOT IN' is equivalent to =ANY

'IN' is equivalent to =ANY

None of the above are true

'NOT IN' is equivalent to != ALL means that when using the 'NOT IN' operator, it checks if a value is not present in a set of values, similar to using the != (not equal to) operator with the ALL keyword.

'IN' is equivalent to =ANY means that when using the 'IN' operator, it checks if a value is present in a set of values, similar to using the = (equal to) operator with the ANY keyword.

Both statements explain the equivalence between these operators and their corresponding combinations with the != and = operators.

Explanation

'NOT IN' is equivalent to != ALL means that when using the 'NOT IN' operator, it checks if a value is not present in a set of values, similar to using the != (not equal to) operator with the ALL keyword.

'IN' is equivalent to =ANY means that when using the 'IN' operator, it checks if a value is present in a set of values, similar to using the = (equal to) operator with the ANY keyword.

Both statements explain the equivalence between these operators and their corresponding combinations with the != and = operators.

Submit

28. Which of the following can be a valid column name?

Column

1966_Invoices

Catch_#22

#Invoices

None of the above

column name should start with a alphabet and it cannot start with no: or special char

Explanation

column name should start with a alphabet and it cannot start with no: or special char

29. Which SELECT statement will return a numeric value?

SELECT SYSDATE - enroll_date + TO_DATE('29-MAY-02')FROM student;

SELECT (14 + enroll_date) + 30.5 * 9FROM student;

SELECT (SYSDATE+ enroll_date) + TO_DATE('29-MAY-02')FROM student;

SELECT (SYSDATE - enroll_date) + 30.5 * 9FROM student;

The SELECT statement that will return a numeric value is the fourth option: SELECT (SYSDATE - enroll_date) + 30.5 * 9 FROM student. This is because it performs mathematical operations (subtraction, multiplication, and addition) on the values SYSDATE, enroll_date, and 30.5, which are all numeric values. The result of these operations will also be a numeric value.

Explanation

The SELECT statement that will return a numeric value is the fourth option: SELECT (SYSDATE - enroll_date) + 30.5 * 9 FROM student. This is because it performs mathematical operations (subtraction, multiplication, and addition) on the values SYSDATE, enroll_date, and 30.5, which are all numeric values. The result of these operations will also be a numeric value.

30. ) Assuming today is Monday, 10 July 2000, what is returned by this statement: SELECT to_char(NEXT_DAY(sysdate, 'MONDAY'), 'DD-MON-RR') FROM dual;

03-JUL-00

10-JUL-00

12-JUL-00

11-JUL-00

17-JUL-00

09-JUL-00

The statement returns the date of the next Monday from the current date. Since today is Monday, 10 July 2000, the next Monday after this date is 17 July 2000. Therefore, the statement will return "17-JUL-00".

Explanation

The statement returns the date of the next Monday from the current date. Since today is Monday, 10 July 2000, the next Monday after this date is 17 July 2000. Therefore, the statement will return "17-JUL-00".

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

SUM(start_date)

AVG(start_date)

COUNT(start_date)

AVG(start_date, end_date)

MIN(start_date)

MAXIMUM(start_date)

The COUNT(start_date) function is valid on the START_DATE column because it counts the number of non-null values in the column. The MIN(start_date) function is also valid on the START_DATE column because it returns the minimum value in the column.

Explanation

The COUNT(start_date) function is valid on the START_DATE column because it counts the number of non-null values in the column. The MIN(start_date) function is also valid on the START_DATE column because it returns the minimum value in the column.

Submit

32. SELECT ROUND (45.953, -1), TRUNC (45.936, 2) FROM dual; Which values are displayed?

46 and 45

46 and 45.93

50 and 45.93

50 and 45.9

45 and 45.93

45.95 and 45.93

The ROUND function rounds the first number, 45.953, to the nearest multiple of 10, resulting in 50. The TRUNC function truncates the second number, 45.936, to two decimal places, resulting in 45.93. Therefore, the values displayed are 50 and 45.93.

Explanation

The ROUND function rounds the first number, 45.953, to the nearest multiple of 10, resulting in 50. The TRUNC function truncates the second number, 45.936, to two decimal places, resulting in 45.93. Therefore, the values displayed are 50 and 45.93.

33. Given the following data in the emp table: ENAME SALARY -------- -------- PING 5000 AILYN 4999 SAM 1000 LESLIE 3000 TOM 2500 RAVI 10000 What will the following select statement produce (choose one)? SELECT ename FROM emp WHERE salary BETWEEN 3000 AND 5000;

ENAME -------- AILYN

. ENAME -------- . AILYNCHRIS LESLIE

An error

None of the above

The select statement will produce the following result: ENAME
--------
AILYN
CHRIS
LESLIE
This is because the select statement is filtering the data from the emp table based on the condition WHERE salary BETWEEN 3000 AND 5000, which means it will select all the rows where the salary is between 3000 and 5000, inclusive. In this case, the rows with the names AILYN, CHRIS, and LESLIE have salaries within this range, so they will be included in the result. Therefore, the correct answer is "None of the above" as it is not one of the given options.

Explanation

The select statement will produce the following result: ENAME
--------
AILYN
CHRIS
LESLIE
This is because the select statement is filtering the data from the emp table based on the condition WHERE salary BETWEEN 3000 AND 5000, which means it will select all the rows where the salary is between 3000 and 5000, inclusive. In this case, the rows with the names AILYN, CHRIS, and LESLIE have salaries within this range, so they will be included in the result. Therefore, the correct answer is "None of the above" as it is not one of the given options.

34. Examine the structure of the employees table. Employee_id number primary key First_name varchar2(25) Last_name varchar2(25) Which 3 statements inserts a row into the table employees?

A. INSERT INTO employees VALUES(NULL,’John’’Smith’);

B. INSERT INTO employees(first_name,last_name) VALUES(’John’’Smith’);

C. INSERT INTO employees VALUES(1000,’John’’Smith’);

D. INSERT INTO employees (first_name,last_name,employee_id) VALUES(1000,’John’’Smith’);

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

F. INSERT INTO employees (employee_id,first_name,last_name) VALUES(1000’John’,’’);

The correct answers are C, E, and F.

C. INSERT INTO employees VALUES(1000,’John’’Smith’); - This statement inserts a row into the employees table with the specified values for employee_id, first_name, and last_name.

E. INSERT INTO employees (employee_id) VALUES(1000); - This statement inserts a row into the employees table with only the employee_id specified.

F. INSERT INTO employees (employee_id,first_name,last_name) VALUES(1000’John’,’’); - This statement inserts a row into the employees table with the specified values for employee_id, first_name, and last_name. However, there is a syntax error in the statement as there is a missing comma between 1000 and 'John'.

Explanation

The correct answers are C, E, and F.

C. INSERT INTO employees VALUES(1000,’John’’Smith’); - This statement inserts a row into the employees table with the specified values for employee_id, first_name, and last_name.

E. INSERT INTO employees (employee_id) VALUES(1000); - This statement inserts a row into the employees table with only the employee_id specified.

F. INSERT INTO employees (employee_id,first_name,last_name) VALUES(1000’John’,’’); - This statement inserts a row into the employees table with the specified values for employee_id, first_name, and last_name. However, there is a syntax error in the statement as there is a missing comma between 1000 and 'John'.

Submit

35. Which of the following are legal queries? (choose one or more)

SELECT deptno, count(deptno) FROM emp GROUP BY ename;

SELECT deptno, count(deptno), job FROM emp GROUP BY deptno;

SELECT deptno, avg(sal) FROM emp;

SELECT deptno, avg(sal) FROM emp GROUP BY deptno;

SELECT avg(sal) FROM emp GROUP BY deptno;

The correct answer includes two queries: "SELECT deptno, avg(sal) FROM emp GROUP BY deptno;" and "SELECT avg(sal) FROM emp GROUP BY deptno;". These queries are legal because they both use the "avg" function to calculate the average salary and group the results by the department number. This is a valid use of the "GROUP BY" clause in SQL.

Explanation

The correct answer includes two queries: "SELECT deptno, avg(sal) FROM emp GROUP BY deptno;" and "SELECT avg(sal) FROM emp GROUP BY deptno;". These queries are legal because they both use the "avg" function to calculate the average salary and group the results by the department number. This is a valid use of the "GROUP BY" clause in SQL.

Submit

SQL Database Quiz Questions

1. Operator John needs to search for text data in a column, but he only remembers part of the string. Which of the following SQL operations allows the use of wildcard comparisons (choose one)?

2.

What first name or nickname would you like us to use?

2. Which SQL statement is used to return only different values?

3. The name of the only column in the DUAL table is

4. With SQL, how do you select all the records from a table named "Persons" where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?

5. What will be the output select INSTR('HELLOWORLD','W') from dual; ?

6. The following SQL statement is illegal because: (choose one) SELECT deptno, AVG(sal) FROM emp WHERE AVG(sal)> 2000 GROUP BY deptno;

7. ) What operator would you choose to prevent this Oracle error message? (choose one) ORA-01427:single -row subquery returns more than one row

10. Which query is used to find maximum of the average salary of employees?

11. ) How many columns are presented after executing this query: SELECT address1||','||address2||','||address2 "Adress" FROM employee;

12. You query the database with this command: SELECT id_number, (quantity – 100 / 0.15 – 35 * 20) FROM inventory ; Which expression is evaluated first (choose one)?

13. How would you display a listing of the sums of employee salaries for those employees not making a commission, for each job type, including only those sums greater than 2500? (choose one)

14. With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" is "Peter"?

15. What TRUNCATE and DELETE commands does?

16. ) Evaluate the set of SQL statements: CREATE TABLE dept (deptno NUMBER(2), dname VARCNAR2(14), loc VARCNAR2(13)); ROLLBACK; DESCRIBE DEPT What is true about the set?

18. Which of the following queries would show the salaries of all employees (not the boss) who have the same name as the boss (the only employee without a manager (mgr)) (choose one)?

19. Examine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2) JOB_CAT VARCHARD2(30) SALARY NUMBER(8,2) Which statement shows the maximum salary paid in each job category of each department?

20. All of the following can ONLY be used with numeric datatypes except: (choose one)

21. Which Oracle access method is the fastest way for Oracle to retrieve a single row?

22. Which of the following query display the last name,hire date and the day of the week on which the employee started with column label as DAY and ordered by the day of the week starting with Monday.

23. Which clause should you use to exclude GROUP results ?

24. Substitution variable can be used with which of the following clauses?

25. What will be the output ? SELECT ROUND(TRUNC(MOD(1600,10),-1),2) from dual;

26. What output will the follwing statement produce? Select NVL2(NULL,'NOT NULL', NULL) from dual;

27. Which of the following statements are true (choose two)?

28. Which of the following can be a valid column name?

29. Which SELECT statement will return a numeric value?

30. ) Assuming today is Monday, 10 July 2000, what is returned by this statement: SELECT to_char(NEXT_DAY(sysdate, 'MONDAY'), 'DD-MON-RR') FROM dual;

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

32. SELECT ROUND (45.953, -1), TRUNC (45.936, 2) FROM dual; Which values are displayed?

33. Given the following data in the emp table: ENAME SALARY -------- -------- PING 5000 AILYN 4999 SAM 1000 LESLIE 3000 TOM 2500 RAVI 10000 What will the following select statement produce (choose one)? SELECT ename FROM emp WHERE salary BETWEEN 3000 AND 5000;

34. Examine the structure of the employees table. Employee_id number primary key First_name varchar2(25) Last_name varchar2(25) Which 3 statements inserts a row into the table employees?

35. Which of the following are legal queries? (choose one or more)

12. You query the database with this command: **SELECT id_number, (quantity – 100 / 0.15 – 35 * 20) FROM inventory ; Which expression is evaluated first (choose one)?**