Part 1: Creating a table PLACE using the command: create table PLACE(P_ZIPCODE v
ID: 3562953 • Letter: P
Question
Part 1: Creating a table PLACE using the command:
create table PLACE(P_ZIPCODE varchar2(5) primary key,
P_STATE varchar2(2),
P_CITY varchar2(20),
P_STATECAPITAL varchar2(1));
Part 2: Populate the table PLACE using the script in the file lab4sql.txt:
Part 3: Write a query to perform each of the following 8 tasks. Save the text of your queries in a text file and export the results of each query into an individual spreadsheet file. Consult chapters 7 & 8 of your textbook if you need help.
[1] List only the P_CITY and P_STATE names of the cities whose P_STATECAPITAL fields equal
Explanation / Answer
1) Select P_CITY,P_STATE from PLACE where P_STATECAPITAL='Y';
2) Select Distinct P_CITY from PLACE where P_STATE='FL' Order By P_CITY ASC;
3) Select * from PLACE Where P_STATE IN (IL,IN,WI);
4) Select P_CITY,P_STATE,P_ZIPCODE from PLACE where P_ZIPCODE Between 20000 AND 30000;
5) Select P_CITY,P_STATE,P_ZIPCODE from PLACE where P_CITY Like 'Z%';
6) Select P_CITY,P_STATE,P_ZIPCODE from PLACE where P_CITY Like '%Y' OR P_STATE Like '%Y';
7) Select P_CITY,P_STATE,P_ZIPCODE from PLACE where P_STATE='FL' AND P_ZIPCODE Like '%7%';
8) Select P_CITY,P_STATE,P_ZIPCODE from PLACE where P_ZIPCODE Not Between 10000 AND 90000;