Assignment -3 the sgt —operctions «ind explain range searchin cist : c operattion. ‘bet’ cind pattern _meetching _aperatoy ike” eth example ________— oe Rina ae The. S@L__opereetions — include Select . insevt ~~ upiete 1 delete, create and drop “The _vange searching opevetio p between’ is _ used _to__vetvreve record 5 __ within a Specified — mange _For exam ple _,iF you went to _yetwve alt the employee S__ whose _3ularieS tye between ___ $sp000 aind _$ #0000, you can _use_the query -_ SEL tet * FROM employee S WHERE sclavy SETWEEN < | | soon6 AND Fog00 4 The pettern maching opevatoy ‘tix Loe 6 lsearch Pov aS specific pattern within a _gin'ng- Tk uses wild card _chavacters '/.' Gepre- ents” any number oF Cholvacteys) and '-' Crepre- 2 Sent s a single __Charcle+ers ) For example if you _wetnk +o. yetvive cll the employees —whees names stayk with ‘John! you can use —_ ——the _Fe lowing query i $$ ——. SELEcT_* From employees WHERE name Like __ —— Toh nypos. The deiete.. command .is..used 40 aciete SPEUFre OWS Trem. a todie bused. on Ba Specitled condition Tt allows Fox the MSe of WHERE clauge 40. determine which wews. +e delese For examples i yeu wemt 4o delete cat the veeoy ds = eh employers eho have lerd the compan, Yeu con ase the Petrewing ctuery | 3 DELETE ERomt employees WHERE status é Resigned The 4vuncade command ig used to delete alt the Aews From ceterbie Untixe the dete te Leemmand .it dees not use a where clause & _camer be Yetted buek .Fow example . iF you want +e Cleay the entive empleyees “+eble 1 YoU Cem -touse the Following tmery } TRUNCATE TABLE empleyecs, 93 Erpletn etine the 4erm i) comdidcste key LU) Patmary key i Beveign bey. b= Cemdidate beyx Is a _ertounn ev user | 8h ceoMourmns that com Uniquely identity _i -_xecerd in qtable Tt ig selected From aa, Le —~—-~-=f the set of candidcte Ney .€ach table can—_muttiple— candidate key s For example nae ——“ Poy cucu LE of tudents . both the stusent and emofl _etddyess___Cem be cemdidate Keys :- tt acoloumn ory o& set oF thats selected as the matin ‘identifier roy a +uble. It must be uniave each veeoyd in +he table and sannot values. Foy example, atuble of can be the primayvy key. __I have nell —Temptovees xd _ 7 _Foreign keys: at is a _coloumn oY _a set ae | coteoumn: In___ctebie thet veters +o +h e_prt- _meuey Key oF anothers table + it establishes La rvelotionship between +wo tubles . Foy examele [Lin catoble of orders, the custemey FD can | be Foreign key that veterences the primary b Lkey eb the customers +terble - create & ttey command with syntax & @:S | describe TT examote stadt CREATE TABLES i eoloumn |_ __mayks FLOAT. depavtments vamc4AR Cso) ve io dh ite armen E_toble- cat ia mone | ot datoty pe j ALTER TABLE toble -name DROP coloumnN Gloumn.name 5 ALTER TABLE table -name Morrey coloumn — Nume_ datatype, Oxam ple! - ALTER Tagie students avd cgpn ELONT, ! t ALTER TABLE students DROP coLouMN depuvimer ALTER TABLE Students ModzEY mayvics INTy | Q.€ | Write at command to creote table Student ; CRNO, name, marks, dept) tolth PYO Pe MH. wtcatype cimd RNO a Primary key é CREATE TABLE Student ¢ RO INT PRIOa~ARY keY Name VARCHAR C50): I marks iNT) dept VARCHAR cso) ytif censidey +he Following dotabdse employee Cemp-id, yam e emp. city, emp.cdddy .emp -deet, soln-dote ) Tat aisplay the emp-id oF employee twoho live th dry Pune ' ev ' Wagpuy’. |p. Change employee name Aayush’ to | Aayan’ _ display the total numbey of employee Whose dept _ts so Ans\: “SELECT _emp-id FROM employee WHERE emp-city __ IN C' Pune! tmagpuy)- b) 1 UPDATE employee SET emp-name = Aavan WHERE emP -nume 2’ Royush |: ie) SELECT Count C*) FROM employee WHERE emp-dept = SO} @-3 Considey the tructuye For hook - Mastey _Cbookid , bookname , authoy .No-oF copies ,price ) Write down s@i auerles for Following . OY. Write a command a create book mastery table, 6) Get authorwise st oF all books. OD display cH books hose price is between Soo 4) Display etl books with details whose name Stayt Boe — 1 books whose price is above ‘Foo. i isploy all bookS chose number of copies are 4 less shan 10.1 _Beokid 1 INT PRT many eV; a _Bookname varcHAR Civs) ——_____ Authoy VARCHAR Cioo)> ~~ _ Non oF copies INT, Pate secimeat C192) es yy b 2+ SELECT CLUE oy rbooknumMe FROM Boek _ ORPER 6Y wuthor: Mastery ——1___ ©. Dise SELECT ¥ EFRort Book _ fastev = WIIHERE book name ake 'Dy.'- 16. sttet * £Rort Sear Mastey WHERE book — Aaume LIKE ‘yy, l@. SELET * ERoM Book ~Mestey wHere i Pace >z00 | (E sevect * erom Book =Mdstey WHERE no” BE. copres ©) s€ceet sum csetary das total ~ Sulury From €m?P 3 \\ _¢) select ename FRoM EMP WHERE depino= 20 AND dept —locotion = mumbai ' I © S€LecT ename From EMP ORDER BYo Selayy AS® UimTT 2: a g10 | Con tdey _+he 4toble student ¢name, marks dent 4 : Lage, place, phone, bivthdute) write spl Query Foy Following. 4) TO tight sdudents having Place og ‘Pune’ ov t Talgaon".db) To ligt stadents having sume _depavtment Cdept) cg that of SRachanel’, d) To tight student name and marke From ‘computer’ depk . So) est Student name «he have marks less than +0 F) wo ist students che ave net Eyom “mumbaj t \ sevect *eRom student WHere place='Pune? OR plaee = ‘Talgaon!, 5) SELECT XERoM student wHERE dept= SELECT dept FROM student WHERE name =! Rochuna’ so bari Lan bi Na Laie | tas | aed QO VermmiTe student ser metyks = 96 WHE name = °Rehwy' do) SeLect name, mayYNS EROM student WHERE dept = ' computer’. 2) To ust studen) aume mavyks less than 40 - Sevecr name FROM Mayks 240; “no have1 a “gevect * Rom student WHERE place <> ‘Mumba’.