Sample questions

The following questions were taken from various database-related newsgroups on UNIX. Responses are shown as links to each question.

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

  I have the following problem :

  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 therefor 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.

  I would be very glad, if somebody could help me.

  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
  

Posted solution, example 1

---

• Example 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.

Posted solution, example 2

---

• Example 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
  

Posted solution, example 3

---

• Example 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

Posted solution, example 4

---

• Example 5 - From an academic user in Maine, in comp.databases.ms-access

  (Could this be someone's homework?)

  How do I generate a mailing list from one table on which I want to exclude matching fields from four other tables?

Posted solution, example 5

---

• Example 6 - From a user in France, in comp.databases.sybase

  I'm struck because of a silly problem that I can't solve because of my bad knowledge of SQL .

  In Sybase, I want to retrieve the row count of each returned row from a select statement.

  Example:

  I have a table FOO with column BAR. I want to get all rows of table FOO ordered by BAR, and at the same time get the row count in the result.

  Let's say the TABLE FOO contains 3 rows

  BAR= 123
  BAR= 32
  BAR= 456
  

  I'd like my SQL statement returns

  1,32
  2,123
  3,456
  

  What shoud I add to the statetement

  select ???????,FOO from BAR ordered by FOO
  

Posted solution, example 6

---

• Example 7 - 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)

Posted solution, example 7

---

• Example 8 - 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
  

Posted solution, example 8

---

• Example 9 - From a US user writing in comp.databases.oracle.server

  I'm a newbie when it comes to databases and SQL. I'm now working with Oracle 7 and am messing around with tables to model a video shop.

  I've the following tables:

  FILM(film_no, film_name, filmmaker_id)
  FILM_MAKER(filmmaker_id, maker_name, maker_address)
  

  The filmmaker_id in FILM is a foreign key.

  The problem is, how do I find out the film maker that has the most films with the video shop?

Posted Solutions

It may be impossible if you want to get the result set from a single SQL statement. But by using such set operators as UNION you can meet the target. The statement has been listed below and it has been written in Sybase format.

    select c.nr, r.regcode
    from customer c, registration r
    where c.nr *= r.customer_nr
    UNION
    select r.customer_nr, rc.regcode
    from registrationcode rc, registration r
    where rc.regcode *= r.regcode

You're right. The number of returned records is not a coincidence. You are getting a Cartesian join (every record in one table joined with every record in another table). You are using the wrong syntax in your query. The way to specify that you are joining tables based on equality in certain fields is by using a JOIN statement. The syntax is

SELECT field1, field2 
FROM table1 INNER JOIN table2 ON
table1.field3 = table2.field3;

FROM (t1 LEFT JOIN t2 ON t1.f1 = t2.f1) 
LEFT JOIN t3 ON t2.f2 = t3.f2;

BTW, it would have been really fun with 5000 and 10000 records instead of 4 and 7! ;-)

Return to example 2

• Posted by a US user:

  SELECT ArtNr, Bex, "True" 
  FROM Article
  WHERE ArtNr IN (SELECT Article FROM Basket)
   
  UNION
   
  SELECT ArtNr, Bex, "False"
  FROM Article
  WHERE ArtNr NOT IN (SELECT Article FROM Basket);
   
  OR:
   
  SELECT Article.*, Basket.Article 
  FROM Article, OUTER Basket
  WHERE ArtNr = Basket.Article
  INTO TEMP fred;
   
  UPDATE fred SET Article = "TRUE" WHERE Article IS NOT NULL;
  UPDATE fred SET Article = "FALSE" WHERE Article IS NULL;
   
  SELECT * 
  FROM fred;
   
  DROP TABLE fred;

• Posted by a user from the Netherlands:

  SELECT Article.*, ArtNr In 
  (SELECT Article FROM Basket WHERE BNr =1) AS
  In_Basket_For_Person_1
  FROM Article;
  

Return to example 3

SELECT Table1.AccountDate, Table2.AccountDate 
FROM Table1 LEFT JOIN Table2 ON 
Table1.AccountDate = Table2.AccountDate WHERE 
(((Table2.AccountDate) Is Null));

Return to example 4

select distinctrow t1.*
from 
(((t1 left join t2 on t1.id = t2.id) 
  left join t3 on t1.id = t3.id)
  left join t4 on t1.id = t4.id) 
  left join t5 on t1.id = t5.id
where t2.id is null and t3.id is null and t4.id is null 
and t5.id is null

select count(*) SEQ, a.BAR
from FOO a, FOO b
where a.BAR >= b.BAR
group by a.BAR

as i read this, you want one numeric result that is the total number staff members who have one and only one comment. if so:

select count(*)
from (select staff_id
        from comments
        where dept_no = 'G33'
        group by staff_id
        having count(*) = 1)

select count(count(*))
from comments
where dept_no = 'G33'
group by staff_id
having count(*) = 1

select count(distinct staff_id)
from comments
where dept_no = 'G33'
  and staff_id not in (select staff_id
                        from comments
                        where dept_no = 'G33'
                        group by staff_id 
                        having count(*) > 1)

Return to example 7

• Posted by a user from Germany

  select * from files a
  where 20 < (select count(*) from files b
            where a.size < b.size)
  order by size desc;
  

  Perhaps you will have to replace '<' by '<=' or '>' by '>='. This is a quite straight forward approach but it should work.
• Posted by a user from Slovenia

  Here are some of the possible SQL sollutions:

  Sollution 1. (*very slow* on large tables):
  ------------
   
  SELECT naam, size FROM files a
  WHERE 20 >= (SELECT COUNT(size) FROM files b
               WHERE b.size >= a.size)
  ORDER BY a.size DESC;
   
  Sollution 2. (much faster than previous one)
  ------------
   
  SELECT a.naam, a.size FROM files a,
    (SELECT rowid x, -1*size y 
     FROM files 
     GROUP BY -1*size, rowid) b
  WHERE a.rowid = b.x
  AND rownum <= 10;
   
  Sollution 3. (the fastest one)
  ------------
   
  SELECT a.naam, a.size FROM files a , dual
  WHERE -1*a.size = DECODE(dual.dummy(+),'X',NULL,NULL)
  AND rownum <= 10;
  

the following should do the trick. In fact, I tested it and it works. The only problem is that there may be more film makers with the maximum number of films (hence the "in" operator). If you want just one of them, add rownum=1 to the outer where condition.

select maker_name
from film_maker
where filmmaker_id in (
   select filmmaker_id from film
   group by filmmaker_id
   having count(*)= (
           select max(count(*))
           from film
           group by filmmaker_id));