Search results for 'Assignment'

POS 410 Week 2 Table Queries SQL for Business

Create two tables using the specifications listed.
• Use the database built in last week’s assignment to create the two tables and associated columns listed below.  Table names, data types, and column lengths must be used as specified; column names may be created as desired and appropriate.  Avoiding the use of spaces and special characters in the column names is recommended.
• In the Employee table, create an Employee ID column that generates a unique number for each employee and designate the column as the Primary Key. In the Job table, use the job title column as the table’s primary key.

Table Name = Employee_Tbl
• Employee ID (type= numeric identity) PK
• Last name (type=varchar(30))
• First name (type=varchar(30))            
• Address (type=varchar(30))
• City (type=varchar(30))        
• State (type=varchar(2))
• Telephone area code (type=varchar(3))            
• Telephone number (type=varchar(7))
• Employer Information Report (EEO-1) classification                  (type=varchar(30))
• Hire date (type=date)
• Salary (type=money)
• Gender (type=varchar(1))
• Age (type=numeric)
• Job Title (type=varchar(30))

    
Table Name = Job_Tbl
• Job title (type=varchar(30)) PK    
• Job description (type=varchar(2000))
• Exemption status (type=varchar(1))

Use the SQL INSERT statement to go to the human resources department in the Kudler Fine Foods Virtual Organization. Using information found in the employee files for the La Jolla and Encinitas stores, enter records into the Employee table for the following employees:
• Glenn Edelman
• Eric McMullen
• Raj Slentz
• Erin Broun
• Donald Carpenter
• David Esquivez
• Nancy Sharp

Use the information from the job classifications and descriptions to enter records into the Job Title table for the following titles:
• Accounting clerk
• Assistant manager
• Bagger
• Cashier
• Computer support specialist
• Director of finance and accounting
• Retail assistant bakery and pastry
• Retail assistant butchers and seafood specialists
• Stocker

Hint: Data entered into the job title column of the employee_tbl and job_tbl tables must match exactly, including case, spaces, and so on.  If they do not match exactly, you will not be able to create the foreign key or perform the joins that are required in later assignments.
Deliverables:  check your results by selecting all of the data from both tables (one table at a time).  Copy and paste screen images of your work in a word doc, then post as an attachment in your assignment tab of the classroom.  You may need to resize your windows and take multiple screen shots to capture all of the required information.  The screen images should show the following information:
• Database, table, and column definitions
• SQL statements used to load the data
• Data stored in both tables (display contents after data is loaded)
Refer to the materials forum for the rubric that will be used to grade this assignment.

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.

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.

CMIS 420 Homework Assignment 3 Advanced Relational Database Concepts and Applications

Homework Task 1:
Create a PL/SQL block to complete the followings.  
Output the cheapest movie information.  You can use the view you created in project 1 - task 1
In your block, referential type should be used to receive the cursor return.
Use DBMS_OUTPUT to output your result.
Possible Exception should be handled in exception handling section.  You are not required to use user-defined exception. Use Oracle predefined exception. When exception occurs, you need to output error code and error message.
Spool the output to a text file.  Don't forget to use "Set serveroutput on"
Submit your code as .sql file and spooled output

Homework Task 2:
Create a stored procedure based on task 1 with an input parameter movie_id. Modify your cursor to use movie_id to select desired movie information. And execute the procedure and spool the execution result.
Spool the output to a text file.  Don't forget to use "set serveroutput on"
Submit your code as .sql file and spooled output

Homework Task 3:
Create a statement trigger on orders table. The trigger fires after updating the table. When the trigger fires one record insert into the following temp table using the insert statement shown below:
Note: you need to create temp_table and its sequence using the following code.

PROMPT creating table temp_table ...............
DROP TABLE temp_table;
CREATE TABLE temp_table
( num_col NUMBER(5) not null primary key,
char_col VARCHAR2(30),
date_col  VARCHAR2(30));

PROMPT creating SEQUENCE trigger_seq ...............
DROP SEQUENCE trigger_seq;
CREATE SEQUENCE trigger_seq
START WITH 1
INCREMENT BY 1;

INSERT INTO temp_table (num_col, char_col, date_col)
VALUES (trigger_seq.NEXTVAL, 'After Statement trigger', TO_CHAR(sysdate, 'DD-MON-YYY HH24:MI:SS'));
Submit your code as .sql file

Homework Task 4:
Create a row trigger on order_items table to fire after inserting the data into order_items table. When the trigger fires it inserts a record into temp_table using the following insert statement:
INSERT INTO temp_table (num_col, char_col, date_col)VALUES (trigger_seq.NEXTVAL, 'After Row Trigger', TO_CHAR(sysdate, 'DD-MON-YYY HH24:MI:SS'));
Submit your code as .sql file

