Question 1

what is the purpose of having so many different tables, when we can have a single table with all information.?

Accepted Answer

Here's a good discussion:

http://stackoverflow.com/questions/9774715/mysql-multiple-tables-or-one-table-with-many-columns

Basically a single table is good when data is one-to-one. When you have thousands of rows  and columns of data, where the data is one-to-many, multiple tables are better to reduce duplicate data.

Question 2

I have many questions about this topic.
1. Why can't you make something like a variable in SQL? I mean, you can do it in JavaScript, but why not SQL?
2. Why do you have to go row by row changing each thing if you can just change a variable name?
3. And why not put all the information from all the tables into 1 table? 
4. What do you need an id for?
5. What's the importance of SQL tables?
Please answer one of my questions.

Accepted Answer

These are all good questions. It shows you're really thinking about this. I like that.

1. SQL itself is not a programming language. It's a Query Language. You would use another programming language to write a program that sends queries to your database.
Here I use JavaScript with a hypothetical `performSqlQuery` function.
```
var query = "SELECT * FROM students WHERE id = " + studentId;
var result = performSqlQuery(query);
```This leads straight to...

2. You can use variables of the programming language where you create your SQL queries.

3. We want to avoid putting duplicate data into the database.
You could put things into one table like this:
```┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ INVOICE                                                                                                                                                                                │
├───────────┬────────────┬──────────────┬────────────────┬─────────────────┬──────────┬────────────┬───────────────┬──────────────────────────┬────────────────────┬───────┬───────┬─────┤
│ invoicenr │ customernr │ date         │ name           │ address         │ zipcode  │ city       │ country       │ iban                     │ product            │ count | price │ vat │
├───────────┼────────────┼──────────────┼────────────────┼─────────────────┼──────────┼────────────┼───────────────┼──────────────────────────┼────────────────────┼───────┼───────┼─────┤
│ "0003417" │ "93276"    │ "2017-09-01" │ "Joseph Smith" │ "120 Penstreet" │ "3829AD" │ "Somecity" │ "Somecountry" │ "GB29NWBK60161331926819" │ "Brause 361 nib"   │     1 │  2.99 │   19│
│ "0003417" │ "93276"    │ "2017-09-01" │ "Joseph Smith" │ "120 Penstreet" │ "3029AD" │ "Somecity" │ "Somecountry" │ "GB29NWBK60161331926819" │ "Art paper A4"     │     1 │ 13.29 │   19│
│ "0003417" │ "93276"    │ "2017-09-01" │ "Joseph Smith" │ "120 Penstreet" │ "3829AD" │ "Somecity" │ "Somecountry" │ "GB29NWBK60161331926819" │ "Pen holder, blue" │     1 │  5.19 │   19│
└───────────┴────────────┴──────────────┴────────────────┴─────────────────┴──────────┴────────────┴───────────────┴──────────────────────────┴────────────────────┴───────┴───────┴─────┘```(You may have to copy this somewhere to view correctly.)

The problem here is that a lot of data is the same. Only the product and the price are different.
Another problem is keeping the data consistent. In this table someone made a slight mistake. Can you spot it? This invoice may never arrive at the correct address.

It's better to split the data. This way you have less data to store, but more importantly, the data is only in one place. If you want to change someone's ZIP code for example, you don't have to go looking for it in many different places.
```
┌─────────────────────────────────────────────────────────────────────────────────────────┐
│ CUSTOMER                                                                                │
├────┬────────────┬───────────┬────────┬──────────┬────────────┬──────────────────────────┤
│ id │ customernr │ firstname │ prefix │ lastname │ address_id │ iban                     │
├────┼────────────┼───────────┼────────┼──────────┼────────────┼──────────────────────────┤
│  1 │ "93276"    │ "Joseph"  │ ""     │ "Smith"  │          1 │ "GB29NWBK60161331926819" │
└────┴────────────┴───────────┴────────┴──────────┴────────────┴──────────────────────────┘
┌─────────────────────────────────────────────────────────────────────┐
│ ADDRESS                                                             │
├────┬─────────────┬──────────┬──────────┬────────────┬───────────────┤
│ id │ streetname  │  housenr │ zipcode  │ city       │ country       │
├────┼─────────────┼──────────┼──────────┼────────────┼───────────────┤
│  1 │ "Penstreet" │ "120"    │ "3829AD" │ "Somecity" │ "Somecountry" │
└────┴─────────────┴──────────┴──────────┴────────────┴───────────────┘
┌───────────────────────────────────────┐
│ PRODUCT                               │
├────┬────────────────────┬───────┬─────┤
│ id │ name               │ price │ vat │
├────┼────────────────────┼───────┼─────┤
│  1 │ "Brause 361 nib"   │  2.99 │ 19  │
│  2 │ "Art paper A4"     │ 13.29 │ 19  │
│  3 │ "Pen holder, blue" │  5.19 │ 19  │
└────┴────────────────────┴───────┴─────┘
┌─────────────────────────────────────────────┐
│ INVOICE                                     │
├────┬───────────┬─────────────┬──────────────┤
│ id │ invoicenr │ customer_id │ date         │
├────┼───────────┼─────────────┼──────────────┤
│  1 │ "0003417" │           1 │ "2017-09-01" │
└────┴───────────┴─────────────┴──────────────┘
┌──────────────────────────────────────┐
│ INVOICELINE                          │
├────┬────────────┬────────────┬───────┤
│ id │ invoice_id │ product_id │ count │
├────┼────────────┼────────────┼───────┤
│  1 │          1 │          1 │     1 │
│  2 │          1 │          2 │     1 │
│  3 │          1 │          3 │     1 │
└────┴────────────┴────────────┴───────┘
```(You may have to copy this somewhere to view correctly.)

