Section 6
Which keyword in a SELECT statement creates an equijoin by
specifying a column name common to both tables?
A
HAVING clause
The
FROM clause
A USING
clause (*)
The
SELECT clause
The primary advantages of using
JOIN ON is: (Select two)
The
join happens automatically based on matching column names and data types.
It permits
columns that donﾒt
have matching data types to be joined. (*)
It
permits columns with different names to be joined. (*)
It will
display rows that do not meet the join condition.
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?
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
JOIN vendors v
ON (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
NATURAL JOIN vendors v
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;
The join column must be
included in the select statement when you use the NATURAL JOIN clause. True or
False?
True
False
(*)
You need to join two tables
that have two columns with the same name, datatype, and precision. Which type
of join would you create to join the tables on both of the columns?
Outer
join
Cross
join
Self-join
Natural
join (*)
Which statement about a natural join is true?
Columns
with the same names cannot be included in the SELECT list of the query.
Columns
with the same names must have identical data types.
Columns
with the same names must have the same precision and datatype. (*)
Columns
with the same names must have compatible data types.
Which select statement will
return the last name and hire date of an employee and his/ her manager for
employees that started in the company before their managers?
SELECT
w.last_name, w.hire_date, m.last_name, m.hire_date
FROM employees w , employees m
WHERE w.manager_id = m.employee_id
AND w.hire_date > m.hire_date
SELECT
w.last_name, w.hire_date, m.last_name, m.hire_date
FROM employees w , employees w
WHERE w.manager_id = w.employee_id
AND w.hire_date < w.hire_date
SELECT
w.last_name, w.hire_date, m.last_name, m.hire_date
FROM employees w , employees m
WHERE w.manager_id = m.employee_id
AND w.hire_date < m.hire_date
(*)
SELECT
w.last_name, w.hire_date, m.last_name, m.hire_date
FROM employees w , employees m
WHERE w.manager_id != m.employee_id
AND w.hire_date < m.hire_date
Which statement about a self
join is true?
A self
join must be implemented by defining a view.
Table
aliases cannot be used to qualify table names.
Table
aliases must be used to qualify table names. (*)
The
NATURAL JOIN clause must be used.
Evaluate this SELECT statement:
SELECT *
FROM employee e, employee m
WHERE e.mgr_id = m.emp_id;
Which type of join is created by this SELECT statement?
a cross
join
a left
outer join
a self
join (*)
a full
outer join
Which of the following database
design concepts is implemented with a self join?
Non-Transferability
Recursive
Relationship (*)
Supertype
Arc
Hierarchical
queries can walk both Top-Down and Bottom-Up. True or False?
True
(*)
False
Which query represents the
correct syntax for a left outer join?
SELECT
companyname, orderdate, total
FROM customers c
LEFT JOIN orders o
ON c.cust_id = o.cust_id;
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
LEFT OUTER JOIN 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;
Which type of join returns rows
from one table that have NO direct match in the other table?
Self
join
Natural
join
Equijoin
Outer
join (*)
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) ;
Optimal
Join
Outer
Join (*)
Equijoin
Inner
Join
Which query will retrieve all the
rows in the EMPLOYEES table, even if there is no match in the DEPARTMENTS
table?
SELECT
e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d ON (e.department_id =
d.department_id);
SELECT
e.last_name, e.department_id, d.department_name
FROM employees e
NATURAL JOIN departments d;
SELECT
e.last_name, e.department_id, d.department_name
FROM employees e
JOIN departments d USING (e.department_id =
d.department_id);
SELECT
e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON (e.department_id =
d.department_id);
(*)
Having your own house is the dream of every person. For a middle class person, it is considered as a lifetime achievement as it requires quite a huge amount of money. Banks play a pivotal role in fulfilling this basic need. The products they offer and the services they provide are of immense use to people who intend to have their own house. For a safe and beneficial home loan, proper awareness over the products, policies, terms and conditions of the bank is most important as ignorance may result in more payments to the bank in terms of principal and interest components.
ReplyDeleteBut working with Mr Pedro changed everything in the lending experience, Mr Pedro helped me with a home loan at 2% rate which was very fast and smooth.
I will recommend Mr Pedro a loan officer and his awesome funding company Email Mr Pedro on pedroloanss@gmail.com.
Marie Carlos,
Texas USA