JOINS AND SUBQUERY

MULTIPLE CHOICE WITH 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;

​