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.