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
Ïà¹ØÎĵµ£º
ch02
--3-4
select * from orders
where 'México D.F.' in
(select City
from Customers
where Orders.CustomerID = Customers.CustomerID )
--3-5
select * from orders
where 'usa' =
(select Country
from Customers
where Orders.CustomerID = Customers.CustomerID )
--4-1
select * from or ......
ÓÅ»¯Æ÷ÔÚÐγÉÖ´Ðмƻ®Ê±ÐèÒª×öµÄÒ»¸öÖØÒªÑ¡ÔñÊÇÈçºÎ´ÓÊý¾Ý¿â²éѯ³öÐèÒªµÄÊý¾Ý¡£¶ÔÓÚSQLÓï¾ä´æÈ¡µÄÈκαíÖеÄÈκÎÐУ¬¿ÉÄÜ´æÔÚÐí¶à´æÈ¡Â·¾¶(´æÈ¡·½·¨)£¬Í¨¹ýËüÃÇ¿ÉÒÔ¶¨Î»ºÍ²éѯ³öÐèÒªµÄÊý¾Ý¡£ÓÅ»¯Æ÷Ñ¡ÔñÆäÖÐ×ÔÈÏΪÊÇ×îÓÅ»¯µÄ·¾¶¡£
¡¡¡¡ÔÚÎïÀí²ã£¬oracle¶ÁÈ¡Êý¾Ý£¬Ò»´Î¶ÁÈ¡µÄ×îСµ¥Î»ÎªÊý¾Ý¿â¿é(Óɶà¸öÁ¬ÐøµÄ²Ù×÷ϵͳ¿é×é³É ......
SQLº¯Êý
ÔÚSQLÖУ¬º¯Êý¶ÔÊý¾Ý»òÊý¾Ý×éÖ´ÐвÙ×÷£¬È»ºó·µ»ØÐèÒªµÄÖµ¡£º¯Êý±í´ïʽ¿ÉÒÔ³öÏÖÔÚSELECTÁбíÖУ¬»òÕß
ÔÚÈκÎÔÊÐí³öÏÖµÄλÖÃÉÏ¡£SQL°üº¬ÁËÆßÖÖº¯Êý:
(1)¾ÛºÏº¯Êý:·µ»Ø»ã×ÜÖµ¡£
(2)תÐͺ¯Êý:½«Ò»ÖÖÊý¾ÝÀàÐÍת»»ÎªÁíÍâÒ»ÖÖ¡£
(3)ÈÕÆÚº¯Êý:´¦ÀíÈÕÆÚºÍʱ¼ä¡£
(4)Êýѧº¯Êý:Ö´ÐÐËãÊõÔËËã¡£
(5)×Ö·û´®º¯Êý:¶Ô×Ö·û´ ......
ת×Ô£ºhttp://tech.ddvip.com/2007-05/117955341625057.html
¼ì²é¸÷Öֱ仯
¡¡¡¡ÎÒÔÚÉè¼ÆÊý¾Ý¿âµÄʱºò»á¿¼Âǵ½ÄÄЩÊý¾Ý×ֶν«À´¿ÉÄܻᷢÉú±ä¸ü¡£±È·½Ëµ£¬ÐÕÊϾÍÊÇÈç´Ë£¨×¢ÒâÊÇÎ÷·½È˵ÄÐÕÊÏ£¬±ÈÈçÅ®ÐÔ½á»éºó´Ó·òÐյȣ©¡£ËùÒÔ£¬ÔÚ½¨Á¢ÏµÍ³´æ´¢¿Í»§ÐÅϢʱ£¬ÎÒÇãÏòÓÚÔÚµ¥¶ÀµÄÒ»¸öÊý¾Ý±íÀï´æ´¢ÐÕÊÏ×ֶΣ¬¶øÇÒ»¹¸½¼ÓÆðʼÈÕºÍÖÕÖ¹ ......