Author: Laxmi Hegde

My python codes
“A picture is worth a thousand words”

googledataanalyticscertificate_badge20220825-46-6hmn5t Download

Download data set from kaggle

https://www.kaggle.com/datasets/spscientist/students-performance-in-exams

import pandas as pd

df = pd.read_csv(r’C:\Users\laxmi\Documents\StudentsPerformance.csv’)
print(df)

df.info()

import numpy as np

from sklearn.pipeline import make_pipeline

import matplotlib.pyplot as plt
import seaborn as sns
from scipy.stats import norm
from sklearn.metrics import mean_absolute_error

gender_count = df[‘gender’].value_counts()
gender_count = gender_count[:10,]
plt.figure(figsize=(10,5))
sns.barplot(gender_count.index, gender_count.values, alpha=0.8)
plt.title(‘Distribution of Gender’)
plt.ylabel(‘Total Students’, fontsize=12)
plt.xlabel(‘Gender’, fontsize=12)
plt.show()

df = pd.read_csv(r’C:\Users\laxmi\Documents\StudentsPerformance.csv’)
sns.barplot(data=df, x=”Gender”, y=”Total Students”)

import pandas as pd

import seaborn as sns

import matplotlib.pyplot as plt

df_gender = df[‘gender’].value_counts()

plt.bar(df_gender.index, df_gender, color =’red’,
width = 0.4)

plt.xlabel(“Gender”)
plt.ylabel(“No. of students”)
plt.title(“Distribution of Student Gender”)
plt.grid(axis=”y”, alpha=0.75)
plt.show()

df.corr()

import seaborn as sns

sns.heatmap(df.corr());

heatmap = sns.heatmap(df.corr(), vmin=-1, vmax=1, annot=True)

#Give a title to the heatmap. Pade defines the distance of the title from the top of the heatmap

heatmap.set_title(‘Correlation Heatmap’, fontdict={‘fontsize’:12}, pad=12);

import numpy as np

df_race = df[‘race/ethnicity’].value_counts()

plt.barh(df_race.index,df_race.values, color=’r’)

#Axis Label

plt.xlabel(‘Ethnicity’)
plt.ylabel(‘Count’)

#Title

plt.title(“Distribution of Ethnicity Within Students”)

plt.show()

import plotly.express as px

fig2 = px.pie(df, values = df[‘parental level of education’].value_counts().values,
names = df[‘parental level of education’].value_counts()

fig3 = px.pie(df, values = df[‘race/ethnicity’].value_counts().values, names = df[‘race/ethnicity’].value_counts().index)
fig3.show()

fig4 = px.pie(df, values = df[‘lunch’].value_counts().values, names = df[‘lunch’].value_counts().index)
fig4.show()

fig5 = px.pie(df, values = df[‘test preparation course’].value_counts().values, names = df[‘test preparation course’].value_counts().index)
fig5.show()

df[‘math score’].describe()
```
count    1000.00000
mean       66.08900
std        15.16308
min         0.00000
25%        57.00000
50%        66.00000
75%        77.00000
max       100.00000
Name: math score, dtype: float64



mask = np.triu(np.ones_like(df.corr(), dtype=bool))
heatmap = sns.heatmap(df.corr(), mask=mask, vmin=-1, vmax=1, annot=True, cmap='BrBG')
```
sns.histplot(df[‘reading score’], stat=”probability”, fill=True, color =’lightblue’).set(title=’Distribution of Reading Scores’)

Distribution Line

sns.kdeplot(df[‘reading score’], color=”black”)

import seaborn as sns

sns.histplot(df[‘writing score’], stat=”probability”, fill=True, color =’lightblue’).set(title=’Distribution of Reading Scores’)

Distribution Line

sns.kdeplot(df[‘writing score’], color=”black”)

nRowsRead = 1000 # specify ‘None’ if want to read whole file
df1 = pd.read_csv((r’C:\Users\laxmi\Documents\StudentsPerformance.csv’), delimiter=’,’, nrows = nRowsRead)
df1.dataframeName = ‘StudentsPerformance.csv’
nRow, nCol = df1.shape
print(f’There are {nRow} rows and {nCol} columns’)
```
There are 1000 rows and 8 columns

import plotly.express as px

fig10 = px.histogram(df, x="math score", marginal = 'box')
fig10.show()
```
fig11 = px.histogram(df, x=”writing score”, marginal = ‘box’)
fig11.show()

fig13 = px.histogram(df, x=”reading score”, marginal = ‘box’)
fig13.show()

import matplotlib.pyplot as plt

sns.pairplot(df)
plt.show()

df.plot(kind=’box’, subplots=True, layout=(2,4), sharex=False, sharey=False, figsize=(10,8))
plt.show()

print(df.mean(numeric_only=True))
```
math score       66.089
reading score    69.169
writing score    68.054
dtype: float64


df.describe().loc[['min','max','mean']].plot(kind='bar', figsize=(10,8))
plt.show()
```
My git hub link for another project not this

Laxmihe/Pandas-Tricks: Pictorial display of plots (github.com)

https://github.com/Laxmihe?tab=repositories&q=&type=&language=&sort=

https://tinyurl.com/2p8kpukb
January 29, 2023
World Population Dataset
Data Analysis using Postgres SQL

googledataanalyticscertificate_badge20220825-46-6hmn5t-1 Download

–Download dataset from kaggle “World Population dataset–

https://www.kaggle.com/datasets/iamsouravbanerjee/world-population-dataset–

CREATE TABLE public.world_population (

Rank INT,

CCA3 Varchar,

“Country/Territory” VARCHAR(200),

Capital Text,

Continent Text,

“2022 Population” BIGINT,

“2020 Population” BIGINT,

“2015 Population” BIGINT,

“2010 Population” BIGINT,

“2000 Population” BIGINT,

“1990 Population” BIGINT,

“1980 Population” BIGINT,

“1970 Population” BIGINT,

“Area (km²)” NUMERIC(10,2),

“Density (per km²)” NUMERIC(10,2),

“Growth Rate” NUMERIC(10,2),

“World Population Percentage”NUMERIC(10,2)

);

SELECT * FROM public.world_population;

COPY public.world_population

FROM ‘C:\Users\laxmi\Downloads\world_population.csv’

WITH (FORMAT CSV, HEADER);

SELECT * FROM public.world_population;

SELECT ROUND (AVG(“2022 Population”),2) AS mean_pop

FROM public.world_population;

SELECT ROUND (AVG(“Growth Rate”),2) AS mean_pop

FROM public.world_population;

SELECT

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY “Growth Rate”) AS median_pop

FROM public.world_population;

SELECT

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY “2022 Population”) AS median_pop

FROM public.world_population;

SELECT

MIN(“2022 Population”) AS min_pop

FROM public.world_population;

SELECT “2022 Population” ,”rank” “cca3”, “Country/Territory”, “capital”
FROM world_population
WHERE “2022 Population” = ‘510’;

–So Vatican City has minimum population–

SELECT

MIN(“Growth Rate”) AS min_rate

FROM public.world_population;

ALTER TABLE public.world_population
ALTER COLUMN “Growth Rate” TYPE Numeric
USING “Growth Rate”::Numeric;

OR

SELECT “Growth Rate”, CAST (“Growth Rate” AS Numeric( 10,2))

FROM public.world population

SELECT “2022 Population” ,”rank” “cca3”, “Country/Territory”, “capital” “Growth Rate”
FROM world_population
WHERE “Growth Rate” = ‘0.91’;

_–Minimum Growth rate is in Ukraine Kiev–

SELECT

MAX(“2022 Population”) AS Max_pop

FROM public.world_population;

SELECT “2022 Population” ,”rank” “cca3”, “Country/Territory”, “capital”
“Growth Rate”,”capital”
FROM world_population
WHERE “2022 Population” = ‘1425887337’;

–So miximum population in 2022 was China Beijing–

SELECT
MIN(“2022 Population”) AS Min_pop
FROM public.world_population;

SELECT

MAX(“2022 Population”) – MIN(“2022 Population”) AS range_pop

FROM public.world_population;

SELECT
MIN(“Density (per km²)”) AS Min_den
FROM public.world_population;

SELECT (“Density (per km²)”), “capital”, “continent”, “rank”
FROM world_population
WHERE (“Density (per km²)”) <= ‘0.03’

SELECT (“Density (per km²)”), “capital”, “continent”, “rank”,
“Country/Territory”
FROM world_population
WHERE (“Density (per km²)”) >= ‘23172.27’;

SELECT “capital”, “continent”, “rank”, “2022 Population”
“Country/Territory”
FROM world_population
WHERE (“continent”) = ‘Asia’;

SELECT

ROUND(STDDEV(“2020 Population”), 2) AS standard_deviation

FROM public.world_population;
```
SELECT 
```
ROUND(SQRT(VARIANCE(“Growth Rate”)), 2) AS stddev_using_variance
FROM public.world_population;

SELECT
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY “Growth Rate”) AS q1
FROM public.world_population;

WITH mean_median_sd AS

(

SELECT

AVG(“2022 Population”) AS mean,

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY “2020 Population”) AS median,

STDDEV(“2022 Population”) AS stddev

FROM public.world_population

)

SELECT

ROUND(3 * (mean – median)::NUMERIC / stddev, 2) AS skewness

FROM mean_median_sd;

WITH RECURSIVE
summary_stats AS
(
SELECT
ROUND(AVG(“2020 Population”), 2) AS mean,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY “2020 Population”) AS median,
MIN(“2020 Population”) AS min,
MAX(“2020 Population”) AS max,
MAX(“2020 Population”) – MIN(“2020 Population”) AS range,
ROUND(STDDEV(“2020 Population”), 2) AS standard_deviation,
ROUND(VARIANCE(“2020 Population”), 2) AS variance,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY “2020 Population”) AS q1,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY “2020 Population”) AS q3
FROM public.world_population
),
row_summary_stats AS
(
SELECT
1 AS sno,
‘mean’ AS statistic,
mean AS value
FROM summary_stats
UNION
SELECT
2,
‘median’,
median
FROM summary_stats
UNION
SELECT
3,
‘minimum’,
min
FROM summary_stats
UNION
SELECT
4,
‘maximum’,
max
FROM summary_stats
UNION
SELECT
5,
‘range’,
range
FROM summary_stats
UNION
SELECT
6,
‘standard deviation’,
standard_deviation
FROM summary_stats
UNION
SELECT
7,
‘variance’,
variance
FROM summary_stats
UNION
SELECT
9,
‘Q1’,
q1
FROM summary_stats
UNION
SELECT
10,
‘Q3’,
q3
FROM summary_stats
UNION
SELECT
11,
‘IQR’,
(q3 – q1)
FROM summary_stats
UNION
SELECT
12,
‘skewness’,
ROUND(3 * (mean – median)::NUMERIC / standard_deviation, 2) AS skewness
FROM summary_stats
)
SELECT *
FROM row_summary_stats
ORDER BY sno;

SELECT

MODE() WITHIN GROUP (ORDER BY “Growth Rate”) AS mode

FROM public.world_population;

SELECT

COUNT(DISTINCT “Growth Rate”) AS cardinality

FROM public.world_population;

–Periodicity and relative periodicity
Using GROUP BY and COUNT in Postgres, we can determine how often each category appears in a categorical field. In order to calculate the relative frequency, we will utilize a CTE to count all of the values in the rating column. We’ll utilize CTE because not all databases allow window functions. We’ll also go over how to use window functions to calculate relative frequency.–

