DBA in Space

[Scruit]

You missing something.....

CREATE TABLE spaceship

(name char(50),

launch_date date)

Tsk tsk tsk young grasshoppa.

SQLServer uses "into" to specify that you want to create a new table from the result set. if you create the table first then use the into clause then you'll get an error.

And you wouldn't want launch date onteh spaceship unless it was only going up once. Instead you'd have a launch entity.

Then we can see who's going up when...

select * from spaceship where name = '<astronauts_name>' order by launch_date

- Don't use select * unless you know what you're doing, or if you are just browsing the database for giggles. If the table structure of the source table changes then your code fails until you change it again unless your destination can handle the new structure. ETL 101. Examples of "know what you're doing" woudl be when I wrote a report where the user could specify what fields they wanted to see on the website - the select query to display on the website did a select * on the dynamically created temporary table (and there were other selects for things like field list so the web page could build the table correctly, display headers etc.)

Also your query would show you all the columns from spaceship for the named astronaut only. More of a "when is Bob going up", not "who's going up when".

DBAs rock. We SO should get more tail then we do. I can't figure out why we don't.

Edited October 26, 2011 by Scruit

[Casper]

DBAs rock. We SO should get more tail then we do. I can't figure out why we don't.

Amen.

[RVTPilot]

DBAs rock. We SO should get more tail then we do. I can't figure out why we don't.

Amen.

'Cuz yer nerds and live in your parents' basement until you are 30.

Like they say...want some trim, be an admin! (If they = admins.)

[Casper]

'Cuz yer nerds and live in your parents' basement until you are 30.

Like they say...want some trim, be an admin! (If they = admins.)

I am 30. That's the only thing you got right.

[redbarron77]

DBAs rock. We SO should get more tail then we do. I can't figure out why we don't.

What do you mean we paleface?

[Scruit]

What do you mean we paleface?

Piss off, Tonto.

[r1crusher]

Tsk tsk tsk young grasshoppa.

SQLServer uses "into" to specify that you want to create a new table from the result set. if you create the table first then use the into clause then you'll get an error.

And you wouldn't want launch date onteh spaceship unless it was only going up once. Instead you'd have a launch entity.

- Don't use select * unless you know what you're doing, or if you are just browsing the database for giggles. If the table structure of the source table changes then your code fails until you change it again unless your destination can handle the new structure. ETL 101. Examples of "know what you're doing" woudl be when I wrote a report where the user could specify what fields they wanted to see on the website - the select query to display on the website did a select * on the dynamically created temporary table (and there were other selects for things like field list so the web page could build the table correctly, display headers etc.)

Also your query would show you all the columns from spaceship for the named astronaut only. More of a "when is Bob going up", not "who's going up when".

DBAs rock. We SO should get more tail then we do. I can't figure out why we don't.

Oh boy, where to start.

I understand how the "into" works, but if you notice I wanted to be more specific about that which is being stored and placed into the spaceship table. Thusly, I didn't run the exact same statement you did which queried and then created the table. I merely created the table separately, and then queried it. My failure was not declaring that there was data already within the table (populated by some other means) before I queried it.

With the stuff I deal with select * from a table is okay but you are quite correct that in certain circumstances doing such could be bad.

And yes, the query would return a list of launch dates of a given astronaut. If you want who's going up when....

select * from spaceship where launch_date = <some date> order by name = '<astronauts_name>' and launch_date

I <3 DB nerdery.

Does this thread fall under Likwids post of stupid shit found on the net?

[Casper]

Oh boy, where to start.

I understand how the "into" works, but if you notice I wanted to be more specific about that which is being stored and placed into the spaceship table. Thusly, I didn't run the exact same statement you did which queried and then created the table. I merely created the table separately, and then queried it. My failure was not declaring that there was data already within the table (populated by some other means) before I queried it.

With the stuff I deal with select * from a table is okay but you are quite correct that in certain circumstances doing such could be bad.

And yes, the query would return a list of launch dates of a given astronaut. If you want who's going up when....

select * from spaceship where launch_date = <some date> order by name = '<astronauts_name>' and launch_date

I <3 DB nerdery.

Does this thread fall under Likwids post of stupid shit found on the net?

Yes.

[Scruit]

select * from spaceship where launch_date = <some date> order by name = '<astronauts_name>' and launch_date

order by name = ?? order by x *and* y ?? Don't think that syntax actually works.

select l.date, a.name

from launch l join crewlist c on l.launch_id=c.launch_id

join astronaut a on a.astronaut_id=c.astronaut_id

order by l.date, a.name

Launch and Astronaut is a many-to-many relationship (an astronaut can fly multiple missions) so I created crewlist as an intersection entity.

My milkshake brings all the girls to the yard. And when I say girls I mean records.

[r1crusher]

order by name = ?? order by x *and* y ?? Don't think that syntax actually works.

select l.date, a.name

from launch l join crewlist c on l.launch_id=c.launch_id

join astronaut a on a.astronaut_id=c.astronaut_id

order by l.date, a.name

Launch and Astronaut is a many-to-many relationship (an astronaut can fly multiple missions) so I created crewlist as an intersection entity.

My milkshake brings all the girls to the yard. And when I say girls I mean records.

Shit, yes that was a syntax error on my part. Didn't need 'name =' after the order by just the column names. I would have caught it if I were actually running it.

I hate joins.