DEP
---
100
200
300
DEP
---
300
200
100
(select dept_num, dept_name
*
ERROR at line 1:
ORA-01789: query block has incorrect number of result columns
(select dept_num
*
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression
DEPT
----
300
7566
7698
7782
JOB_TITLE MGR
---------- ----
Clerk 7698
Clerk 7782
Clerk 7788
Clerk 7902
Salesman 7698
EMPL_LAST_NAME SALARY+COMMISSION
--------------- -----------------
King
Jones
Blake
Raimi
Ford
Smith
Michaels 1900
Ward 1750
Martin 2650
Scott
Turner 1500
EMPL_LAST_NAME SALARY+COMMISSION
--------------- -----------------
Adams
James
Miller
14 rows selected.
Employee Total Compensation
--------------- ------------------
Smith 800
James 950
Adams 1100
Miller 1300
Turner 1500
Ward 1750
Michaels 1900
Raimi 2450
Martin 2650
Blake 2850
Jones 2975
Employee Total Compensation
--------------- ------------------
Ford 3000
Scott 3000
King 5000
14 rows selected.
insert into dept
*
ERROR at line 1:
ORA-00001: unique constraint (ST10.SYS_C0014401) violated
DEPT_NAME COUNT(*)
--------------- ----------
Research 4
Accounting 2
Management 1
Sales 6
Operations 1
DEPT_NAME COUNT(*)
--------------- ----------
Research 4
Accounting 2
Management 1
Sales 6
Operations 1
DEPT_NAME # of Employees
--------------- --------------
Sales 6
Research 4
Accounting 2
Management 1
Operations 1
Computer 0
6 rows selected.
DEPT_NAME # of Employees
--------------- --------------
Sales 6
Research 4
Accounting 2
Management 1
Operations 1
Computer 0
6 rows selected.
EMPL_LAST_NAME DEP HIREDATE
--------------- --- ---------
Adams 400 23-SEP-91
Ford 200 03-DEC-91
Ford 200 03-DEC-91
James 300 03-DEC-91
Jones 200 02-APR-91
King 500 17-NOV-91
Martin 300 28-SEP-91
Miller 100 23-JAN-92
Scott 200 09-NOV-91
Scott 200 09-NOV-91
Smith 200 17-DEC-90
EMPL_LAST_NAME DEP HIREDATE
--------------- --- ---------
Turner 300 08-SEP-91
12 rows selected.
EMPL_LAST_NAME DEP HIREDATE
--------------- --- ---------
Adams 400 23-SEP-91
Ford 200 03-DEC-91
Ford 200 03-DEC-91
James 300 03-DEC-91
Jones 200 02-APR-91
King 500 17-NOV-91
Martin 300 28-SEP-91
Miller 100 23-JAN-92
Scott 200 09-NOV-91
Scott 200 09-NOV-91
Smith 200 17-DEC-90
EMPL_LAST_NAME DEP HIREDATE
--------------- --- ---------
Turner 300 08-SEP-91
12 rows selected.
EMPL_LAST_NAME DEP HIREDATE
--------------- --- ---------
Adams 400 23-SEP-91
Ford 200 03-DEC-91
James 300 03-DEC-91
Jones 200 02-APR-91
King 500 17-NOV-91
Martin 300 28-SEP-91
Miller 100 23-JAN-92
Scott 200 09-NOV-91
Smith 200 17-DEC-90
Turner 300 08-SEP-91
10 rows selected.
EMPL_LAST_NAME DEP HIREDATE
--------------- --- ---------
Adams 400 23-SEP-91
Ford 200 03-DEC-91
James 300 03-DEC-91
Jones 200 02-APR-91
King 500 17-NOV-91
Martin 300 28-SEP-91
Miller 100 23-JAN-92
Scott 200 09-NOV-91
Smith 200 17-DEC-90
Turner 300 08-SEP-91
10 rows selected.
EMPL_LAST_NAME DEP HIREDATE
--------------- --- ---------
Ford 200 03-DEC-91
Scott 200 09-NOV-91
EMPL_LAST_NAME DEP HIREDATE
--------------- --- ---------
Ford 200 03-DEC-91
Scott 200 09-NOV-91
EMPL_LAST_NAME DEP HIREDATE
--------------- --- ---------
Adams 400 23-SEP-91
James 300 03-DEC-91
King 500 17-NOV-91
Martin 300 28-SEP-91
Miller 100 23-JAN-92
Turner 300 08-SEP-91
6 rows selected.
EMPL_LAST_NAME DEP HIREDATE
--------------- --- ---------
Adams 400 23-SEP-91
James 300 03-DEC-91
King 500 17-NOV-91
Martin 300 28-SEP-91
Miller 100 23-JAN-92
Turner 300 08-SEP-91
6 rows selected.
EMPL_LAST_NAME DEP HIREDATE
--------------- --- ---------
Jones 200 02-APR-91
Smith 200 17-DEC-90
EMPL_LAST_NAME DEP HIREDATE
--------------- --- ---------
Jones 200 02-APR-91
Smith 200 17-DEC-90
DEPT_NAME
---------------
Computer
DEPT_NAME
---------------
Computer
EMPL_LAST_NAME
---------------
Smith
James
Adams
Martin
Ward
Miller
Turner
Michaels
Raimi
Blake
Jones
EMPL_LAST_NAME
---------------
Scott
Ford
King
14 rows selected.
order by salary
*
ERROR at line 9:
ORA-00904: "SALARY": invalid identifier
order by salary
*
ERROR at line 8:
ORA-00904: "SALARY": invalid identifier
Table dropped.
Table created.
1 row created.
('10602', '5/8 in lug nut from Argentina or Brazil', 16, 4.50, '105',
*
ERROR at line 3:
ORA-12899: value too large for column "ST10"."PARTS"."PART_NAME" (actual: 39,
maximum: 25)
('10602', '5/8 in lug nut', 16, 10000.00, '105',
*
ERROR at line 3:
ORA-01438: value larger than specified precision allowed for this column
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
the contents of the parts table:
PART_ PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
----- ------------------------- ---------------- ---------- --- ---------
10601 3/8 in lug nut 1000 .02 002 09-SEP-02
10603 hexagonal wrench 13 9.99 003 05-SEP-00
10604 tire 287 39.99 333 06-SEP-00
10605 hammer 30 9.99 003 01-SEP-00
10606 3/8 in bolt 5000 .03 005 04-SEP-00
10607 7/8 in bolt 2655 .04 005 02-SEP-00
6 rows selected.
1 row updated.
is 10604's price now 66.66?
PART_ PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
----- ------------------------- ---------------- ---------- --- ---------
10601 3/8 in lug nut 1000 .02 002 09-SEP-02
10603 hexagonal wrench 13 9.99 003 05-SEP-00
10604 tire 287 66.66 333 06-SEP-00
10605 hammer 30 9.99 003 01-SEP-00
10606 3/8 in bolt 5000 .03 005 04-SEP-00
10607 7/8 in bolt 2655 .04 005 02-SEP-00
6 rows selected.
2 rows updated.
note that BOTH wrench AND hammer have price of 9.99,
and now both indeed have quantity_on_hand of 0
PART_ PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
----- ------------------------- ---------------- ---------- --- ---------
10601 3/8 in lug nut 1000 .02 002 09-SEP-02
10603 hexagonal wrench 0 9.99 003 05-SEP-00
10604 tire 287 66.66 333 06-SEP-00
10605 hammer 0 9.99 003 01-SEP-00
10606 3/8 in bolt 5000 .03 005 04-SEP-00
10607 7/8 in bolt 2655 .04 005 02-SEP-00
6 rows selected.
6 rows updated.
PART_ PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
----- ------------------------- ---------------- ---------- --- ---------
10601 3/8 in lug nut 1000 .02 002 27-OCT-10
10603 hexagonal wrench 0 9.99 003 27-OCT-10
10604 tire 287 66.66 333 27-OCT-10
10605 hammer 0 9.99 003 27-OCT-10
10606 3/8 in bolt 5000 .03 005 27-OCT-10
10607 7/8 in bolt 2655 .04 005 27-OCT-10
6 rows selected.
4 rows updated.
1 row deleted.
is 10604, which had price 66.66, now deleted?
PART_ PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
----- ------------------------- ---------------- ---------- --- ---------
10601 3/8 in lug nut 1000 .02 002 23-JAN-92
10603 hexagonal wrench 0 9.99 003 23-JAN-92
10605 hammer 0 9.99 003 23-JAN-92
10606 3/8 in bolt 5000 .03 005 27-OCT-10
10607 7/8 in bolt 2655 .04 005 27-OCT-10
2 rows deleted.
are both 10606 and 10607, which both had level_code 005, now deleted?
PART_ PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
----- ------------------------- ---------------- ---------- --- ---------
10601 3/8 in lug nut 1000 .02 002 23-JAN-92
10603 hexagonal wrench 0 9.99 003 23-JAN-92
10605 hammer 0 9.99 003 23-JAN-92
3 rows deleted.
no rows selected
0 rows deleted.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
the contents of the parts table:
PART_ PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
----- ------------------------- ---------------- ---------- --- ---------
10601 3/8 in lug nut 1000 .02 002 09-SEP-02
10603 hexagonal wrench 13 9.99 003 05-SEP-00
10604 tire 287 39.99 333 06-SEP-00
10605 hammer 30 9.99 003 01-SEP-00
10606 3/8 in bolt 5000 .03 005 04-SEP-00
10607 7/8 in bolt 2655 .04 005 02-SEP-00
6 rows selected.
2 rows deleted.
PART_ PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
----- ------------------------- ---------------- ---------- --- ---------
10601 3/8 in lug nut 1000 .02 002 09-SEP-02
10603 hexagonal wrench 13 9.99 003 05-SEP-00
10604 tire 287 39.99 333 06-SEP-00
10605 hammer 30 9.99 003 01-SEP-00
Table dropped.
Table created.
1 row created.
insert into orders
*
ERROR at line 1:
ORA-02291: integrity constraint (ST10.SYS_C0018014) violated - parent key not
found
delete from parts
*
ERROR at line 1:
ORA-02292: integrity constraint (ST10.SYS_C0018014) violated - child record
found
update parts
*
ERROR at line 1:
ORA-02292: integrity constraint (ST10.SYS_C0018014) violated - child record
found
Table dropped.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
('555555', '44444444', '10601', '3-Mar-98', NULL, 'G', 'U')
*
ERROR at line 3:
ORA-01400: cannot insert NULL into ("ST10"."ORDERS"."QUANTITY")
insert into orders
*
ERROR at line 1:
ORA-02290: check constraint (ST10.SYS_C0018019) violated
insert into orders(order_num, part_num, delivery_code)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("ST10"."ORDERS"."CUST_NUM")
insert into orders
*
ERROR at line 1:
ORA-02291: integrity constraint (ST10.SYS_C0018022) violated - parent key not
found
ORDER_ CUST_NUM PART_ ORDER_DAT QUANTITY O D
------ -------- ----- --------- ---------- - -
111111 11111111 10601 01-FEB-00 6 B U
333333 33333333 10601 01-FEB-00 8 F
222222 22222222 10605 01-JAN-00 4 P
444444 22222222 10601 01-FEB-00 1 U
Table altered.
Name Null? Type
----------------------------------------- -------- ----------------------------
PART_NUM NOT NULL CHAR(5)
PART_NAME VARCHAR2(25)
QUANTITY_ON_HAND NUMBER(38)
PRICE NUMBER(6,2)
LEVEL_CODE CHAR(3)
LAST_INSPECTED DATE
SUPPLIER VARCHAR2(20)
PART_ PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
----- ------------------------- ---------------- ---------- --- ---------
SUPPLIER
--------------------
10601 3/8 in lug nut 1000 .02 002 09-SEP-02
10603 hexagonal wrench 13 9.99 003 05-SEP-00
10604 tire 287 39.99 333 06-SEP-00
PART_ PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
----- ------------------------- ---------------- ---------- --- ---------
SUPPLIER
--------------------
10605 hammer 30 9.99 003 01-SEP-00
2 rows updated.
PART_ PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
----- ------------------------- ---------------- ---------- --- ---------
SUPPLIER
--------------------
10601 3/8 in lug nut 1000 .02 002 09-SEP-02
10603 hexagonal wrench 13 9.99 003 05-SEP-00
Acme
10604 tire 287 39.99 333 06-SEP-00
Acme
PART_ PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
----- ------------------------- ---------------- ---------- --- ---------
SUPPLIER
--------------------
10605 hammer 30 9.99 003 01-SEP-00
4 rows updated.
PART_ PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
----- ------------------------- ---------------- ---------- --- ---------
SUPPLIER
--------------------
10601 3/8 in lug nut 1000 .02 002 09-SEP-02
Acme
10603 hexagonal wrench 13 9.99 003 05-SEP-00
Acme
10604 tire 287 39.99 333 06-SEP-00
Acme
PART_ PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
----- ------------------------- ---------------- ---------- --- ---------
SUPPLIER
--------------------
10605 hammer 30 9.99 003 01-SEP-00
Acme
Table altered.
PART_ PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
----- ------------------------- ---------------- ---------- --- ---------
SUPPLIER WHATSIT
-------------------- --------------------
10601 3/8 in lug nut 1000 .02 002 09-SEP-02
Acme hi
10603 hexagonal wrench 13 9.99 003 05-SEP-00
Acme hi
10604 tire 287 39.99 333 06-SEP-00
Acme hi
PART_ PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
----- ------------------------- ---------------- ---------- --- ---------
SUPPLIER WHATSIT
-------------------- --------------------
10605 hammer 30 9.99 003 01-SEP-00
Acme hi
Table dropped.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
Table altered.
insert into orders
*
ERROR at line 1:
ORA-02291: integrity constraint (ST10.SYS_C0018025) violated - parent key not
found
Table altered.
Name Null? Type
----------------------------------------- -------- ----------------------------
CUST_NUM NOT NULL CHAR(8)
CUST_LNAME NOT NULL VARCHAR2(30)
CUST_FNAME VARCHAR2(20)
CUST_PHONE VARCHAR2(12)
Table altered.
(cust_fname varchar2(3))
*
ERROR at line 3:
ORA-01441: cannot decrease column length because some value is too big
Table dropped.
Table created.
Table dropped.
Table created.
Sequence dropped.
Sequence created.
1 row created.
1 row created.
1 row created.
PTR PTR_LNAME PTR_FNAME
--- ------------------------------ ---------------
102 Van Gogh Vincent
104 Monet Claude
106 Da Vinci Leonardo
1 row created.
PTG_TITLE PTR
------------------------------ ---
Mona Lisa 106
CURRVAL
----------
106
1 row created.
PART_ PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
----- ------------------------- ---------------- ---------- --- ---------
SUPPLIER WHATSIT
-------------------- --------------------
10614 stuff106 106 .13 005 27-OCT-10
Napa lo
10601 3/8 in lug nut 1000 .02 002 09-SEP-02
Acme hi
10603 hexagonal wrench 13 9.99 003 05-SEP-00
Acme hi
PART_ PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
----- ------------------------- ---------------- ---------- --- ---------
SUPPLIER WHATSIT
-------------------- --------------------
10604 tire 287 39.99 333 06-SEP-00
Acme hi
10605 hammer 30 9.99 003 01-SEP-00
Acme hi