WITH total_count AS

(

SELECT

COUNT(“Growth Rate”) AS total_cnt

FROM public.world_population

)

SELECT

“Growth Rate”,

COUNT(“Growth Rate”) AS frequency,

ROUND(COUNT(“Growth Rate”)::NUMERIC /

(SELECT total_cnt FROM total_count), 4) AS relative_frequency

FROM public.world_population

GROUP BY “Growth Rate”

ORDER BY frequency DESC;

–The count of values in the rating field is captured by a CTE in the example above. The percentage/relative frequency of each category in the rating field was then determined using it. We’ll explore a less complicated method of computing relative frequency utilizing window functions as Postgres supports them. The total number of values in the rating field is determined by adding the counts of ratings across each category, which we will do using the OVER() function.–

SELECT

“Growth Rate”,

COUNT(“Growth Rate”) AS frequency,

ROUND(COUNT(“Growth Rate”)::NUMERIC / SUM(COUNT(“Growth Rate”)) OVER(), 4) AS relative_frequency

FROM public.world_population

GROUP BY “Growth Rate”

ORDER BY frequency DESC;

SELECT
corr(“Area (km²)”, “Density (per km²)”) as “Corr Coef Using PGSQL Func”
```
FROM public.world_population;
```
ALTER TABLE public.world_population

ALTER COLUMN “Area (km²)” TYPE Numeric

USING “Area (km²)”::Numeric;

–the slope, intercept, and R-squared value of a linear regression model with “Area (km²)” as the independent variable and “Density (per km²)” as the dependent variable. The resulting regression formula and R-squared value would be returned as a string with the alias “regression_formula_output”.

The regr_slope() function is used to calculate the slope of the linear regression line, which represents the average change in the dependent variable (“Density (per km²)”) for a unit change in the independent variable (“Area (km²)”).

The regr_intercept() function is used to calculate the y-intercept of the linear regression line, which represents the value of the dependent variable when the independent variable is zero.

The regr_r2() function is used to calculate the R-squared value of the linear regression model, which represents the proportion of the variance in the dependent variable that is explained by the independent variable.

It is important to note that this query will only work if the database you are querying has a table called “world_population” with columns called “Area (km²)” and “Density (per km²)”. It is also important to make sure that the data in these columns are numeric and appropriate for calculating a linear regression model.–

SELECT ‘Y=’ || regr_slope(“Area (km²)”, “Density (per km²)”) || ‘X+’ ||

regr_intercept(“Area (km²)”, “Density (per km²)”) ||

‘ is the regression formula with R-squared value of ‘ || regr_r2(“Area (km²)”, “Density (per km²)”) AS regression_formula_output

FROM public.world_population;

