Find the name and city of all employees who work for ABC Company and earn between $12,000 and $20,000?

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

Question 1 [5 marks]

The dataset of bridges in Pittsburgh is given in the bridges.csv file. Use this dataset to answer the following questions,

a) Address all the missing values.

b) Look for outliers and smooth noisy data.

c) Prepare the dataset to establish a relation among:

1) Length of the bridge and its purpose.

2) Number of lanes and its materials.

3) Span of the bridge and number of lanes.

Question 2 [5 marks]

Write R code using R markdown to perform the following SQL queries on the following database schema given below. The database consists of the following four tables,

Employee (person_name, street,city)

Works (person_name, companyname, salary) Company (company_name, city)

Manages (person_name, manager_name)

a) Find the name and city of all employees who works for ABC Company?

b) Find the name and city of all employees who work for ABC Company and earn between $12,000 and $20,000?

c) Find the employee details who work and live in the different city for which they work?

d) Find the name of the employees who live in the same cities and same street as their manager?

e) Find all employees in the database who do not work for ABC Company?

f) Find all employees in the database who earn less than each employee of ABC Company?

g) Find all employees who earn less than average salary of all employees of their company?

h) Find the company that has less employees?

i) Find the company that has the highest payroll?

j) Find those companies whose employees earn a lowest salary on average than the average salary at ABC Company?

Question 3 [6 marks]

The AIS Dynamic dataset is given in the nari_dynamic_sar.csv file. Use this dataset to answer the following questions,

a) How many unique vessels are available in the dataset?

b) List the number of records available for each vessel in the dataset.

c) Find out the spatial (latitude and longitude) and temporal coverage of each vessel in the dataset.

d) Let us use R to understand the relation between speed over the ground and spatial coverage of the vessels that have multiple records.

Question 4 [08 marks]

The Airline Cost dataset is given in the airlinecost.csv file. This dataset has the following attributes, among others:

• Airline name

• Length of flight in miles

• Speed of plane in miles per hour

• Daily flight time per plane in hours

• Customers served in 1000s

• Total operating cost in cents per revenue ton-mile

• Total assets in $100,000s

• Investments and special funds in $100,000s

a) Develop a linear regression model using R language to predict the number of customers each airline serves from its length of flight and daily flight time per plane. Next, build another regression model to predict the total assets of an airline from the customers served by the airline. Do you have any insight about the data from the last two regression models?

b) Use the gradient descent algorithm in R to find the optimal intercept and gradient to predict the number of customers each airline serves from its length of flight and daily flight time per plane.

Question 5 [06 marks]

The horseshoe crab dataset is given in the horseshoecrab.csv file. This dataset has 173 observations of female crabs, including the following characteristics:

• Satellites: number of male partners in addition to the female’s primary partner.

• Yes: a binary factor indicating if the female has satellites.

• Width: width of the female crab in centimeters.

• Weight: weight of the female in grams.

• Color: a categorical value having range of 1 to 4, where 1 = light color, and 4 = dark.

• Spine: a categorical variable, valued between 1 and 3, indicating the goodness of spine of the female.

a) Use Softmax regression to predict the condition of the spine of female crabs based on the remaining features in the dataset and report the accuracy of your predictions.

Attachments:

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme