Write create table statements for each of these tables

Assignment Help Database Management System

Reference no: EM132670547

As the basis for the SQL statements you will create in the exercises that follow. If possible, run these statements in an actual DBMS, as appropriate, to obtain your results. Name your database GARDEN_GLORY.

Use data types consistent with the DBMS you arc using. If you are not using an actual DBMS, consistently represent data typcs using either the MySQL, Microsoft SQL Server, or Oracle Database data types shown in Figure 3-5. For each SQL statement you write, show the results based on your data.

Write SQL statements and answer questions for this database as follows:

A. Write CREATE TABLE statements for each of these tables. Omit foreign keys.

B. Write foreign key constraints for the relationships in each of these tables. I\lake your own assumptions regarding cascading updates and deletions and justify those assump-tions. (Hint: You can combine the SQL for your answers to parts A and B.)

C. Write SQL statements to insert the data into each of the five Garden Glory database tables. Assume that any surrogate key value will be supplied by the DBMS. Use the data in Figure 3-38, Figure 3-39, Figure 3-40, Figure 3-41, and Figure 3-42.

D. Write SQL statements to list all columns for all tables.

E.. Write an SQL statement to list Last Name, FirstName, and CellPhone for all employees having an experience level of Master_

F. Write an SQL statement to list LastNamc. FirstName, and CellPhone for all employ¬ees having an experience level of Master and FirstName that begins with the letter J.

G. Write an SQL statement CO list LastName, FirstName, and CellPhone of employees who have worked on a property in Seattle. Use a subqucry.

H. Answer question G but use a join using JOIN ON syntax. What arc the consequences of using (or not using) the DISTINCT keyword in this version of the query?

I. Write an SQL statement to list ListNamc, FirstName, and CellPhone of employees who have worked on a property owned by a corporation.. Use a subqucry.

J. Answer question I but use a join using JOIN ON syntax. What are the consequences of using (or not using) the DISTINCT keyword in this version of the query?

K. Write an SQL statement to show the LastName„ FirstName, CcllPhonc, and sum of hours worked for each employee.

L. Write an SQL statement to show the sum of hours worked for each ExperienceLevel of EMPLOYEE_ Sort the results by ExperienceLevel, in descending order_

M. Write an SQL statement to show the sum of HoutsWorked for each type of OWNER but exclude services of employees who have ExperienceLevel of Junior_

N. Write an SQL statement to modify all EMPLOYEE rows with ExperiencrLcvel of Master to SIIIperMaStCr.

0. Write SQL statements to switch the values of ExperienceLevel so that all rows cur¬rently having the value Junior will have the value Senior and all rows currently having the value Senior will have the value Junior. (Hint: Use the value Unknown as a tempo¬rary third value.)

P. Given your assumptions about cascading deletions in your answer to part B. write the fewest number of DELETE statements possible to remove all the data in your database but leave the table structures intact. Do not run these statements if you arc using an actual database!

Attachment:- DBMS_1Binder.rar

Reference no: EM132670547

Questions Cloud

Which of the stocks should be transferred as gifts : Which of the stocks should be transferred as gifts and which as inheritances? Summarize your answer in a two-to three-paragraph e-mail to your instructor

Evaluate who volunteer with the center for plain language : Evaluate who volunteer with the Center for Plain Language to review and grade federal websites as part of the plain language Federal Report Card.

What were the professionals legal obligations : What were the professional's legal obligations? Was the law upheld? Do you think the situation in the article was handled appropriately? Why or why not?

How was the experience portrayed to the audience : How was this experience portrayed to the audience? How did this experience impact a relationship that the individual who was experiencing the loss

Write create table statements for each of these tables : Write CREATE TABLE statements for each of these tables. Omit foreign keys and Write foreign key constraints for the relationships in each of these tables

Describe kubler-ross stage theory of the adjusting process : Describe Kubler-Ross's stage theory of the adjusting process in accepting the death of a loved one and explain how societal concerns would impact the stages.

How lines of communication with employees is developed : Create a matrix that compares how both organizations execute the following compliance components. How lines of communication with employees is developed

Did the auto-ethnography provided in the reading teach : What can research done in this form teach us, not only about grief but about other concepts that may be discussed in this manner?

Calculate Surendra basis for loss : Surendra's personal residence originally cost $340,000 (ignore land). As to the rental property, calculate Surendra's basis for loss

User Account

All Pages