Textual Analysis for TF-IDF (Term Frequency-Inverse Document Frequency; Row-based and column-based, stop-word removal?

For text analysis download data from here;

https://drive.google.com/file/d/1rf4JDrfsxjLEAC-igRwDZr_cIaJiPm5a/view?usp=sharing

CREATE TABLE president_speeches (

president text NOT NULL,

title text NOT NULL,

speech_date date NOT NULL,

speech_text text NOT NULL,

search_speech_text tsvector,

CONSTRAINT speech_key PRIMARY KEY (president, speech_date)

);

COPY president_speeches (president, title, speech_date, speech_text)

FROM ‘C:\Users\laxmi\Documents\president_speeches.csv’

WITH (FORMAT CSV, DELIMITER ‘|’, HEADER OFF, QUOTE ‘@’);

SELECT * FROM president_speeches;

SELECT * FROM president_speeches ORDER BY speech_date DESC;

SELECT * FROM president_speeches

where to_tsvector(speech_text) @@ to_tsquery(‘government’);

–Speeches where word “government” is used–

ALTER TABLE president_speeches

ADD COLUMN document tsvector;

UPDATE president_speeches

SET document=to_tsvector

(president||”||title||”||speech_date||”||speech_text||”||search_speech_text);

SELECT * FROM president_speeches

where to_tsvector(speech_text) @@ to_tsquery(‘Vietnam’);

SELECT (president, title, speech_date, speech_text)

FROM president_speeches

where to_tsvector

(president||”|| title||”|| speech_date||”|| speech_text) @@ to_tsquery(‘Vietnam’);

UPDATE president_speeches

SET document_with_idx=to_tsvector

(president||”||title||”||speech_text||search_speech_text||”||document||coalesce(speech_text,”));

SELECT (president, title, speech_date, speech_text)
FROM president_speeches
where to_tsvector
(president||”|| title||”|| speech_date||”|| speech_text) @@ to_tsquery(‘Immigration’);

SELECT *
FROM president_speeches
where to_tsvector
(president||”|| title||”|| speech_date||”|| speech_text) @@ to_tsquery(‘tax’)
ORDER BY speech_date;

SELECT * FROM president_speeches
where to_tsvector(speech_text) @@ to_tsquery(‘Korea’);

SELECT * FROM president_speeches
where to_tsvector(speech_text) @@ to_tsquery(‘military <-> defense’);

export default function CoffeeButton() { return ( ); }

☕
Enjoyed this post?

If you found this helpful, consider buying me a coffee. Your support keeps this blog running and helps me create more content.
☕ Buy Me a Coffee

paypal.me/LaxmiHegde
January 2, 2023
My experiment with Power bi
Download Microsoft power bi Desktop version is free 🙂

Click get data

https://www.dropbox.com/sh/0wqw8fmiwrzr8ef/AABQijjQM522INXX1FCdamzma?dl=0

googledataanalyticscertificate_badge20220825-46-6hmn5t-2 Download

Download data from here

SportsStats (Olympics Dataset – 120 years of data)

A sports research company called SportsStats collaborates with professional personal trainers and local news outlets to offer “interesting” information that benefit their partners. For the aim of generating a news article or identifying important health insights, insights could be patterns or trends highlighting specific populations, occasions, or regions, among other things.

#Maximum count of medal has been earned by people in the age group of 23 years,

#Athletics and Swimming are the two sports for which maximum medals are awarded

Michael Fred Phelp II has 28 Medals which is maximum!

Male (19831Medals ) has more medals then females (10350 Medals)

London city has the maximum number of medals ie. 2231 = 7.39%

My second experiment with powerbi

Monthly-Sales-Dashboard/Monthly 7 pbix.pbix at main · Laxmihe/Monthly-Sales-Dashboard (github.com)

Your Repositories (github.com)https://github.com/Laxmihe?tab=repositories

https://public.tableau.com/shared/4QKTXFSS8?:display_count=n&:origin=viz_share_link

sales-by-country-region Download
```
Link to my Tableau Dashboard in Tableau Public
```
```
https://public.tableau.com/shared/4QKTXFSS8?:display_count=n&:origin=viz_share_link

https://tinyurl.com/mstpta98

https://public.tableau.com/views/Dashboard1_16781382242470/Dashboard1?:language=en

https://tinyurl.com/wf936zb9

https://public.tableau.com/app/profile/laxmi.hegde.nagaraj

https://tinyurl.com/ykp7vah5                        best
```
December 3, 2022
My experiment with EXCEL

googledataanalyticscertificate_badge20220825-46-6hmn5t-3 Download

https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DA0130EN-SkillsNetwork/Hands-on%20Labs/Peer%20Graded%20Assignment%20-%20Part%201/Montgomery_Fleet_Equipment_Inventory_FA_PART_1_START.csv

Create a free account here

http://www.office.com

https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DA0130EN-SkillsNetwork/Hands-on%20Labs/Peer%20Graded%20Assignment%20-%20Part%202/Montgomery_Fleet_Equipment_Inventory_FA_PART_2_START.xlsx

https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DV0130EN-SkillsNetwork/Hands-on%20Labs/Lab%201%20-%20Creating%20Basic%20Charts/Car_Sales_Kaggle_DV0130EN_Lab1_Start.xlsx

https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DV0130EN-SkillsNetwork/Hands-on%20Labs/Lab%202%20-%20Creating%20Advanced%20Charts%20/Car_Sales_Kaggle_DV0130EN_Lab2_Start.xlsx
export default function CoffeeButton() { return ( ); }

☕
Enjoyed this post?

If you found this helpful, consider buying me a coffee. Your support keeps this blog running and helps me create more content.
☕ Buy Me a Coffee

paypal.me/LaxmiHegde

November 2, 2022

My experiment with SQL

googledataanalyticscertificate_badge20220825-46-6hmn5t-4 Download

Run Query: Find all the invoices whose total is between $5 and $15 dollars.

While the query in this example is limited to 10 records, running the query correctly will indicate how many total records there are – enter that number below.

Answer = 168

Run Query: Find all the customers from the following States: RJ, DF, AB, BC, CA, WA, NY.

What company does Jack Smith work for?
**1 point**

Microsoft Corp

Apple Inc.

Google Inc.

Rogers Canada

Answer : Microsoft Corp

What was the invoice date for invoice ID 315?

Answer

10-27-2012

Run Query: Find all the tracks whose name starts with ‘All’.

While only 10 records are shown, the query will indicate how many total records there are for this query – enter that number below.

Answer = 15

**Run Query:** Find all the customer emails that start with “J” and are from gmail.com.

Enter the one email address returned (you will likely need to scroll to the right) below.

Answer :

jubarnett@gmail.com

Run Query: Find all the invoices from the billing city Brasília, Edmonton, and Vancouver and sort in descending order by invoice ID.

What is the total invoice amount of the first record returned? Enter the number below without a $ sign. Remember to sort in descending order to get the correct answer.

Answer : 13.86

Run Query: Show the number of orders placed by each customer (hint: this is found in the invoices table) and sort the result by the number of orders in descending order.

What is the number of items placed for the 8th person on this list? Enter that number below.

Answer =7

Different code for the same question

Run Query: Find the albums with 12 or more tracks.

While the number of records returned is limited to 10, the query, if run correctly, will indicate how many total records there are. Enter that number below.

Answer : 158

Same question by different code

MODULE 2 Questions

All of the questions in this quiz pull from the open source Chinook Database. Please refer to the ER Diagram below and familiarize yourself with the table and column names to write accurate queries and get the appropriate answers.

How many albums does the artist Led Zeppelin have?

Now Artist Led Zeppelin ID is 22

Create a list of album titles and the unit prices for the artist “Audioslave”.

Find the first and last name of any customer who does not have an invoice. Are there any customers returned from the query?

Find the total price for each album.

What is the total price for the album “Big Ones”?

ANS 14.85

How many records are created when you apply a Cartesian join to the invoice and invoice items table?

Answer 922880

Module 3 Coding Assignment

All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

Using a subquery, find the names of all the tracks for the album “Californication”.

8th track is

Porcelain

Find the total number of invoices for each customer along with the customer’s full name, city and email.

Find the name and ID of the artists who do not have albums.

**After running the query described above**, two of the records returned have the same last name. Enter that name below.

Gilberto

Use a UNION to create a list of all the employee’s and customer’s first names and last names ordered by the last name in descending order.

**After running the query described above**, determine what is the last name of the 6th record? Enter it below. Remember to order things in descending order to be sure to get the correct answer.

Taylor

See if there are any customers who have a different city listed in their billing city versus their customer city.

Answer

No customers have a different city listed in their billing city versus customer city.

Week 4 Quiz

Pull a list of customer ids with the customer’s full name, and address, along with combining their city and country together. Be sure to make a space in between these two and make it UPPER CASE. (e.g. LOS ANGELES USA)

2.

Question 2

Create a new employee user id by combining the first 4 letters of the employee’s first name with the first 2 letters of the employee’s last name. Make the new field lower case and pull each individual step to show your work.

SELECT FirstName,

LastName,

‘SUBSTR’ (FirstName, 1,4) AS A

‘SUBSTR'(LastName,1,2) AS B

‘SUBSTR'(FirstName,1,4)||’SUBSTR'(LastName,1,2) AS UserId

FROM Employees

What is the final result for Robert King?

RobeKi

Show a list of employees who have worked for the company for 15 or more years using the current date function. Sort by lastname ascending.

What is the lastname of the last person on the list returned?

Peacock

Profiling the Customers table, answer the following question.

SELECT COUNT(*)

FROM Customers

WHERE Phone IS NULL

FAX, Company, Phone , Postal code

Find the cities with the most customers and rank in descending order.

ANS

London, Sao Paulo, Moutain View

Create a new customer invoice id by combining a customer’s invoice id with their first and last name while ordering your query in the following order: firstname, lastname, and invoiceID.

Ans

Select all of the correct “AstridGruber” entries that are returned in your results below. Select all that apply.

AstridGruber273

AstridGruber296

AstridGruber370

Spin-off of “Project: Design a store database”

Made using: Khan Academy Computer Science

Spin-off of “More complex queries with AND/OR”

Made using: Khan Academy Computer Science

CREATE TABLE exercise_logs
(id INTEGER PRIMARY KEY AUTOINCREMENT,
type TEXT,
minutes INTEGER,
calories INTEGER,
heart_rate INTEGER);

INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES (“Yoga”, 30, 100, 110);
INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES (“biking”, 10, 30, 105);
INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES (“dancing”, 15, 200, 120);

SELECT*FROM exercise_logs WHERE calories>100 ORDER BY calories;

/* AND */
SELECT * FROM exercise_logs WHERE calories >50 AND minutes <30;

/OR/
SELECT *FROM exercise_logs WHERE calories >50 OR heart_rate>100;

DATABASE SCHEMA

exercise_logs3 rows
id (PK)INTEGER
typeTEXT
minutesINTEGER
caloriesINTEGER
heart_rateINTEGER

QUERY RESULTS

id	type	minutes	calories	heart_rate
3	dancing	15	200	120

id	type	minutes	calories	heart_rate
3	dancing	15	200	120

id	type	minutes	calories	heart_rate
1	Yoga	30	100	110
2	biking	10	30	105
3	dancing	15	200	120

Spin-off of “Project: Data dig”

Made using: Khan Academy Computer Science

https://www.khanacademy.org/computer-programming/spin-off-of-project-data-dig/4910651633352704

Spin-off of “Project: Data dig”

Made using: Khan Academy Computer Science

https://www.khanacademy.org/computer-programming/spin-off-of-project-data-dig/4579792032153600

Spin-off of “Project: Data dig”

Made using: Khan Academy Computer Science

https://www.khanacademy.org/computer-programming/spin-off-of-project-data-dig/4878908956131328

SELECT Speed,
CASE
WHEN Speed > 50 THEN “below normal”
WHEN Speed > 75 THEN “normal”
WHEN Speed > 100 THEN “above normal”
ELSE “below speed”
END as “Speed_zone”
FROM pokemon;

Speed	Speed_zone
45	below speed
60	below normal
80	below normal
80	below normal
65	below normal
80	below normal
100	below normal
100	below normal
100	below normal
43	below speed
58	below normal
78	below normal
78	below normal
45	below speed
30	below speed
70	below normal
50	below speed
35	below speed
75	below normal
145	below normal
56	below normal
71	below normal
101	below normal
121	below normal
72	below normal
97	below normal
70	below normal
100	below normal
55	below normal
80	below normal
90	below normal
110	below normal
40	below speed
65	below normal
41	below speed
56	below normal
76	below normal
50	below speed
65	below normal
85	below normal
35	below speed
60	below normal
65	below normal
100	below normal
20	below speed
45	below speed
55	below normal
90	below normal
30	below speed
40	below speed
50	below speed
25	below speed
30	below speed
45	below speed
90	below normal
95	below normal
120	below normal
90	below normal
115	below normal
55	below normal
85	below normal
70	below normal
95	below normal
60	below normal
95	below normal
90	below normal
90	below normal
70	below normal
90	below normal
105	below normal
120	below normal
150	below normal
35	below speed
45	below speed
55	below normal
40	below speed
55	below normal
70	below normal
70	below normal
100	below normal
20	below speed
35	below speed
45	below speed
90	below normal
105	below normal
15	below speed
30	below speed
30	below speed
45	below speed
70	below normal
60	below normal
75	below normal
100	below normal
45	below speed
70	below normal
25	below speed
50	below speed
40	below speed
70	below normal
80	below normal
95	below normal
110	below normal
130	below normal
70	below normal
42	below speed
67	below normal
50	below speed
75	below normal
100	below normal
140	below normal
40	below speed
55	below normal
35	below speed
45	below speed
87	below normal
76	below normal
30	below speed
35	below speed
60	below normal
25	below speed
40	below speed
50	below speed
60	below normal
90	below normal
100	below normal
60	below normal
85	below normal
63	below normal
68	below normal
85	below normal
115	below normal
90	below normal
105	below normal
95	below normal
105	below normal
93	below normal
85	below normal
105	below normal
110	below normal
80	below normal
81	below normal
81	below normal
60	below normal
48	below speed
55	below normal
65	below normal
130	below normal
65	below normal
40	below speed
35	below speed
55	below normal
55	below normal
80	below normal
130	below normal
150	below normal
30	below speed
85	below normal
100	below normal
90	below normal
50	below speed
70	below normal
80	below normal
130	below normal
130	below normal
140	below normal
100	below normal
45	below speed
60	below normal
80	below normal
65	below normal
80	below normal
100	below normal
43	below speed
58	below normal
78	below normal
20	below speed
90	below normal
50	below speed
70	below normal
55	below normal
85	below normal
30	below speed
40	below speed
130	below normal
67	below normal
67	below normal
60	below normal
15	below speed
15	below speed
20	below speed
40	below speed
70	below normal
95	below normal
35	below speed
45	below speed
55	below normal
45	below speed
50	below speed
40	below speed
50	below speed
30	below speed
70	below normal
50	below speed
80	below normal
110	below normal
85	below normal
30	below speed
30	below speed
95	below normal
15	below speed
35	below speed
110	below normal
65	below normal
91	below normal
30	below speed
85	below normal
48	below speed
33	below speed
85	below normal
15	below speed
40	below speed
45	below speed
85	below normal
30	below speed
30	below speed
30	below speed
45	below speed
85	below normal
65	below normal
75	below normal
5	below speed
85	below normal
75	below normal
115	below normal
40	below speed
55	below normal
20	below speed
30	below speed
50	below speed
50	below speed
35	below speed
65	below normal
45	below speed
75	below normal
70	below normal
70	below normal
65	below normal
95	below normal
115	below normal
85	below normal
40	below speed
50	below speed
60	below normal
85	below normal
75	below normal
35	below speed
70	below normal
65	below normal
95	below normal
83	below normal
100	below normal
55	below normal
115	below normal
100	below normal
85	below normal
41	below speed
51	below normal
61	below normal
71	below normal
110	below normal
90	below normal
100	below normal
70	below normal
95	below normal
120	below normal
145	below normal
45	below speed
55	below normal
80	below normal
100	below normal
40	below speed
50	below speed
60	below normal
70	below normal
35	below speed
70	below normal
60	below normal
100	below normal
20	below speed
15	below speed
65	below normal
15	below speed
65	below normal
30	below speed
50	below speed
70	below normal
30	below speed
60	below normal
80	below normal
85	below normal
125	below normal
85	below normal
65	below normal
40	below speed
50	below speed
80	below normal
100	below normal
65	below normal
60	below normal
35	below speed
70	below normal
30	below speed
90	below normal
100	below normal
40	below speed
160	below normal
40	below speed
28	below speed
48	below speed
68	below normal
25	below speed
50	below speed
20	below speed
30	below speed
50	below speed
70	below normal
50	below speed
20	below speed
50	below speed
50	below speed
30	below speed
40	below speed
50	below speed
50	below speed
60	below normal
80	below normal
100	below normal
65	below normal
105	below normal
135	below normal
95	below normal
95	below normal
85	below normal
85	below normal
65	below normal
40	below speed
55	below normal
65	below normal
95	below normal
105	below normal
60	below normal
60	below normal
35	below speed
40	below speed
20	below speed
20	below speed
60	below normal
80	below normal
60	below normal
10	below speed
70	below normal
100	below normal
35	below speed
55	below normal
50	below speed
80	below normal
80	below normal
90	below normal
65	below normal
70	below normal
70	below normal
60	below normal
60	below normal
35	below speed
55	below normal
55	below normal
75	below normal
23	below speed
43	below speed
75	below normal
45	below speed
80	below normal
81	below normal
70	below normal
40	below speed
45	below speed
65	below normal
75	below normal
25	below speed
25	below speed
51	below normal
65	below normal
75	below normal
115	below normal
23	below speed
50	below speed
80	below normal
100	below normal
25	below speed
45	below speed
65	below normal
32	below speed
52	below normal
52	below normal
55	below normal
97	below normal
50	below speed
50	below speed
100	below normal
120	below normal
30	below speed
50	below speed
70	below normal
110	below normal
50	below speed
50	below speed
50	below speed
110	below normal
110	below normal
110	below normal
110	below normal
90	below normal
90	below normal
90	below normal
90	below normal
95	below normal
115	below normal
100	below normal
150	below normal
150	below normal
90	below normal
180	below normal
31	below speed
36	below speed
56	below normal
61	below normal
81	below normal
108	below normal
40	below speed
50	below speed
60	below normal
60	below normal
80	below normal
100	below normal
31	below speed
71	below normal
25	below speed
65	below normal
45	below speed
60	below normal
70	below normal
55	below normal
90	below normal
58	below normal
58	below normal
30	below speed
30	below speed
36	below speed
36	below speed
36	below speed
36	below speed
66	below normal
70	below normal
40	below speed
95	below normal
85	below normal
115	below normal
35	below speed
85	below normal
34	below speed
39	below speed
115	below normal
70	below normal
80	below normal
85	below normal
105	below normal
135	below normal
105	below normal
71	below normal
85	below normal
112	below normal
45	below speed
74	below normal
84	below normal
23	below speed
33	below speed
10	below speed
60	below normal
30	below speed
91	below normal
35	below speed
42	below speed
82	below normal
102	below normal
92	below normal
5	below speed
60	below normal
90	below normal
112	below normal
32	below speed
47	below speed
65	below normal
95	below normal
50	below speed
85	below normal
46	below speed
66	below normal
91	below normal
50	below speed
40	below speed
60	below normal
30	below speed
125	below normal
60	below normal
50	below speed
40	below speed
50	below speed
95	below normal
83	below normal
80	below normal
95	below normal
95	below normal
65	below normal
95	below normal
80	below normal
90	below normal
80	below normal
110	below normal
40	below speed
45	below speed
110	below normal
91	below normal
86	below normal
86	below normal
86	below normal
86	below normal
86	below normal
95	below normal
80	below normal
115	below normal
90	below normal
100	below normal
77	below normal
100	below normal
90	below normal
90	below normal
85	below normal
80	below normal
100	below normal
125	below normal
100	below normal
127	below normal
120	below normal
100	below normal
63	below normal
83	below normal
113	below normal
45	below speed
55	below normal
65	below normal
45	below speed
60	below normal
70	below normal
42	below speed
77	below normal
55	below normal
60	below normal
80	below normal
66	below normal
106	below normal
64	below normal
101	below normal
64	below normal
101	below normal
64	below normal
101	below normal
24	below speed
29	below speed
43	below speed
65	below normal
93	below normal
76	below normal
116	below normal
15	below speed
20	below speed
25	below speed
72	below normal
114	below normal
68	below normal
88	below normal
50	below speed
50	below speed
35	below speed
40	below speed
45	below speed
64	below normal
69	below normal
74	below normal
45	below speed
85	below normal
42	below speed
42	below speed
92	below normal
57	below normal
47	below speed
112	below normal
66	below normal
116	below normal
30	below speed
90	below normal
98	below normal
65	below normal
74	below normal
92	below normal
50	below speed
95	below normal
55	below normal
60	below normal
55	below normal
45	below speed
48	below speed
58	below normal
97	below normal
30	below speed
30	below speed
22	below speed
32	below speed
70	below normal
110	below normal
65	below normal
75	below normal
65	below normal
105	below normal
75	below normal
115	below normal
45	below speed
55	below normal
65	below normal
20	below speed
30	below speed
30	below speed
55	below normal
98	below normal
44	below speed
59	below normal
79	below normal
75	below normal
95	below normal
103	below normal
60	below normal
20	below speed
15	below speed
30	below speed
40	below speed
60	below normal
65	below normal
65	below normal
108	below normal
10	below speed
20	below speed
30	below speed
50	below speed
90	below normal
60	below normal
40	below speed
50	below speed
30	below speed
40	below speed
20	below speed
55	below normal
80	below normal
57	below normal
67	below normal
97	below normal
40	below speed
50	below speed
105	below normal
25	below speed
145	below normal
32	below speed
65	below normal
105	below normal
48	below speed
35	below speed
55	below normal
60	below normal
70	below normal
55	below normal
60	below normal
80	below normal
60	below normal
80	below normal
65	below normal
109	below normal
38	below speed
58	below normal
98	below normal
60	below normal
100	below normal
108	below normal
108	below normal
108	below normal
111	below normal
121	below normal
111	below normal
101	below normal
90	below normal
90	below normal
101	below normal
91	below normal
95	below normal
95	below normal
95	below normal
108	below normal
108	below normal
90	below normal
128	below normal
99	below normal
38	below speed
57	below normal
64	below normal
60	below normal
73	below normal
104	below normal
71	below normal
97	below normal
122	below normal
57	below normal
78	below normal
62	below normal
84	below normal
126	below normal
35	below speed
29	below speed
89	below normal
72	below normal
106	below normal
42	below speed
52	below normal
75	below normal
52	below normal
68	below normal
43	below speed
58	below normal
102	below normal
68	below normal
104	below normal
104	below normal
28	below speed
35	below speed
60	below normal
60	below normal
23	below speed
29	below speed
49	below speed
72	below normal
45	below speed
73	below normal
50	below speed
68	below normal
30	below speed
44	below speed
44	below speed
59	below normal
70	below normal
109	below normal
48	below speed
71	below normal
46	below speed
58	below normal
60	below normal
118	below normal
101	below normal
50	below speed
40	below speed
60	below normal
80	below normal
75	below normal
38	below speed
56	below normal
51	below normal
56	below normal
46	below speed
41	below speed
84	below normal
99	below normal
69	below normal
54	below normal
28	below speed
28	below speed
55	below normal
123	below normal
99	below normal
99	below normal
95	below normal
50	below speed
110	below normal
70	below normal
80	below normal
70	below normal

https://www.khanacademy.org/computer-programming/spin-off-of-challenge-dynamic-documents/4504908035833856

CREATE TABLE clothes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
type TEXT,
design TEXT);

