SUBJECT: Exercises in SQL, Oracle
COURSE: MCTE 630 - Databases
Professor: Dr. Maxine Cohen
Student: Leanne C. Boyd
Usercode: boydl
Due date: April 1998
===============================================================
UNIX(r) System V Release 4.0 (scis)
===============================================================
SQL> select * from cohenm.customer
2 ;
CUSTOMER_NUMBER LAST FIRST STREET CITY ST
ZIP_CODE
--------------- ---------- ---------- --------------- ---------- -- ---
-------
BALANCE CREDIT_LIMIT SLSREP_NUMBER
---------- ------------ -------------
124 Adams Sally 481 Oak Lansing MI
49224
818.75 1000 3
256 Samuels Ann 215 Pete Grant MI
49219
21.5 1500 6
311 Charles Don 48 College Ira MI
49034
825.75 1000 12
CUSTOMER_NUMBER LAST FIRST STREET CITY ST
ZIP_CODE
--------------- ---------- ---------- --------------- ---------- -- ---
-------
BALANCE CREDIT_LIMIT SLSREP_NUMBER
---------- ------------ -------------
315 Daniels Tom 914 Cherry Kent MI
48391
770.75 750 6
405 Williams Al 519 Watson Grant MI
49219
402.75 1500 12
412 Adams Sally 16 Elm Lansing MI
49224
1817.5 2000 3
CUSTOMER_NUMBER LAST FIRST STREET CITY ST
ZIP_CODE
--------------- ---------- ---------- --------------- ---------- -- ---
-------
BALANCE CREDIT_LIMIT SLSREP_NUMBER
---------- ------------ -------------
567 Dinh Tran 808 Ridge Harper MI
48421
402.4 750 6
587 Galvez Mara 512 Pine Ada MI
49441
114.6 1000 6
622 Martin Dan 419 Chip Grant MI
49219
1045.75 1000 3
CUSTOMER_NUMBER LAST FIRST STREET CITY ST
ZIP_CODE
--------------- ---------- ---------- --------------- ---------- -- ---
-------
BALANCE CREDIT_LIMIT SLSREP_NUMBER
---------- ------------ -------------
522 Nelson Mary 108 Pine Ada MI
49441
98.75 1500 12
10 rows selected.
SQL> update cohenm.part
2 set part_description = 'BT04'
3 where
4
SQL> update cohenm.part
2 set part_description = 'Oven'
3 where part_number = 'BT04';
update cohenm.part
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> select * from part;
select * from part
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create table customer
2 insert into customer
3 (select * from cohenm.customer);
insert into customer
*
ERROR at line 2:
ORA-00906: missing left parenthesis
SQL> create table customer,
2 (insert into customer
3 (select * from cohenm.customer) );
create table customer,
*
ERROR at line 1:
ORA-00906: missing left parenthesis
SQL> create table customer
2 (insert into customer
3 (select * from cohenm.customer) );
(insert into customer
*
ERROR at line 2:
ORA-00904: invalid column name
SQL> help
HELP
HELP [topic] | help
HELP displays information on the commands and conventions of SQL*Plus,
SQL, and PL/SQL. Type "help", a space, all or part of any topic, and
then press Enter.
Typing only part of a topic (e.g., HELP SE) will cause all help topics
that match the string (SE) to be displayed on your screen in
alphabetical
order (e.g., HELP SE will retrieve help on the SELECT statement, the
SET
command, and the Set Operators--in that order).
ENTER THIS... TO DISPLAY THIS...
--------------------------------------------------------------
help menu the main menu of help topics
help commands a list of SQL*Plus, SQL, and PL/SQL commands
help comm information on commands, comments, and commit
help help this screen
=======================================================================
SESSION #2
SQL> DESCRIBE COHENM.CUSTOMER;
Name Null? Type
------------------------------- -------- ----
CUSTOMER_NUMBER NOT NULL NUMBER(3)
LAST VARCHAR2(10)
FIRST VARCHAR2(10)
STREET VARCHAR2(15)
CITY VARCHAR2(10)
STATE CHAR(2)
ZIP_CODE NUMBER(5)
BALANCE NUMBER(8,2)
CREDIT_LIMIT NUMBER(5)
SLSREP_NUMBER NUMBER(2)
SQL> create table boyd_cust
2 (CUSTOMER_NUMBER CHAR(3) NOT NULL
3 LAST CHAR(
4
SQL>
SQL> create table boyd_cust
2 (CUSTOMER_NUMBER VARCHAR2(10)
3 LAST
4
SQL>
SQL>
SQL>
SQL> create table boyd_cust
2 (CUSTOMER_NUMBER CHAR(3) NOT NULL
3 LAST VARCHAR2(10)
4 FIRST VARCHAR2(10)
5 STREET VARCHAR2(15)
6 CITY VARCHAR2(10)
7 STATE CHAR(2)
8 ZIP_CODE
9 ^[[D
10
SQL> create table boyd_cust
2 (CUSTOMER_NUMBER CHAR(3) NOT NULL
3 LAST VARCHAR2(10)
4 FIRST VARCHAR2(10)
5 STREET VARCHAR2(15)
6 CITY VARCHAR2(10)
7 STATE CHAR(2)
8 ZIP_CODE 2 3 4 5 6 7 8
9
SQL>
SQL> SQL>
SQL> create table boyd_cust(CUSTOMER_NUMBER CHAR(3) NOT NULL
2
SQL>
SQL>
SQL> create table boyd_cust
2 (CUSTOMER_NUMBER CHAR(3) NOT NULL
3 LAST VARCHAR2(10)
4 FIRST VARCHAR2(10)
5 STREET VARCHAR2(15)
6 CITY VARCHAR2(10)
7 STATE CHAR(2)
8 ZIP_CODE NUMBER(5)
9 BALANCE NUMBER(8,2)
10 CREDIT_LIMIT NUMBER(5)
11 SLSREP_NUMBER NUMBER(2);
(CUSTOMER_NUMBER CHAR(3) NOT NULL
*
ERROR at line 2:
ORA-00922: missing or invalid option
SQL> create table boyd_cust
2 (CUSTOMER_NUMBER CHAR(3)
3 LAST VARCHAR2(10)
4 FIRST VARCHAR2(10
5
SQL>
SQL>
SQL> create table boyd_cust
(CUSTOMER_NUMBER CHAR(3)
LAST VARCHAR2(10)
FIRST VARCHAR2(10)
STREET VARCHAR2(15)
CITY VARCHAR2(10)
STATE CHAR(2)
ZIP_CODE NUMBER(5)
BALANCE NUMBER(8,2)
CREDIT_LIMIT NUMBER(5)
SLSREP_NUMBER NUMBER(2) ); 2 3 4 5 6 7 8 9 10
11 ^R
SQL>
SQL> create table boyd_cust
2 (CUSTOMER_NUMBER CHAR(3)
3 LAST VARCHAR2(10)
4 FIRST VARCHAR2(10)
5 STREET VARCHAR2(15)
6 CITY VARCHAR2(10)
7 STATE CHAR(2)
8 ZIP_CODE NUMBER(5)
9 BALANCE NUMBER(8,2)
10 CREDIT_LIMIT NUMBER(5)
11 SLSREP_NUMBER NUMBER(2) );
(CUSTOMER_NUMBER CHAR(3)
*
ERROR at line 2:
ORA-00922: missing or invalid option
SQL> create table boyd_cust
2 (CUSTOMER_NUMBER CHAR(3),
3 LAST VARCHAR2(10),
4 FIRST VARCHAR2(10),
5 STREET VARCHAR2(15),
6 CITY VARCHAR2(10),
7 STATE CHAR(2),
8 ZIP_CODE NUMBER(5),
9 BALANCE NUMBER(8,2),
10 CREDIT_LIMIT NUMBER(5),
11 SLSREP_NUMBER NUMBER(2));
Table created.
SQL>
SQL>
SQL> INSERT INTO BOYD_CUST (SELECT * FROM COHENM.CUSTOMER);
10 rows created.
SQL> select * from boyd_cust
2 ;
CUS LAST FIRST STREET CITY ST ZIP_CODE
BALANCE
--- ---------- ---------- --------------- ---------- -- ---------- ----
------
CREDIT_LIMIT SLSREP_NUMBER
------------ -------------
124 Adams Sally 481 Oak Lansing MI 49224
818.75
1000 3
256 Samuels Ann 215 Pete Grant MI 49219
21.5
1500 6
311 Charles Don 48 College Ira MI 49034
825.75
1000 12
CUS LAST FIRST STREET CITY ST ZIP_CODE
BALANCE
--- ---------- ---------- --------------- ---------- -- ---------- ----
------
CREDIT_LIMIT SLSREP_NUMBER
------------ -------------
315 Daniels Tom 914 Cherry Kent MI 48391
770.75
750 6
405 Williams Al 519 Watson Grant MI 49219
402.75
1500 12
412 Adams Sally 16 Elm Lansing MI 49224
1817.5
2000 3
CUS LAST FIRST STREET CITY ST ZIP_CODE
BALANCE
--- ---------- ---------- --------------- ---------- -- ---------- ----
------
CREDIT_LIMIT SLSREP_NUMBER
------------ -------------
567 Dinh Tran 808 Ridge Harper MI 48421
402.4
750 6
587 Galvez Mara 512 Pine Ada MI 49441
114.6
1000 6
622 Martin Dan 419 Chip Grant MI 49219
1045.75
1000 3
CUS LAST FIRST STREET CITY ST ZIP_CODE
BALANCE
--- ---------- ---------- --------------- ---------- -- ---------- ----
------
CREDIT_LIMIT SLSREP_NUMBER
------------ -------------
522 Nelson Mary 108 Pine Ada MI 49441
98.75
1500 12
10 rows selected.
SQL> update credit_limit
2 set
3
SQL> update boyd_cust
2 set credit_limillimit
3
SQL>
SQL> exit
Disconnected from Oracle7 Server Release 7.1.6.2.0 - Production Release
With the distributed, replication and parallel query options
PL/SQL Release 2.1.6.2.0 - Production
boydl@scis > exit
boydl@scis > logout
H
UNIX(r) System V Release 4.0 (scis)
===============================================================
SESSION #3
SQL> update boyd_cust
2 set credit_limit = credit_limit + 100
3 where slsrep_number = '06';
4 rows updated.
SQL> select last, first, credit_limit, slsrep_number
2 from boyd_cust
3 where slsrep_number = '06';
LAST FIRST CREDIT_LIMIT SLSREP_NUMBER
---------- ---------- ------------ -------------
Samuels Ann 1700 6
Daniels Tom 950 6
Dinh Tran 950 6
Galvez Mara 1200 6
SQL> select last, first, credit_limit, slsrep_number
2 from cohenm.customer
3 where slsrep_number = '06';
LAST FIRST CREDIT_LIMIT SLSREP_NUMBER
---------- ---------- ------------ -------------
Samuels Ann 1500 6
Daniels Tom 750 6
Dinh Tran 750 6
Galvez Mara 1000 6
SQL> rollback;
Rollback complete.
SQL> update boyd_cust
2 set credit_limit = credit_limit + 100
3 where slsrep_number = '06';
4 rows updated.
SQL> select last, first, credit_limit, slsrep_number
2 from boyd_cust
3 where slsrep_number = '06';
LAST FIRST CREDIT_LIMIT SLSREP_NUMBER
---------- ---------- ------------ -------------
Samuels Ann 1600 6
Daniels Tom 850 6
Dinh Tran 850 6
Galvez Mara 1100 6
SQL> rollback
2 ;
Rollback complete.
SQL> create table boyd_orders
2 (
3
SQL> describe cohenm.orders;
Name Null? Type
------------------------------- -------- ----
ORDER_NUMBER NOT NULL NUMBER(5)
ORDER_DATE DATE
CUSTOMER_NUMBER NUMBER(3)
SQL> create table boyd_orders
2 (ORDER_NUMBER NUMBER(5),
3 ORDER_DATE DATE,
4 CUSTOMER_NUMBER NUMBER(3) );
Table created.
SQL> insert into boyd_orders (select * from cohenm.orders);
9 rows created.
SQL> select * from boyd_orders;
ORDER_NUMBER ORDER_DAT CUSTOMER_NUMBER
------------ --------- ---------------
12489 02-SEP-98 124
12491 02-SEP-98 311
12494 04-SEP-98 315
12495 04-SEP-98 256
12498 05-SEP-98 522
12500 05-SEP-98 124
12504 05-SEP-98 522
12600 06-SEP-98 311
12600 06-SEP-98 311
9 rows selected.
SQL> describe cohenm.orderline;
Name Null? Type
------------------------------- -------- ----
ORDER_NUMBER NUMBER(5)
PART_NUMBER CHAR(4)
NUMBER_ORDERED NUMBER(3)
QUOTED_PRICE NUMBER(8,2)
SQL> create table boyd_orderline
2 (ORDER_NUMBER NUMBER(5),
3 PART_NUMBER CHAR(4),
4 NUMBER_ORDERED NUMBER(3),
5 QUOTED_PRICE NUMBER(8,2) );
Table created.
SQL> select * from boyd_orderline;
no rows selected
SQL> insert into boyd_orderline (select * from cohenm.orderline);
9 rows created.
SQL> select * from boyd_orderline;
ORDER_NUMBER PART NUMBER_ORDERED QUOTED_PRICE
------------ ---- -------------- ------------
12489 AX12 11 21.95
12491 BT04 1 149.99
12491 BZ66 1 399.99
12494 CB03 4 279.99
12495 CX11 2 22.95
12498 AZ52 2 12.95
12498 BA74 4 24.95
12500 BT04 1 149.99
12504 CZ81 2 325.99
9 rows selected.
SQL> insert into boyd_orders
2 values
3 ('12600','06-SEP-98','311');
1 row created.
SQL> insert into boyd_orderline
2 values
3 ('12600','AX12',5,13.95);
1 row created.
SQL> insert into boyd_orderline
2 values
3 ('12600','BA74',3,4.50);
1 row created.
SQL> select * from boyd_orderline;
ORDER_NUMBER PART NUMBER_ORDERED QUOTED_PRICE
------------ ---- -------------- ------------
12489 AX12 11 21.95
12491 BT04 1 149.99
12491 BZ66 1 399.99
12494 CB03 4 279.99
12495 CX11 2 22.95
12498 AZ52 2 12.95
12498 BA74 4 24.95
12500 BT04 1 149.99
12504 CZ81 2 325.99
12600 AX12 5 13.95
12600 BA74 3 4.5
11 rows selected.
SQL> select * from boyd_orders;
ORDER_NUMBER ORDER_DAT CUSTOMER_NUMBER
------------ --------- ---------------
12489 02-SEP-98 124
12491 02-SEP-98 311
12494 04-SEP-98 315
12495 04-SEP-98 256
12498 05-SEP-98 522
12500 05-SEP-98 124
12504 05-SEP-98 522
12600 06-SEP-98 311
12600 06-SEP-98 311
12600 06-SEP-98 311
10 rows selected.
SQL>
SQL>
SQL> select * from cohenm.orders;
ORDER_NUMBER ORDER_DAT CUSTOMER_NUMBER
------------ --------- ---------------
12489 02-SEP-98 124
12491 02-SEP-98 311
12494 04-SEP-98 315
12495 04-SEP-98 256
12498 05-SEP-98 522
12500 05-SEP-98 124
12504 05-SEP-98 522
12600 06-SEP-98 311
12600 06-SEP-98 311
9 rows selected.
SQL> --end chap. 5 problem 3
SQL>
SQL> --Chapter 5, Problem #4
SQL>
SQL> select customer_number, last, first, balance,
2 slsrep_number
3 from boyd_cust;
CUS LAST FIRST BALANCE SLSREP_NUMBER
--- ---------- ---------- ---------- -------------
124 Adams Sally 818.75 3
256 Samuels Ann 21.5 6
311 Charles Don 825.75 12
315 Daniels Tom 770.75 6
405 Williams Al 402.75 12
412 Adams Sally 1817.5 3
567 Dinh Tran 402.4 6
587 Galvez Mara 114.6 6
622 Martin Dan 1045.75 3
522 Nelson Mary 98.75 12
10 rows selected.
SQL> select customer_number, last, first, balance,
2 slsrep_number
3 from boyd_cust
4 where slsrep_number = '12';
CUS LAST FIRST BALANCE SLSREP_NUMBER
--- ---------- ---------- ---------- -------------
311 Charles Don 825.75 12
405 Williams Al 402.75 12
522 Nelson Mary 98.75 12
SQL> delete boyd_cust
2 where balance = '0'
3 and slsrep_number = '12';
0 rows deleted.
SQL> --end problem #4
SQL>
SQL>
SQL> --Chapter 5, Problem #5
SQL>
SQL> select part_number, part_description, unit_price
2 from cohenm.part;
PART PART_DESCRIPTIO UNIT_PRICE
---- --------------- ----------
AX12 Iron 24.95
AZ52 Dartboard 12.95
BA74 Basketball 29.95
BH22 Cornpopper 24.95
BT04 Gas Stove 149.99
CA14 Griddle 39.99
CB03 Bike 299.99
CX11 Blender 22.95
CZ81 Treadmill 349.95
BZ66 Washer 399.99
10 rows selected.
SQL> describe part_number, part_description, unit_price
Illegal identifier.
SQL> from cohenm.part;
unknown command beginning "from cohen..." - rest of line ignored.
SQL> describe cohenm.part;
Name Null? Type
------------------------------- -------- ----
PART_NUMBER NOT NULL CHAR(4)
PART_DESCRIPTION VARCHAR2(15)
UNITS_ON_HAND NUMBER(3)
ITEM_CLASS CHAR(2)
WAREHOUSE_NUMBER NUMBER(1)
UNIT_PRICE NUMBER(8,2)
SQL> create table SPGOODS
2 (PART_NUMBER CHAR(4),
3 PART_DESCRIPTION VARCHAR2(15)
4 UNIT_PRICE NUMBER(
5
SQL>
SQL> CREATE^H^H
2
SQL> create table SPGOODS
2 (part_number char(4)
3 part
4
SQL>
SQL> create table SPGOODS
2 (part_number char(4),
3 part_description
4
SQL> create table SPGOODS
2 (PART_NUMBER CHAR(4),
3 PART_DESCRIPTION CHAR(15),
4 UNIT_PRICE NUMBER(8,2) );
Table created.
SQL> insert into SPGOODS
2 select cohenm.part_number, cohenm.part_description,
3 cohenm.unit_price
4 from cohenm.part
5 where item_class = 'SG';
cohenm.unit_price
*
ERROR at line 3:
ORA-00904: invalid column name
SQL> insert into SPGOODS
2 select part_number, part_description, unit_price
3 from cohenm.part
4 where item_class = 'SG';
4 rows created.
SQL> select * from SPGOODS;
PART PART_DESCRIPTIO UNIT_PRICE
---- --------------- ----------
AZ52 Dartboard 12.95
BA74 Basketball 29.95
CB03 Bike 299.99
CZ81 Treadmill 349.95
SQL> --end problem #5
SQL>
SQL> --Chapter 5, Problem #6
SQL>
SQL> describe cohenm.sales_rep;
Object does not exist.
SQL> describe cohenm.salesrep;
Name Null? Type
------------------------------- -------- ----
SLSREP_NUMBER NOT NULL NUMBER(2)
LAST VARCHAR2(10)
FIRST VARCHAR2(10)
STREET VARCHAR2(15)
CITY VARCHAR2(10)
STATE CHAR(2)
ZIP_CODE NUMBER(5)
TOTAL_COMMISSION NUMBER(8,2)
COMMISSION_RATE NUMBER(5,2)
SQL> create table boyd_salesrep
2 (SLSREP_NUMBER NUMBER(2) NOT NULL,
3 LAST VARCHAR2(10),
4 FIRST VARCHAR2(10),
5 STREET VARCHAR2(15),
6 CITY VARCHAR2(10),
7 STATE CHAR(2),
8 ZIP_CODE NUMBER(5),
9 TOTAL_COMMISSION NUMBER(8,2),
10 COMMISSION_RATE NUMBER(5,2) );
Table created.
SQL> select * from boyd_salesrep;
no rows selected
SQL> insert into boyd_salesrep (select * from cohenm.salesrep);
3 rows created.
SQL> select * from boyd_salesrep;
SLSREP_NUMBER LAST FIRST STREET CITY ST
ZIP_CODE
------------- ---------- ---------- --------------- ---------- -- -----
-----
TOTAL_COMMISSION COMMISSION_RATE
---------------- ---------------
3 Jones Mary 123 Main Grant MI
49219
2150 .05
6 Smith William 102 Raymond Ada MI
49441
4912.5 .07
12 Diaz Miguel 419 Harper Lansing MI
49224
2150 .05
SQL> update boyd_cust
2 set street = NULL
3 where
4
SQL> update boyd_salesrep
2 set street = NULL
3 where slsrep_number ='03';
1 row updated.
SQL> --Check this.
SQL>
SQL> select slsrep_number, street
2 from boyd_salesrep
3 where slsrep_number ='03';
SLSREP_NUMBER STREET
------------- ---------------
3
SQL> --end problem #6
SQL>
SQL> --Chapter 5, Problem #7
SQL>
SQL> rollback;
Rollback complete.
SQL> rollback;
Rollback complete.
SQL> select * from boyd_salesrep;
no rows selected
SQL> insert into boyd_salesrep (select * from cohenm.salesrep);
3 rows created.
SQL> describe cohenm.part;
Name Null? Type
------------------------------- -------- ----
PART_NUMBER NOT NULL CHAR(4)
PART_DESCRIPTION VARCHAR2(15)
UNITS_ON_HAND NUMBER(3)
ITEM_CLASS CHAR(2)
WAREHOUSE_NUMBER NUMBER(1)
UNIT_PRICE NUMBER(8,2)
SQL> create table boyd_part
2 (PART_NUMBER CHAR(4) NOT NULL,
3 PART_DESCRIPTION VARCHAR2(15),
4 UNITS_ON_HAND NUMBER(3),
5 ITEM_CLASS CHAR(2),
6 WAREHOUSE_NUMBER NUMBER(1),
7 UNIT_PRICE NUMBER(8,2) );
Table created.
SQL> insert into boyd_part (select * from cohenm.part);
10 rows created.
SQL> select * from boyd_part
2 ;
PART PART_DESCRIPTIO UNITS_ON_HAND IT WAREHOUSE_NUMBER UNIT_PRICE
---- --------------- ------------- -- ---------------- ----------
AX12 Iron 104 HW 3 24.95
AZ52 Dartboard 20 SG 2 12.95
BA74 Basketball 40 SG 1 29.95
BH22 Cornpopper 95 HW 3 24.95
BT04 Gas Stove 11 AP 2 149.99
CA14 Griddle 78 HW 3 39.99
CB03 Bike 44 SG 1 299.99
CX11 Blender 112 HW 3 22.95
CZ81 Treadmill 68 SG 2 349.95
BZ66 Washer 52 AP 3 399.99
10 rows selected.
SQL> --note: go back and re-do #1, now that I have table:
SQL>
SQL> update boyd_part
2 set part_description = 'Oven'
3 where part_number = 'BT04';
1 row updated.
SQL> rollback;
Rollback complete.
SQL> update boyd_part
2 set part_descritip
3
SQL> alter table boyd_part
2 add ALLOCATION NUMBER(3,0);
Table altered.
SQL> update boyd_part
2 set ALLOCATION = 0;
0 rows updated.
SQL> select sum(number_ordered)
2 from boyd_orderline
3 where part_number = 'BT04';
SUM(NUMBER_ORDERED)
-------------------
2
SQL> update boyd_part
2 set allocation = 2
3 where part_number = 'BT04';
0 rows updated.
SQL> update boyd_part
2 set ALLOCATION = 2
3 where part_number = 'BT04';
0 rows updated.
SQL> select * from boyd_part
2 ;
no rows selected
SQL> insert into boyd_part (select * from cohenm.part);
insert into boyd_part (select * from cohenm.part)
*
ERROR at line 1:
ORA-00947: not enough values
SQL> SQL> create table boyd_part
2 (PART_NUMBER CHAR(4) NOT NULL,
3 PART_DESCRIPTION VARCHAR2(15),
4 UNITS_ON_HAND NUMBER(3),
5 ITEM_CLASS CHAR(2),
6 WAREHOUSE_NUMBER NUMBER(1),
7 UNIT_PRICE NUMBER(8,2) );unknown command beginning "SQL>
creat..." - rest o
f line ignored.
SQL> SQL> SQL> SQL> SQL> SQL>
SQL> create table boyd_part
2 (PART_NUMBER CHAR(4) NOT NULL,
3 PART_DESCRIPTION VARCHAR2(15),
4 UNITS_ON_HAND NUMBER(3),
5 ITEM_CLASS CHAR(2),
6 WAREHOUSE_NUMBER NUMBER(1),
7 UNIT_PRICE NUMBER(8,2) );
create table boyd_part
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> drop table boyd_part
2 ;
Table dropped.
SQL> create table boyd_part
2 (PART_NUMBER CHAR(4) NOT NULL,
3 PART_DESCRIPTION VARCHAR2(15),
4 UNITS_ON_HAND NUMBER(3),
5 ITEM_CLASS CHAR(2),
6 WAREHOUSE_NUMBER NUMBER(1),
7 UNIT_PRICE NUMBER(8,2) );
Table created.
SQL> alter table boyd_part
2 add ALLOCATION NUMBER(3,0);
Table altered.
SQL> update boyd_part
2 set ALLOCATION = 0;
0 rows updated.
SQL> insert into boyd_part (select * from cohenm.part);
insert into boyd_part (select * from cohenm.part)
*
ERROR at line 1:
ORA-00947: not enough values
SQL> select * from cohenm.part;
PART PART_DESCRIPTIO UNITS_ON_HAND IT WAREHOUSE_NUMBER UNIT_PRICE
---- --------------- ------------- -- ---------------- ----------
AX12 Iron 104 HW 3 24.95
AZ52 Dartboard 20 SG 2 12.95
BA74 Basketball 40 SG 1 29.95
BH22 Cornpopper 95 HW 3 24.95
BT04 Gas Stove 11 AP 2 149.99
CA14 Griddle 78 HW 3 39.99
CB03 Bike 44 SG 1 299.99
CX11 Blender 112 HW 3 22.95
CZ81 Treadmill 68 SG 2 349.95
BZ66 Washer 52 AP 3 399.99
10 rows selected.
SQL> describe coehnm.part;
Object does not exist.
SQL> describe cohenm.part;
Name Null? Type
------------------------------- -------- ----
PART_NUMBER NOT NULL CHAR(4)
PART_DESCRIPTION VARCHAR2(15)
UNITS_ON_HAND NUMBER(3)
ITEM_CLASS CHAR(2)
WAREHOUSE_NUMBER NUMBER(1)
UNIT_PRICE NUMBER(8,2)
SQL> describe boyd_part;
Name Null? Type
------------------------------- -------- ----
PART_NUMBER NOT NULL CHAR(4)
PART_DESCRIPTION VARCHAR2(15)
UNITS_ON_HAND NUMBER(3)
ITEM_CLASS CHAR(2)
WAREHOUSE_NUMBER NUMBER(1)
UNIT_PRICE NUMBER(8,2)
ALLOCATION NUMBER(3)
SQL> drop table boyd_part;
Table dropped.
SQL> create table boyd_part
2 (PART_NUMBER CHAR(4) NOT NULL,
3 UNITS_ON_HAND NUMBER(3),
4 ITEM_CLASS CHAR(2),
5 WAREHOUSE_NUMBER NUMBER(1),
6 UNIT_PRICE NUMBER(8,2) );
Table created.
SQL> describe boyd_part;
Name Null? Type
------------------------------- -------- ----
PART_NUMBER NOT NULL CHAR(4)
UNITS_ON_HAND NUMBER(3)
ITEM_CLASS CHAR(2)
WAREHOUSE_NUMBER NUMBER(1)
UNIT_PRICE NUMBER(8,2)
SQL> insert into boyd_part (select * from cohenm.part);
insert into boyd_part (select * from cohenm.part)
*
ERROR at line 1:
ORA-00913: too many values
SQL> create table boyd_part
2 ((PART_NUMBER CHAR(4) NOT NULL,
3
SQL> create table boyd_part
2 (PART_NUMBER CHAR(4) NOT NULL,
3 PART_DESCRIPTION VARCHAR2(15),
4 UNITS_ON_HAND NUMBER(3),
5
SQL>
SQL> drop table boyd_part;
Table dropped.
SQL> create table boyd_part
2 (PART_NUMBER CHAR(4) NOT NULL,
3 PART_DESCRIPTION VARCHAR2(15),
4 UNITS_ON_HAND NUMBER(3),
5 ITEM_CLASS CHAR(2),
6 WAREHOUSE_NUMBER NUMBER(1),
7 UNIT_PRICE NUMBER(8,2) );
Table created.
SQL> insert into boyd_part (select * from cohenm.part);
10 rows created.
SQL> select * from boyd_part;
PART PART_DESCRIPTIO UNITS_ON_HAND IT WAREHOUSE_NUMBER UNIT_PRICE
---- --------------- ------------- -- ---------------- ----------
AX12 Iron 104 HW 3 24.95
AZ52 Dartboard 20 SG 2 12.95
BA74 Basketball 40 SG 1 29.95
BH22 Cornpopper 95 HW 3 24.95
BT04 Gas Stove 11 AP 2 149.99
CA14 Griddle 78 HW 3 39.99
CB03 Bike 44 SG 1 299.99
CX11 Blender 112 HW 3 22.95
CZ81 Treadmill 68 SG 2 349.95
BZ66 Washer 52 AP 3 399.99
10 rows selected.
SQL> alter table boyd_part
2 add ALLOCATION number(3,0);
Table altered.
SQL> select * from boyd_part;
PART PART_DESCRIPTIO UNITS_ON_HAND IT WAREHOUSE_NUMBER UNIT_PRICE
ALLOCATION
---- --------------- ------------- -- ---------------- ---------- -----
-----
AX12 Iron 104 HW 3 24.95
AZ52 Dartboard 20 SG 2 12.95
BA74 Basketball 40 SG 1 29.95
BH22 Cornpopper 95 HW 3 24.95
BT04 Gas Stove 11 AP 2 149.99
CA14 Griddle 78 HW 3 39.99
CB03 Bike 44 SG 1 299.99
CX11 Blender 112 HW 3 22.95
CZ81 Treadmill 68 SG 2 349.95
BZ66 Washer 52 AP 3 399.99
10 rows selected.
SQL> update boyd_part
2 set ALLOCATION = 0;
10 rows updated.
SQL> select * from boyd_part;
PART PART_DESCRIPTIO UNITS_ON_HAND IT WAREHOUSE_NUMBER UNIT_PRICE
ALLOCATION
---- --------------- ------------- -- ---------------- ---------- -----
-----
AX12 Iron 104 HW 3 24.95
0
AZ52 Dartboard 20 SG 2 12.95
0
BA74 Basketball 40 SG 1 29.95
0
BH22 Cornpopper 95 HW 3 24.95
0
BT04 Gas Stove 11 AP 2 149.99
0
CA14 Griddle 78 HW 3 39.99
0
CB03 Bike 44 SG 1 299.99
0
CX11 Blender 112 HW 3 22.95
0
CZ81 Treadmill 68 SG 2 349.95
0
BZ66 Washer 52 AP 3 399.99
0
10 rows selected.
SQL> select sum(number_ordered)
2 from boyd_orderline
3 where part_number = 'BT04';
SUM(NUMBER_ORDERED)
-------------------
2
SQL> update boyd_part
2 set ALLOCATION = 2
3 where part_number = 'BT04';
1 row updated.
SQL> select * from boyd_part;
PART PART_DESCRIPTIO UNITS_ON_HAND IT WAREHOUSE_NUMBER UNIT_PRICE
ALLOCATION
---- --------------- ------------- -- ---------------- ---------- -----
-----
AX12 Iron 104 HW 3 24.95
0
AZ52 Dartboard 20 SG 2 12.95
0
BA74 Basketball 40 SG 1 29.95
0
BH22 Cornpopper 95 HW 3 24.95
0
BT04 Gas Stove 11 AP 2 149.99
2
CA14 Griddle 78 HW 3 39.99
0
CB03 Bike 44 SG 1 299.99
0
CX11 Blender 112 HW 3 22.95
0
CZ81 Treadmill 68 SG 2 349.95
0
BZ66 Washer 52 AP 3 399.99
0
10 rows selected.
SQL> --end question #7
SQL>
SQL> --Chapter 5, question #8
SQL>
SQL> delete boyd_part
2
SQL>
SQL> alter table boyd_part
2 delete item_class char(2)
3
SQL>
SQL> --end question #8; no clue if it is correct
SQL> --our book certainly leaves gaps in what you can
SQL> --or cannot 'put together' to make sense
SQL>
SQL> Chapter 5, question #9
unknown command beginning "Chapter 5,..." - rest of line ignored.
SQL>
SQL> alter table boyd_part
2 modify part_description char(30);
Table altered.
SQL> describe boyd_part;
Name Null? Type
------------------------------- -------- ----
PART_NUMBER NOT NULL CHAR(4)
PART_DESCRIPTION CHAR(30)
UNITS_ON_HAND NUMBER(3)
ITEM_CLASS CHAR(2)
WAREHOUSE_NUMBER NUMBER(1)
UNIT_PRICE NUMBER(8,2)
ALLOCATION NUMBER(3)
SQL> --end question #9
SQL>
SQL> --chapter 5, question #10
SQL>
=========================================================
CHAPTER 6
SQL> select * from smallcst;
select * from smallcst
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from smallcust;
select * from smallcust
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE VIEW SMALLCST AS
2 SELECT CUSTOMER_NUMBER, LAST, FIRST, STREET,
3 BALANCE, CREDIT_LIMIT
4 FROM BOYD_CUST
5 WHERE CREDIT_LIMIT <= 1000;
View created.
SQL> SELECT CUSTOMER_NUMBER, LAST, FIRST
2 FROM SMALLCST
3 WHERE BALANCE > CREDIT_LIMIT;
CUS LAST FIRST
--- ---------- ----------
315 Daniels Tom
622 Martin Dan
SQL> SELECT CUSTOMER_NUMBER, LAST, FIRST
2 FROM BOYD_CUST
3 WHERE CREDIT_LIMIT <= 1000
4 AND BALANCE > CREDIT_LIMIT;
CUS LAST FIRST
--- ---------- ----------
315 Daniels Tom
622 Martin Dan
SQL> CREATE VIEW BANTAM AS
2 SELECT BOOK_CODE, BOOK_TITLE, BOOK_TYPE,
3 BOOK_PRICE
4 FROM BOOK
5 WHERE PUBLISHER_CODE = 'BB';
View created.
SQL> SELECT BOOK_CODE, BOOK_TITLE, BOOK_PRICE
2 FROM BANTAM
3 WHERE BOOK_PRICE < 10;
BOOK BOOK_TITLE BOOK_PRICE
---- ------------------------------ ----------
0180 Shyness 7.65
0200 Stranger 8.75
138X Death on the Nile 3.95
2908 Hymns to the Night 6.75
3906 Vortex 5.45
6328 Vixen 07 5.55
7405 Night Probe 5.65
7 rows selected.
SQL> SELECT BOOK_CODE, BOOK_TITLE, BOOK_PRICE
2 FROM BOOK
3 WHERE PUBLISHER_CODE = 'BB'
4 AND BOOK_PRICE < 10;
BOOK BOOK_TITLE BOOK_PRICE
---- ------------------------------ ----------
0180 Shyness 7.65
0200 Stranger 8.75
138X Death on the Nile 3.95
2908 Hymns to the Night 6.75
3906 Vortex 5.45
6328 Vixen 07 5.55
7405 Night Probe 5.65
7 rows selected.
---------------------------------------
Begin Session #2:
SQL> CREATE INDEX PARTIND ON
2
SQL> SELECT * FROM BOYD_ORDERLINE;
ORDER_NUMBER PART NUMBER_ORDERED QUOTED_PRICE
------------ ---- -------------- ------------
12489 AX12 11 21.95
12491 BT04 1 149.99
12491 BZ66 1 399.99
12494 CB03 4 279.99
12495 CX11 2 22.95
12498 AZ52 2 12.95
12498 BA74 4 24.95
12500 BT04 1 149.99
12504 CZ81 2 325.99
12600 AX12 5 13.95
12600 BA74 3 4.5
11 rows selected.
SQL> CREATE INDEX PARTIND ON BOYD_ORDERLINE (PART_NUMBER);
Index created.
SQL> SELECT * FROM PARTIND;
SELECT * FROM PARTIND
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE INDEX PARTIND2 ON BOYD_PART (ITEM_CLASS);
Index created.
SQL> CREATE INDEX PARTIND3 ON BOYD_PART (ITEM_CLASS,
2 WAREHOUSE_NUMBER);
Index created.
SQL> CREATE INDEX PARTIND4 ON BOYD_PART (ITEM_CLASS,
2 WAREHOUSE_NUMBER, UNITS_ON_HAND DESC);
Index created.
SQL> exit
Disconnected from Oracle7 Server Release 7.1.6.2.0 - Production Release
With the distributed, replication and parallel query options
PL/SQL Release 2.1.6.2.0 - Production
boydl@scis >
boydl@scis >
boydl@scis > exit
boydl@scis > logout
--------------------------
session #3
SQL> SELECT TABLE_NAME
2 FROM SYSCOLUMNS
3 WHERE OWNER = 'BOYDL';
WHERE OWNER = 'BOYDL'
*
ERROR at line 3:
ORA-00904: invalid column name
SQL> SELECT TABLE_NAME
2 FROM SYSCOLUMNS
3 WHERE CREATOR = 'BOYDL';
SELECT TABLE_NAME
*
ERROR at line 1:
ORA-00904: invalid column name
SQL> SELECT TNAME
2 FROM SYSCOLUMNS
3 WHERE CREATOR = 'BOYDL';
TNAME
------------------------------
AUTHOR
AUTHOR
AUTHOR
BANTAM
BANTAM
BANTAM
BANTAM
BOOK
BOOK
BOOK
BOOK
TNAME
------------------------------
BOOK
BOOK
BOYD_CUST
BOYD_CUST
BOYD_CUST
BOYD_CUST
BOYD_CUST
BOYD_CUST
BOYD_CUST
BOYD_CUST
BOYD_CUST
TNAME
------------------------------
BOYD_CUST
BOYD_ORDERLINE
BOYD_ORDERLINE
BOYD_ORDERLINE
BOYD_ORDERLINE
BOYD_ORDERS
BOYD_ORDERS
BOYD_ORDERS
BOYD_PART
BOYD_PART
BOYD_PART
TNAME
------------------------------
BOYD_PART
BOYD_PART
BOYD_PART
BOYD_PART
BOYD_SALESREP
BOYD_SALESREP
BOYD_SALESREP
BOYD_SALESREP
BOYD_SALESREP
BOYD_SALESREP
BOYD_SALESREP
TNAME
------------------------------
BOYD_SALESREP
BOYD_SALESREP
BRANCH
BRANCH
BRANCH
BRANCH
CUSTORD
CUSTORD
CUSTORD
CUSTORD
CUSTORD
TNAME
------------------------------
CUSTORD
HOUSEWARES
HOUSEWARES
HOUSEWARES
HOUSEWARES
INVENT
INVENT
INVENT
PUBLISHER
PUBLISHER
PUBLISHER
TNAME
------------------------------
PUBLISHER
SMALLCST
SMALLCST
SMALLCST
SMALLCST
SMALLCST
SMALLCST
SMLCUST
SMLCUST
SMLCUST
SMLCUST
TNAME
------------------------------
SMLCUST
SMLCUST
SMLCUST
SMLCUST
SMLCUST
SPGOODS
SPGOODS
SPGOODS
WROTE
WROTE
WROTE
88 rows selected.
SQL> SELECT TNAME, CNAME, COLTYPE
2 FROM SYSCOLUMNS
3 WHERE CREATOR = 'BOYDL';
TNAME CNAME COLTYPE
------------------------------ ------------------------------ ---------
AUTHOR AUTHOR_NUMBER UNDEFINED
AUTHOR AUTHOR_NAME UNDEFINED
AUTHOR AUTHOR_FIRST UNDEFINED
BANTAM BOOK_CODE UNDEFINED
BANTAM BOOK_TITLE UNDEFINED
BANTAM BOOK_TYPE UNDEFINED
BANTAM BOOK_PRICE NUMBER
BOOK BOOK_CODE UNDEFINED
BOOK BOOK_TITLE UNDEFINED
BOOK PUBLISHER_CODE UNDEFINED
BOOK BOOK_TYPE UNDEFINED
TNAME CNAME COLTYPE
------------------------------ ------------------------------ ---------
BOOK BOOK_PRICE NUMBER
BOOK PAPERBACK UNDEFINED
BOYD_CUST CUSTOMER_NUMBER UNDEFINED
BOYD_CUST LAST CHAR
BOYD_CUST FIRST CHAR
BOYD_CUST STREET CHAR
BOYD_CUST CITY CHAR
BOYD_CUST STATE UNDEFINED
BOYD_CUST ZIP_CODE NUMBER
BOYD_CUST BALANCE NUMBER
BOYD_CUST CREDIT_LIMIT NUMBER
TNAME CNAME COLTYPE
------------------------------ ------------------------------ ---------
BOYD_CUST SLSREP_NUMBER NUMBER
BOYD_ORDERLINE ORDER_NUMBER NUMBER
BOYD_ORDERLINE PART_NUMBER UNDEFINED
BOYD_ORDERLINE NUMBER_ORDERED NUMBER
BOYD_ORDERLINE QUOTED_PRICE NUMBER
BOYD_ORDERS ORDER_NUMBER NUMBER
BOYD_ORDERS ORDER_DATE DATE
BOYD_ORDERS CUSTOMER_NUMBER NUMBER
BOYD_PART PART_NUMBER UNDEFINED
BOYD_PART PART_DESCRIPTION UNDEFINED
BOYD_PART UNITS_ON_HAND NUMBER
TNAME CNAME COLTYPE
------------------------------ ------------------------------ ---------
BOYD_PART ITEM_CLASS UNDEFINED
BOYD_PART WAREHOUSE_NUMBER NUMBER
BOYD_PART UNIT_PRICE NUMBER
BOYD_PART ALLOCATION NUMBER
BOYD_SALESREP SLSREP_NUMBER NUMBER
BOYD_SALESREP LAST CHAR
BOYD_SALESREP FIRST CHAR
BOYD_SALESREP STREET CHAR
BOYD_SALESREP CITY CHAR
BOYD_SALESREP STATE UNDEFINED
BOYD_SALESREP ZIP_CODE NUMBER
TNAME CNAME COLTYPE
------------------------------ ------------------------------ ---------
BOYD_SALESREP TOTAL_COMMISSION NUMBER
BOYD_SALESREP COMMISSION_RATE NUMBER
BRANCH BRANCH_NUMBER UNDEFINED
BRANCH BRANCH_NAME UNDEFINED
BRANCH BRANCH_LOCATION UNDEFINED
BRANCH NUMBER_EMPLOYEES NUMBER
CUSTORD LAST CHAR
CUSTORD FIRST CHAR
CUSTORD BALANCE NUMBER
CUSTORD ORDER_NUMBER NUMBER
CUSTORD ORDER_DATE DATE
TNAME CNAME COLTYPE
------------------------------ ------------------------------ ---------
CUSTORD CUSTOMER_NUMBER UNDEFINED
HOUSEWARES PART_NUMBER UNDEFINED
HOUSEWARES PART_DESCRIPTION CHAR
HOUSEWARES UNITS_ON_HAND NUMBER
HOUSEWARES UNIT_PRICE NUMBER
INVENT BOOK_CODE UNDEFINED
INVENT BRANCH_NUMBER UNDEFINED
INVENT UNITS_ON_HAND NUMBER
PUBLISHER PUBLISHER_CODE UNDEFINED
PUBLISHER PUBLISHER_NAME UNDEFINED
PUBLISHER PUBLISHER_CITY UNDEFINED
TNAME CNAME COLTYPE
------------------------------ ------------------------------ ---------
PUBLISHER PUBLISHER_STATE UNDEFINED
SMALLCST CUSTOMER_NUMBER UNDEFINED
SMALLCST LAST CHAR
SMALLCST FIRST CHAR
SMALLCST STREET CHAR
SMALLCST BALANCE NUMBER
SMALLCST CREDIT_LIMIT NUMBER
SMLCUST LAST CHAR
SMLCUST FIRST CHAR
SMLCUST STREET CHAR
SMLCUST CITY CHAR
TNAME CNAME COLTYPE
------------------------------ ------------------------------ ---------
SMLCUST STATE UNDEFINED
SMLCUST ZIP_CODE NUMBER
SMLCUST BALANCE NUMBER
SMLCUST CREDIT_LIMIT NUMBER
SMLCUST CUSTOMER_NUMBER UNDEFINED
SPGOODS PART_NUMBER UNDEFINED
SPGOODS PART_DESCRIPTION UNDEFINED
SPGOODS UNIT_PRICE NUMBER
WROTE BOOK_CODE UNDEFINED
WROTE AUTHOR_NUMBER UNDEFINED
WROTE SEQUENCE_NUMBER NUMBER
88 rows selected.
SQL> SELECT TNAME
2 FROM SYSCATALOG
3 WHERE CREATOR = 'BOYDL';
TNAME
------------------------------
AUTHOR
BANTAM
BOOK
BOYD_CUST
BOYD_ORDERLINE
BOYD_ORDERS
BOYD_PART
BOYD_SALESREP
BRANCH
CUSTORD
HOUSEWARES
TNAME
------------------------------
INVENT
PUBLISHER
SMALLCST
SMLCUST
SPGOODS
WROTE
17 rows selected.
SQL> SELECT TNAME, CNAME, COLTYPE
2 FROM SYSCOLUMNS
3 WHERE TNAME = 'BOYD_PART';
TNAME CNAME COLTYPE
------------------------------ ------------------------------ ---------
BOYD_PART PART_NUMBER UNDEFINED
BOYD_PART PART_DESCRIPTION UNDEFINED
BOYD_PART UNITS_ON_HAND NUMBER
BOYD_PART ITEM_CLASS UNDEFINED
BOYD_PART WAREHOUSE_NUMBER NUMBER
BOYD_PART UNIT_PRICE NUMBER
BOYD_PART ALLOCATION NUMBER
7 rows selected.
SQL> SELECT TNAME, CNAME, COLTYPE
2 FROM SYSCOLUMNS
3 WHERE TNAME = 'COHENM.PART';
no rows selected
SQL> SELECT TNAME, CNAME, COLTYPE
2 FROM SYSCOLUMNS
3 WHERE TNAME = 'PART';
TNAME CNAME COLTYPE
------------------------------ ------------------------------ ---------
PART PART_NUMBER UNDEFINED
PART PART_DESCRIPTION CHAR
PART UNITS_ON_HAND NUMBER
PART ITEM_CLASS UNDEFINED
PART WAREHOUSE_NUMBER NUMBER
PART UNIT_PRICE NUMBER
PART PART_NO UNDEFINED
PART PART_DESCRIPTION UNDEFINED
PART UNITS_ON_HAND NUMBER
PART ITEM_CLASS UNDEFINED
PART WAREHOUSE_NO UNDEFINED
TNAME CNAME COLTYPE
------------------------------ ------------------------------ ---------
PART UNIT_PRICE NUMBER
PART PART_NO UNDEFINED
PART PART_DESCRIPTION UNDEFINED
PART UNITS_ON_HAND NUMBER
PART ITEM_CLASS UNDEFINED
PART WAREHOUSE_NO UNDEFINED
PART UNIT_PRICE NUMBER
18 rows selected.
SQL> SELECT * FROM BOYD_PART;
PART PART_DESCRIPTION UNITS_ON_HAND IT WAREHOUSE_NUMBER
UNIT_PRICE
---- ------------------------------ ------------- -- ---------------- -
---------
ALLOCATION
----------
AX12 Iron 104 HW 3
24.95
0
AZ52 Dartboard 20 SG 2
12.95
0
BA74 Basketball 40 SG 1
29.95
0
PART PART_DESCRIPTION UNITS_ON_HAND IT WAREHOUSE_NUMBER
UNIT_PRICE
---- ------------------------------ ------------- -- ---------------- -
---------
ALLOCATION
----------
BH22 Cornpopper 95 HW 3
24.95
0
BT04 Gas Stove 11 AP 2
149.99
2
CA14 Griddle 78 HW 3
39.99
0
PART PART_DESCRIPTION UNITS_ON_HAND IT WAREHOUSE_NUMBER
UNIT_PRICE
---- ------------------------------ ------------- -- ---------------- -
---------
ALLOCATION
----------
CB03 Bike 44 SG 1
299.99
0
CX11 Blender 112 HW 3
22.95
0
CZ81 Treadmill 68 SG 2
349.95
0
PART PART_DESCRIPTION UNITS_ON_HAND IT WAREHOUSE_NUMBER
UNIT_PRICE
---- ------------------------------ ------------- -- ---------------- -
---------
ALLOCATION
----------
BZ66 Washer 52 AP 3
399.99
0
10 rows selected.
SQL> SELECT TNAME
2 FROM SYSCOLUMNS
3 WHERE CNAME = 'BOYD_PART';
no rows selected
SQL> SELECT TNAME
2 FROM SYSCOLUMNS
3 WHERE CNAME = 'BOYD_PART'
4 AND TNAME =
5
SQL> SELECT TNAME
2 FROM SYSCOLUMNS
3 WHERE CNAME = 'PART_NUMBER';
TNAME
------------------------------
PART
ORDERLINE
BOYD_ORDERLINE
SPGOODS
BOYD_PART
HOUSEWARES
6 rows selected.
SQL> SELECT TNAME
2 FROM SYSCOLUMNS
3 WHERE CNAME = 'BOYD_PART.PART_NUMBER';
no rows selected
SQL> SELECT TNAME
2 FROM SYSCOLUMNS
3 WHERE CNAME = 'PART_NUMBER'
4 AND CREATOR = 'BOYDL';
TNAME
------------------------------
BOYD_ORDERLINE
BOYD_PART
HOUSEWARES
SPGOODS
SQL> SELECT VIEWNAME
2 FROM SYSVIEWS
3 WHERE CREATOR = 'BOYDL';
WHERE CREATOR = 'BOYDL'
*
ERROR at line 3:
ORA-00904: invalid column name
SQL> SELECT VIEWNAME
2 FROM SYSVIEWS
3 WHERE VCREATOR = 'BOYDL';
VIEWNAME
------------------------------
BANTAM
CUSTORD
HOUSEWARES
SMALLCST
SMLCUST
SQL> SELECT TNAME, CNAME, COLTYPE
2 FROM SYSCOLUMNS
3 WHERE CNAME = 'STREET','CITY','ZIP_CODE'
4 AND CREATOR = 'BOYDL'
5 ORDER BY TNAME;
WHERE CNAME = 'STREET','CITY','ZIP_CODE'
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
SQL> SELECT TNAME, CNAME, COLTYPE
2 FROM SYSCOLUMNS
3 WHERE CNAME = 'STREET'
4 AND CNAME = 'CITY'
5 AND CNAME = 'ZIP_CODE'
6 AND CREATOR = 'BOYDL'
7 ORDER BY TNAME;
no rows selected
SQL> SELECT TNAME, CNAME, COLTYPE
2 FROM SYSCOLUMNS
3 WHERE CNAME = ('STREET', 'CITY', 'ZIP_CODE')
4 AND CREATOR = 'BOYDL'
5 ORDER BY TNAME;
WHERE CNAME = ('STREET', 'CITY', 'ZIP_CODE')
*
ERROR at line 3:
ORA-01797: this operator must be followed by ANY or ALL
SQL> SELECT TNAME, CNAME, COLTYPE
2 FROM SYSCOLUMNS
3 WHERE CNAME = ALL ('STREET', 'CITY', 'ZIP_CODE')
4 AND CREATOR = 'BOYDL'
5 ORDER BY TNAME;
no rows selected
SQL> SELECT TNAME, CNAME, COLTYPE
2 FROM SYSCOLUMNS
3 WHERE CNAME = STREET, CITY, ZIP_CODE
4 AND CREATOR = 'BOYDL'
5 ORDER BY TNAME;
WHERE CNAME = STREET, CITY, ZIP_CODE
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
SQL> SELECT TNAME, CNAME, COLTYPE
FROM 2 SYSCOLUMNS
WHERE CNAME 3 = ALL (STREET, CITY, ZIP_CODE)
AND CREATOR 4 =
5 SQL> SQL> SQL> SQL> SQL> SQL>
SQL> SELECT TNAME, CNAME, COLTYPE
2 SQL> SQL> SQL>
SQL> SELECT TNAME, CNAME, COLTYPE
2 FROM SYSCOLUMNS
3 WHERE CNAME = ALL (STREET, CITY, ZIP_CODE)
4 AND CREATOR = 'BOYDL'
ORDER 5 BY TNAME;
WHERE CNAME = ALL (STREET, CITY, ZIP_CODE)
*
ERROR at line 3:
ORA-00904: invalid column name
SQL> SELECT TNAME, CNAME, COLTYPE
2 FROM SYSCOLUMNS
3 WHERE CNAME = 'STREET' AND 'CITY' AND 'ZIP_CODE'
AND 4 WHERE CREATOR = 'BOYDL'
ORDER BY TNAME; 5
WHERE CNAME = 'STREET' AND 'CITY' AND 'ZIP_CODE'
*
ERROR at line 3:
ORA-00920: invalid relational operator
SQL> SELECT TNAME, CNAME, COLTYPE
2 FROM SYSCOLUMNS
3 WHERE CREATOR = 'BOYDL'
O 4 RDER BY TNAME;
TNAME CNAME COLTYPE
------------------------------ ------------------------------ ---------
AUTHOR AUTHOR_NUMBER UNDEFINED
AUTHOR AUTHOR_NAME UNDEFINED
AUTHOR AUTHOR_FIRST UNDEFINED
BANTAM BOOK_CODE UNDEFINED
BANTAM BOOK_TITLE UNDEFINED
BANTAM BOOK_TYPE UNDEFINED
BANTAM BOOK_PRICE NUMBER
BOOK BOOK_CODE UNDEFINED
BOOK BOOK_TITLE UNDEFINED
BOOK PUBLISHER_CODE UNDEFINED
BOOK BOOK_TYPE UNDEFINED
TNAME CNAME COLTYPE
------------------------------ ------------------------------ ---------
BOOK BOOK_PRICE NUMBER
BOOK PAPERBACK UNDEFINED
BOYD_CUST CUSTOMER_NUMBER UNDEFINED
BOYD_CUST LAST CHAR
BOYD_CUST FIRST CHAR
BOYD_CUST STREET CHAR
BOYD_CUST CITY CHAR
BOYD_CUST STATE UNDEFINED
BOYD_CUST ZIP_CODE NUMBER
BOYD_CUST BALANCE NUMBER
BOYD_CUST CREDIT_LIMIT NUMBER
TNAME CNAME COLTYPE
------------------------------ ------------------------------ ---------
BOYD_CUST SLSREP_NUMBER NUMBER
BOYD_ORDERLINE ORDER_NUMBER NUMBER
BOYD_ORDERLINE PART_NUMBER UNDEFINED
BOYD_ORDERLINE NUMBER_ORDERED NUMBER
BOYD_ORDERLINE QUOTED_PRICE NUMBER
BOYD_ORDERS ORDER_NUMBER NUMBER
BOYD_ORDERS ORDER_DATE DATE
BOYD_ORDERS CUSTOMER_NUMBER NUMBER
BOYD_PART PART_NUMBER UNDEFINED
BOYD_PART PART_DESCRIPTION UNDEFINED
BOYD_PART UNITS_ON_HAND NUMBER
TNAME CNAME COLTYPE
------------------------------ ------------------------------ ---------
BOYD_PART ITEM_CLASS UNDEFINED
BOYD_PART WAREHOUSE_NUMBER NUMBER
BOYD_PART UNIT_PRICE NUMBER
BOYD_PART ALLOCATION NUMBER
BOYD_SALESREP SLSREP_NUMBER NUMBER
BOYD_SALESREP LAST CHAR
BOYD_SALESREP FIRST CHAR
BOYD_SALESREP STREET CHAR
BOYD_SALESREP CITY CHAR
BOYD_SALESREP STATE UNDEFINED
BOYD_SALESREP ZIP_CODE NUMBER
TNAME CNAME COLTYPE
------------------------------ ------------------------------ ---------
BOYD_SALESREP TOTAL_COMMISSION NUMBER
BOYD_SALESREP COMMISSION_RATE NUMBER
BRANCH BRANCH_NUMBER UNDEFINED
BRANCH BRANCH_NAME UNDEFINED
BRANCH BRANCH_LOCATION UNDEFINED
BRANCH NUMBER_EMPLOYEES NUMBER
CUSTORD LAST CHAR
CUSTORD FIRST CHAR
CUSTORD BALANCE NUMBER
CUSTORD ORDER_NUMBER NUMBER
CUSTORD ORDER_DATE DATE
TNAME CNAME COLTYPE
------------------------------ ------------------------------ ---------
CUSTORD CUSTOMER_NUMBER UNDEFINED
HOUSEWARES PART_NUMBER UNDEFINED
HOUSEWARES PART_DESCRIPTION CHAR
HOUSEWARES UNITS_ON_HAND NUMBER
HOUSEWARES UNIT_PRICE NUMBER
INVENT BOOK_CODE UNDEFINED
INVENT BRANCH_NUMBER UNDEFINED
INVENT UNITS_ON_HAND NUMBER
PUBLISHER PUBLISHER_CODE UNDEFINED
PUBLISHER PUBLISHER_NAME UNDEFINED
PUBLISHER PUBLISHER_CITY UNDEFINED
TNAME CNAME COLTYPE
------------------------------ ------------------------------ ---------
PUBLISHER PUBLISHER_STATE UNDEFINED
SMALLCST CUSTOMER_NUMBER UNDEFINED
SMALLCST LAST CHAR
SMALLCST FIRST CHAR
SMALLCST STREET CHAR
SMALLCST BALANCE NUMBER
SMALLCST CREDIT_LIMIT NUMBER
SMLCUST LAST CHAR
SMLCUST FIRST CHAR
SMLCUST STREET CHAR
SMLCUST CITY CHAR
TNAME CNAME COLTYPE
------------------------------ ------------------------------ ---------
SMLCUST STATE UNDEFINED
SMLCUST ZIP_CODE NUMBER
SMLCUST BALANCE NUMBER
SMLCUST CREDIT_LIMIT NUMBER
SMLCUST CUSTOMER_NUMBER UNDEFINED
SPGOODS PART_NUMBER UNDEFINED
SPGOODS PART_DESCRIPTION UNDEFINED
SPGOODS UNIT_PRICE NUMBER
WROTE BOOK_CODE UNDEFINED
WROTE AUTHOR_NUMBER UNDEFINED
WROTE SEQUENCE_NUMBER NUMBER
88 rows selected.
SQL> SELECT TNAME, CNAME, COLTYPE
2 FROM SYSCOLUMNS
3 WHERE CNAME = ALL ('STREET', 'CITY', 'ZIP_CODE')
4 AND CREATOR = 'BOYDL'
5 ORDER BY TNAME;
no rows selected 2 3 4 5 2 FROM SYSCOLUMNS
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
SQL> SQL>
unknown command beginning "no rows se..." - rest of line ignored.
SQL>
SQL> SELECT TNAME, CNAME, COLTYPE
2 FROM SYSCOLUMNS
3 WHERE CNAME = ANY ('STREET', 'CITY', 'ZIP_CODE')
4 AND CREATOR = 'BOYDL'
5 ORDER BY TNAME;
TNAME CNAME COLTYPE
------------------------------ ------------------------------ ---------
BOYD_CUST STREET CHAR
BOYD_CUST CITY CHAR
BOYD_CUST ZIP_CODE NUMBER
BOYD_SALESREP STREET CHAR
BOYD_SALESREP CITY CHAR
BOYD_SALESREP ZIP_CODE NUMBER
SMALLCST STREET CHAR
SMLCUST STREET CHAR
SMLCUST CITY CHAR
SMLCUST ZIP_CODE NUMBER
10 rows selected.
---------------------------------------------------
SESSION #4
SQL> CREATE VIEW HARDBACK AS
SELECT 2
3
SQL> SQL>
SQL> CREATE VIEW HARDBACK AS
2 SELECT BOOK.BOOK_CODE, BOOK_TITLE,
3 PUBLISHER_NAME, BOOK_PRICE
4 BOOK, PUBLISHER
5 WHERE BOOK.BOOK_CODE = PUBLISHER.BOOK_CODE;
WHERE BOOK.BOOK_CODE = PUBLISHER.BOOK_CODE
*
ERROR at line 5:
ORA-00923: FROM keyword not found where expected
SQL> CREATE VIEW HARDBACK AS
2 SELECT BOOK.BOOK_CODE, BOOK_TITLE,
3 PUBLISHER_NAME, BOOK_PRICE
4 FROM BOOK, PUBLISHER
5 WHERE BOOK.BOOK_CODE = PUBLISHER.BOOK_CODE;
WHERE BOOK.BOOK_CODE = PUBLISHER.BOOK_CODE
*
ERROR at line 5:
ORA-00904: invalid column name
SQL> CREATE VIEW HARDBACK AS
2
SQL>
SQL> CREATE VIEW HARDBACK AS
2 SELECT BOOK.PUBLISHER_CODE, BOOK_TITLE,
3 PUBLISHER_NAME, BOOK_PRICE
4 FROM BOOK, PUBLISHER
5 WHERE BOOK.PUBLISHER_CODE = PUBLISHER.PUBLISHER_CODE;
View created.
SQL> SQL> SELECT BOOK_CODE, BOOK_TITLE, BOOK_PRICE
2 FROM BANTAM
3 WHERE BOOK_PRICE < 10;
unknown command beginning "SQL> SELEC..." - rest of line ignored.
SQL> SQL> SQL>
SQL> SQL> SELECT BOOK_TITLE, PUBLISHER_NAME
2 FROM HARDBACK
3 WHERE BOOK_PRICE > 20;
BOOK_TITLE PUBLISHER_NAME
------------------------------ --------------------
Database Systems Best and Furrow
DOS Essentials Best and Furrow
dBASE Programming Best and Furrow
Magritte Signet
A Guide to SQL Best and Furrow
SQL> SQL> SELECT BOOK_CODE, BOOK_TITLE, BOOK_PRICE
2 FROM BOOK
3 WHERE PUBLISHER_CODE = 'BB'
4 AND BOOK_PRICE < 10;
unknown command beginning "SQL> SELEC..." - rest of line ignored.
SQL> SQL> SQL> SQL>
SQL> SELECT BOOK_TITLE, PUBLISHER_NAME
2 FROM BOOK, PUBLISHER
3 WHERE BOOK.PUBLISHER_CODE = PUBLISHER.PUBLISHER_CODE
4 AND BOOK_PRICE > 20;
BOOK_TITLE PUBLISHER_NAME
------------------------------ --------------------
Database Systems Best and Furrow
DOS Essentials Best and Furrow
dBASE Programming Best and Furrow
Magritte Signet
A Guide to SQL Best and Furrow
SQL> DESCRIBE BOOK;
Name Null? Type
------------------------------- -------- ----
BOOK_CODE NOT NULL CHAR(4)
BOOK_TITLE CHAR(30)
PUBLISHER_CODE CHAR(2)
BOOK_TYPE CHAR(3)
BOOK_PRICE NUMBER(4,2)
PAPERBACK CHAR(1)
SQL> DESCRIBE PUBLISHER;
Name Null? Type
------------------------------- -------- ----
PUBLISHER_CODE NOT NULL CHAR(2)
PUBLISHER_NAME CHAR(20)
PUBLISHER_CITY CHAR(20)
PUBLISHER_STATE CHAR(2)
SQL> CREATE VIEW VALUE (BRANCH_NUMBER, TOTAL_COUNT) AS
2 SELECT BRANCH_NUMBER, SUM(UNITS_ON_HAND)
3 FROM INVENT
4 GROUP BY BRANCH_NUMBER;
View created.
SQL> SELECT *
2 FROM VALUE;
B TOTAL_COUNT
- -----------
1 19
2 26
3 16
4 10
SQL> SELECT BRANCH_NUMBER, SUM(UNITS_ON_HAND)
2 FROM INVENT
3 GROUP BY BRANCH_NUMBER;
B SUM(UNITS_ON_HAND)
- ------------------
1 19
2 26
3 16
4 10
SQL> describe branch;
Name Null? Type
------------------------------- -------- ----
BRANCH_NUMBER NOT NULL CHAR(1)
BRANCH_NAME CHAR(20)
BRANCH_LOCATION CHAR(20)
NUMBER_EMPLOYEES NUMBER(2)
SQL> GRANT SELECT ON BOOK TO LOPEZ;
GRANT SELECT ON BOOK TO LOPEZ
*
ERROR at line 1:
ORA-01917: user or role 'LOPEZ' does not exist
SQL> GRANT INSERT ON BOOK, PUBLISHER TO BOWEN, MERRILL;
GRANT INSERT ON BOOK, PUBLISHER TO BOWEN, MERRILL
*
ERROR at line 1:
ORA-00990: missing or invalid privilege
SQL> GRANT INSERT ON BOOK AND PUBLISHER
2 TO BOWEN, MERRILL;
GRANT INSERT ON BOOK AND PUBLISHER
*
ERROR at line 1:
ORA-00990: missing or invalid privilege
SQL> GRANT INSERT ON BOOK, PUBLISHER TO BOWEN;
GRANT INSERT ON BOOK, PUBLISHER TO BOWEN
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> GRANT INSERT ON BOOK TO BOWEN, MERRILL;
GRANT INSERT ON BOOK TO BOWEN, MERRILL
*
ERROR at line 1:
ORA-01917: user or role 'BOWEN' does not exist
SQL> GRANT INSERT ON PUBLISHER TO BOWEN, MERRILL;
GRANT INSERT ON PUBLISHER TO BOWEN, MERRILL
*
ERROR at line 1:
ORA-01917: user or role 'BOWEN' does not exist
SQL> GRANT INSERT ON BOOK.BOOK_TITLE, PUBLISHER.PUBLISHER_NAME
2 TO BOWEN, MERRILL;
GRANT INSERT ON BOOK.BOOK_TITLE, PUBLISHER.PUBLISHER_NAME
*
ERROR at line 1:
ORA-00990: missing or invalid privilege
SQL> GRANT VIEW HARDBACK TO BOWEN, MERRILL;
GRANT VIEW HARDBACK TO BOWEN, MERRILL
*
ERROR at line 1:
ORA-00990: missing or invalid privilege
SQL> GRANT HARDBACK TO BOWEN, MERRILL;
GRANT HARDBACK TO BOWEN, MERRILL
*
ERROR at line 1:
ORA-01919: role 'HARDBACK' does not exist
SQL> GRANT INSERT ON HARDBACK TO BOWEN, MERRILL;
GRANT INSERT ON HARDBACK TO BOWEN, MERRILL
*
ERROR at line 1:
ORA-01917: user or role 'BOWEN' does not exist
SQL> GRANT INSERT ON VALUE TO JENKINS, SHERMAN;
GRANT INSERT ON VALUE TO JENKINS, SHERMAN
*
ERROR at line 1:
ORA-01917: user or role 'JENKINS' does not exist
SQL> GRANT SELECT ON BOOK_TITLE, BOOK_CODE, BOOK_PRICE
2 TO PUBLIC;
GRANT SELECT ON BOOK_TITLE, BOOK_CODE, BOOK_PRICE
*
ERROR at line 1:
ORA-00990: missing or invalid privilege
SQL> GRANT SELECT ON HARDBACK TO PUBLIC;
Grant succeeded.
SQL> GRANT INSERT, DELETE ON PUBLISHER TO SCOUT;
GRANT INSERT, DELETE ON PUBLISHER TO SCOUT
*
ERROR at line 1:
ORA-01917: user or role 'SCOUT' does not exist
SQL> GRANT INDEX ON BOOK TO VERNER;
GRANT INDEX ON BOOK TO VERNER
*
ERROR at line 1:
ORA-01917: user or role 'VERNER' does not exist
SQL> GRANT INDEX ON BOOK TO BOYDL;
GRANT INDEX ON BOOK TO BOYDL
*
ERROR at line 1:
ORA-01749: you may not GRANT/REVOKE privileges to/from yourself
SQL> grant index on book to cohenm;
Grant succeeded.
SQL> GRANT ALTER OF AUTHOR TO VERNER, SCOUT;
GRANT ALTER OF AUTHOR TO VERNER, SCOUT
*
ERROR at line 1:
ORA-00990: missing or invalid privilege
SQL> GRANT ALTER ON AUTHOR TO VERNER, SCOUT;
GRANT ALTER ON AUTHOR TO VERNER, SCOUT
*
ERROR at line 1:
ORA-01917: user or role 'VERNER' does not exist
SQL> GRANT ALL ON BRANCH, AUTHOR, PUBLISHER,
2 BOOK, WROTE, INVENT TO SCOUT;
GRANT ALL ON BRANCH, AUTHOR, PUBLISHER,
*
ERROR at line 1:
ORA-00990: missing or invalid privilege
SQL> GRANT ALL ON HARDBACK, VALUE, AUTHOR,
2 WROTE, INVENT TO SCOUT;
GRANT ALL ON HARDBACK, VALUE, AUTHOR,
*
ERROR at line 1:
ORA-00990: missing or invalid privilege
SQL> GRANT ALL ON AUTHOR, WROTE TO SCOUT;
GRANT ALL ON AUTHOR, WROTE TO SCOUT
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> GRANT ALL ON ALL TO SCOUT;
GRANT ALL ON ALL TO SCOUT
*
ERROR at line 1:
ORA-00903: invalid table name
SQL> GRANT ALL ON VALUE, HARDBACK, AUTHOR TO SCOUT;
GRANT ALL ON VALUE, HARDBACK, AUTHOR TO SCOUT
*
ERROR at line 1:
ORA-00990: missing or invalid privilege
SQL> GRANT ALL ON VALUE TO SCOUT;
GRANT ALL ON VALUE TO SCOUT
*
ERROR at line 1:
ORA-01917: user or role 'SCOUT' does not exist
SQL> GRANT ALL TO SCOUT;
GRANT ALL TO SCOUT
*
ERROR at line 1:
ORA-00990: missing or invalid privilege
SQL> GRANT ALL ON AUTHOR TO SCOUT;
GRANT ALL ON AUTHOR TO SCOUT
*
ERROR at line 1:
ORA-01917: user or role 'SCOUT' does not exist
SQL> GRANT ALL ON * TO SCOUT;
GRANT ALL ON * TO SCOUT
*
ERROR at line 1:
ORA-00903: invalid table name
SQL> GRANT ALL ON AUTHOR, VALUE TO SCOUT;
GRANT ALL ON AUTHOR, VALUE TO SCOUT
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> GRANT ALL ON TNAME (*);
GRANT ALL ON TNAME (*)
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> GRANT ALL ON TNAME = (*) TO SCOUT;
GRANT ALL ON TNAME = (*) TO SCOUT
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> GRANT ALL ON HARDBACK, VALUE TO SCOUT;
GRANT ALL ON HARDBACK, VALUE TO SCOUT
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> DROP VIEW HARDBACK;
View dropped.
SQL> DROP VIEW VALUE;
View dropped.
SQL> GRANT ALL ON BRANCH, PUBLISHER TO SCOUT;
GRANT ALL ON BRANCH, PUBLISHER TO SCOUT
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> ROLLBACK;
Rollback complete.
SQL> SELECT VIEW HARDBACK;
SELECT VIEW HARDBACK
*
ERROR at line 1:
ORA-00936: missing expression
SQL> GRANT ALL ON HARDBACK TO SCOUT;
GRANT ALL ON HARDBACK TO SCOUT
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE VIEW HARDBACK AS
2 SELECT BOOK.PUBLISHER_CODE, BOOK_TITLE,
3 PUBLISHER_NAME, BOOK_PRICE
4 FROM BOOK, PUBLISHER
5 WHERE BOOK.PUBLISHER_CODE = PUBLISHER.PUBLISHER_CODE;
View created.
SQL> CREATE VIEW VALUE (BRANCH_NUMBER, TOTAL_COUNT) AS
2 SELECT BRANCH_NUMBER, SUM(UNITS_ON_HAND)
3 FROM INVENTGROUP BY BRANCH_NUMBER;
FROM INVENTGROUP BY BRANCH_NUMBER
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
SQL> CREATE VIEW VALUE (BRANCH_NUMBER, TOTAL_COUNT) AS
2 SELECT BRANCH_NUMBER, SUM(UNITS_ON_HAND)
3 FROM INVENT
4 GROUP BY BRANCH_NUMBER;
View created.
SQL> GRANT ALTER ON BRANCH (BRANCH_NUMBER = '2') TO CHAMBERS;
GRANT ALTER ON BRANCH (BRANCH_NUMBER = '2') TO CHAMBERS
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> CREATE VIEW BRANCH2 AS
2 SELECT *
3 FROM BRANCH
4 WHERE BRANCH_NUMBER = '2'
5 GRANT SELECT ON BRANCH2 TO CHAMBERS;
GRANT SELECT ON BRANCH2 TO CHAMBERS
*
ERROR at line 5:
ORA-00933: SQL command not properly ended
SQL> CREATE VIEW BRANCH2 AS
2 SELECT *
3 FROM VALUE
4 WHERE BRANCH_NUMBER = '2'
5 GRANT ALTER ON BRANCH2 TO CHAMBERS;
GRANT ALTER ON BRANCH2 TO CHAMBERS
*
ERROR at line 5:
ORA-00933: SQL command not properly ended
SQL> CREATE VIEW BRANCH2 AS
2 SELECT * FROM BRANCH
3 WHERE BRANCH_NUMBER = '2',
4 GRANT ALTER ON BRANCH2 TO CHAMBERS;
WHERE BRANCH_NUMBER = '2',
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
SQL> GRANT ALTER ON VALUE TO CHAMBERS
2 WHERE BRANCH_NUMBER = '2';
WHERE BRANCH_NUMBER = '2'
*
ERROR at line 2:
ORA-00933: SQL command not properly ended
SQL> GRANT ALTER ON VALUE
2 WHERE BRANCH_NUMBER = '2' TO CHAMBERS;
WHERE BRANCH_NUMBER = '2' TO CHAMBERS
*
ERROR at line 2:
ORA-00905: missing keyword
SQL> GRANT ALTER ON VALUE (BRANCH_NUMBER = '2') TO CHAMBERS;
GRANT ALTER ON VALUE (BRANCH_NUMBER = '2') TO CHAMBERS
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> HELP
HELP
HELP [topic] | help
HELP displays information on the commands and conventions of SQL*Plus,
SQL, and PL/SQL. Type "help", a space, all or part of any topic, and
then press Enter.
Typing only part of a topic (e.g., HELP SE) will cause all help topics
that match the string (SE) to be displayed on your screen in
alphabetical
order (e.g., HELP SE will retrieve help on the SELECT statement, the
SET
command, and the Set Operators--in that order).
ENTER THIS... TO DISPLAY THIS...
--------------------------------------------------------------
help menu the main menu of help topics
help commands a list of SQL*Plus, SQL, and PL/SQL commands
help comm information on commands, comments, and commit
help help this screen
SQL> HELP GRANT
GRANT command (Object Privileges)
PURPOSE:
To grant privileges for a particular object to users and roles. To
grant system privileges and roles, use the GRANT command (System
Privileges and Roles).
SYNTAX:
GRANT {object_priv | ALL [PRIVILEGES]} [ (column [, column] ...) ]
[, {object_priv | ALL [PRIVILEGES]} [ (column [, column] ...) ] ] ...
ON [schema.]object
TO {user | role | PUBLIC} [, {user | role | PUBLIC}] ...
[WITH GRANT OPTION]
where:
object_priv
is an object privilege to be granted. You can substitute any of
these values:
* ALTER
* DELETE
* EXECUTE
* INDEX
* INSERT
* REFERENCES
* SELECT
* UPDATE
ALL PRIVILEGES
grants all the privileges for the object that you have been granted
with the GRANT OPTION. The user who owns the schema containing an
object automatically has all privileges on the object with the GRANT
OPTION.
column
specifies a table or view column on which privileges are granted.
You can only specify columns when granting the INSERT, REFERENCES,
or UPDATE privilege. If you do not list columns, the grantee has
the specified privilege on all columns in the table or view.
ON
identifies the object on which the privileges are granted. If you
do not qualify object with schema, Oracle assumes the object is in
your own schema. The object can be one of these types:
* table
* view
* sequence
* procedure, function, or package
* snapshots
* synonym for a table, view, sequence, snapshot, procedure,
function, or package
TO
identifies users or roles to which the object privilege is granted.
PUBLIC
grants object privileges to all users.
WITH GRANT OPTION
allows the grantee to grant the object privileges to other users and
roles. The grantee must be a user or PUBLIC, rather than a role.
PREREQUISITES:
The object must be in your own schema or you must have been granted
the object privileges with the GRANT OPTION.
If you are using Trusted Oracle in DBMS MAC mode, your DBMS label
must dominate the label at which the object privilege was granted to
you and the creation label of the grantee user or role.
SEE:
GRANT (System Privileges and Roles), REVOKE (Object Privileges)
GRANT command (System Privileges and Roles)
PURPOSE:
To grant system privileges and roles to users and roles. To grant
object privileges, use the GRANT command (Object Privileges).
SYNTAX:
GRANT {system_priv | role} [, {system_priv | role}] ...
TO {user | role | PUBLIC} [, {user | role | PUBLIC}] ...
[WITH ADMIN OPTION]
where:
system_priv
is a system privilege to be granted.
role
is a role to be granted
TO
identifies users or roles to which system privileges and roles are
granted.
PUBLIC
grants system privileges or roles to all users.
WITH ADMIN OPTION
allows the grantee to grant the system privilege or role to other
users or roles. If you grant a role with ADMIN OPTION, the grantee
can also alter or drop the role.
PREREQUISITES:
To grant a system privilege, you must either have been granted the
system privilege with the ADMIN OPTION or have been granted GRANT
ANY PRIVILEGE system privilege.
To grant a role, you must either have been granted the role with the
ADMIN OPTION or have been granted GRANT ANY ROLE system privilege or
have created the role.
If you are using Trusted Oracle in DBMS MAC mode, your DBMS label
must dominate both the label at which the system privilege or role
was granted to you and the creation label of the grantee user or
role.
SEE:
ALTER USER, CREATE USER, GRANT (Object Privileges), REVOKE (System
Privileges and Roles)
SQL> GRANT ALL PRIVILEGES ON ALL TNAME TO SCOUT;
GRANT ALL PRIVILEGES ON ALL TNAME TO SCOUT
*
ERROR at line 1:
ORA-00903: invalid table name
SQL> GRANT ALL PRIVILEGES ON TNAME = (*) TO SCOUT;
GRANT ALL PRIVILEGES ON TNAME = (*) TO SCOUT
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> GRANT ALL PRIVILEGES TO SCOUT;
GRANT ALL PRIVILEGES TO SCOUT
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> GRANT ALL PRIVILEGES ON VALUE, HARDBACK, AUTHOR TO SCOUT;
GRANT ALL PRIVILEGES ON VALUE, HARDBACK, AUTHOR TO SCOUT
*
ERROR at line 1:
ORA-00990: missing or invalid privilege
SQL> GRANT ALL PRIVILEGES ON VALUE TO SCOUT;
GRANT ALL PRIVILEGES ON VALUE TO SCOUT
*
ERROR at line 1:
ORA-01917: user or role 'SCOUT' does not exist
SQL> GRANT ALL PRIVILEGES ON (VALUE, HARDBACK, AUTHOR) TO SCOUT;
GRANT ALL PRIVILEGES ON (VALUE, HARDBACK, AUTHOR) TO SCOUT
*
ERROR at line 1:
ORA-00903: invalid table name
SQL> GRANT ALL PRIVILEGES ON VALUE TO COHENM;
Grant succeeded.
SQL> GRANT ALL PRIVILEGES ON VALUE, HARDBACK, AUTHOR TO COHENM;
GRANT ALL PRIVILEGES ON VALUE, HARDBACK, AUTHOR TO COHENM
*
ERROR at line 1:
ORA-00990: missing or invalid privilege
SQL> GRANT (ALL PRIVILEGES) ON VALUE, HARDBACK TO (COHENM);
GRANT (ALL PRIVILEGES) ON VALUE, HARDBACK TO (COHENM)
*
ERROR at line 1:
ORA-00990: missing or invalid privilege
SQL> GRANT ALL ON SYSTABLES TO SCOUT;
GRANT ALL ON SYSTABLES TO SCOUT
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> revoke all from Verner;
revoke all from Verner
*
ERROR at line 1:
ORA-00990: missing or invalid privilege
SQL> revoke all on syscolumns from verner;
revoke all on syscolumns from verner
*
ERROR at line 1:
ORA-01917: user or role 'VERNER' does not exist
SQL> REVOKE ALL ON SYSCOLUMNS FROM VERNER;
REVOKE ALL ON SYSCOLUMNS FROM VERNER
*
ERROR at line 1:
ORA-01917: user or role 'VERNER' does not exist
SQL>
SQL> GRANT ALL ON SYSCOLUMNS TO SCOUT;
GRANT ALL ON SYSCOLUMNS TO SCOUT
*
ERROR at line 1:
ORA-01917: user or role 'SCOUT' does not exist
SQL> CREATE BRANCH2 AS
2 SELECT UNITS_ON_HAND FROM INVENT
3 WHERE BRANC_NUMBER = '2'
4
SQL> CREATE BRANCH2 AS
2 SELECT UNITS_ON_HAND FROM INVENT
3 WHERE BRANCH_NUMBER = '2'
4 GRANT ALTER ON BRANCH2 TO CHAMBERS;
CREATE BRANCH2 AS
*
ERROR at line 1:
ORA-00901: invalid CREATE command
SQL> CREATE VIEW BRANCH2 AS
2 SELECT UNITS_ON_HAND FROM INVENT
3 WHERE BRANCH_NUMBER = '2'
4 GRANT ALTER ON BRANCH2 TO CHAMBERS;
GRANT ALTER ON BRANCH2 TO CHAMBERS
*
ERROR at line 4:
ORA-00933: SQL command not properly ended
SQL> CREATE VIEW BRANCH2 AS
2 SELECT UNITS_ON_HAND FROM VALUE
3 WHERE BRANCH_NUMBER = '2'
4 AND VALUE.BRANC_NUMBER = BRANCH.BRANCH_NUMBER
5 GRANT ALTER ON BRANCH2 TO CHAMBERS;
GRANT ALTER ON BRANCH2 TO CHAMBERS
*
ERROR at line 5:
ORA-00933: SQL command not properly ended
SQL> CREATE VIEW BRANCH2 AS
2 SELECT UNITS_ON_HAND FROM SYSCOLUMNS
3 WHERE BRANCH_NUMBER = '2'
4 GRANT ALTER ON BRANCH2 TO CHAMBERS;
GRANT ALTER ON BRANCH2 TO CHAMBERS
*
ERROR at line 4:
ORA-00933: SQL command not properly ended
SQL> GRANT ALTER ON INVENT
2 WHERE BRANCH_NUMBER = '2';
WHERE BRANCH_NUMBER = '2'
*
ERROR at line 2:
ORA-00905: missing keyword
SQL> GRANT ALTER ON INVENT TO CHAMBERS
2 WHERE BRANCH_NUMBER = '2';
WHERE BRANCH_NUMBER = '2'
*
ERROR at line 2:
ORA-00933: SQL command not properly ended
SQL> GRANT ALTER ON VALUE TO CHAMBERS
2 WHERE BRANCH_NUMBER = '2';
WHERE BRANCH_NUMBER = '2'
*
ERROR at line 2:
ORA-00933: SQL command not properly ended
SQL> CREATE VIEW B_2 AS
2 SELECT * FROM INVENT
3 WHERE BRANCH_NUMBER = '2';
View created.
SQL> GRANT ALTER ON B_2 TO CHAMBERS;
GRANT ALTER ON B_2 TO CHAMBERS
*
ERROR at line 1:
ORA-02204: ALTER, INDEX, REFERENCE and EXECUTE not allowed for views
SQL> GRANT UPDATE ON B_2 TO CHAMBERS;
GRANT UPDATE ON B_2 TO CHAMBERS
*
ERROR at line 1:
ORA-01917: user or role 'CHAMBERS' does not exist
SQL> CREATE VIEW BRANCH2 AS
2 SELECT * FROM INVENT
3 WHERE BRANCH_NUMBER = '2'
4 GRANT UPDATE ON BRANCH2 TO CHAMBERS;
GRANT UPDATE ON BRANCH2 TO CHAMBERS
*
ERROR at line 4:
ORA-00933: SQL command not properly ended
SQL> drop view b_2
2 ;
View dropped.
SQL> CREATE VIEW B_2 AS
2 SELECT * FROM SYSVIEWS
3 WHERE BRANCH_NUMBER = '2'
4 GRANT UPDATE ON B_2 TO CHAMBER;
GRANT UPDATE ON B_2 TO CHAMBER
*
ERROR at line 4:
ORA-00933: SQL command not properly ended
SQL> GRANT UPDATE ON SYSVIEWS
2 WHERE BRANCH_NUMBER = '2';
WHERE BRANCH_NUMBER = '2'
*
ERROR at line 2:
ORA-00905: missing keyword
SQL> GRANT UPDATE ON SYSVIEWS TO CHAMBERS
2 WHERE BRANCH_NUMBER = '2';
WHERE BRANCH_NUMBER = '2'
*
ERROR at line 2:
ORA-00933: SQL command not properly ended
+++++++++++++++++++++++++++++++++++++++++++++++++++
this stuff really sucks
+++++++++++++++++++++++++++++++++++++++++++++++++++
session #5
SQL> DROP INDEX BOOKIND3;
DROP INDEX BOOKIND3
*
ERROR at line 1:
ORA-01418: specified index does not exist
SQL> CREATE INDEX BOOKIND ON BOOK (BOOK_TITLE);
Index created.
SQL> CREATE INDEX BOOKIND2 ON BOOK (BOOK_TYPE);
Index created.
SQL> CREATE INDEX BOOKIND3 ON PUBLISHER (PUBLISHER_CODE, PUBLISHER_NAME)
2
SQL>
SQL> CREATE INDEX BOOKIND3 ON PUBLISHER
2 (PUBLISHER_CODE, PUBLISHER_NAME DESC);
Index created.
SQL> DESCRIBE BOOKIND3;
Object does not exist.
SQL> SELECT * FROM BOOKIND3;
SELECT * FROM BOOKIND3
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> DROP INDEX BOOKIND3;
Index dropped.
SQL> SELECT TNAME
2 FROM SYSTABLES
3 WHERE OWNER = 'BOYDL'
4
SQL> SELECT CNAME
2 FROM SYSCOLUMNS
3 WHERE OWNER = 'BOYDL'
4 AND CNAME = 'BOOK_CODE' OR 'BRANCH_NUMBER'
5 ORDER BY TNAME;
ORDER BY TNAME
*
ERROR at line 5:
ORA-00920: invalid relational operator
SQL> SELECT TNAME, CNAME
2 FROM SYSCOLUMNS
3 WHERE OWNER = 'BOYDL'
4 AND CNAME = 'BOOK_CODE' OR 'BRANCH_NUMBER'
5 ORDER BY TNAME;
ORDER BY TNAME
*
ERROR at line 5:
ORA-00920: invalid relational operator
SQL> SELECT TNAME, CNAME
2 FROM SYSCOLUMNS
3 WHERE OWNER = 'BOYDL'
4 AND CNAME = 'BOOK_CODE'
5 ORDER BY TNAME;
WHERE OWNER = 'BOYDL'
*
ERROR at line 3:
ORA-00904: invalid column name
SQL> SELECT TNAME, CNAME
2 FROM SYSCOLUMNS
3 WHERE CREATOR = 'BOYDL'
4 AND CNAME = 'BOOK_CODE' OR 'BRANCH_NUMBER'
5 ORDER BY TNAME;
ORDER BY TNAME
*
ERROR at line 5:
ORA-00920: invalid relational operator
SQL> SELECT TNAME, CNAME
2 FROM SYSCOLUMNS
3 WHERE CREATOR = 'BOYDL'
4 ;
SQL> SELECT TNAME, CNAME
2 FROM SYSCOLUMNS
3 WHERE CREATOR = 'BOYDL'
4 AND CNAME = 'BOOK_CODE' OR 'BRANCH_NUMBER' 2 3 4
5
SQL> SELECT TNAME, CNAME
2 FROM SYSCOLUMNS
3 WHERE CREATOR = 'BOYDL'
4 AND CNAME = 'BOOK_CODE' OR 'BRANCH_NUMBER';
AND CNAME = 'BOOK_CODE' OR 'BRANCH_NUMBER'
*
ERROR at line 4:
ORA-00920: invalid relational operator
SQL> SELECT TNAME, CNAME
2 FROM SYSCOLUMNS
3 WHERE CREATOR = 'BOYDL'
4 AND CNAME = 'BOOK_CODE';
TNAME CNAME
------------------------------ ------------------------------
BANTAM BOOK_CODE
BOOK BOOK_CODE
INVENT BOOK_CODE
WROTE BOOK_CODE
SQL> SELECT TNAME, CNAME
2 FROM SYSCOLUMNS
3 WHERE CREATOR = 'BOYDL'
4 AND CNAME = 'BOOK_CODE', 'BRANCH_NUMBER';
AND CNAME = 'BOOK_CODE', 'BRANCH_NUMBER'
*
ERROR at line 4:
ORA-00933: SQL command not properly ended
SQL> SELECT TNAME, CNAME
2 FROM SYSCOLUMNS
3 WHERE CREATOR = 'BOYDL'
4 AND CNAME = 'BOOK_CODE'
5 OR CNAME = 'BRANCH_NUMBER';
TNAME CNAME
------------------------------ ------------------------------
BRANCH BRANCH_NUMBER
WROTE BOOK_CODE
INVENT BOOK_CODE
INVENT BRANCH_NUMBER
BOOK BOOK_CODE
BANTAM BOOK_CODE
VALUE BRANCH_NUMBER
7 rows selected.
SQL> SELECT TNAME, CNAME
2 FROM SYSCOLUMNS
3 WHERE CREATOR = 'BOYDL'
4 AND CNAME = 'BOOK_CODE'
5 OR CNAME = 'BRANCH_NUMBER'
6 ORDER BY TNAME;
TNAME CNAME
------------------------------ ------------------------------
BANTAM BOOK_CODE
BOOK BOOK_CODE
BRANCH BRANCH_NUMBER
INVENT BOOK_CODE
INVENT BRANCH_NUMBER
VALUE BRANCH_NUMBER
WROTE BOOK_CODE
7 rows selected.
SQL> SELECT TNAME, CNAME, CREATOR
2 FROM SYSCOLUMNS
3 WHERE CREATOR = 'BOYDL'
4 AND CNAME = 'BOOK_CODE'
5 OR CNAME = 'BRANCH_NUMBER'
6 ORDER BY TNAME;
TNAME CNAME
------------------------------ ------------------------------
CREATOR
------------------------------
BANTAM BOOK_CODE
BOYDL
BOOK BOOK_CODE
BOYDL
BRANCH BRANCH_NUMBER
BOYDL
TNAME CNAME
------------------------------ ------------------------------
CREATOR
------------------------------
INVENT BOOK_CODE
BOYDL
INVENT BRANCH_NUMBER
BOYDL
VALUE BRANCH_NUMBER
BOYDL
TNAME CNAME
------------------------------ ------------------------------
CREATOR
------------------------------
WROTE BOOK_CODE
BOYDL
7 rows selected.
SQL> SELECT CNAME, COLTYPE
2 FROM SYSTABLES
3 WHERE TNAME = 'PUBLISHER';
FROM SYSTABLES
*
ERROR at line 2:
ORA-00942: table or view does not exist
SQL> SELECT CNAME, COLTYPE
2 FROM SYSCOLUMNS
3 WHERE TNAME = 'PUBLISHER';
CNAME COLTYPE
------------------------------ ---------
PUBLISHER_CODE UNDEFINED
PUBLISHER_NAME UNDEFINED
PUBLISHER_CITY UNDEFINED
PUBLISHER_STATE UNDEFINED
SQL> --c) List every table that contains a column named PUBLISHER_CODE
SQL>
SQL> SELECT TNAME, CNAME
2 FROM SYSCOLUMNS
3 WHERE CNAME = 'PUBLISHER_CODE';
TNAME CNAME
------------------------------ ------------------------------
PUBLISHER PUBLISHER_CODE
BOOK PUBLISHER_CODE
HARDBACK PUBLISHER_CODE
SQL> --d) List the name of every view that you own
SQL>
SQL> SELECT VIEWNAME
2 FROM SYSVIEWS
3 WHERE CREATOR = 'BOYDL';
WHERE CREATOR = 'BOYDL'
*
ERROR at line 3:
ORA-00904: invalid column name
SQL> SELECT VIEWNAME
2 FROM SYSVIEWS
3 WHERE VCREATOR = 'BOYDL';
VIEWNAME
------------------------------
BANTAM
CUSTORD
HARDBACK
HOUSEWARES
SMALLCST
SMLCUST
VALUE
7 rows selected.
SQL> --e) List the table name, column name, and data types for the
columns named
BOOK_CODE, BOOK_TITLE, and BOOK_PRICE. Order the results by column
name.
SQL>
SQL> SELECT TNAME, CNAME, COLTYPE
2 FROM SYSCOLUMNS
3 WHERE CNAME = 'BOOK_CODE'
4 AND CNAME = 'BOOK_TITLE'
5 AND CNAME = 'BOOK_PRICE'
6 ORDER BY CNAME;
no rows selected
SQL> SELECT TNAME, CNAME, COLTYPE
2 FROM SYSCOLUMNS
3 WHERE CNAME = 'BOOK_CODE';
TNAME CNAME COLTYPE
------------------------------ ------------------------------ ---------
WROTE BOOK_CODE UNDEFINED
INVENT BOOK_CODE UNDEFINED
BOOK BOOK_CODE UNDEFINED
BANTAM BOOK_CODE UNDEFINED
SQL> SELECT TNAME, CNAME, COLTYPE
2 FROM SYSCOLUMNS
3 WHERE CNAME = 'BOOK_CODE'
4 OR CNAME = 'BOOK_TITLE'
5 OR CNAME = 'BOOK_PRICE'
6 ORDER BY CNAME;
TNAME CNAME COLTYPE
------------------------------ ------------------------------ ---------
WROTE BOOK_CODE UNDEFINED
INVENT BOOK_CODE UNDEFINED
BOOK BOOK_CODE UNDEFINED
BANTAM BOOK_CODE UNDEFINED
BOOK BOOK_PRICE NUMBER
HARDBACK BOOK_PRICE NUMBER
BANTAM BOOK_PRICE NUMBER
BOOK BOOK_TITLE UNDEFINED
HARDBACK BOOK_TITLE UNDEFINED
BANTAM BOOK_TITLE UNDEFINED
10 rows selected.
SQL> SELECT TNAME, CNAME, COLTYPE
2 FROM SYSCOLUMNS
3 WHERE CNAME = 'BOOK_CODE'
4 OR CNAME = 'BOOK_TITLE'
5 OR CNAME = 'BOOK_PRICE'
6 ORDER BY CNAME, TNAME;
TNAME CNAME COLTYPE
------------------------------ ------------------------------ ---------
BANTAM BOOK_CODE UNDEFINED
BOOK BOOK_CODE UNDEFINED
INVENT BOOK_CODE UNDEFINED
WROTE BOOK_CODE UNDEFINED
BANTAM BOOK_PRICE NUMBER
BOOK BOOK_PRICE NUMBER
HARDBACK BOOK_PRICE NUMBER
BANTAM BOOK_TITLE UNDEFINED
BOOK BOOK_TITLE UNDEFINED
HARDBACK BOOK_TITLE UNDEFINED
10 rows selected.
SQL> --a) Book types must be PSY, FIC, HOR, MYS, ART, POE, SUS, SFI,
MUS, or CS
SQL>
SQL> ALTER TABLE BOOK
2 ADD CHECK (BOOK_TYPE IN
('SFI','PSY','FIC','HOR','MYS','ART','POE',
3 'SUS','MUS','SC') );
ADD CHECK (BOOK_TYPE IN ('SFI','PSY','FIC','HOR','MYS','ART','POE',
*
ERROR at line 2:
ORA-02293: cannot add check constraint - found noncomplying values
SQL> ALTER TABLE BOOK
2 ADD CHECK (BOOK_TYPE IN
('SFI','PSY','FIC','HOR','MYS','ART','POE',
3 'SUS','MUS','CS') );
Table altered.
SQL> --b) The PAPERBACK column can accept only values of Y or N
SQL>
SQL> ALTER TABLE BOOK
2 ADD CHECK (PAPERBACK IN ('Y','N') );
Table altered.
SQL> --c) The only branch numbers are 1, 2, 3, and 4.
SQL>
SQL> ALTER TABLE BRANCH
2 ADD CHECK (BRANCH_NUMBER IN ('1','2','3','4') );
Table altered.
SQL> --d) The only sequence numbers are 1 and 2.
SQL>
SQL> ALTER TABLE WROTE
2 ADD CHECK (SEQUENCE_NUMBER IN ('1','2') );
Table altered.
SQL> --a) Publisher code is a foreign key in the BOOK table
SQL>
SQL> ALTER TABLE BOOK
2 ADD FOREIGN KEY (PUBLISHER_CODE);
ADD FOREIGN KEY (PUBLISHER_CODE)
*
ERROR at line 2:
ORA-00905: missing keyword
SQL> ALTER TABLE BOOK
2 ADD FOREIGN KEY (PUBLISHER_CODE) REFERENCES PUBLISHER;
Table altered.
SQL> ALTER TABLE BOOK
2 ADD FOREIGN KEY (PUBLISHER_CODE) REFERENCES BOOK;
ADD FOREIGN KEY (PUBLISHER_CODE) REFERENCES BOOK
*
ERROR at line 2:
ORA-02256: number, type and size of referencing columns must match
referenced
columns
SQL> ALTER TABLE PUBLISHER
2 ADD FOREIGN KEY (PUBLISHER_CODE) REFERENCES BOOK;
ADD FOREIGN KEY (PUBLISHER_CODE) REFERENCES BOOK
*
ERROR at line 2:
ORA-02256: number, type and size of referencing columns must match
referenced
columns
SQL> --b) Author number is a foreign key in the WROTE table
SQL>
SQL> ALTER TABLE WROTE
2 ADD FOREIGN KEY (AUTHOR_NUMBER) REFERENCES AUTHOR;
Table altered.
===================================================
CHAPTER 7
SQL> SELECT RTRIM(FIRST)||' '||RTRIM(LAST), STREET,
2 RTRIM(CITY)||', '||RTRIM(STATE)||' '||RTRIM(ZIP_CODE)
3 FROM BOYD_CUST;
RTRIM(FIRST)||''||RTR STREET
--------------------- ---------------
RTRIM(CITY)||','||RTRIM(STATE)||''||RTRIM(ZIP_CODE)
-------------------------------------------------------
Sally Adams 481 Oak
Lansing, MI 49224
Ann Samuels 215 Pete
Grant, MI 49219
Don Charles 48 College
Ira, MI 49034
RTRIM(FIRST)||''||RTR STREET
--------------------- ---------------
RTRIM(CITY)||','||RTRIM(STATE)||''||RTRIM(ZIP_CODE)
-------------------------------------------------------
Tom Daniels 914 Cherry
Kent, MI 48391
Al Williams 519 Watson
Grant, MI 49219
Sally Adams 16 Elm
Lansing, MI 49224
RTRIM(FIRST)||''||RTR STREET
--------------------- ---------------
RTRIM(CITY)||','||RTRIM(STATE)||''||RTRIM(ZIP_CODE)
-------------------------------------------------------
Tran Dinh 808 Ridge
Harper, MI 48421
Mara Galvez 512 Pine
Ada, MI 49441
Dan Martin 419 Chip
Grant, MI 49219
RTRIM(FIRST)||''||RTR STREET
--------------------- ---------------
RTRIM(CITY)||','||RTRIM(STATE)||''||RTRIM(ZIP_CODE)
-------------------------------------------------------
Mary Nelson 108 Pine
Ada, MI 49441
10 rows selected.
SQL> CREATE VIEW REPORT2 AS
2 SELECT RTRIM(FIRST)||' '||RTRIM(LAST), STREET,
3 RTRIM(CITY)||', '||RTRIM(STATE)||' '||RTRIM(ZIP_CODE)
4 FROM BOYD_CUST;
SELECT RTRIM(FIRST)||' '||RTRIM(LAST), STREET,
*
ERROR at line 2:
ORA-00998: must name this expression with a column alias
SQL> CREATE VIEW REPORT2 AS
2 SELECT FIRST, LAST, STREET,
3 CITY, STATE, ZIP_CODE
4 FROM BOYD_CUST;
View created.
SQL> CREATE VIEW REPORT2(NAME, ADDRESS, CITY) AS
2 SELECT
3 RTRIM(FIRST)||' '||RTRIM(LAST), STREET,
4 RTRIM(CITY)||', '||RTRIM(STATE)||' '||RTRIM(ZIP_CODE)
5 FROM BOYD_CUST;
CREATE VIEW REPORT2(NAME, ADDRESS, CITY) AS
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> DROP VIEW REPORT2;
View dropped.
SQL> CREATE VIEW REPORT2(NAME, ADDRESS, CITY) AS
2 SELECT
3 RTRIM(FIRST)||' '||RTRIM(LAST), STREET,
4 RTRIM(CITY)||', '||RTRIM(STATE)||' '||RTRIM(ZIP_CODE)
5 FROM BOYD_CUST;
View created.
SQL> COLUMN NAME HEADING 'CUSTOMER|NAME'
SQL> COLUMN ADDRESS HEADING 'CUSTOMER|ADDRESS'
SQL> COLUMN CITY HEADING 'CUSTOMER|CITY/STATE/ZIP'
SQL> SELECT * ^H
2 FROM REPOR
3
SQL> COLUMN NAME HEADING 'CUSTOMER|NAME'
SQL> COLUMN ADDRESS HEADING 'CUSTOMER|ADDRESS'
SQL> COLUMN CITY HEADING 'CUSTOMER|CITY/STATE/ZIP'
SQL> SELECT *
2 FROM REPORT2;
CUSTOMER CUSTOMER
NAME ADDRESS
--------------------- ---------------
CUSTOMER
CITY/STATE/ZIP
-------------------------------------------------------
Sally Adams 481 Oak
Lansing, MI 49224
Ann Samuels 215 Pete
Grant, MI 49219
Don Charles 48 College
CUSTOMER CUSTOMER
NAME ADDRESS
--------------------- ---------------
CUSTOMER
CITY/STATE/ZIP
-------------------------------------------------------
Ira, MI 49034
Tom Daniels 914 Cherry
Kent, MI 48391
Al Williams 519 Watson
Grant, MI 49219
CUSTOMER CUSTOMER
NAME ADDRESS
--------------------- ---------------
CUSTOMER
CITY/STATE/ZIP
-------------------------------------------------------
Sally Adams 16 Elm
Lansing, MI 49224
Tran Dinh 808 Ridge
Harper, MI 48421
CUSTOMER CUSTOMER
NAME ADDRESS
--------------------- ---------------
CUSTOMER
CITY/STATE/ZIP
-------------------------------------------------------
Mara Galvez 512 Pine
Ada, MI 49441
Dan Martin 419 Chip
Grant, MI 49219
Mary Nelson 108 Pine
CUSTOMER CUSTOMER
NAME ADDRESS
--------------------- ---------------
CUSTOMER
CITY/STATE/ZIP
-------------------------------------------------------
Ada, MI 49441
10 rows selected.
SQL> --COLUMN NAME HEADING 'CUSTOMER|NAME'
SQL> COLUMN ADDRESS HEADING 'CUSTOMER|ADDRESS'
SQL> COLUMN CITY HEADING 'CUSTOMER|CITY/STATE/ZIP'
SQL> SELECT *
2 FROM REPORT2;SQL> unknown command beginning "SQL> COLUM..." -
rest of line
ignored.
SQL> unknown command beginning "SQL> COLUM..." - rest of line ignored.
SQL> unknown command beginning "SQL> SELEC..." - rest of line ignored.
SQL> @REPORT2;
SQL>
SQL> @REPORT2
unable to open file "REPORT2.sql"
SQL> DESCRIBE REPORT2;
Name Null? Type
------------------------------- -------- ----
NAME VARCHAR2(21)
ADDRESS VARCHAR2(15)
CITY VARCHAR2(55)
SQL> SELECT *
2 FROM REPORT2
3 ORDER BY LAST;
ORDER BY LAST
*
ERROR at line 3:
ORA-00904: invalid column name
SQL> SELECT *
2 FROM REPORT2
3 ORDER BY 'CUSTOMER NAME';
CUSTOMER CUSTOMER
NAME ADDRESS
--------------------- ---------------
CUSTOMER
CITY/STATE/ZIP
-------------------------------------------------------
Sally Adams 481 Oak
Lansing, MI 49224
Ann Samuels 215 Pete
Grant, MI 49219
Don Charles 48 College
CUSTOMER CUSTOMER
NAME ADDRESS
--------------------- ---------------
CUSTOMER
CITY/STATE/ZIP
-------------------------------------------------------
Ira, MI 49034
Tom Daniels 914 Cherry
Kent, MI 48391
Al Williams 519 Watson
Grant, MI 49219
CUSTOMER CUSTOMER
NAME ADDRESS
--------------------- ---------------
CUSTOMER
CITY/STATE/ZIP
-------------------------------------------------------
Sally Adams 16 Elm
Lansing, MI 49224
Tran Dinh 808 Ridge
Harper, MI 48421
CUSTOMER CUSTOMER
NAME ADDRESS
--------------------- ---------------
CUSTOMER
CITY/STATE/ZIP
-------------------------------------------------------
Mara Galvez 512 Pine
Ada, MI 49441
Dan Martin 419 Chip
Grant, MI 49219
Mary Nelson 108 Pine
CUSTOMER CUSTOMER
NAME ADDRESS
--------------------- ---------------
CUSTOMER
CITY/STATE/ZIP
-------------------------------------------------------
Ada, MI 49441
10 rows selected.
SQL> CLEAR COLUMNS
columns cleared
SQL>
SQL> COLUMN NAME HEADING COLUMN NAME HEADING 'CUSTOMER|NAME'
SQL> COLUMN ADDRESS HEADING 'CUSTOMER|ADDRESS'
SQL> COLUMN CITY HEADING 'CUSTOMER|CITY/STATE/ZIP'unknown COLUMN
option "NAME"
SQL> unknown command beginning "SQL> COLUM..." - rest of line ignored.
SQL>
unknown command beginning "SQL> COLUM..." - rest of line ignored.
SQL> COLUMN NAME HEADING 'CUSTOMER|NAME'
SQL> COLUMN ADDRESS HEADING 'CUSTOMER|ADDRESS'
SQL> COLUMN CITY HEADING 'CUSTOMER|CITY/STATE/ZIP'
SQL>
SQL> COLUMN NAME HEADING 'CUSTOMER|NAME' FORMAT A25
SQL> COLUMN ADDRESS HEADING 'CUSTOMER|ADDRESS' FORMAT A25
SQL> COLUMN CITY HEADING 'CUSTOMER|CITY/STATE/ZIP' FORMAT A25;
SQL> /
CUSTOMER CUSTOMER CUSTOMER
NAME ADDRESS CITY/STATE/ZIP
------------------------- ------------------------- -------------------
------
Sally Adams 481 Oak Lansing, MI 49224
Ann Samuels 215 Pete Grant, MI 49219
Don Charles 48 College Ira, MI 49034
Tom Daniels 914 Cherry Kent, MI 48391
Al Williams 519 Watson Grant, MI 49219
Sally Adams 16 Elm Lansing, MI 49224
Tran Dinh 808 Ridge Harper, MI 48421
Mara Galvez 512 Pine Ada, MI 49441
Dan Martin 419 Chip Grant, MI 49219
Mary Nelson 108 Pine Ada, MI 49441
10 rows selected.
SQL> CREATE VIEW REPORT3 (NAME, CREDIT_LIMIT, BALANCE) AS
2 SELECT RTRIM(FIRST)||' '||RTRIM(LAST), CREDIT_LIMIT, BALANCE
3 FROM BOYD_CUST;
View created.
SQL> COLUMN NAME HEADING 'CUSTOMER|NAME'
SQL> COLUMN CREDIT_LIMIT HEADING 'CUSTOMER|CREDIT LIMT' FORMAT $9999.99
SQL> COLUMN BALANCE HEADING 'CUSTOMER|BALANCE' FORMAT $9999.99
SQL> /
CREATE VIEW REPORT3 (NAME, CREDIT_LIMIT, BALANCE) AS
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> SELECT *
2 FROM REPORT3;
CUSTOMER CUSTOMER CUSTOMER
NAME CREDIT LIMT BALANCE
------------------------- ----------- ---------
Sally Adams $1000.00 $818.75
Ann Samuels $1500.00 $21.50
Don Charles $1000.00 $825.75
Tom Daniels $750.00 $770.75
Al Williams $1500.00 $402.75
Sally Adams $2000.00 $1817.50
Tran Dinh $750.00 $402.40
Mara Galvez $1000.00 $114.60
Dan Martin $1000.00 $1045.75
Mary Nelson $1500.00 $98.75
10 rows selected.
SQL> CLEAR COLUMNS
columns cleared
SQL> DROP REPORT3
2 ;
DROP REPORT3
*
ERROR at line 1:
ORA-00950: invalid DROP option
SQL> DROP VIEW REPORT3;
View dropped.
SQL> CREATE VIEW REPORT3 (NAME, CREDIT_LIMIT, BALANCE) AS
2 SELECT RTRIM(FIRST)||' '||RTRIM(LAST), CREDIT_LIMIT, BALANCE
3 FROM BOYD_CUST;
View created.
SQL> COLUMN NAME HEADING 'CUSTOMER|NAME'
SQL> COLUMN CREDIT_LIMIT HEADING 'CUSTOMER|CREDIT LIMIT' FORMAT $9999.99
SQL> COLUMN BALANCE HEADING 'CUSTOMER|BALANCE' FORMAT $9999.99
SQL> SELECT *
2 FROM REPORT3;
CUSTOMER CUSTOMER CUSTOMER
NAME CREDIT LIMIT BALANCE
--------------------- ------------ ---------
Sally Adams $1000.00 $818.75
Ann Samuels $1500.00 $21.50
Don Charles $1000.00 $825.75
Tom Daniels $750.00 $770.75
Al Williams $1500.00 $402.75
Sally Adams $2000.00 $1817.50
Tran Dinh $750.00 $402.40
Mara Galvez $1000.00 $114.60
Dan Martin $1000.00 $1045.75
Mary Nelson $1500.00 $98.75
10 rows selected.
SQL> TTITLE 'CUSTOMER CREDIT LIMITS|AND BALANCES'
SQL> SELECT *
2 FROM REPORT3;
Fri May 22
page 1
CUSTOMER CREDIT LIMITS
AND BALANCES
CUSTOMER CUSTOMER CUSTOMER
NAME CREDIT LIMIT BALANCE
--------------------- ------------ ---------
Sally Adams $1000.00 $818.75
Ann Samuels $1500.00 $21.50
Don Charles $1000.00 $825.75
Tom Daniels $750.00 $770.75
Al Williams $1500.00 $402.75
Sally Adams $2000.00 $1817.50
Fri May 22
page 2
CUSTOMER CREDIT LIMITS
AND BALANCES
CUSTOMER CUSTOMER CUSTOMER
NAME CREDIT LIMIT BALANCE
--------------------- ------------ ---------
Tran Dinh $750.00 $402.40
Mara Galvez $1000.00 $114.60
Dan Martin $1000.00 $1045.75
Mary Nelson $1500.00 $98.75
10 rows selected.
SQL> SET LINESIZE 70
SQL> SELECT *
2 FROM REPORT3;
Fri May 22 page 1
CUSTOMER CREDIT LIMITS
AND BALANCES
CUSTOMER CUSTOMER CUSTOMER
NAME CREDIT LIMIT BALANCE
--------------------- ------------ ---------
Sally Adams $1000.00 $818.75
Ann Samuels $1500.00 $21.50
Don Charles $1000.00 $825.75
Tom Daniels $750.00 $770.75
Al Williams $1500.00 $402.75
Sally Adams $2000.00 $1817.50
Fri May 22 page 2
CUSTOMER CREDIT LIMITS
AND BALANCES
CUSTOMER CUSTOMER CUSTOMER
NAME CREDIT LIMIT BALANCE
--------------------- ------------ ---------
Tran Dinh $750.00 $402.40
Mara Galvez $1000.00 $114.60
Dan Martin $1000.00 $1045.75
Mary Nelson $1500.00 $98.75
10 rows selected.
SQL> SET FEEDBACK OFF
SQL> SELECT *
2 FROM REPORT3;
Fri May 22 page 1
CUSTOMER CREDIT LIMITS
AND BALANCES
CUSTOMER CUSTOMER CUSTOMER
NAME CREDIT LIMIT BALANCE
--------------------- ------------ ---------
Sally Adams $1000.00 $818.75
Ann Samuels $1500.00 $21.50
Don Charles $1000.00 $825.75
Tom Daniels $750.00 $770.75
Al Williams $1500.00 $402.75
Sally Adams $2000.00 $1817.50
Fri May 22 page 2
CUSTOMER CREDIT LIMITS
AND BALANCES
CUSTOMER CUSTOMER CUSTOMER
NAME CREDIT LIMIT BALANCE
--------------------- ------------ ---------
Tran Dinh $750.00 $402.40
Mara Galvez $1000.00 $114.60
Dan Martin $1000.00 $1045.75
Mary Nelson $1500.00 $98.75
SQL>
--------------------------------
SQL> CREATE VIEW REPSQUARED(SNUM, SNAME, CNUM, CNAME, BAL) AS
2 SELECT
3 SALESREP.SLSREP_NUMBER||'-'||RTRIM(SALESREP.FIRST)||' '||
4 RTRIM(SALESREP.LAST),
5 CUSTOMER_NUMBER||'-'||RTRIM(CUSTOMER.FIRST)||' '||
6 RTRIM(CUSTOMER.LAST),
7 BALANCE
8
SQL> SELECT *
2 FROM CNAME
3 WHERE CREATOR = 'BOYDL';
FROM CNAME
*
ERROR at line 2:
ORA-00942: table or view does not exist
------------------------------------------
SQL> CREATE VIEW REPSQUARED(SNUM, SNAME, CNUM, CNAME, BAL) AS
2 SELECT
3 SALESREP.SLSREP_NUMBER||'-'||RTRIM(SALESREP.FIRST)||' '||
4 RTRIM(SALESREP.LAST),
5 CUSTOMER_NUMBER||'-'||RTRIM(CUSTOMER.FIRST)||' '||
6 RTRIM(CUSTOMER.LAST),
7 BALANCE
8 FROM BOYD_CUST;
RTRIM(CUSTOMER.LAST),
*
ERROR at line 6:
ORA-00904: invalid column name
SQL> CREATE VIEW REPSQUARED(SNUM, SNAME, CNUM, CNAME, BAL) AS
2 SELECT
3 SALESREP.SLSREP_NUMBER||'-'||RTRIM(SALESREP.FIRST)||' '||
4
SQL> CREATE VIEW REPSQUARED(SNUM, SNAME, CNUM, CNAME, BAL) AS
2 SELECT
3 BOYD_SALESREP.SLSREP_NUMBER||'-'||RTRIM(BOYD_SALESREP.FIRST)||' '||
4 RTRIM(BOYD_SALESREP.LAST),
5 CUSTOMER_NUMBER||'-'||RTRIM(BOYD_CUST.FIRST)||' '||
6 RTRIM(BOYD_CUST.LAST),
7 BALANCE
8 FROM BOYD_CUST, BOYD_SALESREP;
CREATE VIEW REPSQUARED(SNUM, SNAME, CNUM, CNAME, BAL) AS
*
ERROR at line 1:
ORA-01730: invalid number of column names specified
SQL> CREATE VIEW REPSQUARED
2 SELECT
3 BOYD_SALESREP.SLSREP_NUMBER||'-'||RTRIM(BOYD_SALESREP.FIRST)||' '||
4 RTRIM(BOYD_SALESREP.LAST),
5 CUSTOMER_NUMBER||'-'||RTRIM(BOYD_CUST.FIRST)||' '||
6 RTRIM(BOYD_CUST.LAST),
7 BALANCE
8 FROM BOYD_CUST, BOYD_SALESREP;
SELECT
*
ERROR at line 2:
ORA-00905: missing keyword
SQL> CREATE VIEW REPSQUARED AS
2 SELECT
3 BOYD_SALESREP.SLSREP_NUMBER||'-'||RTRIM(BOYD_SALESREP.FIRST)||' '||
4 RTRIM(BOYD_SALESREP.LAST),
5 CUSTOMER_NUMBER||'-'||RTRIM(BOYD_CUST.FIRST)||' '||
6 RTRIM(BOYD_CUST.LAST),
7 BALANCE
8 FROM BOYD_CUST, BOYD_SALESREP;
BOYD_SALESREP.SLSREP_NUMBER||'-'||RTRIM(BOYD_SALESREP.FIRST)||' '||
*
ERROR at line 3:
ORA-00998: must name this expression with a column alias
SQL> CREATE VIEW REPSQUARED(SNUM, SNAME, CNUM, CNAME, BAL) AS
2 SELECT
3 BOYD_SALESREP.SLSREP_NUMBER||'-'||RTRIM(BOYD_SALESREP.FIRST)||' '||
4 RTRIM(BOYD_SALESREP.LAST),
5 CUSTOMER_NUMBER||'-'||RTRIM(BOYD_CUST.FIRST)||' '||
6 RTRIM(BOYD_CUST.LAST),
7 BALANCE
8 FROM BOYD_CUST, BOYD_SALESREP; 2 3 4 5 6 7 8
2 SELECT
*
ERROR at line 2:
ORA-00928: missing SELECT keyword
SQL> CREATE VIEW REPSQUARED(SNUM, SNAME, CNUM, CNAME, BAL) AS
2 SELECT
3 BOYD_SALESREP.SLSREP_NUMBER||'-'||RTRIM(BOYD_SALESREP.FIRST)||' '||
4 RTRIM(BOYD_SALESREP.LAST),
5 CUSTOMER_NUMBER||'-'||RTRIM(BOYD_CUST.FIRST)||' '||
6 RTRIM(BOYD_CUST.LAST),
7 BALANCE
8 FROM BOYD_CUST, BOYD_SALESREP
9 WHERE BOYD_SALESREP.SLSREP_NUMBER = BOYD_CUST.SLSREP_NUMBER;
CREATE VIEW REPSQUARED(SNUM, SNAME, CNUM, CNAME, BAL) AS
*
ERROR at line 1:
ORA-01730: invalid number of column names specified
SQL> CREATE VIEW REPSQUARED(SNUM, SNAME, CNUM, CNAME, BAL) AS
2 SELECT
3 BOYD_SALESREP.SLSREP_NUMBER,
4 RTRIM(BOYD_SALESREP.FIRST)||' '||RTRIM(BOYD_SALESREP.LAST),
5 CUSTOMER_NUMBER,
6 RTRIM(BOYD_CUST.FIRST)||' '||RTRIM(BOYD_CUST.LAST),
7 BALANCE
8 FROM BOYD_CUST, BOYD_SALESREP
9 WHERE BOYD_SALESREP.SLSREP_NUMBER = BOYD_CUST.SLSREP_NUMBER;
View created.
SQL> SELECT *
2 FROM REPSQUARED
3 ORDER BY SLSREP_NUMBER'
4 ;
ORDER BY SLSREP_NUMBER'
*
ERROR at line 3:
ORA-01756: quoted string not properly terminated
SQL> SELECT *
2 FROM REPSQUARED
3 ORDER BY SLSREP_NUMBER;
ORDER BY SLSREP_NUMBER
*
ERROR at line 3:
ORA-00904: invalid column name
SQL> DESCRIBE REPSQUARED;
Name Null? Type
------------------------------- -------- ----
SNUM NOT NULL NUMBER(2)
SNAME VARCHAR2(21)
CNUM CHAR(3)
CNAME VARCHAR2(21)
BAL NUMBER(8,2)
SQL> SELECT *
2 FROM REPSQUARED
3 ORDER BY SNUM;
SNUM SNAME CNU CNAME BAL
---------- --------------------- --- --------------------- ----------
3 Mary Jones 124 Sally Adams 818.75
3 Mary Jones 412 Sally Adams 1817.5
3 Mary Jones 622 Dan Martin 1045.75
6 William Smith 256 Ann Samuels 21.5
6 William Smith 315 Tom Daniels 770.75
6 William Smith 587 Mara Galvez 114.6
6 William Smith 567 Tran Dinh 402.4
12 Miguel Diaz 311 Don Charles 825.75
12 Miguel Diaz 522 Mary Nelson 98.75
12 Miguel Diaz 405 Al Williams 402.75
10 rows selected.
SQL> select *
2 f
3
SQL> SELECT *
2 FROM REPSQUARED
3 ORDER BY SNUM, CNUM;
SNUM SNAME CNU CNAME BAL
---------- --------------------- --- --------------------- ----------
3 Mary Jones 124 Sally Adams 818.75
3 Mary Jones 412 Sally Adams 1817.5
3 Mary Jones 622 Dan Martin 1045.75
6 William Smith 256 Ann Samuels 21.5
6 William Smith 315 Tom Daniels 770.75
6 William Smith 567 Tran Dinh 402.4
6 William Smith 587 Mara Galvez 114.6
12 Miguel Diaz 311 Don Charles 825.75
12 Miguel Diaz 405 Al Williams 402.75
12 Miguel Diaz 522 Mary Nelson 98.75
10 rows selected.
SQL> CLEAR COLUMNS
columns cleared
SQL> COLUMN SNUM HEADING FORMAT A8
unknown COLUMN option "A8"
SQL> COLUMN SNUM HEADING 'SNUM' FORMAT A10
SQL> /
SNUM SNAME CNU CNAME BAL
---------- --------------------- --- --------------------- ----------
########## Mary Jones 124 Sally Adams 818.75
########## Mary Jones 412 Sally Adams 1817.5
########## Mary Jones 622 Dan Martin 1045.75
########## William Smith 256 Ann Samuels 21.5
########## William Smith 315 Tom Daniels 770.75
########## William Smith 567 Tran Dinh 402.4
########## William Smith 587 Mara Galvez 114.6
########## Miguel Diaz 311 Don Charles 825.75
########## Miguel Diaz 405 Al Williams 402.75
########## Miguel Diaz 522 Mary Nelson 98.75
10 rows selected.
SQL> COLUMNS CLEARED
unknown command beginning "COLUMNS CL..." - rest of line ignored.
SQL> SELECT *
2 FROM REPSQUARED
3 ORDER BY SNUM, CNUM;
SNUM SNAME CNU CNAME BAL
---------- --------------------- --- --------------------- ----------
########## Mary Jones 124 Sally Adams 818.75
########## Mary Jones 412 Sally Adams 1817.5
########## Mary Jones 622 Dan Martin 1045.75
########## William Smith 256 Ann Samuels 21.5
########## William Smith 315 Tom Daniels 770.75
########## William Smith 567 Tran Dinh 402.4
########## William Smith 587 Mara Galvez 114.6
########## Miguel Diaz 311 Don Charles 825.75
########## Miguel Diaz 405 Al Williams 402.75
########## Miguel Diaz 522 Mary Nelson 98.75
10 rows selected.
SQL> COLUMNS CLEAR
unknown command beginning "COLUMNS CL..." - rest of line ignored.
SQL> CLEAR COLUMNS
columns cleared
SQL> ROLLBACK;
Rollback complete.
SQL> SELECT *
2 FROM REPSQUARED
3 ORDER BY SNUM, CNUM;
SNUM SNAME CNU CNAME BAL
---------- --------------------- --- --------------------- ----------
3 Mary Jones 124 Sally Adams 818.75
3 Mary Jones 412 Sally Adams 1817.5
3 Mary Jones 622 Dan Martin 1045.75
6 William Smith 256 Ann Samuels 21.5
6 William Smith 315 Tom Daniels 770.75
6 William Smith 567 Tran Dinh 402.4
6 William Smith 587 Mara Galvez 114.6
12 Miguel Diaz 311 Don Charles 825.75
12 Miguel Diaz 405 Al Williams 402.75
12 Miguel Diaz 522 Mary Nelson 98.75
10 rows selected.
SQL> COLUMN BAL HEADING 'BAL' FORMAT $9999.99
SQL> /
SNUM SNAME CNU CNAME BAL
---------- --------------------- --- --------------------- ---------
3 Mary Jones 124 Sally Adams $818.75
3 Mary Jones 412 Sally Adams $1817.50
3 Mary Jones 622 Dan Martin $1045.75
6 William Smith 256 Ann Samuels $21.50
6 William Smith 315 Tom Daniels $770.75
6 William Smith 567 Tran Dinh $402.40
6 William Smith 587 Mara Galvez $114.60
12 Miguel Diaz 311 Don Charles $825.75
12 Miguel Diaz 405 Al Williams $402.75
12 Miguel Diaz 522 Mary Nelson $98.75
10 rows selected.
SQL> CLEAR COLUMNS
columns cleared
SQL> LINESIZE 70
unknown command beginning "LINESIZE 7..." - rest of line ignored.
SQL> SET LINESIZE 70
SQL> TTITLE 'SALES REP REPORT'
SQL> BREAK ON REPORT ON SNUM SKIP 1
SQL> SET FEEDBACK OFF
SQL> COMPUTE SUM OF BAL ON SNUM
SQL> SET PAGESIZE 66
SQL> SET PAUSE OFF
SQL> /
Fri May 22 page 1
SALES REP REPORT
SNUM SNAME CNU CNAME BAL
---------- --------------------- --- --------------------- ----------
3 Mary Jones 124 Sally Adams 818.75
Mary Jones 412 Sally Adams 1817.5
Mary Jones 622 Dan Martin 1045.75
********** ----------
sum 3682
6 William Smith 256 Ann Samuels 21.5
William Smith 315 Tom Daniels 770.75
William Smith 567 Tran Dinh 402.4
William Smith 587 Mara Galvez 114.6
********** ----------
sum 1309.25
12 Miguel Diaz 311 Don Charles 825.75
Miguel Diaz 405 Al Williams 402.75
Miguel Diaz 522 Mary Nelson 98.75
********** ----------
sum 1327.25
SQL> COLUMN BAL HEADING 'BAL' FORMAT $9999.99
SQL> /
Fri May 22 page 1
SALES REP REPORT
SNUM SNAME CNU CNAME BAL
---------- --------------------- --- --------------------- ---------
3 Mary Jones 124 Sally Adams $818.75
Mary Jones 412 Sally Adams $1817.50
Mary Jones 622 Dan Martin $1045.75
********** ---------
sum $3682.00
6 William Smith 256 Ann Samuels $21.50
William Smith 315 Tom Daniels $770.75
William Smith 567 Tran Dinh $402.40
William Smith 587 Mara Galvez $114.60
********** ---------
sum $1309.25
12 Miguel Diaz 311 Don Charles $825.75
Miguel Diaz 405 Al Williams $402.75
Miguel Diaz 522 Mary Nelson $98.75
********** ---------
sum $1327.25
THE END
If you have comments or suggestions, email me at Leanne@refuge-earth.org
All works on this site are protected, copyright ©1984 - 1999