Test Your SQL Basics

The SQL SELECT Statement Questions

1. Determine the output of the below query
SELECT '6+9' FROM dual;
A) 15
B) 6+9
C) 6
D) 9

2. At least how many clauses must be present in a SELECT statement?
A) 0 (Zero)
B) 1
C) 2
D) 5

3. Which of the following statement is correct?
A) EVERY table expression is a part of SELECT statement
B) Each SELECT statement does not consist of a table expression
C) Every SELECT statement is built from a table expression
D) All of the above

4. Which of the following are the optional clauses?
A) SELECT and WHERE
B) WHERE, GROUP BY and ORDER BY
C) SELECT, WHERE and ORDER BY
D) SELECT, WHERE, ORDER BY and GROUP BY

5. Which of the following is the last clause that is executed in a query?
A) SELECT
B) FROM
C) WHERE
D) GROUP BY

6. Which of the following is the correct basic syntax of SELECT clause?
A) SELECT column1, column2,….columnx WHERE table_name;
B) SELECT column1, column2,….columnx
C) SELECT column1, column2,…columnx ORDER BY table_name;
D) SELECT column1, column2,…columnx FROM table_name;

7. Determine the output of the below query
SELECT 36/2-5*10 FROM dual;
A) 130
B) -32
C) -120
D) 175

8. Determine the output of the below query
SELECT sal + NULL FROM emp WHERE empno = 7369;
A) sal + NULL
B) NULL
C) 0
D) 1250

9. Which command is used to display the structure of a table?
A) LIST
B) SHOW
C) DESCRIBE
D) STRUCTURE

10. Predict the output of the below query
SELECT 50 || 0001 FROM dual
A) 500001
B) 51
C) 501
D) 5001

Restricting and Sorting Data Questions

1. What are the elements NOT contained in the WHERE clause predicate of the SELECT query?

A) Comparison operator

B) Comparison condition

C) Column Name

D) Table Name

2. Which of the following values can NOT be returned after evaluation of WHERE clause condition?

A) UNKNOWN

B) TRUE

C) FALSE

D) NULL

3. Which of the following statements are correct about the WHERE clause?

A) Column Alias can be used in WHERE clause to refer a column

B) Comparison operator is an optional element in WHERE clause condition

C) Functions can be used as operands in the WHERE clause

D) There can be multiple WHERE clause in a SELECT query

4. From the below operators, which one of them holds the highest precedence level?

A) Division (/)

B) Multiplication (*)

C) Brackets ( () )

D) Subtraction

5. What does the selection of columns in a SELECT statement known as?

A) Retrieval

B) Selection

C) Projection

D) Limiting

6. Which of the following WHERE clauses will NOT fit in the below SELECT query?

SELECT ename, deptno, sal FROM emp;

A) WHERE HIREDATE IN ('02-JUN-2004');

B) WHERE SAL IN ('1000','4000','2000');

C) WHERE JOB IN (SALES,CLERK);

D) WHERE COMM BETWEEN 0.1 AND 0.5;

7. Choose the WHERE clause that extracts the DNAME values containing the character literal “er” from the DEPT table.

A) WHERE DNAME IN ('%e%r');

B) WHERE DNAME LIKE '%er%';

C) WHERE DNAME BETWEEN 'e' AND 'r';

D) WHERE DNAME CONTAINS 'e%r';

8. Which of the following is used to end a SQL query?

A) :

B) ;

C) .

D) /

9. What is the default date format in Oracle?

A) DD-MON-YY

B) DD-MON-YYYY

C) DD-MM-RR

D) DD-MON-RR

10. Which of the following clause defines a Membership condition?

A) BETWEEN

B) LIKE

C) IS NULL

D) IN (NOT IN)

Using Single-Row Functions Questions

1. What will be the outcome of the following query?
SELECT ROUND(144.23,-1) FROM dual;
A) 140
B) 144
C) 150
D) 100