INSERT INTO clothes (type, design)
VALUES (“dress”, “pink polka dots”);
INSERT INTO clothes (type, design)
VALUES (“pants”, “rainbow tie-dye”);
INSERT INTO clothes (type, design)
VALUES (“blazer”, “black sequin”);
SELECT * FROM clothes;
ALTER TABLE clothes ADD price TEXT default “unknown”;
SELECT * FROM clothes;

DATABASE SCHEMA

clothes3 rows
id (PK)INTEGER
typeTEXT
designTEXT
priceTEXT

QUERY RESULTS

id	type	design
1	dress	pink polka dots
2	pants	rainbow tie-dye
3	blazer	black sequin

id	type	design	price
1	dress	pink polka dots	unknown
2	pants	rainbow tie-dye	unknown
3	blazer	black sequin	unknown

Coursera SQL for Data Science project

Yelp Dataset ER Diagram

less

The entity-relationship (ER) diagram below, should help familiarize you with the design of the Yelp Dataset provided for this peer review activity.

Data Scientist Role Play: Profiling and Analyzing the Yelp Dataset Coursera Worksheet

This is a 2-part assignment. In the first part, you are asked a series of questions that
will help you profile and understand the data just like a data scientist would. For this
first part of the assignment, you will be assessed both on the correctness of your
findings, as well as the code you used to arrive at your answer. You will be graded on
how easy your code is to read, so remember to use proper formatting and comments where
necessary.

In the second part of the assignment, you are asked to come up with your own inferences
and analysis of the data for a particular research question you want to answer. You will be
required to prepare the dataset for the analysis you choose to do. As with the first part,
you will be graded, in part, on how easy your code is to read, so use proper formatting
and comments to illustrate and communicate your intent as required.

For both parts of this assignment, use this “worksheet.” It provides all the questions
you are being asked, and your job will be to transfer your answers and SQL coding where
indicated into this worksheet so that your peers can review your work. You should be able
to use any Text Editor (Windows Notepad, Apple TextEdit, Notepad ++, Sublime Text, etc.)
to copy and paste your answers. If you are going to use Word or some other page layout
application, just be careful to make sure your answers and code are lined appropriately.
In this case, you may want to save as a PDF to ensure your formatting remains intact
for you reviewer.

SELECT COUNT(*)
FROM ‘Attribute table;
Attribute table = 10000
Business table = 10000
category table = 10000
checkin table = 10000
elite_years table = 10000
friend table = 10000
hours table = 10000
photo table = 10000
review table = 10000
tip table = 10000
user table = 10000

/*
Find the total number of distinct records for each of the keys listed below:*/

SELECT COUNT (DISTINCT(id))
FROM business;

| COUNT (DISTINCT(id)) |
+———————-+
| 10000 |

Answer Business = 10000

SELECT COUNT (DISTINCT business_id)
FROM hours;

+——————————+
| COUNT (DISTINCT business_id) |
+——————————+
| 1562

Answer Hours = 1562

SELECT COUNT (DISTINCT business_id)
FROM category;

+——————————+
| COUNT (DISTINCT business_id) |
+——————————+
| 2643 |
+——————————

Answer Category =2643

SELECT COUNT (DISTINCT(business_id))
FROM attribute;
+——————————-+
| COUNT (DISTINCT(business_id)) |
+——————————-+
| 1115 |
+——————————-

Answer Attribute = 1115

SELECT COUNT (DISTINCT id)
FROM review;

+———————+
| COUNT (DISTINCT id) |
+———————+
| 10000 |
+———————
Answer Review = 10000

SELECT COUNT (DISTINCT business_id)
FROM checkin;

+——————————+
| COUNT (DISTINCT business_id) |
+——————————+
| 493 |
+——————————+

Answer Checkin = 493

SELECT COUNT (DISTINCT id)
FROM photo;

+———————+
| COUNT (DISTINCT id) |
+———————+
| 10000 |
+———————

Answer Photo =10000

SELECT COUNT (DISTINCT user_id)
FROM tip;

+————————–+
| COUNT (DISTINCT user_id) |
+————————–+
| 537

Answer Tip = 537

SELECT COUNT (DISTINCT id)
FROM user;

+———————+
| COUNT (DISTINCT id) |
+———————+
| 10000 |
+———————+

Answer User = 10000

SELECT COUNT (DISTINCT user_id)
FROM friend;

+————————–+
| COUNT (DISTINCT user_id) |
+————————–+
| 11 |
+————————–

Answer Friend = 11

SELECT COUNT (DISTINCT user_id)
FROM elite_years;

+————————–+
| COUNT (DISTINCT user_id) |
+————————–+
| 2780 |
+————————–

/*
Are there any columns with null values in the Users table? Indicate “yes,” or “no.”
Answer: “no”

SQL code used to arrive at answer:*/

SELECT id
,name
,review_count
,cool
,yelping_since
,useful
,funny
,fans
,average_stars
FROM user
WHERE id IS NULL OR
name IS NULL OR
review_count IS NULL ;

Answer = no

/* Find the minimum, maximum, and average value for the following fields:*/

SELECT AVG (stars)
FROM review;

+————-+
| AVG (stars) |
+————-+
| 3.7082 |
+————-+
Average = 3,7082

i. Table: Review, Column: Stars

min: 1 max: 5 avg: 3.7082

SELECT MAX (stars)
FROM review;

+————-+
| MAX (stars) |
+————-+
| 5 |
+————-

Answer Max=5

SELECT Min (stars)
FROM review;

+————-+
| Min (stars) |
+————-+
| 1 |
+————-

Answer Min =1

SELECT Min (stars)
FROM business;

+————-+
| Min (stars) |
+————-+
| 1.0 |

Answer Business Min =1

SELECT Max (stars)
FROM business;

+————-+
| Max (stars) |
+————-+
| 5.0 |
+————-
+————-+
| MAX (stars) |
+————-+
| 5.0 |
+————-+

Answer Business Max=5

SELECT avg(stars)
FROM business;
+————+
| avg(stars) |
+————+
| 3.6549 |
+————+

Answer Business Average =3.6549

ii. Table: Business, Column: Stars

min: 1 max: 5 avg: 3.6549

SELECT min(likes)
FROM tip;

+————+
| min(likes) |
+————+
| 0 |
+————

Answer min of likes =0

SELECT max(likes)
FROM tip;

+————+
| max(likes) |
+————+
| 2 |
+————+

Answer max of likes = 2

SELECT avg(likes)
FROM tip;

————+
| avg(likes) |
+————+
| 0.0144 |
+————+

Answer avg of likes = 0.0144

iii. Table: Tip, Column: Likes

min: 0 max: 2 avg: 0.0144

SELECT min(count)
FROM checkin;

+————+
| min(count) |
+————+
| 1 |
+————
Answer of count min =1

SELECT max(count)
FROM checkin;

+————+
| max(count) |
+————+
| 53 |
+————+

Answer of max of checkin is 53

SELECT avg(count)
FROM checkin;

+————+
| avg(count) |
+————+
| 1.9414 |
+————

Answer of avg of checkin = 1,9414

iv. Table: Checkin, Column: Count

min: 1 max: 53 avg: 1.9414

