Please help me answer ALL PARTS of my homework for ITM-315-Database Systems PLEA

Question

Please help me answer ALL PARTS of my homework for ITM-315-Database Systems

PLEASE WRITE THE QUIRES CODES FOR SQL USING MICROSOFT ACCESS

Instructions

HOTEL SPRING 18 DATABASE

1. Word document with gradesheet (see p 2)and data model (part la). 2. Access database with PARK table. 3. hotel spring 18 database modified to include queries 1-7 1. A small city government would like your help developing a data model and database for local parks. Draw a data model (ERD) for a PARK entity with the attrbutes: ParkID, ParkName, Park Address, and ParkEstablishDate. Add 2 more attributes that you think the city practice might like to record for each park. Copy this entity into the grade sheet on page 2 BELOW the grading sheet and Submit this as a Word document named using the following convention LastNameHMWKIModel (fullerHMWKIModel). Create a table (in a new database) in MS ACCESS for the PARK entity and attributes described above. Make sure to choose an appropriate data type for each field. Save your database using the convention LastNameHMWKIDB (e.g., FullerHMWKIDB). Make sure to designate the primary key Add data to your table. Add 3 records (rows) to your database. You should incorporate your last name in the name of one of the parks. Aside from your name, the rest of the data should be fictitious. a. b. c. 2. Using Access and the hotelSpring18 database, code the SQL for the following queries. You will create and save each query separately and name the queries query 1 through query 7 Please be sure to save each query under the appropriate name. The query you save under query 1 will be graded as query 1, etc. Each problem must be answered with a single query om a single table as demonstrated in Chapter 3. Do not change any of the data in the hotel spring 18 database. Rename this database to include your last name (e.g., hotel spring 18 Fuller) and submit. Queryl: List the first name, last name and street addresses of any guests that have a street name that includes "Main" Query 2: Which guests (by number-do not add additional columns to your answer), booked rooms for less than S100. Order the result in ascending order by guest number. If any guest has booked more than 1 room for less than $100, only list that guest one time. Query 3: List all of the information for rooms in hotel 4. Query 4: List the bookings (by hotelNo and roomNo) in hotel 5 that began during may 2014 Query 5: How many times has guest 8 booked rooms in hotel 2? Query 6: What is the average standard price of rooms in hotel 2? Label the resulting column as "Hotel2Average"

Explanation / Answer

--Query 1
SELECT guestfirst,guestLast,gueststreet FROM guest
Where gueststreet like '%main%';

--Query 2
SELECT guestNo FROM booking
WHERE rateBooked < 100
GROUP BY guestNo;

--Query 3
SELECT roomNo,type,standardprice FROM room WHERE hotelNo=4;

--Query 4
SELECT hotelNo,roomNo FROM booking
WHERE hotelNo=5 and dateFROM >= '1/1/2016';

--Query 5
SELECT COUNT(*) FROM booking WHERE hotelno=2 and guestNo=8;

--Query 6
SELECT AVG(standardprice) AS Hotel2Average FROM room
where hotelno=2;

--Query 7
SELECT hotelName,hotelCity From hotel
WHERE hotelCity NOT IN ('Tulsa','Stillwater');

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.