Further into SQL(part-1)
Welcome back, in this story we’ll get further into the SQL,
If you a new to my story you can refer to my previous story for the basics of SQL(https://medium.com/analytics-vidhya/introduction-to-structured-query-language-sql-67e3190165e9)
Now, We’ll get into Importing & Exporting data into MySQL, and Data Analysing like formulating, sorting, slicing, and Filtering the data to our Requirement
Importing CSV Files into MySQL:
Why do we need to import “.csv” files, In real-time Problems/Cases there will be Millions of record which needs to be inserted into the table, it is very difficult to manually type those data into the database so we can use “.csv” files, But the File must consist of same attributes as mention in the table and the same order.
The Column Name must be the same as mentioned in the table, also the data type & size of the data. (if not there will be errors)
Code to Import “.csv” files:
“load data local infile “C:/(File Location as in your System)/team2020.csv” into table players columns terminated by “,” optionally enclosed by “‘“ ignore 1 lines;
(Use “ignore 1 lines” only if you have the name of the column in the .csv file as shown above figure )
To check if all the data is imported correctly (just to Eyeball) use > “select * from team2020”
Export Database or Table Using MySQLdump
Export Table
To Use MySQLdump you need to get out of my MySQL then use the below code: mysqldump -uroot -p cskteam team2020 > C:/(File_Location_of_Backup)/table_backup_Team2020.sql
Export Database
To Use MySQLdump you need to get out of my MySQL then use the below code: mysqldump -uroot -p cskteam > C:/(File_Location_of_Backup)/Database_backup_Cskteam.sql
Data Analysis:
Let’s start with simple Queries:
✒️How to find the Number of Records in Table.
Query Used: select count(*) as No_of_Records from team2020;
✒️How to Find the Unique Values in the Column:
Query Used:select distinct(Avgscore)from team2020;
✒️How to Find the No of Unique Values in the Column:
Query Used: 1 : “ select count(distinct Avgscore) from team2020;” , 2 : “select count(distinct Avgscore)from team2020;”.
✒️How to Find Total, Mean, and Standard Deviation of a Column
Query Used: 1- “ select sum(Avgscore) as Total from team2020;”, 2- “select avg(Avgscore) as Mean from team2020;”, 3- “select stddev(Avgscore) as Standard_Deviation from team2020;”
✒️How to Fetch a particular data from with some Conditions from the Below Table:
To find
1. The Players with Avgscore greater than or equal to 46 (Mean),
2. Player Max and Min AvgScore,
3. Sorting Data in Ascending Order and Descending order for Avgscore,
4. Finding No of Players with Respect to Country,
5. How to get players whose name starts with S, Name which Contains R, Name Ending with I.
6. How to get 2nd and 3rd highest player wrt to avgscore
Query Used:
1. “select * from team2020 where Avgscore>=46;”
2. “Select Max(Avgscore),Min(Avgscore) from team2020;”
3. (a) Ascending Order :“ Select * from team2020 order by Avgscore ; ” (b)Descending order: “ Select * from team2020 order by Avgscore desc; ”
4. “ Select Country,count(*) from team2020 Group by country; ”
5. (a) Name Starting with S:“ Select * from team2020 where name like “S%”; ” (b) Name which contains R: “ Select * from team2020 where name like “%R%”; ” (c) Name Ending with I: “ Select * from team2020 where name like “%I”; ”
6. “ Select Name, AvgScore from team2020 order by Avgscore desc limit 2 offset 1; ” (or)“ Select Name, AvgScore from team2020 order by 2 desc limit 2 offset 1; ”
(Note: 2 is used in order by but got the same result its because in select each column will be numbered like 1,2,3 that can be used in Order by or Group by clause)
The Queries which I have used are simple, We can use the Combination of a group by clause, order by, Limit and Offset clause all together
Question: To Find 2nd and 3rd Country with no of Players in the List.
Query: “select count(Country),Country from team2020 group by 2 order by 1 desc limit 2 offset 1;”
Well, you made it till the end of this story, My Advice is to try the Above Queries try to play with Clauses, and also by changing the numbers, As big the table is the Better Difference you see, Stay tuned for more upcoming with more exciting Queries in Further into SQL(part-2).
~Vasu Devan S
The Queries which i have used are simple
