- Database assignment question
Database assignment question
Read the database description information at the conclusion of these instructions to understand your tables and relationships among them.
- In your e-book on your student menu tab, select Advanced Query Editor from the drop down list to open your query editor. In the lower right, set the Database to SQLServer at MyEducator and Credentials to Your Student Account.
- Create a Notepad file named SQL1_Yourname that contains only the SQL commands needed for numbers 3-14. Do NOT put any comments or anything else in this file. Skip two blank lines between each answer (if an answer requires more than one SQL statement, you may skip 1 line between them).
- Put all the drop table commands needed to execute your create table statements more than once first in your file.
- Copy, paste, and execute the create table and insert statements to set up your tables and records in your database. These commands are located in the .txt file attached to this assignment in D2L.
- Add the following 2 records to the Owner table.
SM72 Smeltz Becky and Dave 922 Garland Glander Bay FL 31044
TR72 Trent Ashton 922 Crest Bay Shores FL 30992
6. Add the following 2 records to the Marina_Slip table.
8 2 3 25 2000.00 Listy Dolphin 25 SM72
11 2 6 40 4200.00 Karvel Ray 4025 TR72
7. Add the following 3 records to the Service_Request table.
5 3 5 ‘Open pockets at base of two stantions.’
‘Technician has completed the initial filling of the open pockets. Will complete the job after the initial fill has had sufficient time to dry.’ 4 2 July 13, 2017
7 6 2 ‘Engine overheating. Loss of coolant. Diagnose and repair.’
‘Open’ 2 0 July 13, 2017
8 6 2 ‘Heat exchanger not operating correctly.’
‘Technician has determined that the exchanger is faulty. New exchanger has been ordered.’
4 1 July 17, 2017
- You have discovered that the next service dates are incorrect on several records in the Service_request table. Update all records that have 2010 in the next service date to 2017.
- Display the SlipID, Description, Status, and Next service date for all service requests with a next service date before July 17, 2017.
- Display the owner names of owners with boats in the Alexamara Central marina.
- Display the owner names, boat names, and slipID for all the boats in the Alexamara East marina.
- Display the service category description, service description, status, boat name, and owner names for all the boats with an Open service request status whose next service date is in July 2017 in alphabetical order by owner name.
- List the owner names, boat names, rental fees, and marina name for all boats with rental fees more than 2000 and not more than 3600 in alphabetical order by owner within each marina.
- List the owner name (in alphabetical order), boat name, and rental fee for all boats with a fee less than 2000 or owned by someone from Bowton, FL who has a Sprite 4000 boat type.