For Problems 1–4, using the STORES Database, formulate SQL queries, Hand in a listing of each query and its output.

1. (10 Points) For each customer, list each stock item ordered, the manufacturer, the quantity ordered, and the total price paid. Include the following columns in the order given below:

From Customer Table:              Company

From Stock Table:                      Description

From the Manufact Table:        Manu_Name

From the Items Table:                                Quantity, Total Price

Order the output by Company and Description.

2. (10 Points) List all orders with a shipping date between December 25, 1999 and January 5, 2000. List the Order Number, Order Date, Customer company name, and Shipping Date. Order by Customer Company Name and Order Number.

3. (10 Points) Count the number of customers who do not have any orders placed.

4. (10 Points) List all customers who are ordering equipment whose description begins with ‘tennis’ or ‘volleyball’. List the Customer number, Stock number, and Description. Do not repeat any rows.

5. (15 Points) Use the following SQL CREATE commands to CREATE the following tables in your

User ID:

                                CREATE TABLE Professor

                                (Prof_ID                NUMBER(3) Constraint pk_Professor Primary Key,

                                Prof_Lname         VARCHAR2(15)                 NOT NULL,

                                Prof_Hiredate      DATE,

                                Prof_Sal                NUMBER(8,2),

                                Prof_Dept             CHAR(6)

                                );

                                CREATE TABLE Student

                                (Stu_ID                  NUMBER(4) Constraint pk_Student Primary Key,

                                Stu_Lname           VARCHAR2(15)                 NOT NULL,

                                Stu_Major            CHAR(6),

Stu_CredHrs         NUMBER(4),

                                Stu_GradePts       NUMBER(5),

                                Prof_ID                 NUMBER(3),

                                CONSTRAINT fk_Student_Prof_ID FOREIGN KEY(Prof_ID)

                                                REFERENCES Professor

                                );

Hand in: Print out of the Create commands, the system response and a DESCRIBE of the tables.

6. (15 Points) Insert the following data into the tables created above using SQL INSERT commands.

Professor Table:

Student Table:

Case- IT Auditing

Windows servers are used for all server types (application, web and database delivery). Windows patch updates comply with the change management policy. Windows patches are downloaded to a central patch server. All patches are reviewed and tested prior to deployment to production servers. All approved patches are first deployed to the development servers, then to the test servers and finally to production servers. Although patches occur on Wednesday evening, if a system reboot is required these are not perform until the weekend downtime window. Approval is not required for patch updates for these are considered a normal standard process for ABC. Additionally, prior to deployment all patches are reviewed to ensure they will function with current systems.

Question: What are the Controls and what are the GAPS

Assume that the following relationships were created in a database.

CUSTOMER (CustomerNumber, CustomerLastName, CustomerFirstName, Phone)

COURSE (CourseNumber, CourseTitle, TeachingMode, CourseCreationDate, Fee)

ENROLLMENT (EnrollmentID, CustomerNumber, CourseNumber, EnrollmentDate, AmountPaid)

Legend:
Primary Key

Foreign Key

Possible values

TeachingMode: PRE - Presencial, ONL - Online


Write the SQL (ORACLE) statements required to complete what is required below. Provide ONE instruction per request / question. Write your answers in the space provided. Identify each answer with the corresponding request / question number.

1. Create the CUSTOMER table include the constraints 
2. Create the COURSE table include the constraints 
3. Create the ENROLLMENT table include the constraints 
4. Create the sequence seqEnroll 
5. Insert a row in the CUSTOMER table. 
6. Insert a row in the COURSE table. 
7. Insert a row in the ENROLLMENT table. The primary key field is a substitute (Surrogate / Artificial) so it will use the sequence you created in statement 4. The transaction must belong to the CUSTOMER you created in # 5 and the COURSE you created in # 6. 
8. List customers in ascending alphabetical order by last name. Include the following information in this order: last name, first name and telephone number 
9. List all the courses that have the word Pastels in the title. Include all the data in the COURSE table. 
10. List all the courses the clients are enrolled in. Include the following data in this order: CustomerNumber, CourseNumber, and AmountPaid. 
11. List all courses that started on or before October 1, 2018. Include the following data in this order: CourseDate, CourseNumber, CourseTitle, Fee. 
12. Show for each client the total paid for classroom courses and total paid for online courses TeachingMode the total paid by each client. 
Show courses that are cheaper than the average price Use subquery and functions. 
13. List all course information if you have had at least ten clients enrolled in the past twelve months. It must work at any time, the twelve-month period will depend on the date the consultation is run. DO NOT USE SPECIFIC DATES. Use subquery and functions.

