Jump to content

add user and table to script


mckenzie
 Share

Recommended Posts

hi there i've been given a task to change the following script to create a new user, dev1 with password dev1 who has unlimited access. I have to create another table called university that has ID, name, address, number of students, date of frst contact! i have been trying but not getting anywhere. it seems fairly simple to do by copy and apstgin the original script and changing the names.the underlined code is what i attempted. i try the user thing but fail.

spool dbcre8.logconnect system/managerdrop user ora1 cascade;drop user ora2 cascade;create user ora1 identified by ora1;grant create session to ora1;grant create table to ora1;grant create view to ora1;grant create sequence to ora1;grant create synonym to ora1;grant create procedure to ora1;grant create trigger to ora1;grant create cluster to ora1;grant create type to ora1;alter user ora1 quota unlimited on system;drop user ora2 cascade;create user ora2 identified by ora2;grant create session to ora2;grant create table to ora2;grant create view to ora2;grant create sequence to ora2;grant create synonym to ora2;grant create procedure to ora2;grant create trigger to ora2;grant create type to ora2;grant create cluster to ora2;alter user ora2 quota unlimited on system;connect ora1/ora1CREATE OR REPLACE TYPE address_type AS OBJECT (street		VARCHAR2(25),city		VARCHAR2(25),country	VARCHAR2(20) )/CREATE TABLE person_address OF address_type;CREATE TABLE student (	student_id      	number(4)       not null,	student_fname   	varchar2(10)	not null,	student_lname   	varchar2(12)	not null,	address 		REF address_type SCOPE IS person_address,	tel_no          	varchar2(15),	fax_no          	number(15),	gender       		varchar2(1),	date_of_birth   	date,	student_desc            varchar2(250),	preferred_language      varchar2(2),	passport_program        varchar2(1),	company_id		number(2)       not null);CREATE TABLE company (	company_id      number(2)       not null,	company_name    varchar2(15),	city            varchar2(12),	industry        varchar2(15),	no_employees    number(5),	revenue         number(10),	training_budget number(6));CREATE TABLE instructor (	instructor_id   number(3)       not null,	instructor_name varchar2(15)	not null,	address 	REF address_type SCOPE IS person_address,	salary          number(6,2),	commission      number(6,2),	mentor_id       number(3),	date_hired      date,	profile		CLOB);CREATE TABLE course (	course_id       number(3)       not null,	title           varchar2(30),	duration        number(2),	cost            number(5),	subject_id      number(2),	brochure	bfile);CREATE TABLE authorization (	instructor_id   number(3)       not null,	course_id       number(3)       not null,	auth_date       date);CREATE TABLE subject_area (	subject_id      number(2)       not null,	subject_name    varchar2(30));CREATE TABLE offering (	offering_id     number(5)       not null,	course_id       number(3)       not null,	site_id         number(2)       not null,	instructor_id   number(4),	start_date      date,	max_no_students number(2));[u]CREATE TABLE university (                university_id  number(5)         not null,                name            varchar(20),                        number_of_students      number(6),                       date_of_first_contact      date,);[/u]CREATE TYPE classroom_type AS OBJECT(room_number NUMBER(2) ,capacity NUMBER(2) ,description VARCHAR2(200));/CREATE TYPE classroom_varray_type AS VARRAY(50) OF classroom_type;/CREATE TABLE site (	site_id         number(2)       not null,	address 	address_type,	classroom	classroom_varray_type,	location        varchar2(12));CREATE TABLE attendance (	offering_id     number(5)       not null,	student_id      number(4)       not null,	evaluation      varchar2(1),	amount_paid     number(6,2));CREATE OR REPLACE TYPE invoice_item_type AS OBJECT(student_lname		VARCHAR2(12) ,course_title		VARCHAR2(30) ,start_date		DATE ,end_date		DATE ,amount			NUMBER(7,2) );/CREATE TYPE invoice_item_table_type AS TABLE OF invoice_item_type;/CREATE TABLE invoice(invoice_number	NUMBER(6) NOT NULL,company_id		NUMBER(2) NOT NULL,billing_date		DATE NOT NULL,due_date		DATE,invoice_item		invoice_item_table_type)NESTED TABLE invoice_item STORE AS invoice_item_table;insert into person_address (street, city, country)values ('111 BAY STATE ROAD', 'BOSTON', 'USA');insert into person_address (street, city, country)values ('111 VALLEY WAY', 'SAN FERNANDO', 'USA');insert into person_address (street, city, country)values ('4545 ORACLE DRIVE', 'SAN FRANCISCO', 'USA');insert into person_address (street, city, country)values ('22 GOLDEN GATE DRIVE', 'SAN FRANCISCO', 'USA');insert into person_address (street, city, country)values ('77 ELM STREET', 'NEWARK', 'USA');insert into person_address (street, city, country)values ('544 42ND STREET', 'NEW YORK', 'USA');insert into person_address (street, city, country)values ('111 BEACHCOMBER PLACE', 'OCEANSIDE', 'USA');insert into person_address (street, city, country)values ('25 MISSION WAY', 'SAN DIEGO', 'USA');insert into person_address (street, city, country)values ('177 AIRPORT ROAD', 'NEWARK', 'USA');insert into person_address (street, city, country)values ('5567 KNIGHTSBRIDGE COURT', 'LONDON', 'UK');insert into person_address (street, city, country)values ('111 CRUISING WAY', 'TORTOLA', 'BVI');insert into person_address (street, city, country)values ('45 KING STREET', 'OTTAWA', 'CANADA');insert into person_address (street, city, country)values ('45555 BIG STREET', 'DALLAS', 'USA');insert into person_address (street, city, country)values ('AVENUE CHAMPS-ELYSEES 456', 'PARIS', 'FRANCE');insert into person_address (street, city, country)values ('562 HOCKEY STREET', 'TORONTO', 'CANADA');insert into person_address (street, city, country)values ('4242 MISTY LANE', 'SEATTLE', 'USA');insert into person_address (street, city, country)values ('KUNGSGATAN 56', 'STOCKHOLM', 'SWEDEN');insert into person_address (street, city, country)values ('11-882 KAMEHAMEHA HIGHWAY', 'HAWAII', 'USA');insert into person_address (street, city, country)values ('333 SAN JUAN BLVD', 'SAN JUAN', 'PUERTO RICO');insert into person_address (street, city, country)values ('7500 IMPERIAL BLVD', 'LOS ANGELES', 'USA');insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (1111, 'DIANE', 'BROWN', '(617)342-2345', 6173422345, 'F', '01-FEB-1964', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='111 BAY STATE ROAD')where student_id = 1111;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (2299, 'CHRIS', 'ADAMS', '(213)334-2789', 2133342789, 'M', '22-MAR-1965', 'EN', 30);update student set address = (select ref(x) from person_address xwhere x.street='7500 IMPERIAL BLVD')where student_id = 2299;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (4568, 'KEVIN', 'COX', '(619)433-6845', 6194336845, 'M', '04-DEC-1963', 'EN', 10);update student set address = (select ref(x) from person_address xwhere x.street='25 MISSION WAY')where student_id = 4568;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (5556, 'JOHN', 'TYLER', '(212)444-9769', 2124449769, 'M', '13-JUN-1966', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='544 42ND STREET')where student_id = 5556;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6874, 'NANCY', 'GIBBS', '(714)346-2896', 7143462896, 'F', '15-JUL-1965', 'EN', 10);update student set address = (select ref(x) from person_address xwhere x.street='111 VALLEY WAY')where student_id = 6874;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6789, 'JENNY', 'ROSE', '(415)334-2345', 4153342345, 'F', '16-SEP-1963', 'SW', 10);update student set address = (select ref(x) from person_address xwhere x.street='4545 ORACLE DRIVE')where student_id = 6789;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (1199, 'FRANK', 'NELSON', '(609)345-2346', 6093452346, 'M', '22-APR-1964', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='77 ELM STREET')where student_id = 1199;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6263, 'KEN', 'CRICK', '(415)345-2313', 4153452313, 'M', '14-MAR-1965', 'EN', 50);update student set address = (select ref(x) from person_address xwhere x.street='22 GOLDEN GATE DRIVE')where student_id = 6263;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (3452, 'NIGEL', 'TURNER', '(818)352-2511', 8183522511, 'M', '16-FEB-1962', 'SW', 10);update student set address = (select ref(x) from person_address xwhere x.street='111 BEACHCOMBER PLACE')where student_id = 3452;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (2346, 'CARL', 'DUDLEY', '(609)223-7890', 6092237890, 'M', '28-NOV-1967', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='5567 KNIGHTSBRIDGE COURT')where student_id = 2346;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (9842, 'MIKE', 'COOPER', '(713)726-9842', 7137269842, 'M', '18-OCT-1962', 'EN', 50);update student set address = (select ref(x) from person_address xwhere x.street='562 HOCKEY STREET')where student_id = 9842;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6901, 'LILY', 'GREEN', '(809)342-9621', 8093429621, 'F', '14-MAY-1964', 'EN', 30);update student set address = (select ref(x) from person_address xwhere x.street='111 CRUISING WAY')where student_id = 6901;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (7652, 'MARY', 'FRANKS', '(713)234-8521', 7132348521, 'F', '23-DEC-1965', 'EN', 60);update student set address = (select ref(x) from person_address xwhere x.street='45 KING STREET')where student_id = 7652;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (4631, 'JOE', 'FISHER', '(709)452-7392', 7094527392, 'M', '22-NOV-1962', 'EN', 50);update student set address = (select ref(x) from person_address xwhere x.street='AVENUE CHAMPS-ELYSEES 456')where student_id = 4631;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (3459, 'LARRY', 'MYERS', '(909)352-6216', 9093526216, 'M', '15-AUG-1967', 'EN', 10);update student set address = (select ref(x) from person_address xwhere x.street='4242 MISTY LANE')where student_id = 3459;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6738, 'NICK', 'CARTER', '(808)238-2358', 8082382358, 'M', '17-FEB-1964', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='11-882 KAMEHAMEHA HIGHWAY')where student_id = 6738;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (8390, 'CAROL', 'WOOD', '(809)342-2345', 8093422345, 'F', '01-MAR-1964', 'EN', 60);				   update student set address = (select ref(x) from person_address xwhere x.street='KUNGSGATAN 56')where student_id = 8390;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (8872, 'DEBBY', 'ARCHER', '(980)247-2349', 9802472349, 'F', '01-SEP-1964', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='333 SAN JUAN BLVD')where student_id = 8872;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6474, 'PETER', 'HALL', '(707)843-2346', 7078432346, 'M', '22-FEB-1965', 'EN', 10);update student set address = (select ref(x) from person_address xwhere x.street='45555 BIG STREET')where student_id = 6474;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (9835, 'PAUL', 'MARKS', '(706)343-2345', 7063432345, 'M', '16-DEC-1964', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='177 AIRPORT ROAD')where student_id = 9835;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (7777, 'FRED', 'FLINTSTONE', '(747)987-6543', 7478882929, 'M', '04-JUL-1904', 'FR', 70);update student set address = (select ref(x) from person_address xwhere x.street='177 AIRPORT ROAD')where student_id = 7777insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (10, 'USCABLES', 'WASHINGTON', 'MANUFACTURING', 250, 4000000, 50000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (20, 'KODAK', 'HOUSTON', 'MANUFACTURING', 2500, 10000000, 100000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (30, 'KLM', 'AMSTERDAM', 'SERVICE', 5000, 11000000, 990000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (40, 'HAMBRO', 'LONDON', 'SERVICE', 2000, 6000000, 500000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (50, 'FORD', 'DETROIT', 'MANUFACTURING', 8000, 20000000, 100000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (60, 'CITYBANK', 'BOSTON', 'FINANCE', 3000, 5000000, 200000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (70, 'DISNEY', 'ORLANDO', 'ARTS', 2500, 10000000, 300000);insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (835, 'SPARKS', 4000 , 200, 978, '16-DEC-1984');update instructor set address = (select ref(x) from person_address xwhere x.street='177 AIRPORT ROAD')where instructor_id = 835;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (978, 'STEEL', 5000 , 250, 222, '16-JAN-1980');update instructor set address = (select ref(x) from person_address xwhere x.street='177 AIRPORT ROAD')where instructor_id = 978;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (222, 'CAINE', 5500 , 350, NULL, '02-NOV-1976');update instructor set address = (select ref(x) from person_address xwhere x.street='333 SAN JUAN BLVD')where instructor_id = 222;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (243, 'TUCKER', 2000 , NULL, 835, '18-DEC-1990');update instructor set address = (select ref(x) from person_address xwhere x.street='11-882 KAMEHAMEHA HIGHWAY')where instructor_id = 243;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (263, 'JOHNSON', 4000 , NULL, 835, '18-JUL-1992');update instructor set address = (select ref(x) from person_address xwhere x.street='562 HOCKEY STREET')where instructor_id = 263;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (515, 'SHELLEY', 3500 , 200, 700, '20-JAN-1991');update instructor set address = (select ref(x) from person_address xwhere x.street='4242 MISTY LANE')where instructor_id = 515;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (453, 'LODGE', 2500 , 100, 835, '14-SEP-1988');update instructor set address = (select ref(x) from person_address xwhere x.street='4242 MISTY LANE')where instructor_id = 453;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (700, 'WAYNE', 4500 , 300, NULL, '16-MAY-1981');update instructor set address = (select ref(x) from person_address xwhere x.street='AVENUE CHAMPS-ELYSEES 456')where instructor_id = 700;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (628, 'MONROE', 3000 , NULL, 700, '16-JUN-1984');update instructor set address = (select ref(x) from person_address xwhere x.street='5567 KNIGHTSBRIDGE COURT')where instructor_id = 628;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (790, 'NEWMAN', 3100 , 300, 700, '16-DEC-1982');update instructor set address = (select ref(x) from person_address xwhere x.street='544 42ND STREET')where instructor_id = 790;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (795, 'BOGART', 3200 , NULL, 700, '16-DEC-1983');update instructor set address = (select ref(x) from person_address xwhere x.street='111 CRUISING WAY')where instructor_id = 795;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (560, 'LAUREL', 5200 , 200, 978, '16-DEC-1983');update instructor set address = (select ref(x) from person_address xwhere x.street='333 SAN JUAN BLVD')where instructor_id = 560;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (123, 'FLINTSTONE,FRED', 1000 , NULL, 978, '16-JUN-1989');update instructor set address = (select ref(x) from person_address xwhere x.street='177 AIRPORT ROAD')where instructor_id = 123insert into course values (215, 'UNIX', 5, 1995, 10, null);insert into course values (315, 'C++', 5, 1995, 20, null);insert into course values (415, 'ADA', 4, 1500, 20, null);insert into course values (515, 'ORACLE', 5, 1995, 30, null);insert into course values (615, 'SYBASE', 3, 1200, 30, null);insert into course values (115, 'DOS', 1, 500, 10, null);insert into course values (715, 'VMS', 2, 995, 10, null);insert into course values (815, 'NETWARE', 5, 1995, 40, null);insert into course values (915, 'INFORMIX', 3, 1200, 30, null);insert into subject_area values (10, 'SYSTEMS');insert into subject_area values (20, 'LANGUAGES');insert into subject_area values (30, 'DBMS');insert into subject_area values (40, 'NETWORKS');insert into authorization values (222, 315, '14-MAR-1990');insert into authorization values (222, 415, '13-JAN-1992');insert into authorization values (978, 815, '14-MAR-1989');insert into authorization values (700, 315, '14-FEB-1991');insert into authorization values (835, 515, '12-MAR-1992');insert into authorization values (515, 915, '22-APR-1991');insert into authorization values (263, 215, '14-JUN-1992');insert into authorization values (263, 315, '14-FEB-1992');insert into authorization values (453, 615, '14-JUL-1993');insert into authorization values (453, 715, '14-AUG-1993');insert into authorization values (628, 315, '13-JUL-1991');insert into authorization values (790, 315, '14-AUG-1991');insert into site values (1, address_type('3 Place de Concorde','PARIS','FRANCE'),classroom_varray_type (classroom_type (1, 20, null),				classroom_type (2, 24, null),				classroom_type (3, 30, null)),'Europe Ctr');insert into site values (2, address_type('1600 Pennsylvania','WASHINGTON','USA'),classroom_varray_type (classroom_type (1, 20, null),				classroom_type (2, 24, null)), 'US East');insert into site values (3, address_type('22 Trafalgar Square','LONDON','UK'), classroom_varray_type (classroom_type (1, 20, null),				classroom_type (2, 24, null),				classroom_type (3, 24, null)),'Europe West');insert into site values (4, address_type('Kungsgatan 26','STOCKHOLM','SWEDEN'), classroom_varray_type (classroom_type (1, 20, null)), 'Europe North');insert into site values (5, address_type('343 Elm Street','OTTAWA','CANADA'),classroom_varray_type (classroom_type (1, 20, null),				classroom_type (2, 20, null),				classroom_type (3, 20, null)), 'Canada Ctr');insert into site values (6, address_type('101 Boylston Street','BOSTON','USA'),classroom_varray_type (classroom_type (1, 20, null),				classroom_type (2, 24, null)), 'US East');insert into site values (7, address_type('1010 Sepulveda','LOS ANGELES','USA'),classroom_varray_type (classroom_type (1, 20, null),				classroom_type (2, 20, null),				classroom_type (3, 20, null)) , 'US West');insert into site values (8, address_type('101 Younge Street','TORONTO','CANADA'), classroom_varray_type (classroom_type (1, 20, null),				classroom_type (2, 24, null)), 'Canada South'); insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1001, 315, 700, 1, '16-DEC-1994', 24);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1002, 315, 263, 3, '16-JUL-1995', 20);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1003, 915, 560, 7, '23-NOV-1996', 30);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1004, 215, 263, 2, '26-SEP-1995', 24);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1005, 815, 978, 3, '15-OCT-1995', 20);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1006, 915, 560, 4, '04-JAN-1996', 22);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1007, 615, 453, 4, '30-SEP-1995', 30);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1008, 515, 835, 5, '20-JUN-1995', 24);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1009, 315, 790, 4, '21-JUN-1995', 24);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1010, 315, NULL, 4, '30-AUG-1997', 24);insert into attendance values (1001,1111, 4, 1000);insert into attendance values (1002,2299, NULL, 1000);insert into attendance values (1003,4568, 3, 1200);insert into attendance values (1002,6789, 4, 1150);insert into attendance values (1005,9835, 3, 1995);insert into attendance values (1006,9842, 4, 1000);insert into attendance values (1006,1199, 4, 1200);insert into attendance values (1006,3452, 2, 200);insert into attendance values (1007,8390, 4, 1200);insert into attendance values (1007,6474, 4, 900);insert into attendance values (1008,8390, 4, 1000);insert into attendance values (1003,1199, 4, 1000);insert into attendance values (1001,6738, 4, 850);insert into attendance values (1001,5556, 4, 1995);insert into attendance values (1001,6263, 1, 1995);insert into attendance values (1002,2346, 4, 1995);insert into attendance values (1003,6901, 4, 1200);insert into attendance values (1003,7652, 4, 1200);insert into attendance values (1003,4631, 3, 1200);insert into attendance values (1008,3459, 4, 1995);insert into attendance values (1004,2299, 3, 1995);insert into attendance values (1004,6789, 4, 1995);insert into attendance values (1004,6263, 4, 1995);insert into attendance values (1007,9842, 3, 1200);insert into attendance values (1007,4631, 3, 1200);insert into attendance values (1005,6738, 4, 1000);insert into attendance values (1005,3459, 3, 1995);insert into attendance values (1006,6474, 2, 1200);insert into attendance values (1006,9835, 4, 1000);insert into attendance values (1007,9835, 4, 1200);insert into attendance values (1009,3459, 3, 4000);commit;create sequence inv_no_seq start with 10000 ;insert into invoiceselect inv_no_seq.nextval, co.company_id, trunc(sysdate),   trunc(sysdate) + 30,CAST (MULTISET(select    s.student_lname, c.title, o.start_date   , o.start_date + c.duration, a.amount_paidfrom offering o , attendance a, course c, student swhere o.offering_id=a.offering_idand o.course_id = c.course_idand s.student_id = a.student_idand co.company_id = s.company_id) AS INVOICE_ITEM_TABLE_TYPE)FROM COMPANY CO;commit;spool off

