Develop a query that will identify and mark inactive those customers that have b

Question

Develop a query that will identify and mark inactive those customers that have bills overdue by more than 30 days (this can usually be done using a sub-query). Remember, you are only marking a customer as inactive, not actually deleting the customer record from the system.

SQL FILE.

CREATE TABLE Rating
(
RatingID NUMBER(5) CONSTRAINT PK_RatingID PRIMARY KEY,
RatingCode VARCHAR2(6) NOT NULL,
RatingDescription VARCHAR2(100)
);

CREATE TABLE Customer
(
CustomerID NUMBER(6) CONSTRAINT PK_CustomerID PRIMARY KEY,
CustomerFName VARCHAR2(20) NOT NULL,
CustomerLNAME VARCHAR2(20) NOT NULL,
CustomerPhone NUMBER(10) NOT NULL,
CustomerStreet VARCHAR2(15),
CustomerCity VARCHAR2(30),
CustomerState CHAR(2),
CustomerEmail VARCHAR2(75) NOT NULL,
SignUp_Date DATE NOT NULL,
InactiveDate DATE,
InactiveReason VARCHAR2(250),
EmployeeID NUMBER(6)
);

CREATE TABLE Employee
(
EmployeeID NUMBER(6) CONSTRAINT PK_EmployeeID PRIMARY KEY,
EmployeeFName VARCHAR2(20) NOT NULL,
EmployeeLName VARCHAR2(20) NOT NULL
);

CREATE TABLE Packages
(
PackageID NUMBER(6) CONSTRAINT pk_PackageID PRIMARY KEY,
PackageName VARCHAR2(50) NOT NULL,
PackagePrice NUMBER(5) NOT NULL
);

CREATE TABLE Channel_Package
(
PackageID NUMBER(6) CONSTRAINT pk_Package_PackageID PRIMARY KEY,
ChannelID NUMBER(5) NOT NULL
);

CREATE TABLE Channel_Type
(
ChannelTypeID VARCHAR2(12) CONSTRAINT pk_ChannelTypeID PRIMARY KEY,
ChannelTypeDesc VARCHAR2(125)
);

CREATE TABLE Channel
(
ChannelID NUMBER(5) CONSTRAINT pk_ChannelID PRIMARY KEY,
ChannelName VARCHAR2(40) NOT NULL,
ChannelNumber NUMBER(5) NOT NULL,
ChannelTypeID VARCHAR2(12) NOT NULL
);

CREATE TABLE Program
(
ProgramID NUMBER(2) CONSTRAINT PK_ProgramID PRIMARY KEY,
ProgramName VARCHAR2(40) NOT NULL,
ProgramLength NUMBER(4),
ProgramDesc VARCHAR2(125),
ChannelTypeID VARCHAR2(12) NOT NULL,
RatingID NUMBER(5),
SupplierID NUMBER(5) NOT NULL
);

CREATE TABLE Survey
(
SurveyID NUMBER(5) CONSTRAINT pk_SurveyID PRIMARY KEY,
CustomerID NUMBER(6) NOT NULL,
ChannelID NUMBER(5) NOT NULL,
SurveyDate DATE
);

CREATE TABLE Billing
(
BillingID NUMBER(12) CONSTRAINT pk_BillingID PRIMARY KEY,
BillingDate DATE NOT NULL,
BillingPaidDate DATE,
BillingDueAmount VARCHAR2(5) NOT NULL,
BillingPaidAmount VARCHAR2(5),
SubscriptionID NUMBER(12),
BillingDueDate DATE
);

CREATE TABLE Subscription
(
SubscriptionID NUMBER(12) CONSTRAINT pk_SubscriptionID PRIMARY KEY,
CustomerID NUMBER(6) NOT NULL,
PackageID NUMBER(6) NOT NULL,
StartDate DATE,
EndDate DATE
);

CREATE TABLE Schedule
(
ChannelID NUMBER(5) CONSTRAINT pk_Schedule_ChannelID PRIMARY KEY,
ProgramID NUMBER(2) NOT NULL,
Showtime DATE
);

ALTER TABLE Schedule
ADD CONSTRAINT FK_ChannelID FOREIGN KEY (ChannelID)
REFERENCES Channel;

ALTER TABLE Customer
ADD CONSTRAINT FK_EmployeeID FOREIGN KEY (EmployeeID)
REFERENCES Employee;

ALTER TABLE Channel
ADD CONSTRAINT FK_ChannelTypeID FOREIGN KEY (ChannelTypeID)
REFERENCES Channel_Type;

ALTER TABLE Channel_Package
ADD CONSTRAINT FK_PackageID FOREIGN KEY (PackageID)
REFERENCES Packages;

ALTER TABLE Subscription
ADD CONSTRAINT FK_CustomerID FOREIGN KEY (CustomerID)
REFERENCES Customer;

