Question

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. Create a table with the following fields called "Homework1Data" with (4 fields), then run the inserts below into the table (Figure 1-1). 2. Write 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:

CREATE 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');

Answer 1

Query

SELECT
SUBSTR(name, 1, INSTR(name,',')-1) AS FirstName,
SUBSTR(name, INSTR(name,',') + 1, LENGTH(name)) AS LastName,
address,
SUBSTR(location, 1, INSTR(location,',')-1) AS City,
SUBSTR(location, INSTR(location,',') + 1, LENGTH(location)) AS State,
zip
FROM homework1data
order by Zip,LastName,FirstName;

SUBSTR(name, 1, INSTR(name,',')-1) AS FirstName --- selects name from 1st index till the index which has comma(,)

SUBSTR(name, INSTR(name,',') + 1, LENGTH(name)) AS LastName --- selects all characters after comma(,) till the end

Output