SELECT min(review_count)
FROM user;

——————-+
| min(review_count) |
+——————-+
| 0 |
+——————-

The answer of min of users =0

SELECT max(review_count)
FROM user;

max(review_count) |
+——————-+
| 2000 |

Answer of max of review count = 2000

SELECT avg(review_count)
FROM user;

+——————-+
| avg(review_count) |
+——————-+
| 24.2995 |
+——————-+

Answer average of review count = 24,2995

v. Table: User, Column: Review_count

min: 0 max: 2000 avg: 24.2995

/* List the cities with the most reviews in descending order:

SQL code used to arrive at the answer: */

SELECT city
, SUM (review_count) AS reviews
FROM business
GROUP BY city
ORDER BY reviews DESC

Copy and Paste the Result Below:

+—————–+———+
| city | reviews |
+—————–+———+
| Las Vegas | 82854 |
| Phoenix | 34503 |
| Toronto | 24113 |
| Scottsdale | 20614 |
| Charlotte | 12523 |
| Henderson | 10871 |
| Tempe | 10504 |
| Pittsburgh | 9798 |
| Montréal | 9448 |
| Chandler | 8112 |
| Mesa | 6875 |
| Gilbert | 6380 |
| Cleveland | 5593 |
| Madison | 5265 |
| Glendale | 4406 |
| Mississauga | 3814 |
| Edinburgh | 2792 |
| Peoria | 2624 |
| North Las Vegas | 2438 |
| Markham | 2352 |
| Champaign | 2029 |
| Stuttgart | 1849 |
| Surprise | 1520 |
| Lakewood | 1465 |
| Goodyear | 1155 |
+—————–+———+
(Output limit exceeded, 25 of 362 total rows shown)

/* Find the distribution of star ratings to the business in the following cities:

i. Avon

SQL code used to arrive at the answer:*/

SELECT (stars) AS star_rating
,city
FROM business
GROUP BY city
ORDER BY COUNT (star_rating)

Star Rating Count

0 0

1 0

1.5 1

2 0

2.5 2

3 0

3.5 3

4 2

4.5 1

5 1

/*ii. Beachwood

SQL code used to arrive at the answer:*/

SELECT stars,

SUM(review_count) AS count

FROM business

WHERE city == ‘Beachwood’

GROUP BY stars

Star Rating Count

0 0
1 0
1.5 0
2 1
2.5 1
3 2
3.5 2
4 1
4.5 2
5 5

/* Find the top 3 users based on their total number of reviews:

SQL code used to arrive at answer:*/

SELECT id
,name
,review_count
FROM user
ORDER BY review_count DESC
LIMIT 3 ;

Copy and Paste the Result Below:

+————————+——–+————–+
| id | name | review_count |
+————————+——–+————–+
| -G7Zkl1wIWBBmD0KRy_sCw | Gerald | 2000 |
| -3s52C4zL_DHRK0ULG6qtg | Sara | 1629 |
| -8lbUNlXVSoXqaRRiHiSNg | Yuri | 1339 |
+————————+——–+————–

/*8. Does posing more reviews correlate with more fans?

Yes, but you should also consider how long they have been yelping. Their fan base grows
as they have more reviews and longer-running Yelp accounts.

Please explain your findings and interpretation of the results:*/

SELECT id
,name
,review_count
,fans
,yelping_since
,useful
FROM user
ORDER BY fans DESC;

+————————+———–+————–+——+———————+——–+
| id | name | review_count | fans | yelping_since | useful |
+————————+———–+————–+——+———————+——–+
| -9I98YbNQnLdAmcYfb324Q | Amy | 609 | 503 | 2007-07-19 00:00:00 | 3226 |
| -8EnCioUmDygAbsYZmTeRQ | Mimi | 968 | 497 | 2011-03-30 00:00:00 | 257 |
| –2vR0DIsmQ6WfcSzKWigw | Harald | 1153 | 311 | 2012-11-27 00:00:00 | 122921 |
| -G7Zkl1wIWBBmD0KRy_sCw | Gerald | 2000 | 253 | 2012-12-16 00:00:00 | 17524 |
| -0IiMAZI2SsQ7VmyzJjokQ | Christine | 930 | 173 | 2009-07-08 00:00:00 | 4834 |
| -g3XIcCb2b-BD0QBCcq2Sw | Lisa | 813 | 159 | 2009-10-05 00:00:00 | 48 |
| -9bbDysuiWeo2VShFJJtcw | Cat | 377 | 133 | 2009-02-05 00:00:00 | 1062 |
| -FZBTkAZEXoP7CYvRV2ZwQ | William | 1215 | 126 | 2015-02-19 00:00:00 | 9363 |
| -9da1xk7zgnnfO1uTVYGkA | Fran | 862 | 124 | 2012-04-05 00:00:00 | 9851 |
| -lh59ko3dxChBSZ9U7LfUw | Lissa | 834 | 120 | 2007-08-14 00:00:00 | 455 |
| -B-QEUESGWHPE_889WJaeg | Mark | 861 | 115 | 2009-05-31 00:00:00 | 4008 |
| -DmqnhW4Omr3YhmnigaqHg | Tiffany | 408 | 111 | 2008-10-28 00:00:00 | 1366 |
| -cv9PPT7IHux7XUc9dOpkg | bernice | 255 | 105 | 2007-08-29 00:00:00 | 120 |
| -DFCC64NXgqrxlO8aLU5rg | Roanna | 1039 | 104 | 2006-03-28 00:00:00 | 2995 |
| -IgKkE8JvYNWeGu8ze4P8Q | Angela | 694 | 101 | 2010-10-01 00:00:00 | 158 |
| -K2Tcgh2EKX6e6HqqIrBIQ | .Hon | 1246 | 101 | 2006-07-19 00:00:00 | 7850 |
| -4viTt9UC44lWCFJwleMNQ | Ben | 307 | 96 | 2007-03-10 00:00:00 | 1180 |
| -3i9bhfvrM3F1wsC9XIB8g | Linda | 584 | 89 | 2005-08-07 00:00:00 | 3177 |
| -kLVfaJytOJY2-QdQoCcNQ | Christina | 842 | 85 | 2012-10-08 00:00:00 | 158 |
| -ePh4Prox7ZXnEBNGKyUEA | Jessica | 220 | 84 | 2009-01-12 00:00:00 | 2161 |
| -4BEUkLvHQntN6qPfKJP2w | Greg | 408 | 81 | 2008-02-16 00:00:00 | 820 |
| -C-l8EHSLXtZZVfUAUhsPA | Nieves | 178 | 80 | 2013-07-08 00:00:00 | 1091 |
| -dw8f7FLaUmWR7bfJ_Yf0w | Sui | 754 | 78 | 2009-09-07 00:00:00 | 9 |
| -8lbUNlXVSoXqaRRiHiSNg | Yuri | 1339 | 76 | 2008-01-03 00:00:00 | 1166 |
| -0zEEaDFIjABtPQni0XlHA | Nicole | 161 | 73 | 2009-04-30 00:00:00 | 13 |
+————————+———–+————–+——+———————+——–+
(Output limit exceeded, 25 of 10000 total rows shown)

/*9. Are there more reviews with the word “love” or with the word “hate” in them?

Answer: love has 1780, while hate only has 232 🙂 “Love triumphs”

SQL code used to arrive at answer:*/

SELECT COUNT (*)
FROM review
WHERE text LIKE ‘%love%’ ;

+———–+
| COUNT (*) |
+———–+
| 1780 |
+———–+

SELECT COUNT (*)
FROM review
WHERE text LIKE ‘%hate%’ ;

+———–+
| COUNT (*) |
+———–+
| 232 |
+———–

= 1780 = 232
Love is more

/*10. Find the top 10 users with the most fans:

SQL code used to arrive at answer:*/

SELECT id
,name
,fans
,yelping_since
FROM user
ORDER BY fans DESC
LIMIT 10;

Copy and Paste the Result Below:

+————————+———–+——+———————+
| id | name | fans | yelping_since |
+————————+———–+——+———————+
| -9I98YbNQnLdAmcYfb324Q | Amy | 503 | 2007-07-19 00:00:00 |
| -8EnCioUmDygAbsYZmTeRQ | Mimi | 497 | 2011-03-30 00:00:00 |
| –2vR0DIsmQ6WfcSzKWigw | Harald | 311 | 2012-11-27 00:00:00 |
| -G7Zkl1wIWBBmD0KRy_sCw | Gerald | 253 | 2012-12-16 00:00:00 |
| -0IiMAZI2SsQ7VmyzJjokQ | Christine | 173 | 2009-07-08 00:00:00 |
| -g3XIcCb2b-BD0QBCcq2Sw | Lisa | 159 | 2009-10-05 00:00:00 |
| -9bbDysuiWeo2VShFJJtcw | Cat | 133 | 2009-02-05 00:00:00 |
| -FZBTkAZEXoP7CYvRV2ZwQ | William | 126 | 2015-02-19 00:00:00 |
| -9da1xk7zgnnfO1uTVYGkA | Fran | 124 | 2012-04-05 00:00:00 |
| -lh59ko3dxChBSZ9U7LfUw | Lissa | 120 | 2007-08-14 00:00:00 |
+————————+———–+——+———————+

/*9. Are there more reviews with the word “love” or with the word “hate” in them?

Answer: love has 1780, while hate only has 232 🙂 “Love triumphs”*/

SQL code used to arrive at answer:

SQL code used to arrive at answer:*/

SELECT COUNT (*)
FROM review
WHERE text LIKE ‘%love%’ ;

+———–+
| COUNT (*) |
+———–+
| 1780 |
+———–+

SELECT COUNT (*)
FROM review
WHERE text LIKE ‘%hate%’ ;

+———–+
| COUNT (*) |
+———–+
| 232 |
+———–

= 1780 = 232
Love is more

= 1780 = 232

/*10. Find the top 10 users with the most fans:

SQL code used to arrive at answer:*/

SELECT id,

name,

fans

FROM user

ORDER BY fans DESC

LIMIT 10

Copy and Paste the Result Below:

+————————+———–+——+

| id | name | fans |

+————————+———–+——+

| -9I98YbNQnLdAmcYfb324Q | Amy | 503 |

| -8EnCioUmDygAbsYZmTeRQ | Mimi | 497 |

| –2vR0DIsmQ6WfcSzKWigw | Harald | 311 |

| -G7Zkl1wIWBBmD0KRy_sCw | Gerald | 253 |

| -0IiMAZI2SsQ7VmyzJjokQ | Christine | 173 |

| -g3XIcCb2b-BD0QBCcq2Sw | Lisa | 159 |

| -9bbDysuiWeo2VShFJJtcw | Cat | 133 |

| -FZBTkAZEXoP7CYvRV2ZwQ | William | 126 |

| -9da1xk7zgnnfO1uTVYGkA | Fran | 124 |

| -lh59ko3dxChBSZ9U7LfUw | Lissa | 120 |

/*11. Is there a strong correlation between having a high number of fans and being listed
as “useful” or “funny?”

Yes, see the interpretation.

SQL code used to arrive at answer: */

SELECT name
,fans
,useful
,funny
,review_count
,yelping_since
,cool
FROM user
ORDER BY fans DESC ;

