I think the DDS is the way I'd do it but, if you want a straight 5V logic solution you can use a 4060 logic chip. Here's one - the device uses a reference frequency from a crystal (or RC network) and offers a frequency divided down version - it's a 14-stage binary ripple counter and if you use a 1MHz clock you can easily derive 976.56Hz.
Next, if you are not happy with a square wave you can apply low-pass filtering to extract the fundamental frequency. Depending on how much low-pass filtering you do determines how pure the sinewave is. Here is a good application note about turning squares into sines. None of it is rocket science hence I'm letting the links speak for themselves.
The circuit is more complex and more fiddly than is apparent.
There are a number of factors that make this so but:
C1 polarity reverses and
C1-Q1_base junction is below ground for a significant part of the cycle.
This is an intended part of the design, but not one that most people are aware of or that is overly intuitive without some thought as to how the circuit works.
Timing occurs on capacitor charge and discharge but mostly on the latter as the on pulse is usually a low percentage of the duty cycle. When Q2 turns off the right hand end drops from near V+ to near V- and its left hand end falls to below ground. The capacitor is arguably shown with the wrong polarity for normal use, but in this case the design limits the +degree of reverse polarisation of the capacitor.
The product of the betas (current gains of the two transistors has a critical maximum value. Too HIGH betas kills operation
The current in R2 when Q1 Q2 are on multiplied by B1 and B2 sets the maximum stable on state current in L1. If the supportable current is > actual trhen the circuit is liable to be stable in the on state. It's not exactly at the limit exactly when
Ir1 x B1 x B2 < Il
as there are other scaling factors but it tends to be proportional to this expression. That means that eg if a circuit works with lowish beta transistors it may stop working if higher beta transistors are substituted.
Note that it can be oscillating at high frequency so the LED appears always on but isn't.
The main timing occurs by having Q1 base driven NEGATIVE wrt ground and charging up via the R1/R2 divider. He has used a low value cap and relies on R1 being adjustable to the crucial point that allows a long charge time due to "just enough drive".
Better is a larger cap (100 uF good, more better probably, a larger value of R2 and maybe 1 1M pot.
An LED can be used - an eg 1k or even 100R across the LED to start may get you going.
One cell LED driver:
Here is a variation that I devised 15++ years ago.
It can hardly have been original even then, but I've never seen an older version. (Somebody will have).
0.7V < Vin 1.5 V probably
Can be used on higher voltages with extra components.
L1 = something lying around - probably 100's of uH to some mH OK.
R2 and C1 set flash rate and can be adjusted for a slow flash or apparently a steady output due to high frequency. The LED can be in either position shown but not both at once.
The LED2 position is probably the best as the LED gets Vin_batt + the Inductor ringing voltage.
LED1 gets inductor ring voltage only.
Best Answer
A transistor is saturated enough by this voltage to pull the cap to ground and discharge it
The problem with that is that an NPN transistor does not switch on/off abruptly. If it did your solution would work. There are Unijunction transistors which do just this.
But with an NPN it will discharge the capacitor to such a voltage where there's a balance, it will discharge it slowly such that the transistor's input voltage is such that it will discharge at the rate (slowly) I just mentioned. So: no oscillation! It will just sit there, consume current and nothing else.
It is possible to make a one transistor NPN oscillator if you add a transformer, the well known "Joule thief" circuit (for making an LED light up on a single 1.5 V battery) is an example of this:
Note how there is a (small) transformer there. It takes care of the phase inversion needed to make this oscillate. This circuit could be adapted to drive a piezo element instead of an LED.
Design challenges will be:
making this oscillate at an audible frequency (as is it works at around 100 kHz)
The LED limits the Vce of the NPN, a piezo element will not limit the voltage so maybe a zener diode is needed there.
getting the transformer right will require some trial and error.