"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