BA 370MS Access Queries Assignment As detailed in the Database Lab video, Dean P
ID: 3916151 • Letter: B
Question
BA 370MS Access Queries Assignment As detailed in the Database Lab video, Dean Petrich (Deano the Clown) created a database with one table to keep track of his inventory of pianos.
That database is called
BA370 MS Access Queries.accdb. It is available on Canvas. You will be working with Dean’s data for this assignment. Download the database and open it in MS Access. Look at the Piano table, become familiar with the fields, and think of questions you might be able to answer with database queries.
Now create queries to provide the following information. For each one, clip the first few rows of the result, and include the clip in a Word document called BA370 Queries (Your_Name). docx Submit your Word document in Canvas before the assignment deadline.
1. Sort the pianos from high quality to low.
2. Sort the pianos from high quality to low and, within each quality, sort by Building and then by Location within that building.
3. List all of the pianos with a Type of‘Spinet.’
4. List the pianos in the shed and sort the results by manufacturer.
5. Count the pianos for each value of quality (ranging from 1 to 5).
6. Write a query to produce the report on the Last Slide in the class notes.
Piano ID Brand Serial Number Color Type Building Location Sound Board Venier Strings Felts Key Type Percent Missing Pedals Sound Remarks YearMade 1 Cable Kinsbury 191972 Brown Upright Shop Front Good Small pieces missing Excellent, no rust Worn Ivory 75 3, good 4 Beautiful wood 1915 2 Farband 6262 Dark Brown XP Shop Back Good Minor chips Rust Some wear Plastic, good 0 ?? 3 3 Aeolian 82107 Dark brown Player Shop Back left Good Chips Rust All need replacing … mouse problem Plastic 0 ?? 1 Probably not worth fixing. 4 Aldrich 26365 Walnut Upright Shop Left Good Good Excellent Good, but filed down? Ivory 0 ?? 4 Quality piano, beautifully done, short. Sound??? 1908 5 Kimball 912497 Walnut Upright Shop Center Good Cracked, top broken, ornate Rusty Good Plasting 0 3, middle pedal broken 1 Maybe not a keeper? 6 Straube 9424 Brown Upright Shop Front Good OK, but bad finish Good Good, lite wear Plastic 0 ?? 3 7 Lester Plano 0 Brown Spinet Shop Back left Good Very dirty, paint Good, some rust Good Plastic 0 3 good 0 Kind of a mess 8 Victor 32839 Dark brown Upright Shop Front Good Good Some rust Worn Plastic 0 3 9 Farrand 25402 Dark Brown Upright Shop Back wall Good Good 1 missing, no rust Heavily sanded, but good Plastic 0 3 OK 3 10 Kranich and Bach 52874 B lack Player Shop Back wall Good Excellent Like new Escellentq Plastic 0 2 good 0 Won't play, but like new. 11 George P Bent 547 Dark brown Upright Shop Left side Good Minor wear Minor rust Good, but small Plastic 0 3 good 3 12 Richmond 28403 Red/Brown Upright Shed S1 Good Bad finish Good Worn, small Ivory 30 3 rust 3 13 Spinet 0 Spinet Shed 2 0 0 Bad shape, trash. 14 P.S. Wick 12379 Light Brown Upright Shed S3 Good Good Rust, all copper Work but OK Ivory 0 2 Seems like a quality piano. Why isn't the sound better? 15 Chickering 119222 Brown Upright Shed S4 Good Good, scaling on top Good, some rust OK Ivory 50 ??? 4 Worth saving! 1911 16 Hammond 13082 Green Upright Shed S5 ??? Bad, many problems Rusty, very dirty Worn, dirty Plastic 0 ??? 4 Exterior looks terrible, but is there life here??? -1 17 Foster 0 Brown Upright Shed S6 Good Ornate, minor breaking Rusty Worn Ivory 25 ??? 4 18 Junk 0 Junk Shed S7 0 0 Trash, terrible paint. A shame. 19 Junk 0 Junk Shed S8 0 0 A mess. 20 Walworth 7590 Red Upright Shed S9 Good Parting, separated Rusty OK or less Ivory 0 ??? 0 Won't play, but have remark that there's hope for it. 21 Junk 0 Junk Shed S10 0 0 22 Monarch 168898 Borwn Upright Shed S11 ?? Good Good, clean interior Good some wear Plastic 0 ?? 4 1924 23 Schiller 10285 Chestnut Upright Shed S12 Good Excellent, some missing, ornate Good Bit of wear, but good Ivory 0 ??? 4 1893 24 Wurlitzer 0 ??? Spinet Shed S13 Good Poor Good Dirty, need work Plastic 0 2 Bad shape 25 Lowery 0 Light Brown Spinet Shed S14 Good New New New Plastic 3 3 great shape 0 Beautiful piano, needs to be sold soon. 26 Story and Clark 44386 Black Upright Shed S15 Goooodf OK, ornate, lid loose needs hinges Good Bood, some wear Plastic, damaged 0 Good 4 Recently acquired. 1912 27 Bush and Lane 49999 Brown Upright Shed S16 OK /bad Rusty Dirty, need minor work Plastic 0 2 28 Janss 0 Brown Spinet Shed S20 ? OK, bad finish Like new Like new Plastic 0 ?? 2 Spinet worth saving. 29 Willard 0 Black Upright Shed S22 Minor repairs needed Painted and a mess. Ornate. Rusty Like new Plastic 0 1 30 Janesssen, Wilson and White Blue Former player Shed S27 Good Awful; Good, minor rust Like new Ivory, chipped 0 3 Terrible appearance. Major work to take out flaws. 31 Wenteroth 0 Brown Upright Shed S19 Bad Ornate and a mess Rusty Need work 0 2 Junk it??? 32 Beckworth 54868 Dark brown Former Player Shed S18 ??? OK Rusty Some wear Plastic 0 3 33 Cable Nelson 238787 Red Spinet Shed S21 Good Rough, but OK Very rusty Good Plastic 0 4 This piano wants to be rescued. 1952 34 Kline 0 Brown Upright Shed S17 Bad Very rusty Needs work. Plastic 0 0 Bad shape. 35 Horkley 0 Upright Shed S28 ??? OK Rusty Awful Plastic 0 0 Been wet in shed. Rusty, dirty mess. 36 Johnmaster 0 Dark brown Upright Shed S25 ??? Good, but top is lost Very rusty Good Ivory 50 3 37 Shonenger 29924 Brown Upright Shed S22 OK OK, ornate OK Need work, but OK Ivory 10 ?? 2 38 Belll 4451 Green Upright Shed S23 Good Missing ornate pieces, needs work. Rust Good Plastic 3 39 Karlbach 11662 Dark brown Upright Shed S26 Good Needs woodwork Good Good Ivory 33 2 40 Kincade 66520 Light brown Spinet Shed N1 Good OK Excellent Excellent Plastic 0 3 41 Harrington 106577 Spinet Shed N2 Good Very Good Excellent Excellent Plastic 0 3 great 4 1926 42 Smith and Barnes 18100 Former Player Shed N3 ??? Bad, major wood problems Rust OK Plastic 0 0 43 J & C Fisher 151454 Upright Shed N4 OK Good, but paint worn Light Rust Need filing Ivory 60 ?? 4 Gorgeous board on the top inside. Someone cared. 1924 44 Willard 78669 Upright Shed N7 ?? Needs paint, wood work Rusty Some wear Ivory 95 3 45 Going to school 0 Spinet Shed N6 0 0 Replaces school piano that won't stay in tune. 46 Kincade 238520 Spinet Shed N5 Good Stain needs work Excellent Excellent Plastic 0 3 47 Western Cottage 18479 Upright Shed N8 Good Pretty Rusty Need filing Plastic 20 3 48 Unknown 0 Shed N9 Top a mess. Plastic 0 3 Can 't see inside. 49 Getts 122018 Spinet Shed N12 ?? `OK OK `Wear Plastic 0 3 50 Story & Clark 346312 Spinet Shed N11 Good Excellent Excellent Excellent Plastic 0 4 Very nice with bench. 1962 51 Monarch 12625 Former Player Shed N13 ? OK Rusty A mess Plastic 0 3 52 George Strick 26691 Upright Shed N14 ??? Loose layers Rusty Need filing Ivory 50 3 53 George Strick 26204 Former Player Shed N17 ??? OK Rusty Need filing Plastic 0 4 1904 54 Mason and Hamilton 0 Upright Shed 16 ??? Good Good Some wear Ivory 0 4 55 Esteu 20158 Upright North Tent NT1 Gorgeous Good Broken Ivory, half chipped 50 2 OK 4 1891 56 H.G. Johnson 10749 Upright North Tent NT2 Good Top broken hinges Light rust Light wear Plast 0 4 1927 57 Winter ??? 326493 Spinet North Tent NT3 Finish needs work Good Light wear Plastic 0 3 good 4 Missing music stand 1955 58 Baldwin 637957 Spinet North Tent NT4 OK Good Light wear Plastic??? 4 Broken leg 1989 59 Henry Miller 88517 Spinet North Tent NT5 Poor finish Good Good Plastic 0 3 60 Briggs 80360 Upright North Tent NT6 OK Good, one broken New? Ivory 40 4 1921 61 Gerhard 33475 Upright North Tent NT7 ??? Needs repair to case Rusty Worn Plastic 10 3 62 Krehling 150710 Upright North Tent NT8 De-laminating Rusty/ broken Action broken? Plastic, chipped 0 1 63 Hobart Cable 77182 Upright North Tent NT9 OK Finish bad, top hinges broken OK Some wear Plastic 0 Missing middle, square 4 1905 64 Story & Clark 250735 Spinet North Tent NT10 Good OK, finish problematic, broken music stand Rust Worn Plastic 0 3 65 Mehlin 28733 Upright North Tent NT11 Good Stripped and not finished OK Worn Ivory 0 Pretty/elegant 4 1909 66 Aeolian 182562 Spinet North Tent NT12 Good Good Good New set, some unglued Plastic 0 4 -1 67 Farrand 27631 Upright South Tent ST0 OK OK OK Very worn Ivory 50 3 4 1910 68 Hains Brothers 9896 Upright South Tent ST1 Big problems Rusty, many replaced Worn to wood Plastic 0 2 3 69 Kurtzman 21398 White!!! Upright South Tent ST5 Good OK, painted white Good Worn Ivory 50 3 good 5 No wheels 1903 70 Kohler Campbell 13891 South Tent ST4 OK Awful mess OK Very worn Ivory 100 0 71 Wurlitzer 1132226 Spinet South Tent ST3 Finish marginal OK Need filing Plastic 0 3, square 3 72 Clarendon 24652 Upright South Tent ST2 Bad Mess Plastic 0 0 Junk. 73 Hobart Cabel 77154 Upright South Tent ST6 Unglued Peeling and buckling Rust 0 0 Junk 74 Kimball 152522 Upright South Tent ST7 OK Rust Good, worn Ivory 3 3 nice 2 75 Sterling 34586 Upright South Tent ST8 Shot Warped 0 0 Junk 76 Weaver 44953 Upright South Tent ST9 OK Some work needed Rusty Worn Plastic 0 2 2 Pewter lifters 77 Mathushek 12963 Upright South Tent ST13 Blue Rusty Much wear Ivory, unglued 0 2 4 1883 78 Kohler & Chase 77539 Upright South Tent ST12 Warped top Rust Worn Ivory 50 2 Serial number starts with F, F77539. 79 Chickering 106503 Upright South Tent ST11 Major problems Ivory, unglued. 0 0 Junk 80 Werner 69764 Upright South Tent ST10 OK Peeling. Rusty, breaking Unglued Ivory 100 3, 1 broken 0 Junk 81 Baldwin 70452 Spinet South Tent ST14 Good, hairline crack Water on finish Excellent Excellent Plastic 0 2 4 1933Explanation / Answer
Q1) Sort the pianos from high quality to low.
SELECT * FROM PIANO ORDER BY Sound DESC;
Q2) Sort the pianos from high quality to low and, within each quality, sort by Building and then by Location within that building
SELECT * FROM PIANO ORDER BY SOUND DESC, BUILDING ASC, LOCATION ASC;
Q3) List all of the pianos with a Type of‘Spinet.’
SELECT * FROM PIANO WHERE TYPE = 'Spinet'
Q4) List the pianos in the shed and sort the results by manufacturer
SELECT * FROM PIANO WHERE BUILDING = 'Shed' ORDER BY Brand ASC;
Q5) Count the pianos for each value of quality (ranging from 1 to 5)
SELECT Sound, COUNT(Sound) from PIANO WHERE SOUND IN (1,2,3,4,5) GROUP BY (SOUND);
Please let me know in case of any clarificatiosn required. Thanks!
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.