SQL Portfolio Project - Part 1 | Analytics | Ashutosh Kumar
HTML-код
- Опубликовано: 21 май 2022
- The best technique of learning a skill is completing a project which is based on real life scenario and when it comes to analytics , SQL is the one of the most asked skillset in all the interviews, so friends in this video i have created an end to end project on data analysis using SQL, thi will be relevant to all those people who want to make a career into data analytics, business analytics ,data science , i n this video i have analysis on indian census data for 2011 . I have gathered data from the websites mentioned below or you can find the two data sets used into this video below-
Websites-
www.census2011.co.in/district...
www.census2011.co.in/literacy...
Datasets (either use the github or google drive link both are same, but github recommended)-
Dataset 1 -docs.google.com/spreadsheets/...
github.com/ashutoshkr103/SQL-...
Dataset 2 -
github.com/ashutoshkr103/SQL-...
Code -
github.com/ashutoshkr103/SQL-...
----------------------------------------------------------------------------------------------------------------------
Check out some more relevant content here
👉 SQL Portfolio Project Indian Census- Part 2
• SQL Portfolio Project ...
👉 SQL Portfolio Project - Shark Tank India
• SQL Portfolio Project ...
👉 How to Learn SQL
• How to learn sql for b...
👉 Complete playlist on Sql Interview questions and answers
• HackerRank SQL problem...
👉 Top free and best resources to learn analytics -
• Free and Best resource...
👉 How to become a data analyst complete roadmap-
• Data Analyst Complete ...
👉 Data analytics internships and job stipend-
• 70,000 + per month dat...
👉 Top 3 you tube channels to learn sql for free for beginners
• Video
👉 How to get data analytics internships-
• How to apply for data ...
👉Check out the roadmap to become a business analyst - • Business Analyst Compl...
👉All about analytics playlist-
• All about analytics
👉 Top 3 you tube channels to learn excel for free for beginners
• Top 3 you tube channel...
____________________________________________________________________
Fill the form below to subscribe yourself to the analytics jobs mailing list to receive regular job opening updates - docs.google.com/forms/d/e/1FA...
Why you should definitely fill the analytics job updates google form - • Job Openings into busi...
_______________________________________________________________________
Connect with me
📸Instagram - / ashutoszh
💻Linkedin- / ashutosh.analytics
_____________________________________________________________________
Comment down if you have any doubts
Please leave a LIKE 👍 and SUBSCRIBE ❤️ to my channel to receive more amazing content in data analytics and data science.
_____________________________________________________________________
🏷️ Tags
sql,
sql for analytics,
sql tutorials for beginners,
portfolio project sql,
sql portfolio project,
portfolio project on sql for resume,
sql interview portfolio project,
sql interview questions and answers,
sql for data analytics,
sql data science,
interview questions on sql,
sql hard questions,
sql problem solving,
ashutosh,
ashutosh kumar,
ashutosh kumar analytics
🏷️ HashTags
#sql #portfolio #project
👉 SQL Portfolio Project Indian Census - Part 2
ruclips.net/video/RY4npAVNSfc/видео.html
After watching this video, I just wanna thank the person who created the concept of dark theme.
Never find this kind of Sql session on RUclips.. Thumbs Up sir 👍👍
Recommended for Aspiring data Analysts . Great content 👍.
Great explanation Ashutosh. Thats' true nth Value doesnt work in SQL Server. Instead we could use Rank or Row_number in a sub query based on the requirement. Here for 5th value , we could write
Select * from
(Select sale, row_number() over(partition by sate order by date) 'rownm' from Value) val2
Where rownm = 5;
Thank you Ashutosh for this Portfolio Project series in SQL. Really appreciate the efforts you put into this. It's videos like this help people like us who are starting their Data Analytics journey to stay motivated and focused.
Thanks
thank You so Much Ashutosh .
this project really help in understanding the Concepts !
thanks a lot sir ..your videos help me a lot ...
very helpful for beginners to add projects in their portfolio and to know where to apply what concepts...
Great effort sir. Thank you very much please keep posting these types of useful videos
Thanks
Amazing video... Crystal clear explanation
Thanks a lot
great video brother. Thanks for doing this in English. Love from Sri Lanka
Thanks a lot
Hi Ashutosh, it is indeed a great video but all the time I was wondering how did you import so many web pages into a single excel sheet. It would be really helpful if you answer this query so that i can practice from other web pages as well.
HI @Ashutosh, I'm following your sql tutorials for months and enjoying the learning a lot.
I just have a question that while using aggregate functions like sum() or avg() I'm getting error like "Operand data type nvarchar is invalid for avg operator". Could you please guide me how to get rid of this error?? Thanks, and your efforts are much much appreciated.
Amazing lesson
superb bhai kmal krdia bht achay se smjhaya sb
Thanks a lot
Awesome Video Bro, Much appreciable Job
Thanks a lot
Thank you for the session.
Hii Ashutosh
Thanks for such an awesome video for understanding SQL Concepts
I have to ask 2 questions
1.Using count function,no of rows showed in Both dataset 1641,999 why it different from yours
2.So many null values are there how to clean them
Excellent vedio thanku Ashutosh 🙂
Thanks a lot
Kudos!! sir, I have made this project , and now I want to put it somewhere so that I can mention it in my resume. Where can I keep my project sir.?
im trying in mysql, the 1 data-set has been successful done, but in 2 data-set, its showing = table defination has changed, please retry transaction...
Sir, i have learnt sql using mysql. So, i have imported ur table. It works fine.
But i got issues while doing sum in population
In mysql i have wrote it as command is
select sum(population) from census_2011.dataset1;
But its showing another value somewhat close to 19k instead of some million value.
I couldnt understand where did i made mistake.
Could u please clarify sir
Hello Sir the data set 1, I am not able to import the data to tables. I converted the file to xls and imported the file it is showing success but not able to see the data in table
Thank you soo much Ashutosh , Much needed
Please do this SQL live project on power BI and Tableau dashboard as well.
❤
thanks Sure 👍
@@AshutoshKumaryt have you done it?? if yes link dedo bro... bhaut acha videos bnaya he
Hello, when i am creating a new table #topstates
state nvarchar(255) throwing an error
Using workbench for this project
Thankyou so much Sir, really grateful for these simple explanation😇
You are most welcome
@@AshutoshKumaryt waiting for new sql project sir
i mean real use case , the case u have shown was so easy .. i want something in medium level ?
Hi Ashutosh,
At 35:50
-bottom 3 state showing lowest sex ratio
the output I am getting is
State NULL
Dadra and Nagar Haveli 774
Daman and Diu 783
please let me know why am I getting NULL value with State in it
Sir what should we do if all excel version are failed to load
Thanks a lot for the video ! I have a question is there a way we can compare the two tables side by side so that we compare literacy and say population of two states
we can join both the tables, no ?
You can directly upload the excel file from the OS menu.
Here is how to do it:
Click the Windows OS Start button.
Navigate to Microsoft SQL Server 2017 (or whatever version you have)
Select SQL Server 2017 Export and Import Data (32-bit)
Or SQL Server 2017 Export and Import Data (64-bit)
Take care while selecting the destination if using this step.
This worked for me.
Thanks a lot
from where in Microsoft SQL Server we select SQL Server 2017 Export and Import Data (32-bit)
Or SQL Server 2017 Export and Import Data (64-bit)
Thank you Brother
Thanks a lot
Sir can I use this in Cv?
thank you for this!
Thanks a lot
Thanks for the video
Hey how did you download the data and converted into excel?
Thanks for your feedback, i just simply copy paste the data , but if you want to make it look more fancy you can try doing using web scrapping using beautiful soup or selenium
Good job 👌
thanks a lot
hlw sir i have a problem whn i want to import the dataset .....my systm did not allow me .....so what i do sir please help me
which topic added in resumee..
hey please show some projects of company use case?
I am using create table then load data infile in mysql workbench, In dataset2 column data types area and population both column used int datatypes, they have taken first values 16 and 27 after comma was not taken in both column. Then remove comma from both column then load it. Is any other solution to this problem?
sorry sir did not understand the question
I am unable to import data set file.. none of the option is working
i am unable to upload the data into SQL ?
Quick comment @Ashutosh. You had mentioned that while using Union operator, the number of rows should be equal in the 2 queries. Which is actually incorrect. For a Union operator, the number of columns being selected should match in the 2 queries being union'd. Please correct this in the video if possible.
Oh my bad , thanks for bringing this
External table is not in the expected format. (Microsoft JET Database Engine)
this error is displayed on uploading the excel file 1 pls resolve
Keep the file open in background and try importing once
Thanks
Thanks a lot
Hi, i think limit clause is not working in ssms, i wrote the same query but its not working, i found that only top function works in ssms?
Yes limit is not working for me too
I like PostgresSQL
Hey Sql server native client has been deprecated in recent versions. Which alternate destination shall I use?
Check this out once the comment box
ruclips.net/video/epYmIRdg_qo/видео.html
Hey Ashutosh, is there any telegram group that I can join, or where u would have joined because I wanted to join a community to get help or help some1, becuase I am trying to do this video data, but its throwing error
great idea i soon will create one an dspread through community post and future videos.
great video...
Thanks Aditya
@@AshutoshKumaryt also I would request…can u show how we can then visualise our sql query results in power bi or tableau
That would be a good project bcz…by visualisation u can showcase your findings in a much better way to the end user
@@adityasingh-yw9lj sure
good work sir. I have a small request sir, please increase the font of editor from next time to increase visibility.
Thanks
@@AshutoshKumaryt sir , I have made this project , now I want to mention it in my linkedIn for which I need to have a link for reference. How and where can I upload it sir to show as proof of my work done.
Hi Piyush,
When I'm importing data it's taking too time i don't know why so please let me know is there other way to import data quickly???
Hi Sarfaraz let me know the error
what does growth column signifies in dataset 1 ?? Is it population growth?
Yes
Hey ashutosh, Great video.
Although I just wanna let you know there are null values as #N/A in the state column of Dataset 2..
I'd like you to let me know on how to fill those null values
great observation brother , you are the first one who noticed this , while for this one you can remove these rows or insert the correct data manually
you can use having value>0
Why am getting additional columns like F6 F7 F8 with null values after importing data to sql sever anyone faced such issue. Though i deleted those additional columns but why its hapening please answer.
just save the data in a new sheet and import the new sheet
Thank you so much, can you share the project description for the resume.
Data Exploration on the Indian census data
Sir, at the timestamp 18:20, where the query to find the data of Jharkhand & Bihar was written, Why cant we use AND?? Is this query [ SELECT * FROM WHERE State = 'Bihar' AND State = 'Jharkhand' ] wrong??
Hey,
If you use and both command should be true in single row but it isn't a case here
So use or instead
Select * from tablename where state = "bihar" or state = "goa"
For more read on bitwise operator
OR
Use
Select * from tablename where state in ("bihar", "goa")
i am getting error external table is not in the expected format while importing data from excel file
Me too
HI, Ashutosh thanks for the Project, I would like to request you to share the excel files which helps to practice simultaneously.
The links of the files are in the description box of the video
I use Mac, and I have no subs. of Microsoft Excel. can't w directly download data? please advise for Mac users, many people use mAc over windows
you mean mac does not support excel ?
@@AshutoshKumaryt it does support excel , but need to subscribe to use it, as it's iOS no windows functions will work until Subscription is there. one can use web version of excel but can't save the files.
I'm not getting sum(population) proper count please help me in this 🙏
plz,write exactly how it is written you will get the answer
I am. Using mysql and not sql server.. So do I have to learn sql server first?
Nope just go along
Server name kaise de....... Not accepting any names ... Not even localhost
hii i have been trying to import that data..but its showing error..what should i do to resolve this error?
you might've figured it out by now, but if you haven't then keep the excel sheet open in the background and then try it. even Ashutosh has suggested the same and it worked for me.
@@skm7887 Hey thanks will try it out..and let you know 🙂
@@Mrunali08 sure:)
Sir can i do this in MySQL? Is codes similiar?
Yes sure
select sum(Population) from censusii; ans=13175 Geeting wrong output from in mysql
Just check if u have imported all the rows from the dataset
Same ..I m also getting wrong output in mysql..I have checked ..all 640 rows are imported
hi Ashutosh i have downloaded 1st dataset but 2nd dataset unable to download please provide link to download data
Open the GitHub link click on the three dots you will see a download button
Bhai please address the queries , you have taught to import data but most of us are unable to import it, even after we kept it open in the background. How come we learn if you remain so negligent and ignore us??
Hi Shivam i get what you are saying the thing is importing excel sheet data in ssms is very system and os specific so it is very difficult for me to know exactly what issue someone is facing while importing data because there is a specific process to import data so if that is not happening either issue is with system or compatibility issues of ssms software ,even i faced this so googling the error helped me to fix , if this was a code error issue i reply to every comment because in that i exactly know what the issue is
@@AshutoshKumaryt hey!
Even after trying multiple times in multiple way, it's throwing the error: the ms:ace:oledb.16.0 provider is not registered on the local machine.
Does this mean to register any oledb?
HELP
can i use mysql instead of ms sql
Yes
@@AshutoshKumaryt thank you
how to download it from the website theres no download option?
Attached files in description box
Hi Ashutosh,
Can't we join queries with union operator without making temporary tables? I have applied this logic and it gave me the same answer.
****1st Method: Using Temp Tables:
select * from #bottomstates
select * from
(select top 3 state, topstate from #topstates order by topstate desc)a
union
select * from
(select top 3 state, bottomstate from #bottomstates order by bottomstate asc)b
***2nd Method: Normally:
select * from
(select Top 3 state,round(avg(literacy),0)av from Census..Data1
group by state
order by av desc)a
union
select * from
(
select Top 3 state,round(avg(literacy),0)av from Census..Data1
group by state
order by av)b
Output:
Arunachal Pradesh 64
Bihar 62
Goa 89
Jammu and Kashmir 65
Kerala 94
Lakshadweep 92
Union can be used to join tables vertically
please make a video on how to upload Excel in Postgree sql in detail im facing a diificulty in area_km2 and population columns data type, accepting it only in VARCHAR but then can not performing sum operation
now i got it i did correction in Excel file changed number format that worked.
great!
@@durgeshrathod236 how did you do it brother?i am also getting the same problem.
@@AshutoshKumaryt sir please help me with the same doubt.
data type of some columns is causing the problem.
I am using mysql workbench.
@@abhishekkrishna400 hey bro just change the data type of columns before you are importing the sheet in MySQL it works absolutely fine after that
i am not getting those import export options sir
This shouldn't happen
Thanku so much sir ❤️❤️
Would this be a nice project to add in resume for fresher
Also sir if u can tell some good site from where we can practice SQL for interview written exams
Yes you can also one thing is pls explore from your end too what more you can do coz it's very difficult to cover everything in 1 hour video
5:21
your dataset1 is not available now pls help it out
github.com/ashutoshkr103/SQL-portfolio-project-files-and-codes/blob/main/Dataset1.xlsx
bhai can I do it in MySQL, because I already have it installed
You can but you might face some exceptions
Bhai how would I be able to add this to my resume once I complete the project
Lol
@@finalboss8624 what’s funny
@@wordlife_97 you are asking how would you add a project in resume simple you add the project name
make a account on github.com and add project details
33:59 why we didn't use limit here to find top 3 states.
here when applying the limit it is showing an error in the video also.
limit does not support in ssms instead use top
Am I the only one who notice that dataset 2 has 450 rows and in video it shows 640 for both datasets
Use this Akash and let me know if this helps
github.com/ashutoshkr103/SQL-portfolio-project-files-and-codes/blob/main/Dataset2.xlsx
I'm not able to import dataset2 , it's showing error. I have checked all the excel lower version , what should I do then ?
Please keep the file open in the background once and then try saurabh
I am getting 1000 rows instead of 640 rows.What should I do next???
Use this vinayak and let me know if this helps I guess you are talking about dataset 2
github.com/ashutoshkr103/SQL-portfolio-project-files-and-codes/blob/main/Dataset2.xlsx
Can we get playlist for project
That is what is the plan
hi Ashutosh, your second datset is asking for approval on mail
sorry , now its public
can i learn all imp sql queries?
yes
Koi bhi version select krne se ni ho rha hai bhai..kya krna chahiae?
error kya aara hai?
@@AshutoshKumaryt The same error that was shown in the video but usko theek krne k liae ek solution use kra Maine or vo hai ki apko apni excel file to save as krna hoga or type wale section pr excel-97 wala type choose krna hoga. Aise hi mai import kr paya data.
importing excel file doesn't work in any format
Facing the same problem, please let me know if u find any solution to it.
keep the file open and try once
FYI limit cannot be used in mssql
Thx that's why we used top function
can we do it in MYSQL
Yes
@@AshutoshKumaryt what changes we need to do in MYSQL
unable to import data set 2
Keep the file open in the background and then try importing once
@@AshutoshKumaryt tried
importing in mac giving message unable to convert string into small int
failed to convert parameter value from a string to a int32. ---> system.formatexception: input string was not in a correct format. while importing
None of the Excel versions from the drop down list are working.
Help!
keep file open nd then try once
Why is it so difficult to import csv files from Excel to Mysql workbench. Can't import all the rows. PLEASE HELP!!!!!!!!!!!!!!!!!!!!!!!!!
Please convert CSV to xlsx and check if it works
@@AshutoshKumaryt Not working . Mysql workbench requires only CSV files.
i am not able to import excel file
Keep the file open in background and try once
@@AshutoshKumaryt I have done so many times still unable to import
idk why buy my query is not working ;
Select state, avg(sex_ratio) as Sex_ratio From ravi1
group by state;
whats the error u r getting ?
@@AshutoshKumaryt thankyou for replying , but i googled this and found that in my sql we should not have any column name with space , if there is thene use back tick while using the column name - `sex ratio` , like this it worked
U teach reall well ,maza aa gya , please if possible do something like this with python
@@RSVERMA948 great
pls try to upload good quality video content is good
thx
sir, what is the aim of project?
i need want to know for my interview sir
Data Exploration on the indian census data
In your dataset i have got 1000+ rows but ?
sorry did not understand
Brother first of all tell to change the data type of the columns 😅..I somehow figured out after half an hour
Are you also using ssms ?
@@AshutoshKumaryt no my sql
How can i explain this project in resume
Pls reply this question
Yes... please tell us...what is the name given to that project on resume
this can be considered as a data exploration project using sql and u can note down ur findings , let me know if you need more help
answered
answred
This code throwing me error in workbench
What's the error?