Sql Hw

Topics: 175, Copper Pages: 5 (546 words) Published: July 15, 2013
1. SELECT PARTNUMB, PARTDESC
FROM PART
Query1|
PARTNUMB| PARTDESC|
AX12| IRON|
CX11| MIXER|
WHERE UNONHAND BETWEEN 100 AND 200

2. SELECT PARTNUMB, PARTDESC, (UNITPRCE*UNONHAND) ONHANDVALUE FROM PART
WHERE VALUE >= 1000

Query1|
PARTNUMB| PARTDESC| ONHANDVALUE|
AX12| IRON| $1,866.80|
BH22| TOASTER| $3,320.25|
BT04| STOVE| $4,432.89|
BZ66| WASHER| $16,221.40|
CB03| BIKE| $8,250.00|
CX11| MIXER| $6,490.40|
CZ81| WEIGHTS| $22,669.92|

3. SELECT CUSTNUMB, CUSTNAME
FROM CUSTOMER
WHERE CUSTNAME LIKE 'Nelson,*'

Query1|
CUSTNUMB| CUSTNAME|
522| Nelson, Mary|

4. SELECT *
FROM PART
GROUP BY ITEMCLSS
ORDER BY PARTNUMB

SELECT PART.PARTNUMB, PART.PARTDESC, PART.ITEMCLSS, PART.WRHSNUMB, PART.UNITPRCE FROM PART
ORDER BY PART.ITEMCLSS, PART.PARTNUMB

Query1|
PARTNUMB| PARTDESC| ITEMCLSS| WRHSNUMB| UNITPRCE|
BT04| STOVE| AP| 2| $402.99|
BZ66| WASHER| AP| 3| $311.95|
AX12| IRON| HW| 3| $17.95|
BH22| TOASTER| HW| 3| $34.95|
CA14| SKILLET| HW| 3| $19.95|
CX11| MIXER| HW| 3| $57.95|
AZ52| SKATES| SG| 2| $24.95|
BA74| BASEBALL| SG| 1| $4.95|
CB03| BIKE| SG| 1| $187.50|
CZ81| WEIGHTS| SG| 2| $108.99|

5. SELECT SUM(BALANCE) AS TOTAL
FROM CUSTOMER
WHERE SLSRNUMB = 12

Query1|
TOTAL|
$451.35|

6. SELECT DISTINCT SLSRNUMB, SUM(BALANCE) AS TOTAL
FROM CUSTOMER
GROUP BY SLSRNUMB

Query1|
SLSRNUMB| TOTAL|
3| $1,903.00|
6| $590.45|
12| $451.35|

SELECT DISTINCT SLSRNUMB, SUM(BALANCE) AS TOTAL
FROM CUSTOMER
GROUP BY SLSRNUMB
HAVING COUNT(CUSTNUMB) >= 4

Query1|
SLSRNUMB| TOTAL|
6| $590.45|

7. SELECT ORDERS.ORDNUMB, ORDDTE, ORDLNE.PARTNUMB, ORDLNE.QUOTPRCE FROM ORDERS, ORDLNE
WHERE ORDERS.ORDNUMB = ORDLNE.ORDNUMB

Query1|
ORDNUMB| ORDDTE| PARTNUMB| QUOTPRCE|
12489| 9/2/1991| AX12| $14.95|
12491| 9/2/1991| BT04| $402.99|
12491| 9/2/1991| BZ66| $311.95|
12494| 9/4/1991| CB03| $175.00|
12495| 9/4/1991| CX11| $57.95|
12498| 9/5/1991| AZ52| $22.95|
12498| 9/5/1991| BA74| $4.95|
12500| 9/5/1991| BT04| $402.99|
12504| 9/5/1991| CZ81| $108.99|

8. SELECT CUSTOMER.CUSTNUMB, CUSTNAME
FROM CUSTOMER
WHERE NOT EXISTS
(SELECT *
FROM ORDERS
WHERE ORDERS.CUSTNUMB = CUSTOMER.CUSTNUMB AND ORDDTE = #9-5-1991#)

Query1|
CUSTNUMB| CUSTNAME|
256| Samuels, Ann|
311| Charles, Don|
315| Daniels, Tom|
405| Williams, Al|
412| Adams, Sally|
567| Baker, Joe|
587| Roberts, Judy|
622| Martin, Dan|

9. SELECT FIRST.PARTNUMB, FIRST.PARTDESC, FIRST.ITEMCLSS, SECOND.PARTNUMB, SECOND.PARTDESC, SECOND.ITEMCLSS FROM PART FIRST, PART SECOND
WHERE FIRST.PARTNUMB < SECOND.PARTNUMB AND FIRST.WRHSNUMB = SECOND.WRHSNUMB AND FIRST.ITEMCLSS = SECOND.ITEMCLSS

Query1|
FIRST.PARTNUMB| FIRST.PARTDESC| FIRST.ITEMCLSS| SECOND.PARTNUMB| SECOND.PARTDESC| SECOND.ITEMCLSS| AX12| IRON| HW| BH22| TOASTER| HW|
BH22| TOASTER| HW| CA14| SKILLET| HW|
AX12| IRON| HW| CA14| SKILLET| HW|
BA74| BASEBALL| SG| CB03| BIKE| SG|
CA14| SKILLET| HW| CX11| MIXER| HW|
BH22| TOASTER| HW| CX11| MIXER| HW|
AX12| IRON| HW| CX11| MIXER| HW|
AZ52| SKATES| SG| CZ81| WEIGHTS| SG|

10. SELECT ORDERS.ORDNUMB, ORDERS.ORDDTE
FROM ORDERS
WHERE CUSTNUMB =
(SELECT CUSTNUMB
FROM CUSTOMER
WHERE CUSTNAME = 'Nelson, Mary')
AND ORDNUMB NOT IN
(SELECT DISTINCT ORDNUMB
FROM ORDLNE
WHERE ORDLNE.PARTNUMB =
(SELECT PARTNUMB
FROM PART
WHERE PARTDESC = 'WEIGHTS'))

Query1|
ORDNUMB| ORDDTE|
12498| 9/5/1991|

11. SELECT PARTNUMB, PARTDESC
FROM PART
WHERE UNITPRCE > ALL
(SELECT UNITPRCE
FROM PART
WHERE ITEMCLSS = 'HW')
Query1|
PARTNUMB| PARTDESC|
BT04| STOVE|
BZ66| WASHER|
CB03| BIKE|
CZ81| WEIGHTS|
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • Learn Sql Essay
  • Sql Research Paper
  • SQL-an analysis Essay
  • Sql Essay
  • Essay about Sql Server
  • Some SQL Multiple Choice revision Essay
  • Sql Practice Questions Essay
  • Database Sql Essay

Become a StudyMode Member

Sign Up - It's Free