* Table relationship: one-to-many: multi-port plus foreign keys, one end with a primary key many-to-many: Add a relational table, into a one-to-many relationship one-to-one: foreign key-unique key E-R diagram: entity-Table properties-column tuples-row primary key columns: Unique foreign key columns: used to restrict the access to the associated items of primary key columns * -- create createtablebook (bidnumber (5), bname
/* Table relationship: one-to-many: Multi-terminal plus foreign keys, one-to-many primary keys at one end: Add a relational table to change to one-to-many relationship: foreign key -- unique key E-R diagram: entity -- table properties -- column tuples -- Row primary key columns: unique and only foreign key columns: used to restrict the getrow of primary key column Association items */-- create table book (bid number (5), bname
/*
Table relationship: one-to-many: Add Foreign keys to multiple terminals, and add primary keys to one end
Many-to-many: Add a relational table and change it to a one-to-many relationship.
One-to-one: foreign key --> unique key
E-R diagram: entity --> table
Attribute --> Column
Tuples --> rows
Primary Key column: unique and only
Foreign key column: Used to constrain the values of the correlated items of primary key columns
*/
-- Create
Create table book (
Bid number (5 ),
Bname varchar2 (20 ),
Slid number (5)
)
-- Create a primary key
Alter table book
Add constraints PK_BOOKlei
Primary key (bid)
-- Create a foreign key
Alter table book
Add constraints FK_BOOK_BOOKlei
Foreign key (slid)
References booklei (blid)
-- Create
Create table booklei (
Blid number (5 ),
Blname varchar2 (20)
)
-- Many-to-many
Create table student (
Sid number (5) primary key,
Sname varchar2 (20)
)
Create table tea_stu (
Tid number (5) references student (sid ),
Tid number (5) references teacher (tid)
)
Create table teacher (
Tid number (5) primary key,
Tname varchar2 (20)
)
-- One-to-one
Create table person (
Pid number (5) primary key,
Pname varchar2 (20 ),
Cid number (5) unique references idcard (cid)
)
Create table idcard (
Cid number (5) primary key,
Cnumber varchar2 (20)
)
-- Join query (Cartesian Product)
-- Equijoin (not necessarily =, and the two tables do not necessarily have a primary-foreign key relationship)
Select * from emp e, dept d where e. deptno = d. deptno
-- Query the employees whose department is 'accounting' (** the number of scans for connection queries and nested subqueries is different ***)
Select * from emp e, dept d where e. deptno = d. deptno and d. dname = 'accounting'
Select * from emp where deptno = (select deptno from dept where dname = 'accounting ')
-- Query the employee's salary grade
Select * from SALGRADE s, emp e where e. sal between s. losal and s. hisal
-- Query the number of people of each wage level
Select s. grade, count (*) from emp e, salgrade s
Where e. sal between s. losal and s. hisal
Group by s. grade
-- Query the number of people in each department for each wage level
Select s. grade, d. dname, count (*) from emp e, dept d, salgrade s
Where e. deptno = d. deptno and e. sal between s. losal and s. hisal
Group by s. grade, d. dname
Order by s. grade
-- Outer Join: left Outer Join (based on the table on the left)/right Outer Join (based on the table on the right)
Select * from emp e left join dept d on d. deptno = e. deptno
Select * from dept d left join emp e on d. deptno = e. deptno
Where empno is null
-- Self-connection
Select e1.ename, count (*) from emp e, emp e1 where e. mgr = e1.empno
Group by e1.ename
Zookeeper