Justin Stone was an employee of DataCare Services, Inc. His salary was $45,000 through November 10, 2018, when he was laid off. DataCare Services provided medical insurance for Justin and his family during his employment and agreed to continue this coverage through the end of 2018. He received $7,000 of unemployment compensation from November 11, 2018, through December 31, 2018. FICA withholdings were as follows: Social Security of $2,790 ($45,000 x 6.2%) and Medicare of $653 ($45,000 x 1.45%). Justin lives at 112 Green Road, Crown City, OH 45623. His Social Security number is 111-11-1112.

Justin owned an apartment building until November 22, 2018 when he sold it for $200,000 (the apartment building's address is 4826 Orange Street, Crown City, OH 45623). For 2018, he had rent revenue of $33,000. He incurred and paid expenses as follows: $4,568 of repairs, $12,000 of mortgage interest, $10,000 of real estate taxes, and $1,000 of miscellaneous expenses. He purchased the building on January 2, 2012, for $125,000. The building generated an operating profit each year that Justin owned it. Justin received $13,000 in cash as a gift from his mother to help “tide him over” while he was unemployed. He also withdrew $10,000 from his checking account. He “invested” $300 in lottery tickets during the year but had no winnings.

Other information follows:

• On November 22, 2018, Justin sold for $3,500 equipment that had been used for repairing various items in the apartments. The equipment was purchased for $25,000 on July 10, 2011, and was fully depreciated prior to 2018.
• Justin has $3,000 of unrecaptured § 1231 losses from prior years.
• Justin is age 38; is single; is divorced; and has custody of his 9-year-old son, Flint. Justin provides more than 50% of Flint’s support. Flint’s Social Security number is 123-45-6789.
• Justin had $1,000 interest income from Blue Corporation bonds.
• Justin had $1,500 interest income from a State Bank certificate of deposit.
• Justin had a $2,000 0% / 15% / 20% long-term capital gain distribution from the Brown Stock Investment Fund.
• Justin had the following itemized deductions: $4,600 real estate taxes on his home; $8,900 mortgage interest on his home; $4,760 charitable contributions (all in cash, all properly documented, and no single contribution exceeding $25); $4,300 state income tax withholding during 2018; $2,000 state estimated income tax payments during 2018; $2,600 sales taxes paid.
• Justin does not want to donate to the Presidential Election Campaign Fund.
• He had $10,000 of Federal income tax withholding during 2018 and made total Federal estimated income tax payments of $12,000 during 2018.

Required:
Compute Justin's 2018 net tax payable or refund due by providing the information requested for Form 1040 and Schedules A, B, D, and E and Forms 4562, 4797, and 8582.

Vaughn Corporation was formed 5 years ago through a public subscription of common stock. Daniel Brown, who owns 15% of the common stock, was one of the organizers of Vaughn and is its current president. The company has been successful, but it currently is experiencing a shortage of funds. On June 10, 2018, Daniel Brown approached the Topeka National Bank, asking for a 24-month extension on two $35,180 notes, which are due on June 30, 2018, and September 30, 2018. Another note of $5,970 is due on March 31, 2019, but he expects no difficulty in paying this note on its due date. Brown explained that Vaughn’s cash flow problems are due primarily to the company’s desire to finance a $299,820 plant expansion over the next 2 fiscal years through internally generated funds.

The commercial loan officer of Topeka National Bank requested the following financial reports for the last 2 fiscal years.

(a) Compute the following items for Vaughn Corporation. (Round answer to 2 decimal places, e.g. 2.25 or 2.25%.)

The unadjusted trial balance of the Manufacturing Equitable at December 31, 2018, the end of its fiscal year, included the following account balances. Manufacturings 2018 financial statements were issued on April 1, 2019

Accounts receivable $ 95250

Accounts payable 42800

Bank notes payable 601000

Mortgage note payable 1,490,00

Other information

a. The bank notes, issued August 1, 2018, are due on July 31, 2019, and pay interest at a rate of 12%, payable at maturity.

b.The mortgage note is due on March 1, 2019. Interest at 11% has been paid up to December 31 (assume 11% is a realistic rate). Manufacturing intended at December 31, 2018, to refinance the note on its due date with a new 10-year mortgage note. In fact, on March 1, Manufacturing paid $480,000 in cash on the principal balance and refinanced the remaining $1,010,000.

c.Included in the accounts receivable balance at December 31, 2018, were two subsidiary accounts that had been overpaid and had credit balances totaling $19350 The accounts were of two major customers who were expected to order more merchandise from Manufacturing and apply the overpayments to those future purchases.

d.On November 1, 2018, Manufacturing rented a portion of its factory to a tenant for $25200 per year, payable in advance. The payment for the 12 months ended October 31, 2019, was received as required and was credited to rent revenue.

Required:

(1) Prepare any necessary adjusting journal entries at December 31, 2018, pertaining to each item of other information (a–d). (If no entry is required for a transaction/event, select "No journal entry required" in the first account field.) Journal entry worksheet: a. Record the bank notes, issued August 1, 2018, are due on July 31, 2019, and pay interest at a rate of 12%, payable at maturity. b. Record the mortgage note is due on March 1, 2019. Interest at 11% has been paid up to December 31 (assume 11% is a realistic rate). Manufacturing intended at December 31, 2018, to refinance the note on its due date with a new 10-year mortgage note. In fact, on March 1, Manufacturing paid $480,000 c. Record included in the accounts receivable balance at December 31, 2018, were two subsidiary accounts that had been overpaid and had credit balances totaling $19,350. The accounts were of two major customers who were expected to order more merchandise from Manufacturing and apply the overpayments to those future purchases. d. Record on November 1, 2018, Manufacturing rented a portion of its factory to a tenant for $25,200 per year, payable in advance. The payment for the 12 months ended October 31, 2019, was received as required and was credited to rent revenue.

(2) Prepare the current and long-term liability sections of the December 31, 2018, balance sheet.

Balance Sheet (partial)

At December 31, 2018

Current liabilities:

Total current liabilities

Long-term liabilities:

ASSIGNMENT:

Answer both questions below:

1. Using the internet or any other resource, perform a search for “systems development life cycle.” Write a summary that outlines the similarities and differences between the systems development life cycle (SDLC) and each of the project management life cycles. Can they work together?
2. There are many aspects that make IT projects unique. Describe four aspects and provide examples. Given examples from your concentration (Cybersecurity, IT Security, IT Project Management, Information Security Management, Business Analytics, Business Administration, Software Development, Database), as to why projects from these concentrations would be unique.

Use the Financial database from “Excel Databases.xls” on Blackboard. Use Total Revenues, Total Assets, Return on Equity, Earnings Per Share, Average Yield, and Dividends Per Share to predict the average P/E ratio for a company. Use Excel to perform a forward selection regression analysis. Assume a 5% level of significance. Based on your final model, what is the p-value from the test of the overall model? Write your answer as a number and round to 3 decimal places.

Excel Data: https://drive.google.com/file/d/1TQG5r2wzLGk--75whZXyb0SDTHZTWS0S/view?usp=sharing

Using the data shown, you will create two charts. The first chart is a bar chart that will display % growth in different job titles between 2010 and 2020Est. The second chart will be a pie chart showing the 2020Est. Jobs in different categories as a % of overall 2020Est. jobs.

Discuss whether or not each of the following activities is a data mining task. Provide your reasons as well in detail. Fill in your answers in the space provided below.

(a) Dividing the customers of a company according to their gender.

(b) Dividing the customers of a company according to their profitability.

(

c) Computing the total sales of a company.

(d) Sorting a student database based on student identification numbers.

(e) Predicting the outcomes of tossing a (fair) pair of dice.

(f) Predicting the future stock price of a company using historical records.

(g) Monitoring the heart rate of a patient for abnormalities.

(h) Monitoring seismic waves for earthquake activities.