+———–+——+——–+——–+————–+———————+——–+
| name | fans | useful | funny | review_count | yelping_since | cool |
+———–+——+——–+——–+————–+———————+——–+
| Amy | 503 | 3226 | 2554 | 609 | 2007-07-19 00:00:00 | 2751 |
| Mimi | 497 | 257 | 138 | 968 | 2011-03-30 00:00:00 | 159 |
| Harald | 311 | 122921 | 122419 | 1153 | 2012-11-27 00:00:00 | 122890 |
| Gerald | 253 | 17524 | 2324 | 2000 | 2012-12-16 00:00:00 | 15008 |
| Christine | 173 | 4834 | 6646 | 930 | 2009-07-08 00:00:00 | 4321 |
| Lisa | 159 | 48 | 13 | 813 | 2009-10-05 00:00:00 | 6 |
| Cat | 133 | 1062 | 672 | 377 | 2009-02-05 00:00:00 | 1076 |
| William | 126 | 9363 | 9361 | 1215 | 2015-02-19 00:00:00 | 9370 |
| Fran | 124 | 9851 | 7606 | 862 | 2012-04-05 00:00:00 | 9344 |
| Lissa | 120 | 455 | 150 | 834 | 2007-08-14 00:00:00 | 342 |
| Mark | 115 | 4008 | 570 | 861 | 2009-05-31 00:00:00 | 2765 |
| Tiffany | 111 | 1366 | 984 | 408 | 2008-10-28 00:00:00 | 1279 |
| bernice | 105 | 120 | 112 | 255 | 2007-08-29 00:00:00 | 109 |
| Roanna | 104 | 2995 | 1188 | 1039 | 2006-03-28 00:00:00 | 636 |
| Angela | 101 | 158 | 164 | 694 | 2010-10-01 00:00:00 | 105 |
| .Hon | 101 | 7850 | 5851 | 1246 | 2006-07-19 00:00:00 | 5104 |
| Ben | 96 | 1180 | 1155 | 307 | 2007-03-10 00:00:00 | 1143 |
| Linda | 89 | 3177 | 2736 | 584 | 2005-08-07 00:00:00 | 3019 |
| Christina | 85 | 158 | 34 | 842 | 2012-10-08 00:00:00 | 102 |
| Jessica | 84 | 2161 | 2091 | 220 | 2009-01-12 00:00:00 | 2067 |
| Greg | 81 | 820 | 753 | 408 | 2008-02-16 00:00:00 | 746 |
| Nieves | 80 | 1091 | 774 | 178 | 2013-07-08 00:00:00 | 940 |
| Sui | 78 | 9 | 18 | 754 | 2009-09-07 00:00:00 | 2 |
| Yuri | 76 | 1166 | 220 | 1339 | 2008-01-03 00:00:00 | 561 |
| Nicole | 73 | 13 | 10 | 161 | 2009-04-30 00:00:00 | 6 |
+———–+——+——–+——–+————–+———————+——–+

/*Please explain your findings and interpretation of the results:

Yes, but number three Harald does appear to be a significant anomaly.
More “helpful” “cool” and “funny” reviews get more fans for the other users,
but also in conjunction with the review count and length of time they have
been yelping

Part 2: Inferences and Analysis

Pick one city and category of your choice and group the businesses in that city
or category by their overall star rating. Compare the businesses with 2-3 stars to
the businesses with 4-5 stars and answer the following questions. Include your code.

i. Do the two groups you chose to analyze have a different distribution of hours?

The 4-5 star group seems to have shorter hours then the 2-3 star group.
Please note the query returned only three businesses so not a great
sample size.

ii. Do the two groups you chose to analyze have a different number of reviews?

Yes and no, one of the 4-5 star group has a lot more reviews but then the other
4-5 star group has close to the same number of reviews as the 2-3 star group

iii. Are you able to infer anything from the location data provided between these two
groups? Explain.

No, every business is in a different zip-code.

SQL code used for analysis:

SELECT business_id
,category
FROM category
GROUP BY category
ORDER BY category=’%shopping%’;

SELECT city
,id
,stars
FROM business
GROUP BY stars
ORDER BY 2 OR 3;

SELECT city
,id
,stars
FROM business
WHERE stars BETWEEN 2 AND 3;

SELECT city
,id
,stars
FROM business
WHERE stars BETWEEN 4 AND 5;

Group business based on the ones that are open and the ones that are closed. What
differences can you find between the ones that are still open and the ones that are
closed? List at least two differences and the SQL code you used to arrive at your
answer.

i. Difference 1:

The businesses that are open tend to have more reviews than ones that
are closed on average.

Open: AVG(review_count) = 31.757
Closed: AVG(review_count0 = 23.198

/*ii. Difference 2:

The average star rating is higher for businesses that are open than
businesses that are closed.

Open: AVG(stars) = 3.679
Closed: AVG(stars) = 3.520

SQL code used for analysis: */

SELECT COUNT (DISTINCT (id))
,AVG (review_count)
FROM business;

+———————–+——————–+
| COUNT (DISTINCT (id)) | AVG (review_count) |
+———————–+——————–+
| 10000 | 30.4561 |
+———————–+——————–

SELECT COUNT (DISTINCT (id))
,AVG (review_count)
,SUM(review_count)
FROM business;

+———————–+——————–+——————-+
| COUNT (DISTINCT (id)) | AVG (review_count) | SUM(review_count) |
+———————–+——————–+——————-+
| 10000 | 30.4561 | 304561 |
+———————–+——————–+——————-+

SELECT COUNT (DISTINCT (id))
,AVG (review_count)
,SUM(review_count)
,AVG (stars)
FROM business;

+———————–+——————–+——————-+————-+
| COUNT (DISTINCT (id)) | AVG (review_count) | SUM(review_count) | AVG (stars) |
+———————–+——————–+——————-+————-+
| 10000 | 30.4561 | 304561 | 3.6549 |
+———————–+——————–+——————-+————-

SELECT COUNT (DISTINCT (id))
,AVG (review_count)
,SUM(review_count)
,AVG (stars)
,is_open
FROM business;

+———————–+——————–+——————-+————-+———+
| COUNT (DISTINCT (id)) | AVG (review_count) | SUM(review_count) | AVG (stars) | is_open |
+———————–+——-|————+——————-+————-+———+
| 10000 | |30.4561 | 304561 | 3.6549 | 1 |
+———————–+——————–+——————-+————-+———+

SELECT COUNT (DISTINCT (id))
,AVG (review_count)
,SUM(review_count)
,AVG (stars)
,is_open
FROM business
GROUP BY is_open;

+———————–+——————–+——————-+—————+———+
| COUNT (DISTINCT (id)) | AVG (review_count) | SUM(review_count) | AVG (stars) | is_open |
+———————–+——————–+——————-+—————+———+
| 1520 | 23.1980263158 | 35261 | 3.52039473684 | 0 |
| 8480 | 31.7570754717 | 269300 | 3.67900943396 | 1 |
+———————–+——————–+——————-+—————+———

Answer review count 35261 < 269300 so The businesses that are open tend to have more reviews

For this last part of your analysis, you are going to choose the type of analysis you
want to conduct on the Yelp dataset and are going to prepare the data for analysis.

Ideas for analysis include: Parsing out keywords and business attributes for sentiment
analysis, clustering businesses to find commonalities or anomalies between them,
predicting the overall star rating for a business, predicting the number of fans a
user will have, and so on. These are just a few examples to get you started, so feel
free to be creative and come up with your own problem you want to solve. Provide
answers, in-line, to all of the following:

i. Indicate the type of analysis you chose to do:

Predicting whether a business will stay open or close. We wish not to explicitly
examine the text of the reviews, but this would be an interesting analysis.

ii. Write 1-2 brief paragraphs on the type of data you will need for your analysis
and why you chose that data:

To better help businesses understand the importance of different factors which
will help their business stay open. Some data that may be important; number of
reviews, star rating of business, hours open, and of course location location
location. We will gather the latitude and longitude as well as city, state,
postal_code, and address to make processing easier later on. Categories and
attributes will be used to better distinguish between different types of
businesses. is_open will determine which business is open and which business
have closed (not hours) but permanently.

iii. Output of your finished dataset:

SELECT id
,is_open
,stars
,review_count
FROM business
GROUP BY review_count
ORDER BY review_count DESC
LIMIT 20 ;

+————————+———+——-+————–+
| id | is_open | stars | review_count |
+————————+———+——-+————–+
| 2weQS-RnoOBhb1KsHKyoSQ | 1 | 3.5 | 3873 |
| 0W4lkclzZThpx3V65bVgig | 1 | 4.0 | 1757 |
| 0FUtlsQrJI7LhqDPxLumEw | 1 | 4.0 | 1549 |
| 2iTsRqUsPGRH1li1WVRvKQ | 1 | 4.5 | 1410 |
| –9e1ONYQuAa-CB_Rrw7Tw | 1 | 4.0 | 1389 |
| -ed0Yc9on37RoIoG2ZgxBA | 1 | 4.0 | 1252 |
| 0NmTwqYEQiKErDv4a55obg | 1 | 4.0 | 1116 |
| 0AQnRQw34IQW9-1gJkYnMA | 1 | 3.0 | 1084 |
| -U7tvCtaraTQ9b0zBhpBMA | 1 | 2.5 | 961 |
| -6tvduBzjLI1ISfs3F_qTg | 1 | 4.0 | 902 |
| 364hhL5st0LV16UcBHRJ3A | 1 | 4.5 | 864 |
| -FLnsWAa4AGEW4NgE8Fqew | 1 | 3.5 | 823 |
| 2sx52lDoiEtef7xgPCaoBw | 1 | 4.5 | 821 |
| 0_aeYE2-VbsZts_UpILgDw | 1 | 4.0 | 786 |
| 0ldxjei8v4q95fApIei3Lg | 1 | 4.0 | 785 |
| -av1lZI1JDY_RZN2eTMnWg | 1 | 3.5 | 778 |
| 1ZnVfS-qP19upP_fwOhZsA | 1 | 4.0 | 768 |
| 0q_BHpxbikVtPRRLRu-U0g | 1 | 4.5 | 758 |
| 1d6c6Q2j2jwVzBfX_dLHlg | 1 | 4.0 | 726 |
| -Eu04UHRqmGGyvYRDY8-tg | 1 | 4.5 | 723 |
+————————+———+——-+————–+

SELECT id
,is_open
,stars
,review_count
FROM business
GROUP BY is_open
ORDER BY review_count DESC
LIMIT 20 ;

+————————+———+——-+————–+
| id | is_open | stars | review_count |
+————————+———+——-+————–+
| 38tScZkvRLoa5h-wNPyjkw | 1 | 2.5 | 25 |
| 38k_heLKR2J5P7JKV2AonQ | 0 | 3.5 | 19 |
+————————+———+——-+————–+

Answer is open has more reviews then the closed.

SELECT id
,review_count
,yelping_since
,useful
,funny
,cool
FROM user ;

