Database Design and Development

Homework $3$

Create the SQL query as requested for each question. Be certain to follow the formatting illustrated in each question and use column aliases as shown. Note that you are provided with sample data only. Your code must work correctly even if the underlying data is updated $-$ in other words, don't look up values in the data to use in your code, let the code do the work. The data you are using is static $-$ it is not being updated by the users.

The assignment is individual effort only. You cannot talk to each other or anyone else regarding the problems except the instructor. Collaborating with anyone else, including "homework help" websites, in any way is a violation of the class academic misconduct policy $($see the syllabus$).$

NO CREDIT will be given for code that:

Is developed using a tool other than SQL Server Management Studio $($SSMS$)$

Is not developed in your assigned database

Uses functions, keywords, or techniques not covered in this class $($The assignment is a test of your mastery of the material we covered in class. Check the NOT ALLOWED LIST in D$2$L for common mistakes.$)$

Queries that return an error when executed

Queries that contain a Cartesian product $($watch the video about Cartesian products$)$

Queries that contain a subquery anywhere other than FROM or WHERE.

All queries must be terminated with a semicolon.

Do not use unnecessary subqueries.

Submission is to be made through Desire$2$Learn $($D$2$L$).$ Upload your submission with a $.$sql extension $($do NOT put it in an MS Word document, do NOT turn in a zip file$)$ to the Homework $3$ dropbox in D$2$L$.$

You must create the TOM schema in your database and copy the tables into the TOM schema of your database from the TOM schema of the STARTERDB database. Do NOT make up your own tables or make up your own data $-$ only use the table structures and data that already exist on our server.

Be certain to use the column aliases and formats illustrated in the sample output shown for each problem.

Use comments $($either a double dash $--$ or a block comment $/**/)$ to number your answers with the appropriate question number. You do not need to re$-$write the question. Do not start your answer on the same line as the comment that numbers the answer.

Use a comment at the beginning of the text file to include your name in the file. Nothing but your name should be on the first line $($e$.$g$.,--$John Smith$).($Hint: your name does not start with "Name:"$)$

Remove all extra commands $($like SELECT $*$ FROM TOM.CREW;$)$ that you used as you were crafting your answers. Points will be deducted for extra commands left in the file.

Include your code for Question $0$ in your answer file.

Only submit one query per question. If there is more than one query for a question, then only the first query will be evaluated.

Preview

File

Edit

View

Go

Tools

Window

Help

marur

Homework $3.$pdf

Page $2$ of $8$

Top $O^{\text{'}}$ 'the Morning $($TOM$)$ is a company that provides custom stone countertops for kitchens, bathrooms, and other areas of a home or business. The company is providing you access to sample data for a small area of their overall database. Read the brief description below to help you understand the data. The data were captured in $2024.$

TOM ERD

Explanation of the data model:

The EMPLOYEE table contains a portion of the data kept on employees that work for TOM doing installation of purchased products. The employees are organized into crews each day that they work. A crew is typically comprised of a leader and one or more assistants. The leader is responsible for the quality of the installation work done by the crew that day. Employees are not always part of the same crew $-$ crews change from day to day. The TRUCK table contains some of the data about the box trucks that crews use to deliver products to the installation site. The stone countertops are very heavy $($usually $300-400lbs$ each$)$ so care must be taken to ensure the weight capacity of the trucks is not exceeded. Each day a truck can be assigned to deliver multiple products and the group of employees that are in a crew that day. The truck usually has multiple products loaded each assigned day. The products may be destined for one job site or multiple job sites. The crew drives the truckload of products to the site and installs the products that were ordered for that job site. Each product is a stone countertop. The product has a description to help the crew distinguish between the products on their truck. The weight of the product and how many minutes the crew is estimated to need to install the product is also recorded. There is also a description of the installation location for each product so the crew can be assured of putting each product in the correct location within the building. The SITE table has data about the job sites where products are to be installed, and who to contact about any issues with accessing the site,

Page $2$ of $8$