Got an iPhone or iPad? We have a brand new Pastebin App for both devices, and it's totally free! Click here to download the new Pastebin App for iOS.
Public Pastes
Guest

sql_12112009

By: a guest on Nov 12th, 2009  |  syntax: SQL  |  size: 2.66 KB  |  hits: 39  |  expires: Never
download  |  raw  |  embed  |  report abuse
Copied
  1. --Ðàçãëåäàéòå òàáëèöàòà Customers.
  2. --Íàïèøåòå çàÿâêà, êîÿòî âðúùà ðàçëè÷íèòå ðåãèîíè, â êîèòî êîìïàíèÿòà èìà êëèåíòè.
  3. SET schema Northwind;
  4. SELECT DISTINCT region FROM customers;
  5. --Íàïèøåòå çàÿâêà, êîÿòî èçâåæäà èìå íà ïðîäóêò è èìå íà êëèåíòà,
  6. --êîéòî ãî å ïîðú÷àë.
  7. SELECT DISTINCT p.productname,c.contactname FROM
  8. products p JOIN "Order Details" od ON p.productid=od.productid
  9. JOIN orders o ON od.orderid=o.orderid
  10. JOIN customers c ON o.customerid=c.customerid;
  11.  
  12. --Íàïèøåòå çàÿâêà, êîÿòî èçâåæäà íîìåð íà êëèåíò, äòà íà ïîðú÷êà,
  13. --è èìå íà ïðîäóêò, ñàìî çà òåçè ñòðàíè, êîèòî èìàò íàä 2 ïîðú÷êè.
  14. --(Èçïîëçâàéòå è òàáëèöàòà Order Details).
  15.  
  16. SELECT c.customerid, o.orderdate, p.productname
  17.  FROM
  18.  customers c JOIN orders o ON o.customerid=c.customerid
  19.  JOIN "Order Details" od ON od.orderid=o.orderid
  20.  JOIN products p ON p.productid=od.productid
  21.  WHERE (SELECT count(shipcountry) FROM orders o2
  22.  WHERE o2.shipcountry=c.country)>2;
  23.  
  24.  --Íàïèøåòå çàÿâêà, êîÿòî èçâåæäà, ñïèñúê íà ñëóæèòåëèòå,
  25.  --êîèòî ñà ïîä÷èíåíè äèðåêòíî íà òîçè, êîèòî íÿìà øåô.
  26.  SELECT e.* FROM employees boss JOIN employees e ON e.reportsto=boss.employeeid
  27.  WHERE boss.reportsto IS NULL;
  28.  
  29. -- Íàïèøåòå çàÿâêà, êîÿòî èçâåæäà
  30. -- äâåòå èìåíà íà äàäåí ñëóæèòåë è èìåíàòà íà íåãîâèÿ øåô.
  31. SELECT e.firstname, e.lastname, boss.firstname BossFirstName, boss.lastname BossLastName
  32. FROM employees e JOIN employees boss
  33. ON e.reportsto=boss.employeeid;
  34.  
  35. --Èçâåäåòå èìåíàòà íà âñè÷êè êëèåíòè îò òàáëèöàòà Customers,
  36. --êîèòî ñà îò ñúùèÿ ãðàä êàòî êëèåíò ñ èìå 'Thomas Hardy'.
  37. --....
  38.  
  39. --Èçâåäåòå èìåòî è öåíàòà íà íàé-ñêúïèÿ ïðîäóêò, êîéòî êëèåíò
  40. --ñ èìå 'Philip Cramer', íÿêîãà ñè å ïîðú÷âàë.
  41. SELECT p.productname, p.unitprice FROM products p
  42. JOIN "Order Details" od ON od.productid=p.productid
  43. JOIN orders o ON o.orderid=od.orderid
  44. JOIN customers c ON c.customerid=o.customerid
  45. WHERE
  46. c.contactname='Philip Cramer'
  47. AND p.unitprice >=ALL(
  48. SELECT p.unitprice FROM products p
  49. JOIN "Order Details" od ON od.productid=p.productid
  50. JOIN orders o ON o.orderid=od.orderid
  51. JOIN customers c ON c.customerid=o.customerid
  52. WHERE
  53. c.contactname='Philip Cramer'
  54. );
  55.  
  56. SELECT p.productname, p.unitprice FROM products p
  57. JOIN "Order Details" od ON od.productid=p.productid
  58. JOIN orders o ON o.orderid=od.orderid
  59. JOIN customers c ON c.customerid=o.customerid
  60. WHERE
  61. c.contactname='Philip Cramer'
  62. ORDER BY p.unitprice DESC
  63. fetch first 1 rows only;
  64.  
  65. --Èçâåäåòå èìåíàòà íà âñè÷êè ïðîäóêòè, êîèòî ñà ïî-åâòèíè îò íàé-ñêúïèÿ ïðîäóêò,
  66. --êîéòî êëèåíò ñ èìå 'Philip Cramer' íÿêîãà ñè å ïîðú÷âàë.
  67. ---....
  68.  
  69. --Èçâåäåòå èìåíàòà íà âñè÷êè êëèåíòè
  70. --îò òàáëèöàòà Customers, êîèòî íèêîãà íå ñà ïîðú÷âàëè ïðîäóêò ñ èìå 'Tofu'.
  71. ---...
create a new version of this paste RAW Paste Data