————————+————–+———————+——–+——–+——–+
| id | review_count | yelping_since | useful | funny | cool |
+————————+————–+———————+——–+——–+——–+
| —1lKK3aKOuomHnwAkAow | 245 | 2007-06-04 00:00:00 | 67 | 22 | 9 |
| —94vtJ_5o_nikEs6hUjg | 2 | 2016-05-27 00:00:00 | 0 | 0 | 0 |
| —cu1hq55BP9DWVXXKHZg | 57 | 2009-04-18 00:00:00 | 34 | 14 | 0 |
| —fhiwiwBYrvqhpXgcWDQ | 8 | 2011-04-20 00:00:00 | 2 | 3 | 1 |
| —PLwSf5gKdIoVnyRHgBA | 2 | 2015-07-31 00:00:00 | 1 | 0 | 0 |
| —udAKDsn0yQXmzbWQNSw | 43 | 2014-07-12 00:00:00 | 1 | 0 | 0 |
| –0kuuLmuYBe3Rmu0Iycww | 26 | 2010-03-08 00:00:00 | 10 | 2 | 0 |
| –0RtXvcOIE4XbErYca6Rw | 2 | 2013-05-30 00:00:00 | 0 | 0 | 0 |
| –0sXNBv6IizZXuV-nl0Aw | 1 | 2013-01-09 00:00:00 | 0 | 0 | 0 |
| –0WZ5gklOfbUIodJuKfaQ | 7 | 2013-02-19 00:00:00 | 0 | 0 | 0 |
| –104qdWvE99vaoIsj9ZJQ | 3 | 2016-04-26 00:00:00 | 0 | 0 | 2 |
| –1av6NdbEbMiuBr7Aup9A | 9 | 2010-09-26 00:00:00 | 0 | 0 | 0 |
| –1mPJZdSY9KluaBYAGboQ | 5 | 2011-07-04 00:00:00 | 0 | 0 | 0 |
| –26jc8nCJBy4-7r3ZtmiQ | 2 | 2014-08-03 00:00:00 | 15 | 13 | 9 |
| –2bpE5vyR-2hAP7sZZ4lA | 23 | 2015-10-12 00:00:00 | 0 | 0 | 0 |
| –2HUmLkcNHZp0xw6AMBPg | 28 | 2016-07-28 00:00:00 | 7 | 1 | 0 |
| –2vR0DIsmQ6WfcSzKWigw | 1153 | 2012-11-27 00:00:00 | 122921 | 122419 | 122890 |
| –3B8LdT1NCD-bPkwS5-5g | 4 | 2016-11-10 00:00:00 | 0 | 0 | 0 |
| –3l8wysfp49Z2TLnyT0vg | 111 | 2013-12-14 00:00:00 | 97 | 57 | 32 |
| –3oMd6gjXpAzhjLBrsVCQ | 2 | 2010-03-22 00:00:00 | 1 | 0 | 0 |
| –3WaS23LcIXtxyFULJHTA | 213 | 2010-05-02 00:00:00 | 63 | 6 | 2 |
| –41c9Tl0C9OGewIR7Qyzg | 239 | 2011-07-03 00:00:00 | 64 | 15 | 3 |
| –44NNdtngXMzsxyN7ju6Q | 2 | 2013-01-22 00:00:00 | 0 | 0 | 0 |
| –4q8EyqThydQm-eKZpS-A | 400 | 2008-01-07 00:00:00 | 405 | 313 | 72 |
| –4rAAfZnEIAKJE80aIiYg | 25 | 2013-09-14 00:00:00 | 12 | 5 | 1 |
+————————+————–+———————+——–+——–+——–+
(Output limit exceeded, 25 of 10000 total rows shown)

-2vR0DIsmQ6WfcSzKWigw | 1153 | 2012-11-27 00:00:00 | 122921 | 122419 | 122890 | has many review count and many more

My new project Here I am using Postgres PG Admin 4

Download data from here

https://www.dropbox.com/sh/0wqw8fmiwrzr8ef/AABQijjQM522INXX1FCdamzma?dl=0

Then open Pgadmin4

Type this code

CREATE TABLE public.athlete_events

(ID integer,

Name varchar (200),

Sex varchar (100),

Age varchar (100),

Height varchar (10),

Weight varchar (100),

Team varchar (100),

NOC varchar (5),

Games varchar (100),

Year varchar (100),

Season varchar(100),

City varchar (100),

Sport varchar (100),

Event varchar (100),

Medal varchar (100));

Then Refresh Tables

Then type this code in query tool

COPY athlete_events
FROM ‘C:\Users\laxmi\Downloads\athlete_events.csv’
WITH (FORMAT CSV, HEADER);

you see this in messages

COPY 271116 Query returned successfully in 2 secs 158 msec.

Refresh again!

then type this code to check the table

SELECT *FROM athlete_events;

You should see this

–Minumum age of the participants–

SELECT Age FROM public.athlete_events

ORDER BY Age;

you should see this

–Minimum Age looks like it is 10 Years,

–minimum height–

SELECT Height FROM public.athlete_events

ORDER BY Height;

–minimum height is 127–

It looks like this

SELECT Year FROM public.athlete_events

ORDER BY Year;

This is how it looks

–Minimum year is 1896

First go to medal then to query tool

medal >Query tool then type this

DELETE FROM athlete_events

WHERE medal = ‘NA’;

you should see this

DELETE 231333 Query returned successfully in 1 secs 467 msec.

go back to athlete_events then to query tool like in this image

type this code

SELECT COUNT (*)Sex

FROM public.athlete_events

WHERE Sex= ‘M’

ORDER BY Sex;

you should see this

2853

type this code in query tool

SELECT COUNT (*)Sex

FROM public.athlete_events

WHERE Sex= ‘F’

ORDER BY Sex;

you should see this

11253

so Male = 2853 , Female = 11253

type this code

SELECT COUNT (*)medal

FROM public.athlete_events

WHERE medal= ‘Gold’;

you will see this

13372

so gold = 13372

type this code

SELECT COUNT (*)medal

FROM public.athlete_events

WHERE medal= ‘Silver’;

–So silver is 13116–

Type this code

SELECT COUNT (*)medal

FROM public.athlete_events

WHERE medal= ‘Bronze’;

— Bronze is 13295–

type this code

SELECT COUNT (*)medal,Sex

FROM public.athlete_events

GROUP BY Sex;

you will see this

Male 28530

Female 11253

Type this code

SELECT * FROM athlete_events
WHERE medal = (
SELECT MAX (medal)
FROM athlete_events);

You should see this

SO maximum medal is won by Kjetil Andr Aamodt!

SELECT COUNT (*) Name

FROM athlete_events

WHERE Name = (‘Kjetil Andr Aamodt’);

Kjetil won 8 medals

Type this code

SELECT *

FROM athlete_events

WHERE Name = (‘Kjetil Andr Aamodt’);

you should see this

DELETE FROM athlete_events
WHERE Age = ‘NA’;

you should see this

DELETE 732 Query returned successfully in 439 msec.

type this code to alter table

ALTER TABLE athlete_events

ALTER COLUMN Age TYPE INT

USING Age::INT;

you should see this

ALTER TABLE Query returned successfully in 679 msec.

–Changed data type of Age to Integer–

–After changing data type from varchar to integer then only we can calculate average–

SELECT ROUND (AVG(Age), 2) avg_Age
FROM athlete_events;

You should see this

25.93

_so the average age of the participants is 25.93–

ALTER TABLE athlete_events
ALTER COLUMN Height TYPE INT
USING Height::INT;

or you can you can use this code output is same

SELECT Year ::Numeric(10)
FROM athlete_events;

you should see this

ALTER TABLE Query returned successfully in 546 msec.

SELECT ROUND (AVG(Height), 2) avg_Height
FROM athlete_events;

–you should see this 177.56–

SELECT COUNT (*)NOC

FROM

athlete_events

WHERE NOC=’USA’

GROUP BY NOC;

__you should see this–

–4595–

SELECT COUNT (*)NOC

FROM
athlete_events
WHERE NOC=’CHN’

GROUP BY NOC;

–985–

SELECT COUNT (*)NOC

FROM
athlete_events
WHERE NOC=’RUS’

GROUP BY NOC;

–you should see this–

–1145–

my-sql-code-portfolio-a2 Download

https://youtu.be/KfaMwgQelTc

August 31, 2022

Palmer Penguins
coursera-google-data-analytics Download

My journey from a Teacher to Data analysis

https://www.credly.com/badges/17bb38b0-40d2-490f-adbf-eb82de46aadb/public_url

R Studio Programme

This week’s dataset involved deep diving into penguin habits to understand their body mass and other aspects:

The Palmer Penguins is a dataset constructed by Dr. Kirsten Gorman and relates to the structural size measurements of 3 species of penguins: adult, male and female Adelie, Chinstrap and Gentoo penguins. The data was collected at Palmer station Antarctica LTER between the period 2007-09. For each species, 4 structural size measurements were collected: bill length, bill depth, flipper length, and body mass. In total 344 samples were collected (however 2 samples have missing structural size measurements).

The data on the 3 different species of Penguins was collected from 3 islands in the Palmer archipelago in Antarctica. These islands are the Dream island, Torgerson island and Biscoe island

cite artwork @allison horst

https://public.tableau.com/app/profile/laxmi.hegde.nagaraj/viz/PalmerPenguin_16612261191200/Sheet2

My analysis of the following bar graph

Of all the Palmer penguins, Gentoos are the largest. The second is Chinstrap and the last is Adelie.

Adelie species of Torgersen Island penguin’s male and female have negligible differentiation in body mass, flipper length, culmen depth, and culmen length

let’s start coding

install r studio

install.packages(tidyverse)

library (tidyverse)

Install.packages(ggplot2)

library(ggplot2)

install.packages(palmerpenguins)

library(palmerpenguins)

head(penguins)

ggplot(data=penguins, aes(x=flipper_length_mm, y=body_mass_g))+geom_point()

ggplot(data=penguins, aes(x=flipper_length_mm, y=body_mass_g))+geom_point(color=”red”)

ggplot(data=penguins, aes(x=flipper_length_mm, y=body_mass_g))+geom_point(aes(color=species))

ggplot(data=penguins, aes(x=flipper_length_mm, y=body_mass_g))+geom_bar(aes(shape=species, color=species))

ggplot(data=penguins, aes(x=flipper_length_mm, y=body_mass_g))+geom_bar(aes(shape=species, color=species))

ggplot(data=penguins, aes(x=flipper_length_mm, y=body_mass_g))+geom_boxplot(aes( color=species))

ggplot(data=penguins, aes(x=flipper_length_mm, y=body_mass_g))+geom_col(aes( color=species))

str(penguins)

penguins %>%

group_by(species) %>%

summarize(across(where(is.numeric), mean, na.rm = TRUE))

penguins %>%

