70-461

DatabaseHead

Thanks for the heads up Carl. I think I might go for it, seems manageable.

@ Daniel

I work as a master data analyst, so I manage the data that comes into the database and build SQL scripts to review data, look for integrity issues etc. I do build tables, views and large queries. So not really but sort of

daniel108

@DatabaseHead

That's still pretty cool man

scaredoftests

But, oh so worth it! Very close to the test questions.

daniel108

i might get it after I go through these books

scaredoftests

Plus you get flashcards. I know, you are excited now, right?

DatabaseHead

Scared which book are you talking about? Sorry lost it....

daniel108

thats cool, making your own flashcards sucks

DatabaseHead

Are you guys going to talk me into reading my training kit book again?! I literally have pages falling out of the dang thing, in fact it's hard to keep it in order now the binding has given way. Ugh....... For the life of me I can't stomach the XML section. Maybe I'll take some pills or something to help me focus.

daniel108

I think scared is talking about Transcender. The questions are similar to the actual test and it comes with flashcards..

Training kit doesnt seem too bad. Working with XMLs in SQL is definitely a weakness of mine but we use them a lot at my job.

DatabaseHead

269 just for the e training? Anyway to swing a package deal?

scaredoftests

DatabaseHead wrote: »

Scared which book are you talking about? Sorry lost it....

Transcender, when you buy the test, it comes with flashcards

scaredoftests

But the Training Kit doesn't cover XML that much. Joe's Pro's Volume 5 covers that. With a much less complicated view. I love it.

NotHackingYou

I found the Training kit to be pretty good, but the XML section was dry. Practice, practice, practice.

DatabaseHead

I'm actually working on a query right now, I always seems to default to the virtual table aka Derived Table. Love them joins in the from......

Just can't seem to get away from them.

daniel108

Derived tables are pretty cool. It made things so much easier when I figured out how to write subqueries. I used to select a column then copy the results and use them in a where clause. It was a pain having to format each element with single quotes...

Good times...

Then tried figuring out if I could store the select statment as an array.

It blew my mind when I figured out you could select from a select statment.

DatabaseHead

Blow my mind I am not sure I follow. Any white pages or sites in regards to this? I am familiar with Array's and Collections from VBA back in the day when Access was the thing lol. Not sure how you call them in your select. Are you talking about doing a distinct in a sub select and then referencing it in your outer select?

daniel108

DatabaseHead wrote: »

Blow my mind I am not sure I follow. Any white pages or sites in regards to this? I am familiar with Array's and Collections from VBA back in the day when Access was the thing lol. Not sure how you call them in your select. Are you talking about doing a distinct in a sub select and then referencing it in your outer select?

Select <select list>
From (select <select list> from table_name where col = x)

This is a basic select from a select. I thought this was what u meant by derived tables.

My array idea didn't work.

I'll post a link later

DatabaseHead

Yeah that's it for me.

SELECT *
FROM A LEFT OUTER JOIN ( SELECT * FROM B ) AS C ON C.UI = A.UI INNER JOIN
Another table etc. Usually when I have to sum or max/min to a certain level and then join back into the table. Clear as mud?!

daniel108

Lets say there are two tables: Person and Documents.

Lets also say that the personid is an attribute in both tables but all I know is the peoples names.

John
Mark
David

I want all records from documents associated with these three people.

So I need their IDs.

I would write

SELECT personid FROM dbo.person WHERE firstname in ('John','Mark','David')

Results:
personid
1
2
3

Now i have to write another query and use those results in the WHERE clause. I thought that maybe it was possible to store it as an array or something. This is before I knew about JOINS or subqueries. I was thinking that I could store 1, 2, 3 as a variable.

DatabaseHead

SELECT P.*, D.*
FROM PERSON AS P
JOIN DOCUMENTS AS D
ON D.PersonID = P.PersonID AND P.FIRSTNAME IN ('JOHN', 'MARK', 'DAVID')


WITH CTE_TEST
AS
(

SELECT *
FROM PERSON
WHERE FIRSTNAME IN ('JOHN', 'MARK', 'DAVID')

);


SELECT *
FROM CTE_TEST AS T
JOIN DOCUMENTS AS D
ON D.PersonID = T.PersonID

Any of these work for you?

DatabaseHead

Hopefully my bad SQL didn't scare everyone off! Rest assured I am applying for a Senior Account Manager position. Strictly financially though.....

daniel108

haha na, It's a good query. I just didn't know that was possible when I first started learning SQL. CTEs and derived tables make things easier.

I just got to Chapters 6 Querying Full Text and 7 Querying XML data. Ya'll weren't lying, these chapters are rough....I'm skipping them and coming back to them later.

daniel108

How XML heavy is the exam?

scaredoftests

I think there is about 5 or 6 XML questions...

DatabaseHead

With two fails under my belt I agree with SoT. There is about 5 - 6 and both times I took the exam they were the first ones right out the gate. <Shakes head>

scaredoftests

DatabaseHead wrote: »

With two fails under my belt I agree with SoT. There is about 5 - 6 and both times I took the exam they were the first ones right out the gate. <Shakes head>

and talk about deer in the headlights look. LOL. I have failed 2X as well @Databasehead. We should form a club. kidding...

DatabaseHead

I just wanted to get up and leave.

DatabaseHead

I've come to the conclusion that this exam is for developers not analyst like myself. I think I'm going to finally put my hopes of this certs to bed. I will still encourage and add to this thread. I like it....

daniel108

Do you think getting an MCSA would help me get a data analyst job? Trying to get out of help desk work...

EagerDinosaur

I think MCSA (SQL Server) is useful in re-assuring people that you know at least some SQL Server stuff and can be trusted to some extent. Exam 461 gives broad coverage of hands-on SQL query writing. 462 covers hands-on DBA tasks, but also the design of high-availability configurations. 463 covers SSIS and data warehouse design (which I was surprised to find is quite different to the design of OLTP databases).

I've recently found out that one of the (slightly paranoid) customers I work for won't let anyone without MS certification login to their SQL Server boxes. I think certifications can open doors in some circumstances.