Halaman

Thursday, August 8, 2019

Answer Sections 6 Quiz Database Programming with SQL 2019 Learner - English


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 dont 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);
(*)

1 comment:

  1. 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.
    But 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

    ReplyDelete

Final Exam Java Programming 2019 Learner - English

Final Exam