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

How do the different tables help to organize?

Accepted Answer

Typically, in a database system, each table will represent a different entity - students, classrooms, courses - or it will be a a bridge table (which is used to linked tables with many-to-many relationships).

Keeping the data related to an entity in a single table logically organized and it can reduce the number of rows to a minimum.  It also keeps data easier to maintain.

Imagine you had a table that listed a row with a teacher's name, the course the teacher is teaching, and the classroom number.  Now, imagine that you have one teacher who teaches eight different courses.  This means you have rows with the teacher's name repeated eight times.

A few days into the term, you discover that the teacher's name has been misspelled.  If you had a single table with teacher names, you could just update the name in one row in the `teachers` table.  However, in this case, you need to change the name eight different times.

That may not sound so bad.  Now, imagine that you are working for a large company that does business with other large companies.  One of the companies that supplies you with hundreds of parts decides to rebrand and change its name.  Now, imagine having to change the name across hundreds of parts and thousands of documents.  Imagine trying to remember every place that the name is used. Changing the name in one table is much faster.

Question 8

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 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 - JavaScript and the web

Course: Computer programming - JavaScript and the web > Unit 3

Splitting data into related tables

Want to join the conversation?

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