Prep and try our 1Z0-007 valid and latest training questions & answers

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.

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.

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

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.

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;