"Real world" problem set 1

   1 - From a user in Germany, in comp.datatabases.theory

     I have the following problem  (see paragraph starting Now I'm 
	looking for...):

     there are three tables ...

     customer (nr int key, ....)
     registration (regcode char, customer_nr int, reg_date date)
     registrationcode (regcode char, artikel char)

     There could be a customer who has a registration and therefore
	 a registrationcode. there could  be also a customer who has 
	no registration and no registrationcode and a registration-code 
	which has no customer asigned.

     Now I´m looking for some way (in SQL) to show customer-data and 
	registration-code - no matter if there is no customer or no 
	registration code for each customer / registrationcode.

     This SQL gives only all customers with and without registration- code  
	(Code from Adabas D):

     select c.nr,rc.recode from customer c,
     registration r, registrationcode rc
     where c.nr = r.customer_nr (+) and
     r.regcode (+) = rc.regcode

	(Note:  I presume (+) indicates outer joins.  --  EVS)

 2 - From a US user writing in comp.databases

     I am hoping someone can help me spot my error with this SELECT  
	statement

     Tables:
     SAO
     ------
     SAOID
     Other info

     SAOPatent (two foreign keys)
     ------------------
     SAOID
     PatentID

     Patents
     -----------
     PatentID
     PatentTitle
     PatentHolderID

     PatentHolders
     ----------------------
     PatentHolderID
     PatentHolderName

     So, this is a four-table join and given that I want to see the  
	PatentTitle, SAOID and PatentHolderName for a given PAtentHolderID 
	I entered:

     SELECT PatentTitle, SAOID, PatentHolderName
     FROM SAO, SAOPatent, Patents, PatentHolders
     WHERE 0=0
     AND Patents.PatentHolderID = PatentHolders.PatentHolderID
     AND SAO.SAOID = SAOPatent.SAOID
     AND Patents.PatentID = SAOPatent.PatentID
     AND PatentHolderID = 1

     I figured I'd get a list of all the PatentTitles where the  
	PatentHOlder was PatentHolderID=1. Instead I am getting a list 
	of about 28 records, all the same.   One thing I don't understand 
	is that I should be getting a list of about 7 different patents 
	associated with PatentHOlderID=1. Instead I am getting 28.  I also 
	find it curious that I have exactly 4 records in the PatentHolders 
	database the co-incidence of 7*4=28 is too close for comfort.

3 - From a user in Germany writing in comp.databases

     I have 2 tables:

     Article
     -------
     ArtNr      Bez
     -------------------
     111111     Artikel1
     222222     Artikel2
     333333     Artikel3

     Basket
     ------
     BNr        Article
     ------------------
     1          1111111
     2          2222222

     I want one select-statement that gets all artcle and if the basket 
	for  a special person has this article or not.  
	The result should look like that

     ArtNr   Bez        In_Basket_for_Person_1
     -----------------------------------------
     111111  Article1   True or something like that
     222222  Article2   False or something like that
     333333  Article3   False or something like that

4 - From a US user writing in comp.databases.ms-access

     I have two tables and I want to retrieve just the rows from the first
	table that don't match the second table.  How can I do that? I 
	tried using this query but it returns a cartesian product.

     SELECT Table1.AccountDate
     FROM Table1 inner join table2 on table1.accountdate <>
     table2.accountdate

  5 - From a user in Singapore (?), in comp.databases.oracle.misc

     I hope someone can help give me an SQL statement to get the following:-

     I have 2 tables, Emp and Dept.

     Comments (3 fields)
     ---
     Staff_id      Dept_no         Staff_Comments
     12345A                S45             some comments
     12345A                S45             some other comments
     99999Q                G33             also comments

     Dept (2 fields)
     ----
     Dept_no               Dept_name
     S45           Architecture
     S46           Science
     G33           Economics

     I would like to know the statement where I can find the number of 
	staff in department 'G33' who has only made ONE comment (meaning 
	only one entry in the COMMENT TABLE)

  6 - From two students from Belgium, in comp.databases.oracle.server

     how can i select the 20 biggest files from my table????????

     table files
     -------------
     naam     varchar2(50)
     size    integer