Project Description You’re working as an analyst for Zuber, a new ride-sharing company that’s launching in Chicago. Your task is to find patterns in the available information. You want to understand passenger preferences and the impact of external factors on rides. Working with a database, you’ll analyze data from competitors and test a hypothesis about the impact of weather on ride frequency. Description of Data A database with info on taxi rides in Chicago: neighborhoods table: data on city neighborhoods – name: name of the neighborhood – neighborhood_id: neighborhood code cabs table: data on taxis – cab_id: vehicle code – vehicle_id: the vehicle’s technical ID – company_name: the company that owns the vehicle Table SchemeNote: there isn’t a direct connection between the tables trips and weather_records in the database. But you can still use JOIN and link them using the time the ride started (trips.start_ts) and the time the weather record was taken (weather_records.ts). Instructions on Completing the Project https://practicum-content.s3.us-west-1.amazonaws.com/data-analyst-eng/moved_chicago_weather_2017.html (see ‘parsing_data.py’) Step 2: Exploratory Data Analysis (see ‘databases.sql’) Step 3: Test the hypothesis that the duration of rides from the the Loop to O’Hare International Airport changes on rainy Saturdays. 1) Retrieve the identifiers of the O’Hare and Loop neighborhoods from the neighborhoods table. Ignore rides for which data on weather conditions is not available. Step 4: Exploratory Data Analysis (Python) In addition to the data you retrieved in the previous tasks, you’ve been given a second file. You now have these two CSVs: For these two datasets you now need to: – import the files – study the data they contain – make sure the data types are correct – identify the top 10 neighborhoods in terms of drop-offs – make graphs: taxi companies and number of rides, top 10 neighborhoods by number of dropoffs – draw conclusions based on each graph and explain the results Step 5: Testing Hypotheses (Python) project_sql_result_07.csv: The result of the last query. It contains data on rides from the Loop to O’Hare International Airport. Test the hypothesis: “The average duration of rides from the Loop to O’Hare International Airport changes on rainy Saturdays.” Set the significance level (alpha) value on your own. Explain: – how you formed the null and alternative hypotheses – what criterion you used to test the hypotheses and why
Project description You work as an analyst for the telecom operator Megaline. The company offers its clients two prepaid plans, Surf and Ultimate. The commercial department wants to know which of the plans brings in more revenue in order to adjust the advertising budget. Description of the plans Surf Monthly charge: $20 500 monthly minutes, 50 texts, and 15 GB of data After exceeding the package limits: – 1 minute: 3 cents – 1 text message: 3 cents – 1 GB of data: $10 Ultimate Monthly charge: $70 3000 monthly minutes, 1000 text messages, and 30 GB of data After exceeding the package limits: – 1 minute: 1 cent – 1 text message: 1 cent – 1 GB of data: $7 Instructions on completing the project Step 1. Open the data file and study the general information File path: /datasets/megaline_calls.csv /datasets/megaline_internet.csv /datasets/megaline_messages.csv /datasets/megaline_plans.csv /datasets/megaline_users.csv Step 2. Prepare the data Convert the data to the necessary types Find and eliminate errors in the data Explain what errors you found and how you removed them. For each user, find: The number of calls made and minutes used per month The number of text messages sent per month The volume of data per month The monthly revenue from each user (subtract the free package limit from the total number of calls, text messages, and data; multiply the result by the calling plan value; add the monthly charge depending on the calling plan) Step 3. Analyze the data Describe the customers’ behavior. Find the minutes, texts, and volume of data the users of each plan require per month. Calculate the mean, variance, and standard deviation. Plot histograms. Describe the distributions. Step 4. Test the hypotheses The average revenue from users of Ultimate and Surf calling plans differs. The average revenue from users in NY-NJ area is different from that of the users from other regions. You decide what alpha value to use. Explain: How you formulated the null and alternative hypotheses. What criterion you used to test the hypotheses and why. Step 5. Write an overall conclusion Description of the data Megaline rounds seconds up to minutes, and megabytes to gigabytes. For calls, each individual call is rounded up: even if the call lasted just one second, it will be counted as one minute. For web traffic, individual web sessions are not rounded up. Instead, the total for the month is rounded up. If someone uses 1025 megabytes this month, they will be charged for 2 gigabytes. The users table (data on users): user_id — unique user identifier first_name — user’s name last_name — user’s last name age — user’s age (years) The calls table (data on calls): duration — call duration (in minutes) user_id — the identifier of the user making the call The messages table (data on texts): The internet table (data on web sessions): The plans table (data on the plans): plan_name — calling plan name usd_monthly_fee — monthly charge in US dollars minutes_included — monthly minute allowance messages_included — monthly text allowance mb_per_month_included — data volume allowance (in megabytes) usd_per_minute — price per minute after exceeding the package limits (e.g., if the package includes 100 minutes, the 101st minute will be charged) usd_per_message — price per text after exceeding the package limits usd_per_gb — price per extra gigabyte of data after exceeding the package limits (1 GB = 1024 megabytes)
In this project, you will work with data from the entertainment industry. You will study a dataset with records on movies and shows. The research will focus on the “Golden Age†of television, which began in 1999 with the release of The Sopranos and is still ongoing. The aim of this project is to investigate how the number of votes a title receives from IMDb users impacts its ratings. The assumption is that highlyrated shows (we will focus on TV shows, ignoring movies) released during the “Golden Age†of television also have the most votes. This project is similar to the tasks you will be getting in your job as a data professional. Many business decisions are initially born as assumptions, your contribution as an expert in the data domain is to answer the question “Did the assumption formulated before the study appear to be true?†Description of the data The data is stored in the file: /datasets/movies_and_shows.csv Description of columns: name – first and last name of actor (director) character – character played (for actors) role — the person’s contribution to the title (it can be in the capacity either of actor or director) title — title of movie (show) type — show or movie genres — list of genres under which the movie (show) falls release_year — year when the movie (show) was released imdb_score — score on IMDb imdb_votes — votes on IMDb Stages Data on movies and shows is stored in the /datasets/movies_and_shows.csv file. There is no information about the quality of the data, so you will need to explore it before doing the analysis. First, you’ll evaluate the quality of the data and see whether its issues are significant. Then, during data preprocessing, you will try to account for the most critical problems. Your project will consist of three stages: 1. Data overview 2. Data preprocessing 3. Data analysis
Project Description Rusty Bargain used car sales service is developing an app to attract new customers. In that app, you can quickly find out the market value of your car. You have access to historical data: technical specifications, trim versions, and prices. You need to build the model to determine the value. Rusty Bargain is interested in: the quality of the prediction; the speed of the prediction; the time required for training Project instructions 1) Download and look at the data. 2) Train different models with various hyperparameters. Compare gradient boosting methods with random forest, decision tree, and linear regression. 3) Analyze the speed and quality of the models. Data description The dataset is stored in file /datasets/car_data.csv. download dataset. Features VehicleType — vehicle body type RegistrationYear — vehicle registration year Gearbox — gearbox type Power — power (hp) Model — vehicle model RegistrationMonth — vehicle registration month FuelType — fuel type Brand — vehicle brand NotRepaired — vehicle repaired or not NumberOfPictures — number of vehicle pictures Target Price — price (Euro)
Project Description Mobile carrier Megaline has found out that many of their subscribers use legacy plans. They want to develop a model that would analyze subscribers’ behavior and recommend one of Megaline’s newer plans: Smart or Ultra. You have access to behavior data about subscribers who have already switched to the new plans. For this classification task, you need to develop a model that will pick the right plan. Since you’ve already performed the data preprocessing step, you can move straight to creating the model. Develop a model with the highest possible accuracy. In this project, the threshold for accuracy is 0.75. Check the accuracy using the test dataset. Project Instructions 1) Open and look through the data file. Path to the file: datasets/users_behavior.csv 2) Split the source data into a training set, a validation set, and a test set. 3) Investigate the quality of different models by changing hyperparameters. Briefly describe the findings of the study. 4) Check the quality of the model using the test set. 5) Sanity check the model. Data Description Every observation in the dataset contains monthly behavior information about one user. The information given is as follows: – Ñalls — number of calls – minutes — total call duration in minutes – messages — number of text messages – mb_used — Internet traffic used in MB – is_ultra — plan for the current month (Ultra – 1, Smart – 0)
Project Description You work for the OilyGiant mining company. Your task is to find the best place for a new well. Steps to choose the location: – Collect the oil well parameters in the selected region: oil quality and volume of reserves; – Build a model for predicting the volume of reserves in the new wells; – Pick the oil wells with the highest estimated values; – Pick the region with the highest total profit for the selected oil wells. You have data on oil samples from three regions. Parameters of each oil well in the region are already known. Build a model that will help to pick the region with the highest profit margin. Analyze potential profit and risks using the Bootstrapping technique. Project Instructions 1. Download and prepare the data. Explain the procedure. 2. Train and test the model for each region: 2.1. Split the data into a training set and validation set at a ratio of 75:25. 2.2. Train the model and make predictions for the validation set. 2.3. Save the predictions and correct answers for the validation set. 2.4. Print the average volume of predicted reserves and model RMSE. 2.5. Analyze the results. 3. Prepare for profit calculation: 3.1. Store all key values for calculations in separate variables. 3.2. Calculate the volume of reserves sufficient for developing a new well without losses. Compare the obtained value with the averagevolume of reserves in each region. 3.3. Provide the findings about the preparation for profit calculation step. 4. Write a function to calculate profit from a set of selected oil wells and model predictions: 4.1. Pick the wells with the highest values of predictions. 4.2. Summarize the target volume of reserves in accordance with these predictions 4.3. Provide findings: suggest a region for oil wells’ development and justify the choice. Calculate the profit for the obtained volume ofreserves. 5. Calculate risks and profit for each region: 5.1. Use the bootstrapping technique with 1000 samples to find the distribution of profit. 5.2. Find average profit, 95% confidence interval and risk of losses. Loss is negative profit, calculate it as a probability and then express asa percentage. 5.3. Provide findings: suggest a region for development of oil wells and justify the choice. Data Description Geological exploration data for the three regions are stored in files: – geo_data_0.csv – geo_data_1.csv – geo_data_2.csv – id — unique oil well identifier – f0, f1, f2 — three features of points – product — volume of reserves in the oil well (thousand barrels) Conditions: Only linear regression is suitable for model training. When exploring the region, a study of 500 points is carried with picking the best 200 points for the profit calculation. The budget for development of 200 oil wells is 100 USD million. One barrel of raw materials brings 4.5 USD of revenue The revenue from one unit of product is 4,500 dollars (volume of reserves is in thousand barrels). After the risk evaluation, keep only the regions with the risk of losses lower than 2.5%. From the ones that fit the criteria, the region with the highest average profit should be selected. The data is synthetic: contract details and well characteristics are not disclosed.
Project Statement The Film Junky Union, a new edgy community for classic movie enthusiasts, is developing a system for filtering and categorizing movie reviews. The goal is to train a model to automatically detect negative reviews. You’ll be using a dataset of IMBD movie reviews with polarity labelling to build a model for classifying positive and negative reviews. It will need to have an F1 score of at least 0.85. Project Instructions 1) Load the data. 2) Preprocess the data, if required. 3) Conduct an EDA and make your conclusion on the class imbalance. 4) Preprocess the data for modeling. 5) Train at least three different models for the given train dataset. 6) Test the models for the given test dataset. 7) Compose a few of your own reviews and classify them with all the models. 8) Check for differences between the testing results of models in the above two points. Try to explain them. 9) Present your findings. Data Description The data is stored in the imdb_reviews.tsv file. The data was provided by Andrew L. Maas, Raymond E. Daly, Peter T. Pham, Dan Huang, Andrew Y. Ng, and Christopher Potts. (2011). Learning Word Vectors for Sentiment Analysis. The 49th Annual Meeting of the Association for Computational Linguistics (ACL 2011). Here’s the description of the selected fields: review: the review text pos: the target, ‘0’ for negative and ‘1’ for positive ds_part: ‘train’/’test’ for the train/test part of dataset, correspondingly
Statement The Sure Tomorrow insurance company wants to solve several tasks with the help of Machine Learning and you are asked to evaluate that possibility. Task 1: Find customers who are similar to a given customer. This will help the company’s agents with marketing. Task 2: Predict whether a new customer is likely to receive an insurance benefit. Can a prediction model do better than a dummy model? Task 3: Predict the number of insurance benefits a new customer is likely to receive using a linear regression model. Task 4: Protect clients’ personal data without breaking the model from the previous task. It’s necessary to develop a data transformation algorithm that would make it hard to recover personal information if the data fell into the wrong hands. This is called data masking, or data obfuscation. But the data should be protected in such a way that the quality of machine learning models doesn’t suffer. You don’t need to pick the best model, just prove that the algorithm works correctly. Project Instructions 1) Load the data. 2) Check that the data is free of issues — there is no missing data, extreme values, and so on. 3) Work on each task and answer the questions posed in the project template. 4) Draw conclusions based on your experience working on the project. Data Description The dataset is stored in file /datasets/insurance_us.csv. Features: insured person’s gender, age, salary, and number of family members. Target: number of insurance benefits received by an insured person over the last five years.
Task statement Prepare a prototype of a machine learning model for Zyfra. The company develops efficiency solutions for heavy industry. The model should predict the amount of gold recovered from gold ore. You have the data on extraction and purification. The model will help to optimize the production and eliminate unprofitable parameters. Technological Process Mined ore undergoes primary processing to get the ore mixture or rougher feed, which is the raw material for flotation (also known as the rougher process). After flotation, the material is sent to two-stage purification.1. Flotation Gold ore mixture is fed into the float banks to obtain rougher Au concentrate and rougher tails (product residues with a low concentration of valuable metals). The stability of this process is affected by the volatile and non-optimal physicochemical state of the flotation pulp (a mixture of solid particles and liquid). 2. Purification The rougher concentrate undergoes two stages of purification. After purification, we have the final concentrate and new tails. Data description Technological Process Rougher feed — raw material Rougher additions (or reagent additions) — flotation reagents: Xanthate, Sulphate, Depressant Xanthate — promoter or flotation activator; Sulphate — sodium sulphide for this particular process; Depressant — sodium silicate. Rougher process — flotation Rougher tails — product residues Float banks — flotation unit Cleaner process — purification Rougher Au — rougher gold concentrate Final Au — final gold concentrate Parameters of Stages – air amount — volume of air – fluid levels – feed size — feed particle size – feed rate Feature Naming Here’s how you name the features: [stage].[parameter_type].[parameter_name] Example: rougher.input.feed_ag Possible values for [stage]: rougher — flotation primary_cleaner — primary purification secondary_cleaner — secondary purification final — final characteristics Possible values for [parameter_type]: input — raw material parameters output — product parameters state — parameters characterizing the current state of the stage calculation — calculation characteristics Recovery Calculation You need to simulate the process of recovering gold from gold ore. Use the ‘Recovery’ formula to simulate the recovery process: C — share of gold in the concentrate right after flotation (for finding the rougher concentrate recovery)/after purification (for finding the final concentrate recovery) F — share of gold in the feed before flotation (for finding the rougher concentrate recovery)/in the concentrate right after flotation (for finding the final concentrate recovery) T — share of gold in the rougher tails right after flotation (for finding the rougher concentrate recovery)/after purification (for finding the final concentrate recovery) To predict the coefficient, you need to find the share of gold in the concentrate and the tails. Note that both final and rougher concentrates matter. Evaluation Metric To solve the problem, we will need new metrics. sMAPE (symmetric Mean Absolute Percentage Error):We need to predict two values: 1. rougher concentrate recovery rougher.output.recover 2. final concentrate recovery final.output.recovery Final sMAPE is clculated using these two values:Project Description The data is stored in three files: gold_recovery_train.csv — training dataset download gold_recovery_test.csv — test dataset download gold_recovery_full.csv — source dataset download The source dataset contains the training and test sets with all the features. You have the raw data that was only downloaded from the warehouse. Before building the model, check the correctness of the data. Porject Instructions 1. Prepare the Data 1.1. Open the files and look into the data. Path to files: – /datasets/gold_recovery_train.csv – /datasets/gold_recovery_test.csv /datasets/gold_recovery_full.csv 1.2. Check that recovery is calculated correctly. Using the training set, calculate recovery for the rougher.output.recovery feature. Find theMAE between your calculations and the feature values. Provide findings. 1.3. Analyze the features not available in the test set. What are these parameters? What is their type? 1.4. Perform data preprocessing.2. Analyze the Data 2.1. Take note of how the concentrations of metals (Au, Ag, Pb) change depending on the purification stage. 2.2. Compare the feed particle size distributions in the training set and in the test set. If the distributions vary significantly, the modelevaluation will be incorrect. 2.3. Consider the total concentrations of all substances at different stages: raw feed, rougher concentrate, and final concentrate. Do younotice any abnormal values in the total distribution? If you do, is it worth removing such values from both samples? Describe the findings and eliminate anomalies. 3. Build the Model 3.1. Write a function to calculate the final sMAPE value. 3.2. Train different models. Evaluate them using cross-validation. Pick the best model and test it using the test sample. Provide findings.
Project Description You work for the online store Ice, which sells video games all over the world. User and expert reviews, genres, platforms (e.g. Xbox or PlayStation), and historical data on game sales are available from open sources. You need to identify patterns that determine whether a game succeeds or not. This will allow you to spot potential big winners and plan advertising campaigns. The dataset contains the abbreviation ESRB. The Entertainment Software Rating Board evaluates a game’s content and assigns an age rating such as Teen or Mature. Instructions on Completing the Project Step 1: Open the Data File and Study the General Information File path: /datasets/games.csv Step 2: Prepare the Data Replace the column names (make them lowercase). Convert the data to the required types. Describe the columns where the data types have been changed and why. If necessary, decide how to deal with missing values: Explain why you filled in the missing values as you did or why you decided to leave them blank. Why do you think the values are missing? Give possible reasons. Pay attention to the abbreviation TBD (to be determined). Specify how you intend to handle such cases. Calculate the total sales (the sum of sales in all regions) for each game and put these values in a separate column. Step 3: Analyze the Data Look at how many games were released in different years. Is the data for every period significant? Look at how sales varied from platform to platform. Choose the platforms with the greatest total sales and build a distribution based on data for each year. Find platforms that used to be popular but now have zero sales. How long does it generally take for new platforms to appear and old ones to fade? Work only with the data that you’ve decided is relevant. Disregard the data for previous years. Which platforms are leading in sales? Which ones are growing or shrinking? Select several potentially profitable platforms. Build a box plot for the global sales of all games, broken down by platform. Are the differences in sales significant? What about average sales on various platforms? Describe your findings. Take a look at how user and professional reviews affect sales for one popular platform (you choose). Build a scatter plot and calculate the correlation between reviews and sales. Draw conclusions. Keeping your conclusions in mind, compare the sales of the same games on other platforms. Take a look at the general distribution of games by genre. What can we say about the most profitable genres? Can you generalize about genres with high and low sales? Step 4: Create User Profile for Each Region For each region (NA, EU, JP), determine: The top five platforms. Describe variations in their market shares from region to region. The top five genres. Explain the difference. Do ESRB ratings affect sales in individual regions? Step 5: Test Hypotheses Average user ratings of the Xbox One and PC platforms are the same. Average user ratings for the Action and Sports genres are different. Set the alpha threshold value yourself. Explain: How you formulated the null and alternative hypotheses What significance level you chose to test the hypotheses, and why Step 6: Write General Conclusion Data Description Name Platform Year_of_Release Genre NA_sales (North American sales in USD million) EU_sales (sales in Europe in USD million) JP_sales (sales in Japan in USD million) Other_sales (sales in other countries in USD million) Critic_Score (maximum of 100) User_Score (maximum of 10) Rating (ESRB)
Project Showcase Project Description The telecom operator Interconnect would like to be able to forecast their churn of clients. If it’s discovered that a user is planning to leave, they will be offered promotional codes and special plan options. Interconnect’s marketing team has collected some of their clientele’s personal data, including information about their plans and contracts. Interconnect’s Services Interconnect mainly provides two types of services: 1) Landline communication. The telephone can be connected to several lines simultaneously. 2) Internet. The network can be set up via a telephone line (DSL, digital subscriber line) or through a fiber optic cable. Some other services the company provides include: – Internet security: antivirus software (DeviceProtection) and a malicious website blocker (OnlineSecurity) – A dedicated technical support line (TechSupport) – Cloud file storage and data backup (OnlineBackup) – TV streaming (StreamingTV) and a movie directory (StreamingMovies) The clients can choose either a monthly payment or sign a 1- or 2-year contract. They can use various payment methods and receive an electronic invoice after a transaction. Data Description The data consists of files obtained from different sources: – contract.csv — contract information – personal.csv — the client’s personal data – internet.csv — information about Internet services – phone.csv — information about telephone services Work Plan A) Initialization: Import necessary libraries such as Pandas, NumPy, Matplotlib, Seaborn for data manipulation and visualization, and Scikit-learn for model building. B) Load Data: Load all four CSV files (contract.csv, personal.csv, internet.csv, phone.csv) into separate DataFrames. Ensure that the data types are appropriate for analysis. C) Data Preprocessing: – Check for missing values and fill or drop them as necessary. – Identify and remove duplicate rows. – Convert any inappropriate data types to the correct format (e.g., dates, categorical variables). D) Exploratory Data Analysis: – Use visualizations (bar plots, histograms, box plots) to identify trends and relationships between customer attributes and churn rates. – Look for correlations between features and the target variable. E) Model Training Preparation: – Split the dataset into training, validation and test sets. – Encode categorical variables using techniques like one-hot encoding or label encoding as appropriate. – Handle class imbalance through appropriate means. F) Model Training: – Select a variety of classification algorithms (e.g., Logistic Regression, Random Forest, Gradient Boosting). – Use crossvalidation to tune hyperparameters and select the best-performing model based on the AUC-ROC score. G) Model Analysis & Test: – Analyze the performance of each model using AUC-ROC and accuracy metrics. – Choose the best model and retrain it on the entire training set, then evaluate it on the test set.
For this project, you’ll work with data from Instacart. The dataset we’ve provided for you has been modified from the original. We’ve reduced the size of the dataset so that your calculations run faster and we’ve introduced missing and duplicate values. We were also careful to preserve the distributions of the original data when we made our changes. Your mission is to clean up the data and prepare a report that gives insight into the shopping habits of Instacart customers. Data dictionary There are five tables in the dataset, and you’ll need to use all of them to do your data preprocessing and EDA. Below is a data dictionary that lists the columns in each table and describes that data that hold. instacart_orders.csv: each row corresponds to one order on the Instacart app – order_id: ID number that uniquely identifies each order user_id: ID number that uniquely identifies each customer account – order_number: the number of times this customer has placed an order order_dow: day of the week that the order placed (which day is 0 is uncertain) – order_hour_of_day: hour of the day that the order was placed days_since_prior_order: number of days since this customer placed their previous order products.csv: each row corresponds to a unique product that customers can buy – product_id: ID number that uniquely identifies each product – product_name: name of the product – aisle_id: ID number that uniquely identifies each grocery aisle category – department_id: ID number that uniquely identifies each grocery department category order_products.csv: each row corresponds to one item placed in an order – order_id: ID number that uniquely identifies each order product_id: ID number that uniquely identifies each product – add_to_cart_order: the sequential order in which each item was placed in the cart – reordered: 0 if the customer has never ordered this product before, 1 if they have aisles.csv: – aisle_id: ID number that uniquely identifies each grocery aisle category – aisle: name of the aisle departments.csv: – department_id: ID number that uniquely identifies each grocery department category – department: name of the department Stages Your project will consist of three stages: 1) Open the data files 2) Preprocess the data 3) Perfroma analysis on the data
Course Name: Database Systems Semester: Summer 24Lab 03 : Introduction to Bank DB, SQL Joins and Constraints Activity List Suggestions for this Lab: ● Use a Text editor such as Notepad to type and save your program. ● Copy and Paste your program from the Text editor to the command line. If the program works, save the program. Otherwise, fix the error and save it. ● Save your text file regularly.Task 1CREATE DATABASE Bank; USE Bank;create table customer ( customer_id varchar(10) not null, customer_name varchar(20) not null, customer_street varchar(30), customer_city varchar(30), primary key (customer_id)); create table branch ( branch_name varchar(15), branch_city varchar(30), assets int, primary key (branch_name), check (assets >= 0)); create table account ( branch_name varchar(15), account_number varchar(10) not null, balance int, primary key (account_number), check (balance >= 0)); create table loan ( loan_number varchar(10) not null, branch_name varchar(15), amount int, primary key (loan_number)); create table depositor ( customer_id varchar(10) not null, account_number varchar(10) not null, primary key (customer_id,account_number), foreign key (customer_id) references customer(customer_id), foreign key (account_number) references account(account_number)); create table borrower ( customer_id varchar(10) not null, loan_number varchar(10) not null, primary key (customer_id, loan_number), foreign key (customer_id) references customer(customer_id), foreign key (loan_number) references loan(loan_number));Task 2insert intoOnce all your tables have been created, you should insert the data below. The insertion code customer values (‘C-101′,’Jones’, ‘Main’, ‘Harrison’), (‘Chas been provided for you. After insertion, check that data has been correctly inserted in all -201′,’Smith’, ‘North’, ‘Rye’), (‘C-211′,’Hayes’, ‘Main’, ‘Harrison’), (‘C-212′,’Curry’, ‘North’, ‘Rye’), (‘C-215′,’Lindsay’, ‘Park’, ‘Pittsfield’), (‘C-220′,’Turner’, ‘Putnam’, ‘Stamford’), (‘C-222′,’Williams’, ‘Nassau’, ‘Princeton’), (‘C-225′,’Adams’, ‘Spring’, ‘Pittsfield’), (‘C-226′,’Johnson’, ‘Alma’, ‘Palo Alto’), (‘C-233′,’Glenn’, ‘Sand Hill’, ‘Woodside’), (‘C-234′,’Brooks’, ‘Senator’, ‘Brooklyn’), (‘C-255′,’Green’, ‘Walnut’, ‘Stamford’); insert into branch values (‘Downtown’, ‘Brooklyn’,9000000), (‘Redwood’, ‘Palo Alto’,2100000), (‘Perryridge’, ‘Horseneck’,1700000), (‘Mianus’, ‘Horseneck’,400000), (‘Round Hill’, ‘Horseneck’,8000000), (‘Pownal’, ‘Bennington’,300000), (‘North Town’, ‘Rye’,3700000), (‘Brighton’, ‘Brooklyn’,7100000); insert into account values (‘Downtown’,’A-101′,500), (‘Mianus’,’A-215′,700) , (‘Perryridge’,’A-102′,400), (‘Round Hill’,’A-305′,350), (‘Brighton’,’A-201′,900), (‘Redwood’,’A-222′,700), (‘Brighton’,’A-217′,750); insert into loan values (‘L-17’, ‘Downtown’, 1000), (‘L-23’, ‘Redwood’, 2000), (‘L-15’, ‘Perryridge’, 1500), (‘L-14’, ‘Downtown’, 1500), (‘L-93’, ‘Mianus’, 500), (‘L-11’, ‘Round Hill’, 900), (‘L-16’, ‘Perryridge’, 1300); insert into depositor values (‘C-226’, ‘A-101’), (‘C-201’, ‘A-215’), (‘C-211’, ‘A-102’), (‘C-220’, ‘A-305’), (‘C-226’, ‘A-201’), (‘C-101’, ‘A-217’), (‘C-215’, ‘A-222’); insert into borrower values (‘C-101’, ‘L-17’), (‘C-201’, ‘L-23’), (‘C-211’, ‘L-15’), (‘C-226’, ‘L-14’), (‘C-212’, ‘L-93’), (‘C-201’, ‘L-11’), (‘C-222’, ‘L-17’), (‘C-225’, ‘L-16’);Task 3 The command below is a general format for joining two tables. You can replace “Inner Join” with any of the three other Join operations. Select * from Table1 inner join Table2 on1. Retrieve all customer’s id, name, city and account number using a. Inner Join b. Left Join c. Right Join d. Full Join [Not supported by MySQL] 2. Explain the differences you observed between the four joins in Task 3(1). If there was no difference in the result between two or more join operations then explain why.Task 4 You can join more than two tables using the following format: Select * from ((Table1 inner join Table2 on Table1.attribute=Table2.attribute) inner join Table 3 on Table3.attribute = Table1/2.attribute); Retrieve the following information from your database using “join”: Customer name, city, account number, balance and branch name. Task 5 Inner join can also be accomplished without using the “join” keyword in the following way:Select * from T1, T2, T3,…..Tn where T1.attr=T2.attr […..other conditions] ;Apply the above format on Task 4 and compare your results. Task 6 1. Find names and cities of customers who have a loan at Perryridge branch 2. Find the accounts with balances between 700 and 900. 3. Find the names of customers on streets with names ending in “Hill”. 4. Find the names of branches whose assets are greater than the assets of some branch in Brooklyn. 5. Find the set of names of branches whose assets are greater than the assets of all branches in Horseneck. 6. Find the set of names of customers at Brighton branch, in alphabetical order. 7. Show the loan data, ordered by decreasing amounts, then increasing loan numbers. 8. Find the names of branches having at least one account, with average balances greater than or equal 700. 9. Find the names and account number of customers who have the 3 highest balances in their accounts. [Hint: https://www.w3schools.com/sql/sql_top.asp]Task 7Solve the following tasks:1. Find the names of customers with accounts at a branch where Johnson has an account. 2. Find the names of customers with an account but not a loan at Mianus branch. 3. Find the names of each branch and the number of customers having at least one account at that branch. 4. Find the average balance of all customers in ‘Palo Alto’ having at least 2 accounts 5. Find the name and account number of the customer who has the 3rd highest balance in their account.
Project Statement The supermarket chain Good Seed would like to explore whether Data Science can help them adhere to alcohol laws by making sure they do not sell alcohol to people underage. You are asked to conduct that evaluation, so as you set to work, keep the following in mind: The shops are equipped with cameras in the checkout area which are triggered when a person is buying alcohol Computer vision methods can be used to determine age of a person from a photo The task then is to build and evaluate a model for verifying people’s age To start working on the task, you’ll have a set of photographs of people with their ages indicated. Project Instructions 1) Perform exploratory data analysis to get an overall impression of the dataset. 2) Train and evaluate the model. 3) Make conclusions of the model evaluation Data Description The dataset is stored in the /datasets/faces/ folder, there you can find – The final_files folder with 7.6k photos – The labels.csv file with labels, with two columns: file_name and real_age
Course Code: CSE 370 Credits: 3.0 Course Name: Database Systems Semester: Summer 24Lab Assignment 1member_id name email influence_count Joining_date multiplierWrite the queries of the tasks given below [6 * 2 = 12]. 1. Create the above table with the appropriate data type for each column. 2. Change the column name “influence_count”. The new name should be “followers,” and the data type should be integer. 3. Update the number of followers of each developer by +10. 4. There is a formula to find the efficiency of the developers. Efficiency = ((followers*100/1000000) * (multipliers*100/20))/100. Show the efficiency of each developer in a column named “Efficiency” along with their name. 5. Show the name of the developers in UpperCase and the descending order of their Joining_date. 6. Retrieve the member_ id, name, email and followers of the developers who have either “.com” or “.net” in their email address.
Course Name: Database Systems Semester: Summer 24Lab 02: SQL Subqueries & Aggregate Functions Activity List ● All commands are shown in the red boxes. ● In the green box, write the appropriate query/answer. ● All new queries should be typed in the command window after mysql> ● Start by connecting to the server using: mysql -u root -p [password: ] ● For more MySQL queries, go to www.w3schools.com/sql or google it! Initial Table: It’s a bit different than Lab 01! std_id name major section days_present project_mark s cgpa submission_date Link for Table Data: https://docs.google.com/document/d/1ZFFMN863k9GOjTG6ibbCAEEdqF3ExJzug-ymPON6ofA/The purpose of the SELECT statement is to retrieve and display data from one or more database tables. It is an extremely powerful command. SELECT is the most frequently used SQL command and has the following general form: SELECT [DISTINCT | ALL] {* | [columnExpression [AS newName]] [, . . .]} FROM TableName [alias] [, . . .] [WHERE condition] [GROUP BY columnList] [HAVING condition] [ORDER BY columnList] columnExpression represents a column name or an expression, TableName is the name of an existing database table or view that you have access to, and alias is an optional abbreviation for TableName.The sequence of processing in a SELECT statement is: ↓ FROM specifies the table or tables to be used WHERE filters the rows subject to some condition GROUP BY forms groups of rows with the same column value HAVING filters the groups subject to some condition SELECT specifies which columns are to appear in the output ORDER BY specifies the order of the output The order of the clauses in the SELECT statement cannot be changed. The only two mandatory clauses are the first two: SELECT and FROM; the remainder are optional. The SELECT operation is closed: the result of a query on a table is another table. Task 1: Aggregate Functions, Group By and Having:● What is the purpose of the group by keyword? In the above command, if we group by sub_date, instead of major, what will be the output?● The having and where clauses are both used to specify a condition when selecting rows. What is the difference between them?Task 2: Sub Queries/Nested Queries, Any and All:Now, try the nested/sub query on the rightSELECT name FROM Lab_Grades WHERE project_marks=(SELECT MAX(project_marks) FROM Lab_Grades);● Did you understand the role of “any” and “all” in the above queries? Explain below.Task 3: Correlated Subqueries and Exists:● L1 and L2 are temporary aliases and create two separate instances for Lab_Grades; why are they required?● Please identify the difference between the above two queries. [Hint: 1 asks for unique-only 1 student got the highest and the other didn’t]Retrieve the major which has the highest number of students enrolled.Task 4: Take a Quiz SELECT major FROM Lab_Grades GROUP BY major HAVING count(*) >= ALL (SELECT count(*) FROM Lab_Grades GROUP BY major);Go to https://sqlzoo.net/wiki/Nested_SELECT_Quiz to test your understanding of the queries taught in class.
Course Title: Programming Language II Course Code: CSE111 Lab Assignment no: 8Let’s Play with Numbers!!!Write the ComplexNumber class so that the following code generates the output below.class RealNumber: def __init__(self, r=0): self.__realValue = r def getRealValue(self): return self.__realValue def setRealValue(self, r): self.__realValue = r def __str__(self): return ‘RealPart: ‘+str(self.getRealValue()) cn1 = ComplexNumber() print(cn1) print(‘———‘) cn2 = ComplexNumber(5,7) print(cn2) OUTPUT: RealPart: 1.0 ImaginaryPart: 1.0 ——————– RealPart: 5.0 ImaginaryPart: 7.0Write the ComplexNumber class so that the following code generates the output below.class RealNumber: def __init__(self, number=0): self.number = number def __add__(self, anotherRealNumber): return self.number + anotherRealNumber.number def __sub__(self, anotherRealNumber): return self.number – anotherRealNumber.number def __str__(self): return str(self.number) r1 = RealNumber(3) r2 = RealNumber(5) print(r1+r2) cn1 = ComplexNumber(2, 1) print(cn1) cn2 = ComplexNumber(r1, 5) print(cn2) cn3 = cn1 + cn2 print(cn3) cn4 = cn1 – cn2 print(cn4) OUTPUT: 8 2 + 1i 3 + 5i 5 + 6i -1 – 4iWrite the CheckingAccount class so that the following code generates the output below:class Account: def __init__(self, balance): self._balance = balance def getBalance(self): return self._balance print(‘Number of Checking Accounts: ‘, CheckingAccount.numberOfAccount) print(CheckingAccount()) print(CheckingAccount(100.00)) print(CheckingAccount(200.00)) print(‘Number of Checking Accounts: ‘, CheckingAccount.numberOfAccount) OUTPUT: Number of Checking Accounts: 0 Account Balance: 0.0 Account Balance: 100.00 Account Balance: 200.00 Number of Checking Accounts: 3Write the Mango and the Jackfruit classes so that the following code generates the output below:class Fruit: def __init__(self, formalin=False, name=”): self.__formalin = formalin self.name = name def getName(self): return self.name def hasFormalin(self): return self.__formalin class testFruit: def test(self, f): print(‘—-Printing Detail—-‘) if f.hasFormalin(): print(‘Do not eat the’,f.getName(),’.’) print(f) else: print(‘Eat the’,f.getName(),’.’) print(f) m = Mango() j = Jackfruit() t1 = testFruit() t1.test(m) t1.test(j) OUTPUT: —-Printing Detail—– Do not eat the Mango. Mangos are bad for you —-Printing Detail—– Eat the Jackfruit. Jackfruits are good for youWrite the ScienceExam class so that the following code generates the output below:def examSyllabus(self): return “Maths , English” def examParts(self): return “Part 1 – Maths Part 2 – English ”engineering = ScienceExam(100,90,”Physics”,”HigherMaths”) print(engineering) print(‘———————————-‘) print(engineering.examSyllabus()) print(engineering.examParts()) print(‘==================================’) architecture = ScienceExam(100,120,”Physics”,”HigherMaths”,”Drawing”) print(architecture) print(‘———————————-‘) print(architecture.examSyllabus()) print(architecture.examParts()) OUTPUT: Parts: 4 ———————————- Maths , English , Physics , HigherMaths Part 1 – Maths Part 2 – English Part 3 – Physics Part 4 – HigherMaths ================================== Parts: 5 ———————————- Maths , English , Physics , HigherMaths , Drawing Part 1 – Maths Part 2 – English Part 3 – Physics Part 4 – HigherMaths Part 5 – DrawingGiven the following class, write the code for the Sphere and the Cylinder class so that the following output is printed.class Shape3D:pi = 3.14159 def __init__(self, name = ‘Default’, radius = 0): self._area = 0 self._name = name self._height = ‘No need’ self._radius = radiusdef calc_surface_area(self): return 2 * Shape3D.pi * self._radiusdef __str__(self): return “Radius: “+str(self._radius)sph = Sphere(‘Sphere’, 5) print(‘———————————-‘) sph.calc_surface_area() print(sph) print(‘==================================’) cyl = Cylinder(‘Cylinder’, 5, 10) print(‘———————————-‘) cyl.calc_surface_area() print(cyl) OUTPUT: Shape name: Sphere, Area Formula: 4 * pi * r * r ———————————- Radius: 5, Height: No need Area: 314.159 ================================== Shape name: Cylinder, Area Formula: 2 * pi * r * (r + h) ———————————- Radius: 5, Height: 10 Area: 471.2385Write the PokemonExtra class so that the following code generates the output below:class PokemonBasic:def __init__(self, name = ‘Default’, hp = 0, weakness = ‘None’, type = ‘Unknown’): self.name = name self.hit_point = hp self.weakness = weakness self.type = typedef get_type(self): return ‘Main type: ‘ + self.typedef get_move(self): return ‘Basic move: ‘ + ‘Quick Attack’def __str__(self): return “Name: ” + self.name + “, HP: ” + str(self.hit_point) + “, Weakness: ” + self.weaknessprint(‘ ————Basic Info:————–‘) pk = PokemonBasic() print(pk) print(pk.get_type()) print(pk.get_move())print(‘ ————Pokemon 1 Info:————-‘) charmander = PokemonExtra(‘Charmander’, 39, ‘Water’, ‘Fire’) print(charmander) print(charmander.get_type()) print(charmander.get_move())print(‘ ————Pokemon 2 Info:————-‘) charizard = PokemonExtra(‘Charizard’, 78, ‘Water’, ‘Fire’, ‘Flying’, (‘Fire Spin’, ‘Fire Blaze’)) print(charizard) print(charizard.get_type()) print(charizard.get_move()) OUTPUT: ————Basic Info:————– Name: Default, HP: 0, Weakness: None Main type: Unknown Basic move: Quick Attack————Pokemon 1 Info:————– Name: Charmander, HP: 39, Weakness: Water Main type: Fire Basic move: Quick Attack————Pokemon 2 Info:————– Name: Charizard, HP: 78, Weakness: Water Main type: Fire, Secondary type: Flying Basic move: Quick Attack Other move: Fire Spin, Fire Blaze–FootBallTeam and the CricketTeam classes that inherit from Team class so that the following code generates the output below:Driver Code Output class Team: def __init__(self, name): self.name = “default” self.total_player = 5 def info(self) print(“We love sports”) # Write your code here. class Team_test: def check(self, tm): print(“=========================”) print(“Total Player:”, tm.total_player) tm.info()f = FootBallTeam(“Brazil”) c = CricketTeam(“Bangladesh”) test = Team_test() test.check(f) test.check(c) ========================= Total Player: 11 Our name is Brazil We play Football We love sports ========================= Total Player: 11 Our name is Bangladesh We play Cricket We love sports–Pikachu and Charmander classes that are derived from the Pokemon class so that the following output is produced:Driver Code Output class Pokemon: def __init__(self, p): self.pokemon = p self.pokemon_type = “Needs to be set” self.pokemon_weakness = “Needs to be set” def kind(self): return self.pokemon_type def weakness(self): return self.pokemon_weakness def what_am_i(self): print(“I am a Pokemon.”)pk1 = Pikachu() print(“Pokemon:”, pk1.pokemon) print(“Type:”, pk1.kind()) print(“Weakness:”, pk1.weakness()) pk1.what_am_i() print(“========================”) c1 = Charmander() print(“Pokemon:”, c1.pokemon) print(“Type:”, c1.kind()) print(“Weakness:”, c1.weakness()) c1.what_am_i() Pokemon: Pikachu Type: Electric Weakness: Ground I am a Pokemon. I am Pikachu. ======================== Pokemon: Charmander Type: Fire Weakness: Water, Ground and Rock I am a Pokemon. I am Charmander.Task –CSE and EEE classes that are derived from the Department class so that the following output is produced:Driver Code Output class Department: def __init__(self, s): self.semester = s self.name = “Default” self.id = -1 def student_info(self): print(“Name:”, self.name) print(“ID:”, self.id) def courses(self, c1, c2, c3): print(“No courses Approved yet!”)s1 = CSE(“Rahim”, 16101328,”Spring2016″) s1.student_info() s1.courses(“CSE110”, “MAT110”, “ENG101”) print(“==================”) s2 = EEE(“Tanzim”, 18101326, “Spring2018”) s2.student_info() s2.courses(“Mat110”, “PHY111”, “ENG101”) print(“==================”) s3 = CSE(“Rudana”, 18101326, “Fall2017”) s3.student_info() s3.courses(“CSE111”, “PHY101”, “MAT120”) print(“==================”) s4 = EEE(“Zainab”, 19201623, “Summer2019”) s4.student_info() s4.courses(“EEE201”, “PHY112”, “MAT120”) Name: Rahim ID: 16101328 Courses Approved to this CSE student in Spring2016 semester : CSE110 MAT110 ENG101 ================== Name: Tanzim ID: 18101326 Courses Approved to this EEE student in Spring2018 semester : Mat110 PHY111 ENG101 ================== Name: Rudana ID: 18101326 Courses Approved to this CSE student in Fall2017 semester : CSE111 PHY101 MAT120 ================== Name: Zainab ID: 19201623 Courses Approved to this EEE student in Summer2019 semester : EEE201 PHY112 MAT120
Course Title: Programming Language II Course Code: CSE 111 Lab Assignment no: 7** You are not allowed to change any of the code of the tasks ** Use Inheritance to solve all problemsTask – 1Given the following classes, write the code for the BBA_Student class so that the following output is printed:class Student: def __init__(self, name=’Just a student’, dept=’nothing’): self.__name = name self.__department = dept def set_department(self, dept): self.__department = dept def get_name(self): return self.__name def set_name(self,name): self.__name = name def __str__(self): return ‘Name: ‘+self.__name+’ Department: ‘+self.__department #write your code here print(BBA_Student()) print(BBA_Student(‘Humpty Dumpty’)) print(BBA_Student(‘Little Bo Peep’)) Output: Name: default Department: BBA Name: Humpty Dumpty Department: BBA Name: Little Bo Peep Department: BBA–class Vehicle: def __init__(self): self.x = 0 self.y = 0 def moveUp(self): self.y+=1 def moveDown(self): self.y-=1 def moveRight(self): self.x+=1 def moveLeft(self): self.x-=1 def __str__(self): return ‘(‘+str(self.x)+’ , ‘+str(self.y)+’)’ #write your code here print(‘Part 1’) print(‘——‘) car = Vehicle() print(car) car.moveUp() print(car) car.moveLeft() print(car) car.moveDown() print(car) car.moveRight() print(car) print(‘——‘) print(‘Part 2’) print(‘——‘) car1 = Vehicle2010() print(car1) car1.moveLowerLeft() print(car1) car2 = Vehicle2010() car2.moveLeft() print(car1.equals(car2)) car2.moveDown() print(car1.equals(car2)) OUTPUT: Part 1 —— (0 , 0) (0 , 1) (-1 , 1) (-1 , 0) (0 , 0) —— Part 2 —— (0 , 0) (-1 , -1) False True ——A vehicle assumes that the whole world is a 2-dimensional graph paper. It maintains its x and y coordinates (both are integers). The vehicle gets manufactured (constructed) at (0, 0) coordinate. Subtasks: 1. Design a Vehicle2010 class which inherits movement methods from Vehicle and adds new methods called move UpperRight, UpperLeft, LowerRight, LowerLeft. Each of these diagonal move methods must re-use two inherited and appropriate move methods. 2. Write an “equals” method which tests if significant class properties are the same (in this case x and y). Note: All moves are 1 step. That means a single call to any move method changes value of either x or y or both by 1.Task – 3Given the following classes, write the code for the Cricket_Tournament and the Tennis_Tournment class so that the following output is printed.class Tournament: def __init__(self,name=’Default’): self.__name = name def set_name(self,name): self.__name = name def get_name(self): return self.__name#write your code herect1 = Cricket_Tournament() print(ct1.detail()) print(“———————–“) ct2 = Cricket_Tournament(“IPL”,10,”t20″) print(ct2.detail()) print(“———————–“) tt = Tennis_Tournament(“Roland Garros”,128) print(tt.detail()) OUTPUT: Cricket Tournament Name: Default Number of Teams: 0 Type: No type ———————– Cricket Tournament Name: IPL Number of Teams: 10 Type: t20 ———————– Tennis Tournament Name: Roland Garros Number of Players: 128Book and the CD class so that the following output is printed.class Product: def __init__(self,id, title, price): self.__id = id self. __title = title self. __price = price def get_id_title_price(self): return “ID: “+str(self.__id)+” Title:”+self.__title+ “Price: “+str(self.__price) #write your code herebook = Book(1,”The Alchemist”,500,”97806″,”HarperCollins”) print(book.printDetail()) print(“———————–“) cd = CD(2,”Shotto”,300,”Warfaze”,50,”Hard Rock”) print(cd.printDetail()) OUTPUT: ID: 1 Title: The Alchemist Price: 500 ISBN: 97806 Publisher: HarperCollins ———————– ID: 2 Title: Shotto Price: 300 Band: Warfaze Duration: 50minutes Genre: Hard RockDog and the Cat class so that the following output is printed.class Animal: def __init__(self,sound): self.__sound = sound def makeSound(self): return self.__soundclass Printer: def printSound(self, a): print(a.makeSound())#write your code here d1 = Dog(‘bark’) c1 = Cat(‘meow’) a1 = Animal(‘Animal does not make sound’) pr = Printer() pr.printSound(a1) pr.printSound(c1) pr.printSound(d1) OUTPUT: Animal does not make sound meow barkTriangle and the Trapezoid class so that the following output is printed.class Shape:def __init__(self, name=’Default’, height=0, base=0): self.area = 0 self.name = name self.height = height self.base = basedef get_height_base(self): return “Height: “+str(self.height)+”,Base: “+str(self.base) #write your code heretri_default = triangle() tri_default.calcArea() print(tri_default.printDetail()) print(‘————————–‘) tri = triangle(‘Triangle’, 10, 5) tri.calcArea() print(tri.printDetail()) print(‘—————————‘) trap = trapezoid(‘Trapezoid’, 10, 6, 4) trap.calcArea() print(trap.printDetail()) OUTPUT: Shape name: Default Height: 0, Base: 0 Area: 0.0 ————————— Shape name: Triangle Height: 10, Base: 5 Area: 25.0 ————————— Shape name: Trapezoid Height: 10, Base: 6, Side_A: 4 Area: 50.0Player and the Manager class so that the following output is printed. To calculate the match earning use the following formula: 1. Player: (total_goal * 1000) + (total_match * 10) 2. Manager: match_win * 1000class Football:def __init__(self, team_name, name, role): self.__team = team_name self.__name = name self.role = role self.earning_per_match = 0def get_name_team(self): return ‘Name: ‘+self.__name+’, Team Name: ‘ +self.__team#write your code hereplayer_one = Player(‘Juventus’, ‘Ronaldo’, ‘Striker’, 25, 32) player_one.calculate_ratio() player_one.print_details() print(‘——————————————‘) manager_one = Manager(‘Real Madrid’, ‘Zidane’, ‘Manager’, 25) manager_one.print_details() OUTPUT: Name: Ronaldo, Team Name: Juventus Team Role: Striker Total Goal: 25, Total Played: 32 Goal Ratio: 0.78125 Match Earning: 25320K ———————————- Name: Zidane, Team Name: Real Madrid Team Role: Manager Total Win: 25 Match Earning: 25000K
