Tuesday, May 1, 2012

Database Single Table Queries

Database Single Table Queries

Simple Selection:

*Using the OWNER table in the database

Which owners are from "Stoughton"?

select FIRST_NAME, LAST_NAME, CITY 
from OWNER
where CITY = 'Stoughton';


Which owners are not from "Stoughton"?

select FIRST_NAME, LAST_NAME, CITY 
from OWNER
where CITY <> 'Stoughton';


Which owners have last names in the second half of the alphabet? (beginning with the letter  "N" and above)?

select FIRST_NAME, LAST_NAME
from OWNER
where LAST_NAME >= 'N';


Who is in the lower half of the alphabet ("M" and less alphabetically)?

select FIRST_NAME, LAST_NAME
from OWNER
where LAST_NAME <= 'M';


Who has a last name greater or equal to "Elliott" but less than or equal to "Jones"?

select FIRST_NAME, LAST_NAME
from OWNER
where LAST_NAME between 'Elliott' and 'Jones';


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_NAME, LAST_NAME, ADDRESS
from OWNER
where ADDRESS is null;


Test your query by modifying it to select all owners that have a value in their address field.

select FIRST_NAME, LAST_NAME, ADDRESS
from OWNER
where ADDRESS is not null;




Multiple Selection:

*Note; "Upper Half" of the alphabet means N through Z

Which owners have the word "and" in their first name and are from the zip code "53589"

select FIRST_NAME, LAST_NAME, ZIP
from OWNER
where FIRST_NAME like '%and%' and ZIP='53589';



Which owners are from "Beloit" or "Lodi"?

select FIRST_NAME, LAST_NAME, CITY

from OWNER
where CITY ='Beloit' or CITY ='Lodi';



Which owners have an "C" in their first name, and an "C" in their last name and an "C" in their city name?

select FIRST_NAME, LAST_NAME, CITY

from OWNER
where FIRST_NAME like '%c%' and LAST_NAME like '%c%' and CITY like '%c%';



Which owners have an "C" in their first name, OR an "C" in their last name OR an "C" in their city name? Compare this with the result table for exercise directly above.

select FIRST_NAME, LAST_NAME, CITY
from OWNER
where FIRST_NAME like '%c%' and LAST_NAME like '%c%' and CITY like '%c%';



Which owners are in the upper half of the alphabet and from "Green Bay"?

select FIRST_NAME, LAST_NAME, CITY
from OWNER
where LAST_NAME >= 'N' and CITY ='Green Bay';


Which owners are in the upper half of the alphabet OR from "Green Bay"?

select FIRST_NAME, LAST_NAME, CITY
from OWNER
where LAST_NAME >= 'M' or CITY ='Green Bay';




Which owners are in the upper half of the alphabet OR from "Green Bay" but not both? This one is an exclusive or, (XOR) instead of a normal OR.

select FIRST_NAME, LAST_NAME, CITY
from OWNER
where LAST_NAME >= 'M' xor CITY ='Green Bay';


Which owners have an "B" or and "F" in their last name and have a "3" in their zip code?


select FIRST_NAME, LAST_NAME, ZIP
from OWNER
where (LAST_NAME like '%b%' or LAST_NAME like '%f%') and ZIP like '%3';


Which owners have an "B" in their last name OR have a "F" in their last name AND have a "3" in their zip code?


select FIRST_NAME, LAST_NAME, ZIP
from OWNER
where LAST_NAME like '%b%' or (LAST_NAME like '%f%' and ZIP like '%3%');









Putting them all together:


Which owners have last names in the lower half (A to M) of the alphabet and are from "Beloit", or are from the upper half (M and above) of the alphabet and are from "Green Bay"? Sort the result table by city in descending order and last name in ascending order.  


select FIRST_NAME, CITY
from OWNER
where (LAST_NAME <'N' and CITY='Beloit') or (LAST_NAME >'M' and CITY='Green Bay')
Order by CITY desc, LAST_NAME;



Management asks for a review to put a report together, "Would like to see which owners are from Green Bay and Beloit or have the letter "B" in their first name."


select FIRST_NAME, CITY
from OWNER
where CITY='Green Bay' or (CITY='Beloit' and FIRST_NAME like '%b%');

No comments:

Post a Comment