4. In the INVOICE table you can see a `customer_id` of 1. That means that that particular invoice belongs to the customer with id 1. In this case that's Joseph Smith.
Can you tell how the INVOICELINE table works?

5. Perhaps, if you understand the previous answers, you have enough information to answer this yourself. :)

Question 3

What's the different between sub query and "join"? When do we need to use join or sub query? Which is better?

Accepted Answer

Lots of good answers here:

http://stackoverflow.com/questions/2577174/join-vs-sub-query

This seems to be a hot issue around the SQL campfire.

Question 4

while it did not specify which SQL in this article that was used. I am learning SQL server and when I copied the code into SQL server, it does not work. Have you considered if the code would work on different platforms?

Accepted Answer

KhanAcademy says in the introduction to this unit that they use SQLite, which is one particular version. The similarities between versions are greater than the differences, so the concepts carry over, but for your particular use you might have slightly different syntax so you should carefully read the documentation for the specific system you're trying to copy into.

Question 5

When I have multiple Tables can I maintain a directory of sorts to help me relate which table stands for what? 
What happens in an Organization where there are hundreds of rows and columns and so many tables all relating to one another. And the person who created that database is no longer working there. How will I know what table is for what? And how does one maintain a directory, is there such a thing for SQL called "directory"?

Accepted Answer

There is a "SYSTEM" database that is maintained by database system itself, that is a collection of tables containing metadata about the "USER" databases.  For example you can get a list of tables in your database by entering SELECT TABLE_NAME FROM SYSTEM.TABLES where DATABASE_NAME = "MY_MUSIC";  The actual syntax and SYSTEM tables is dependent on the specific database vendor. The column names, datatypes, primary keys, default values, foreign keys and other constraints plus much more are also in system tables

Question 6

when do games come in khan academy?

Accepted Answer

Camacho,

There is a course dedicated to games:  https://www.khanacademy.org/computing/computer-programming/programming-games-visualizations

Question 7

By breaking down information into these multiple tables would that slow down the software or application because it is having to sift through so many tables? I guess what I'm asking is if it can be beneficial to require more detailed queries rather than writing in depth tables.

Accepted Answer

Searching for it might be a bit slower, but writing the data may be quicker because you can omit duplicate data.
Updating data will be even quicker and easier, because you don't have to search everywhere. This is the main reason to separate data into tables: if you do it right, you only have to enter a piece of data once.

Question 8

What is the use of sql commands when one can do with a graphical user interface

Accepted Answer

A graphical user interface (GUI) only displays data. It doesn't store it.
The GUI will fire events when the user does something. Some of those events will tell the program to run SQL statements. Those SQL statements will then save or retrieve the data from the database.

Question 9

What's a scenario where a book detail would change? Seems static to me.

Accepted Answer

It's possible for books to have the same title and same author.  Or you may need to track revisions of a book to identify specific editions or versions.

`student_name`	`student_email`	`test`	`grade`
Peter Rabbit	peter@rabbit.com	Nutrition	95
Alice Wonderland	alice@wonderland.com	Nutrition	92
Peter Rabbit	peter@rabbit.com	Chemistry	85
Alice Wonderland	alice@wonderland.com	Chemistry	95

`student_name`	`book_title`	`book_author`
Peter Rabbit	The Tale of Mrs. Tiggy-Winkle	Beatrix Potter
Peter Rabbit	Jabberwocky	Lewis Carroll
Alice Wonderland	The Hunting of the Snark	Lewis Carroll
Alice Wonderland	Jabberwocky	Lewis Carroll

`id`	`student_first`	`student_last`	`student_email`	`phone`	`birthday`
1	Peter	Rabbit	peter@rabbit.com	555-6666	2001-05-10
2	Alice	Wonderland	alice@wonderland.com	555-4444	2001-04-02

`student_name`	`test`	`grade`
Peter Rabbit	Nutrition	95
Alice Wonderland	Nutrition	92
Peter Rabbit	Chemistry	85
Alice Wonderland	Chemistry	95

`student_id`	`test`	`grade`
1	Nutrition	95
2	Nutrition	92
1	Chemistry	85
2	Chemistry	95

Computer programming

Course: Computer programming > Unit 3

Splitting data into related tables

Want to join the conversation?

`student_id`	`book_id`
1	1
1	2
2	3
2	2