1 2 3 < >
1 SQL SQL 2 SQL 3
column DEPT DEPT# DNAME BUDGET D1 D2 D3 Marketing Development Research 10M 12M 5M tuple EMP EMP# ENAME DEPT# SALARY D1 40 D1 45 E1 E2 E3 Lopez Cheng Finzi D2 30 E4 Satio D2 35
SELECT (RESTRICT) : DEPT# DNAME BUDGET DEPTs where BUDGET > 8M D1 Marketing 10M D2 Development 12M PROJECT : DEPTs over DEPT#, BUDGET DEPT# D1 D2 D3 BUDGET 10M 12M 5M
DEPTs and EMPs over DEPT# DEPT# DNAME BUDGET D1 D1 D2 Marketing Marketing Development 10M 10M 12M EMP# E1 E2 E3 ENAME Lopez Cheng Finzi SALARY 40K 42K 30K D2 Development 12M E4 Satio 35K
table world operator operator operator
DEPT# D1.. EMP# E1, E2.. repeating group DEPT# D1 D1.. EMP# E1 E2..
< > 169 680902-10634 29 DH01 170 670802-11654 30 DH01 171 610701-20631 41 DH02 172 710814-10721 26 DH03 173 681202-12739 33 DH04 174 620211-10621 34 DH03 SP S# S1 S1 S1 S1 S1 S1 S2 S2 S3 S4 S4 S4 P# P1 P2 P3 P4 P5 P6 P1 P2 P2 P2 P4 P5 QTY 300 200 400 200 100 100 300 400 200 200 300 400
S# S1 S2 S3 S4 S4 SNAME STATUS 20 10 30 20 30 CITY S S# S1 S2 SNAME STATUS 20 10 CITY S S# S1 S2 S3 SNAME STATUS 20 10 30 CITY S ERROR!
A S0001 S0002 S0003 701212-1063423 720818-1023531 740921-1125342 B S0001 S0001 S0002 5,000 6,000 4,000
< > 169 680902-10634 29 DH01 170 670802-11654 30 DH01 169 610701-20631 41 DH02 172 710814-10721 26 DH03 173 681202-12739 33 DH04 174 620211-10621 34 DH03
S# S1 S2 S3 S4 S5 SNAME STATUS 20 10 30 20 30 CITY S P# P1 P2 P3 P4 P5 P6 PNAME WEIGHT 12 17 17 14 12 19 CITY P COLOR Red Green Blue Red Blue Red S# S1 S1 S1 S1 S1 S1 S2 S2 S3 S4 S4 S4 P# P1 P2 P3 P4 P5 P6 P1 P2 P2 P2 P4 P5 QTY 300 200 400 200 100 100 300 400 200 200 300 400 SP
< > < > 169 680902-10634 29 DH01 170 670802-11654 30 DH01 171 610701-20631 41 DH02 172 710814-10721 26 DH03 173 681202-12739 33 DH04 174 620211-10621 34 DH03 DH01 DH02 DH03 DH04 DH05 DH06
DEPT EMP DEPT# DNAME BUDGET EMP# ENAME DEPT# SALARY primary key primary key foreign key
S# P#
< > manager 169 680902-10634 171 DH01 170 670802-11654 174 DH01 171 610701-20631 174 DH02 172 710814-10721 170 DH03 173 681202-12739 171 DH04 174 620211-10621 DH03
< > < > 169 680902-10634 29 DH01 170 670802-11654 30 DH01 171 610701-20631 41 DH02 172 710814-10721 26 DH03 173 681202-12739 33 DH04 174 620211-10621 34 DH03 DH01 DH02 DH03 DH04 DH05 DH06
fi EMP EMP# ENAME DEPT# SALARY E1 E2 E3 E4 Lopez Cheng Finzi Satio D1 D1 D2 D2 40 45 30 35
EMP EMP# ENAME DEPT# SALARY D1 40 D1 45 E1 E2 E3 Lopez Cheng Finzi D2 30 E4 Satio D2 35 NEW_EMP1 EMP# ENAME E1 E2 E3 E4 Lopez Cheng Finzi Satio DEPT# D1 D1 D2 D2
EMP EMP# ENAME DEPT# SALARY E1 E2 E3 E4 Lopez Cheng Finzi Satio D1 D1 D2 D2 40 45 30 35
EMP EMP# ENAME DEPT# SALARY D1 40 D1 45 E1 E2 E3 Lopez Cheng Finzi D2 30 E4 Satio D2 35 NEW_EMP2 EMP# ENAME SALARY E1 Lopez 40 E2 Cheng 45 E4 Satio 35
SELECT emp#, ename FROM new_emp1 WHERE emp# = E3 SELECT emp#, ename, dept# FROM new_emp2 WHERE emp# = E2 SELECT emp#, ename FROM new_emp2 WHERE emp# = E3 OK Error! No result
student 97012 79.3.1 20 97013 79.4.5 20 97014 78.2.3 21 Student_view1 Student_view2 Student_view3 97012 97013 97014 97012 3.1 20 97013 4.5 20 97014 2.3 21 97012 97 97013 97 97014 97
EMP EMP# ENAME DEPT# SALARY E1 E2 E3 Lopez Cheng Finzi D1 D1 D2 E4 Satio D2 35 40 45 30 NEW_EMP1 EMP# ENAME E1 E2 E3 E4 Lopez Cheng Finzi Satio DEPT# D1 D1 D2 D2
SELECT emp#, ename, dname FROM emp, dept WHERE emp.dept# = dept.dept# CREATE VIEW myemp AS SELECT emp#, ename, dname FROM emp, dept WHERE emp.dept# = dept.dept# SELECT emp#, ename, dname FROM myemp
SELECT emp#, ename, dname FROM myemp WHERE emp# = E3 SELECT emp#, ename, dname FROM emp, dept WHERE emp.dept# = dept.dept# AND emp# = E3
EMP EMP# ENAME DEPT# SALARY E1 E2 E3 Lopez Cheng Finzi D1 D1 D2 E4 Satio D2 35 40 45 30 NEW_EMP1 EMP# ENAME E1 E2 E3 Lopez Cheng Finzi E4 Satio DEPT# D1 D1 D2 D2? OK OK
NEW_EMP3 EMP# ENAME DEPT# UPSALARY E1 E2 E3 Lopez Cheng Finzi D1 D1 D2 140 145 130 E4 Satio D2 135
Production_data CREATE SNAPSHOT daily_prod_summary AS SELECT prod_date, sum(amount) as total GROUP BY prod_date REFRESH EVERY DAY ;
Production_data Daily_prod_summary Daily update