convert Oracle to SQL Server Queries

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

SQL Server 2000 Forums

Transact-SQL (2000)

convert Oracle to SQL Server Queries

Author

Topic

ragh
Starting Member

34 Posts

Posted - 2002-11-28 : 22:46:33

/* -- 15) Which course has below AVERAGE number of students?
SELECT COURSE FROM STUDIES HAVING COUNT(NAME)<(SELECT AVG(COUNT(NAME)) FROM STUDIES
GROUP BY COURSE) GROUP BY COURSE */
----------------------------------------------------------------------
/*-- 16) Which institute conducts the above course?
SELECT SPLACE
FROM STUDIES
WHERE COURSE IN (SELECT COURSE
FROM STUDIES
HAVING COUNT(NAME) < (SELECT AVG(COUNT(NAME))
FROM STUDIES
GROUP BY COURSE)
GROUP BY COURSE) */
----------------------------------------------------------------------
/* -- 37) In WHICH language are MOST of the programmer's proficient?

SELECT PROF1 FROM PROGRAMMER GROUP BY PROF1
HAVING COUNT(PROF1)=(SELECT MAX(COUNT(PROF1))
FROM PROGRAMMER
GROUP BY PROF1)
OR COUNT(PROF2)=(SELECT MAX(COUNT(PROF2))
FROM PROGRAMMER
GROUP BY PROF2)
UNION
SELECT PROF2
FROM PROGRAMMER
GROUP BY PROF2
HAVING COUNT(PROF1)=(SELECT MAX(COUNT(PROF1))
FROM PROGRAMMER
GROUP BY PROF1)
OR COUNT(PROF2)=(SELECT MAX(COUNT(PROF2))
FROM PROGRAMMER
GROUP BY PROF2) */
----------------------------------------------------------------------

Anyone have any documents about converting oracle syntax to ms-sql sysntax please send it to me. raghx_2000@yahoo.com

bye :)

Ragh

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-11-29 : 05:37:18

Don't know if it'll help, but I did write an article on this topic:
http://www.sqlteam.com/redir.asp?ItemID=11324

--
HTH,
Vyas
http://vyaskn.tripod.com

ragh
Starting Member

34 Posts

Posted - 2002-12-09 : 23:29:21

hi vyas !

I saw the link, but you have just given a description about the differences, but in real there's much much difference in oracle and swl server, ex: Last_day() etc..
How can i solve these queries ? that the Q

:)

Ragh

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-12-10 : 00:54:50

There is no easy way. Like you said, there are major differences. You basically have to rework your app to take advantage of the way SQL server works.

Damian

ragh
Starting Member

34 Posts

Posted - 2002-12-11 : 01:41:03

hey here's another stuff
I have a table called programmer where i have a column as dob (date of birth) i want to select all those programmers who were born on the last day of the month, i repeat LASTDAY of the month, i know fetch the last day of the current month but how could i find the rows,
I wrote this query :
-- Who are the programmers who were born on the last day of the month?
SELECT name, dob AS 'Person Name'
FROM Programmer
WHERE (
SELECT
DAY(DATEADD(d,-DAY(DATEADD(m,DATEPART(d,dob),dob)),DATEADD(m,DATEPART(d,dob),dob))))
<= Day(dob)
The problem is it shows all of them other than born in february 28 or 29th. ???
How can i alter this... got any idea guys...!

Ragh

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-11 : 08:54:07

You don't need a subquery to do this:

SELECT name AS "Person Name", dob FROM Programmer
WHERE DateDiff(month, dob, DateAdd(day, 1, dob))=1

Edited by - robvolk on 12/11/2002 08:54:32

ragh
Starting Member

34 Posts

Posted - 2002-12-12 : 23:21:13

Thanks robs !

Can you please try to answer the above 15 and 16 query, all i need is to find the avg # students and the place where the course is conducted .

:)

Ragh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources