Reference no: EM132293922

Oracle SQL

The purpose of this exercise is to provide the Marketing department with a method to sort a predefined data list by zip code, last name, first name so they can take advantage of bulk mailing rates.  

Assignment:

1. Here is the table with the following fields called "Homework1Data" with (4 fields), then run the inserts below into the table (Figure 1-1).

2. Please help with a single SQL Query that has the following fields (6 fields so they can easily be moved around using a Mail Merge) using these names (alias); Firstname, Lastname, Address, City, State, Zip be sure to sort by zip code, last name, first name

3. Turn in a copy of your SQL Query (single statement - not a procedure)

Hint:

The magic exist in the string manipulation functions (LENGTH, INSTR, SUBSTR, etc.)

Figure 1-1:

this is the TABLE homework1data (  name VARCHAR2(30),  address VARCHAR2(30),  location VARCHAR2(30),  zip VARCHAR2(10));

-----------------------------------------

INSERT INTO Homework1Data  

(Name, Address, Location, Zip) 

VALUES 

('Ferguson, Shawn M.', '1940 Fountainview Court', 'Reynoldsburg, Ohio', '43068');

INSERT INTO Homework1Data  

(Name, Address, Location, Zip) 

VALUES 

('Phillips, George', '19 Pleasant St.', 'Columbus, OH', '43231');

INSERT INTO Homework1Data  

(Name, Address, Location, Zip) 

VALUES 

('Thompson, Mary', '200 E. Main St.', 'Columbus, Oh', '43215');

INSERT INTO Homework1Data  

(Name, Address, Location, Zip) 

VALUES 

('Swatson, Robert', '584 Yellowstone Dr.', 'Westerville, OH', '43081');

INSERT INTO Homework1Data  

(Name, Address, Location, Zip) 

VALUES 

('Banks, Heather T.', '19 Pleasant St.', 'Columbus, Ohio', '43231');

This is the first part of the SQL query;

SELECT SUBSTR('Ferguson, Shawn M.' INSTR('Ferguson, Shawn M.',' '))

AS LASTNAME

FROM DUAL;