ALTER TABLE Program
ADD CONSTRAINT FK_RatingID FOREIGN KEY (RatingID)
REFERENCES Rating;

ALTER TABLE Survey
ADD CONSTRAINT FK_Survey_CustomerID FOREIGN KEY (CustomerID)
REFERENCES Customer;

INSERT INTO Employee
VALUES (100001, 'Bob', 'Thorton');
INSERT INTO Employee
VALUES (100002, 'Jill', 'Valentine');
INSERT INTO Employee
VALUES (100003, 'Russell', 'Crow');

INSERT INTO Customer
VALUES (000001, 'Howard', 'Hughes', 3215874123, 'shaw ave', 'clovis', 'ca', 'howard.hughes@email.com', '01-APR-14', '05-APR-14', 'NULL', 100001);
INSERT INTO Customer
VALUES (000002, 'Bill', 'Gates', 3215874123, 'fresno ave', 'fresno', 'ca', 'bill.gates@email.com', '07-APR-14', '09-APR-14', 'NULL', 100002);
INSERT INTO Customer
VALUES (000003, 'ben', 'hur', 3215874123, 'herndon ave', 'clovis', 'ca', 'ben.hur@email.com', '09-APR-14', '16-APR-14', 'NULL', 100003);

INSERT INTO Rating
VALUES(10000,'G+','RATING G FOR General');
INSERT INTO Rating
VALUES(10001,'PG','RATING PG FOR Parental Guidance Recommended');
INSERT INTO Rating
VALUES(10002,'M+','RATING M FOR MATURE AUDIENCES 18+');
INSERT INTO Rating
VALUES(10003,'MA','RATING MA15+ FOR MATURE ACCOMPANIED BY PARENT');
INSERT INTO Rating
VALUES(10004,'R+','RATING R FOR Restricted UNDER 17+ NOT ADMITTED');

INSERT INTO Program
VALUES(14,'Sale of the Year',24,'Game Show Lifestyle','Adult',10000,12345);
INSERT INTO Program
VALUES(12,'Opera by the Nile',240,'Luciano Pavorotti sings opera on the Nile','Family',10001,54321);
INSERT INTO Program
VALUES(10,'Fly Killers are Back',175,'Flies attack the city once again; the Bygone Brigade are back',
'Adult',10002,98765);

INSERT INTO Survey
VALUES(42680,01,54321,'12-JAN-14');
INSERT INTO Survey
VALUES(24682,02,12345,'15-MAR-14');
INSERT INTO Survey
VALUES(86420,03,90877,'05-SEP-14');

INSERT INTO Subscription
VALUES(548621059347,000001,996632,'01-NOV-14','01-NOV-15');
INSERT INTO Subscription
VALUES(548621059350,000002,996633,'01-JAN-04','01-JAN-15');
INSERT INTO Subscription
VALUES(548621059400,000003,985212,'01-JUN-01','01-JUN-15');

INSERT INTO Billing
VALUES(963758413267,'15-DEC-14','22-DEC-14','111','75',548621059347,'25-DEC-14');
INSERT INTO Billing
VALUES(963741589267,'15-JUN-14','22-JUN-14','155','155',548621059350,'25-JUN-14');
INSERT INTO Billing
VALUES(963758418730,'01-MAR-14','15-MAR-14','71','71',548621059400,'15-MAR-14');

INSERT INTO Packages
VALUES(996632,'Movies Galore',30);
INSERT INTO Packages
VALUES(996633,'News Globe',30);
INSERT INTO Packages
VALUES(985212,'Total Watcher',40);

INSERT INTO Channel_Package
VALUES(996632,1);
INSERT INTO Channel_Package
VALUES(996633,6);
INSERT INTO Channel_Package
VALUES(985212,9);

INSERT INTO Channel_Type
VALUES('Adult','Appropriate for adults');
INSERT INTO Channel_Type
VALUES('Family','Appropriate for family');
INSERT INTO Channel_Type
VALUES('Kids','Appropriate for children');

INSERT INTO Channel
VALUES(1,'Movie',3245,'Family');
INSERT INTO Channel
VALUES(6,'Lifestyle',3244,'Adult');
INSERT INTO Channel
VALUES(9,'Sports',3241,'Family');

INSERT INTO Schedule
VALUES(1,14,'12-APR-14');
INSERT INTO Schedule
VALUES(6,12,'13-APR-14');
INSERT INTO Schedule
VALUES(9,10,'14-APR-14');

Explanation / Answer

UPDATE CUSTOMER
SET InactiveDate =SYSDATE,InactiveReason='Bills overdue for more than 30 days'
WHERE CUSTID IN
(SELECT CustomerID FROM Subscription WHERE SubscriptionID IN (SELECT SubscriptionID FROM Billing
WHERE (SYSDATE - BillingDueDate ) > 30 AND BillingPaidDate IS NULL));

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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