SQL ERD, PHP For-loop, SQL Equi-Join/Nequi-Join/Self-Join/Outer-Join

Hyun Seo (Lucy) Lee 이현서·2020년 9월 24일
0
post-thumbnail

9/24/2020

SQL Entity-Relationship Diagram (ERD)

  • all tables are in a parent-child relationship. This is the whole relation part in RDBSM. None of the tables are without a relationship to another table. (Since that is true, it's also true that all tables are able to be "JOINED" together as they will be linked SOMEhow, though possibly through many many nodes of tables.)
  • Primary Keys serve a very important role in the relationships.
  • Parent table is created first, and the child table usually refers to the parent table through a column in its field that references the PK of the parent field.
    • ex: if dno is dept table's PK, then emp table can have a column dno that is its FK (Foreign Key) and it references the dno from the parent table dept.

PHP For-loop

  • in for or while loops, "break;" will let you get out of the loops.

  • "continue;" will get you out of that current instance of the loop, moving onto the next instance of loop.

    In an example: for ($i = 1, $a = 1; $i <= 29; $i = $i + $a, $a++) {}, the $i=1,$a=1 (initializer) gets executed first, and then the condition $i<=29 gets checked. If true, it executes the stuff inside the brackets {}, and then lastly, it executes the incremental stuff like $i=$i+$a and $a++ get executed. Then it repeates until the condition is false!

SQL Equi-Join & Nequi-Join
> > SELECT table1.col, ... table2.col, ... FROM table1, table2, ... WHERE join_condition AND regular_conditions;

  • if there are n tables, there must be AT LEAST n-1 join conditions.

    ex: > SELECT eno, ename, emp.dno, dept.dno, dname FROM dept, emp WHERE dept.dno = emp.dno; --> the underlined part is the Equi Join condition.

  • Nequi-Join

    ex: > SELECT eno, ename, sal, grade FROM emp, salgrade WHERE sal BETWEEN losal AND hisal;

(note: you can put a nickname for each of the tables, but once you do so, you must only use the nickname in the SELECT and all the other parts of the query.)

SQL Self-Join

> SELECT nickname1.col1, ... nickname2.col2, ... FROM table nickname1, table nickname2, ... WHERE join_condition AND regular_condition;

  • You HAVE to use a nickname for each of the tables since you are SELF-joining. You need the SQL to treat it as if they were 2 different tables, and by giving it different nicknames, you're basically creating a copy of it.

Outer-Join

> SELECT d.dno, dname, ename FROM dept d, emp e WHERE d.dno = e.dno(+) ORDER BY 1;

  • There may be some values of d.dno that is not mentioned in e.dno. After all, a child table does not mean that it had to have used ALL of its parent's PKs.
  • Whatever the focus is on, you should do the (+) on the OTHER table to ensure that the focused data does not get left out. (Ex: print ALL professors and the list of their courses --> there may be some professors who do not have any courses currently. so put (+) on the course table side. If it should be ALL the courses with their professors on it, then it would be the opposite.)

0개의 댓글