IT 111 Database Design and SQL

Assignment 8

Instructions:  PROBLEM 7

Based on your solution for PROBLEM 1.1 and the solution that is posted in Week 3 – Weekly Activities, do the following:

  1. Create the actual database of the design in Microsoft SQL Server
  2. Use the provided template to begin.
  3. Add all DROP tables as instructed
  4. Write the CREATE SQL statements to create each table.   In doing so, add the constraints to add the primary keys to each table.
  5. Ensure each column that is added to each table has the correct and appropriate data type.
  6. Once complete with each table, add at least 3 rows of different data to each table.  Ensure that the PK in each table is unique.  Start and 1 and add 1 to each PK for each table.
  7. Write the following SELECT SQL Statements:
    1. Write a Select SQL statement to return all rows from the Songs Table
    2. Write a Select SQL statement to return all customers from the Customers Table for customers with a specific last name.
    3. Write a Select SQL statement to return all rows from the Songs Table for songs recorded after a specific date (based on your data).
  8. Write the following UPDATE SQL Statements.  Include a select statement before and after each update statement to check if the statement worked as expected.
    1. Write an Update SQL Statement to change the City for a specific customer.
    2. Write an Update SQL Statement that will change a specific Record Label to a new name.
  9. Write the following DELETE SQL statements:   Include a select statement before and after each delete statement to check if the statement worked as expected.
    1. Write a Delete SQL Statement to delete a specific customer
    2. Write a Delete SQL Statement to delete all songs for a specific record label

 

Complete all using Microsoft SQL Serve  and upload the .sql file via the assignment manager.  

 

 

INSTRUCTION: PROBLEM 8

Based on your solution for PROBLEM 2.2 and the solution that is posted in Week 3 – Weekly Activities, do the following:

  1. Create the actual database of the design in Microsoft SQL Server
  2. Use the provided template to begin.
  3. Add all DROP tables as instructed
  4. Write the CREATE SQL statements to create each table.   In doing so, add the constraints to add the primary keys to each table.
  5. Ensure each column that is added to each table has the correct and appropriate data type.
  6. Once complete with each table, add at least 3 rows of different data to each table.  Ensure that the PK in each table is unique.  Start at 1 and add 1 to each PK for each row inserted into each table.
  7. Write the following SELECT SQL Statements:
    1. Write a Select SQL statement to return all rows from the Vendors Table
    2. Write a Select SQL statement to return all customers from the Customers Table that have a specific zip code.
    3. Write a Select SQL statement to return all rows from the Products Table that have a retail price less than a specific price (based on your data).
  8. Write the following UPDATE SQL Statements.  Include a select statement before and after each update statement to check the results.
    1. Write an Update SQL Statement to change the zip code for all customers in a specific city.
    2. Write an Update SQL Statement that will change the contact information for a specific vendor.   Make sure to change all 4 columns of contact data.

Write the following DELETE SQL statements.  Include a select statement before and after each delete

Instructions: problem 9

Based on your solution for Problem 7 and the solution that is posted in Week 4 – Weekly Activities, do the following:

  1. Based on the design of your database, add any additional tables that may be needed to complete relationships.
  2. Add the foreign keys that correspond to the related primary keys to establish the necessary relationships for all tables.
  3. Write the necessary ALTER commands to establish referential integrity between the related tables.
  4. Order your DROP Tables appropriately to eliminate potential for errors.
  5. Once complete with the above, insert 5 rows in any new tables created.
  6. Once complete with above, modify your insert commands to populate the foreign keys that were added.
  7. Write the following SELECT SQL Join Statements:
    1. Write a Select SQL statement to return all rows and all columns displaying the customer’s information and their favorite songs and related information.   Be sure to specify the columns that you would like to select.  Do not use SELECT *.
    2. Make a copy of the query from ‘7a’.   Modify the copy to only return data for a specific customer from the Customers Table based on last name.
    3. Write a Select SQL statement to return all rows and all columns displaying the artist’s information and their songs and related information.   Specific the column names.  Do not use SELECT *.
    4. Make a copy of ‘7c’.   Modify the copy to only select songs recorded after a specific date.

 

Place New Order
It's Free, Fast & Safe

"Looking for a Similar Assignment? Order now and Get a Discount!