Create the TIME table via SQL. The Sale_Day primary key column values should be

ID: 3560163 • Letter: C

Question

Create the TIME table via SQL. The Sale_Day primary key column values should be all dates from your first sale date through and including your last sale date from your SALES table. The Day_Type values should be 'Weekday', 'Weekend', or 'Holiday' (this trumps Weekday and Weekend). Use a PL/SQL block to populate the TIME table. After populating your TIME table execute the SQL statement "SELECT day_type,COUNT(*),MIN(sale_day),MAX(sale_day) FROM time GROUP BY day_type ORDER BY day_type" to show the summarized contents of your table. Show all your SQL and PL/SQL and the results

SALES TABLE

SALES_ID NOT NULL VARCHAR2(6) PK
VEHICLE_STATUS NOT NULL VARCHAR2(25)
SALE_DATE NOT NULL DATE
MILEAGE NOT NULL NUMBER(6)
CUST_ID NOT NULL VARCHAR2(6) FK
SALESPERSON_ID NOT NULL VARCHAR2(6) FK
VIN NOT NULL VARCHAR2(20) FK
GROSSSALEPRICE NOT NULL NUMBER(10,2)

SALES_ID VEHICLE_STATUS SALE_DATE MILEAGE CUST_ID SALESPERSON_ID VIN GROSSSALEPRICE
-------- ------------------------- --------- ---------- ------- -------------- -------------------- --------------
s69228 used 13-JUL-09 56734 c10028 sp0001 WAUAF78E17A810752 12345
s69229 new 29-JUN-14 12 c10029 sp0010 5TDZT64AX9S741402 48234
s69230 new 02-APR-14 2345 c10030 sp0005 3N1BB51D81L946744 12345
s69231 new 24-JUL-14 23671 c10031 sp0010 1D7HE52N98S926753 29456
s69232 new 22-JUN-14 100 c10032 sp0001 1GCEK14VX6E522349 23000
s69233 new 01-JUN-14 300 c10033 sp0001 JN1AY1AR4CM024432 20000
s69234 new 23-MAR-14 1000 c10034 sp0001 1GTEK14TX2Z165398 10000
s69235 used 02-FEB-10 35000 c10035 sp0010 1G6KF5790YU479987 23000

Explanation / Answer

/* Create Statement */

create table TIME_TABLE ( Sale_Day DATE NOT NULL, Day_Type varchar2(10) NOT NULL,foreign key(Sale_Day) references SALES(SALE_DATE));

***************************************************************************************************************************

/* Procedure */

create or replace procedure TIME_PROCEDURE
AS
l_date date;
temp varchar2(5);
CURSOR c1 IS SELECT SALE_DATE FROM SALES;
begin
OPEN c1;
LOOP
FETCH c1 INTO l_date;
EXIT WHEN c1%NOTFOUND;
select to_char(to_date(l_date), 'DY') into temp from dual;
if l_date like '01-JAN-%' then
Insert into tym values(l_date,'Holiday');
elsif temp=' MON' or temp ='TUE' or temp='WED' or temp='THU' or temp='FRI' then
Insert into tym values(l_date,'Weekday');
ELSE
Insert into tym values(l_date,'Weekend');
end if;
END LOOP;
CLOSE c1;
End TIME_PROCEDURE;

*****************************************************************************************************************************

/* Populating TIME_TABLE */

BEGIN

TIME_PROCEDURE;

END;

***************************************************************************************************************************

/* Select Statement */

SELECT DAY_TYPE,COUNT(*),MIN(SALE_DAY),MAX(SALE_DAY) FROM TIME_TABLE GROUP BY DAY_TYPE ORDER BY DAY_TYPE;

Navigate

Create the TIME table via SQL. The Sale_Day primary key column values should be

Create the Target Market portion of the Marketing Communications Plan using the

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

Create the TIME table via SQL. The Sale_Day primary key column values should be

Question

Explanation / Answer

Related Questions

Navigate