You need to use SQL Server at MyEducator with Credentials set as your Student Ac
ID: 3721700 • Letter: Y
Question
You need to use SQL Server at MyEducator with Credentials set as your Student Account through the Advanced Query editor to do this assignment. Be sure to format and name columns as appropriate.
Save your work as instructed below and turn in:
1. A printout of each query (4-21), both your input and the results, where needed. Staple the above printouts in order and handwrite #4-21 on each (if you click print on the browser and change the layout to portrait, it should give you what you need to printout).
2. Submit one Notepad file (containing all queries 4-21 only through D2L (make sure it runs).
3. Run the script file to create all the tables and insert all the records for Alexamara Marina Group (attached to this assignment in D2L. There is another drop table script file attached if you need it.) Do not include this information in the Notepad file you submit.
4. Modify Marina_Slip to have foreign keys. Marina_Num should reference Marina and Owner_Num should reference Owner. For both, on update cascade and on delete no action.
5. Modify Service_Request to have foreign keys. Category_Num should reference Service_Category and Slip_ID should reference Marina_Slip. On update cascade and on delete no action.
6. List the slip ID, boat name, owner name, category description, and number of hours estimated on each service request for Electrical systems.
7. How many Sprite boats are stored at each marina? List the Marina number and number of Sprite boats.
8. For every service request category, list the category description, the number of requests for that category, the average estimated hours, and the average spent hours (only include spent hours in the average when the number of spent hours is not 0) – be sure to format appropriately.
9. List the names for all owners, in alphabetical order, who have a boat in a slip that is larger than the average slip.
10. List the marina name, length, and calculate the total rental fees Alexamara receives each year based on the length of the slip for each marina.
Do numbers 11-21 below. After each change is made, execute an appropriate query to show that the change was made correctly. In other words, for number 11 you should have the create and alter table commands, and for number 12, have the insert command as well as the select command to show the records.
11. Create a LargeSlip table with the structure shown below. Be sure to specify what happens if a corresponding record with a PK is updated or deleted.
Column Name
Type
Length
Dec. Places
Nulls?
Description
MarinaNum
Char
4
No
Marina No – PK, FK-Marina
SlipNum
Char
4
No
Slip No in the marina - PK
RentalFee
Dec
8
2
Annual slip rental fee
BoatName
Char
50
Name of boat currently in slip
OwnerNum
Char
4
Number of boat owner renting slip, FK - Owner
12. Insert into the LargeSlip table the record information for all boat/owners whose length is 40 feet.
13. Decrease the rental fee in LargeSlip by one percent for any slip whose fee is greater than $3000.
14. Insert the information for the following boat into LargeSlip:
1, A4, $3900, Bilmore, FE82
15. Delete all slips in the LargeSlip table for which the owner number is TR72.
16. The boat in Marina 1, slip A1 is changing its name. Change the boat name in LargeSlip to null.
17. Add a new column called Charter that is one character to LargeSlip and set all the values to N.
Drop the LargeSlip table.
Create a view named LargeSlipView (same info as 11 & 12 above)
List all the information from LargeSlipView for all rental fees greater than $3500.
Delete the LargeSlipView
Should you/can you update tables through the LargeSlipView? Why or why not? (You may answer this in the comments when you upload in D2L, rather
Column Name
Type
Length
Dec. Places
Nulls?
Description
MarinaNum
Char
4
No
Marina No – PK, FK-Marina
SlipNum
Char
4
No
Slip No in the marina - PK
RentalFee
Dec
8
2
Annual slip rental fee
BoatName
Char
50
Name of boat currently in slip
OwnerNum
Char
4
Number of boat owner renting slip, FK - Owner
Explanation / Answer
4. aleter table Marina_Slip add foreign key(Marina_Num) references Marina(MarinaNum) on update cascade on delete no action, add foreign key(OwnerNum) references Owner(OwnerNum) on update cascade on delete no action;
5.alter table ServiceRequest add foreign key(CategoryNum) references ServiceCategory(Category_Num) on update cascade on delete no action, add foreign key(SlipID) references MarinaSlip(SlipID) on update cascade on delete no action;
To add the foreign keys to the already existing tables we need to use alter query with add command and then the constraint required so that the constraint will be added.
6.select ms.SlipID,ms.BoatName,o.OwnerName,sc.CategoryDescription,sr.EstHours from MarinaSlip ms,Owner o,ServiceCategory sc,ServiceRequest sr where ms.OwnerNum=o.OwnerNum and sc.CategoryNum=sr.CategoryNum and sr.SlipId=ms.SlipID and sc.CategoryDescription='Electrical systems';
The above query will match all the required category numbers and slipid and owner number from different tables and the category type electrical systems it will display the given reuired fields.
7. select m.MarinaNum,count(ms.BoatType) from Marina m,MarinaSlip ms where ms.MarinaNum=m.MarinaNum and ms.BoatType like 'Sprite%' group by m.MarinaNum;
It will display the marina num with no of boats parked there with the boat name starting with sprite.
As per the chegg policy I am allowed to answer only 4 parts . Kindly understand and post the reamining as new post.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.