need some query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

need some query help

Author

Topic

adnorton
Starting Member

4 Posts

Posted - 2006-06-06 : 10:00:17

Here is what I have so far. I am guessing number 3 is a join command.

13. Assume the following database table definitions and answer questions A through C:

account: account_id, added_by, added_date, updated_by, updated_date, name, status
instrument: instrument_id, added_by, added_date, updated_by, updated_date, name, status
position: account_id, instrument_id, quantity, added_by, added_date, updated_by, updated_date

A) Select the first 10 accounts, sorted by account_id, that have a status of "A"

SELECT TOP 10 Amount
FROM account
WHERE status = ‘A’
ORDER BY account_id

B) Select all position records for all accounts that have a status of "I" and were updated in the last month.

SELECT *
FROM position
WHERE status = ‘I’ AND updated_date

C) Write a query that will display all of the columns in the database showing the column name and the table it belongs to where the column name contains the text "added".

SELECT

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-06 : 10:12:44

A) Select the first 10 accounts, sorted by account_id, that have a status of "A"

SELECT TOP 10 Amount account_id
FROM account
WHERE status = 'A'
ORDER BY account_id

B) Select all position records for all accounts that have a status of "I" and were updated in the last month.

SELECT *
FROM position
WHERE status = ‘I’ AND updated_date  -- NOT COMPLETE. updated_date between ??? and ???

C) Write a query that will display all of the columns in the database showing the column name and the table it belongs to where the column name contains the text "added".
make use of INFORMATION_SCHEMA.COLUMNS

adnorton
Starting Member

4 Posts

Posted - 2006-06-06 : 10:25:38

Thanks for the quick response.

How does this look?

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = `added’

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-06 : 10:29:15

[code]SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like '%added%'[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-06 : 10:37:53

adnorton,

"showing the column name and the table"
You did not include the table name.

adnorton
Starting Member

4 Posts

Posted - 2006-06-06 : 10:40:37

oops

I did this

SELECT COLUMN_NAME, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE `%added%`

Thanks for the help much appreciated.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-06 : 10:41:50

quote:
Originally posted by adnorton
...How does this look?...

It looks like you cut and pasted your homework.

CODO ERGO SUM

adnorton
Starting Member

4 Posts

Posted - 2006-06-06 : 14:56:32

quote:
It looks like you cut and pasted your homework.

BZZZZZZZZZZZZZZZZ

Try again. =)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-06 : 18:00:04

quote:
Originally posted by adnorton

quote:
It looks like you cut and pasted your homework.

BZZZZZZZZZZZZZZZZ

Try again. =)

OK. It looks like you cut and pasted interview questions.

CODO ERGO SUM

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-07 : 01:45:43

Well at least he made some effort himself, thats uncommon.

I think the answer to #2 should be something more like:

SELECT P.*
FROM account A
INNER JOIN position P
ON P.account_id = A.account_id
WHERE A.status = 'I' AND
   P.updated_date >= dateadd(m, datediff(m, 0, getdate()) - 1, 0) AND
   P.updated_date < dateadd(m, datediff(m, 0, getdate()), 0)

but it is formulated a bit vague.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources