JOINS AND SUBQUERY
MULTIPLE CHOICE WITH ANSWER
Answer:
1)
_______________joins the table to itself.It is done by creating alias names for the same table.
a.Equi join
b.self join
c.Outer join
d.Non equijoin
2)
Which statement would display the highest credit limit available in each income level in each city in
the Customer table?
Select one:
a. SELECT cust_city, cust_income_level,MAX(cust_credit_limit)
FROM customers
GROUP BY cust_city , , cust_income_level ,MAX(cust_credit_limit);
b. SELECT cust_city, cust_income_level,MAX(cust_credit_limit)
FROM customers
GROUP BY cust_credit_limit , cust_income_level, cust_city ;
c. SELECT cust_city, cust_income_level,MAX(cust_credit_limit)
FROM customers
GROUP BY cust_city, cust_income_level,cust_credit_limit;
d. SELECT cust_city, cust_income_level,MAX(cust_credit_limit)
FROM customers
GROUP BY cust_city, cust_income_level;
3)
Generate a report that gives details of the customer's last name, name of the product and the quantity sold for all customers in 'Tokyo'.
Which two queries give the required result? (Choose two.)
Select one or more:
a. SELECT c.cust_last_name,p.prod_name,s.quantity_sold
FROM products p JOIN sales s JOIN customers c
ON(p.prod_id=s.prod_id)
ON(s.cust_id=c.cust_id)
WHERE c.cust_city='Tokyo';
b. SELECT c.cust_last_name,p.prod_name,s.quantity_sold
FROM products p JOIN sales s
ON(p.prod_id=s.prod_id)
JOIN customers c
ON(s.cust_id=c.cust_id)
WHERE c.cust_city='Tokyo';
c. SELECT c.cust_last_name,p.prod_name,s.quantity_sold
FROM sales s JOIN products p
USING (prod_id)
JOIN customers c
USING (cust_id)
WHERE c.cust_city='Tokyo';
d. SELECT c.cust_last_name,p.prod_name,s.quantity_sold
FROM products p JOIN sales s
USING (prod_id)
ON(p.prod_id=s.prod_id)
JOIN customers c
USING(cust_id)
WHERE c.cust_city='Tokyo';
4)
In which cases would you use an outer join?
Select one:
a. Only when the tables have a primary key/foreign key relationship.
b. The tables being joined have only matched data.
c. The tables being joined have both matched and unmatched data.
d. The tables being joined have NOT NULL columns.
e. The tables being joined have only unmatched data.
5)
Consider the below tables:
Employee Table
Column Name DataType Constraint
Name Varchar2(20)
Empno Number(10) PK
salary Number(10,2)
Tax Table
Column Name DataType Constraint
Taxgrade Number
Lowsal Number(10)
highsal Number(10,2)
We want to create a report that displays the employee details along with the tax category of each employee. The tax category is determined by comparing the salary of the employee from the EMP table to the lower and upper salary values in the TAX table.
Which SELECT statement produces the required results?
Select one:
a. SELECT e.name, e.salary, t.taxgrade
FROM emp e, tax t
WHERE e.salary BETWEEN t.lowsal AND t.highsal;
b. SELECT e.name, e.salary, t.taxgrade
FROM emp e, tax t
WHERE e.salary >= t.lowsal AND <= t.highsal;
c. SELECT e.name, e.salary, t.taxgrade
FROM emp e, tax t
WHERE e.salary <= t.lowsal AND e.salary >= t.highsal;
d. SELECT e.name, e.salary, t.taxgrade
FROM emp e, tax t
WHERE e.salary IN t.lowsal AND t.highsal;