Ethernet Satellite Dish International Plastics Inc China Hea

Ethernetsatellite Dishinternational Plastics Inc China Headquart

Ethernetsatellite Dishinternational Plastics, Inc. - China Headquarters Corp Marketing Finance HR R&D Network MAC PRO (15) Dual 1.8 GB 1 Ghz 6 core 12 GB Ram 1 TB HD 27" monitors Satellite base station NAS Iomega P800M 24 Port Hub Linksys EF2H24 WIN Exchange Server WIN Network Server UNIX ERP/MRP Server ERP = SAP UPS: Serve the entire server room; additional UPS in satellite base station. IBM pSeries 6E4 Multipocessor 16GB RAM 1 TB HD 2 ea IBM HS20 Blade Svrs 2 X XEON 2.8 GHz 1 GB RAM 1 GB Fiber backbone 3 ea Phaser 6700 color Printers 100 base T 35 ea Dell Vostro i3 3.3 Ghz 4 GB RAM 500 GB HD WIN 7 OS Office ea 24 port switches Cisco IP Phones Gateway/Switch 1 HP Plotter Identical to other WIN servers VOIP/Data Router T3 to outside network Gateway/Switch VOIP/Data Router Ka Band ATM AA1 PVC 51.8 Mb data rate, AES end-to-end 8 HP Laserjet BW printers – P3015dn 2 in each department MSAccess - Assignment 2 – Chapter 4 Worksheet (86 pts) Instructions : Add your SQL command and results (using copy/paste and screen capture) to each question. Do NOT remove any existing content or images from this file. You MUST include both your SQL Command and Results from running that command in your response to each question. Take advantage of the “Hints†that accompany each question. The Entity Relationship Diagram (ERD) for the Colonial Adventure Tours database is shown below. You need this diagram to know the table and attribute names. 1. (4 pts) List the guide number, last name, first name and hire date of each guide. Mod1218 Put your SQL command and your results here: 2. (4 pts) List the trip name of each trip that has the type Biking. Put your SQL command and your results here: 3. (4 pts) List the last name and state of each guide that lives in Massachusetts (MA). Put your SQL command and your results here: 4. (4 pts) List the last name and state of each guide that does NOT live in Massachusetts (MA). Put your SQL command and your results here: 5. (4 pts) List the trip name and distance of each trip that has the type Hiking and that has a distance longer than 10 miles. Put your SQL command and your results here: 6. (4 pts) You are interested in a biking trip in the early fall for more than 10 individuals. List all the columns for those trips that meet your requirements. Put your SQL command and your results here: 7. (4 pts) List the reservation ID and trip price of those reservations that have a trip price that is greater than or equal to $40 but less than or equal to $60. Put your SQL command and your results here: 8. (4 pts) List the customer number, customer last name, customer first name , city and state of each customer that lives in New Jersey (NJ), New York (NY) or Pennsylvania (PA). Use the IN operator in your command. . Put your SQL command and your results here: 9. (4 pts) Repeat the previous question and sort the records by state in ascending order and then by customer last name in descending order. Put your SQL command and your results here: 10. (4 pts) How many trips are in the state of Massachusetts (MA)? Put your SQL command and your results here: 11. (4 pts) How many trips originate in each state? Put your SQL command and your results here: 12. (4 pts) Find the name of each trip containing the word “ Pond â€. Put your SQL command and your results here: 13. (4 pts) Colonial Adventure Tours calculates the total price of a trip by adding the trip price plus other fees and multiplying the result by the number of persons included in the reservation. List the reservation ID, trip ID, customer number, number of persons and total price for all reservations where the number of persons is greater than four. Use the column name TOTAL_PRICE for the calculated field. Put your SQL command and your results here: 14. (4 pts) List the guide’s last name, first name and hire date for all guides that were hired before June 10, 2013. Order your results by the hire date. Put your SQL command and your results here: 15. (4 pts) What is the minimum distance and maximum distance for each type of trip? Put your SQL command and your results here: 16. (4 pts) Display the different seasons in which trips are offered. List each season only once. Put your SQL command and your results here: 17. (4 pts) List the trip IDs for paddling trips. Put your SQL command and your results here: 18. (4 pts) List the reservation IDs for reservations that are for a paddling trip. You must use a subquery. Put your SQL command and your results here: 19. (4 pts) What is the total number of persons for all reservations. Put your SQL command and your results here: 20. (10 pts) On this last question you will be writing your own question and generating the SQL command to answer that question. In addition to the typical SELECT clause and FROM clause you must also include a WHERE clause with an AND clause. Have fun with this one. Put your question here: Put your SQL command and your results here: MSAccess - Assignment 2 – Chapter 4 Worksheet (86 pts) Instructions : Add your SQL command and results (using copy/paste and screen capture) to each question. Do NOT remove any existing content or images from this file. You MUST include both your SQL C ommand and Results from runnin g that command in your response to each question. Take advantage of the “ Hints †that accompany each question. The Entity Relationship Diagram (ERD) for the Colonial Adventure Tours database is shown below. You need this diagram to know the table and attribute names. MSAccess - Assignment 2 – Chapter 3 Worksheet (34 pts) Instructions : Add your SQL statements (using copy & paste) and results (using screen captures) to each question. Do NOT remove any existing content or images from this file. 1. (5 pts) Create a table named ADVENTURE_TRIP. The table has the same structure as the TRIP table. Mod1218 Your SQL CREATE statement to create the ADVENTURE_TRIP table here is shown below: CREATE TABLE ADVENTURE_TRIP (TRIP_ID NUMBER PRIMARY KEY, TRIP_NAME CHAR(75), START_LOCATION CHAR(50), STATE CHAR(2), DISTANCE NUMBER, MAX_GRP_SIZE NUMBER, TYPE CHAR(20), SEASON CHAR(20)); As shown in the following image right click the newly created ADVENTURE_TRIP table, select “Design Viewâ€. Result should match the following: Place your results here: 2. (5 pts) Add the following row to the ADVENTURE_TRIP table: trip ID: 45; trip name: Jay Peak; start location: Jay; state: VT; distance: 8; maximum group size: 8; type: Hiking and season: Summer. Note: Strings are case sensitive. For example, enter Jay Peak (not JAY PEAK) as the TRIP_NAME. Hint: Your results should match these results. Place your SQL INSERT statement here: Screen capture the MS Access window that shows the results of running the following SQL command: SELECT * FROM ADVENTURE_TRIP; 3. (4 pts) Delete the ADVENTURE_TRIP table. Screen capture the MSAccess window that shows the SQL command and the results of running the command that deletes the ADVENTURE_TRIP table. Place your SQL command here: Capture a screen image of all your tables (note: the ADVENTURE_TRIP table will no longer be listed). Hint: Your results should match these results. 4. Accomplish a screen capture of the description of EACH of the five Colonial Adventure Tours tables (CUSTOMER, RESERVATION, TRIP, TRIP_GUIDES, GUIDE). As you did in the last part of Question 1, right-click each table name, select “Design View†and screen capture the results. a. (2 pts) CUSTOMER table Place your results here: b. (2 pts) RESERVATION table Place your results here: c. (2 pts) TRIP table Place your results here: d. (2 pts) TRIP_GUIDES table Place your and results here: e. (2 pts) GUIDE table Place your results here: 5. Accomplish a screen capture of the contents of EACH of the five Colonial Adventure Tours tables (CUSTOMER, RESERVATION, TRIP, TRIP_GUIDES, GUIDE). You only need to show up to the first 10 rows of each table. Use the “SELECT†SQL command. Hint: Check out the bottom of page 74 and Figure 3-21 in your eBook. a. (2 pts) CUSTOMER table Place your SQL command and results here: b. (2 pts) RESERVATION table Place your SQL command and results here: c. (2 pts) TRIP table Place your SQL command and results here: d. (2 pts) TRIP_GUIDES table Place your SQL command and results here: e. (2 pts) GUIDE table Place your results here: