ijÍâÆóSQL ServerÃæ試題
--> Title : ijÍâÆóSQL ServerÃæ試題
--> Author : wufeng4552
--> Date : 2010-1-15
Question 1£ºCan you use a batch SQL or store procedure to calculating the Number of Days in a Month
Answer 1£ºÕÒ³öµ±ÔµÄÌìÊý
select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-01' as datetime))))
Question2£ºCan you use a SQL statement to calculating it!
How can I print "10 to 20" for books that sell for between $10 and $20£¬"unknown" for books whose price is null, and "other" for all other prices?
Answer 2£º
select bookid,
bookname,
price=case when price is null then 'unknown'
when price between 10 and 20 then '10 to 20'
else price
end
from books
Question3£ºCan you use a SQL statement to finding duplicate values!
How can I find authors with the same last name?You can use the table authors in datatabase pubs. I want to get the result as below:
Output:
au_lname number_dups
---------------------------------------- -----------
Ringer 2
(1 row(s) affected)
Answer 3:
select au_lname,
number_dups=count(1)
from authors
group by au_lname
Question4£ºCan you create a cro
Ïà¹ØÎĵµ£º
ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃæ£¬ºóÃæ ......
×î½üÓõ½ÈÕÆÚ·¶Î§µÄ²éѯ£¬Óõ½ÁËbetween£¬µ«ÊÇ·¢ÏÖÈç¹ûÈÕÆÚ´øÊ±¼äµÄ»°£¬BETWEEN '2010-1-4' AND
'2010-1-4'ÕâÑùµÄÓï¾äÊDz鲻µ½Êý¾ÝµÄ£¬ºóÀ´Ïëµ½ÁËÒ»ÖָĽø·½·¨£¬¹©´ó¼Ò²Î¿¼¡£
ÈçÏ£º
BETWEEN '2010-1-4' AND DATEADD(day, 1, '2010-1-4')
ÓÃÁËDATEADDÒÔºó£¬Ð§¹ûºÜ²»´í ......
ÓÅ»¯Æ÷ÔÚÐγÉÖ´Ðмƻ®Ê±ÐèÒª×öµÄÒ»¸öÖØÒªÑ¡ÔñÊÇÈçºÎ´ÓÊý¾Ý¿â²éѯ³öÐèÒªµÄÊý¾Ý¡£¶ÔÓÚSQLÓï¾ä´æÈ¡µÄÈκαíÖеÄÈκÎÐУ¬¿ÉÄÜ´æÔÚÐí¶à´æÈ¡Â·¾¶(´æÈ¡·½·¨)£¬Í¨¹ýËüÃÇ¿ÉÒÔ¶¨Î»ºÍ²éѯ³öÐèÒªµÄÊý¾Ý¡£ÓÅ»¯Æ÷Ñ¡ÔñÆäÖÐ×ÔÈÏΪÊÇ×îÓÅ»¯µÄ·¾¶¡£
¡¡¡¡ÔÚÎïÀí²ã£¬oracle¶ÁÈ¡Êý¾Ý£¬Ò»´Î¶ÁÈ¡µÄ×îСµ¥Î»ÎªÊý¾Ý¿â¿é(Óɶà¸öÁ¬ÐøµÄ²Ù×÷ϵͳ¿é×é³É ......
Ò»¡¢ÅäÖà SQL Server£¬Ê¹Ö®ÔÊÐí CLR ¼¯³É:
¡¡¡¡1.µ¥»÷“¿ªÊ¼”°´Å¥£¬ÒÀ´ÎÖ¸Ïò“ËùÓгÌÐò”¡¢Microsoft SQL Server 2005 ºÍ“ÅäÖù¤¾ß”£¬È»ºóµ¥»÷“ÍâΧӦÓÃÅäÖÃÆ÷”¡£
¡¡¡¡2.ÔÚ SQL Server 2005 ÍâΧӦÓÃÅäÖÃÆ÷¹¤¾ßÖУ¬µ¥»÷“¹¦ÄܵÄÍâΧӦÓÃÅäÖÃÆ÷”¡£
¡¡¡¡3.Ñ¡ÔñÄ ......
ÄãÊÇ·ñÓöµ½¹ý ÏëÔÚ ×Ö·û´®ÀïÃæÐ´ SQLÓï¾ä£¬µ«ÊÇ×ÜÊÇÓöµ½ ijЩ·ûºÅ²»»áд.
±ÈÈç˵ÔÚ×Ö·û´®ÀïÃæÐ´¸ö±äÁ¿.
like: str sql="select * from abc where id= ' "++" ' "
idµÄ±äÁ¿Ó¦ ÏÈÓõ¥ÒýºÅÈ»ºó“+”ºÅ¡£
½ñÌìÓöµ½¸öºÜ³¤µÄSQLÓï¾ä£¬¶øÇÒSQLÓï¾äÀïÃæÇ¶Ì×ÁË×Ö·û´®¡£µ±Ê±¸ù±¾²»»áд ......