April 12, 2010 at 2:11 pm
I also have to wonder, why is this "Masters" exam using SQL 2000 as the material for examination? If we provided answers relevant to SQL 2005, they won't work on SQL 2000 and then this person would quite possibly fail the exam.
Or am I mistaken somewhere?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 12, 2010 at 2:33 pm
CirquedeSQLeil (4/12/2010)
I also have to wonder, why is this "Masters" exam using SQL 2000 as the material for examination? If we provided answers relevant to SQL 2005, they won't work on SQL 2000 and then this person would quite possibly fail the exam.Or am I mistaken somewhere?
You may just be mistaken in thinking the info should be up to date. I just took a class that was supposed to also suffice as prep for an A+ certification, (not why I took it) which totally ignored Vista and Win 7, assuming XP was the latest and greatest operating system for Windows, and turned out to be for A+ 2006, which is retired in the US now.
When I asked the prof why we were using such obviously outdated info, his response was to refer it to the dean of the CIS dept.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
April 12, 2010 at 2:33 pm
heh, can't help but chuckle as I reinforce the paranoia surrounding credentials. 😀
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
April 12, 2010 at 2:39 pm
jcrawf02 (4/12/2010)
CirquedeSQLeil (4/12/2010)
I also have to wonder, why is this "Masters" exam using SQL 2000 as the material for examination? If we provided answers relevant to SQL 2005, they won't work on SQL 2000 and then this person would quite possibly fail the exam.Or am I mistaken somewhere?
You may just be mistaken in thinking the info should be up to date. I just took a class that was supposed to also suffice as prep for an A+ certification, (not why I took it) which totally ignored Vista and Win 7, assuming XP was the latest and greatest operating system for Windows, and turned out to be for A+ 2006, which is retired in the US now.
When I asked the prof why we were using such obviously outdated info, his response was to refer it to the dean of the CIS dept.
I don't expect bleeding edge up to date, but at least teach current stuff. At a bare minimum this should be centered around SQL 2005 (IMO). I would rather see it centered around SQL 2008, but know that there is a lag in producing texts for academia concerning technology. Ten year old technology is far too old for my tastes in an academic setting.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 12, 2010 at 2:43 pm
I don't expect bleeding edge up to date, but at least teach current stuff. At a bare minimum this should be centered around SQL 2005 (IMO). I would rather see it centered around SQL 2008, but know that there is a lag in producing texts for academia concerning technology. Ten year old technology is far too old for my tastes in an academic setting.
Unless the point of the class is an 'intro to databases' type thing rather than to teach you a specific RDBMS. Bet even then, I would think they would use something like MySQL or something without a "pretty" GUI that can do all the work for you.
The Redneck DBA
April 12, 2010 at 2:44 pm
Jason Shadonix (4/12/2010)
I don't expect bleeding edge up to date, but at least teach current stuff. At a bare minimum this should be centered around SQL 2005 (IMO). I would rather see it centered around SQL 2008, but know that there is a lag in producing texts for academia concerning technology. Ten year old technology is far too old for my tastes in an academic setting.
Unless the point of the class is an 'intro to databases' type thing rather than to teach you a specific RDBMS. Bet even then, I would think they would use something like MySQL or something without a "pretty" GUI that can do all the work for you.
...they taught that one in Access... 😉
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
April 12, 2010 at 2:49 pm
jcrawf02 (4/12/2010)
Jason Shadonix (4/12/2010)
I don't expect bleeding edge up to date, but at least teach current stuff. At a bare minimum this should be centered around SQL 2005 (IMO). I would rather see it centered around SQL 2008, but know that there is a lag in producing texts for academia concerning technology. Ten year old technology is far too old for my tastes in an academic setting.
Unless the point of the class is an 'intro to databases' type thing rather than to teach you a specific RDBMS. Bet even then, I would think they would use something like MySQL or something without a "pretty" GUI that can do all the work for you.
...they taught that one in Access... 😉
I took one years ago as part of my undergrad degree (when I thought I was going to be a Programmer), and we didn't even use a computer until a project at the end. We had to hand-draw database diagrams, and use that relational algebra notation all over the place.
I've come close to jumping into a Master's degree a time or two, but could never bring myself to spend the money. Mostly because I've never found a program nearby that specializes in database concepts very much.
The Redneck DBA
April 12, 2010 at 3:53 pm
I have considered the masters degree a few times as well. Personally, I don't see the benefit as it relates to cost. I think I would rather do the MCM than to get a Masters degree. Same cost, and same return (IMO).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 12, 2010 at 4:05 pm
CirquedeSQLeil (4/12/2010)
I have considered the masters degree a few times as well. Personally, I don't see the benefit as it relates to cost. I think I would rather do the MCM than to get a Masters degree. Same cost, and same return (IMO).
This is the same rationale that pushed me into a non-technical masters. I can handle the tech side pretty well, so giving myself tools on the "other side of the fence" seemed to add more value than any other degree. Makes it a whole lot easier to "talk business" with the client mgrs that way.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 21, 2010 at 4:35 pm
HEHE, I knew I would find this on the internet, I didn't expect someone to post the entire test though lol. Anyway, the truth is this is a test being given out by an employer. Unfortunately the post is geographical inconvenient for me so I won’t be going for it. However the test is good practice for another interview I have coming up so I would appreciate any help regarding triggers.
This was one of the questions posted in the original post...
create table TBL_EMP_AUDIT
(auditid int primary key identity(1,1),
notes varchar(200),
delta money)
Write a trigger which, when the salary of any employee(s) is updated, inserts a single record into this audit table with the total amount of the change across all employees.
e.g. if five employees were given £1000 pay cuts in a single update statement, the audit table should contain one row with values delta = £-5000, notes = ‘Salaries updated’
My assumption is that the trigger needs to be able to have a view of the table before and after the update query, thus allowing a simple subtraction, how is this achievable? I also assume that the AFTER statement will allow me to gather the second part of the information I require but how do I get the first part\BEFORE? Is my approach wrong?
Any assistance in this matter would be appreciated. Most of the code on the net relates to sending a mail from a trigger which doesn’t help much.
Kind Regards,
Steven
June 22, 2010 at 4:53 am
Hi all,
I come up with the below which does the job, however I was just wondering if there is a better\alternate way to approach this issue. Your thoughts are much appriciated.
ALTER TRIGGER EMP_Table_Trigger
on dbo.TBL_EMP
INSTEAD OF UPDATE
AS
BEGIN
DECLARE @originalValue money
SET @originalValue = (SELECT SUM(salary) FROM dbo.TBL_EMP WHERE empid in (SELECT empid FROM inserted))
DECLARE @UpdatedValue money
SET @UpdatedValue = (SELECT SUM(salary) FROM inserted)
DECLARE @FinalValue money
SET @FinalValue = (@UpdatedValue - @originalValue)
INSERT INTO ReportServer.dbo.TBL_EMP_AUDIT
VALUES ('Salaries updated', @FinalValue)
UPDATE dbo.TBL_EMP
SET salary = (SELECT salary FROM inserted WHERE TBL_EMP.empid = inserted.empid)
END
June 23, 2010 at 5:58 pm
steven.k.berry (6/22/2010)
Hi all,I come up with the below which does the job, however I was just wondering if there is a better\alternate way to approach this issue. Your thoughts are much appriciated.
ALTER TRIGGER EMP_Table_Trigger
on dbo.TBL_EMP
INSTEAD OF UPDATE
AS
BEGIN
DECLARE @originalValue money
SET @originalValue = (SELECT SUM(salary) FROM dbo.TBL_EMP WHERE empid in (SELECT empid FROM inserted))
DECLARE @UpdatedValue money
SET @UpdatedValue = (SELECT SUM(salary) FROM inserted)
DECLARE @FinalValue money
SET @FinalValue = (@UpdatedValue - @originalValue)
INSERT INTO ReportServer.dbo.TBL_EMP_AUDIT
VALUES ('Salaries updated', @FinalValue)
UPDATE dbo.TBL_EMP
SET salary = (SELECT salary FROM inserted WHERE TBL_EMP.empid = inserted.empid)
END
Since this is for a test, I won't post any code. I will, however, make the suggestion that you read about triggers in Books Online because the solution is a whole lot easier than what you have above.
--Jeff Moden
June 3, 2011 at 6:43 am
I know that this topic is now slightly old, but I'd like to warn potential candidates looking at this thread that I set this test, so I know when someone has copied the answer to the trigger question verbatim from this forum!
To those who were wondering, this is not part of any Master's course, this is a basic SQL test that forms part of our recruitment screening process for technical roles. We always give further whiteboard questions at interview so it's obvious pretty quickly if they actually know any SQL.
Simon Mendoza
CTO, MDSL
June 15, 2011 at 9:31 pm
simon.mendoza (6/3/2011)
I know that this topic is now slightly old, but I'd like to warn potential candidates looking at this thread that I set this test, so I know when someone has copied the answer to the trigger question verbatim from this forum!To those who were wondering, this is not part of any Master's course, this is a basic SQL test that forms part of our recruitment screening process for technical roles. We always give further whiteboard questions at interview so it's obvious pretty quickly if they actually know any SQL.
Simon Mendoza
CTO, MDSL
Very cool for you to post your reply. Thank you very much. 🙂
--Jeff Moden
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply