Lab exam answers

PRELIM LABORATORY EXAM ANSWERS

1.)    Select productName,buyPrice,MSRP,(MSRP-buyPrice) as Profit

from products p

having (MSRP-buyPrice) > all (Select (MSRP-buyPrice)

                  from products p

                  where p.productline=(Select p.productline

 from products p

where productName="1996 Moto Guzzi 1100i"));

Answes:

ProductName	buyPrice	MSRP	Profit
'1952 Alpine Renault 1300'	98.58	214.3	115.72
'2001 Ferrari Enzo'	95.59	207.8	112.21

2.)    SELECT productName, ProductVendor

from products

where productVendor=(Select productVendor from products where productName="1969 Corvair Monza")

and productName!="1969 Corvair Monza";

Product Name	ProductVendor
'1958 Setra Bus'	'Welly Diecast Productions'
'1968 Dodge Charger'	'Welly Diecast Productions'
'1969 Dodge Charger'	'Welly Diecast Productions'
'1917 Grand Touring Sedan'	'Welly Diecast Productions'
'1936 Harley Davidson El Knucklehead'	'Welly Diecast Productions'
'1971 Alpine Renault 1600s'	'Welly Diecast Productions'
'The Queen Mary'	'Welly Diecast Productions'

3.)    SELECT productName,sum(quantityinStock) as ProductInStock,sum(quantityOrdered)as TotalQuantityOrdered,sum(quantityinStock)-sum(quantityOrdered) as QuantityLeft

from products p,orderdetails od, orders o

where p.productCode=od.productCode

and od.orderNumber=o.orderNumber

and status="Shipped"

group by productName;

Rows Fetch(109 rows)

4.)    SELECT productName,orderNumber

from products p LEFT JOIN orderdetails od

ON p.productCode=od.productCode

where orderNumber is NULL;

'1985 Toyota Supra'

NULL

5.)    SELECT customerName,creditLimit

from customers c

where creditLimit < all (Select creditLimit from customers c, employees e

                        where c.salesRepEmployeeNumber=e.employeeNumber and lastname="Vanauf" and firstname="George")

and creditLimit!=0;

'Atelier graphique'	21000
'Auto-Moto Classics Inc.'	23000
'Boards & Toys Co.'	11000
'Gifts4AllAges.com'	41900
'Royale Belge'	23500
'Microscale Inc.'	39800
'Frau da Collezione'	34800

6.)    SELECT customerName,sum(quantityordered)

from customers c, orders o, orderdetails od

where c.customerNumber=o.customerNumber and o.orderNumber=od.orderNumber

and country="USA"

group by customerName;

Rows Fetch(36 rows)