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

4 comments:

  1. I will immediately take hold of your rss feed as I can't find your e-mail subscription link or e-newsletter service. Do you've any? Kindly allow me realize in order that I could subscribe. Thanks. gmail sign in

    ReplyDelete
    Replies
    1. Great Article Cloud Computing Projects

      Networking Projects

      Final Year Projects for CSE

      JavaScript Training in Chennai

      JavaScript Training in Chennai

      The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

      Delete
  2. Thank you for sharing your thoughts. I really appreciate your efforts and I am waiting for your next write ups thanks once again. paypal login my account official site

    ReplyDelete
  3. If you never go using a fixed loan, you'll be able to also choose from 5 year adjustable rate mortgages that provide you with a low rate like 2 percent interest for five years before an increased rate is locked in. mortgage calculator All content about the Refresh Financial websites are presented only as in the date published or indicated, and may be superseded by subsequent market events or for other reasons. mortgage calculator

    ReplyDelete