Quiz
QUIZ 7
Create a free account to finish this quiz
Save your progress and see your final score.
Already have an account? Sign in
Questions in this quiz (15)
1If table A has 10 rows and table B has 5 rows, how many rows will be returned if you perform a cartesian join on those two tables?
- A50
- B10
- C5
- D15
2If table A has 10 rows and table B has 5 rows, how many rows will be returned if you perform a equi-join on those two tables?
- A5
- B10
- CIt depends on how many rows have matching data in each of the two tables.
- D50
3You have two tables named EMPLOYEES and SALES. You want to identify the sales representatives who have generated at least $100,000 in revenue.
Which query should you issue?- ASELECT e.first_name, e.last_name, s.sales
FROM employees e, sales s
WHERE e.employee_id = s.employee_id AND revenue >= 100000; - BSELECT e.first_name, e.last_name, s.sales
FROM employees, sales
WHERE e.employee_id = s.employee_id AND revenue >= 100000; - CSELECT e.first_name, e.last_name, s.sales
FROM employees e, sales s
WHERE e.employee_id = s.employee_id AND revenue > 100000; - DSELECT first_name, last_name, sales
FROM employees e, sales s
WHERE e.employee_id = s.employee_id AND revenue > 100000;
- ASELECT e.first_name, e.last_name, s.sales
4You need to provide a list of the first and last names of all employees who work in the Sales department who earned a bonus and had sales over $50,000. The company president would like the sales listed starting with the highest amount first. The EMPLOYEES table and the SALES_DEPT table contain the following columns:
EMPLOYEES
EMP_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPTARTMENT_ID VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(10)
SALES_DEPT
SALES_ID NUMBER(10) PRIMARY KEY
SALES NUMBER(20)
QUOTA NUMBER(20)
MANAGER VARCHAR2(30)
BONUS NUMBER(10)
EMPLOYEE_ID NUMBER(10) FOREIGN KEY
Which SELECT statement will accomplish this task?- ASELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s.sales
FROM employees e, sales_dept s
ORDER BY sales DESC
WHERE e.employee_id = s.employee_id AND sales > 50000 AND s.bonus IS NOT NULL; - BSELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s. sales
WHERE e.employee_id = s.employee_id
FROM employees e, sales_dept s AND s.bonus IS NOT NULL AND sales > 50000
ORDER BY sales DESC; - CSELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s. sales
FROM employees e, sales_dept s
WHERE e.employee_id = s.employee_id AND s.bonus IS NOT NULL AND sales > 50000
ORDER BY sales DESC; - DSELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s. sales
ORDER BY sales DESC
FROM employees e, sales_dept s
WHERE e.employee_id = s.employee_id AND s.bonus IS NOT NULL AND sales > 50000;
- ASELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s.sales
5The CUSTOMERS and SALES tables contain these columns:
CUSTOMERS
CUST_ID NUMBER(10) PRIMARY KEY
COMPANY VARCHAR2(30)
LOCATION VARCHAR2(20)
SALES
SALES_ID NUMBER(5) PRIMARY KEY
CUST_ID NUMBER(10) FOREIGN KEY
TOTAL_SALES NUMBER(30)
Which SELECT statement will return the customer ID, the company and the total sales?- ASELECT cust_id, company, total_sales
FROM customers c, sales s
WHERE c.cust_id = s.cust_id; - BSELECT c.cust_id, c.company, s.total_sales
FROM customers c, sales s
WHERE c.cust_id = s.cust_id (+); - CSELECT cust_id, company, total_sales
FROM customers, sales
WHERE cust_id = cust_id; - DSELECT c.cust_id, c.company, s.total_sales
FROM customers c, sales s
WHERE c.cust_id = s.cust_id;
- ASELECT cust_id, company, total_sales
6The PATIENTS and DOCTORS tables contain these columns:
PATIENTS
PATIENT_ID NUMBER(9)
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
DOCTORS
DOCTOR_ID NUMBER(9)
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
You issue this statement:
SELECT patient_id, doctor_id
FROM patients, doctors;
Which result will this statement provide?- AA report containing all possible combinations of the PATIENT_ID and DOCTOR_ID values
- BA report containing each patient's id value and his doctor's id value
- CA syntax error
- DA report with NO duplicate PATIENT_ID or DOCTOR_ID values
7You have the following EMPLOYEES table:
EMPLOYEE_ID NUMBER(5) NOT NULL PRIMARY KEY
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
ADDRESS VARCHAR2(35)
CITY VARCHAR2(25)
STATE VARCHAR2(2)
ZIP NUMBER(9)
TELEPHONE NUMBER(10)
DEPARTMENT_ID NUMBER(5) NOT NULL FOREIGN KEY
The BONUS table includes the following columns:
BONUS_ID NUMBER(5) NOT NULL PRIMARY KEY
ANNUAL_SALARY NUMBER(10)
BONUS_PCT NUMBER(3, 2)
EMPLOYEE_ID VARCHAR2(5) NOT NULL FOREIGN KEY
You want to determine the amount of each employee's bonus as a calculation of salary times bonus. Which of the following queries should you issue?- ASELECT e.first_name, e.last_name, b.annual_salary * b. bonus_pct
FROM employees e, bonus b
WHERE e.employee_id = b.employee_id; - BSELECT first_name, last_name, annual_salary * bonus_pct
FROM employees, bonus NATURAL JOIN; - CSELECT e.first_name, e.last_name, b.annual_salary, b. bonus_pct
FROM employees, bonus
WHERE e.employee_id = b.employee_id; - DSELECT e.first_name, e.last_name, b.annual_salary, b. bonus_pct
FROM employees e, bonus b
WHERE e.employee_id = b.employee_id;
- ASELECT e.first_name, e.last_name, b.annual_salary * b. bonus_pct
8You have been asked to create a report that lists all corporate customers and all orders that they have placed. The customers should be listed alphabetically beginning with the letter 'A', and their corresponding order totals should be sorted from the highest amount to the lowest amount.
Which of the following statements should you issue?- ASELECT c.custid, c.companyname, o.orderdate, o. custid, o.amount
FROM customers c, orders o
WHERE c.custid = o.custid
ORDER BY companyname ASC, amount ASC; - BSELECT c.custid, c.companyname, o.orderdate, o. custid, o.amount
FROM customers c, orders o
WHERE c.custid = o.custid
ORDER BY amount DESC, companyname; - CSELECT c.custid, c.companyname, o.orderdate, o. custid, o.amount
FROM customers c, orders o
WHERE c.custid = o.custid
ORDER BY companyname, amount; - DSELECT c.custid, c.companyname, o.orderdate, o. custid, o.amount
FROM customers c, orders o
WHERE c.custid = o.custid
ORDER BY companyname, amount DESC;
- ASELECT c.custid, c.companyname, o.orderdate, o. custid, o.amount
9Using Oracle Proprietary join syntax, which two operators can be used in an outer join condition using the outer join operator (+)?
- ABETWEEN...AND... and IN
- BIN and =
- CAND and =
- DOR and =
10 The EMPLOYEE_ID column in the EMPLOYEES table corresponds to the EMPLOYEE_ID column of the ORDERS table.
The EMPLOYEE_ID column in the ORDERS table contains null values for rows that you need to display.
Which type of join should you use to display the data?- ASelf-join
- BOuter join
- CEquijoin
- DNatural join
11Using Oracle Proprietary join syntax, which operator would you use after one of the column names in the WHERE clause when creating an outer join?
- A=
- B+
- C(+)
- D*
12Which operator is typically used in a nonequijoin?
- A>=, <=, or BETWEEN ...AND
- BNOT
- COR
- DIN
- E*
13Which of the following best describes the function of an outer join?
- AAn outer join will return data only if both tables contain an identical pair of columns.
- BAn outer join will return only those rows that do not meet the join criteria.
- CAn outer join will return all rows that meet the join criteria and will return NULL values from one table if no rows from the other table satisfy the join criteria.
- DAn outer join will return only data from the far left column in one table and the far right column in the other table.
14Evaluate 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?- AThe self-join of the player table
- BThe join between the player table and the team table on PLAYER_ID
- CThe join between the player table and the team table on MANAGER_ID
- DThe join between the player table and the team table on TEAM_ID
15Nonequijoins are normally used with which of the following? (Choose two)
- ARanges of dates
- BRanges of text
- CRanges of numbers
- Dranges of columns
- ERanges of rowids
Information
- Description
- Quiz on QUIZ 7, created by Claudia Piscociu on 14/01/2017.
- Author
- Claudia Piscociu
- Language
- English
- Views
- 7
- Rating
- No ratings yet Rate this quiz