filter(island == “Torgersen”

penguins %>%

arrange(species)

penguins_subset <- penguins %>%

sample_n(12)

view(penguins_subset)

penguins_subset %>%

mutate(body_weight_pounds = body_mass_g / 453.59237)
```
penguins_subset %>%
      summarise(avg_body_mass = mean(body_mass_g))
```
googledataanalyticscertificate_badge20220825-46-6hmn5t Download

googledataanalyticscertificate_badge20220825-46-6hmn5t-5 Download
August 23, 2022
Houston Texas Real Estate Listings (2107 Fortuna Bella Drive Pearland, TX)

2107 Fortuna Bella Drive Pearland,

TX Single-Family

PRICE $ 450,000

This mansion in the sought-after Villa Verde neighborhood will leave you speechless. With almost 3,000 square feet of area, you won’t be short on options. Enjoy movie night in your own personal media room, complete with sound wall panels for a cinematic experience. Work from home in the spacious study adjacent to the dining room at the front of the house. The living area has HIGH ceilings and LARGE windows. Relax on the rear patio, which is shaded and has a ceiling fan. A great area for grilling or a cup of coffee in the morning. Finally, unwind in the main bedroom and en suite, which includes a walk-in master closet. This property includes a tandem garage for EXTRA storage. *Buyers should do their homework and double-check dimensions.

Link to this property website

2107 Fortuna Bella Drive, Pearland, TX, 77581 – Photos, Videos & More! (exprealty.com)

https://laxminagaraj.exprealty.com/details.phpmls=67&mlsid=54894979#rslt

#shorts

Information about Brokerage Services https://drive.google.com/file/d/1bfiF7Eg54zaUU7yPE77obCfT6Sh4ubcS/view?usp=sharing

Consumer protection notice

https://drive.google.com/file/d/1V8Er38R7a8gIfqtUymfq4ddhfLpqz85/view?usp=sharing

Text :Fortuna phone: 325-213-5484

June 6, 2022
Laxmi Hegde Teaching Portfolio
original-asu-tesol Download

ASU-TESOL Download

My teaching philosophy

When I was younger, an instructor motivated me to become a teacher. Over time, my desire to become a teacher grew stronger. I was able to comprehend new teaching strategies with the support of several outstanding professors… It was challenging, but not impossible, thanks to a lot of aid. In teaching, I learned a lot of new things. When educating our students with a feeling of purpose, we too receive a sense of relevance as teachers. We must believe our work is important and that we are making a difference in our community. Thankfully, teaching is an area where we CAN make a difference! Our desire to learn new techniques and teach has a positive impact on society. When asked why they became teachers, many people joke that it’s because we get summers off. An excellent teacher may make a difference in students’ lives and help them achieve their greatest potential.

My pedagogical perspective is that each child is unique and needs a challenging educational environment in which to develop physically, mentally emotionally, and socially. My goal is to create an environment where students can achieve their full potential. I will create a safe space for pupils to express themselves and take risks. Teaching is, in my opinion, one of the best ways to learn. I can understand what strategies work for students, where the content is tough, and how I may change my approach to best support their learning.

Students, in my opinion, are neither blank slates nor empty vessels waiting to be filled with information. I believe that engaged learners will remember information and be able to scaffold their learning with subsequent concepts because they can apply concepts to their own lived experiences. I try to avoid using didactic teaching approaches as much as possible; no one likes it when an instructor stands in front of the class and gives a lecture on a topic that has nothing to do with their learning or practice environment. When I offer course content, I try to connect learning objectives and outcomes to real-world examples that students may relate to or see in their profession. I frequently ask students to think about their practice or research questions to see whether this notion may be applied to their circumstances, enabling an opportunity for debate.

A good work/family balance is possible for a teacher. In an instructor’s career, there are many wonderful moments like “WOW moments.” A teacher’s primary goal is to assist students in their learning. My short-term goal as a teacher is to promote critical thinking capabilities among the students by utilizing the latest questioning strategies.

My long-term goal is to prepare my students to face real-world difficulties in the best possible way. The teachers’ goal is to create an environment in which students communicate (practice 80% of the time and the instructor communicates (instructs) 20% of the time in every lesson.

Teaching toolbox tip

Teacher Tip

Level: Intermediate to Advanced

Genuinely enjoyable icebreakers

It’s only a matter of time before you meet a fresh group of students! While you’re looking for ways to improve your instruction and help students succeed, you’re also starting to form relationships with them. Getting to know the children in your class and assisting them in becoming familiar with one another might be overwhelming at the start of the school year.

The purpose of this activity is to assist students to get to know one another in a new way than the traditional icebreaker game. It can encourage students to consider whether they, too fit cultural stereotypes, as well as give them an opportunity to explain how they are similar to and distinct from a group using language

Preparation and Materials: Place four placards in four corners of the room, titled
1. “only child boy or girl.”
2. “two brothers or two sisters.” and
3. “One brother one sister (Combination sibling).”
4. One brother two sisters or 2 sisters one brother or a family which has more than 3 siblings.
Procedure:
- As students enter the room, direct them to form the appropriate groups. (4 minutes)
- In small groups, students can describe what it was like to have a same-sex sibling.(for example sharing clothing) Tell them that they will present the advantages and disadvantages to the class. (20 minutes 35 minutes.
➢ Students from each group will report on the benefits and drawbacks of their
respective groups. Following the pupils’ prompts, the teacher will write on the
board a list. (duration: 20 minutes)

➢ Students should read the list made on the board (either one list or all) Change the
vocabulary to simpler phrases for kids who are less advanced.

➢ Following that, each person might discuss these benefits and drawbacks. An
excellent method to do this is to have each participant discuss on advantages and
disadvantages with which he or she agrees or disagrees. Request an example from
the student. You (and the kids) will learn a little bit more about each other as you
travel around the room doing this.

The majority of students will realize that only a few of the words are
appropriate for them. Fill up their lists on the board, agreeing on one side and
disagreeing on the other.

Benefits of same-gender sibling Drawback of same-gender sibling
Variations: The final step can also be utilized as a quick writing exercise in the form of a
paragraph. Allow less advanced pupils to pick from a list of terms (which can be
changed) and write one word that accurately represents them and one word that
erroneously characterizes them. This can be turned into a yes/no question (Are you
interested? Yes, I’m interested.)

Advantages of two sisters to parents(note I love to write only advantages;)
If you have siblings of the same gender, you will save money. Clothing and favorite toys
can simply be passed on to the younger sibling. Siblings of the same gender share many
hobbies, and if they were born at the same time, you can even pass down clothing that
the elder sibling outgrows. To ensure that you have these hand-me-down items, keep
anything from your firstborn child

PhTEACH YOURSELF TO SHARE
children of the same gender will most likely share toys, clothes, and a room. Children of
the same gender are more likely to share a room as they become older, so children will
be comfortable sharing as adults.

ACTIVITIES THAT MAY BE SIMILAR

In any case, most young siblings desire to do what their older sibling is doing. It may be
simpler for the parent to schedule siblings who are of the same gender. Putting gender
preconceptions aside, if both sons enjoy baseball, parents will have a better chance of
going to two games on one day of the week. If parents have a boy who plays baseball
and a girl who plays softball (two completely separate leagues), parents may run into
scheduling issues.

Photo by Polesie Toys on Pexels.com

BOND DEEPER
Siblings of the same gender often have a stronger affinity than siblings of opposing
genders. While this isn’t true for all siblings of the same gender, you’ll find that the
younger years provide a unique opportunity for friendship and bonding. As your same-gender siblings become older, they may develop a small distance during their adolescent
years before resuming their close relationship in early adulthood. Siblings of the same
gender may have more in common in terms of personal experiences.

MODESTY
Parents can be a little more lenient about modesty when their children are of the same
gender (to a certain extent). Putting your children of the same gender in the same bath
saves time, as does having them both change in the same room. It will also save you
time and effort if you are both going to the bathroom while out of the house. It’s usually
easier for one parent to bring both children into the same bathroom rather than taking
them to separate bathrooms

Teaching tip 2

What’s in the Bowl or Hat- Tell a story Activity!

This activity teaches fourth-graders how to tell a tale.
30 to 45 minutes
It is determined by the number of pupils and the amount of time allotted to
deliver a narrative.
Supplies required:
picture flashcards, an empty magician’s hat, or a bowl
Steps
1. The teacher will put picture flashcards in a hat or bowl and sit. This
  hat/bowl would be transferred from person to person.
2. Here are a couple of examples of picture flashcards
One student will pick a card and read it or view an image on it.
If the student picks a card, the student has to read what is written on the
card and execute that activity.

If a student chooses a photograph, he or she must develop a story using
that photograph.

If a student chooses a term, he or she must next construct a sentence using
that word.

If a student is unable to complete the exercises on the card, he or she must
write an essay on a topic chosen by the teacher.

After that, the hat will be passed around until all of the students have had
their turn.

Modifications
The teacher can alter these activities by changing the cards

My Grammer lesson plan :

grammer-lesson-plan-1-1 Download

My public speaking lesson plan

lesson-plan-2 Download

blank-lesson-plan Download

Click to access blank-lesson-plan.pdf

Listening and speaking lesson plan

listening-and-speaking-skill-part-i-4-a Download

listening and speaking skills. Lesson plan (continued)

listening-and-speaking-10a Download

The 7Habitss of highly effective people by Stephen R Covey (Lesson Plan)

lesson-plan-5-1 Download

The 7 Habits of Highly Effective People (Stephen R. Covey) ( continued)

lesson-plan-8-1 Download

Lesson Plan on Pronunciation

lesson-plan-6-1 Download

Autobiography and biography lesson plan

lesson-plan-7-1 Download

Argumentative and Persuasive essay lesson plan

argumentative-9 Download
May 16, 2022
Houston Texas Real Estate Listings (9307 Sandy Lane Manvel, TX)

NO FLOODING. This lovely 4 bedroom, 3.5 bath home in the Leedy Estates community stands on 1.27 acres! A wonderfully grown planted and recently painted exterior property with detached 3 car garage and carport is reached by a wrap around driveway. The living area features lofty ceilings and built-in solid wood cabinetry, as well as a wet bar station, making it ideal for entertaining. Flex room downstairs with french doors, ideal for a home office or fitness area. Granite countertops and freshly painted cabinetry in the remodeled kitchen lead immediately into the breakfast area and formal dining area, which is ideal for holiday celebrations. Downstairs, the primary bedroom has a MASSIVE bath space with a separate tub, walk-in shower, and separate vanities.

Take a stroll outside to the outdoor sanctuary, where you may cool down in your above-ground POOL or unwind in your HOT TUB after a hard day! Your next party will be wonderful on 1.27 acres of unfettered living. A/C, roof, and much more were all replaced lately.

Link to property website: check this out :

https://laxminagaraj.exprealty.com/details.php?mls=67&mlsid=28125716#

May 13, 2022
English Pronunciation

lesson-plan-template-w_-video-link-1-2 Download

Any English language student knows that spelling isn’t always useful in determining how to pronounce a word correctly.

The spelling of English words hasn’t changed much over time, but how we pronounce them has. Pronunciation becomes a serious issue for language learners as a result of this. However, good pronunciation is more than just ‘how words and letters sound.’ Other factors to consider include intonation (how the tone of voice changes during a sentence, going up or down), stress (which words and syllables have more ‘weight’ when we speak), and connected speech (how words sound different when they are joined together in natural speech). All of these characteristics help with pronunciation, but don’t confuse them with an accent.

There are many distinct sorts of accents in the United Kingdom, the United States, and other English-speaking countries, but people with those accents can all be deemed to have correct pronunciation. You don’t have to sound British or American when learning English. It is not necessary to sound like you were born in New York or London to have good pronunciation. Many native English speakers, in fact, like hearing English with a Spanish, Italian, or French accent! So, even if you keep your native accent, how can you improve your pronunciation so that people from all over the world can understand you easily?

https://www.slideshare.net/slideshow/embed_code/key/w4zO0pzk0Zmm1

Phonics English Pronunciation. from laxmi hegde

March 17, 2022

Author: Laxmi Hegde

Distribution Line

Distribution Line

Enjoyed this post?

Enjoyed this post?

2.

DATABASE SCHEMA

QUERY RESULTS

DATABASE SCHEMA

QUERY RESULTS

Yelp Dataset ER Diagram