2. Which of the following statements is true regarding the COUNT function?
A) COUNT (*) counts duplicate values and NULL values in columns of any data type.
B) COUNT function cannot work with DATE datatypes.
C) COUNT (DISTINCT job_id) returns the number of rows excluding rows containing duplicates and NULL values in the job_id column.
D) A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.

3. Which of the following commands is used to count the number of rows and non-NULL values in Oracle database?
A) NOT NULL
B) INSTR
C) SUBSTR
D) COUNT

4. What will be the outcome of the query given below?
SELECT 100+NULL+999 FROM dual;
A) 100
B) 999
C) NULL
D) 1099

5. Determine the output of the below query.
SELECT RPAD(ROUND('78945.45'),10,'*') FROM dual;
A) 78945*****
B) **78945.45
C) The function RPAD cannot be nested with other functions
D) 78945.45****

6. Which of the following commands allows you to substitute a value whenever a NULL or non-NULL value is encountered in an SQL query?
A) NVL
B) NVLIF
C) NVL2
D) LNNVL

7. What is true about the NULLIF function in Oracle DB?
A) NULLIF(expr1,expr2) will return expr2 if the two expressions are NOT NULL.
B) NULLIF(expr1,expr2) will return 0 if the two expressions are NULL.
C) NULLIF(expr1,expr2) will return NULL if the two expressions are equal.
D) Expr1 can be NULL in NULLIF(expr1, expr2)

8. Which of the following is not a property of functions?
A) Perform calculations on data
B) Convert column data types
C) Modify individual data items
D) None of the above

9. Which of the following are also called Group functions?
A) Single row functions
B) Multi group functions
C) Multiple row functions
D) Single group functions

10. What is true about Number functions?
A) They return both Character as well as Number values
B) They can't accept expressions as input
C) Number functions can't be nested.
D) They accept Number arguments and return Number values only.

Conditional Expressions Questions

1. What is true about data types in Oracle DB?
A) They are given to columns for sorting purposes.
B) They are given to columns for a structured representation in a table.
C) They are given to columns to constrain the nature of the data it can store.
D) They are not mandatory.

2. Which of the following functions simplify working with columns that potentially contain null values?
A) Nested functions
B) General functions
C) Conditional functions
D) None of the above

3. How many input parameters are mandatory in NVL function?
A) 0
B) 1
C) 2
D) 3

4. What will be the output of the following query?
SELECT NVL(1234) FROM dual;
A) 1234
B) 1000
C) NULL
D) ORA-00909:invalid number of arguments error

5. What happens during an implicit conversion in Oracle DB?
A) Oracle DB implicitly converts one data type to the expected data type
B) The user has to convert the data type to the expected data type
C) Oracle DB doesn't convert any data type
D) Implicit conversion can be controlled by the user

6. Which of the following expressions can be used explicitly for a conversion of a NUMBER to a CHAR?
A) TO_CHAR
B) Use TO_DATE and then convert the date to a NUMBER
C) TO_NUMBER
D) Such conversion is not possible

7. Which of the following are the functions for explicit conversion provided by Oracle to convert one data type to the other?
A) TO_CHAR
B) TO_DATE
C) TO_NUMBER
D) All of the above

8. Which of the following is true about the following statement in Oracle DB?
TO_CHAR(date, 'frmt')
A) The fmt can be written in double quotes as well as single quotes.
B) Case of the fmt doesn't matter in this function
C) Fmt can include any character or NUMBER
D) The fmt has to be enclosed in single quotes and has to be a valid date format.

9. How many maximum places for display will Oracle DB allocate to the Month element in the following statement?
SELECT TO_CHAR (sysdate, 'fmMonth') FROM dual;
A) 5
B) 6
C) 7
D) 9

10. What is true about the COALESCE function in Oracle DB?
A) It can take only 2 arguments
B) All the arguments in the COALESCE function can be of different data types
C) All the arguments in the COALESCE function should be of the same data type
D) None of the above

Search This Blog

{Learn-Intern-Certify}©