UH2@PSUVM.BITNET (Lee Sailer) (02/23/88)
I've only read about SQL, and have never had to write a query in it. This morning I am wondering, how do you do a simple relational UNION in SQL? That is, given two relations, R1 and R2, how do you create R3 that contains those tuples that are in either R1, R2, or both, assuming that R1 and R2 have the same attributes?
rfm%urth@Sun.COM (Richard McAllister) (02/25/88)
In article <34358UH2@PSUVM> UH2@PSUVM.BITNET (Lee Sailer) writes: >This morning I am wondering, how do you do a simple relational UNION in SQL? >That is, given two relations, R1 and R2, how do you create R3 that contains >those tuples that are in either R1, R2, or both, assuming that R1 and R2 >have the same attributes? You write SELECT * FROM R1 UNION SELECT * FROM R2 to get the rows of R3. If you want to create a new "virtual" relation R3 (SQL calls this a VIEW) you're pretty much out of luck as few implementations of SQL allow basing a VIEW on a UNION. Rich
UH2@PSUVM.BITNET (Lee Sailer) (02/25/88)
In article <43005@sun.uucp>, rfm%urth@Sun.COM (Richard McAllister) says: > >In article <34358UH2@PSUVM> UH2@PSUVM.BITNET (Lee Sailer) writes: > >>This morning I am wondering, how do you do a simple relational UNION in SQL? >>That is, given two relations, R1 and R2, how do you create R3 that contains >>those tuples that are in either R1, R2, or both, assuming that R1 and R2 >>have the same attributes? > >You write > > SELECT * FROM R1 > UNION > SELECT * FROM R2 That is certainly straightforward enough, but still I was surprised. One of the characterisitcs of SQL, I thought, was that there are ONLY 4 operators, SELECT, INSERT, DELETE, and UPDATE. I figured that there would be some tricky way to use a SELECT to do a Union, Intersect, Difference, etc. So, how come it is said that SQL has only 4 operators, when obviously there are more? lee
sysop@stech.UUCP (Jan Harrington) (02/27/88)
in article <34358UH2@PSUVM>, UH2@PSUVM.BITNET (Lee Sailer) says: > > I've only read about SQL, and have never had to write a query in it. > This morning I am wondering, how do you do a simple relational > UNION in SQL? That is, given two relations, R1 and R2, how do > you create R3 that contains those tuples that are in either R1, R2, or > both, assuming that R1 and R2 have the same attributes? Assuming that your SQL implementation supports UNION (not all do), you could try something like: SELECT * FROM R1 UNION SELECT * FROM R2; UNION will link two independent SELECTs (both SELECTS must include the same column names, in the same order) or the results of two subqueries. Jan Harrington, sysop Scholastech Telecommunications UUCP: ihnp4!husc6!amcad!stech!sysop or allegra!stech!sysop BITNET: JHARRY@BENTLEY ******************************************************************************** Miscellaneous profundity: "No matter where you go, there you are." Buckaroo Banzai ********************************************************************************
john@riddle.UUCP (Jonathan Leffler) (03/02/88)
In article <34586UH2@PSUVM> UH2@PSUVM.BITNET (Lee Sailer) writes: >So, how come it is said that SQL has only 4 operators, when obviously >there are more? There aren't more operators: UNION is part of the SELECT statement. The SELECT statement in SQL is capable of doing most of the Relational Calculus operations, including the Relational Calculus operation called SELECT -- which is very feeble compared with an SQL select, but absolutely crucial. #include <disclaimer.h> Jonathan Leffler (john@sphinx.co.uk)
ard@pdn.UUCP (Akash Deshpande) (03/02/88)
In article <34586UH2@PSUVM>, UH2@PSUVM.BITNET (Lee Sailer) writes: > One of the characterisitcs of SQL, I thought, was that there are ONLY > 4 operators, SELECT, INSERT, DELETE, and UPDATE. I figured that > there would be some tricky way to use a SELECT to do a Union, Intersect, > Difference, etc. > > So, how come it is said that SQL has only 4 operators, when obviously > there are more? > > lee There are two separate issues here - completeness and expressiveness. Relational algebra, relational calculus and set calculus are all equivalently complete. Operations in one can be expressed in terms of operations in another. However, for more expressiveness, one may want to have operations from more than one groups in a language. Details can be found in 'An Introduction to Database Systems' Date. -- Akash Deshpande Paradyne Corporation {gatech,rutgers,attmail}!codas!pdn!ard Mail stop LF-207 (813) 530-8307 o P.O. Box 2826 (813) 535-3987 h Largo, Florida 34649-2826
allbery@ncoast.UUCP (Brandon Allbery) (03/06/88)
As quoted from <34586UH2@PSUVM> by UH2@PSUVM.BITNET (Lee Sailer): +--------------- | One of the characterisitcs of SQL, I thought, was that there are ONLY | 4 operators, SELECT, INSERT, DELETE, and UPDATE. | So, how come it is said that SQL has only 4 operators, when obviously | there are more? +--------------- Because, strictly speaking, UNION is a sub-operator of SELECT; you can't UNION two INSERT statements or etc. (You can, however, say INSERT ... SELECT ... UNION SELECT ..., at least in theory. Implementation depends on how completely the DBMS implements SQL.) -- Brandon S. Allbery, moderator of comp.sources.misc {well!hoptoad,uunet!hnsurg3,cbosgd,sun!mandrill}!ncoast!allbery
doug@bear.cis.ohio-state.edu (Doug Kerr) (01/12/90)
I'm trying to write a single SELECT statement that involves a complex
sort order and have had no success. The problem is given a relation
R(A,B,C,D)
sort as follows:
all tuples with A > A1, sorted by A
all tuples with A <= A1 and B > B1, sorted by B
all other tuples (A <= A1 & B <= B1), sorted by C
I can write seperate SELECTS for each subset, but is there a way to
combine the three sets into one? I need this ordering because I need
to make assignments in an embeded SQL program corresponding to the D
values based on the ranking given by this sort order, i.e. I may
assign one value to the first 10 D's, another value to the next 20
values, etc. There is no relationship between 10, 20, etc. and the
breaks in the three groups. The program will be much easier to write
if I need one cursor rather than 3.
Thanks for any help you can provide.
-=-
Douglas S. Kerr, Department of Computer and Information Science
The Ohio State University; 2036 Neil Ave. Columbus OH USA 43210-1277
doug@cis.ohio-state.edu 614/292-1519
...!{pyramid,killer}!osu-cis!cis.ohio-state.edu!dougmikei@ulticorp.UUCP (mikei) (10/05/90)
Greetings,
Perhaps there is an SQL guru out there who can be of some assistance.
Note I am using GUPTA's SQLBase.
Given a table of patients PID int WEIGHT int
where one PID may have numerous weight readings.
How would I select the PID for only those patients whose
weight was ALWAYS <= 100.
ANy ideas would be appreciated.
uunet!ulticorp!mikei
.bengsig@oracle.nl (Bjorn Engsig) (10/05/90)
Article <391@ulticorp.UUCP> by mikei@ulticorp.UUCP (mikei) says: |Greetings, | |Given a table of patients PID int WEIGHT int |where one PID may have numerous weight readings. | |How would I select the PID for only those patients whose |weight was ALWAYS <= 100. select pid from patients group by pid having max(weight)<=100; or, if your database engine does not have having, first create a view giving the max weights of the patients, and then select the one <100 from it: create view patmax (pid,weight) as select pid,max(weight) from patients group by pid; select pid from patmax where weight<=100; -- Bjorn Engsig, Domain: bengsig@oracle.nl, bengsig@oracle.com Path: uunet!mcsun!orcenl!bengsig From IBM: auschs!ibmaus!cs.utexas.edu!uunet!oracle!bengsig
radstaat@idca.tds.PHILIPS.nl (Christina Radstaat) (10/05/90)
In article <391@ulticorp.UUCP> mikei@ulticorp.UUCP (mikei) writes: >Greetings, > >Perhaps there is an SQL guru out there who can be of some assistance. >Note I am using GUPTA's SQLBase. > >Given a table of patients PID int WEIGHT int >where one PID may have numerous weight readings. > >How would I select the PID for only those patients whose >weight was ALWAYS <= 100. > If you can use subqueries in a select, the following would give you the right answer: select unique PID from patients where WEIGHT <= 100 and PID not in ( select PID from patients where WEIGHT > 100 ) -- ------------------------------------------------------------------------------- # Christina Radstaat PAV/V2-A12/13 Philips IS Apeldoorn (NL) +31 55 432824 # # domain: radstaat@idca.tds.philips.nl uucp: ...!mcvax!philapd!radstaat # -------------------------------------------------------------------------------
root@lsicom2.UU.NET (Admin) (10/05/90)
mikei@ulticorp.UUCP (mikei) writes: >Greetings, >Perhaps there is an SQL guru out there who can be of some assistance. >Note I am using GUPTA's SQLBase. >Given a table of patients PID int WEIGHT int >where one PID may have numerous weight readings. >How would I select the PID for only those patients whose >weight was ALWAYS <= 100. >ANy ideas would be appreciated. > uunet!ulticorp!mikei >. This isn't specifically Gupta........ select PID from table where not exists (select PID from table where WEIGHT > 100)
jpk@ingres.com (Jon Krueger) (10/10/90)
From article <391@ulticorp.UUCP>, by mikei@ulticorp.UUCP (mikei): > Given a table of patients PID int WEIGHT int > where one PID may have numerous weight readings. > How would I select the PID for only those patients whose > weight was ALWAYS <= 100. Or "find patients whose weight has never exceeded 100 pounds", or "find patients whose weight is never known to have exceeded 100 pounds": select pid from patients where pid not in (select pid from patients where weight > 100) Another entertaining query is "find patients who have experienced significant weight changes". We might deem significant any change of 10 pounds or more between two successive weighings: /* assumes not more than one weight entered per patient per day */ select p.pid, p.date, p.weight, p2.date, p2.weight from patients p, patients p2 where abs(p2.weight - p.weight) >= 10 and p.pid = p2.pid and p.date < p2.date and not exists (select p3.pid from patients p3 where p3.date > p.date and p3.date < p2.date) Of course, it might have been years between weighings, a factor which this query does not take into account. On the other hand, a simple graph of weight as a function of date might prove more useful to the clinician than all the crafting we might put into refining this query. The database might more productively be used to validate data entry based on recent history: exec sql select weight into :last_wt, name into :nm, date into :dt from patients where pid = :this_pid and date = (select max(date) from patients where pid = :this_pid); if (abs(last_wt - this_wt) > 10) { printf("%s weighed %d pounds on %s. ", nm, last_wt, dt); printf("Are you *sure* %s now weighs %d pounds?", nm, this_wt); } -- Jon Does not necessarily reflect the views of Ingres Corp. -- Jon Krueger, jpk@ingres.com
dkennedy@oracle.uucp (DAN KENNEDY) (10/10/90)
In article <1090@lsicom2.UU.NET> root@lsicom2.UU.NET (Admin) writes: > >>Given a table of patients PID int WEIGHT int >>where one PID may have numerous weight readings. > >>How would I select the PID for only those patients whose >>weight was ALWAYS <= 100. > > >This isn't specifically Gupta........ > > >select PID from table >where not exists > (select PID from table > where WEIGHT > 100) ... and it won't work The query above will return all the rows from "table" if anyone weighs > 100. Try... select distinct a.pid from table a where not exists (select 'foo' from table b where a.pid = b.pid and b.weight > 100) The "a" and "b" are table aliases used to avoid confusion in the subquery. The distinct simply assures that your output doesn't list the same PID more than once. -- Dan
wsinpdb@svin02.info.win.tue.nl (Paul de Bra) (10/11/90)
In article <1990Oct10.121246.8576@oracle.com> dkennedy@oracle.UUCP (DAN KENNEDY) writes: >In article <1090@lsicom2.UU.NET> root@lsicom2.UU.NET (Admin) writes: >> >>>Given a table of patients PID int WEIGHT int >>>where one PID may have numerous weight readings. >... [ answers with subqueries deleted ] how bout select pid from table group by pid having min(weight) < 100 Paul. (debra@research.att.com)
jgoodwin@ncratl.Atlanta.NCR.COM (John Goodwin) (10/12/90)
Why not try the following:
select unique PID
from patients a
where 100 >= (select max(b.WEIGHT)
from patients b
where b.pid = a.pid)
This is one quick and dirty solution. Good Luck.