Homework Task 5:
Test your triggers.
Update orders table to set total payment to 300. fire statement trigger on orders table.
Insert one record into order_items. Fire row trigger on order_items table.
Query temp_table to get the inserted records for trigger firing.
Spool the output to a text file and submit it.

Create a database using Microsoft Access.

The database should contain three tables: Customers, Products, and Transactions; three queries: list of customers, transactions by customer, and transactions by product; and be something that a small business, such as a jewelry store or used car lot, can use for its daily needs.

The database must contain an input form for the transactions and one report that is based on any of the queries; the tables must each contain 10 records; and the records in the transactions table must be created by way of the input form.

In this assignment, use Microsoft Access to create the following: Forms Tables Queries Reports

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.

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

IT358 Unit 5 INNER JOIN OUTER JOIN subquery

Outcomes addressed in this activity:
Create a query with an INNER JOIN
Create a query with an OUTER JOIN
Create a query that contains a subquery

Project requirements:
In this project, you will write joins and subqueries:
1. Complete the Hands-on Assignments #1–5 for Chapter 9.
2. Complete the Hands-on Assignments #1–5 for Chapter 12.

Oracle 11G SQL Chapter 9 Hands-On assignment Solution
Generate and test two SQL queries for each of the following tasks: (a) the SQL statement needed to perform the stated task using the traditional approach, and (b) the SQL statement needed to perform the stated task using the JOIN keyword.
1. Create a list that displays the title of each book and the name and phone number of the person at the publisher’s office whom you would need to contact to reorder each book.
2. Determine which orders have not yet shipped and the name of the customer who placed each order. Sort the results by the date on which the order was placed.
3. List the customer number and names of all individuals who have purchased books in the fitness category.
4. Determine which books Jake Lucas has purchased. Perform the search using the customer name, not the customer number.
5. Determine the profit of each book sold to Jake Lucas. Sort the results by the date of the order. If more than one book was ordered, sort the results by the profit amount in descending order. Perform the search using the customer name, not the customer number.

Oracle 11G SQL Chapter 12 Hands-On assignment Solution
Use a subquery to accomplish each of the tasks. First, execute the query you will use as the subquery to verify the results.
1. Determine which books have a retail price that is less than the average retail price of all books sold by JustLee Books.
2. Determine which books cost less than the average cost of other books in the same category.
3. Determine which orders were shipped to the same state as order 1014.
4. Determine which orders had a higher total amount due than order 1008.
5. Determine which author or authors wrote the book(s) most frequently purchased by customers of JustLee Books.

IT358 Unit 6 Views

Outcomes addressed in this activity:
Create a view, using CREATE
VIEW command or the CREATE OR REPLACE VIEW command
Explain the effect of the WITH READ ONLY option
Demonstrate the ability to update records in a simple view
Demonstrate the ability to re-create a view
Identify problems associated with adding records to a complex view
Identify the key-preserved table underlying a complex view

Project requirements:
In this project, you will write joins and subqueries:
1. Complete the Review Questions for numbers 1–5 from Chapter 13.
2. Complete the Hands-on Assignments #1-10 from Chapter 13.

Oracle 11G SQL Chapter 13 Hands-On assignment Solution
1. Create a view that will list the name of the contact person at each publisher and the person’s phone number. Do not include the publisher’s ID in the view. Name the view CONTACT.
2. Change the CONTACT view so that no users can accidentally perform DML operations on the view.
3. Create a view called HOMEWORK13 that will include the columns named Col1 and Col2 from the FIRSTATTEMPT table. Make certain the view will be created even if the FIRSTATTEMPT table does not exist.
4. Attempt to view the structure of the HOMEWORK13 view.
5. Create a view that will list the ISBN and title for each book in inventory along with the name and telephone number of the individual to contact in the event the book needs to be reordered. Name the view REORDERINFO.
6. Try to change the name of one of the individuals in the REORDERINFO view to your name. Was there an error message displayed when performing this step? If so, what was the cause of the error message?
7. Select one of the books in the REORDERINFO view and try to change the ISBN of the book. Was there an error message displayed when performing this step? If so, what was the cause of the error message?
8. Delete the record in the REORDERINFO view that contains your name (if that step was not performed successfully, then delete one of the contacts already listed in the table).Was there an error message displayed when performing this step? If so, what was the cause of the error message?
9. Issue a rollback command to undo any changes made with any previous DML operations.
10. Delete the REORDERINFO view.