LISTINGURI
LISTINGURI DE TABELE
Definitii de tabele:
Aplicatia Personal
CREATE TABLE DEPT (
DEPTNO NUMBER (2) NOT NULL,
DNAME CHAR (14),
LOC CHAR (13),
PRIMARY KEY (DEPTNO) CONSTRAINT DEPT_PRIMARY_KEY);
CREATE TABLE EMP (
EMPNO NUMBER (4) NOT NULL,
ENAME CHAR (10),
JOB CHAR (9),
MGR NUMBER (4) REFERENCES EMP (EMPNO) CONSTRAINT EMP_SELF_KEY,
HIREDATE DATE,
SAL NUMBER (7,2),
COMM NUMBER (7,2),
DEPTNO NUMBER (2) NOT NULL,
FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO) CONSTRAINT EMP_FOREIGN_KEY,
PRIMARY_KEY (EMPNO) CONSTRAINT EMP_PRIMARY_KEY);
CREATE TABLE SALGRADE (
GRADE NUMBER;
LOSAL NUMBER;
HISAL NUMBER);
CREATE TABLE DUMMY (
DUMMY NUMBER);
Listingul tabelelor Personal
SQL> SELECT * FROM DEPT ORDER BY DEPTNO;
DEPTNO DNAME LOC
------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
4 records selected.
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------------------------------------------------------------------------
7369 SMITH CLERK 7902 17-12-80 800 20
7499 ALLEN SALESMAN 7698 20-02-81 1600 300 30
7521 WARD SALESMAN 7698 22-02-81 1250 500 30
7566 JONES MANAGER 7839 02-04-81 2975 20
7654 MARTIN SALESMAN 7698 28-09-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-05-81 2850 30
7782 CLARK MANAGER 7839 09-06-81 2450 10
7788 SCOTT ANALYST 7566 09-12-82 3000 20
7839 KING PRESIDENT 17-11-81 5000 10
7844 TURNER SALESMAN 7698 08-09-81 1500 30
7876 ADAMS CLERK 7788 12-01-83 1100 20
7900 JAMES CLERK 7698 03-12-81 950 30
7902 FORD ANALYST 7566 03-12-81 3000 20
7934 MILLER CLERK 7782 23-01-82 1300 10
14 records selected.
SQL> SELECT * FROM SALGRADE ORDER BY GRADE;
GRADE LOSAL HISAL
---------------------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
5 records selected.
Definitii de tabele:
Aplicatia Bunuri sportive
CREATE TABLE ORD (
ORDID NUMBER (4) NOT NULL,
ORDERDATE DATE,
COMMPLAN CHAR (1),
CUSTID NUMBER (6) NOT NULL,
SHIPDATE DATE,
TOTAL NUMBER (8,2) CHECK (TOTAL=0) CONSTRAINT TOTAL_ZERO,
MAXLINE NUMBER (3) FOREIGN KEY (CUSTID) REFERENCES CUSTOMER (CUSTID)
CONSTRAINT ORD_FOREIGN_KEY, PRIMARY KEY (ORDID) CONSTRAINT ORD_PRIMARY_KEY);
CREATE TABLE ITEM (
ORDID NUMBER (4) NOT NULL,
ITEMID NUMBER (4) NOT NULL,
PRODID NUMBER (6),
ACTUALPRICE NUMBER (8,2),
QTY NUMBER (8),
ITEMOT NUMBER (8,2),
FOREIGN KEY (ORDID) REFERENCES ORD (ORDID) CONSTRAINT ITEM_FOREIGN_KEY,
PRIMARY KEY (ORDID, ITEMID) CONSTRAINT ITEM_PRIMARY_KEY);
CREATE TABLE CUS (
CUSTID NUMBER (6) NOT NULL,
NAME CHAR (45),
ADDRESS CHAR (40),
TOWN CHAR (30),
COUNTY CHAR (15),
PCODE CHAR (9),
AREA NUMBER (3),
PHONE CHAR (9),
REPID NUMBER (4) NOT NULL,
CREDITLIMIT NUMBER (9, 2),
COMMENTS LONG,
PRIMARY KEY (CUSTID) CONSTRAINT CUSTOMER_PRIMARY_KEY, CHECK (CUSTID = 0)
CONSTRAINT CUSTID_ZERO);
CREATE TABLE PRODUCT (
PRODID NUMBER (6) PRIMARY KEY CONSTRAINT PRODUCT_PRIMARY_KEY,
DESCRIP CHAR (30));
CREATE TABLE PRICE (
PRODID NUMBER (6) NOT NULL,
STDPRICE NUMBER (8,2),
MINPRICE NUMBER (8,2),
STARTDATE DATE,
ENDDATE DATE);
CREATE TABLE SEQNOS (
TABLE_NAME CHAR (15);
MAXSEQNO NUMBER (5));
Listingurile tabelelor Bunuri sportive:
SQL> SELECT * FROM ORD ORDER BY ORDID
ORDID ORDERDATE CPLAN CUSTID SHIPDATE TOTAL MAXLINE
-----------------------------------------------------------------------
601 01-05 A 106 30-05-86 2.4 1
602 05-06 B 102 20-06-86 56 1
603 05-06 102 05-06-86 224 1
604 15-06 A 106 30-06-86 698 3
605 14-07 A 106 30-07-86 8324 6
606 14-07 A 103 30-070-86 3.4 1
607 18-07 C 104 18-07-86 5.6 1
608 25-07 C 104 25-07-86 35.2 2
609 01-08 B 100 15-08-86 97.5 3
610 07-07 A 101 08-01-87 101.4 3
611 11-01 B 102 11-01-87 45 1
612 15-01 C 104 20-01-87 5860 4
613 01-02 108 01-02-87 6400 4
614 01-02 102 05-02-87 23940 3
615 01-02 107 06-02-87 710 3
616 03-02 103 10-02-87 764 5
617 05-02 105 03-03-87 46370 10
618 15-02 A 102 06-03-87 3510.5 3
619 22-02 104 04-02-87 1260 4
620 12-03 100 12-03-87 4450 3
621 15-03 A 100 01-01-87 730 2
21 records selected.
SQL> SELECT * FROM ITEM ORDER BY ORDID, ITEMID;
ORDID ITEMID PRODID PRICE QTY ITEMTOT
-----------------------------------------------
601 1 200376 2.40 1 240
602 1 100870 2.80 20 56.00
603 2 100860 56.00 4 224.00
604 1 100890 58.00 3 174.00
604 2 100861 42.00 2 84.00
604 3 100860 44.00 10 440.00
605 1 100861 45.00 100 4500.00
605 2 100870 2.80 500 1400.00
605 3 100890 58.00 5 290.00
605 4 101860 24.00 50 1200.00
605 5 101863 9.00 100 900.00
605 6 102130 3.40 10 34.00
606 1 102130 3.40 1 3.40
607 1 100871 5.60 1 5.60
608 1 101860 24.00 1 24.00
608 2 100871 5.60 2 11.20
609 1 100861 35.00 1 35.00
609 2 100870 2.50 5 12.50
609 3 100890 50.00 1 50.00
610 1 100860 35.00 1 35.00
610 2 100870 2.80 3 8.40
610 3 100890 58.00 1 58.00
611 1 100861 45.00 1 45.00
612 1 100860 30.00 100 3000.00
612 2 100861 40.50 20 810.00
612 3 101863 10.00 150 1500.00
612 4 100871 5.50 100 550.00
613 1 100871 5.60 100 560.00
613 2 101860 24.00 200 4800.00
613 3 200380 4.00 150 600.00
613 4 200376 2.20 200 440.00
614 1 100860 35.00 444 15540.00
614 2 100870 2.80 1000 2800.00
614 3 100871 5.60 1000 5600.00
615 1 100861 45.00 4 180.00
615 2 100870 2.80 100 280.00
615 3 100871 5.00 50 250.00
616 1 100861 45.00 10 450.00
616 2 100870 2.80 50 140.00
616 3 100890 58.00 2 116.00
616 4 102130 3.40 10 34.00
616 5 200376 2.40 10 24.00
617 1 100860 35.00 50 1750.00
617 2 100861 45.00 100 4500.00
617 3 100870 2.80 500 1400.00
617 4 100871 5.60 500 2800.00
617 5 100890 58.00 500 29000.00
617 6 101860 24.00 100 2400.00
617 7 101863 12.50 200 2500.00
617 8 102130 3.40 100 340.00
617 9 200376 2.40 200 480.00
617 10 200380 4.00 300 1200.00
618 1 100860 35.00 23 805.00
618 2 100861 45.11 50 2255.50
618 3 100870 45.00 10 450.00
619 1 200380 4.00 100 400.00
619 2 200376 2.40 100 240.00
619 3 102130 3.40 100 340.00
619 4 100871 5.60 50 280.00
620 1 100860 35.00 10 350.00
620 2 200376 2.40 1000 2400.00
620 3 102130 3.40 500 1700.00
621 1 100861 45.00 10 450.00
621 2 100870 2.80 100 280.00
64 records selected.
SQL> SELECT * FROM SEQNOS;
TABLE_NAME MAXSEQNO
------------------------
CUS 111
ORD 621
PRODUCT 200380
3 records selected.
SQL> SELECT * FROM PRODUCT ORDER BY PRODID;
PRODID DESCRIP
-------------------------------------------
100860 ACE TENNIS RACKET I
100861 ACE TENNIS RACKET II
100870 ACE TENNIS BALLS - 3 PACK
100871 ACE TENNIS BALLS - 6 PACK
100890 ACE TENNIS NET
101860 SP TENNIS RACKET
101863 SP JUNIOR RACKET
102130 RH: "GUIDE TO TENNIS"
200376 SB ENERGY BAR - 6 PACK
200380 SB VITA SNACK - 6 PACK
10 records selected.
SQL> SELECT * FROM PRICE ORDER BY PRODID;
PRODID PRICE MINPRC STARTDATE ENDDATE
-------------------------------------------------
100860 30.00 24.00 01-01-85 31-12-85
100860 32.00 25.60 01-01-86 31-05-86
100860 35.00 28.00 01-06-86
100861 30.00 24.00 01-01-85 31-12-85
100861 32.00 25.60 01-01-86 31-05-86
100861 35.00 28.00 01-06-86
100870 30.00 24.00 01-01-85 31-12-85
100870 35.00 28.00 01-06-86
100871 30.00 24.00 01-01-85 31-12-85
100871 35.00 28.00 01-06-86
100890 30.00 24.00 01-01-85 31-12-85
100890 35.00 28.00 01-06-86
101860 24.00 18.00 15-02-85
101863 12.50 18.00 15-02-85
17 records selected.
SQL> SELECT * FROM DUMMY;
DUMMY
--------
0
1 records selected.