Question

How many customers placed orders between 1-Apr-03 and 31-May-03?

COUNT

12

a.    SELECT COUNT(DISTINCT order_no) AS count

      FROM Orders

      WHERE order_date BETWEEN '1-APR-03' AND '31-May-03';

b.    SELECT COUNT(DISTINCT order_no) AS count

      FROM Orders

      WHERE order_date BETWEEN '4/1/03' AND '5/31/03';

c.    a and b are correct

d.    a and b are incorrect

Question 2 (2 points)

. Find product no. , brand and unit price of the products whose unit price is greater than the unit price of all the products of the brand 'Microsoft ' .

A) SELECT product_no, brand, unit_price

    FROM oes2.product

    WHERE unit_price  > ALL ( SELECT unit_price

                                                    FROM oes2.product

                                                   WHERE brand = 'Microsoft' ) ;

B) SELECT product_no, brand, unit_price

     FROM oes2.product

    WHERE unit_price  > ( SELECT MAX(unit_price)

                                           FROM oes2.product

                                            WHERE brand ='Microsoft' ) ;

C) A and B are incorrect .

D) A and B are correct .

Question 3 (2 points)

For each branch size with no. of positions greater than 8 find the total number of positions and the sum of their salary .

BRANCH_SIZE	NO_OF_POSITIONS	TOTAL_SALARY
HQ	9	566000

1. SELECT   branch_size , COUNT (position) AS no_of_positions ,

                     SUM(salary) AS total_salary

    FROM oes2.salary

    HAVING COUNT(position)>8

    GROUP BY branch_size

    ORDER BY branch_size ;

2. SELECT   branch_size , COUNT(position) AS no_of_positions ,

                  SUM(salary) AS total_salary

    FROM oes2.salary

   GROUP BY branch_size

   HAVING COUNT(POSITION) > 8

   ORDER BY BRANCH_SIZE;

3. SELECT   branch_size , COUNT (position) AS no_of_positions ,

                     SUM(salary) AS TOTAL_SALARY

    FROM OES2.salary

   GROUP BY branch_size

    WHERE COUNT(POSITION) > 8

    ORDER BY BRANCH_SIZE;

4. SELECT   branch_size , COUNT(position) AS no_of_positions ,

                     SUM(salary) AS total_salary

    FROM oes2.salary

    WHERE COUNT(POSITION) > 8

    GROUP BY branch_size

   ORDER BY BRANCH_SIZE;

Question 4 (2 points)

Using OES2 database, list addresses of all branches in Mankato or St. Paul .

Question 4 options:

	SELECT branch_no, street, city FROM OES2.branch WHERE city='MANKATO' OR city='SAINT PAUL' ;
	SELECT branch_no , street ,city FROM OES2.branch WHERE city='Mankato' OR city='St PAUL' ;
	SELECT branch_no , street, city FROM OES2.branch WHERE city="MANKATO" OR city="SAINT PAUL" ;
	SELECT branch_no , street , city FROM OES2.branch WHERE city='MAN' OR city='SAINT PAUL' ;

Question 5 (2 points)

Using OES2 database, list all employees with a commission greater than 10,000.

Question 5 options:

	SELECT employee_No, fName, lName, commission FROM Employee WHERE commission < 10000;
	SELECT employee_No, fName, lName, commission FROM Employee WHERE commission > 10000;
	SELECT employee_No, fName, lName, commission FROM Employee WHERE commission >< 10000;
	SELECT employee_No, fName, lName, commission FROM Employee WHERE commission <> 10000;

Answer 1

Ans

1 c) a,b are correct

2 b) is correct

3 2)SELECT branch_size , COUNT(position) AS no_of_positions ,SUM(salary) AS total_salaryFROM oes2.salaryGROUP BY branch_size HAVING COUNT(POSITION) > 8 ORDER BY BRANCH_SIZE; is correct

4 SELECT branch_no , street ,city

FROM OES2.branch

WHERE city='Mankato' OR city='St PAUL' ; is correct

5

SELECT employee_No, fName, lName, commission

FROM Employee

WHERE commission > 10000; is correct.

,

.

Dates format are valid, greater than max means max from all, having comes after groupby, names are case-sensitive, greater than is >

.

.

.If any doubt ask in the comments.

Please appreciate the work by giving a thumbs up.