[Solved]Need Help Adding Queries Exercise 26 B C D D Listed Figure Used Queries Need Add Tables In Q37066692

Need help adding these Queries- Exercise 2.6 a, b, c, d (A-D):Below is listed the Figure used, queries I need to add then mytables/inserts.

— cr-mailorder.sql
— creates the mailorder DB-schema

— keep these two commands at the top of every sql file
set echo on
set linesize 120

drop table ZIPCODES cascade constraints;
commit;

— better way of declaring primary key and foreign key
— using constraint declaration
create table ZIPCODES
(
   zip char(5),
   city varchar2(20),
   constraint ZIPPK
   primary key(zip)
);

— another way of declaring primary key
–create table ZIPCODES2
–(
—   zip char(5) primary key,
—   city varchar(20),
–);

— one another way of declaring primary key
–create table ZIPCODES3
–(
—   zip char(5),
—   city varchar(20),
—   primary key(zip)
–);

drop table EMPLOYEES cascade constraints;
commit;

create table EMPLOYEES
(
   eno number,
   ename varchar2(20),
   zip char(5),
   hdate date,
   constraint EMPSPK
   primary key(eno),
   constraint EMPSZIPFK
   foreign key(zip) references ZIPCODES(zip)
       ON DELETE SET NULL
);

drop table PARTS cascade constraints;
commit;

create table PARTS
(
   pno number,
   pname varchar2(30),
   qoh number,
   price number,
   qlevel number,
   constraint PARTSPK
   primary key(pno)
);

drop table CUSTOMERS cascade constraints;
commit;

create table CUSTOMERS
(
   cno number,
   cname varchar2(30),
   street varchar2(30),
   zip char(5),
   phone char(12),
   constraint CUSTPK
       primary key(cno),
   constraint CUSTPK1
       foreign key(zip) referencesZIPCODES(zip)
);

drop table ORDERS cascade constraints;
commit;

create table ORDERS
(
   ono number,
   cno number,
   eno number,
   received date,
   shipped date,
   constraint ORDERPK
       primary key(ono),
   constraint ORDERPK1
       foreign key(cno) referencesCUSTOMERS(cno),
   constraint ORDERPK2   
       foreign key(eno) referencesEMPLOYEES(eno)      
);

drop table ODETAILS cascade constraints;
commit;

create table ODETAILS
(
   ono number,
   pno number,
   qty number,
   constraint ODETPK
       primary key(ono, pno),
   constraint ODETPK1
       foreign key(ono) referencesORDERS(ono),
   constraint ODETPK2
       foreign key(pno) referencesPARTS(pno)
);

— ins-mailorder.sql
— inserts tuples into the mailorder DB

— keep these two commands at the top of every sql file
set echo on
set linesize 120

delete from ZIPCODES;
commit;
insert into ZIPCODES values (‘67226’, ‘Wichita’);
insert into ZIPCODES values (‘60606’, ‘Fort Dodge’);
insert into ZIPCODES values (‘50302’, ‘Kansas City’);
insert into ZIPCODES values (‘54444’, ‘Colombia’);
insert into ZIPCODES values (‘66002’, ‘Liberal’);
insert into ZIPCODES values (‘61111’, ‘Fort Hays’);

delete from EMPLOYEES;
commit;
insert into EMPLOYEES values (1000, ‘Jones’, ‘67226’,’12-DEC-95′);
insert into EMPLOYEES values (1001, ‘Smith’, ‘60606’,’01-JAN-92′);
insert into EMPLOYEES values (1002, ‘Brown’, ‘50302’,’01-SEP-94′);

— ADD the rest
delete from PARTS;
commit;
insert into PARTS values (10506, ‘Land Before Time I’, 200, 19.99,20);
insert into PARTS values (10507, ‘Land Before Time II’, 156, 19.99,20);
insert into PARTS values (10508, ‘Land Before Time III’, 190,19.99, 20);
insert into PARTS values (10509, ‘Land Before Time IV’, 60, 19.99,20);
insert into PARTS values (10601, ‘Sleeping Beauty’, 300, 24.99,20);
insert into PARTS values (10701, ‘When Harry Met Sally’, 120,19.99, 30);
insert into PARTS values (10800, ‘Dirty Harry’, 140, 14.99,30);
insert into PARTS values (10900, ‘Dr. Zhivago’, 100, 24.99,30);

delete from CUSTOMERS;
commit;
insert into CUSTOMERS values (1111, ‘Charles’, ‘123 Main St.’,67226, ‘316-636-5555’);
insert into CUSTOMERS values (2222, ‘Bertram’, ‘237 Ash Ave.’,67226, ‘316-689-5555’);
insert into CUSTOMERS values (3333, ‘Barbara’, ‘111 Inwood St.’,60606, ‘316-111-1234’);

