SQLServer

Need Help in SQL Server Assignment?
We can help you if you are having difficulty with your SQL Server Assignment. Just email your SQL Server Assignment at admin@assignmentcache.com.
We provide help for students all over the world in SQL Server Assignment.

Items 1 to 10 of 30 total

per page
Page:
  1. 1
  2. 2
  3. 3

Grid  List 

Set Ascending Direction
  1. ITCO231 Unit 5 HR Database Tables

    ITCO231 Unit 5 HR Database

    Regular Price: $25.00

    Special Price: $20.00

    ITCO231 Unit 5 HR Database


    Update the database diagram, and generate the data definition language (DDL) for the Contacts table and any others that you wish according to the following diagram. Be sure to define the appropriate relationship between the Contact table and the Employee table.


    Add data to your database and validate that your records loaded properly. In tabular format, include 3 rows for each table, making sure that the primary–key and foreign–key relationships are properly applied.
    Next, you will insert the 20 rows of data that you identified (using the concepts you worked on identifying the primary and foreign keys), then perform queries using JOIN syntax of the database.
    Task 1: Create 3 rows of data for each table ensuring that the referential integrity is valid.
    Task 2: Add the 20 rows of data to the appropriate table in your database (using any appropriate method available).
    Task 3: SELECT all columns and all rows of the tables. Create a screenshot of each query and output data, and submit them.
    Task 4: Write SELECT statements for the following (include a screenshot of the SQL and its execution, including the resulting data):
    Display the employee id, contact id, first_name, middle name, last_name, and phone number for all employees.
    Rows returned
    Display the employee id, contact id, first_name, middle name and last_name, and email address for all employees.
    Rows returned
    Display the employee id, contact id, first_name, middle name, last_name, phone number, and email address for all employees.
    Rows returned
    Combine all of the DDL/SQL statements (text only), diagram and screenshots into a single Word document. Submit it for grading.

    Learn More
  2. POS 410 Week 3 Data Changes and SQL Statements

    POS 410 Week 3 Data Changes and SQL Statements SQL for Business

    Regular Price: $12.00

    Special Price: $10.00

    POS 410 Week 3 Data Changes and SQL Statements SQL for Business

    Use the Kudler_FF database to build a foreign key.  The foreign key should be built on the job title column in the employee_tbl, which references the job title column in the job_tbl.  An “alter table” statement will be required to build this FK on the employee_tbl.

    Deliverable: Copy (screen image) the SQL statement used to build the foreign key and the associated output message from its execution into a Word document and post them to the assignment tab of the classroom.

    Use the Kudler_FF database to write SQL statements and enter records into the Employee table for workers identified in the employee files for the administrative offices and the DelMar store. Check results by selecting all columns from both tables.

    Deliverable: Copy (screen image) the SQL used to check your results and the output to a Word document and post to the assignment tab of the classroom.  

    Use the tables in the Kudler_FF database to write the following SQL queries:
    o Write a SQL query that joins the two tables in the Kudler_FF database, and displays the Employee Last Name, First Name, Hire Date, Area Code, Job Title, and Exemption Status.
    o Write a SQL query that joins the two tables in the Kudler_FF database and uses BETWEEN to restrict record selection; use hire dates to restrict data.
    o Write a SQL query that joins the two tables in the Kudler_FF database and uses LIKE to restrict record selection; use telephone area codes to restrict data.
    o Write a SQL query that joins the two tables in the Kudler_FF database, and restricts the record selection to an exemption status.
    o Write a SQL query that uses the UNION of the two tables to produce the results set

    Deliverable: Copy (screen image) each query and the results set to a Word document and post them to the assignment tab of the classroom.  

    Refer to the materials forum for the rubric that will be used to grade this assignment.

    Learn More
  3. Ski Club Skiing Database Using SQL Management Studio

    Ski Club Skiing Database Using SQL Management Studio

    Regular Price: $15.00

    Special Price: $12.00

    Ski Club Skiing Database Using SQL Management Studio

    Using SQL Management Studio, create the normalized tables, fields and relationships between the tables for the Skiing Database scenario below.

    You have been contracted by the president of the Ski Club to design a Skiing Database:
    The president has asked you to design a database to keep track of member and skiing resort information.
    For each member of the club, she wants you to track name, address, phone number, age and skiing ability.
    For the various ski mountains you need to track the name of the resort, its location, # of trails, # of lifts.
    The president also wants a monthly report of skier activity including the skiers name, phone number, skiing ability, Ski Resort visited, the date of the visit and general comments on the skiing there.

    Don't add any additional fields other than what is being requested. That does not mean you should not break down each field to it's smallest possible component.
    Create the database using SQL Management Studio.
    Detach the completed Database file and submit both the .mdf and .ldf files to the assignment in a zipped folder with your firstname followed by the initial of your lastname.

    Learn More
  4. IT452 Unit 4 Sub Queries

    IT452 Unit 4 Outer Joins and Subqueries

    Regular Price: $20.00

    Special Price: $15.00

    IT452 Unit 4 Outer Joins and Subqueries

    a) List all order ID's for the customer named 'Customer LOLJO'. You must use a subquery.

    b) List the lastname and firstname of all employees who have sold to customer 'Customer LOLJO'. Use a nested subquery (no joins).

    c) List the empid, lastname, and firstname of all employees who are managers. (If an employee is a manager, his/her empid will appear as someone else's mgrid.) Use a subquery. (HINT: The subquery will list all the distinct mgrid values.)

    d) List the empid, lastname, and firstname of all employees who did not take an order in the month of February, 2007. Write an uncorrelated subquery that uses NOT IN.

    c) List the empid, lastname, and firstname of all employees who did not take an order in the month of February, 2007. Write a correlated subquery that uses NOT EXISTS.

    e) List the empid, lastname, and firstname of all employees who did not take an order in the month of February, 2007. Use an outer join.
    (HINT: First, write an inner join that returns all the (distinct) employees who *did* take an order during that month. Next, change WHERE to AND so that the predicate is part of the ON clause of the join. Finally, change your join to an outer join, using the WHERE clause to filter for only non-matching rows.)

    f) Write a subquery that returns the first order placed in the orders table

    g) Using a scalar subquery (returns only 1 row) to outer query, write a query to returns the customer id for orders placed by the sales rep Judy Lew

    h) Select the EmployeeID, lastname, firstname for the employee that had sales but does NOT have a manager. (requires Inner Join and subquery)

    i) Find the customer, shipcountry and number of orders that the customer made where the customer is not located in the United States. (Requires a Count, subquery and condition)

    Learn More
  5. IT452 Unit 5 Table Expressions

    IT452 Unit 5 Table Expressions

    Regular Price: $20.00

    Special Price: $15.00

    IT452 Unit 5 Table Expressions

    1. What is the average number of order line items over all orders? The Sales.OrderDetails table has one row for each product for each order. Each row represents a "line item" on a sales invoice for the orderid. Start by writing a query that returns the number (COUNT) of line items (rows) for each orderid. Modify that query to CAST the line item count as decimal (8,4). Then use that query as a derived table to get the average number of line items over all orders.
    (Returns 9 rows)

    2. Create a view named Sales.LOLJO_Orders that lists all the orderid values for orders placed by customer 'Customer LOLJO'. (Requires an Inner Join)
    (Returns 10 rows)

    3. Create a view named Sales.WhoSoldToLOLJO that lists the empid, lastname, and firstname of all employees who have sold to customer 'Customer LOLJO'. Execute a Select query to show the results of the view.
    (6 rows)

    4. Write a CTE to extract the employee with the most orders. Write the Outer query to display the EmployeeID and Highest Orders Sold Count for that Employee Result: (empid 4, 156 orders)

    5. Create an inline table-valued function named Sales.fCustOrders that takes a customer name as an argument and returns the orderid and orderdate values for orders placed by that customer. The orderdate should have the date only, no time (Hint: CAST). Your project should have your CREATE FUNCTION statement. Query the function to see the results from the function for the argument (page 165) for companyname 'Customer MLTDN'.
    (4 rows)

    6. Create a VIEW called TopSales that shows the TOP 10 percent of sales. Verify the results of the view in a SELECT clause.
    (216 rows)

    7. Encrypt the TopSales View and bind the schema so that it cannot be dropped or altered created in #6. Test it by trying to remove the producID from the underlying Table and show the error

    8. Change the TopSales view from # 7 so that it contains a filter that only has qty = 130 and does not allow modifications. Test it with an Update Statement by trying to change the quantity.

    Learn More
  6. IT452 Unit 6 Set Operations and Data Modification

    IT452 Unit 6 Set Operations and Data Modification

    Regular Price: $20.00

    Special Price: $15.00

    IT452 Unit 6 Set Operations and Data Modification

    1. List the contact name, contact title, address, city, region, postal code, and country of all customer and supplier contacts. Sort the results by country, then by region, then by city. Use UNION ALL. 120 rows returned

    2. List the city, region, and country that have both a customer and a supplier. Use INTERSECT. 4 rows returned

    3. Create a table in the tempdb database using the following CREATE TABLE statement:
    USE tempdb; CREATE TABLE dbo.DOGS ( DogID int IDENTITY NOT NULL ,Name varchar(20) NOT NULL ,BirthDay date ,Alive char(1) NOT NULL -- Either 'Y' or 'N' ); GO
    Write one INSERT statement with one VALUES clause to insert information about the following dogs:
    Name Birthday Alive
    Samantha 1993-03-17 Passed away Feb 2009 (Alive = 'N')
    Misty 1993-06-20 No longer alive
    Henry the 1/8th 2003-10-21 Alive
    (Note: a small dog) Inka 2006-09-18 Alive
    Result: (4 row(s) affected) Paste your INSERT statement into your Assignment document.

    4. Use SELECT … INTO … syntax to create a Vendor table (name the table Vendor) in the tempdb database that has the exact structure and content as the purchasing.vendor table. Validate that the new vendor table was created in the tempdb (using SQL Query) and that all rows were inserted. Paste both queries into your project document. (104 rows returned)

    5. Write a DELETE statement that removes the vendors from the tempdb.vendor table that have the PreferredVendorStatus flag turned on. Result: (11 row(s) affected)

    6. The tempdb.vendor table is no longer needed. Please truncate it. Verify the table has been emptied using a SQL Query.

    7. Run the following code to create the tempdb.dbo.DimProducts table:
    USE tempdb;
    CREATE TABLE [dbo].[DimProducts]( [dimProdID] [int] NOT NULL,
    [ProductID] [int] NOT NULL, [ProductName] [nvarchar](60) NOT NULL,
    [UnitPrice] [smallmoney] NOT NULL, [BeginDate] [date] NOT NULL,
    [EndDate] [date] NOT NULL, CONSTRAINT [PK_Products] PRIMARY KEY
    CLUSTERED ( [dimProdID] ASC ) );
    GO
    Use bcp to import data into the tempdb.dbo.DimProducts table from the DimProducts.txt file. The file may be obtained from Doc Sharing.
    You should get a message that 77 rows were imported. Put a copy of your bcp command into your Assignment document.

    8. Run the following code to create the tempdb.dbo.ProductStage table:
    USE tempdb;
    CREATE TABLE [dbo].[ProductStage]( [dimProdID] [int] NOT NULL,
    [ProductID] [int] NOT NULL, [ProductName] [nvarchar](60) NOT NULL,
    [UnitPrice] [smallmoney] NOT NULL, [BeginDate] [date] NOT NULL,
    [EndDate] [date] NOT NULL );
    GO
    Use BULK INSERT to import data into the tempdb.dbo.ProductStage table from the ProductStage.csv file. That file may be obtained from Doc Sharing.
    You should get a message that 27 rows were imported. Put a copy of your BULK INSERT command into your Assignment document.

    9. Write a query that determines if there are any product names in the tempdb.dbo.ProductStage table that are not in the tempdb.dbo.DimProducts table. Use EXCEPT. Put a copy of your query into your Assignment document. In addition, put the product name(s) you returned from this query into your Assignment document.

    10. Write a MERGE statement that modifies the tempdb.dbo.DimProducts table based on the contents of the tempdb.dbo.ProductStage table. When the dimProdID values match, update the target table based on what is different in the source row. [Examine the data in both tables to determine what row(s) will need updating.] When the dimProdID values do not match, insert the source row. Paste your MERGE query into your Assignment document. It should return a message that 27 row(s) were affected. In addition, report how many rows.

    Learn More
  7. IT452 Unit: 7 Programmable Objects

    IT452 Unit 7 Programmable Objects

    Regular Price: $20.00

    Special Price: $15.00

    IT452 Unit 7 Programmable Objects

    1. Create a variable for the region column, birthdate column and empid column.

    2. Create and use a variable to pull the category name for categoryID 8. Write a separate query to return the output.

    3. Write an IF ELSE element that checks for the current month and prints ‘This Month is November’ if it is November or prints ‘This Month is not November’ if the month is not November.
    Prints This Month is November if you enter 11

    4. Write a WHILE Loop that returns ProductName, CategoryID and UnitPrice and compares the CategoryID to 4. As long as it is less than 4 it should print the product name, categoryid and unitprice.
    12 rows for Category 1
    12 rows for Category 2
    13 rows for Category 3

    5. Write a script that performs the following tasks:
    a. Define a variable to hold the maximum database ID value.
    b. Set that variable equal to the maximum database ID value.  To determine the maximum database ID value, use the database_id column of the sys.databases table in the master database.
    c. Use PRINT to print the value of the variable. Result: [Note:  PRINT writes to the Messages tab of the query window.]
    [Note:  Your result may vary.]

    6. Create a cursor that updates only to and from the ProductName column of the Production.Products table and print that column out. Returns all of the productnames upon successful execution

    7. Create a table variable called work table with the following structure. Insert the row of data listed and execute the batch.
    Worktable
    PK int Primary Key
    Col1 int Not Null
    Insert 1 row with the following data:
    1, 101
    Execute the batch
    Returns 1 row

    8. Create a global temporary table called GlobalTempWork with the following structure
    GlobalTempWork
    Test nvarchar primary key,
    Col1 int
    Insert 1 row with the following data
    10
    Execute the batch to return the 1 row

    Learn More
  8. IT452 Unit 8 Programmable Objects II

    IT452 Unit 8 Programmable Objects II

    Regular Price: $20.00

    Special Price: $15.00

    IT452 Unit 8 Programmable Objects II

    1. Write a stored procedure called sp_GetCompanyName. This procedure requiring a join should get the customer name (not individual) and the city the customer gets items shipped to for those customers that have items shipped to Madrid (this is a hint on the table it should be joined with in the query). Execute the stored procedure and submit results with query.
    Returns 2 rows

    2. Write a stored procedure called sp_GetEmployees to dynamically retrieve the city that each employee lives in when it is executed. In addition, please return the lastname and firstname. Execute the procedure and submit the code and results for employees living in London.
    Returns 4 rows  

    3. [TSQLFundamentals2008 and tempdb]  Scenario: You want to test a stored procedure that will delete the oldest orders. You will run your tests in the tempdb database.

    a. Create an exact copy of the TSQLFundamentals2008.Sales.Orders table in the tempdb database (as table tempdb.dbo.Orders) by using SELECT INTO.
    The tempdb.dbo.Orders table should have 830 rows.

    b. In tempdb, create a stored procedure named dbo.pDeleteOldestOrders that (1) determines the earliest year and month of orderdate values in the Orders table, and then (2) deletes all orders with an orderdate in that year and month. (Hint: Use YEAR and MONTH functions in subqueries to determine the year and month of the oldest order. Place these values in variables, then use the variables in the WHERE clause of your DELETE statement.)
    Should show 22 rows affected

    4. Use Northwind (create one if one does not exist) and Master  Create a rollback (prevent changes if attempt to change occurs and prints a system message and the custom message using RAISERRORThese Procs may not be altered or dropped!) Trigger called NoTouchDaProc that prevents any stored procedure from being altered or dropped in the database (Hint: in the database tells you which type of trigger).
    Submit code and printscreen and drop the trigger after getting results.

    5. Use the tempdb and using SELECT INTO create a table called Person using all of the data from the Adventureworks.Person.Contact Table.
    You should have 76 rows of individuals with the LastName Carter and 88 rows of individuals with the LastName Johnson before you begin.
    1. Create a Trigger called PersonTrigger that works on data after it is inserted into the table and checks for a change to the LastName field. If there is a change to the LastName field print to the screen 'You might have modified the LastName column'. Otherwise have it print 'The LastName column is untouched.' (Hint: IF Else would work here). Make print screens of successful completion and copy and paste your code into your submission.
    2. Change the LastName of the person with the contactID of 32. This should update 1 row and fire the PersonTrigger with the correct message. Make print screens of successful completion and copy and paste your code into your submission.
    3. Alter the PersonTrigger to check the Inserted and Deleted tables (those that get created when data is inserted or deleted for tables/rows included within a trigger) so that that the trigger now looks in the Inserted and Deleted tables for data updated in the LastName field of the person table. Here is what the SELECT clause should look like: SELECT D.LastName + ‘ changed to ‘ + I.LastName. Complete the balance of the query including the required JOIN between Inserted and Deleted. At this point you should now have 89 people with the LastName Johnson. Make print screens of successful completion and copy and paste your code into your submission.
    4. Change anyone with the LastName of Carter to Johnson. This should fire the altered trigger and use the new SELECT clause to report the results to the screen with the number of rows changed to Johnson. 89 rows should be changed from Carter to Johnson. Make print screens of successful completion and copy and paste your code into your submission.

    6. [TSQLFundamentals2008 and tempdb]  Scenario: You want to archive deleted orders. You will create a DML trigger to accomplish writing all columns of a deleted order and the time the deletion occurred to an OrdersArchive table. You will test this trigger in the tempdb database.
    a. "Reset" the dbo.Orders table in the tempdb database by dropping the table (DROP TABLE tempdb.dbo.Orders; ) and then re-running your code from step a. of the previous problem.
    b. Create the tempdb.dbo.OrdersArchive table by executing the following code:
    USE tempdb;
    GO
    CREATE TABLE [dbo].[OrdersArchive]
    (
    [orderid] [int] NOT NULL CONSTRAINT PK_OrdersArchive PRIMARY KEY,
    [custid] [int] NULL,
    [empid] [int] NOT NULL,
    [orderdate] [datetime] NOT NULL,
    [requireddate] [datetime] NOT NULL,
    [shippeddate] [datetime] NULL,
    [shipperid] [int] NOT NULL,
    [freight] [money] NOT NULL,
    [shipname] [nvarchar](40) NOT NULL,
    [shipaddress] [nvarchar](60) NOT NULL,
    [shipcity] [nvarchar](15) NOT NULL,
    [shipregion] [nvarchar](15) NULL,
    [shippostalcode] [nvarchar](10) NULL,
    [shipcountry] [nvarchar](15) NOT NULL
    [whenarchived] [datetime] NOT NULL
       CONSTRAINT DF_WhenArchived DEFAULT CURRENT_TIMESTAMP
    );

    c. Create a trigger named dbo.tdArchiveOrders that writes rows deleted from dbo.Orders to the dbo.OrdersArchive table. Note that the when archived column should contain the current datetime value for the deleted row.
    d. Execute the dbo.pDeleteOldersOrders stored procedure that you wrote in the previous problem.

    In your Assignment document show:
    • The code that creates the dbo.tdArchiveOrders trigger
    • The contents of the messages tab of the query window after executing the stored procedure

    Learn More
  9. IT452 Unit 10 Final Project Creating Tables

    IT452 Unit 10 Final Project Misty Tutoring Business

    Regular Price: $20.00

    Special Price: $15.00

    IT452 Unit 10 Final Project
    In this project, you will create an end-to-end solution to meet a business need. This project is worth 200 points.

    Scenario
    Your good friend, Misty, has a tutoring business in Southern California. It is quite successful. A client will call with a tutoring need, and Misty will look on her Excel spread sheet of tutors for those qualified in the tutoring area the client needs, and who live near the client.
    Misty now has approximately 20 freelance tutors registered with her. It has become quite difficult for her to tell what tutors are qualified and live near the client. You have told her you will develop a way for her to get such a list.
    She has sent you the TutorList.xls file with the current list of tutors. (This file is available in Doc Sharing.) The Subject column has values of A (tutor is proficient in English and history), B (tutor is proficient in math and science), or C (tutor is proficient in all basic subjects). Hint: You need to create a database that consists of the data in these to files. The DateQualified column has the latest date the tutor was interviewed and checked out by Misty. In addition, she has provided the current term’s client list (Clients.xls). You will need to use BULK INSERT to load the data from the sheets into the tutoring database and write queries that find tutors in the same zip code as the client and provide the tutor in the same zip code in a report. Misty should be able to select the zip code as a parameter from the tutors in the report and the zip code as a parameter from the clients as a parameter to execute the reports.
    Misty also requires that she should be able to interact with a Web page, because she does not want to know how to "run database software". This will require creating a web based report that she can access via a web link.
    The first thing you should do is list the steps in your plan. What will be the major steps you need to take to accomplish your goal? The first step will be to install SQL Server 2008 Express with Advanced Services on Misty's computer. (She has agreed to that part of the plan.) The last step will be to create a report that asks for parameter values for a stored procedure or query at run time. This does not literally need to be done but should be a step in the plan if you advise her that it is needed on her machine.
    For the purpose of this project, you will use your computer to work out the steps and create the report.
    The first item to place in your final project document is your outline of major steps that need to be accomplished. Label this section:

    1. Outline – 20 points
    <Put your plan step list here.> (The outline will be worth 20 points.)
    Then, for each step after the first (the installation step), make a section in your project, e.g.

    2. <Whatever Step 2 is in your plan outline> - 80 points
    In that section, state how you accomplished that step and/or give the code you used. Enough detail should be given so a knowledgeable person could completely recreate what you did. Show at least some of the results you obtained when you tested to see whether your step had been accomplished. (For
    queries, 3 or 4 lines of output, and stating the number of lines returned is sufficient.) If a screen shot is the best way to demonstrate the results, include it in your project. Please use ALT+PrintScreen so that only the in-focus or active window, not the entire desktop, is copied.
    [Note: You are starting with step 2, because step 1 of your plan is the installation, and it will be assumed that you know how to do that.]
    [Hint: One of your steps should be the creation of a stored procedure that uses a variable for the zip code and determines which tutors live in the same zip code as the client. But, this step will be carried out in #3 below so you might want to letter your steps to avoid confusion.

    3. Create the query or stored proc. that will produce the result set for the report - 40 points (Creation and Alter)
    Create Procedure – 20 points
    Alter Procedure – 20 points
    The query / stored procedure that generates the result set for the report will be worth 20 points each. You then will also Alter the Stored Procedure one time and execute it. You should determine that it gives the desired results before configuring your report. Hint: The SELECT query portion of the stored procedure will be the query you use in your report datasource when you get to the portion of the design wizard (if you use the wizard instead of manually creating everything).

    4. [The last step.] Configuring the Report and the Report Parameters – 60 points
    Query for Report 20 points
    Configuring Parameters – 20 points
    Configuring/Designing 2 reports – 20 points
    The final 60 points will be for the last step in your plan – configuring the report and the parameters for the report. The tutor subject choices should be A, B, or C. The date selection should be from the DateQualified values, with a default value of the earliest DateQualified. The customer zip code must be one of those in the TutorList. [Hint: The latter two parameter items require creation of additional data sets, one for the DateQualified values, and one for the zip code values. Hint: Each of the datasets should use a SELECT query as well to provide the data for the parameter. Look at what you did in Project 9 as an example. The DateQualified data set will have datetime data type. Each parameter will become a drop down on your report.
    You should place four screen shots under this final step in your project document.
    1. For the parameter that specifies Misty's choice of a tutor that resides in the same zip code as the client show one such client/tutor example.
    2. For the parameter that specifies Misty's choice of minimum DateQualified, show how you have configured the Available Values for that parameter.
    3. Show the Report Data pane of BIDS, showing all parameters and all data sets with their fields.
    4. Deploy the report. Run the first report that does not contain parameters for zip code 91016. This one should show only the tutors available for that zip code. Run the second report with parameters for a customer zip code of 91803 for the report with parameters showing which client they qualify to work with, what their skill set is, and date equal to the earliest DateQualified value. Paste the screen shot of this report into your project document.
    Because the entire project involves sequential steps, if you become hung somewhere in the middle and cannot proceed, you may send details to your instructor, and receive a hint in exchange for an appropriate point "cost" or deduction. If you cannot figure out what query you need to create the report, the instructor will send it to you, on request from you, for a "cost" of 20 points so you can move on.
    Your screen shot of the report without parameters should look similar to this on the Report BIDS screen. It should be for ZipCode 91016 regardless to subject and qualified date:
    Your screen shot of the report without parameters should look similar to this on the Report Manager screen:
    Your screen shot of the report with parameters should look similar to this on the Report BIDS screen It should be for ZipCode 91803 regardless to subject and qualified date:
    Your screen shot of the report with parameters should look similar to this on the Report Manager screen:

    Learn More
  10. Database Design Final Project Queries

    Database Design Final Project Students Get Together SQL Server Database

    Regular Price: $40.00

    Special Price: $30.00

    Database Design Final Project Students Get Together SQL Server Database
    This exam stresses the concepts of building the database correctly and being able to extract accurate information from It. While the problem is a simple one, logical thought is required to reach the end result.

    The Scenario
    Well, you have all struggled and made your best efforts. I know that much time and effort went into completing all the work for this course. And now you all need to blow off some steam. So, you all decide to get together to celebrate the conclusion of the course. Everyone wants to celebrate and someone suggests that you get together to go out for a dessert (or two). Now we know everyone has his or her own tastes, and we wouldn’t want to go somewhere that doesn’t have a dessert we like. All the local eateries have certain kinds of dessert they serve. They surely can’t serve every kind of dessert.

    You will need to design a database to keep track of all the students in the class. You will also track what kinds of dessert each student likes. A student may like many different desserts, and many students may like the same dessert. Also, we need to keep track of all the restaurants In the area, and all the different desserts they serve. A restaurant may serve many desserts, and many restaurants may serve the same dessert.

    Tables
    Use DBDL to design the tables (fields, keys, relationships, etc.) necessary to track the information as described above.
    Explain any assumptions. Make sure all table are in 3NF and the correct relationships exist between the tables.

    Use the SQL Server Management Studio to create the database, tables, relationships, and diagram based on the DBDL.
    After the Database is created, you will create the six queries listed below Save the queries as Ql. Q2, Q3, Q4, 05 and Q6.

    Queries
    1. Create the SQL to show what desserts a restaurant serves. (Show all restaurants and each dessert that they serve.)
    2. Create the SQL to show what desserts a student likes. (Show all students and each dessert that they like.)
    3. Create the SQL to show students who like Apple Pie.
    4. Create the SQL to show the NUMBER of restaurants that serve Apple Pie.
    5. Create the SQL to show the most popular dessert. (Based on student likes, not restaurants where it is served.)
    6. Create the SQL to show all the restaurants that each student can go to and have at least one dessert that they like. I.e. - If a restaurant has a dessert that the person likes, then they can go to that restaurant. (Show all students and restaurants that each can go to.)

    Database Diagram
    Create the database diagram of your tables.

    Submitting Your Final Project:
    Create a Folder Called FinalProjet_YourLastNarne. Put the DBDL (create in Word and Save as a .PDF file), Queries and the .mdf and .ldf Database files in the
    FinalProjeel Folder, zip the folder and submit it to the assignment.

    Learn More

Items 1 to 10 of 30 total

per page
Page:
  1. 1
  2. 2
  3. 3

Grid  List 

Set Ascending Direction