Steve, can you do a video on how to architect a huge project like this or something like an ecommerce on MYSQL? Like how many tables we would need and how to connect them together.
I might do a project course some day using MySQL and database architecting. The number of tables varies greatly depending on what you are doing. I've worked on sites with ecommerce parts that had 60 tables and others that had 200+.
For practice purposes I've created these 8 tables by myself and not download the file you suggested in the beginning of the video 7:52 can you explain how did you create a foriegn key inside two tables "payments" and "orders" on column 'customerNumber: int(11)' that points to the same column named 'customerNumber: int(11)' inside the "customer" table ..?? Because its giving me an error when I'm trying to create it normally.
This video explains how I add Foreign Keys - ua-cam.com/video/UQK9_gKQHZg/v-deo.html The biggest issue with creating them is that ALL the properties of the matching column in the two tables must be EXACTLY the same. Not NULL, datatype, Unsigned, etc.
@@SteveGriffith-Prof3ssorSt3v3 I've already watched the video in the video you explain how to add foriegn key to columns but what if i want to add a foriegn key in table A and in table B and that points to the same column in column C at 7:52 i can see in your Designer that column customerNumber inside orders table and customerNumber inside payments table are pointing to the same column customerNumber inside customer table, sorry for this long ass question but i can't seem to figure out how do i point two columns from different table to same column.
@@SteveGriffith-Prof3ssorSt3v3 I'm such an idiot, it was a small typo and I'm sorry for all this hustle And i Really appreciate your guidance sir you're doing a great job.
Hi Steve. Instead of selecting from the Productline table if one selects from orderDetails table instead will it have performance issues? I mean deciding on the MAIN table in a select statement with a JOIN clause
With any Select clause that accesses multiple tables you need to think through the sequence of joins. Each Join is going to be restricting the number of matches based on the primary key and foreign key relationships. Each table should be representing a specific real world object. Your SQL statement's Select clause needs to use the correct tables to represent the real data. You shouldn't have multiple tables that all have the same data. There needs to be a purpose behind each table. There may be similar data in two tables but they should represent different things. A products table is a list of all products. A line item table represents a product that is placed into an order, which will have a snapshot of the price at that moment in time plus a quantity. They may have the same product id in both tables but they have a different meaning. Always use the correct table.
Hello Steve, if I want to create reports in MS SQL Server. Which tool is better the Report Builder or the Visual Studio (Data Tools) or something else?
Thank you very much Steve! This was very helpful! I got one question though. I see that in your relational data base the join path you followed was like a chain. However, if you wanted to select a colum from the "payments" table, how would you do it? I have one of those cases and I haven´t figured out how to reach that other table that is not in the same linear path.
The join will change depending on what else you want beyond "payments". With any joined result set you can select things from different tables. SELECT payments.amount, customers.customerName FROM customers INNER JOIN payments ON customers.customerNumber = payments.customerNumber you just need to have the table name (or alias) in front of each column name in the SELECT clause.
@@SteveGriffith-Prof3ssorSt3v3 That would be a different query though, is there a way to do it in one go? I mean, combing your initial selection with one that could get data from payments as well, not possible?
@Santiago Sierra yes you can. Just join each table, in order, connecting the primary and foreign keys. The SELECT clause has access to all the joined tables.
@@SteveGriffith-Prof3ssorSt3v3, thank you for your follow up! I've been using Workbench to follow your videos, and I'm happy you told me about that feature since I didn't know about it. You are the best!
I thought I was good enough for db only to come back in this video not knowing anything about joins. Thank you for this! Question though: if I have a query of SELECT std .name FROM student as std LEFT JOIN room as rm ON rm.student_id = std.student_id LEFT JOIN class as cl ON cl.room_id = rm.room_id I know that student table is the Left table for the first join. What would be the Left table of the second join? Is it the class table or the room table? When combining multiple joins like Left then Right then Inner, does that cancel each other? Thank you for the reply, Prof Steve.
simply put you are saying that you want everything in the recordset that is to the LEFT or RIGHT of the JOIN keyword and then the stuff that matches ON from the other side.
hello steve, i wonder u have videos about database optimization? like indexes, how to deal with locking etc... anything that improve database performance, bec that is really my biggest problem right now, website is slow
Rows don't get joined. Rows can be part of the same recordset, when you do a SELECT. You might be thinking of a GROUP BY clause that can be inside a SELECT statement. - ua-cam.com/video/cW2MLIZ5CaU/v-deo.html
@@ABUTAHER-wg7gz I haven't used woocommerce in about 20 years. WordPress in about 10. If you watch the full MySQL Playlist from the start you will find explanations for any standard query.
The world is run on databases.
Nice series, clear and concise.
Thanks. Next video will be up in 15 minutes.
Thank you :) have a SQL interview next Thursday and studying. Great speaking voice and clear video!
My gratitude is infinitely endless and heartfelt. Thank you.
That's such a great video with an exceptionally cool explanation of the theme.
Glad to watch this as this cleared a big doubt of mine!!
keep it up 👍.
Thank you, thank you. just what I needed to figure out my query.
Nice work Steve, you're a godsend. Thank you.
Amazing explanation
This is super helpful. Thank you.
7:55
Steve, can you do a video on how to architect a huge project like this or something like an ecommerce on MYSQL? Like how many tables we would need and how to connect them together.
I might do a project course some day using MySQL and database architecting. The number of tables varies greatly depending on what you are doing. I've worked on sites with ecommerce parts that had 60 tables and others that had 200+.
When running a user defined function, is it important you reference it to a table like your syntax included 'from people'
your voice is amazing
Thanks for help
For practice purposes I've created these 8 tables by myself and not download the file you suggested in the beginning of the video
7:52 can you explain how did you create a foriegn key inside two tables "payments" and "orders" on column 'customerNumber: int(11)' that points to the same column named 'customerNumber: int(11)' inside the "customer" table ..??
Because its giving me an error when I'm trying to create it normally.
This video explains how I add Foreign Keys - ua-cam.com/video/UQK9_gKQHZg/v-deo.html
The biggest issue with creating them is that ALL the properties of the matching column in the two tables must be EXACTLY the same. Not NULL, datatype, Unsigned, etc.
@@SteveGriffith-Prof3ssorSt3v3 I've already watched the video in the video you explain how to add foriegn key to columns but what if i want to add a foriegn key in table A and in table B and that points to the same column in column C at 7:52 i can see in your Designer that column customerNumber inside orders table and customerNumber inside payments table are pointing to the same column customerNumber inside customer table, sorry for this long ass question but i can't seem to figure out how do i point two columns from different table to same column.
And the data type int, not null etc are identical in my columns I've checked it literally 23 times
@@aadil4236 You need to add them separately. You are turning one column into a Foreign Key in each table.
@@SteveGriffith-Prof3ssorSt3v3 I'm such an idiot, it was a small typo and I'm sorry for all this hustle
And i Really appreciate your guidance sir you're doing a great job.
Hi Steve. Instead of selecting from the Productline table if one selects from orderDetails table instead will it have performance issues? I mean deciding on the MAIN table in a select statement with a JOIN clause
With any Select clause that accesses multiple tables you need to think through the sequence of joins. Each Join is going to be restricting the number of matches based on the primary key and foreign key relationships. Each table should be representing a specific real world object. Your SQL statement's Select clause needs to use the correct tables to represent the real data. You shouldn't have multiple tables that all have the same data. There needs to be a purpose behind each table. There may be similar data in two tables but they should represent different things. A products table is a list of all products. A line item table represents a product that is placed into an order, which will have a snapshot of the price at that moment in time plus a quantity.
They may have the same product id in both tables but they have a different meaning.
Always use the correct table.
@@SteveGriffith-Prof3ssorSt3v3 thanks for your response bro. Much much appreciated
Hello Steve, if I want to create reports in MS SQL Server. Which tool is better the Report Builder or the Visual Studio (Data Tools) or something else?
Couldn't say. It's been years since I worked with MS SQL server
Thank you very much Steve! This was very helpful! I got one question though. I see that in your relational data base the join path you followed was like a chain. However, if you wanted to select a colum from the "payments" table, how would you do it? I have one of those cases and I haven´t figured out how to reach that other table that is not in the same linear path.
The join will change depending on what else you want beyond "payments".
With any joined result set you can select things from different tables.
SELECT payments.amount, customers.customerName
FROM customers INNER JOIN payments ON customers.customerNumber = payments.customerNumber
you just need to have the table name (or alias) in front of each column name in the SELECT clause.
@@SteveGriffith-Prof3ssorSt3v3 That would be a different query though, is there a way to do it in one go? I mean, combing your initial selection with one that could get data from payments as well, not possible?
@Santiago Sierra yes you can. Just join each table, in order, connecting the primary and foreign keys. The SELECT clause has access to all the joined tables.
Thanks again for these videos! You're fantastic. I hate to ask, but is there a PDF or image file of this ERD? If not, I can screenshot it. Thank you!
If you download MySQL Workbench, you can connect to the database and it will let you generate and edit the ERD. dev.mysql.com/downloads/workbench/
@@SteveGriffith-Prof3ssorSt3v3, thank you for your follow up! I've been using Workbench to follow your videos, and I'm happy you told me about that feature since I didn't know about it. You are the best!
I thought I was good enough for db only to come back in this video not knowing anything about joins. Thank you for this! Question though: if I have a query of
SELECT std .name FROM student as std
LEFT JOIN room as rm ON rm.student_id = std.student_id
LEFT JOIN class as cl ON cl.room_id = rm.room_id
I know that student table is the Left table for the first join. What would be the Left table of the second join? Is it the class table or the room table?
When combining multiple joins like Left then Right then Inner, does that cancel each other? Thank you for the reply, Prof Steve.
simply put you are saying that you want everything in the recordset that is to the LEFT or RIGHT of the JOIN keyword and then the stuff that matches ON from the other side.
Hi, I want to practice with your videos. Although I'm not sure where to run these queries when I click on the link. Where should I go?
If you watch the series from the start I explain how to set it up.
The script to create the DB failed to run in a Postgres12/PgAdmin4/Linux. One example of syntax error is: int(11).
It is a MySQL database export file, not a postgreSQL one.
@@SteveGriffith-Prof3ssorSt3v3 Hummm OK. My bad...
the changes are likely minor but would have to be made in the .sql file.
Do you have few exercises to practice on this customer database.?
Sorry. No
hello steve, i wonder u have videos about database optimization? like indexes, how to deal with locking etc... anything that improve database performance, bec that is really my biggest problem right now, website is slow
Nope. Sorry
Can I use this db file in mysql workbench?
Yes!
hello mr. how to join two row join same table, how to maintain woocommarce product
Rows don't get joined. Rows can be part of the same recordset, when you do a SELECT.
You might be thinking of a GROUP BY clause that can be inside a SELECT statement. - ua-cam.com/video/cW2MLIZ5CaU/v-deo.html
@@SteveGriffith-Prof3ssorSt3v3 sorry, it will be column, means how to wordpress post are working
@@SteveGriffith-Prof3ssorSt3v3 but i want a single category products every fetch like where category='something'
@@SteveGriffith-Prof3ssorSt3v3 if is it build in woocommarce api's category search, not mysql query fetch
@@ABUTAHER-wg7gz I haven't used woocommerce in about 20 years. WordPress in about 10. If you watch the full MySQL Playlist from the start you will find explanations for any standard query.
why too many browser???
To test websites on all the platforms