Advertisement
Shailrshah

SQL Queries

Sep 24th, 2013
455
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 18.93 KB | None | 0 0
  1. CREATE TABLE custo(
  2.     cust_id     VARCHAR2(3) NOT NULL PRIMARY KEY,
  3.     lnaame      VARCHAR2(20),
  4.     fname       VARCHAR2(20),
  5.     area        VARCHAR2(2),
  6.     ph_no       NUMBER(10)
  7. );
  8.  
  9. CREATE TABLE movies(
  10.     mv_no       NUMBER(2) NOT NULL PRIMARY KEY,
  11.     title       VARCHAR2(20),
  12.     TYPE        VARCHAR2(20),
  13.     star        VARCHAR2(20),
  14.     price       NUMBER(6,2)
  15. );
  16.  
  17. CREATE TABLE invoice(
  18.     inv_no      VARCHAR2(3) NOT NULL PRIMARY KEY,
  19.     cust_id     VARCHAR2(3),
  20.     mv_no       NUMBER(2),
  21.     issue_date  DATE,
  22.     return_date     DATE,
  23.     CONSTRAINT cust_con FOREIGN KEY(cust_id) REFERENCES custo(cust_id),
  24.     CONSTRAINT movies_con FOREIGN KEY(mv_no) REFERENCES movies(mv_no)
  25. );
  26.  
  27. -- 1. Names of all the customers
  28. SQL> SELECT fname,lname FROM custo;
  29. -- FNAME           LNAME
  30. -- --------------- ---------------
  31. -- Ivan            Bayross
  32. -- Vandana         Saitwal
  33. -- Basu            Navindgi
  34. -- Ravi            Sreedharan
  35. -- Rukmini
  36. -- Pramada         Jaguste
  37.  
  38. -- 2. Entire customer table
  39. SQL> SELECT * FROM custo;
  40. -- CUS LNAME           FNAME           AR   PHONE_NO
  41. -- --- --------------- --------------- -- ----------
  42. -- a01 Bayross         Ivan            sa    6125467
  43. -- a02 Saitwal         Vandana         mu    5560379
  44. -- a04 Navindgi        Basu            ba    6125401
  45. -- a05 Sreedharan      Ravi            va
  46. -- a06                 Rukmini         gh    5125274
  47. -- a03 Jaguste         Pramada         da   45863891
  48.  
  49.  
  50. -- 3. fname and area of customers
  51. SQL> SELECT fname, area FROM custo;
  52. -- FNAME           AR
  53. -- --------------- --
  54. -- Ivan            sa
  55. -- Vandana         mu
  56. -- Basu            ba
  57. -- Ravi            va
  58. -- Rukmini         gh
  59. -- Pramada         da
  60.  
  61.  
  62. -- 4. Various movie types in the movie table
  63. SQL> SELECT DISTINCT(TYPE) FROM movies;
  64. -- TYPE
  65. -- ----------
  66. -- action
  67. -- comedy
  68. -- drama
  69. -- horror
  70. -- romance
  71. -- suspense
  72. -- thriller
  73.  
  74. -- 5. Name of all customers having 'a' as the second letter of their fnames
  75. SQL> SELECT fname FROM cust WHERE fname LIKE '_a%';
  76. -- FNAME
  77. -- ---------------
  78. -- vandana
  79. -- basu
  80. -- ravi
  81.  
  82.  
  83. -- 6. lnames of all customers that begin with 's' or 'j'
  84. SQL> SELECT lname FROM custo WHERE lname LIKE 'S%' OR lname LIKE 'J%';
  85. -- LNAME
  86. -- ---------------
  87. -- Saitwal
  88. -- Sreedharan
  89. -- Jaguste
  90.  
  91.  
  92. -- 7.Customers who stay in an area whose second letter is 'a'
  93. SQL> SELECT fname, lname FROM cust WHERE area LIKE '_a%';
  94. -- FNAME           LNAME
  95. -- --------------- ---------------
  96. -- ivan            bayross
  97. -- pramada         jaguste
  98. -- basu            navindgi
  99. -- ravi            sreedharan
  100.  
  101.  
  102. -- 8.Customers who stay in area 'da' or 'mu' or 'gh'
  103. SQL> SELECT fname,lname FROM custo WHERE area = 'da' OR area = 'mu' OR area = 'gh';
  104. -- FNAME           LNAME
  105. -- --------------- ---------------
  106. -- Vandana         Saitwal
  107. -- Rukmini
  108. -- Pramada         Jaguste
  109.  
  110.  
  111. -- 9. Customers whose phone numbers are greater than 5550000
  112. SQL> SELECT fname, lname FROM custo WHERE phone_no > 5550000;
  113. -- FNAME           LNAME
  114. -- --------------- ---------------
  115. -- Ivan            Bayross
  116. -- Vandana         Saitwal
  117. -- Basu            Navindgi
  118. -- Pramada         Jaguste
  119.  
  120.  
  121. -- 10. Customers who have been issued movies in the month of September
  122. SQL> SELECT cust_id FROM invoice  WHERE issue_date LIKE '%SEP%';
  123. -- CUS
  124. -- ---
  125. -- a06
  126. -- a06
  127. -- a03
  128.  
  129.  
  130. -- 11. invoice table information for cust_id 'a01' and 'a02'
  131. SQL> SELECT * FROM invoice WHERE cust_id = 'a01' OR cust_id = 'a02';
  132. -- INV      MV_NO CUS ISSUE_DAT RETURN_DA
  133. -- --- ---------- --- --------- ---------
  134. -- i01          4 a01 23-JUL-93 25-JUL-93
  135. -- i02          3 a02 12-AUG-93 15-AUG-93
  136. -- i03          1 a02 15-AUG-93 18-AUG-93
  137. -- i08          9 a01 11-AUG-93 14-AUG-93
  138.  
  139.  
  140. -- 12. Movies of type and 'action' and 'comedy'
  141. SQL> SELECT title FROM movies WHERE TYPE = 'action' OR TYPE = 'comedy';
  142. -- TITLE
  143. -- -------------------------
  144. -- bloody vengeance
  145. -- home alone
  146. -- quick change
  147. -- carry on doctor
  148.  
  149.  
  150. -- 13. Movie whose price is greater than 150 and less than or equal to 200
  151. SQL> SELECT title FROM movies WHERE price > 150 AND price <=200;
  152. -- TITLE
  153. -- -------------------------
  154. -- bloody vengeance
  155. -- the firm
  156. -- pretty woman
  157. -- the fugitive
  158. -- dracula
  159. -- gone with the wind
  160.  
  161.  
  162. -- 14, 15. movies that cost more than 150 and new cost as original cost * 15
  163. SQL> SELECT title, price * 15 AS "new_cost" FROM movies WHERE price > 150;
  164. -- TITLE                       new_cost
  165. -- ------------------------- ----------
  166. -- bloody vengeance             2714.25
  167. -- the firm                        3000
  168. -- pretty woman                 2258.25
  169. -- the fugitive                    3000
  170. -- dracula                      2253.75
  171. -- gone with the wind              3000
  172.  
  173.  
  174. -- 16. Movies sorted according to title
  175. SQL> SELECT title FROM movies ORDER BY title;
  176. -- TITLE
  177. -- -------------------------
  178. -- bloody vengeance
  179. -- carry on doctor
  180. -- coma
  181. -- dracula
  182. -- gone with the wind
  183. -- home alone
  184. -- pretty woman
  185. -- quick change
  186. -- the firm
  187. -- the fugitive
  188.  
  189.  
  190. -- 17. names and types of all movies except horror movies
  191. SQL> SELECT title, TYPE FROM movies WHERE TYPE != 'horror';
  192.  
  193. -- TITLE                     TYPE
  194. -- ------------------------- ----------
  195. -- bloody vengeance          action
  196. -- the firm                  thriller
  197. -- pretty woman              romance
  198. -- home alone                comedy
  199. -- the fugitive              thriller
  200. -- coma                      suspense
  201. -- quick change              comedy
  202. -- gone with the wind        drama
  203. -- carry on doctor           comedy
  204.  
  205.  
  206. -- 18. Square root of price of each movie
  207. SQL> SELECT SQRT(price) FROM movies;
  208. -- SQRT(PRICE)
  209. -- -----------
  210. --  13.4517657
  211. --  14.1421356
  212. --  12.2698818
  213. --  12.2474487
  214. --  14.1421356
  215. --          10
  216. --  12.2576507
  217. --          10
  218. --  14.1421356
  219. --          10
  220.  
  221.  
  222. -- 19. Divide cost of movie 'home alone' by difference between its price and 130
  223. SQL> SELECT price /(price - 100) FROM movies WHERE title = 'home alone';
  224. -- PRICE/(PRICE-100)
  225. -- -----------------
  226. --                 3
  227.  
  228.  
  229. -- 20. Names, areas and cust_id of customers without phone numbers
  230. SQL> SELECT fname,lname, area, cust_id FROM custo WHERE phone_no IS NULL;
  231. -- FNAME           LNAME           AR CUS
  232. -- --------------- --------------- -- ---
  233. -- Ravi            Sreedharan      va a05
  234.  
  235.  
  236. -- 21. names of customers without lname.
  237. SQL> SELECT fname FROM custo WHERE lname IS NULL;
  238. -- FNAME
  239. -- ---------------
  240. -- Rukmini
  241.  
  242.  
  243. -- 22. mv_no, title, type of movies whose stars begin with 'm'
  244. SQL> SELECT mv_no, title, TYPE FROM movies WHERE star LIKE 'm%';
  245. --      MV_NO TITLE                     TYPE
  246. -- ---------- ------------------------- ----------
  247. --          4 home alone                comedy
  248. --          6 coma                      suspense
  249.  
  250.  
  251. -- 23. mv_no and inv_no of customers having inv_no less than 'i05'
  252. SQL> SELECT mv_no, inv_no FROM invoice WHERE inv_no<'i05';
  253. --      MV_NO INV
  254. -- ---------- ---
  255. --          4 i01
  256. --          3 i02
  257. --          1 i03
  258. --          6 i04
  259.  
  260.  
  261. -- 24. Total number of customers
  262. SQL> SELECT COUNT(cust_id) FROM Custo;
  263. -- COUNT(CUST_ID)
  264. -- --------------
  265. --              6
  266.  
  267.  
  268. -- 25. Sum of prices of all movies
  269. SQL> SELECT SUM(price) FROM movies;
  270. -- SUM(PRICE)
  271. -- ----------
  272. --    1531.75
  273.  
  274.  
  275. -- 26. Average price of all movies
  276. SQL> SELECT SUM(price)/COUNT(price) FROM movies;
  277. -- SUM(PRICE)/COUNT(PRICE)
  278. -- -----------------------
  279. --                 153.175
  280.  
  281.  
  282. -- 27. Maximum and and Minimum movie prices, renaming to "max_price" and "min_price"
  283. SQL> SELECT MAX(price) AS "max_price", MIN(price) AS "min_price" FROM movies;
  284. --  max_price  min_price
  285. -- ---------- ----------
  286. --        200        100
  287.  
  288.  
  289. -- 28. Number of movies having price greater than 150
  290. SQL> SELECT COUNT(price) FROM movies WHERE price>= 150;
  291. -- COUNT(PRICE)
  292. -- ------------
  293. --            7
  294.  
  295.  
  296. -- 29. Printing something
  297. SQL> SELECT 'The invoice number of customer ID ' || cust_id || ' is ' || inv_no || ' and movie number is ' || mv_no FROM invoice;
  298. -- --------------------------------------------------------------------------------
  299. -- The invoice number of customer ID a01 is i01 and movie number is 4
  300. -- The invoice number of customer ID a02 is i02 and movie number is 3
  301. -- The invoice number of customer ID a02 is i03 and movie number is 1
  302. -- The invoice number of customer ID a06 is i10 and movie number is 8
  303. -- The invoice number of customer ID a01 is i08 and movie number is 9
  304. -- The invoice number of customer ID a05 is i07 and movie number is 9
  305. -- The invoice number of customer ID a06 is i06 and movie number is 2
  306. -- The invoice number of customer ID a04 is i05 and movie number is 7
  307. -- The invoice number of customer ID a03 is i09 and movie number is 5
  308. -- The invoice number of customer ID a03 is i04 and movie number is 6
  309.  
  310.  
  311. SQL> SELECT cust_id||' has taken movie number '||mv_no||' on '|| issue_date||' and will return on '||return_date FROM invoice;
  312. -- --------------------------------------------------------------------------------
  313. -- a01 has taken movie number 4 on 23-JUL-93 and will return on 25-JUL-93
  314. -- a02 has taken movie number 3 on 12-AUG-93 and will return on 15-AUG-93
  315. -- a02 has taken movie number 1 on 15-AUG-93 and will return on 18-AUG-93
  316. -- a06 has taken movie number 8 on 03-SEP-93 and will return on 06-SEP-93
  317. -- a01 has taken movie number 9 on 11-AUG-93 and will return on 14-AUG-93
  318. -- a05 has taken movie number 9 on 07-JUL-93 and will return on 10-JUL-93
  319. -- a06 has taken movie number 2 on 18-SEP-93 and will return on 21-SEP-93
  320. -- a04 has taken movie number 7 on 05-AUG-93 and will return on 08-AUG-93
  321. -- a03 has taken movie number 5 on 06-JUL-93 and will return on 09-JUL-93
  322. -- a03 has taken movie number 6 on 10-SEP-93 and will return on 13-SEP-93
  323.  
  324.  
  325. -- 30. Printing the type and average price of each movie
  326. SQL> SELECT TYPE, avg(price) FROM movies GROUP BY TYPE;
  327. -- TYPE       AVG(PRICE)
  328. -- ---------- ----------
  329. -- action         180.95
  330. -- comedy     116.666667
  331. -- drama             200
  332. -- horror         150.25
  333. -- romance        150.55
  334. -- suspense          100
  335. -- thriller          200
  336.  
  337.  
  338. -- 31. Number of movies in each type
  339. SQL> SELECT TYPE, COUNT(title) FROM movies GROUP BY TYPE;
  340. -- TYPE       COUNT(TITLE)
  341. -- ---------- ------------
  342. -- action                1
  343. -- comedy                3
  344. -- drama                 1
  345. -- horror                1
  346. -- romance               1
  347. -- suspense              1
  348. -- thriller              2
  349.  
  350.  
  351. -- 32. Number of movies in 'comedy' and 'thriller' type
  352. SQL> SELECT TYPE, COUNT(TYPE) FROM movies WHERE TYPE = 'comedy' OR TYPE = 'thriller' GROUP BY TYPE;
  353. -- TYPE       COUNT(TYPE)
  354. -- ---------- -----------
  355. -- comedy               3
  356. -- thriller             2
  357.  
  358.  
  359. -- 33. Average price of all movies where type is 'comedy' or 'thriller' and price is greater than or equal to 150
  360. SQL> SELECT TYPE, avg(price) FROM movies WHERE price <= 150 GROUP BY TYPE;
  361. -- TYPE       AVG(PRICE)
  362. -- ---------- ----------
  363. -- comedy     116.666667
  364. -- suspense          100
  365.  
  366.  
  367. -- 34. Average price of all movies where type is 'comedy' or 'thriller' and price
  368. SQL> SELECT TYPE, avg(price) FROM movies WHERE price>=150 GROUP BY TYPE HAVING TYPE = 'comedy' OR TYPE = 'thriller';
  369. -- TYPE       AVG(PRICE)
  370. -- ---------- ----------
  371. -- comedy            150
  372. -- thriller          200
  373.  
  374.  
  375. -- 35. The movie number which has been issued to 'Ivan'
  376. SQL> SELECT mv_no FROM invoice i, custo c WHERE i.cust_id = c.cust_id AND fname = 'Ivan';
  377. --      MV_NO
  378. -- ----------
  379. --          4
  380. --          9
  381.  
  382.  
  383. -- 36. Names and movie from all the customers who have been issued a movie
  384. SQL> SELECT fname, lname, mv_no FROM invoice i, custo c WHERE i.cust_id = c.cust_id;
  385. -- FNAME           LNAME                MV_NO
  386. -- --------------- --------------- ----------
  387. -- Ivan            Bayross                  4
  388. -- Vandana         Saitwal                  3
  389. -- Vandana         Saitwal                  1
  390. -- Rukmini                                  8
  391. -- Ivan            Bayross                  9
  392. -- Ravi            Sreedharan               9
  393. -- Rukmini                                  2
  394. -- Basu            Navindgi                 7
  395. -- Pramada         Jaguste                  5
  396. -- Pramada         Jaguste                  6
  397.  
  398.  
  399. -- 37. Movie title & no. and customer ID for all issued movies
  400. SQL> SELECT title, cust_id, m.mv_no FROM movie m, invoice i WHERE m.mv_no = i.mv_no;
  401. -- TITLE                     CUS      MV_NO
  402. -- ------------------------- --- ----------
  403. -- Home Alone                a01          4
  404. -- Pretty Woman              a02          3
  405. -- Bloody Vengeance          a02          1
  406. -- Coma                      a03          6
  407. -- Dracula                   a04          7
  408. -- The Firm                  a06          2
  409. -- Gone with the Wind        a05          9
  410. -- Gone with the Wind        a01          9
  411. -- The Figitive              a03          5
  412. -- Quick Change              a06          8
  413.  
  414.  
  415. -- 38. Title and types of movies issued to Vandana
  416. SQL> SELECT title, TYPE FROM movie m, invoice i, cust c WHERE m.mv_no = i.mv_no AND i.cust_id = c.cust_id AND c.fname = 'Vandana';
  417. -- TITLE                     TYPE
  418. -- ------------------------- ----------
  419. -- Pretty Woman              Romance
  420. -- Bloody Vengeance          Action
  421.  
  422.  
  423. -- 39. Names of customers who have been issued Drama movies
  424. SQL> SELECT lname, fname FROM cust c, movie m, invoice i WHERE c.cust_id = i.cust_id AND  i.mv_no = m.mv_no  AND m.TYPE = 'Drama';
  425. -- LNAME           FNAME
  426. -- --------------- ---------------
  427. -- Sreedharan      Ravi
  428. -- Bayross         Ivan
  429.  
  430.  
  431. -- 40. Customer names and movie titles for issued movies with movie number greater or equal to 3.
  432. SQL> SELECT 'The movie taken by '||fname||' '||lname||' is '||title||'.' FROM invoice i, movie m, cust c WHERE i.cust_id = c. cust_id AND i.mv_no = m.mv_no AND m.mv_no >=3;
  433. --------------------------------------------------------------------------------
  434. -- The movie taken by Vandana Saitwal is Pretty Woman.
  435. -- The movie taken by Ivan Bayross is Home Alone.
  436. -- The movie taken by Pramada Jaguste is The Figitive.
  437. -- The movie taken by Pramada Jaguste is Coma.
  438. -- The movie taken by Basu Navindgi is Dracula.
  439. -- The movie taken by Rukmini  is Quick Change.
  440. -- The movie taken by Ivan Bayross is Gone with the Wind.
  441. -- The movie taken by Ravi Sreedharan is Gone with the Wind.
  442.  
  443.  
  444. -- 41. Customers which have been issued movie number 9.
  445. SQL> SELECT  fname FROM custo WHERE cust_id IN (SELECT cust_id FROM invoice WHERE mv_no=9);
  446. -- FNAME
  447. -- ---------------
  448. -- Ivan
  449. -- Ravi
  450.  
  451.  
  452. -- 42. Customer name and area with invoice number 'i10'.
  453. SQL> SELECT  fname, area FROM custo WHERE cust_id IN (SELECT cust_id FROM invoice WHERE inv_no = 'i10');
  454. -- FNAME           AR
  455. -- --------------- --
  456. -- Rukmini         gh
  457.  
  458.  
  459. -- 43. Customer names and phone numbers who have been issued movies
  460. SQL> SELECT lname, fname, ph_no gtom cust WHERE cust_id IN(SELECT cust_id IN invoice);
  461.  
  462.  
  463. -- 44. Name of movies issued to Vandana and Ivan
  464. SQL> SELECT title FROM movies WHERE mv_no IN(SELECT mv_no FROM invoice WHERE cust_id IN(SELECT cust_id FROM custo WHERE fname = 'Vandana' OR fname = 'Ivan'));
  465. -- TITLE
  466. -- -------------------------
  467. -- home alone
  468. -- pretty woman
  469. -- bloody vengeance
  470. -- gone with the wind
  471.  
  472.  
  473. -- 45. Movie number, movie names issued to all customers
  474. SQL> SELECT mv_no, title FROM movies WHERE mv_no IN (SELECT mv_no FROM invoice);
  475. --      MV_NO TITLE
  476. -- ---------- -------------------------
  477. --          4 home alone
  478. --          3 pretty woman
  479. --          1 bloody vengeance
  480. --          8 quick change
  481. --          9 gone with the wind
  482. --          2 the firm
  483. --          7 dracula
  484. --          5 the fugitive
  485. --          6 coma
  486.  
  487.  
  488. -- 46. Type and movie number of movies issued to cust_id a01 and a02
  489. SQL> SELECT mv_no, title FROM movies WHERE mv_no IN (SELECT mv_no FROM invoice WHERE cust_id = 'a01' OR cust_id = 'a02');
  490. --      MV_NO TITLE
  491. -- ---------- -------------------------
  492. --          4 home alone
  493. --          3 pretty woman
  494. --          1 bloody vengeance
  495. --          9 gone with the wind
  496.  
  497.  
  498. -- 47. Cust_id of the customer issuing the movie staring tom cruise
  499. SELECT cust_id FROM invoice WHERE mv_no IN (SELECT mv_no FROM movies WHERE star = 'tom cruise');
  500. -- CUS
  501. -- ---
  502. -- a06
  503.  
  504.  
  505. -- 48. lname, fname who have been issued movies
  506. SQL> SELECT lname, fname FROM custo WHERE cust_id IN(SELECT cust_id FROM invoice);
  507. -- LNAME           FNAME
  508. -- --------------- ---------------
  509. -- Bayross         Ivan
  510. -- Saitwal         Vandana
  511. --                 Rukmini
  512. -- Sreedharan      Ravi
  513. -- Navindgi        Basu
  514. -- Jaguste         Pramada
  515.  
  516.  
  517. -- 49. Invoice number and day on which customers were issued movies
  518. SQL> SELECT inv_no,to_char(issue_date, 'day') FROM invoice;
  519. -- INV TO_CHAR(I
  520. -- --- ---------
  521. -- i01 friday
  522. -- i02 thursday
  523. -- i03 sunday
  524. -- i10 friday
  525. -- i08 wednesday
  526. -- i07 wednesday
  527. -- i06 saturday
  528. -- i05 thursday
  529. -- i09 tuesday
  530. -- i04 friday
  531.  
  532.  
  533. -- 50. Month(in alphabets) in which customers are supposed to return the movies
  534. SQL> SELECT to_char(return_date, 'month') FROM invoice;
  535. -- TO_CHAR(R
  536. -- ---------
  537. -- july
  538. -- august
  539. -- august
  540. -- september
  541. -- august
  542. -- july
  543. -- september
  544. -- august
  545. -- july
  546. -- september
  547.  
  548.  
  549. -- 51. issue date in the form 'dd-month-year'
  550. SQL> SELECT to_char(issue_date, 'dd-month-yyyy') FROM invoice;
  551. -- TO_CHAR(ISSUE_DAT
  552. -- -----------------
  553. -- 23-july     -1993
  554. -- 12-august   -1993
  555. -- 15-august   -1993
  556. -- 03-september-1993
  557. -- 11-august   -1993
  558. -- 07-july     -1993
  559. -- 18-september-1993
  560. -- 05-august   -1993
  561. -- 06-july     -1993
  562. -- 10-september-1993
  563.  
  564. -- 52. The date, 15 days after the current date
  565. SQL> SELECT sysdate+15 FROM dual;
  566. -- SYSDATE+1
  567. -- ---------
  568. -- 08-OCT-13
  569.  
  570.  
  571. -- 53. Number of days elapsed between the current date and the return date of the movies for all customers
  572. SQL> SELECT sysdate-return_date FROM invoice;
  573. -- SYSDATE-RETURN_DATE
  574. -- -------------------
  575. --          7365.64499
  576. --          7344.64499
  577. --          7341.64499
  578. --          7322.64499
  579. --          7345.64499
  580. --          7380.64499
  581. --          7307.64499
  582. --          7351.64499
  583. --          7381.64499
  584. --          7315.64499
  585.  
  586.  
  587. -- 54. Changing telephone number of Pramada to 466389
  588. SQL> UPDATE custo SET phone_no = 466389 WHERE fname = 'Vandana';
  589. -- 1 row updated.
  590.  
  591.  
  592. -- 55. Changing issue date of cust_id 'a01' to 24-07-93
  593. UPDATE invoice SET issue_date = '24-JUL-93' WHERE cust_id = 'a01';
  594. -- 1 row updated.
  595.  
  596.  
  597. -- 56. Changing the price of Gone with the Wind to Rs. 250.00
  598. SQL> UPDATE movies SET price = 250.00 WHERE title = 'gone with the wind';
  599. -- 1 row updated.
  600.  
  601.  
  602. -- 57. Deleting the record with invoice no. 'i08' from the invoice table
  603. SQL> DELETE FROM invoice WHERE inv_no = 'i08';
  604. -- 1 row deleted.
  605.  
  606.  
  607. -- 58. Deleting all the records having return date befor 10/07/93
  608. DELETE FROM invoice WHERE return_date < '10-JUL-93';
  609. -- 1 row deleted.
  610.  
  611.  
  612. -- 59. Changing area of cust_id 'a05' to 'vs'
  613. SQL> UPDATE custo SET area = 'vs' WHERE cust_id = 'a05';
  614. -- 1 row updated.
  615.  
  616.  
  617. -- 60. Changing the return date of invoice number 'i08' to 16/08/93
  618. SQL> UPDATE invoice SET return_date = '16-AUG-93' WHERE inv_no = 'i08';
  619. -- 1 row updated.
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement