Question

1, The following query is called_________

SQL>select e.empno,e.ename, m.ename, m.empno
from emp e, emp m where e.mgr = m.empno;

cross join

outer join

Self join

Full join

Natural Joins

2.

Steve wants to write a query to display the employee last name, department name, location id, and city of all employees who earn a commission.
He wrote the following query. is it true or false.

SELECT e.last_name, d.department_name, d.location_id, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND e.commission_pct IS NOT NULL;

Group of answer choices

True

False

3.

The PRODUCT table contains these columns:

PRODUCT_ID NUMBER(9)
DESCRIPTION VARCHAR2(20)
COST NUMBER(5,2)
MANUFACTURER_ID VARCHAR2(10)

You want to display product costs with these desired results:

1. The cost displayed for each product is increased by 20 percent.
2. The product manufacturer id must be 25001, 25020, or 25050 but not 25002
3. Twenty percent of the original cost is less than $4.
Note: (select two options)

Which statement should you use?

Group of answer choices

SELECT description, cost * .20
FROM product
WHERE cost * .20 < 4.00 AND manufacturer_id BETWEEN '25001' AND '25050';

SELECT description, cost * 1.20
FROM product
WHERE cost * .20 < 4.00 AND manufacturer_id IN ('25001', '25020', '25050');

SELECT description, cost * 1.20
FROM product
WHERE cost * .20 < 4.00 AND manufacturer_id <> 25002
AND manufacturer_id IN ('25001', '25020', '25050');

SELECT description, cost * .20
FROM product
WHERE cost * .20 < 4.00;

4.

PRODUCT table contains these columns:

ID NUMBER(7) PK
SALE_PRICE NUMBER(7,2)

Evaluate these two SQL statements:

1. SELECT MAX(sale_price), MIN(sale_price), AVG(sale_price)
FROM product;

2. SELECT ROUND(MAX(sale_price),2), ROUND(MIN(sale_price),2), ROUND(AVG(sale_price),2)
FROM product
GROUP BY sale_price;

How will the results differ?

Group of answer choices:

One of the statements will generate an error.

Statement 1 will display a result for each sale price; statement 2 will display a result for each product.

Statement 2 will only display one row of results; statement 1 could display more than one.

Statement 1 will display three values; statement 2 will display three values for each sale price.

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

Question 1:

Answer :Self join

Explanation :

• Joining a table to itself is called as self join.
• In the query given above a table is joined to itself that is emp is joined with emp.

**************************

Question 2:

Answer :True

Explanation :SQL query given in the question will return true because sql query will join three tables employees e, departments d, locations l and will return the result.

**************************

Question 3:

Answer :

SELECT description, cost * 1.20
FROM product
WHERE cost * .20 < 4.00 AND manufacturer_id IN ('25001', '25020', '25050');

SELECT description, cost * 1.20
FROM product
WHERE cost * .20 < 4.00 AND manufacturer_id <> 25002
AND manufacturer_id IN ('25001', '25020', '25050');

Explanation :SQL query given above will list the required result.

**************************

Question 4:

Answer :Statement 1 will display three values; statement 2 will display three values for each sale price.

Explanation :

• SELECT MAX(sale_price), MIN(sale_price), AVG(sale_price)
  FROM product;
• Sql statement will display max,min and avg for sale_price.
• while statement2 will display min,max and avg for each sale price because group by is used.
• Which will group all the records based on sales price.

**************************

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.