Assignment 3 - Single Table Queries
select ZIP, OWNER_NUM, LAST_NAME from owner order by 1 desc, 2, 3 desc;
7. Select Zip, Owner_Num and Last_Name sorted by zip code in descending order, owner number in ascending order and last name in descending order using ordinal positions (Owner Table)
select FIRST_NAME, LAST_NAME from owner order by LAST_NAME;
Select First_Name, Last_Name sorted by Last_Name (Owner Table)
select FIRST_NAME, LAST_NAME from owner order by LAST_NAME, FIRST_NAME;
Select First_Name, Last_Name sorted by Last_Name and then by First_Name (Owner Table)
select OWNER_NUM, LAST_NAME from owner order by OWNER_NUM asc;
Select Owner_num, Last_Name sorted by Owner_Num in ascending order (Owner Table)
select OWNER_NUM, LAST_NAME from owner order by OWNER_NUM desc;
Select Owner_num, Last_Name sorted by Owner_Num in descending order (Owner Table)
select * from owner;
Select all columns and all rows (Owner Table)
select OWNER_NUM, FIRST_NAME, LAST_NAME from owner;
Select only Owner_Num, First_Name, Last_Name (Owner Table)
select FIRST_NAME, LAST_NAME, CITY from owner where CITY like 'Glander Bay' or (LAST_NAME like '%a%' and CITY like 'Bowton';
The boss asks for a report... "I'd like to see which owners are from Glander Bay and Bowton or have the letter 'A' in their first name". (Select first and last name as well as the selection criteria field) (Owner Table)
select FIRST_NAME, LAST_NAME, CITY from owner where CITY = 'Glander Bay';
Which owners are from "Glander Bay"? (Select first and last name as well as the selection criteria field) (Owner Table)
select FIRST_NAME, LAST_NAME, CITY from owner where CITY like 'Bowton' or CITY like 'Lewiston';
Which owners are from Bowton or "Lewiston"? (Select first and last name as well as the selection criteria field) (Owner Table)
select FIRST_NAME, LAST_NAME, CITY from owner where LAST_NAME >= 'n' xor CITY = 'Glander Bay';
Which owners are in the upper half of the alphabet OR from "Glander Bay" but not both? This one is an exclusive or, (XOR) instead of a normal OR. (Note: "Upper half" of the alphabet means N through Z). (Select first and last name as well as the selection criteria field) (Owner Table)
select FIRST_NAME, LAST_NAME, CITY from owner where LAST_NAME >= 'n' or CITY = 'Glander Bay';
Which owners are in the upper half of the alphabet OR from "Glander Bay"? (Note: "Upper half" of the alphabet means N through Z). (Select first and last name as well as the selection criteria field) (Owner Table)
select FIRST_NAME, LAST_NAME, CITY from owner where LAST_NAME >= 'n' and CITY = 'Glander Bay';
Which owners are in the upper half of the alphabet and from "Glander Bay"? (Note: "Upper half" of the alphabet means N through Z). (Select first and last name as well as the selection criteria field) (Owner Table)
select FIRST_NAME, LAST_NAME, CITY from owner where CITY != 'Glander Bay';
Which owners are not from "Glander Bay"? (Select first and last name as well as the selection criteria field) (Owner Table)
select LAST_NAME from owner where LAST_NAME like 'A%';
Which owners have a Last_Name beginning with the letter "A"
select LAST_NAME from owner where LAST_NAME like '%son';
Which owners have a Last_Name ending in "son" (Owner Table)
select LAST_NAME from owner where LAST_NAME like '%a';
Which owners have a Last_Name ending with the letter "a" (Owner Table)
select LAST_NAME from owner where LAST_NAME like '%a%';
Which owners have a Last_Name with the letter "A" anywhere in their last name (Owner Table)
select LAST_NAME, ZIP from owner where ADDRESS like '_2%2%1';
Which owners have a Zip code beginning with any number in the first position, followed by a "2" in the second position, and then have another "2" anywhere but ending with a "1" in the last position (Owner Table)
select FIRST_NAME, LAST_NAME, ADDRESS from owner where ADDRESS is not Null;
Which owners have a value in the address field? (Select first and last name as well as the selection criteria field) (Owner Table)
select FIRST_NAME, LAST_NAME, CITY from owner where FIRST_NAME like '%a%' or LAST_NAME like '%a%' or CITY like '%a%';
Which owners have an "A" in their first name, OR an "A" in their last name OR an "A" in their city name? (Select first and last name as well as the selection criteria field) (Owner Table)
select FIRST_NAME, LAST_NAME, CITY from owner where FIRST_NAME like '%a%' and LAST_NAME like '%a%' and CITY like '%a%';
Which owners have an "A" in their first name, and an "A" in their last name and an "A" in their city name? (Select first and last name as well as the selection criteria field) (Owner Table)
select FIRST_NAME, LAST_NAME, ZIP from owner where (LAST_NAME like '%a%' or LAST_NAME like '%e%') and ZIP like '%2%';
Which owners have an "A" in their last name OR... have an "E" in their last name AND have a "2" in their Zip code? (Select first and last name as well as the selection criteria field) (Owner Table)
select FIRST_NAME, LAST_NAME, ZIP from owner where (LAST_NAME like '%a%' xor LAST_NAME like '%e%') and ZIP like '%2%';
Which owners have an "A" or an "E" in their last name (but can't have both letters) and have a "2" in their Zip code? (Select first and last name as well as the selection criteria field) (Owner Table)
select FIRST_NAME, LAST_NAME, ZIP from owner where LAST_NAME like '%a%' or LAST_NAME like '%e%' and ZIP like '%2%';
Which owners have an "A" or an "E" in their last name and have a "2" in their Zip code? (Select first and last name as well as the selection criteria field) (Owner Table)
select FIRST_NAME, LAST_NAME, CITY from owner where LAST_NAME <= 'm' and CITY like 'Bowton" or LAST_NAME >= 'm' and CITY like 'Glander Bay' order by 3 desc, 2;
Which owners have last names in the lower half (A to M) of the alphabet and are from "Bowton", or are from the upper half (M and above) of the alphabet and are from "Glander Bay"? Sort the result table by city in descending order and last name in ascending order. (Select first and last name as well as the selection criteria field) (Owner Table)
select FIRST_NAME, LAST_NAME from owner where LAST_NAME >= 'n';
Which owners have last names in the second half of the alphabet? (beginning with the letter "N" and above). (Select first and last name as well as the selection criteria field) (Owner Table)
select FIRST_NAME, LAST_NAME, ADDRESS from owner where ADDRESS is Null;
Which owners have no address? (trick question, everyone has an address but if you were looking for a null value in the address field, go ahead and query it). (Select first and last name as well as the selection criteria field) (Owner Table)
select LAST_NAME, ZIP from owner where ADDRESS like '_1%';
Which owners have the number "1" as the second number in their Zip code (Owner Table)
select LAST_NAME, ADDRESS from owner where ADDRESS like '%2%2%';
Which owners have the number "2" followed anywhere by another "2" in their Address (Owner Table)
select LAST_NAME, ADDRESS from owner where ADDRESS '%22%';
Which owners have the number "2" followed immediately by another "2" anywhere in their Address (Owner Table)
select LAST_NAME, ADDRESS from owner where ADDRESS like '%2%';
Which owners have the number "2" in their Address (Owner Table)
select FIRST_NAME, LAST_NAME, ZIP from owner where FIRST_NAME like '%and%' and ZIP like '31062';
Which owners have the word "and" in their first name and are from Zip code "31062"? (Select first and last name as well as the selection criteria field) (Owner Table)
select FIRST_NAME, LAST_NAME from owner where LAST_NAME between 'Blake' and 'Kelly';
Who has a last name greater or equal to "Blake" but less than or equal to "Kelly"? (Select first and last name as well as the selection criteria field) (Owner Table)
select FIRST_NAME, LAST_NAME from owner where LAST_NAME <= 'm';
Who is in the lower half? ("M" and less alphabetically)? (Select first and last name as well as the selection criteria field) (Owner Table)