🚀 BI CAREER STARTER PROGRAM 🚀 A fully mentored certification program taking you from ZERO TO BI ANALYST IN 12 WEEKS. Your new career starts here 👉🏻 geni.us/bicsp
I love the fact that you gave out the problem statement first rather than raw coding. This allows your audience to conceptualize the problem for which we’re creating the solution! You’ve just gained a subscriber!
I've got to say, I was a bit disappointed that I couldn't follow the tutorial all the way through to the end. I saw all of the positive comments and got excited, and even bought the data to follow along, which I did until the Looker part. At about 35:00, there is a huge jump, where the instructor explains that you should connect your database to an instance of MySQL in Looker. However, there is no explanation on how to do it. I tried setting it up by myself but was so lost. I was quite disappointed that I couldn't follow the tutorial from that point onwards. I think this was a good tutorial until that part, but IT IS NOT a Follow-Along type of tutorial, so keep that in mind!
I build dbms for a months and then i saw your videos making it in hour 😭 what a life saver .... I've been interested to study data analytics in order to help in my studies And i came up to your channel 🥰 thanks a lot hope you make more videoes about it 🥰
Hey quick question, on 33:48 how can I find the instance connection name? I understand he said in the google cloud back office but I need a little more guidance.
Even just passively watching this video and making notes, without directly following along, has been really helpful. In future I will create my own projects using a similar structure. Much appreciated, thank you!
This video is phenomenal. I noticed that most other tutorials on youtube don't really touch on the process of cutting and stitching data together so I really appreciate you going over it in such simple manner.
What you are doing is amazing! It is really rare to see an end to end real world project like that. I think this project really fit for someone to look a job in small start up, or becoming an BI analyst. Also, if we replace this excel data source with data from API (using Python to extract and load to DB), it would be exactly BI system in my start up company now.
Hello, thank you for the video, it is quiet helpful. For the calculation of shift duration i suggest this way : SELECT start_time, end_time, ROUND(TIME_TO_SEC(TIMEDIFF(end_time, start_time)) / 3600, 1) AS shift_duration_hours FROM shift; It is very simple, hope it helps !
I cannot thank you enough for this tutorial!!!!! You went from beginning to end in a very methodical, easy to understand manner!!! There are so many tips and tricks in this video that I will have to watch it a few more times to catch everything!! I particularly enjoyed the DB design and visualization portions. I just learned SQL, so the queries and subqueries were easy for me to understand. This is an excellent learning tool for any data analyst and at any learning level!!!
Hi, i started learning data analysis for 2 days and i found this video, it introduce me to website that will be helpful in the future, thank you very much for the knowledge
Awesome SQL project, crystal clear and consice. This is precisely what I have long been looking for, the essential SQL skills relevant to data analytic workflow.
Great video from start to end. Very useful information and great use of complimentary visuals. I appreciate your ability to effectively teach a complicated topic so well! Thank you.
The fact that this video is almost a year ago and you're still replying to every comment is incredible man! great effort and explanation too. cheers from Egypt❤
Mate!... I'm back to my business analyst career after a year+ break . Got myself into the Google certificate in business analytics to brush up knowledge, today searching for jobs I saw heaps BI jobs, then I found you clearing out the path in how to approach those jobs and how create portfolios. Thanks a lot 🙏 Pst: do you recommend to get certified in powerbi? More than 50% of BI jobs I checked here in Sydney were related with powerbi. Thanks in advance and keep up your amazing work. Legend!
Building a dashboard using Power BI desktop and using Power BI Service to build and share the desktop. These are skills you will learn during the certification course.
That's a great project covering every aspect of data analysis and visualisation. Very good work mate... This video deserves more likes and views. Keep going with other contents as well
Hi Adam great video, I'm learn a lot about designing relational database. Maybe you can add the explanation about the relationship type, is it one to many, many to many, or many to one. Also for your question about the most efficient query at 32.00 here what I find: SELECT start_time, end_time, TIME_TO_SEC(TIMEDIFF(end_time, start_time)) / 3600 AS hours_difference
Hi Adam, absolutely loved the video! I have a couple of question about what you did while designing the DB. 1. While choosing datatypes for date/created_date why did you choose datetime and not timestamp? 2. Does the direction of the linkage matter when you linked staff_id in rota to staff_id in the staff table? @11:57 I am just getting started so please need your help. TIA! You might see more comments from me as I progress through the video : )
Glad you enjoyed the video. TBH, the answers to your questions can be found using a simple Google search. It's not that I don't want to be helpful, it's just that the articles/posts you find from there will probably explain it better than I would in the comment of a UA-cam video 👍🏻
Lovely Project and description, I hope you see this and know that you are amazing, and if you don't mind I'll definitely recreate this and add as part of my portfolio. Amazing Project and scenario. Thank You once again.
Excellent video for those who are starting out in SQL and to learn about the tools that are being used today. But I would like to know what tool is used when creating the relational model. I found the ease with which the SQL lines are written and at the same time the table is displayed quite interesting.
Hi I'm stuck on 33:48. Not sure where to access google cloud back office after creating an account. How would I access it? Am I uploading the previous work onto google cloud? Am I using an instance created by you? Thanks!
@@LearnBI where do I navigate to after clicking console? Also is there an explanation on how to create our own instance? I purchased your files, am I supposed to run the dump file somewhere? Sorry, I just feel a little lost after completing section 2.
I think the best thing for you to do would be to install MySQL onto your computer (find a tutorial on UA-cam) and create an instance that way. Then you could use Navicat or MySQL Workbench to run the sql dump file.
First diagram for the Orders customers items and address - I believe the relationships are flipped. wouldn't it make more sense that the customer table is in a one-to-many relationship to the Orders Table and not vis-versa. because the repetition (the many) happens in the Orders table not the customers table.
Hi Adam! Thank you lots for this video! Many people recommended your channel on Tiktok, and I you're truly amazing teacher! I'm wondering, is there an excel file with all data tables together? I cannot find it (for example 10:06)
Hi, Adam Finer I appreciate the amazing work you have done in creating this insightful video. It will be helpful if u give raw data set of these(not a cleaned dataset)
Thank you so much for this video, it is really great ! I was wondering why do I need to do this SQL queries before taking the data to the data visualization tool if we can do this connections and calculations directly there? When will I have to do this and when not?
It will depend on the sql functionalities included with the BI tool you’re using. Most allow you to use custom queries to create data sources but you’ll need to write them before hand is they are complex and BI tools aren’t really made for that. Also, the sql queries I wrote in the project are to create data sources in the bi tool, not to get the data for individual visualizations (which might be actually what you’re referring to).
In some cases - where the BI tool has data modelling capabilities and there are just simple joins to be made - you can simply connect your database to your BI tool. In this case the SQL queries are more complex (e.g. contain sub-queries) and cannot be done in the BI tool.
Hello Adam, I appreciate the amazing work you have done in creating this insightful video. Might you be able to point me towards a basic SQL course. I am new in all the IT stuff and currently doing a beginner Data analytics course. I need to understand the SQL formulas and how to create them. Thank you for fantastic work.
Thanks for your feedback! There's a 3-part series on my channel that teaches basic SQL: ua-cam.com/play/PLR0triVyTrBWOLNu3ato7Y9hnGVyhTe1c.html I'd recommend you start there. Then you could take a look at the courses offered by LearnSQL: geni.us/learnsql
I just reached 31:55, where we need to calculate the total hours per shift, and this method worked: "SELECT r.date, s.first_name, s.last_name, s.hourly_rate, sh.start_time, sh.end_time, TIMESTAMPDIFF(HOUR, sh.start_time, sh.end_time) AS TimeDifferenceInHours, s.hourly_rate * TIMESTAMPDIFF(HOUR, sh.start_time, sh.end_time) AS TotalHourlyRate FROM rota r LEFT JOIN staff s ON r.staff_id = s.staff_id LEFT JOIN shift sh ON r.shift_id = sh.shift_id;"
Hi Adam and thank you for making this video. I have a question and I scrolled down and didn't see anyone asking. At 10 minutes and 44 seconds, the diagram shows the inventory table primary key is not being used on recipe table (as there is no item ID in it). Should that have been connected to the Item table instead? And bypassed recipe table altogether. Or should recipe table have an Inventory ID? Let us know it you get a chance.
Hi Adam, thanks for the video. I am completely stuck at the 'Database Authentication' part of the video at 33:50. I think I have successfully created an instance, but whenever I fill in the next 3 boxes I just get an error message. I've tried everything, asked for help from colleagues and googled with no luck. Can you advise please?
Hi, it's really difficult for me to help with so little information. All of the connection information you'll need can be found in Google Cloud Platform. It's possible that you'll need to whitelist your IP address in Connections>>NETWORKING in your instance.
@Adam Finer Loved your explanation of the project-there’s so much to learn! I just wanted to ask, where can we get the data from? Also, how do we download the database?
Thank you for this video!!! you gave us a good amount of content. I have a doubt about the data tables, from where can we get all the data that you've shown in the Excel spreadsheet? Could you please provide them?
When I try to add tables in Navicat, only 2 talbes are added to the database. I wonder where I'm going wrong. Love your videos and tutorials btw, very grateful for your channel!
You’re quite right. The orders data isn’t random because it was generated by people placing “real” orders. But the stock data was generated by myself and didn’t allow for the fact that products could sell out. It could easily be fixed by just increasing the amount of inventory in the appropriate table. Glad you like the video!
Hi Adam! Thank you so much for the video! I would like to clarify why we can join the inventory and s2 on different keys (item_id and ing_id)? I thought we could only join tables with the same key.
Yes, this is my project that I built from scratch. For the data I built a fake pizza ordering page so that people could create fake orders to generate the data.
@@LearnBI will we have one with tableau? Because tableau and power bi used in most companies, im in internship and the company use tableau. Thanks for answering 🖤
@LearnBI Hi Adam, Thanks a lot for the step-by-step guide videos they've been really helpful. However I'm really new data analytics so I'm still trying to figure out a lot of things. I'm trying to follow up with you in this tutorial step by step but I don't even know where to begin, are you using looker studio to work on this project and also how do I get the data you working with? Thanks
Hi Adam, thanks for the video. I have a question regarding the relationships you have established for the tables in QuickDBD. Looking at the diagram, it suggests that the cust_id in the orders table and the cust_id in the customers table have a one-to-many relationship, while I think it should be the other way around? Since one customer can appear several times in the orders table but should unique in the customers table. Same for the item_id field in orders and in items.
@@LearnBI Hi Adam, I have finished watching. I could think if two explanations, but not sure which is correct: 1. Have noticed at the end of part 1, there are some error messages regarding foreign key. This probably means the tables were created in Navicat with no relationships established and we would not need them for the purpose of this exercise? 2. In part 2, you used Left outer join to join the tables orders and item on the key item_id (one-to-many based on the QuickDBD diagram). I guess this could be the reason? Thank you for your reply. Your videos have helped me greatly getting into SQL.
You’re very welcome, I’m glad you find them helpful. I’m not a sql expert, I just use what I know to achieve what I need to. In this project we were able to do what we set out to by creating, populating and querying the tables as we set them up. The cardinality type didn’t really come into play and “joining” the tables when I set them up in dbd was simply to demonstrate the relational model and how tables related to one another. I’m sure most sql professionals would probably cringe when they watch this but that’s fine by me. If it works, it works 😎
Hi I downloaded your paid files, and I was able to work in mySQL workbench, however I am using a local host. What is the easiest way to connect my data to the Looker studio? I thought about creating a Google Cloud mysql instance, but will I be charged??
Because Looker Studio is a cloud-based BI tool, it cannot connect to localhost. My suggestion would be to work on the SQL in Workbench, write the queries you need then export to CSV. Then you can use the file upload connector in Looker to build your reports. Creating a Google Cloud MySQL instance is a paid solution, you'd be looking at around $7-$10 a month.
Hey Adam! Love the video and all that you've shown us in it. Although, I created an instance on the GCP and connected my Navicat to my instance but, when I create a database in Navicat, it doesn't show up on my GCP instance. I'm not sure if there is a permissions issue or what may be wrong. Was hoping you could help me with this issue?
Hi i'm a novice but I couldn't get the Quick DBD model to work on SSMS, I noticed that some of the one-to-many relations are drawn the wrong way round on the diagram. Once fixed it works fine :)
Been a while since these comments have been posted. But the alternative I found was creating these tables in SSMS using Database Diagrams. This provides you with a similar visual way of creating the tables. You can also create these tables, export them to MYSQL and then give the code provided to chat GPT and ask it to make it compatible to SSMS.
Hello Adam, great video! I am learning a lot about relational databases from your tutorial. I have a question about the relationship between `Ingredient.ing_weight` and `recipe.quantity`. As I understand it, `recipe.quantity` represents the weight of each ingredient in each item, and `Ingredient.ing_weight` represents unit weight as ingredient price. The way I calculate the cost of each ingredient of each item is as follows: (recipe.quantity/Ingredient_weight) * Ing_Price * SUM(orders.quantity). Am I understand it correctly?
Nice video. Hello guys, please I need help with the SQL import and export wizard. I've been trying to set it up to SQL server native client 11.0 but I haven't even seen it as an option in the destination folder. I'm just confused. Thanks.
Hello Adam I am following the project and got to the point of buying/downloading the dataset which I did. It turns out that the sql script file did not run successfully. There's an error in adding a foriegn key to the ingredients table and after 2 hours I haven't been able to fix it because you used a different complex style to add the foreign keys. Please can you rectify this error and send me a link to access the corrected version.
Hello Zulu, thanks for your support. You can just ignore those errors. They don’t impact your capacity to work with the database, you will find that all the tables have been created with data.
They should work, they’re simple statements that work for everyone else so there must be a problem with the way running the sql. Where and how are you executing the file please?
Hello Adam, can you explain the fix for the orders by address, what I understood was that you added "united states" to the end of the full address for each order therefore putting them all in the same country, but why does the geo map still show a map of the UK?
![SQL Course for Beginners [Full Course]](http://i.ytimg.com/vi/7S_tz1z_5bA/mqdefault.jpg)
![SQL Course for Beginners [Full Course]](/img/tr.png)
🚀 BI CAREER STARTER PROGRAM 🚀
A fully mentored certification program taking you from ZERO TO BI ANALYST IN 12 WEEKS.
Your new career starts here 👉🏻 geni.us/bicsp
can you attach datset excel ?
I've never seen a project explained like this. This is what I was looking for in my entire career. Thank U soo much.
Good day can we connect.
I just finished learning SQL basics to advanced and looking up to find a mate seeing your comment is just about 23h ago
You're very welcome!
I love the fact that you gave out the problem statement first rather than raw coding.
This allows your audience to conceptualize the problem for which we’re creating the solution!
You’ve just gained a subscriber!
Thanks! Glad you enjoyed it
I've got to say, I was a bit disappointed that I couldn't follow the tutorial all the way through to the end. I saw all of the positive comments and got excited, and even bought the data to follow along, which I did until the Looker part. At about 35:00, there is a huge jump, where the instructor explains that you should connect your database to an instance of MySQL in Looker. However, there is no explanation on how to do it. I tried setting it up by myself but was so lost. I was quite disappointed that I couldn't follow the tutorial from that point onwards. I think this was a good tutorial until that part, but IT IS NOT a Follow-Along type of tutorial, so keep that in mind!
having the same issue, were you able to figure out how to migrate from navicat to lookerstudio??
Can you send excel dataset,?
I build dbms for a months and then i saw your videos making it in hour 😭 what a life saver .... I've been interested to study data analytics in order to help in my studies And i came up to your channel 🥰 thanks a lot hope you make more videoes about it 🥰
Glad I could help!
Hey quick question, on 33:48 how can I find the instance connection name? I understand he said in the google cloud back office but I need a little more guidance.
Even just passively watching this video and making notes, without directly following along, has been really helpful. In future I will create my own projects using a similar structure. Much appreciated, thank you!
This video is phenomenal. I noticed that most other tutorials on youtube don't really touch on the process of cutting and stitching data together so I really appreciate you going over it in such simple manner.
My pleasure. Glad you enjoyed it.
Adam deserves millions of subscribers and views he is a real educator/entertainer, you deserve it alla mate
Thank you!
What you are doing is amazing! It is really rare to see an end to end real world project like that. I think this project really fit for someone to look a job in small start up, or becoming an BI analyst. Also, if we replace this excel data source with data from API (using Python to extract and load to DB), it would be exactly BI system in my start up company now.
Really glad you like it! I try and produce original real world content to help people achieve their analytics goals.
That is really cool. Thumbs-up
Hello, thank you for the video, it is quiet helpful. For the calculation of shift duration i suggest this way : SELECT
start_time,
end_time,
ROUND(TIME_TO_SEC(TIMEDIFF(end_time, start_time)) / 3600, 1) AS shift_duration_hours
FROM
shift;
It is very simple, hope it helps !
I cannot thank you enough for this tutorial!!!!! You went from beginning to end in a very methodical, easy to understand manner!!! There are so many tips and tricks in this video that I will have to watch it a few more times to catch everything!! I particularly enjoyed the DB design and visualization portions. I just learned SQL, so the queries and subqueries were easy for me to understand. This is an excellent learning tool for any data analyst and at any learning level!!!
You're very welcome!
Hi, i started learning data analysis for 2 days and i found this video, it introduce me to website that will be helpful in the future, thank you very much for the knowledge
My pleasure!
Sir this is very useful for my journey of SQL learning
Thank you, Adam. Hope to see more of you step-by-step tutorial project.
Awesome SQL project, crystal clear and consice. This is precisely what I have long been looking for, the essential SQL skills relevant to data analytic workflow.
Men i never seen a project like this you are really love this domain , you are goat . keep going we love You
Thanks a lot!
Much appreciated, I am not a specialist but this video answered most of the questions I had😊
very clear and understandable english for not native people like me , ty god
Great video from start to end. Very useful information and great use of complimentary visuals. I appreciate your ability to effectively teach a complicated topic so well! Thank you.
The fact that this video is almost a year ago and you're still replying to every comment is incredible man! great effort and explanation too. cheers from Egypt❤
I appreciate that!
ممكن لو معاك الملفات تبعتهالي وشكر ا مقدما
@bodymohmed6493 yes, as it says in all caps in the description, this is a paid download. Thanks for your support!
Your video is so useful for me, I believe it will be useful for many other people in many many years!!! Thank you so much!!!
Glad it was helpful!
Mate!... I'm back to my business analyst career after a year+ break . Got myself into the Google certificate in business analytics to brush up knowledge, today searching for jobs I saw heaps BI jobs, then I found you clearing out the path in how to approach those jobs and how create portfolios. Thanks a lot 🙏
Pst: do you recommend to get certified in powerbi? More than 50% of BI jobs I checked here in Sydney were related with powerbi.
Thanks in advance and keep up your amazing work. Legend!
Welcome back! I reckon a portfolio containing well built projects made in Power BI would be more impressive than a certification.
This is gold advice!
Building a dashboard using Power BI desktop and using Power BI Service to build and share the desktop. These are skills you will learn during the certification course.
Pretty good video!
Thank you.
Only “pretty” good? 🤣 You're welcome!
Thank you so much for this video Adam.
My pleasure!
Very informative training and with just the right level of detail
Gorgeous explanation! quite useful, highly appreciated ❤❤🔥
You’re welcome! 🙏🏻
That's a great project covering every aspect of data analysis and visualisation.
Very good work mate...
This video deserves more likes and views.
Keep going with other contents as well
Thanks, really glad you appreciate it 👍🏻
Hi Adam great video, I'm learn a lot about designing relational database. Maybe you can add the explanation about the relationship type, is it one to many, many to many, or many to one. Also for your question about the most efficient query at 32.00 here what I find:
SELECT
start_time,
end_time,
TIME_TO_SEC(TIMEDIFF(end_time, start_time)) / 3600 AS hours_difference
Hi Adam, absolutely loved the video! I have a couple of question about what you did while designing the DB.
1. While choosing datatypes for date/created_date why did you choose datetime and not timestamp?
2. Does the direction of the linkage matter when you linked staff_id in rota to staff_id in the staff table? @11:57
I am just getting started so please need your help. TIA! You might see more comments from me as I progress through the video : )
Glad you enjoyed the video. TBH, the answers to your questions can be found using a simple Google search. It's not that I don't want to be helpful, it's just that the articles/posts you find from there will probably explain it better than I would in the comment of a UA-cam video 👍🏻
Lovely Project and description, I hope you see this and know that you are amazing, and if you don't mind I'll definitely recreate this and add as part of my portfolio. Amazing Project and scenario. Thank You once again.
My pleasure. And of course you can recreate it for your portfolio 👍🏻
excellent video. very instructive and easy to understand.
This was awesome! Please do more paid projects! Can you do one for Excel, Tableau and ML?
There are 2-3 more coming in January
So much fun!!! Looooved it!!!
Great!
Excellent video for those who are starting out in SQL and to learn about the tools that are being used today. But I would like to know what tool is used when creating the relational model. I found the ease with which the SQL lines are written and at the same time the table is displayed quite interesting.
It’s called quickdatabasediagrams.com
Thanks for sharing your knowledge ❤
Thanks for sharing high-quality information.
My pleasure.
That's really amazing.
Very helpful video .. Thank you very much Sir.
Most welcome
Awesome video!
amazing! thank you so much
My pleasure
Thank you Adam!
My pleasure!
Thanks, bro. that is a great project
You're welcome
Hi I'm stuck on 33:48. Not sure where to access google cloud back office after creating an account. How would I access it? Am I uploading the previous work onto google cloud? Am I using an instance created by you?
Thanks!
You’ll need to create your own instance. To access it once it’s set up you need to go to cloud.google.com and click on Console in the header.
@@LearnBI where do I navigate to after clicking console? Also is there an explanation on how to create our own instance? I purchased your files, am I supposed to run the dump file somewhere?
Sorry, I just feel a little lost after completing section 2.
I think the best thing for you to do would be to install MySQL onto your computer (find a tutorial on UA-cam) and create an instance that way. Then you could use Navicat or MySQL Workbench to run the sql dump file.
@@LearnBI same problem, i still can't figure out how to connect mysql to looker. i have no clue how to set up to the connector in looker:(
@@nyomantriananda3204 My Favourite SQL Tool - Navicat Tutorial
ua-cam.com/video/f4TzPRJ6gLA/v-deo.html
This is great.
Thanks a lot, it's very useful :)
You're welcome!
Amazing Work Thanks.
You’re welcome
CAN YOU TELL ME HOW HE PREPARE TABLE CONNECTION IN FIRST PART. I AM NEW IN DATA SCIENCE
First diagram for the Orders customers items and address - I believe the relationships are flipped. wouldn't it make more sense that the customer table is in a one-to-many relationship to the Orders Table and not vis-versa. because the repetition (the many) happens in the Orders table not the customers table.
You are right
This is a masterpiece/ crash course on how to do reports. Im tired of using Excel, Guess im changing tools now
Glad you like it!
You are amazing. 😍
No, you are 🤩
It's a shame this is the only project. I enjoyed this and would gladly pay to do others.
I’d like to make more but it’s just a question of finding the time
Great tutorial!!!!
Thank you!
Hi Adam! Thank you lots for this video! Many people recommended your channel on Tiktok, and I you're truly amazing teacher! I'm wondering, is there an excel file with all data tables together? I cannot find it (for example 10:06)
Glad you like the video. The data is available for purchase here ➡️learnbi.online/pizzaproject. Thanks for your support! 🙏🏻
Amazing job
Thanks!
Hi, Adam Finer
I appreciate the amazing work you have done in creating this insightful video.
It will be helpful if u give raw data set of these(not a cleaned dataset)
Sorry but I’m not sure I understand what you’re asking for. Raw and clean data are not opposites.
Thank you so much for this video, it is really great ! I was wondering why do I need to do this SQL queries before taking the data to the data visualization tool if we can do this connections and calculations directly there? When will I have to do this and when not?
It will depend on the sql functionalities included with the BI tool you’re using. Most allow you to use custom queries to create data sources but you’ll need to write them before hand is they are complex and BI tools aren’t really made for that. Also, the sql queries I wrote in the project are to create data sources in the bi tool, not to get the data for individual visualizations (which might be actually what you’re referring to).
In some cases - where the BI tool has data modelling capabilities and there are just simple joins to be made - you can simply connect your database to your BI tool. In this case the SQL queries are more complex (e.g. contain sub-queries) and cannot be done in the BI tool.
Hello Adam, I appreciate the amazing work you have done in creating this insightful video. Might you be able to point me towards a basic SQL course. I am new in all the IT stuff and currently doing a beginner Data analytics course. I need to understand the SQL formulas and how to create them. Thank you for fantastic work.
Thanks for your feedback! There's a 3-part series on my channel that teaches basic SQL: ua-cam.com/play/PLR0triVyTrBWOLNu3ato7Y9hnGVyhTe1c.html I'd recommend you start there. Then you could take a look at the courses offered by LearnSQL: geni.us/learnsql
Hey. I am new too in the field. Have you chosen a project to do?
Hi where can i find the sql scrypt for creting tables? can i do this project for example in dbeaver? Thanks
Check out my SQL playlist
I just reached 31:55, where we need to calculate the total hours per shift, and this method worked: "SELECT
r.date,
s.first_name,
s.last_name,
s.hourly_rate,
sh.start_time,
sh.end_time,
TIMESTAMPDIFF(HOUR, sh.start_time, sh.end_time) AS TimeDifferenceInHours,
s.hourly_rate * TIMESTAMPDIFF(HOUR, sh.start_time, sh.end_time) AS TotalHourlyRate
FROM
rota r
LEFT JOIN staff s ON r.staff_id = s.staff_id
LEFT JOIN shift sh ON r.shift_id = sh.shift_id;"
Hi Adam and thank you for making this video. I have a question and I scrolled down and didn't see anyone asking. At 10 minutes and 44 seconds, the diagram shows the inventory table primary key is not being used on recipe table (as there is no item ID in it). Should that have been connected to the Item table instead? And bypassed recipe table altogether. Or should recipe table have an Inventory ID? Let us know it you get a chance.
Hi Adam, thanks for the video.
I am completely stuck at the 'Database Authentication' part of the video at 33:50. I think I have successfully created an instance, but whenever I fill in the next 3 boxes I just get an error message. I've tried everything, asked for help from colleagues and googled with no luck. Can you advise please?
Hi, it's really difficult for me to help with so little information. All of the connection information you'll need can be found in Google Cloud Platform. It's possible that you'll need to whitelist your IP address in Connections>>NETWORKING in your instance.
Grateful for that
God bless
@Adam Finer Loved your explanation of the project-there’s so much to learn! I just wanted to ask, where can we get the data from? Also, how do we download the database?
There’s a link in the description.
Thank you for this video!!! you gave us a good amount of content. I have a doubt about the data tables, from where can we get all the data that you've shown in the Excel spreadsheet? Could you please provide them?
The data is available for purchase here: learnbi.online/pizzaproject
Hello Sir , it very awesome way to explaine the difficult things to sipmle form !
can make this dashboard in MS Power BI also
Very Inspiring
you are the best
No, you’re the best! ☺️
When clicking run, it only gives about 4 row. How to get all the rows?
Far too little information for me to help you
When I try to add tables in Navicat, only 2 talbes are added to the database. I wonder where I'm going wrong.
Love your videos and tutorials btw, very grateful for your channel!
You just need to execute the sql file. It already has the data in it
Thank You!!!
You're welcome!
Thank you❤
You're welcome
THANK YOU SO MUCH, could you explain how to get the instance connection name?
That is my personal instance. You would need to set up your own.
51.41 the was a negative stock value Banofie Pie but this could be because you chose random data. Thank you for the video very explicit!
You’re quite right. The orders data isn’t random because it was generated by people placing “real” orders. But the stock data was generated by myself and didn’t allow for the fact that products could sell out. It could easily be fixed by just increasing the amount of inventory in the appropriate table. Glad you like the video!
Hi Adam! Thank you so much for the video!
I would like to clarify why we can join the inventory and s2 on different keys (item_id and ing_id)? I thought we could only join tables with the same key.
You can join where the values in the fields match
@@LearnBI ohh I see, thank you so much!
This is great. If I may ask, do you have any portfolio project using design query in editor for your analysis?
No but you can export the database SQL from from quickdbd into SQL server. Then you’d just need to import the data into the database you create.
Thanku sir Iam waiting
Hello thanks for the information and project. Is this video your project? or is your proect just a video without your naration/audio-video?
Yes, this is my project that I built from scratch. For the data I built a fake pizza ordering page so that people could create fake orders to generate the data.
Hello mate great video. But can you do a small video of last part with tableau?
Nice idea but this is a SQL project, not a Tableau project one.
@@LearnBI will we have one with tableau? Because tableau and power bi used in most companies, im in internship and the company use tableau. Thanks for answering 🖤
I have no plans to at the moment. But I do teach both Tableau and Power BI at the Learn BI Academy ➡️ www.learnbi.academy/
I couldn't get why you connected the item id from inventory to the ingredients id in the recipe can you explain a bit
Those 2 fields contain the same data.
Great explanation but I always fibds it hard to understand the maths, for hoe to calculate cost or unit cost, etc. Is there a way I can improve that?
ขอขอบคุณ
Thank you 🙏🏻
@LearnBI Hi Adam, Thanks a lot for the step-by-step guide videos they've been really helpful. However I'm really new data analytics so I'm still trying to figure out a lot of things. I'm trying to follow up with you in this tutorial step by step but I don't even know where to begin, are you using looker studio to work on this project and also how do I get the data you working with? Thanks
I have a question on how to do the database authentication during the cloud sql for mysql part, any video on that, thank you very much
Great vid! thanks for this. Can i use Tableau instead of Google Data Studio since i know tableau better?
Yes, absolutely
@@LearnBI sorry if I'm asking too much, but can i perform SQL codes in tableau as same as the Google Data Studio?
Hi Adam, thanks for the video. I have a question regarding the relationships you have established for the tables in QuickDBD. Looking at the diagram, it suggests that the cust_id in the orders table and the cust_id in the customers table have a one-to-many relationship, while I think it should be the other way around? Since one customer can appear several times in the orders table but should unique in the customers table. Same for the item_id field in orders and in items.
Thanks for your feedback. Have you watched the entire video?
@@LearnBI Hi Adam, I have finished watching. I could think if two explanations, but not sure which is correct:
1. Have noticed at the end of part 1, there are some error messages regarding foreign key. This probably means the tables were created in Navicat with no relationships established and we would not need them for the purpose of this exercise?
2. In part 2, you used Left outer join to join the tables orders and item on the key item_id (one-to-many based on the QuickDBD diagram). I guess this could be the reason?
Thank you for your reply. Your videos have helped me greatly getting into SQL.
You’re very welcome, I’m glad you find them helpful. I’m not a sql expert, I just use what I know to achieve what I need to. In this project we were able to do what we set out to by creating, populating and querying the tables as we set them up. The cardinality type didn’t really come into play and “joining” the tables when I set them up in dbd was simply to demonstrate the relational model and how tables related to one another. I’m sure most sql professionals would probably cringe when they watch this but that’s fine by me. If it works, it works 😎
@@LearnBI That makes sense. Thanks Adam! Have a nice day.
Why is staff_id (Staff table) to staff_id (Rota table) One-to-Many relationship ? Shouldn't it be Many-to-One ?
Great video btw !!
Thanks! For our purposes and queries, it doesn’t really matter
ref 10:49 , how come item_id under inventory table is connected to ing_id under recipe table ?
All of the joins are explained in the video
Hi I downloaded your paid files, and I was able to work in mySQL workbench, however I am using a local host. What is the easiest way to connect my data to the Looker studio? I thought about creating a Google Cloud mysql instance, but will I be charged??
Because Looker Studio is a cloud-based BI tool, it cannot connect to localhost. My suggestion would be to work on the SQL in Workbench, write the queries you need then export to CSV. Then you can use the file upload connector in Looker to build your reports. Creating a Google Cloud MySQL instance is a paid solution, you'd be looking at around $7-$10 a month.
Wait so you can recreate it entirely if you get the paid project files and follow along with the video? That's insane!
Will it be useful for adding to resume of a Business analysis related person
Definitely
Hey Adam! Love the video and all that you've shown us in it. Although, I created an instance on the GCP and connected my Navicat to my instance but, when I create a database in Navicat, it doesn't show up on my GCP instance. I'm not sure if there is a permissions issue or what may be wrong. Was hoping you could help me with this issue?
Did you solve your problem?
Hi i'm a novice but I couldn't get the Quick DBD model to work on SSMS, I noticed that some of the one-to-many relations are drawn the wrong way round on the diagram. Once fixed it works fine :)
Hi.. I currently face same problem, can you show me how to fix that? really appreciate with your work.
Been a while since these comments have been posted. But the alternative I found was creating these tables in SSMS using Database Diagrams. This provides you with a similar visual way of creating the tables.
You can also create these tables, export them to MYSQL and then give the code provided to chat GPT and ask it to make it compatible to SSMS.
Hello Adam, great video! I am learning a lot about relational databases from your tutorial. I have a question about the relationship between `Ingredient.ing_weight` and `recipe.quantity`. As I understand it, `recipe.quantity` represents the weight of each ingredient in each item, and `Ingredient.ing_weight` represents unit weight as ingredient price.
The way I calculate the cost of each ingredient of each item is as follows:
(recipe.quantity/Ingredient_weight) * Ing_Price * SUM(orders.quantity).
Am I understand it correctly?
Everything is explained in the video 🙂
Hey Adam ... everything is amazing ...can you please tell me from where i can get this data ?..ca you please share the files
learnbi.online/pizzaproject
46:53 Can you please explain how you added these stock data sources?
GET YOUR HANDS ON THE DATA
learnbi.online/pizzaproject
It would be matter of immense learning if you share the csv files to insert data to the database tables. Please
@LearnBI
That wouldn’t be fair on those who have purchased it.
It would be matter of immense learning if you share the csv files to insert data to the database tables. Please
That wouldn’t be fair on those who have paid for it.
Nice video.
Hello guys, please I need help with the SQL import and export wizard. I've been trying to set it up to SQL server native client 11.0 but I haven't even seen it as an option in the destination folder. I'm just confused. Thanks.
You’ll need to give a lot more information about what you’re trying to do for anyone to give you advice.
Hello Adam I am following the project and got to the point of buying/downloading the dataset which I did. It turns out that the sql script file did not run successfully. There's an error in adding a foriegn key to the ingredients table and after 2 hours I haven't been able to fix it because you used a different complex style to add the foreign keys. Please can you rectify this error and send me a link to access the corrected version.
Hello Zulu, thanks for your support. You can just ignore those errors. They don’t impact your capacity to work with the database, you will find that all the tables have been created with data.
@@LearnBI It wont run past that line
@@zuby2550 which line are you referring to? And how are you executing the SQL?
@@LearnBI The create address table statement. I tried running both the entire code or just that statement but both dont work.
They should work, they’re simple statements that work for everyone else so there must be a problem with the way running the sql. Where and how are you executing the file please?
Hello Adam, can you explain the fix for the orders by address, what I understood was that you added "united states" to the end of the full address for each order therefore putting them all in the same country, but why does the geo map still show a map of the UK?
Do you have a video timestamp?
@@LearnBI yes from 44:20
@@ibejibenson6065 I can't really explain it any better than I do in the video.
Can you confirm whether what I understood was right or not? Did you make all the address located in the united states?@@LearnBI
@ibejibenson6065 I created a calculated field that appended "United States" to the address field and used that in the query.
Sir, ¿Can I do this proyect with SSMS?
Thanks you!
I don't see why not. You would just need to export to SQL Server from QuickDBD.