Need resources for ER diagram and SQL

[late347]

I have a gut feeling that I will retake a test about these subjects.

Do you know good resources about creating ER diagram and relational model for database (sql database primarily)

Sometimes even creating a correct ER Diagram (chen notation) feels difficult and confusing. These type of homework problems have been things like word problems where we must construvt the ER diagram from a short written description.

For relational model... I think I can create relational model when I have the correctly defined ER diagram from which to begin work...

I think I could also use some resources for SQL queries but I do know already some basic queries. I studied some basic queries at w3school and I took the quiz about SQL but still I don't really have good enough understanding for a test at our school.

But I don't quite fully understand the logic begind the queries. Especially
Left join, right join, aliases, inner join,

And the logic begind the where operator in SQL queries is a little bit fuzzy to me. Especially if you somehow combine multiple tables in a query. How are you supposed to know which fields(?) Are equal to which fields in let's say two tables...?

 

[shadowshed]

And the logic begind the where operator in SQL queries is a little bit fuzzy to me. Especially if you somehow combine multiple tables in a query. How are you supposed to know which fields(?) Are equal to which fields in let's say two tables...?

This is about WHERE syntax, right?

For example, when you have 2 tables and you run this query:

SELECT * FROM TABLE1, TABLE2

you'll get the output rows, which each made of every row in TABLE1 paired with every row in TABLE2.

So when you run another query like this:

SELECT * FROM TABLE1, TABLE2
WHERE COLUMN_A = COLUMN_B

basically it'll give you the same result as previous query, except that it checks that in every row, the paired ones have two column values that are equal.

My suggestion is, try it out! It'll make more sense.

 

[late347]

This is about WHERE syntax, right?

For example, when you have 2 tables and you run this query:

SELECT * FROM TABLE1, TABLE2

you'll get the output rows, which each made of every row in TABLE1 paired with every row in TABLE2.

So when you run another query like this:

SELECT * FROM TABLE1, TABLE2
WHERE COLUMN_A = COLUMN_B

basically it'll give you the same result as previous query, except that it checks that in every row, the paired ones have two column values that are equal.

My suggestion is, try it out! It'll make more sense.

What do you mean by column a and column b.

In which table is what column?Okay when you press select * from table1,table2;That means that your result is everything from both of the tables...

 

[shadowshed]

I mean TABLE1.COLUMN_A and TABLE2.COLUMN_B

 

[late347]

I mean TABLE1.COLUMN_A and TABLE2.COLUMN_B

okay since you now changed the query.

I know that select portion chooses those columns which we want in the result. Eg.

select name,age from table1;
this kind of query would probably show in the results, the name column and the age column from that table1.

my understanding is that in the from portion. It is required that you select some column, which actually exists in that table where from you are selecting.
If you have generalized column names such as "name" such that for example...
name column appears in table1, and also in table2.
Then you get an error if you simply select using "name ". My understanding is that you need to specify it further such as...

select table1.name, table2.name from table1, table2;
This query would select both each name column from table1 and table2

basically it'll give you the same result as previous query, except that it checks that in every row, the paired ones have two column values that are equal.

My suggestion is, try it out! It'll make more sense.

that was a little bit confusing. What are the "paired ones" ?
Which exact things are equal to each other?

 

[shadowshed]

okay since you now changed the query.

I know that select portion chooses those columns which we want in the result. Eg.

select name,age from table1;
this kind of query would probably show in the results, the name column and the age column from that table1.

my understanding is that in the from portion. It is required that you select some column, which actually exists in that table where from you are selecting.
If you have generalized column names such as "name" such that for example...
name column appears in table1, and also in table2.
Then you get an error if you simply select using "name ". My understanding is that you need to specify it further such as...

select table1.name, table2.name from table1, table2;
This query would select both each name column from table1 and table2

Yes, you are correct.

that was a little bit confusing. What are the "paired ones" ?
Which exact things are equal to each other?

Hmm, what I call as "paired ones" are table1's rows X table2's rows.
I believe you noticed that when you tried it before.

So in the second query, we added a constraint WHERE TABLE1.COLUMN_A = TABLE2.COLUMN_B. The query executes the SELECT function first, and then it checks on every row, filtering out rows that doesn't match the constraint.

If you still quite don't get it, try to play and run it.