delete from ORDERS;
commit;
insert into ORDERS values (1020, 1111, 1000, ’10-DEC-94′,’12-DEC-94′);
insert into ORDERS values (1021, 1111, 1000, ’12-JAN-95′,’15-JAN-95′);
insert into ORDERS values (1022, 2222, 1001, ’13-FEB-95′,’20-FEB-95′);
insert into ORDERS values (1023, 3333, 1000, ’20-JAN-97′,null);

delete from ODETAILS;
commit;
insert into ODETAILS values (1020, 10506, 1);
insert into ODETAILS values (1020, 10507, 1);
insert into ODETAILS values (1020, 10508, 2);
insert into ODETAILS values (1020, 10509, 3);
insert into ODETAILS values (1021, 10601, 4);
insert into ODETAILS values (1022, 10601, 1);
insert into ODETAILS values (1022, 10701, 1);
insert into ODETAILS values (1023, 10800, 1);
insert into ODETAILS values (1023, 10900, 1);

A sample from a mail-order database. Figure 1.4 Employees ENO 1000 1001 1002 ENAME HDATE 12-DEC-95 67226 6060601-JAN-92 01-SEP-94 Parts QOH 200 PRICE 19.99 19.99 19.99 19.99 24.99 LEVEL 20 PNO 10506 10507 10508Land Before Time III 10509 10601 Sleeping Beauty 10701When Harry Met Sally PNAME Land Before Time I Land Before Time II Land Before Time IV 60 120 30 30 30 14.99 10800 10900 Dirty Harry Dr. Zhivago Customers STREET 123 Main St 237 Ash Ave. 111 Inwood St PHONE 316-636-5555 316-689-5555 316-111-1234 CNAME Charles Bertram Barbara CNO 67226 67226 60606 Orders SHIPPED 12-DEC-94 15-JAN-95 20-FEB-95 nu ENO 1000 1000 100113-FEB-95 1000 ONO 1020 1021 1022 1023 CNO RECEIVED 10-DEC-94 12-JAN-95 20-JUN-97 Odetails PNO 10506 CITY ONO 1020 QTY 67226 60606 50302 1020 10507 Fort Dodge Kansas Cit)y 10508 10509 10601 10601 10701 10800 10900 1020 1020 1021 1022 1022 1023 1023 66002 Liberal 26 For theail-order daabase, wte SQL expressions to perform the following updates to the database: (a) Decrease by 15 perent the prices of all pars that cost less than $20.00. (b) Update all the nnl1-valued shipped dates of orders to the current date. c)Decrease by S10.00 the prices of parts that cost more than the average price of all parts. (d) Transfer all the orders belouging to the employee with eno 1000 to the emmplayee with eno 1001 Show transcribed image text A sample from a mail-order database. Figure 1.4 Employees ENO 1000 1001 1002 ENAME HDATE 12-DEC-95 67226 6060601-JAN-92 01-SEP-94 Parts QOH 200 PRICE 19.99 19.99 19.99 19.99 24.99 LEVEL 20 PNO 10506 10507 10508Land Before Time III 10509 10601 Sleeping Beauty 10701When Harry Met Sally PNAME Land Before Time I Land Before Time II Land Before Time IV 60 120 30 30 30 14.99 10800 10900 Dirty Harry Dr. Zhivago Customers STREET 123 Main St 237 Ash Ave. 111 Inwood St PHONE 316-636-5555 316-689-5555 316-111-1234 CNAME Charles Bertram Barbara CNO 67226 67226 60606 Orders SHIPPED 12-DEC-94 15-JAN-95 20-FEB-95 nu ENO 1000 1000 100113-FEB-95 1000 ONO 1020 1021 1022 1023 CNO RECEIVED 10-DEC-94 12-JAN-95 20-JUN-97 Odetails PNO 10506 CITY ONO 1020 QTY 67226 60606 50302 1020 10507 Fort Dodge Kansas Cit)y 10508 10509 10601 10601 10701 10800 10900 1020 1020 1021 1022 1022 1023 1023 66002 Liberal
26 For theail-order daabase, wte SQL expressions to perform the following updates to the database: (a) Decrease by 15 perent the prices of all pars that cost less than $20.00. (b) Update all the nnl1-valued shipped dates of orders to the current date. c)Decrease by S10.00 the prices of parts that cost more than the average price of all parts. (d) Transfer all the orders belouging to the employee with eno 1000 to the emmplayee with eno 1001

Expert Answer

---

Answer to Need help adding these Queries- Exercise 2.6 a, b, c, d (A-D): Below is listed the Figure used, queries I need to add th… . . .