Load the createInsurance.txt script file to create and populate the tables necessary for this assignment. The insurance database includes the following tables: Employer, Member, Carrier, Plan and Agent.
Employers have employees (that are called members). The members subscribe (or elect) to participate in a health plan. These health plans are offered by Insurance Carriers and sold to Employers by Agents. The table relationships are defined for you and are pictured below. Review the table structures before you attempt to solve the assignment problems.
You will need to write the SQL to solve 14 query problems defined below. You will build a single script file named yourLastName_A8Script.txt that contains all your SQL and SQL/PL statement necessary to solve and format the 14 queries. Output must be directed to a single .txt file named yourLastName_A8Spool.txt. You will need to submit both files for grading.
Assignment Steps:
Step 1) Download and execute the createInsuranceScript.txt script file to build and populate the 5 tables of the database. The code is error free – and should execute without issue.
Step 2) Verify that your tables were created. You can visually inspect the table data using SQL Developer's user interface – by clicking on the Tables under the connection – and drilling down to view the table list – and then selecting each table and viewing the Data tab in the worksheet area. **No output from this section needs to be included in your spooled file.
Step 3) Write the SQL code for the queries needed to create the result sets needed to meet the assignment problem specification -- as defined in the accompanying document. Refer back to Assignment 6 – and use the same template to guide you in organizing your script file.
Step 4) Formatted output is required! You will need to write PL statements to create titles that bookend and title your result sets. Define the page size and line size of your output. Also, format the display of the columns in your result sets – all numbers should be formatted: currency to display as currency, other number to display with fixed number of decimal places. Your result set output should fit each row to one line of output without it wrapping to multiple lines.
Reduce the width of text columns by truncating the text output. Remember that COLUMN statements persist – and thus only need to be defined once for each column – not once for each query. Place all COLUMN statements together near the top of your script file make it easy to grade your SQL solutions. (Be sure to place a CLEAR COLUMNS statement at the top of your code).
Test your queries individually and then test your script file to ensure that it executes error free. Add the
Spool statements to generate and capture the spooled output and post both the script file and spool file
Project Deliverables:
1. (3 points) Script file, properly delineated and neatly organized with solutions to queries 1 – 14.
This file should only contain SQL code, comments and PL statements to format your output.
2. (24 points). Correct SQL statements for query problems 1 – 12.
3. (6 points). Correct SQL statements for query problems 13 and 14.
4. (3 points) Spool file with contents of echoed SQL and spooled query output.
5. (4 points) Formatted output in your spool file that displays string and numeric data meaningfully in the generated result sets.
#sqlassignmenthelp #oracleassignmenthelp #oraclequeryhelp #sqlqueryhelp #oracle #sql #plsql
Solutions: Database creation ,tables , insert data
--drop table Member CASCADE CONSTRAINTS;--drop table Employer CASCADE CONSTRAINTS;--drop table Plan CASCADE CONSTRAINTS;--drop table Carrier CASCADE CONSTRAINTS;--drop table Agent CASCADE CONSTRAINTS;--commit;CREATE TABLE Carrier( CarrierID varchar2(4) not null primary key,carName varchar2(35),carAddress varchar2(50),carCity varchar2(30),carStCode varchar2(2),carZip varchar2(10),carPhone varchar2(10),carWebSite varchar2(255),carContactFirstname varchar2(35),carContactLastName varchar2(35),carEmail varchar2(255));commit;INSERT INTO Carrier VALUES ('ANTH','Anthem','100 East Way','Atlanta','GA','36366','9064773364','www.athem.org','Nate','Zechery','zechery.nate@anthem.org');INSERT INTO Carrier VALUES ('BCBS','Blue Cross Blue Shield','222 Sylvania Avenue','Toledo','OH','43613','4194743357','www.bcbs.com','Ann','Bailey','abailey@bcbs.com');INSERT INTO Carrier VALUES ('DLT','Delta Dental','439 South Parkway','Phoenix','AZ','65100','8864375036','www.deltadental.com','Madeline','Masters','mmasters@deltadental.com');INSERT INTO Carrier VALUES ('MM','Medical Mutual','6800 South Main','Pittsburgh','PA','12005','2074553300','www.medicalmutual.com','Emily','Hackson','ehackson@medicalmutual.com');INSERT INTO Carrier VALUES ('NWD','Nationwide','100 High Street','Columbus','OH','45020','6148550000','www.nationwide.com','Nick','Jacione','jacione.nick@nationwide.com');INSERT INTO Carrier VALUES ('PHC','Paramount Health Care Grp','PO Box 1228','Boston','MA','01445','2153781880','www.paramounthealth.org','Josh','Watkins','jwatkins@paramount.org');commit;CREATE TABLE Agent( AgentID varchar2(5) not null primary key,agentFname varchar2(35),agentLName varchar2(35),agentPhone varchar2(13),AgentuperID varchar2(5),agentTitle varchar2(25));Commit;INSERT INTO Agent VALUES ('AA020','Adam','Akerson','(440)123-1111','JR010','Salesman');INSERT INTO Agent VALUES ('BA550','Bill','Almes','(915)222-9999','JR010','Salesman');INSERT INTO Agent VALUES ('JR010','Jordan','Richards','(419)222-1212','JR990','District Manager');INSERT INTO Agent VALUES ('JR990','John','Robie','(419)841-1188', NULL,'VP Sales');INSERT INTO Agent VALUES ('LR020','Lauren','Renard','(419)290-1677', 'AA020','Intern');INSERT INTO Agent VALUES ('RB001','Ron','Bell','(419)841-1189',NULL,'President');INSERT INTO Agent VALUES ('TM220','Tom','Matthews','(704)377-2266','JR990','Regional Manager');Commit;CREATE TABLE Employer( EmployerID integer not null primary key,emplName varchar2(35),emplAddress varchar2(50),emplCity varchar2(30),emplState varchar2(2),emplZip varchar2(10),emplPhone varchar2(10),emplContactFirstname varchar2(35),emplContactLastName varchar2(35),emplContactEmail varchar2(255),AgentID varchar2(5) references Agent);commit;INSERT INTO Employer VALUES (1,'Segal','#11 - Hwy 16','Bonneville','PA','54323','4092411234','Mark','Stark','mstark@segal.com', 'RB001');INSERT INTO Employer VALUES (2,'Fair Weather Enterprises','345 Wiltshire Ave','New Surrey','MA','76566','5372551200','John','Adams','jadams@hotmail.com', 'JR010');INSERT INTO Employer VALUES (3,'Halston and Co.','387 Roper Place','Midland','WY','12658','3049554300','Julie','Reynolds','jreynolds@halston.com', 'JR990');INSERT INTO Employer VALUES (4,'MMB Holdings','4090 Lethbridge','Creston','OH','44065','6141252115','Jack','James','jj33@yahoo.com', 'JR010');INSERT INTO Employer VALUES (5,'WardCo','35-9087 14 Street','Munro','MI','48114','7348438500','Paul','Tinnion','ptinnion@comcast.net', 'LR020');INSERT INTO Employer VALUES (6,'Bakertime Mobile','759 East 31 Ave','Trenton','AL','24568','6157435555','Martin','Jackson','jackson.martin@bakertime.com','JR990');insert into Employer values (7, 'Skidoo', '12200 Toban Drive', 'Arlington', 'TX', '76011', '8175501252', 'Shirley', 'Kennedy', 'skennedy0@berkeley.edu', 'AA020');insert into Employer values (8, 'Twitternation', '73 Nova Trail', 'Arlington', 'VA', '22234', '5712853529', 'Judith', 'Martinez', 'jmartinez1@domainmarket.com', 'JR010');insert into Employer values (9, 'Babbleset', '904 Dottie Way', 'Shreveport', 'LA', '71137', '3188494834', 'Larry', 'Spencer', 'lspencer2@cdc.gov', 'TM220');insert into Employer values (10, 'Kwimbee', '2 Ohio Trail', 'Fort Lauderdale', 'FL', '33305', '9542007888', 'Lillian', 'Snyder', 'lsnyder3@amazon.com', 'TM220');insert into Employer values (11, 'Skalith', '4 Manitowish Lane', 'Indianapolis', 'IN', '46202', '7657670387', 'Jonathan', 'Graham', 'jgraham4@yellowbook.com', 'JR990');commit;CREATE TABLE Plan( PlanID integer not null primary key,plnDescription varchar2(35),plnCost decimal (8,2),CarrierID varchar2(4) references Carrier);commit;INSERT INTO Plan VALUES (1,'Single Basic Medical',125.00,'BCBS');INSERT INTO Plan VALUES (2,'2-Party Basic Medical',240.00,'BCBS');INSERT INTO Plan VALUES (3,'Family Basic Medical',285.00,'BCBS');INSERT INTO Plan VALUES (4,'Single SuperMed',160.00,'ANTH');INSERT INTO Plan VALUES (5,'2-Party SuperMed',275.00,'ANTH');INSERT INTO Plan VALUES (6,'Family SuperMed',350.00,'ANTH');INSERT INTO Plan VALUES (7,'Single Dental Only',85.00,'DLT');INSERT INTO Plan VALUES (8,'Family Dental Only',190.00,'DLT');INSERT INTO Plan VALUES (9,'Life Only',25.50,'NWD');INSERT INTO Plan VALUES (10,'Single SuperMed with Dental',185.00,'MM');INSERT INTO Plan VALUES (11,'2-Party SuperMed with Dental',320.00,'MM');INSERT INTO Plan VALUES (12,'Family SuperMed with Dental',425.00,'MM');commit;CREATE TABLE Member( MemberNo integer not null primary key,mbrFirstname varchar2(35),mbrLastName varchar2(35),mbrStreet varchar2(50),mbrCity varchar2(30),mbrState varchar2(2),mbrZip varchar2(10),mbrPhoneNo varchar2(10),PlanID integer references Plan,mbrEmail varchar2(255),mbrDateEffective date,employerID integer references Employer);commit;INSERT INTO Member VALUES (1,'Val','Kelly','1489 Mockingbird Lane','Sherman','OK','75090','9038933947',3,'imafriend@hotmail.com',TO_DATE('30-SEP-2007', 'dd-MON-yyyy'),1);INSERT INTO Member VALUES (2,'George','Martin','875 Greenwaters','Lavender','OK','78947','4055272810',2,'georgem@kxyd.com',TO_DATE('30-JUN-2013', 'dd-MON-yyyy'),1);INSERT INTO Member VALUES (3,'Bob','Smith','222 West Oak','Silver','TX','76949','9047624451',3,'catchbob@home.com',TO_DATE('15-AUG-2001', 'dd-MON-yyyy'),1);INSERT INTO Member VALUES (4,'Baylee','Michaels','543 North Meridian','Crossett','AR','71635','8703643571',3,'onthego@hotmail.com',TO_DATE('16-SEP-2002', 'dd-MON-yyyy'),1);INSERT INTO Member VALUES (5,'Lisa','Kelly','37110 Huntington','Wichita','KS','67201','3168316887',2,'panda@excite.com',TO_DATE('18-MAY-2010', 'dd-MON-yyyy'),5);INSERT INTO Member VALUES (6,'Jacob','Richards','6114 Wandering Way','','OK','74501','9184266796',1,'laughsalot@friend.com',TO_DATE('02-FEB-2011', 'dd-MON-yyyy'),4);INSERT INTO Member VALUES (7,'Danette','Randolph','201 Everette Drive','Midland','TX','79701','9156997345',5,'lookatme@hotmail.com',TO_DATE('01-MAR-1997', 'dd-MON-yyyy'),4);INSERT INTO Member VALUES (8,'Elizabeth','Wells','2944 Rosehaven','Benton','AR','72022','5017943638',2,'lizzieisbusy@huntington.edu',TO_DATE('02-MAR-1991', 'dd-MON-yyyy'),2);INSERT INTO Member VALUES (9,'Joan','Viaze','4012 Ridgeway','Dodge City','KS','67843','6202252214',3,'jviaze@motorco.com',TO_DATE('04-MAY-2000', 'dd-MON-yyyy'),2);INSERT INTO Member VALUES (10,'Peter','Lansing','9459 Thorn Ridge','Muskogee','OK','74403','9186221380',3,'Planing@home.com',TO_DATE('05-JUL-2007', 'dd-MON-yyyy'),5);INSERT INTO Member VALUES (11,'Tan','Wu','2518 Van Buren','Midland','TX','79701','9156973993',3,'mrwutoyou@home.com',TO_DATE('06-OCT-2015', 'dd-MON-yyyy'),5);INSERT INTO Member VALUES (12,'Ken','Ho','1100 Red Ridge','Benton','AR','72022','5013151588',1,'howdyim@home.com',TO_DATE('08-SEP-2016', 'dd-MON-yyyy'),5);INSERT INTO Member VALUES (13,'Omar','Lavender','9494 Broken Gate','Dodge City','KS','67843','6202272889',1,'osharif@hotmail.com',TO_DATE('09-OCT-2014', 'dd-MON-yyyy'),2);INSERT INTO Member VALUES (14,'Gladys','Burgerman','2912 Crestline Drive','Miami','OK','74355','9185429652',9,'kx874474@hotmail.com',TO_DATE('04-SEP-1997', 'dd-MON-yyyy'),2);INSERT INTO Member VALUES (15,'Margaret','Shue','4949 Eagle Road','','TX','79701','9155200297',3,'iftheshuefits@merchants.org',TO_DATE('08-MAR-2003', 'dd-MON-yyyy'),6);INSERT INTO Member VALUES (16,'Debra','Sharman','1484 Cedar Lane','Benton','AR','72022','9157948546',3,'dshrmand@juniper.org',TO_DATE('12-SEP-2004', 'dd-MON-yyyy'),1);INSERT INTO Member VALUES (17,'Loubna','Parish','3132 Overland Park','Dodge City','KS','67843','6204081147',2,'lparish@hotmail.com',TO_DATE('16-SEP-2004', 'dd-MON-yyyy'),5);INSERT INTO Member VALUES (18,'Katie','Hsieh','784 Western Drive','Tulsa','OK','74110','9186642810',2,'khsieh1234@aol.com',TO_DATE('18-SEP-2006', 'dd-MON-yyyy'),6);INSERT INTO Member VALUES (19,'Paul','Kopochesky','1907 Greenburg Drive','Commerce','TX','75428','9038868630',1,'mrkopochesky@aol.com',TO_DATE('20-JAN-2008', 'dd-MON-yyyy'),4);INSERT INTO Member VALUES (20,'Frank','Bellmonte','7654 Collier','Clairemore','OK','74018','9183412943',1,'tangodancer@aol.com',TO_DATE('22-FEB-2009', 'dd-MON-yyyy'),6);INSERT INTO Member VALUES (21,'Fred','McMann','1988 South Hampton','','LA','71483','3186280652',1,'fredsred@hotmail.com',TO_DATE('24-JAN-2010', 'dd-MON-yyyy'),6);INSERT INTO Member VALUES (22,'Frank','Ackers','4204 Pebble Creek','Monroe','LA','71208','3186510957',2,'frankieaker@aol.coom',TO_DATE('26-MAR-2011', 'dd-MON-yyyy'),6);INSERT INTO Member VALUES (23,'Paul','Brady','5757 Crooked Creek','Bunkie','LA','71322','3183461071',2,'pbrady@aol.com',TO_DATE('28-APR-2012', 'dd-MON-yyyy'),4);INSERT INTO Member VALUES (24,'George','Levingston','9777 Old Willow','Bunkie','LA','71322','3188382382',4,'glevingston@auniv.edu',TO_DATE('30-APR-2012', 'dd-MON-yyyy'),2);INSERT INTO Member VALUES (25,'Tabitha','Daniels','100 North Gaylord Drive','Oklahoma City','OK','73109','4057511755',2,'tdaniels@marshalls.com',TO_DATE('08-MAR-2001', 'dd-MON-yyyy'),2);INSERT INTO Member VALUES (26,'Vonka','Viera','9432 Mercer Lane','Sherman','TX','75090','9038943147',7,'vviera9484@aol.com',TO_DATE('03-FEB-1999', 'dd-MON-yyyy'),1);INSERT INTO Member VALUES (27,'Lola','Dougherty','5874 Nightingale Lane','Sherman','TX','75090','9038932913',4,'lrrrablelola@home.com',TO_DATE('12-SEP-2013', 'dd-MON-yyyy'),1);INSERT INTO Member VALUES (28,'Mark','Wallace','Apt. 23, Avondale Drive','Dallas','TX','75208','9729139572',1,'callwally@hotmail.com',TO_DATE('12-APR-2015', 'dd-MON-yyyy'),2);INSERT INTO Member VALUES (29,'Tracy','James','3847 Rocky Road','Little Rock','AR','72211','5012194804',2,'inthought@morganhills.com',TO_DATE('11-SEP-2016', 'dd-MON-yyyy'),2);INSERT INTO Member VALUES (30,'Peter','Gallant','4839 Broadway','Tulsa','OK','74109','9186641333',2,'marathonrunner@fastnetwork.com',TO_DATE('22-JUL-2012', 'dd-MON-yyyy'),2);INSERT INTO Member VALUES (31,'Joan','Allen','56 Wellsley Road','Commerce','OK','75429','9186750722',4,'fastcatcher@aol.com',TO_DATE('01-MAY-1988', 'dd-MON-yyyy'),6);INSERT INTO Member VALUES (32,'Loubna','Parish','3132 Overland Park','Dodge City','KS','67843','6204081147',2,'lparich@hotmail.com',TO_DATE('14-JAN-2010', 'dd-MON-yyyy'),4);insert into Member VALUES (33, 'Alan', 'Olson', '5145 Scofield Drive', 'Kansas City', 'MO', '64130', '8166970261', 1, 'aolson0@soundcloud.com', '17-Aug-2015', 1);insert into Member VALUES (34, 'Elizabeth', 'Elliott', '66130 Hanson Trail', 'Buffalo', 'NY', '14205', '7165726524', 2, 'eelliott1@artisteer.com', '10-Sep-2016', 2);insert into Member VALUES (43, 'Jean', 'Lawrence', '2157 Portage Junction', 'Lansing', 'MI', '48901', '5175790115', 4, 'jlawrence2@github.com', '12-Jan-2016', 3);insert into Member VALUES (44, 'Samuel', 'Rose', '524 Eastwood Court', 'Winston Salem', 'NC', '27105', '3363371097', 5, 'srose3@furl.net', '07-Apr-2016', 1);insert into Member VALUES (45, 'Andrew', 'Owens', '29 Talmadge Drive', 'Independence', 'MO', '64054', '8162577649', 6, 'aowens4@bizjournals.com', '27-Mar-2017', 2);insert into Member VALUES (46, 'Daniel', 'Cox', '7070 Arrowood Alley', 'Hartford', 'CT', '06140', '8607711351', 12, 'dcox5@cdbaby.com', '28-Jun-2010', 2);insert into Member VALUES (47, 'Cheryl', 'Howell', '0 Banding Center', 'Albany', 'NY', '12227', '5184278088', 10, 'chowell6@hc360.com', '12-Sep-2016', 3);insert into Member VALUES (48, 'Bruce', 'Morales', '3 Cody Pass', 'Biloxi', 'MS', '39534', '2287010816', 12, 'bmorales7@timesonline.co.uk', '18-Jun-2010', 6);insert into Member VALUES (49, 'James', 'Franklin', '7565 Fieldstone Court', 'Topeka', 'KS', '66606', '7859802737', 5, 'jfranklin8@edublogs.org', '20-Nov-2016', 6);insert into Member VALUES (50, 'Louise', 'Wells', '6121 Mayfield Trail', 'Miami', 'FL', '33261', '3054177946', 6, 'lwells9@zimbio.com', '01-Oct-2010', 6);insert into Member VALUES (51, 'Timothy', 'Russell', '2 Kennedy Plaza', 'Tacoma', 'WA', '98405', '2531473762', 7, 'trussella@dyndns.org', '11-Jan-2016', 8);insert into Member VALUES (52, 'Rachel', 'Hart', '43 Brentwood Avenue', 'Columbia', 'SC', '29240', '8036217909', 6, 'rhartb@blinklist.com', '07-Apr-2016', 8);insert into Member VALUES (53, 'Tina', 'Hansen', '54574 Hooker Hill', 'Charleston', 'WV', '25331', '3042733024', 7, 'thansenc@imageshack.us', '14-Jun-2014', 8);insert into Member VALUES (54, 'Juan', 'Payne', '3887 Morningstar Lane', 'Houston', 'TX', '77050', '2812675251', 6, 'jpayned@cpanel.net', '11-Oct-2010', 7);insert into Member VALUES (55, 'Keith', 'Kennedy', '5026 David Trail', 'Hattiesburg', 'MS', '39404', '6012159841', 3, 'kkennedye@ox.ac.uk', '20-Apr-2016', 9);insert into Member VALUES (56, 'Marie', 'Carr', '04461 Glacier Hill Parkway', 'New York City', 'NY', '10004', '7183084272', 6, 'mcarrf@buzzfeed.com', '06-Jan-2012', 9);insert into Member VALUES (57, 'Victor', 'Medina', '2 Lake View Place', 'Fort Myers', 'FL', '33913', '2394850766', 6, 'vmedinag@imageshack.us', '11-Feb-2016', 9);insert into Member VALUES (58, 'Randy', 'Martinez', '190 Arkansas Way', 'Indianapolis', 'IN', '46226', '3178836450', 2, 'rmartinezh@salon.com', '23-Jun-2016', 5);insert into Member VALUES (59, 'Larry', 'Rogers', '674 Dayton Lane', 'Terre Haute', 'IN', '47805', '8129961728', 3, 'lrogersi@europa.eu', '14-Nov-2013', 1);insert into Member VALUES (60, 'Terry', 'Carter', '2 Surrey Place', 'Berkeley', 'CA', '94705', '5102125262', 4, 'tcarterj@apache.org', '16-Nov-2015', 2);insert into Member VALUES (61, 'Janet', 'Willis', '8887 Pearson Junction', 'Portland', 'OR', '97255', '9715710647', 5, 'jwillisk@yahoo.com', '21-Oct-2013', 3);insert into Member VALUES (62, 'Debra', 'Gibson', '4133 Sachtjen Park', 'Macon', 'GA', '31205', '4788746612', 7, 'dgibsonl@go.com', '04-Sep-2015', 11);insert into Member VALUES (63, 'Paula', 'Ortiz', '85 Independence Parkway', 'Baltimore', 'MD', '21290', '4109284723', 7, 'portizm@eepurl.com', '28-Jun-2014', 11);insert into Member VALUES (64, 'Jessica', 'Baker', '17 Manley Point', 'Tampa', 'FL', '33680', '8136981662', 9, 'jbakern@blinklist.com', '13-Feb-2012', 10);insert into Member VALUES (65, 'John', 'Robinson', '547 Graedel Junction', 'Minneapolis', 'MN', '55407', '6512424596', 10, 'jrobinsono@smh.com.au', '16-Oct-2016', 9);insert into Member VALUES (66, 'Judith', 'Rice', '94869 Barnett Center', 'Dallas', 'TX', '75392', '2145394920', 10, 'jricep@e-recht24.de', '21-Jun-2011', null);insert into Member VALUES (67, 'Russell', 'Richards', '3 Mockingbird Crossing', 'Tampa', 'FL', '33620', '8135707634', 10, 'rrichardsq@netlog.com', '29-Jul-2014', 2);insert into Member VALUES (68, 'David', 'Harvey', '2 Lotheville Avenue', 'Midland', 'TX', '79705', '4327838380', 1, 'dharveyr@elpais.com', '23-Apr-2015', 6);insert into Member VALUES (69, 'Ernest', 'Stewart', '5481 Anniversary Lane', 'Lima', 'OH', '45807', '4199601768', 1, 'estewarts@hibu.com', '03-May-2016', 6);insert into Member VALUES (70, 'Howard', 'Pierce', '9 Dovetail Point', 'San Diego', 'CA', '92186', '6198261963', 1, 'hpiercet@mail.ru', '14-Jan-2017', 8);insert into Member VALUES (71, 'Nicole', 'Banks', '77 Algoma Junction', 'Houston', 'TX', '77206', '7137592453', 3, 'nbanksu@youku.com', '03-Apr-2016', 9);insert into Member VALUES (72, 'Arthur', 'Adams', '18 2nd Court', 'New York City', 'NY', '10014', '3475056191', 4, 'aadamsv@apple.com', '04-Jul-2013', 3);insert into Member VALUES (73, 'Kathryn', 'Perry', '2 Manitowish Plaza', 'Detroit', 'MI', '48211', '7344243517', 4, 'kperryw@elpais.com', '30-Aug-2015', 3);insert into Member VALUES (74, 'Christine', 'Reynolds', '547 Mandrake Drive', 'Abilene', 'TX', '79605', '3256468565', 5, 'creynoldsx@addthis.com', '23-Nov-2016', 4);insert into Member VALUES (75, 'Christina', 'Bell', '4 Talmadge Terrace', 'Orlando', 'FL', '32891', '4075151798', 6, 'cbelly@newsvine.com', '23-Sep-2015', 4);insert into Member VALUES (76, 'Denise', 'Perkins', '0 Onsgard Avenue', 'Wichita Falls', 'TX', '76305', '9406630869', 6, 'dperkinsz@usnews.com', '23-Dec-2014', 5);insert into Member VALUES (77, 'Judy', 'Schmidt', '886 Hovde Pass', 'Anniston', 'AL', '36205', '2568885760', 10, 'jschmidt10@huffingtonpost.com', '30-Jun-2012', 5);insert into Member VALUES (78, 'Sara', 'Dixon', '70696 Novick Place', 'Canton', 'OH', '44720', '2342879824', 2, 'sdixon11@ameblo.jp', '18-Nov-2014', 5);insert into Member VALUES (79, 'William', 'Brooks', '55 New Castle Plaza', 'Abilene', 'TX', '79605', '3254476634', 2, 'wbrooks12@alibaba.com', '20-May-2015', 6);insert into Member VALUES (80, 'Lois', 'West', '4 Vernon Terrace', 'Orlando', 'FL', '32819', '3218924409', 4, 'lwest13@indiegogo.com', '04-Apr-2011', 1);commit;update Memberset planid = nullwhere memberno in (7, 21, 27, 50, 60, 70);commit;