Edited by aspnetguy
Link to comment
Share on other sites

hi there i've been given a task to change the following script to create a new user, dev1 with password dev1 who has unlimited access. I have to create another table called university that has ID, name, address, number of students, date of frst contact! i have been trying but not getting anywhere. it seems fairly simple to do by copy and apstgin the original script and changing the names.the underlined code is what i attempted. i try the user thing but fail.
spool dbcre8.logconnect system/managerdrop user ora1 cascade;drop user ora2 cascade;create user ora1 identified by ora1;grant create session to ora1;grant create table to ora1;grant create view to ora1;grant create sequence to ora1;grant create synonym to ora1;grant create procedure to ora1;grant create trigger to ora1;grant create cluster to ora1;grant create type to ora1;alter user ora1 quota unlimited on system;drop user ora2 cascade;create user ora2 identified by ora2;grant create session to ora2;grant create table to ora2;grant create view to ora2;grant create sequence to ora2;grant create synonym to ora2;grant create procedure to ora2;grant create trigger to ora2;grant create type to ora2;grant create cluster to ora2;alter user ora2 quota unlimited on system;connect ora1/ora1CREATE OR REPLACE TYPE address_type AS OBJECT (street  VARCHAR2(25),city  VARCHAR2(25),country	VARCHAR2(20) )/CREATE TABLE person_address OF address_type;CREATE TABLE student (	student_id      	number(4)       not null,	student_fname    varchar2(10)	not null,	student_lname    varchar2(12)	not null,	address  	REF address_type SCOPE IS person_address,	tel_no          	varchar2(15),	fax_no          	number(15),	gender        	varchar2(1),	date_of_birth    date,	student_desc            varchar2(250),	preferred_language      varchar2(2),	passport_program        varchar2(1),	company_id  number(2)       not null);CREATE TABLE company (	company_id      number(2)       not null,	company_name    varchar2(15),	city            varchar2(12),	industry        varchar2(15),	no_employees    number(5),	revenue         number(10),	training_budget number(6));CREATE TABLE instructor (	instructor_id   number(3)       not null,	instructor_name varchar2(15)	not null,	address  REF address_type SCOPE IS person_address,	salary          number(6,2),	commission      number(6,2),	mentor_id       number(3),	date_hired      date,	profile  CLOB);CREATE TABLE course (	course_id       number(3)       not null,	title           varchar2(30),	duration        number(2),	cost            number(5),	subject_id      number(2),	brochure	bfile);CREATE TABLE authorization (	instructor_id   number(3)       not null,	course_id       number(3)       not null,	auth_date       date);CREATE TABLE subject_area (	subject_id      number(2)       not null,	subject_name    varchar2(30));CREATE TABLE offering (	offering_id     number(5)       not null,	course_id       number(3)       not null,	site_id         number(2)       not null,	instructor_id   number(4),	start_date      date,	max_no_students number(2));[u]CREATE TABLE university (                university_id  number(5)         not null,                name            varchar(20),                        number_of_students      number(6),                       date_of_first_contact      date,);[/u]CREATE TYPE classroom_type AS OBJECT(room_number NUMBER(2) ,capacity NUMBER(2) ,description VARCHAR2(200));/CREATE TYPE classroom_varray_type AS VARRAY(50) OF classroom_type;/CREATE TABLE site (	site_id         number(2)       not null,	address  address_type,	classroom	classroom_varray_type,	location        varchar2(12));CREATE TABLE attendance (	offering_id     number(5)       not null,	student_id      number(4)       not null,	evaluation      varchar2(1),	amount_paid     number(6,2));CREATE OR REPLACE TYPE invoice_item_type AS OBJECT(student_lname  VARCHAR2(12) ,course_title  VARCHAR2(30) ,start_date  DATE ,end_date  DATE ,amount  	NUMBER(7,2) );/CREATE TYPE invoice_item_table_type AS TABLE OF invoice_item_type;/CREATE TABLE invoice(invoice_number	NUMBER(6) NOT NULL,company_id  NUMBER(2) NOT NULL,billing_date  DATE NOT NULL,due_date  DATE,invoice_item  invoice_item_table_type)NESTED TABLE invoice_item STORE AS invoice_item_table;insert into person_address (street, city, country)values ('111 BAY STATE ROAD', 'BOSTON', 'USA');insert into person_address (street, city, country)values ('111 VALLEY WAY', 'SAN FERNANDO', 'USA');insert into person_address (street, city, country)values ('4545 ORACLE DRIVE', 'SAN FRANCISCO', 'USA');insert into person_address (street, city, country)values ('22 GOLDEN GATE DRIVE', 'SAN FRANCISCO', 'USA');insert into person_address (street, city, country)values ('77 ELM STREET', 'NEWARK', 'USA');insert into person_address (street, city, country)values ('544 42ND STREET', 'NEW YORK', 'USA');insert into person_address (street, city, country)values ('111 BEACHCOMBER PLACE', 'OCEANSIDE', 'USA');insert into person_address (street, city, country)values ('25 MISSION WAY', 'SAN DIEGO', 'USA');insert into person_address (street, city, country)values ('177 AIRPORT ROAD', 'NEWARK', 'USA');insert into person_address (street, city, country)values ('5567 KNIGHTSBRIDGE COURT', 'LONDON', 'UK');insert into person_address (street, city, country)values ('111 CRUISING WAY', 'TORTOLA', 'BVI');insert into person_address (street, city, country)values ('45 KING STREET', 'OTTAWA', 'CANADA');insert into person_address (street, city, country)values ('45555 BIG STREET', 'DALLAS', 'USA');insert into person_address (street, city, country)values ('AVENUE CHAMPS-ELYSEES 456', 'PARIS', 'FRANCE');insert into person_address (street, city, country)values ('562 HOCKEY STREET', 'TORONTO', 'CANADA');insert into person_address (street, city, country)values ('4242 MISTY LANE', 'SEATTLE', 'USA');insert into person_address (street, city, country)values ('KUNGSGATAN 56', 'STOCKHOLM', 'SWEDEN');insert into person_address (street, city, country)values ('11-882 KAMEHAMEHA HIGHWAY', 'HAWAII', 'USA');insert into person_address (street, city, country)values ('333 SAN JUAN BLVD', 'SAN JUAN', 'PUERTO RICO');insert into person_address (street, city, country)values ('7500 IMPERIAL BLVD', 'LOS ANGELES', 'USA');insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (1111, 'DIANE', 'BROWN', '(617)342-2345', 6173422345, 'F', '01-FEB-1964', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='111 BAY STATE ROAD')where student_id = 1111;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (2299, 'CHRIS', 'ADAMS', '(213)334-2789', 2133342789, 'M', '22-MAR-1965', 'EN', 30);update student set address = (select ref(x) from person_address xwhere x.street='7500 IMPERIAL BLVD')where student_id = 2299;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (4568, 'KEVIN', 'COX', '(619)433-6845', 6194336845, 'M', '04-DEC-1963', 'EN', 10);update student set address = (select ref(x) from person_address xwhere x.street='25 MISSION WAY')where student_id = 4568;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (5556, 'JOHN', 'TYLER', '(212)444-9769', 2124449769, 'M', '13-JUN-1966', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='544 42ND STREET')where student_id = 5556;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6874, 'NANCY', 'GIBBS', '(714)346-2896', 7143462896, 'F', '15-JUL-1965', 'EN', 10);update student set address = (select ref(x) from person_address xwhere x.street='111 VALLEY WAY')where student_id = 6874;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6789, 'JENNY', 'ROSE', '(415)334-2345', 4153342345, 'F', '16-SEP-1963', 'SW', 10);update student set address = (select ref(x) from person_address xwhere x.street='4545 ORACLE DRIVE')where student_id = 6789;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (1199, 'FRANK', 'NELSON', '(609)345-2346', 6093452346, 'M', '22-APR-1964', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='77 ELM STREET')where student_id = 1199;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6263, 'KEN', 'CRICK', '(415)345-2313', 4153452313, 'M', '14-MAR-1965', 'EN', 50);update student set address = (select ref(x) from person_address xwhere x.street='22 GOLDEN GATE DRIVE')where student_id = 6263;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (3452, 'NIGEL', 'TURNER', '(818)352-2511', 8183522511, 'M', '16-FEB-1962', 'SW', 10);update student set address = (select ref(x) from person_address xwhere x.street='111 BEACHCOMBER PLACE')where student_id = 3452;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (2346, 'CARL', 'DUDLEY', '(609)223-7890', 6092237890, 'M', '28-NOV-1967', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='5567 KNIGHTSBRIDGE COURT')where student_id = 2346;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (9842, 'MIKE', 'COOPER', '(713)726-9842', 7137269842, 'M', '18-OCT-1962', 'EN', 50);update student set address = (select ref(x) from person_address xwhere x.street='562 HOCKEY STREET')where student_id = 9842;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6901, 'LILY', 'GREEN', '(809)342-9621', 8093429621, 'F', '14-MAY-1964', 'EN', 30);update student set address = (select ref(x) from person_address xwhere x.street='111 CRUISING WAY')where student_id = 6901;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (7652, 'MARY', 'FRANKS', '(713)234-8521', 7132348521, 'F', '23-DEC-1965', 'EN', 60);update student set address = (select ref(x) from person_address xwhere x.street='45 KING STREET')where student_id = 7652;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (4631, 'JOE', 'FISHER', '(709)452-7392', 7094527392, 'M', '22-NOV-1962', 'EN', 50);update student set address = (select ref(x) from person_address xwhere x.street='AVENUE CHAMPS-ELYSEES 456')where student_id = 4631;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (3459, 'LARRY', 'MYERS', '(909)352-6216', 9093526216, 'M', '15-AUG-1967', 'EN', 10);update student set address = (select ref(x) from person_address xwhere x.street='4242 MISTY LANE')where student_id = 3459;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6738, 'NICK', 'CARTER', '(808)238-2358', 8082382358, 'M', '17-FEB-1964', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='11-882 KAMEHAMEHA HIGHWAY')where student_id = 6738;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (8390, 'CAROL', 'WOOD', '(809)342-2345', 8093422345, 'F', '01-MAR-1964', 'EN', 60);       update student set address = (select ref(x) from person_address xwhere x.street='KUNGSGATAN 56')where student_id = 8390;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (8872, 'DEBBY', 'ARCHER', '(980)247-2349', 9802472349, 'F', '01-SEP-1964', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='333 SAN JUAN BLVD')where student_id = 8872;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6474, 'PETER', 'HALL', '(707)843-2346', 7078432346, 'M', '22-FEB-1965', 'EN', 10);update student set address = (select ref(x) from person_address xwhere x.street='45555 BIG STREET')where student_id = 6474;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (9835, 'PAUL', 'MARKS', '(706)343-2345', 7063432345, 'M', '16-DEC-1964', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='177 AIRPORT ROAD')where student_id = 9835;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (7777, 'FRED', 'FLINTSTONE', '(747)987-6543', 7478882929, 'M', '04-JUL-1904', 'FR', 70);update student set address = (select ref(x) from person_address xwhere x.street='177 AIRPORT ROAD')where student_id = 7777insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (10, 'USCABLES', 'WASHINGTON', 'MANUFACTURING', 250, 4000000, 50000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (20, 'KODAK', 'HOUSTON', 'MANUFACTURING', 2500, 10000000, 100000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (30, 'KLM', 'AMSTERDAM', 'SERVICE', 5000, 11000000, 990000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (40, 'HAMBRO', 'LONDON', 'SERVICE', 2000, 6000000, 500000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (50, 'FORD', 'DETROIT', 'MANUFACTURING', 8000, 20000000, 100000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (60, 'CITYBANK', 'BOSTON', 'FINANCE', 3000, 5000000, 200000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (70, 'DISNEY', 'ORLANDO', 'ARTS', 2500, 10000000, 300000);insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (835, 'SPARKS', 4000 , 200, 978, '16-DEC-1984');update instructor set address = (select ref(x) from person_address xwhere x.street='177 AIRPORT ROAD')where instructor_id = 835;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (978, 'STEEL', 5000 , 250, 222, '16-JAN-1980');update instructor set address = (select ref(x) from person_address xwhere x.street='177 AIRPORT ROAD')where instructor_id = 978;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (222, 'CAINE', 5500 , 350, NULL, '02-NOV-1976');update instructor set address = (select ref(x) from person_address xwhere x.street='333 SAN JUAN BLVD')where instructor_id = 222;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (243, 'TUCKER', 2000 , NULL, 835, '18-DEC-1990');update instructor set address = (select ref(x) from person_address xwhere x.street='11-882 KAMEHAMEHA HIGHWAY')where instructor_id = 243;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (263, 'JOHNSON', 4000 , NULL, 835, '18-JUL-1992');update instructor set address = (select ref(x) from person_address xwhere x.street='562 HOCKEY STREET')where instructor_id = 263;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (515, 'SHELLEY', 3500 , 200, 700, '20-JAN-1991');update instructor set address = (select ref(x) from person_address xwhere x.street='4242 MISTY LANE')where instructor_id = 515;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (453, 'LODGE', 2500 , 100, 835, '14-SEP-1988');update instructor set address = (select ref(x) from person_address xwhere x.street='4242 MISTY LANE')where instructor_id = 453;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (700, 'WAYNE', 4500 , 300, NULL, '16-MAY-1981');update instructor set address = (select ref(x) from person_address xwhere x.street='AVENUE CHAMPS-ELYSEES 456')where instructor_id = 700;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (628, 'MONROE', 3000 , NULL, 700, '16-JUN-1984');update instructor set address = (select ref(x) from person_address xwhere x.street='5567 KNIGHTSBRIDGE COURT')where instructor_id = 628;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (790, 'NEWMAN', 3100 , 300, 700, '16-DEC-1982');update instructor set address = (select ref(x) from person_address xwhere x.street='544 42ND STREET')where instructor_id = 790;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (795, 'BOGART', 3200 , NULL, 700, '16-DEC-1983');update instructor set address = (select ref(x) from person_address xwhere x.street='111 CRUISING WAY')where instructor_id = 795;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (560, 'LAUREL', 5200 , 200, 978, '16-DEC-1983');update instructor set address = (select ref(x) from person_address xwhere x.street='333 SAN JUAN BLVD')where instructor_id = 560;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (123, 'FLINTSTONE,FRED', 1000 , NULL, 978, '16-JUN-1989');update instructor set address = (select ref(x) from person_address xwhere x.street='177 AIRPORT ROAD')where instructor_id = 123insert into course values (215, 'UNIX', 5, 1995, 10, null);insert into course values (315, 'C++', 5, 1995, 20, null);insert into course values (415, 'ADA', 4, 1500, 20, null);insert into course values (515, 'ORACLE', 5, 1995, 30, null);insert into course values (615, 'SYBASE', 3, 1200, 30, null);insert into course values (115, 'DOS', 1, 500, 10, null);insert into course values (715, 'VMS', 2, 995, 10, null);insert into course values (815, 'NETWARE', 5, 1995, 40, null);insert into course values (915, 'INFORMIX', 3, 1200, 30, null);insert into subject_area values (10, 'SYSTEMS');insert into subject_area values (20, 'LANGUAGES');insert into subject_area values (30, 'DBMS');insert into subject_area values (40, 'NETWORKS');insert into authorization values (222, 315, '14-MAR-1990');insert into authorization values (222, 415, '13-JAN-1992');insert into authorization values (978, 815, '14-MAR-1989');insert into authorization values (700, 315, '14-FEB-1991');insert into authorization values (835, 515, '12-MAR-1992');insert into authorization values (515, 915, '22-APR-1991');insert into authorization values (263, 215, '14-JUN-1992');insert into authorization values (263, 315, '14-FEB-1992');insert into authorization values (453, 615, '14-JUL-1993');insert into authorization values (453, 715, '14-AUG-1993');insert into authorization values (628, 315, '13-JUL-1991');insert into authorization values (790, 315, '14-AUG-1991');insert into site values (1, address_type('3 Place de Concorde','PARIS','FRANCE'),classroom_varray_type (classroom_type (1, 20, null),    classroom_type (2, 24, null),    classroom_type (3, 30, null)),'Europe Ctr');insert into site values (2, address_type('1600 Pennsylvania','WASHINGTON','USA'),classroom_varray_type (classroom_type (1, 20, null),    classroom_type (2, 24, null)), 'US East');insert into site values (3, address_type('22 Trafalgar Square','LONDON','UK'), classroom_varray_type (classroom_type (1, 20, null),    classroom_type (2, 24, null),    classroom_type (3, 24, null)),'Europe West');insert into site values (4, address_type('Kungsgatan 26','STOCKHOLM','SWEDEN'), classroom_varray_type (classroom_type (1, 20, null)), 'Europe North');insert into site values (5, address_type('343 Elm Street','OTTAWA','CANADA'),classroom_varray_type (classroom_type (1, 20, null),    classroom_type (2, 20, null),    classroom_type (3, 20, null)), 'Canada Ctr');insert into site values (6, address_type('101 Boylston Street','BOSTON','USA'),classroom_varray_type (classroom_type (1, 20, null),    classroom_type (2, 24, null)), 'US East');insert into site values (7, address_type('1010 Sepulveda','LOS ANGELES','USA'),classroom_varray_type (classroom_type (1, 20, null),    classroom_type (2, 20, null),    classroom_type (3, 20, null)) , 'US West');insert into site values (8, address_type('101 Younge Street','TORONTO','CANADA'), classroom_varray_type (classroom_type (1, 20, null),    classroom_type (2, 24, null)), 'Canada South'); insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1001, 315, 700, 1, '16-DEC-1994', 24);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1002, 315, 263, 3, '16-JUL-1995', 20);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1003, 915, 560, 7, '23-NOV-1996', 30);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1004, 215, 263, 2, '26-SEP-1995', 24);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1005, 815, 978, 3, '15-OCT-1995', 20);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1006, 915, 560, 4, '04-JAN-1996', 22);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1007, 615, 453, 4, '30-SEP-1995', 30);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1008, 515, 835, 5, '20-JUN-1995', 24);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1009, 315, 790, 4, '21-JUN-1995', 24);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1010, 315, NULL, 4, '30-AUG-1997', 24);insert into attendance values (1001,1111, 4, 1000);insert into attendance values (1002,2299, NULL, 1000);insert into attendance values (1003,4568, 3, 1200);insert into attendance values (1002,6789, 4, 1150);insert into attendance values (1005,9835, 3, 1995);insert into attendance values (1006,9842, 4, 1000);insert into attendance values (1006,1199, 4, 1200);insert into attendance values (1006,3452, 2, 200);insert into attendance values (1007,8390, 4, 1200);insert into attendance values (1007,6474, 4, 900);insert into attendance values (1008,8390, 4, 1000);insert into attendance values (1003,1199, 4, 1000);insert into attendance values (1001,6738, 4, 850);insert into attendance values (1001,5556, 4, 1995);insert into attendance values (1001,6263, 1, 1995);insert into attendance values (1002,2346, 4, 1995);insert into attendance values (1003,6901, 4, 1200);insert into attendance values (1003,7652, 4, 1200);insert into attendance values (1003,4631, 3, 1200);insert into attendance values (1008,3459, 4, 1995);insert into attendance values (1004,2299, 3, 1995);insert into attendance values (1004,6789, 4, 1995);insert into attendance values (1004,6263, 4, 1995);insert into attendance values (1007,9842, 3, 1200);insert into attendance values (1007,4631, 3, 1200);insert into attendance values (1005,6738, 4, 1000);insert into attendance values (1005,3459, 3, 1995);insert into attendance values (1006,6474, 2, 1200);insert into attendance values (1006,9835, 4, 1000);insert into attendance values (1007,9835, 4, 1200);insert into attendance values (1009,3459, 3, 4000);commit;create sequence inv_no_seq start with 10000 ;insert into invoiceselect inv_no_seq.nextval, co.company_id, trunc(sysdate),   trunc(sysdate) + 30,CAST (MULTISET(select    s.student_lname, c.title, o.start_date   , o.start_date + c.duration, a.amount_paidfrom offering o , attendance a, course c, student swhere o.offering_id=a.offering_idand o.course_id = c.course_idand s.student_id = a.student_idand co.company_id = s.company_id) AS INVOICE_ITEM_TABLE_TYPE)FROM COMPANY CO;commit;spool off

omg Edited by aspnetguy
Link to comment
Share on other sites

yeh that would've been a good idea. I'm a bit behind on my SQL so just rushed the post. This forum seems to calm me down so u won't get anymore of that wild posting out of me. With this task i have to add 2 users, ones called dev1 and the other ukacademic1. dev1 has a password named dev1 and ukacademic1 has a password ukacc1. dev1 has unlimited access but ukacademic1 has only the read only access to a view of the student, instructor and university tables but be restricted to the students/ instructors who have links with UK universities. how would i go about doing this?

Link to comment
Share on other sites

ok thank god we got that code box thing out the way, phew was that a hum-dinger of a problem now back to adding 2 users and granting readonly access to 3 tables where they are linked to uk universtities. any clues? i'll be on here all nite. yep setting upcamp!

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...