Database SQL table relation & amp; connection Query

Source: Internet
Author: User
Tags dname
* 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.