Sabtu, 11 November 2017

Section 6 Quiz Database Programming with SQL


  1. Which two sets of join keywords create a join that will include unmatched rows from the first table specified in the SELECT statement? LEFT OUTER JOIN and FULL OUTER JOIN (*)
  2. What types of joins will return the unmatched values from both tables in the join? Full outer joins (*)
  3. You need to join the EMPLOYEE_HIST and EMPLOYEES tables. The EMPLOYEE_HIST table will be the first table in the FROM clause. All the matched and unmatched rows in the EMPLOYEES table need to be displayed. Which type of join will you use? A right outer join (*)
  4. EMPLOYEES Table: 
    NameNull?Type
    EMPLOYEE_IDNOT NULLNUMBER(6)
    FIRST_NAMEVARCHAR2(20)
    LAST_NAMENOT NULLVARCHAR2(25)
    DEPARTMENT_IDNUMBER (4)
    DEPARTMENTS Table:
    NameNull?Type
    DEPARTMENT_IDNOT NULLNUMBER 4
    DEPARTMENT_NAMENOT NULLVARCHAR2(30)
    MANAGER_IDNUMBER (6)
    A query is needed to display each department and its manager name from the above tables. However, not all departments have a manager but we want departments returned in all cases. Which of the following SQL: 1999 syntax scripts will accomplish the task? SELECT d.department_id, e.first_name, e.last_name 
    FROM employees e
    RIGHT OUTER JOIN departments d
    ON (e.employee_id = d.manager_id);

    (*)
  5. Hierarchical queries can walk both Top-Down and Bottom-Up. True or False? True (*)
  6. Which SELECT statement implements a self join? SELECT p.part_id, t.product_id 
    FROM part p, part t
    WHERE p.part_id = t.product_id;

    (*)
  7. Which of the following database design concepts is implemented with a self join? Recursive Relationship (*)
  8. 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
    (*)
  9. Which statement about a self join is true? Table aliases must be used to qualify table names. (*)
  10. A NATURAL JOIN is based on: Columns with the same name and datatype (*)
  11. 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? A cross join (*)
  12. The join column must be included in the select statement when you use the NATURAL JOIN clause. True or False? False (*)
  13. Below find the structures of the PRODUCTS and VENDORS tables:
  14. 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? 
      (Choose all correct answers) 
    SELECT p.product_name, v.vendor_name
    FROM products p
    NATURAL JOIN vendors v
    ORDER BY p.product_name;

    (*)

  15. You can do nonequi-joins with ANSI-Syntax. True or False? True (*)
  16. You created the CUSTOMERS and ORDERS tables by issuing these CREATE TABLE statements in sequence:

  17. CREATE TABLE customers
    (custid varchar2(5),
    companyname varchar2(30),
    contactname varchar2(30),
    address varchar2(30),
    city varchar2(20),
    state varchar2(30),
    phone varchar2(20),
    constraint pk_customers_01 primary key (custid));

    CREATE TABLE orders
    (orderid varchar2(5) constraint pk_orders_01 primary key,
    orderdate date,
    total number(15),
    custid varchar2(5) references customers (custid));

    You have been instructed to compile a report to present the information about orders placed by customers who reside in Nashville. Which query should you issue to achieve the desired results? 
SELECT orderid, orderdate, total
FROM orders o
JOIN customers c ON o.custid = c.custid
WHERE city = 'Nashville';

(*)



2 komentar:

  1. There has certainly been some major developments in how the world interacts. I see the differnet readers have a wide variety of views. Need a staff for hire, inbox. Thanks

    BalasHapus
  2. Harrah's Casino Reno - Mapyro
    Harrah's Casino Reno is a hotel and casino located 39 춘천 출장샵 miles south of Reno, Nevada. Harrah's Reno 상주 출장안마 opened in 1906 여수 출장안마 and 영천 출장마사지 is one of 군산 출장안마 the oldest

    BalasHapus