Section 1
(Answer
all questions in this section)
1. The
SELECT statement retrieves information from the database. In a SELECT
statement, you can do all of the following EXCEPT: Mark for Review
(1) Points
Joining
Selection
Projection
Manipulation
(*)
2. In the
default order of precedence, which operator would be evaluated first? Mark for Review
(1) Points
Multiplications
and Divisions are at the same level and would be evaluated first based on left
to right order (*)
Subtractions
and Additions are at the same level and would be evaluated first based on left
to right order
Divisions
and Subtractions are at the same level and would be evaluated first based on
left to right order
Additions
and Multiplications are at the same level and would be evaluated first based on
left to right order
3. You
cannot use computers unless you completely understand exactly how they work.
True or False? Mark for Review
(1) Points
True
False
(*)
4. There
is only one kind of software used by all computers. True or False? Mark for Review
(1) Points
True
False
(*)
5. Databases
are used in most countries and by most governments. Life, as we know it, would
change drastically if we no longer had access to databases. True or False? Mark for Review
(1) Points
True
(*)
False
Section 2
(Answer
all questions in this section)
6. The
EMPLOYEES table includes these columns:
EMPLOYEE_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(15) NOT NULL
FIRST_NAME VARCHAR2(10) NOT NULL
HIRE_DATE DATE NOT NULL
You want to produce a report that provides the last
names, first names, and hire dates of those employees who were hired between
March 1, 2000, and August 30, 2000. Which statements can you issue to
accomplish this task?
Mark for Review
(1) Points
SELECT
last_name, first_name, hire_date
FROM employees
AND hire_date >= '01-Mar-2000' and hire_date <=
'30-Aug-2000';
SELECT
last_name, first_name, hire_date
FROM employees
GROUP BY hire_date >= '01-Mar-2000' and hire_date
<= '30- Aug-2000';
SELECT
last_name, first_name, hire_date
FROM employees
WHERE hire_date BETWEEN '30-Aug-2000' AND '01-Mar-2000';
SELECT
last_name, first_name, hire_date
FROM employees
WHERE hire_date BETWEEN '01-Mar-2000' AND '30-Aug-2000';
(*)
7. When using
the "LIKE" operator, the % and _ symbols can be used to do a
pattern-matching, wild card search. True or False? Mark for Review
(1) Points
True
(*)
False
8. Which
SELECT statement will display both unique and non-unique combinations of the
MANAGER_ID and DEPARTMENT_ID values from the EMPLOYEES table? Mark for Review
(1) Points
SELECT
manager_id, department_id FROM employees; (*)
SELECT
manager_id, DISTINCT department_id FROM employees;
SELECT
manager_id, department_id DISTINCT FROM employees;
SELECT
DISTINCT manager_id, department_id FROM employees;
9. You
want to retrieve a list of customers whose last names begin with the letters
'Fr' . Which keyword should you include in the WHERE clause of your SELECT
statement to achieve the desired result?
Mark for Review
(1) Points
BETWEEN
AND
IN
LIKE
(*)
10. Which
of the following elements cannot be included in a WHERE clause? Mark for Review
(1) Points
A
constant
A
column name
A
column alias (*)
A
comparison condition
Section 2
(Answer
all questions in this section)
11. You
need to display employees with salaries that are at least 30000 or higher.
Which comparison operator should you use?
Mark for Review
(1) Points
"=>"
>
!=
>=
(*)
Section 3
(Answer
all questions in this section)
12. What
value will the following SQL statement return?
SELECT employee_id
FROM employees
WHERE employee_id BETWEEN 100 AND 150
OR employee_id
IN(119, 175, 205)
AND (employee_id
BETWEEN 150 AND 200);
Mark for Review
(1) Points
200,
201, 202, 203, 204, 205, 206
100,
101, 102, 103, 104, 107, 124, 141, 142, 143, 144, 149 (*)
19
No
rows will be returned
13. Which
SELECT statement should you use to limit the display of product information to
those products with a price of less than 50?
Mark for Review
(1) Points
SELECT
product_id, product_name
FROM products
WHERE price < 50;
(*)
SELECT
product_id, product_name
FROM products
WHERE price <= 50;
SELECT
product_id, product_name
FROM products
HAVING price < 50;
SELECT
product_id, product_name
FROM products
GROUP BY price < 50;
SELECT
product_id, product_name
FROM products
WHERE price < 50.00
GROUP BY price;
14. Which
clause would you include in a SELECT statement to sort the rows returned by the
LAST_NAME column? Mark for
Review
(1) Points
ORDER
BY (*)
WHERE
HAVING
FROM
15. Will
the following statement return one row?
SELECT MAX(salary), MIN(Salary), AVG(SALARY)
FROM employees;
Mark for Review
(1) Points
Yes,
it will return the highest salary from each employee.
Yes,
it will return the average salary from the employees table.
Yes,
it will return the highest salary, the lowest salary, and the average salary
from all employees. (*)
No,
it is illegal. You cannot use more than one multi-row function in a SELECT
statement.
Section 4
(Answer
all questions in this section)
16. ROUND
and TRUNC functions can be used with which of the following Datatypes? Mark for Review
(1) Points
Dates
and numbers (*)
Dates
and characters
Numbers
and characters
None
of the above
17. You
want to create a report that displays all orders and their amounts that were
placed during the month of January. You want the orders with the highest
amounts to appear first. Which query should you issue? Mark for Review
(1) Points
SELECT
orderid, total
FROM orders
WHERE order_date IN ( 01-Jan-2002 , 31-Jan-2002 )
ORDER BY total;
SELECT
orderid, total
FROM orders
WHERE order_date BETWEEN '31-Jan-2002' AND '01-Jan-2002'
ORDER BY total DESC;
SELECT
orderid, total
FROM orders
WHERE order_date BETWEEN '01-Jan-2002' AND '31-Jan-2002'
ORDER BY total DESC;
(*)
SELECT
orderid, total
FROM orders
WHERE order_date LIKE '01-Jan-2002' AND '31-Jan-2002'
ORDER BY total DESC;
18. The
PRICE table contains this data:
PRODUCT_ID MANUFACTURER_ID
86950 59604
You query the database and return the value 95. Which
script did you use?
Mark for Review
(1) Points
SELECT
TRIM(product_id, -3, 2)
FROM price
WHERE manufacturer_id = 59604;
SELECT
LENGTH(product_id, 3, 2)
FROM price
WHERE manufacturer_id = 59604;
SELECT
SUBSTR(product_id, -1, 3)
FROM price
WHERE manufacturer_id = 59604;
SELECT
SUBSTR(product_id, 3, 2)
FROM price
WHERE manufacturer_id = 59604;
(*)
19. You
query the database with this SQL statement:
SELECT LOWER(SUBSTR(CONCAT(last_name, first_name)), 1, 5)
"ID"
FROM employee;
In which order are the functions evaluated?
Mark for Review
(1) Points
LOWER,
CONCAT, SUBSTR
CONCAT,
SUBSTR, LOWER (*)
LOWER,
SUBSTR, CONCAT
SUBSTR,
CONCAT, LOWER
Section
5
(Answer
all questions in this section)
20. CASE
and DECODE evaluate expressions in a similar way to IF-THEN-ELSE logic.
However, DECODE is specific to Oracle syntax. True or False? Mark for Review
(1) Points
True
(*)
False
Section 5
(Answer
all questions in this section)
21. When
executed, which statement displays a zero if the TUITION_BALANCE value is zero
and the HOUSING_BALANCE value is null?
Mark for Review
(1) Points
SELECT
NVL (tuition_balance + housing_balance, 0) "Balance Due"
FROM student_accounts;
(*)
SELECT
TO_NUMBER(tuition_balance, 0), TO_NUMBER (housing_balance, 0), tutition_balance
+ housing_balance "Balance Due"
FROM student_accounts;
SELECT
NVL(tuition_balance, 0), NVL (housing_balance), tuition_balance + housing_balance
"Balance Due"
FROM student_accounts;
SELECT
tuition_balance + housing_balance
FROM student_accounts;
22. You
need to replace null values in the DEPT_ID column with the text N/A. Which
functions should you use?
Mark for Review
(1) Points
TO_CHAR
and NULLIF
TO_NUMBER
and NULLIF
TO_CHAR
and NVL (*)
TO_CHAR
and NULL
23. If you
use the RR format when writing a query using the date 27-Oct-17 and the year is
2001, what year would be the result?
Mark for Review
(1) Points
1917
1901
2017
(*)
2001
24. The
EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2 (25)
FIRST_NAME VARCHAR2 (25)
SALARY NUMBER(6)
You need to create a report to display the salaries of
all employees. Which SQL Statement should you use to display the salaries in
format: "$45,000.00"?
Mark for Review
(1) Points
SELECT
TO_NUM(salary, '$999,999.00')
FROM employees;
SELECT
TO_CHAR(salary, '$999,999')
FROM employees;
SELECT
TO_NUM(salary, '$999,990.99')
FROM employees;
SELECT
TO_CHAR(salary, '$999,999.00')
FROM employees;
(*)
25. Which
best describes the TO_CHAR function?
Mark for Review
(1) Points
The
TO_CHAR function can be used to remove text from column data that will be
returned by the database.
The
TO_CHAR function can be used to display dates and numbers according to
formatting conventions that are supported by Oracle. (*)
The
TO_CHAR function can be used to specify meaningful column names in an SQL
statement's result set.
The
TO_CHAR function can only be used on Date columns.
Section 6
(Answer
all questions in this section)
26. Which
SELECT statement implements a self join?
Mark for Review
(1) Points
SELECT
p.part_id, t.product_id
FROM part p, product t
WHERE p.part_id = t.product_id (+);
SELECT
p.part_id, t.product_id
FROM part p, product t
WHERE p.part_id = t.product_id;
SELECT
p.part_id, t.product_id
FROM part p, product t
WHERE p.part_id =! t.product_id;
SELECT
p.part_id, t.product_id
FROM part p, part t
WHERE p.part_id = t.product_id;
(*)
27. Hierarchical
queries MUST use the LEVEL pseudo column. True or False? Mark for Review
(1) Points
True
False
(*)
28. Below
find the structures of the PRODUCTS and VENDORS tables:
PRODUCTS
PRODUCT_ID NUMBER
PRODUCT_NAME VARCHAR2 (25)
VENDOR_ID NUMBER
CATEGORY_ID NUMBER
VENDORS
VENDOR_ID NUMBER
VENDOR_NAME VARCHAR2 (25)
ADDRESS VARCHAR2 (30)
CITY VARCHAR2 (25)
REGION VARCHAR2 (10)
POSTAL_CODE VARCHAR2 (11)
You want to create a query that will return an
alphabetical list of products, including the product name and associated vendor
name, for all products that have a vendor assigned.
Which two queries could you use?
Mark for Review
(1) Points
(Choose
all correct answers)
SELECT
p.product_name, v.vendor_name
FROM products p
JOIN vendors v
USING (vendor_id)
ORDER BY p.product_name;
(*)
SELECT
p.product_name, v.vendor_name
FROM products p
LEFT OUTER JOIN vendors v
ON p.vendor_id = v.vendor_id
ORDER BY p.product_name;
SELECT
p.product_name, v.vendor_name
FROM products p
JOIN vendors v
USING (p.vendor_id)
ORDER BY p.product_name;
SELECT
p.product_name, v.vendor_name
FROM products p
JOIN vendors v
ON (vendor_id)
ORDER BY p.product_name;
SELECT
p.product_name, v.vendor_name
FROM products p
NATURAL JOIN vendors v
ORDER BY p.product_name;
(*)
29. You
need to join all the rows in the EMPLOYEES table to all the rows in the
EMP_REFERENCE table. Which type of join should you create? Mark for Review
(1) Points
A
cross join (*)
An
inner join
An
equijoin
A
full outer join
30. Which
of the following conditions will cause an error on a NATURAL JOIN? Mark for Review
(1) Points
If
it selects rows from the two tables that have equal values in all matched
columns.
When
you attempt to write it as an equijoin.
If
the columns having the same names have different data types, then an error is
returned. (*)
When
the NATURAL JOIN clause is based on all columns in the two tables that have the
same name.
Section 6
(Answer
all questions in this section)
31. What is
another name for a simple join or an inner join? Mark for Review
(1) Points
Equijoin
(*)
Outer
Join
Self
Join
Nonequijoin
32. The
following statement is an example of what kind of join?
SELECT car.vehicle_id, driver.name
FROM car
LEFT OUTER JOIN driver ON (driver_id) ;
Mark for Review
(1) Points
Equijoin
Outer
Join (*)
Inner
Join
Optimal
Join
Section 7
(Answer
all questions in this section)
33. Will
the following statement work?
SELECT department_name, last_name
FROM employees, departments
WHERE department_id = department_id;
Mark for Review
(1) Points
No,
Oracle will not allow joins in the WHERE clause
Yes,
Oracle will resolve which department_id colum comes from which table.
No,
Oracle will return a Column Ambiguously Defined error. (*)
Yes,
there are no syntax errors in that statement
34. When
joining 3 tables in a SELECT statement, how many join conditions are needed in
the WHERE clause? Mark for
Review
(1) Points
3
0
2
(*)
1
35. Evaluate
this SELECT statement:
SELECT p.player_id, m.last_name, m.first_name,
t.team_name
FROM player p
LEFT OUTER JOIN player m ON (p.manager_id = m.player_id)
LEFT OUTER JOIN team t ON (p.team_id = t.team_id);
Which join is evaluated first?
Mark for Review
(1) Points
The
join between the player table and the team table on TEAM_ID
The
join between the player table and the team table on MANAGER_ID
The
join between the player table and the team table on PLAYER_ID
The
self-join of the player table (*)
Section 8
(Answer
all questions in this section)
36. Evaluate
this SELECT statement:
SELECT COUNT(*)
FROM products;
Which statement is true?
Mark for Review
(1) Points
The
number of unique PRODUCT_IDs in the table is displayed.
An
error occurs due to an error in the SELECT clause.
The
number of rows in the table is displayed. (*)
An
error occurs because no WHERE clause is included in the SELECT statement.
37. The
VENDORS table contains these columns:
VENDOR_ID NUMBER Primary Key
NAME VARCHAR2(30)
LOCATION_ID NUMBER
ORDER_DT DATE
ORDER_AMOUNT NUMBER(8,2)
Which two clauses represent valid uses of aggregate
functions for this table?
Mark for Review
(1) Points
(Choose
all correct answers)
SELECT
SUM(order_dt)
SELECT
MIN(AVG(order_amount)) (*)
WHERE
MAX(order_dt) = order_dt
FROM
MAX(order_dt)
SELECT
SUM(order_amount) (*)
38. Which
group function would you use to display the highest salary value in the
EMPLOYEES table? Mark for
Review
(1) Points
COUNT
MIN
MAX (*)
AVG
Section 9
(Answer
all questions in this section)
39. The
difference between UNION and UNION ALL is
Mark for Review
(1) Points
UNION
will remove duplicates; UNION ALL returns all rows from all queries including
the duplicates. (*)
UNION
is a synomym for UNION ALL.
UNION
ALL is more like a NATURAL JOIN.
There
is no difference; you get exactly the same result from both.
40. Examine
the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS(.......);
Select the correct GROUP BY GROUPING SETS clause from the
following list:
Mark for Review
(1) Points
GROUP
BY GROUPING SETS (department_id, salary), (department_id, job_id),
(department_id, manager_id)
GROUP
BY GROUPING SETS ((department_id, manager_id), (department_id, SUM(salary),
(manager_id, job_id))
GROUP
BY GROUPING SETS (department_id, AVG(salary)), (department_id, job_id),
(department_id, manager_id)
GROUP
BY GROUPING SETS ((department_id, manager_id), (department_id, job_id),
(manager_id, job_id)) (*)
Section 9
(Answer
all questions in this section)
41. CUBE
can be applied to all aggregate functions including AVG, SUM, MIN, MAX, and
COUNT. True or False? Mark
for Review
(1) Points
True
(*)
False
42. Group functions
can be nested to a depth of? Mark for
Review
(1) Points
Group
functions cannot be nested.
Two
(*)
Three
Four
43. Evaluate
this SELECT statement:
SELECT MIN(hire_date), department_id
FROM employees
GROUP BY department_id;
Which values are displayed?
Mark for Review
(1) Points
The
latest hire date in the EMPLOYEES table
The
earliest hire date in the EMPLOYEES table
The
earliest hire date in each department (*)
The
hire dates in the EMPLOYEES table that contain NULL values
44. Which
statement about the GROUP BY clause is true?
Mark for Review
(1) Points
By
default, rows are not sorted when a GROUP BY clause is used.
To
exclude rows before dividing them into groups using the GROUP BY clause, you
should use a WHERE clause. (*)
You
must use the HAVING clause with the GROUP BY clause.
You
can use a column alias in a GROUP BY clause.
Section 10
(Answer
all questions in this section)
45. Which
of the following best describes the meaning of the ANY operator? Mark for Review
(1) Points
Equal
to any member in the list
Compare
value to each value returned by the subquery (*)
Equal
to each value in the list
Compare
value to the first value returned by the subquery
Section 10
(Answer
all questions in this section)
46. Which statement
about the ANY operator, when used with a multiple-row subquery, is true? Mark for Review
(1) Points
The
ANY operator can be used with the DISTINCT keyword.
The
ANY operator can be used with the LIKE and IN operators.
The
ANY operator is a synonym for the ALL operator.
The
ANY operator compares every value returned by the subquery. (*)
47. Evaluate
this SELECT statement:
SELECT player_id, name
FROM players
WHERE team_id IN
(SELECT team_id
FROM teams
WHERE team_id
> 300 AND salary_cap > 400000);
What would happen if the inner query returned a NULL
value?
Mark for Review
(1) Points
No
rows would be returned by the outer query. (*)
A
syntax error in the inner query would be returned.
A
syntax error in the outer query would be returned.
All
the rows in the PLAYER table would be returned by the outer query.
48. Which
comparison operator can only be used with a single-row subquery? Mark for Review
(1) Points
ALL
ANY
<>
(*)
IN
49. Which
operator can be used with a multiple-row subquery? Mark for Review
(1) Points
IN
(*)
<>
=
LIKE
50. Table
aliases must be used when you are writing correlated subqueries. (True or
false?) Mark for Review
(1) Points
True
False
(*)
You want to create a list of all albums that have been produced by the company. The list should include the title of the album, the artist's name, and the date the album was released. The ALBUMS table includes the following columns:
ReplyDeleteALB_TITLE VARCHAR2(150) NOT NULL
ALB_ARTIST VARCHAR2(150) NOT NULL
ALB_DATE DATE NOT NULL
Which statement can you use to retrieve the necessary information?
Mark for Review
(1) Points
SELECT alb_title, alb_artist, alb_dates
FROM album;
SELECT *
FROM albums;
SELECT alb_title; alb_artist; alb_date
FROM albums;*
SELECT alb_title, alb_artist, alb_dates
FROM albums;
A Relational Database generally contains two or more tables. True or False? Mark for Review
ReplyDelete(1) Points
True*
False
The following is a valid SQL SELECT statement. True or False?
ReplyDeleteSELECT first_name || ' ' || last_name alias AS Employee_Name
FROM employees:
Mark for Review
(1) Points
True
False*
You need to display employees whose salary is in the range of 30000 and 50000. Which comparison operator should you use? Mark for Review
ReplyDelete(1) Points
IS NULL
IN
LIKE
BETWEEN...AND...*
Which of the following are examples of comparison operators used in the WHERE clause? Mark for Review
ReplyDelete(1) Points
=, >, <, <=, >=, <>
between ___ and ___
in (..,..,.. )
like
is null
All of the above*
Evaluate this SELECT statement:
ReplyDeleteSELECT last_name, first_name, salary
FROM employees;
How will the heading for the FIRST_NAME column appear in the display by default in Oracle Application Express?
Mark for Review
(1) Points
The heading will display with the first character capitalized and left justified.
The heading will display as uppercase and left justified.
The heading will display with the first character capitalized and centered.
The heading will display as uppercase and centered.*
A column alias can be specified in an ORDER BY Clause. True or False? Mark for Review
ReplyDelete(1) Points
True*
False
Which of the following best describes the meaning of the LIKE operator? Mark for Review
ReplyDelete(1) Points
Match a character pattern.*
To find Null values.
To test for values in a list.
Display rows based on a range of values.
Which query would return a user password combining the ID of an employee and the first 4 digits of the last name? Mark for Review
ReplyDelete(1) Points
SELECT CONCAT (employee_id, SUBSTR(last_name,1,4))
AS "User Passwords"
FROM employees (*)
SELECT CONCAT (employee_id, INSTR(last_name,4,1))
AS "User Passwords"
FROM employees
SELECT CONCAT (employee_id, SUBSTR(last_name,4,1))
AS "User Passwords"
FROM employees
SELECT CONCAT (employee_id, INSTR(last_name,1,4))
AS "User Passwords"
FROM employees
What does the following SQL SELECT statement return?
ReplyDeleteSELECT UPPER( SUBSTR('Database Programming', INSTR('Database Programming','P'),20))
FROM dual;
Mark for Review
(1) Points
Programming
Database
PROGRAMMING (*)
DATABASE
You need to subtract three months from the current date. Which function should you use? Mark for Review
ReplyDelete(1) Points
ROUND
MONTHS_BETWEEN
ADD_MONTHS (*)
TO_DATE
Consider the following data in the Employees table: (last_name, commission_pct, manager_id)
ReplyDeleteDATA:
King, null, null
Kochhar, null, 100
Vargas, null, 124
Zlotkey, .2, 100
What is the result of the following statement:
SELECT last_name, COALESCE(commission_pct, manager_id, -1) comm
FROM employees ;
Mark for Review
(1) Points
King, -1
Kochhar, 100
Vargas, 124
Zlotkey, 100
King, null
Kochhar, 100
Vargas, 124
Zlotkey, .2
Statement will fail
King, -1
Kochhar, 100
Vargas, 124
Zlotkey, .2 (*)
If quantity is a number datatype, what is the result of this statement?
ReplyDeleteSELECT NVL(200/quantity, 'zero') FROM inventory; Mark for Review
(1) Points
zero
ZERO
Null
The statement fails (*)
CASE and DECODE evaluate expressions in a similar way to IF-THEN-ELSE logic. However, DECODE is specific to Oracle syntax. True or False? Mark for Review
ReplyDelete(1) Points
True (*)
False
Which SQL Statement should you use to display the prices in this format: "$00.30"? Mark for Review
ReplyDelete(1) Points
SELECT TO_CHAR(price, '$99,900.99') (*)
FROM product;
SELECT TO_CHAR(price, '$99,990.99')
FROM product;
SELECT TO_NUMBER(price, '$99,900.99')
FROM product;
SELECT TO_CHAR(price, '$99,999.99')
FROM product;
Which statement is true about SQL functions? Mark for Review
ReplyDelete(1) Points
Functions can convert values or text to another data type.
Functions can round a number to a specified decimal place.
Functions can convert upper case characters to lower case characters.
a, b and c are true. (*)
None of the above statements are true.
Which query represents the correct syntax for a left outer join? Mark for Review
ReplyDelete(1) Points
SELECT companyname, orderdate, total
FROM customers c
LEFT OUTER orders o
ON c.cust_id = o.cust_id;
SELECT companyname, orderdate, total
FROM customers c
OUTER JOIN orders o
ON c.cust_id = o.cust_id;
SELECT companyname, orderdate, total
FROM customers c
LEFT OUTER JOIN orders o
ON c.cust_id = o.cust_id; (*)
SELECT companyname, orderdate, total
FROM customers c
LEFT JOIN orders o
ON c.cust_id = o.cust_id;
A join between tables where the result set includes matching values from both tables but does NOT return any unmatched rows could be called which of the following? (Choose three) Mark for Review
ReplyDelete(1) Points
(Choose all correct answers)
Simple join (*)
Full outer join
Nonequijoin
Self join (*)
Equijoin (*)
The join column must be included in the select statement when you use the NATURAL JOIN clause. True or False? Mark for Review
ReplyDelete(1) Points
True
False (*)
You can do nonequi-joins with ANSI-Syntax. True or False? Mark for Review
(1) Points
True (*)
False
Which SELECT statement implements a self join? Mark for Review
ReplyDelete(1) Points
SELECT e.employee_id, m.manager_id
FROM employees e
NATURAL JOIN employees m;
SELECT e.employee_id, m.manager_id
FROM employees e, departments m
WHERE e.employee_id = m.manager_id;
SELECT e.employee_id, m.manager_id
FROM employees e, managers m
WHERE e.employee_id = m.manager_id;
SELECT e.employee_id, m.manager_id
FROM employees e, employees m
WHERE m.employee_id = e.manager_id; (*)
You need to create a report that lists all employees in department 10 (Sales) whose salary is not equal to $25,000 per year. Which query should you issue to accomplish this task? Mark for Review
ReplyDelete(1) Points
SELECT last_name, first_name, salary
FROM employees
WHERE salary <= 25000 AND dept_id = 10;
SELECT last_name, first_name, salary
FROM employees
WHERE salary != 25000 AND dept_id = 10; (*)
SELECT last_name, first_name, salary
FROM employees
WHERE salary > 25000 AND dept_id = 10;
SELECT last_name, first_name, salary
FROM employees
WHERE salary = 25000 AND dept_id = 10;
You have two tables named EMPLOYEES and SALES. You want to identify the sales representatives who have generated at least $100,000 in revenue.
ReplyDeleteWhich query should you issue? Mark for Review
(1) Points
SELECT e.first_name, e.last_name, s.sales
FROM employees e, sales s
WHERE e.employee_id = s.employee_id AND revenue > 100000;
SELECT e.first_name, e.last_name, s.sales
FROM employees e, sales s
WHERE e.employee_id = s.employee_id AND revenue >= 100000; (*)
SELECT e.first_name, e.last_name, s.sales
FROM employees, sales
WHERE e.employee_id = s.employee_id AND revenue >= 100000;
SELECT first_name, last_name, sales
FROM employees e, sales s
WHERE e.employee_id = s.employee_id AND revenue > 100000;
The EMPLOYEES table contains these columns:
ReplyDeleteEMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
SALARY NUMBER(9,2)
HIRE_DATE DATE
BONUS NUMBER(7,2)
COMM_PCT NUMBER(4,2)
Which three functions could be used with the HIRE_DATE, LAST_NAME, or SALARY columns? (Choose three.)
Mark for Review
(1) Points
(Choose all correct answers)
COUNT (*)
AVG
SUM
MAX (*)
MIN (*)
The basic storage structure in a Relational Database is a _________: Mark for Review
ReplyDelete(1) Points
Row
Table (*)
Field
Key
The basic storage structure in a Relational Database is a _________: Mark for Review
ReplyDelete(1) Points
Row
Table (*)
Field
Key
You cannot use computers unless you completely understand exactly how they work. True or False? Mark for Review
ReplyDelete(1) Points
True
False (*)
What command can be used to create a new row in a table in the database? Mark for Review
ReplyDelete(1) Points
NEW
ADD
CREATE
INSERT (*)
Which SQL keyword specifies that an alias will be substituted for a column name in the output of a SQL query? Mark for Review
ReplyDelete(1) Points
AS (*)
AND
SUBSTITUTE
OR
The EMPLOYEES table contains these columns:
ReplyDeleteSALARY NUMBER(7,2)
BONUS NUMBER(7,2)
COMMISSION_PCT NUMBER(2,2)
All three columns contain values greater than zero.
There is one row of data in the table and the values are as follows:
Salary = 500, Bonus = 50, Commission_pct = .5
Evaluate these two SQL statements:
1.
SELECT salary + bonus + commission_pct * salary - bonus AS income
FROM employees;
2.
SELECT (salary + bonus ) + commission_pct * (salary - bonus) income
FROM employees;
What will be the result?
Mark for Review
(1) Points
Statement 1 will display a different column heading.
Statement 2 will return a higher value than statement 1. (*)
One of the statements will NOT execute.
Statement 1 will return a higher value than statement 2.
The concatenation operator ... Mark for Review
ReplyDelete(1) Points
Brings together columns or character strings into other columns
Creates a resultant column that is a character expression
Is represented by two vertical bars ( || )
All of the above (*)
This comment has been removed by the author.
ReplyDeleteWhich of the following elements cannot be included in a WHERE clause? Mark for Review
ReplyDelete(1) Points
A column alias (*)
A column name
A comparison condition
A constant
Which of the following commands will display the last name concatenated with the job ID from the employees table, separated by a comma and space, and label the resulting column "Employee and Title"? Mark for Review
ReplyDelete(1) Points
SELECT " last name" ||', '|| "job_id" + "Employee and Title" FROM emp;
SELECT last_name||', '|| job_id "Employee and Title" FROM employees; (*)
SELECT " last name" ||', '|| "job_id" + "Employee and Title" FROM employees;
SELECT last_name||","|| job_id "Employee and Title" FROM employees;
Which example would limit the number of rows returned? Mark for Review
ReplyDelete(1) Points
SELECT title FROM d_songs WHERE type_code = = 88;
SELECT title FROM d_songs WHERE type_code = 88; (*)
SELECT title FROM d_songs WHEN type_code = 88;
SELECT title FROM d_songs WHEN type_code = = 88;
Which of the following are examples of comparison operators used in the WHERE clause? Mark for Review
ReplyDelete(1) Points
=, >, <, <=, >=, <>
between ___ and ___
in (..,..,.. )
like
is null
All of the above (*)
Which two statements would select salaries that are greater than or equal to 2500 and less than or equal to 3500? (Choose two) Mark for Review
ReplyDelete(1) Points
(Choose all correct answers)
WHERE salary >= 2500 AND salary <= 3500 (*)
WHERE salary <=2500 AND salary >= 3500
WHERE salary BETWEEN 3500 AND 2500
WHERE salary BETWEEN 2500 AND 3500 (*)
The function COUNT is a single row function. True or False? Mark for Review
ReplyDelete(1) Points
True
False (*)
Which of the following would be returned by this SQL statement:
ReplyDeleteSELECT First_name, last_name, department_id
FROM employees
WHERE department_id IN(50,80)
AND first_name LIKE ' C% '
OR last_name LIKE ' %s% '
Mark for Review
(1) Points
FIRST_NAME LAST_NAME DEPARTMENT_ID
Shelly Higgins 110
FIRST_NAME LAST_NAME DEPARTMENT_ID
Curtis Davies 50
FIRST_NAME LAST_NAME DEPARTMENT_ID
Randall Matos 50
FIRST_NAME LAST_NAME DEPARTMENT_ID
Michael Hartstein 20
All of the above (*)
What clause must you place in a SQL statement to have your results sorted from highest to lowest salary? Mark for Review
ReplyDelete(1) Points
ORDER BY salary ASC
None, the database always sorts from highest to lowest on the salary column.
ORDER BY salary DESC (*)
ORDER salary BY DESC
Evaluate this SELECT statement:
ReplyDeleteSELECT employee_id, last_name, first_name, salary 'Yearly Salary'
FROM employees
WHERE salary IS NOT NULL
ORDER BY last_name, 3;
Which clause contains an error?
Mark for Review
(1) Points
SELECT employee_id, last_name, first_name, salary 'Yearly Salary' (*)
FROM employees
WHERE salary IS NOT NULL
ORDER BY last_name, 3;
What is the result of the following query?
ReplyDeleteSELECT ADD_MONTHS ('11-Jan-1994',6)
FROM dual; Mark for Review
(1) Points
17-Jan-1994
11-Jul-1994 (*)
17-Jul-1994
11-Jan-1995
Character functions accept character arguments and only return character values. True or False? Mark for Review
ReplyDelete(1) Points
True
False (*)
Which of the following SQL statements would correctly return a song title identified in the database as "All These Years"? Mark for Review
ReplyDelete(1) Points
WHERE title IN('All','These','Years');
WHERE title CONTAINS 'Years';
WHERE title LIKE INITCAP('%all these years'); (*)
WHERE title LIKE LOWER('all these years');
You issue this SQL statement:
ReplyDeleteSELECT TRUNC(751.367,-1) FROM dual;
Which value does this statement display?
Mark for Review
(1) Points
751
700
751.3
750 (*)
Which of the following is a conditional expression used in SQL? Mark for Review
ReplyDelete(1) Points
NULLIF
CASE (*)
DESCRIBE
WHERE
Which of the following is a conditional expression used in SQL? Mark for Review
ReplyDelete(1) Points
NULLIF
CASE (*)
DESCRIBE
WHERE
Which of the following General Functions will return the first non-null expression in the expression list? Mark for Review
ReplyDelete(1) Points
NVL2
NULLIF
COALESCE (*)
NVL
The STYLES table contains this data:
ReplyDeleteSTYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85940 12.00
968950 SANDAL 85909 10.00
869506 SANDAL 89690 15.00
809090 LOAFER 89098 10.00
890890 LOAFER 89789 14.00
857689 HEEL 85940 11.00
758960 SANDAL 86979
Evaluate this SELECT statement:
SELECT style_id, style_name, category, cost
FROM styles
WHERE style_name LIKE 'SANDAL' AND NVL(cost, 0) < 15.00
ORDER BY category, cost;
Which result will the query provide?
Mark for Review
(1) Points
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85909 12.00
968950 SANDAL 85909 10.00
869506 SANDAL 89690 15.00
758960 SANDAL 86979
STYLE_ID STYLE_NAME CATEGORY COST
968950 SANDAL 85909 10.00
895840 SANDAL 85940 12.00
758960 SANDAL 86979
(*)
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85909 12.00
968950 SANDAL 85909 10.00
758960 SANDAL 86979
869506 SANDAL 89690 15.00
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85940 12.00
968950 SANDAL 85909 10.00
758960 SANDAL 86979
Which two statements concerning SQL functions are true? (Choose two.) Mark for Review
ReplyDelete(1) Points
(Choose all correct answers)
Conversion functions convert a value from one data type to another data type. (*)
Single-row functions manipulate groups of rows to return one result per group of rows.
Not all date functions return date values. (*)
Character functions can accept numeric input.
Number functions can return number or character values.
If you use the RR format when writing a query using the date 27-Oct-17 and the year is 2001, what year would be the result? Mark for Review
ReplyDelete(1) Points
1917
2001
1901
2017 (*)
Sysdate is 12-May-2004.
ReplyDeleteYou need to store the following date: 7-Dec-89
Which statement about the date format for this value is true? Mark for Review
(1) Points
The RR date format will interpret the year as 1989, and the YY date format will interpret the year as 2089 (*)
The RR date format will interpret the year as 2089, and the YY date format will interpret the year as 1989
Both the YY and RR date formats will interpret the year as 2089
Both the YY and RR date formats will interpret the year as 1989
Which statement about a self join is true? Mark for Review
ReplyDelete(1) Points
A self join must be implemented by defining a view.
The NATURAL JOIN clause must be used.
Table aliases must be used to qualify table names. (*)
Table aliases cannot be used to qualify table names.
Given the following descriptions of the employees and jobs tables, which of the following scripts will display each employeeï¾’s possible minimum and maximum salaries based on their job title?
ReplyDeleteEMPLOYEES Table:
Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER (6)
FIRST_NAME VARCHAR2 (20)
LAST_NAME NOT NULL VARCHAR2 (25)
EMAIL NOT NULL VARCHAR2 (25)
PHONE_NUMBER VARCHAR2 (20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2 (10)
SALARY NUMBER (8,2)
COMMISSION_PCT NUMBER (2,2)
MANAGER_ID NUMBER (6)
DEPARTMENT_ID NUMBER (4)
JOBS Table:
Name Null? Type
JOB_ID NOT NULL VARCHAR2 (10)
JOB_TITLE NOT NULL VARCHAR2 (35)
MIN_SALARY NUMBER (6)
MAX_SALARY NUMBER (6)
Mark for Review
(1) Points
SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
FROM employees e
NATURAL JOIN jobs j;
SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
FROM employees e
NATURAL JOIN jobs j
USING (job_id);
SELECT first_name, last_name, job_id, min_salary, max_salary
FROM employees
NATURAL JOIN jobs;
(*)
SELECT first_name, last_name, job_id, min_salary, max_salary
FROM employees e
FULL JOIN jobs j (job_id);
SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
FROM employees e
NATURAL JOIN jobs j ON (e.job_title = j.job_title);
Evaluate this SELECT statement:
ReplyDeleteSELECT a.lname || ', ' || a.fname as "Patient", b.lname || ', ' || b.fname as "Physician", c.admission
FROM patient a
JOIN physician b
ON (b.physician_id = c.physician_id)
JOIN admission c
ON (a.patient_id = c.patient_id);
Which clause generates an error?
Mark for Review
(1) Points
ON (b.physician_id = c.physician_id); (*)
ON (a.patient_id = c.patient_id)
JOIN physician b
JOIN admission c
The join column must be included in the select statement when you use the NATURAL JOIN clause. True or False? Mark for Review
ReplyDelete(1) Points
True
False (*)
Which statement about a natural join is true? Mark for Review
ReplyDelete(1) Points
Columns with the same names must have identical data types.
Columns with the same names must have compatible data types.
Columns with the same names cannot be included in the SELECT list of the query.
Columns with the same names must have the same precision and datatype. (*)
Which operator is typically used in a nonequijoin? Mark for Review
ReplyDelete(1) Points
>=, <=, or BETWEEN ...AND (*)
NOT
IN
OR
*
You have two tables named EMPLOYEES and SALES. You want to identify the sales representatives who have generated at least $100,000 in revenue.
ReplyDeleteWhich query should you issue? Mark for Review
(1) Points
SELECT e.first_name, e.last_name, s.sales
FROM employees, sales
WHERE e.employee_id = s.employee_id AND revenue >= 100000;
SELECT e.first_name, e.last_name, s.sales
FROM employees e, sales s
WHERE e.employee_id = s.employee_id AND revenue > 100000;
SELECT first_name, last_name, sales
FROM employees e, sales s
WHERE e.employee_id = s.employee_id AND revenue > 100000;
SELECT e.first_name, e.last_name, s.sales
FROM employees e, sales s
WHERE e.employee_id = s.employee_id AND revenue >= 100000;
(*)
What happens when you create a Cartesian product? Mark for Review
ReplyDelete(1) Points
The table is joined to another equal table
All rows that do not match in the WHERE clause are displayed
All rows from one table are joined to all rows of another table (*)
The table is joined to itself, one column to the next column, exhausting all possibilities
Given the following data in the employees table (employee_id, salary, commission_pct)
ReplyDeleteDATA: (143, 2600, null
144, 2500, null
149, 10500, .2
174, 11000, .3
176, 8600, .2
178, 7000, .15)
What is the result of the following statement:
SELECT SUM(commission_pct), COUNT(salary)
FROM employees
WHERE employee_id IN( 143,144,149,174,176,178);
Mark for Review
(1) Points
SUM = .85 and COUNT = 4
SUM = .85 and COUNT = 6 (*)
SUM = 1.85 and COUNT =4
SUM = 1.85 and COUNT = 6
The following statement will work, even though it contains more than one GROUP function:
ReplyDeleteSELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
FROM employees;
True or False?
Mark for Review
(1) Points
True (*)
False
Evaluate this SELECT statement:
ReplyDeleteSELECT COUNT(*)
FROM employees
WHERE salary > 30000;
Which result will the query display?
Mark for Review
(1) Points
The total of the SALARY column for all employees that have a salary greater than 30000
The query generates an error and returns no results.
The number of employees that have a salary less than 30000
The number of rows in the EMPLOYEES table that have a salary greater than 30000 (*)
When using SET operators, the number of columns and the data types of the columns must be identical in all of the SELECT statements used in the query. True or False. Mark for Review
ReplyDelete(1) Points
True (*)
False
The PRODUCTS table contains these columns:
ReplyDeletePRODUCT_ID NUMBER(9) PK
CATEGORY_ID VARCHAR2(10)
LOCATION_ID NUMBER(9)
DESCRIPTION VARCHAR2(30)
COST NUMBER(7,2)
PRICE NUMBER(7,2)
QUANTITY NUMBER
You display the total of the extended costs for each product category by location.
You need to include only the products that have a price less than $25.00.
The extended cost of each item equals the quantity value multiplied by the cost value.
Which SQL statement will display the desired result?
Mark for Review
(1) Points
SELECT SUM(cost * quantity) TOTAL, location_id
FROM products
WHERE price < 25.00
GROUP BY location_id;
SELECT category_id, SUM(cost * quantity) TOTAL, location_id
FROM products
WHERE price < 25.00
GROUP BY category_id, location_id;
(*)
SELECT category_id, SUM(cost * quantity) TOTAL,location_id
FROM products
WHERE price > 25.00
GROUP BY category_id, location_id;
SELECT SUM(cost * quantity) TOTAL
FROM products
WHERE price < 25.00;
If a select list contains both a column as well as a group function then what clause is required? Mark for Review
ReplyDelete(1) Points
ORDER BY clause
GROUP BY clause (*)
JOIN clause
HAVING clause
Read the following SELECT statement. Choose the column or columns that must be included in the GROUP BY clause.
ReplyDeleteSELECT COUNT(last_name), grade, gender
FROM STUDENTS
GROUP_BY ?????;
Mark for Review
(1) Points
last_name, grade
last_name
last_name, gender
grade, gender (*)
Examine the following statement:
ReplyDeleteSELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS(.......);
Select the correct GROUP BY GROUPING SETS clause from the following list:
Mark for Review
(1) Points
GROUP BY GROUPING SETS (department_id, AVG(salary)), (department_id, job_id), (department_id, manager_id)
GROUP BY GROUPING SETS (department_id, salary), (department_id, job_id), (department_id, manager_id)
GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, job_id), (manager_id, job_id)) (*)
GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, SUM(salary), (manager_id, job_id))
If you want to include subtotals and grand totals for all columns mentioned in a GROUP BY clause, you should use which of the following extensions to the GROUP BY clause? Mark for Review
ReplyDelete(1) Points
ROLLUP
CUBE (*)
HAVING
GROUP BY ALL COLUMNS
What would happen if you attempted to use a single-row operator with a multiple-row subquery? Mark for Review
ReplyDelete(1) Points
No rows will be selected.
The data returned may or may not be correct.
All the rows will be selected.
An error would be returned. (*)
Examine the structures of the PARTS and MANUFACTURERS tables:
ReplyDeletePARTS:
PARTS_ID VARCHAR2(25) PK
PARTS_NAME VARCHAR2(50)
MANUFACTURERS_ID NUMBER
COST NUMBER(5,2)
PRICE NUMBER(5,2)
MANUFACTURERS:
ID NUMBER PK
NAME VARCHAR2(30)
LOCATION VARCHAR2(20)
Assume that the tables have been populated with data including 100 rows in the PARTS table, and 20 rows in the MANUFACTURERS table. Which SQL statement correctly uses a subquery?
Mark for Review
(1) Points
SELECT parts_name, price, cost
FROM parts
WHERE manufacturers_id IN
(SELECT id
FROM manufacturers m
JOIN parts p
ON (m.id = p.manufacturers_id));
(*)
SELECT parts_name
FROM (SELECT AVG(cost) FROM manufacturers)
WHERE cost > AVG(cost);
SELECT parts_name, price, cost
FROM parts
WHERE manufacturers_id !=
(SELECT id
FROM manufacturers
WHERE LOWER(name) = 'cost plus');
UPDATE parts SET price = price * 1.15
WHERE manufacturers_id =
(SELECT id
FROM manufacturers
WHERE UPPER(location) IN("ATLANTA", "BOSTON", "DALLAS"));
When a multiple-row subquery uses the NOT IN operator (equivalent to <>ALL), if one of the values returned by the inner query is a null value, the entire query returns: Mark for Review
ReplyDelete(1) Points
No rows returned (*)
All rows that were selected by the inner query including the null values
A list of Nulls
All rows that were selected by the inner query minus the null values
Which of the following is TRUE regarding the order of subquery execution? Mark for Review
ReplyDelete(1) Points
The subquery executes once after the main query.
The subquery executes once before the main query. (*)
The result of the main query is used with the subquery.
The outer query is executed first.
If the subquery returns no rows, will the outer query return any values? Mark for Review
ReplyDelete(1) Points
Yes, Oracle will find the nearest value and rewrite your statement implicitly when you run it.
Yes. It will just run and ignore the subquery.
No, because you are not allowed to return empty values from a subquery.
No, because the subquery will be treated like a null value. (*)
What command will return data from the database to you?
ReplyDeleteMark for Review
(1) Points
GET
SELECT (*)
RETURN
FETCH
In which clause of a SELECT statement would you specify the name of the table or tables being queried?
ReplyDeleteMark for Review
(1) Points
The FROM clause (*)
The SELECT clause
The WHERE clause
Any of the above options; you can list tables wherever you want in a SELECT statement.
Evaluate this SELECT statement:
ReplyDeleteSELECT (salary * raise_percent) raise
FROM employees;
If the RAISE_PERCENT column only contains null values, what will the statement return?
Mark for Review
(1) Points
A null value or a zero depending on the value of the SALARY column
Only null values (*)
A null value or a numeric value depending on the value of the SALARY column
Only zeroes
If you write queries using the BETWEEN operator, it does not matter in what order you enter the values, i.e. BETWEEN low value AND high value will give the same result as BETWEEN high value and low value. True or False?
ReplyDeleteMark for Review
(1) Points
True
False (*)
The Concatenation Operator does which of the following?
ReplyDeleteMark for Review
(1) Points
Links rows of data together inside the database.
Separates columns.
Is represented by the asterisk (*) symbol
Links two or more columns or literals to form a single output column (*)
Which clause would you include in a SELECT statement to restrict the data returned to only the employees in department 10?
ReplyDeleteMark for Review
(1) Points
FROM
IS
SELECT
WHERE (*)
You need to display only unique combinations of the LAST_NAME and MANAGER_ID columns in the EMPLOYEES table. Which keyword should you include in the SELECT clause?
ReplyDeleteMark for Review
(1) Points
DISTINCTROW
DISTINCT (*)
UNIQUEONE
ONLY
To restrict the rows returned from an SQL Query, you should use the _____ clause:
ReplyDeleteMark for Review
(1) Points
SELECT
WHERE (*)
GROUP BY
CONDITION
All of the Above
You need to change the default sort order of the ORDER BY clause so that the data is displayed in reverse alphabetical order. Which keyword should you include in the ORDER BY clause?
ReplyDeleteMark for Review
(1) Points
SORT
CHANGE
DESC (*)
ASC
Which statement about the ORDER BY clause is true?
ReplyDeleteMark for Review
(1) Points
The ORDER BY clause can only contain columns that are included in the SELECT list.
The ORDER BY clause should immediately precede the FROM clause in a SELECT statement
You can use a column alias in the ORDER BY clause. (*)
The default sort order of the ORDER BY clause is descending.
Which of the following are examples of logical operators that might be used in a WHERE clause. (Choose Two)
ReplyDeleteMark for Review
(1) Points
AND, OR (*)
< >, =, <=, >=, <>
NOT (*)
LIKES
All of the above
The following statement represents a multi-row function. True or False?
ReplyDeleteSELECT UPPER(last_name)
FROM employees;
Mark for Review
(1) Points
True
False (*)
Evaluate this SQL statement:
ReplyDeleteSELECT e.employee_id, e.last_name, e.first_name, m.manager_id
FROM employees e, employees m
ORDER BY e.last_name, e.first_name
WHERE e.employee_id = m.manager_id;
This statement fails when executed. Which change will correct the problem?
Mark for Review
(1) Points
Include a SORT clause.
Reorder the clauses in the query. (*)
Remove the table aliases in the ORDER BY clause.
Remove the table aliases in the WHERE clause.
The PLAYERS table contains these columns:
ReplyDeletePLAYERS TABLE:
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
SALARY NUMBER(8,2)
TEAM_ID NUMBER(4)
MANAGER_ID NUMBER(9)
POSITION_ID NUMBER(4)
You want to display all players' names with position 6900 or greater.
You want the players names to be displayed alphabetically by last name and then by first name.
Which statement should you use to achieve the required results?
Mark for Review
(1) Points
SELECT last_name, first_name
FROM players
WHERE position_id <= 6900
ORDER BY last_name, first_name;
SELECT last_name, first_name
FROM players
WHERE position_id >= 6900
ORDER BY last_name DESC, first_name;
SELECT last_name, first_name
FROM players
WHERE position_id >= 6900
ORDER BY last_name, first_name; (*)
SELECT last_name, first_name
FROM players
WHERE position_id > 6900
ORDER BY last_name, first_name;
Which of the following is true of the ORDER BY clause:? (Choose Two)
ReplyDeleteMark for Review
(1) Points
Must be the last clause of the SQL statement (*)
Displays the fetched rows in no particular order
Defaults to a descending order (DESC)
Defaults to an ascending order (ASC) (*)
Evaluate this SELECT statement:
ReplyDeleteSELECT *
FROM employees
WHERE department_id = 34
OR department_id = 45
OR department_id = 67;
Which operator is the equivalent of the OR conditions used in this SELECT statement?
Mark for Review
(1) Points
IN (*)
AND
LIKE
BETWEEN AND ...
Evaluate this SELECT statement:
ReplyDeleteSELECT last_name, first_name, department_id, manager_id
FROM employees;
You need to sort data by manager id values and then alphabetically by employee last name and first name values. Which ORDER BY clause could you use?
Mark for Review
(1) Points
ORDER BY manager_id, last_name, first_name (*)
ORDER BY manager_id, first_name, last_name
ORDER BY last_name, first_name, manager_id
ORDER BY department_id, last_name
The PLAYERS table contains these columns:
ReplyDeletePLAYERS TABLE:
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
SALARY NUMBER(8,2)
TEAM_ID NUMBER(4)
MANAGER_ID NUMBER(9)
POSITION_ID NUMBER(4)
You must display the player name, team id, and salary for players whose salary is in the range from 25000 through 100000 and whose team id is in the range of 1200 through 1500. The results must be sorted by team id from lowest to highest and then further sorted by salary from highest to lowest. Which statement should you use to display the desired result?
Mark for Review
(1) Points
SELECT last_name, first_name, team_id, salary
FROM players
WHERE (salary > 25000 OR salary < 100000)
AND team_id BETWEEN 1200 AND 1500
ORDER BY team_id, salary;
SELECT last_name, first_name, team_id, salary
FROM players
WHERE salary BETWEEN 25000 AND 100000
AND team_id BETWEEN 1200 AND 1500
ORDER BY team_id, salary DESC; (*)
SELECT last_name, first_name, team_id, salary
FROM players
WHERE salary > 24999.99 AND salary < 100000
AND team_id BETWEEN 1200 AND 1500
ORDER BY team_id ASC, salary DESC;
SELECT last_name, first_name, team_id, salary
FROM players
WHERE salary BETWEEN 24999.99 AND 100000.01
AND team_id BETWEEN 1200 AND 1500
ORDER BY team_id DESC, salary DESC;
Which of the following statements best describes the rules of precedence when using SQL?
ReplyDeleteMark for Review
(1) Points
The order in which the columns are displayed
The order in which the expressions are sorted
The order in which the operators are returned
The order in which the expressions are evaluated and calculated (*)
All of the above
Which statement about the default sort order is true?
ReplyDeleteMark for Review
(1) Points
Character values are displayed in reverse alphabetical order.
The earliest date values are displayed first. (*)
The lowest numeric values are displayed last.
Null values are displayed first.