Oracle Introduction to Oracle9i: SQL Sample Questions:

1. Which two are true about aggregate functions? (Choose two.)

A) You cannot group the rows of a table by more than one column while using aggregate functions.
B) You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.
C) You can use aggregate functions on a table, only by grouping the whole table as one single group.
D) You can use aggregate functions in any clause of a SELECT statement.
E) You can use aggregate functions only in the column list of the select clause and in the WHERE clause of a SELECT statement.
F) You can pass column names, expressions, constants, or functions as parameter to an aggregate function.

2. Which three statements about subqueries are true? (Choose three)

A) A single row subquery can retrieve only one column and one row.
B) A single row subquery can retrieve only one row but many columns.
C) A single row subquery can use the IN operator.
D) A multiple row subquery can use the "=" operator.
E) A multiple row subquery can be compared by using the ">" operator.
F) A multiple row subquery can retrieve multiple rows and multiple columns.

3. The EMPLOYEES table contains these columns:

You need to write a query that will produce these results:
1.Display the salary multiplied by the commission_pct.
2.Exclude employees with a zero commission_pct.
3.Display a zero for employees with a null commission value.
Evaluate the SQL statement:
SELECT LAST_NAME, SALARY*COMMISSION_PCT
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
What does the statement provide?

A) All of the desired results
B) One of the desired results
C) Two of the desired results
D) An error statement

4. What is true about joining tables through an equijoin?

A) You can join a maximum of two tables through an equijoin.
B) You specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.
C) To join two tables through an equijoin, the columns in the join condition must be primary key and foreign key columns.
D) You can join a maximum of two columns through an equijoin.
E) You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions.

5. You want to display the titles of books that meet these criteria:
1.Purchased before January 21, 2001
2.Price is less then $500 or greater than $900
You want to sort the results by their data of purchase, starting with the most recently
bought book.
Which statement should you use?

A) SELECT book_title
FROM books
WHERE price between 500 and 900
AND purchase_date < '21-JAN-2001'
ORDER BY purchase_date;
B) SELECT book_title
FROM books
WHERE (price < 500 OR price > 900)
AND purchase_date < '21-JAN-2001'
ORDER BY purchase date DESC;
C) SELECT book_title
FROM books
WHERE price < 500 or > 900
AND purchase_date < '21-JAN-2001'
ORDER BY purchase date DESC;
D) SELECT book_title
FROM books
WHERE price IN (500,900)
AND purchase_date < '21-JAN-2001'
ORDER BY purchase date ASC;

Solutions: