ea systemSQL KEYWORDS SQL KEYWORDS KEYWORDS: DESCRIPTION KEYWORDS: DESCRIPTION ea ew aun tan eetng le ese coin tems be, Example: Aino colina named Example: Rares thf, pare clan app tal paiess fate named wes! prop trom be users te ALTER TABLE users: ‘COLUMN ALTER TABLE users: pov ensi aes vies ROR COLUMN ea nae Wrst ae cneeiton an xing ete te ee cae orea spots ais bar DROP Exaile: Ones ctabate named DATABASE ‘weballesetip, Evaro: Ads now PRIMARY KEY ‘ADD constant nod se? on ers DROP DATABASE webstesenp constraint | toarasumvure ALTER TABLE wer Removes detours olin {ADD CONSTRAINT vee PRIMARY KEY Example Hty80L: Removes the dtat ,sueNaa ‘een ear shen he perer preauae abe Ps, dito dt coins a able ALTER TABLE produce Cconsvais na tabla, es par the above, ALTER COLUMN name OROP DEFAULT, Example: Adds a new boctean column Same ener aa aT murertasie | Aver tesa pron | Coane ernest soveoctsenan cornered Example 2: Deletes the approved columa from ie ‘eae abe Checks forthe existence of ny record ALTER TABLE deals win tha subqueneturring tue fone or [DROP COLUMN aperoved rete records ae ehmed Example: Lists any dealership wit a deal ‘Changes he data ype of atts column Finance peerage les an 10, zara intr users ttle, make the exists Tecra eat ALTER Colum incept date nia ate pe SELECT dealership COLUMN FROM deslenhios ALTER TABLE usare WHERE EXISTS (SELECT deal_name ALTER COLUM ioept_date datetime FROM deals WHERE dealership = jd ‘deals deslreip_id AND france, ‘Reus tue fhe subquery values percentage < 1) ‘mest passed candton }__|} pera dt Example: Reus he users wth higher ‘Spectien which abet atector delete data ‘numberof tasks than the user wath te trom highest number of tasks in be HR. ALL. Soren Example Selects data fom the users table ‘SELECT fel name, surname [Link]. FROM SELECT ares mansger FROM users FROM arce_managere WHERE taka no> ALL (SELECT tok WHERE EXISTS (SELECT Producwame FROM user WHERE deparinent d= 2), FROM Products WHERE area manager id suas pecaaer eianl aaaAND ANY as Asc BETWEEN sed oon separate conitons within a Example Rowe evorslecatodin {edon, Usted Kingdom SELECT “FROM aves [AND host cigs London: Ratums te any of the subquery values ‘eo tha gen conten Example Retuns produc om the products ale wich nave raced Sere = eore nthe odes ttle veh a quanty of more tan 5. SELECT name FROM products WHERE produea = ANY (SELECT podutd FROM orders WHERE Qty > ‘ulus whieh ony ents forthe curaon at tte usr. Example:Alasesnorh_eas_user_ sibrerptons car, SELECT nor eas use subscriptions ASre-sube FROM usere WHERE ne_ebe> 5: Example:Aopes,Barancs, Peaches, Selects values win the gven ange {elect tock a quatty betwoen 100 and 180, SELECT “FROM stock WHERE quanty BETWEEN 100 AND 150 ample 2Select stokowih a quantty ‘ot betwean 100 and 10 Atmativly, {sing eve OT Hoywor Tare revorees ne lege ond selects values side the given rage SELECT “FROM stock WHERE quantty NOT BETWEEN 100 2D #80 INSERT INTO Is NULL 1S NOT NULL LUKE, Not oR Used slongude a WHERE caute aba shorthand far mule OR conics. Soinseaaat SELECT FROM users WHERE county » USA’ OR county = eounty # Russa OR county « Austate’ Youcanute= SELECT FROM utors WHERE county IN (USA. Unto Wingo Russa usta) ‘Addn ros to atabl Example Ads new vice INSERT INTO care (ake, mode ‘ileage, yen VALUES (au, AQ, $0000, 2015) “Tests for enply (NULL) values. [Example Retuns users that havert given contol muta SELECT FROM utors WHERE contact numbers NUL: ‘Tha raver of NULL Toss for valu that arent orp NULL. Returns tu ifthe operand valve matches a pate Firstname ends wih son SELECT FROM users WHERE fot name LIKE siton Retums te da record DOESN'T neat he Example: Retuns re dhe users fitiname doesnt end hon SELECT “FROM users, WHERE fst name NOT LIKE sot sea wongaie viene to induces when athe condone tue, Example Retuna users ative ineiter ‘Shaed or Manchester, SELECT FROM users, WHERE ciy="Shefield' OR Manchester’CASE CHECK CREATE DATABASE Change aueryououtdepending on Example Rete users ond bole subseripions, along wit anew column ated acteny levels nat makes 0 |Mogement bsaed on ine number of subscriptions. SELECT frot_name, surname, subscriptions (CASE WHEN subecriobons > 10 THEN Nery active’ WHEN Quentty BETWEEN SAND 10 THEN Active" ELSE thauive! END AS acti levels FROM users: ‘Ada consln hati he vale whieh 29n be added to 9 column. Example 4(MySQL): Makes sur any users fadceda ie voe's ade are 18 oc over, (CREATE TABLE users firs oama verenart285), Example 21MySQL} Adds a check ater the dle nos ae09y Deen exeales, ALTER TABLE usere ALTER TABLE users Creates anew database Example t(MlySQL): Creat databace narod wobetovat CREATE DATABASE webstesowp: ORDER BY ROWNUM SELECT SELECT DISTINCT SELECT INTO Used 'o sort he result data in ascending (detaut) 0” descencing orcer tough te Use of ASC or DESC noywowds Example: Ruins counties i alphabetical SELEGT* FROM counties ORDER BY name: Retums resute where te row number resis ne passed conaiion Example : Returns the fp 10 counties from tha counties ie, SELECT* FROM counties WHERE ROWNUM C= 10, Used coc data from a databoss, which le en retuned ina results se. Example Selects ol columns trom a8 uses SELECT“ FROM users: Example 2: Select the frst name and surname colin from altura XX SELECT fname, sumame FROM vsers; ‘Sames a8 SELECT, except cups values re excluded Example : Cries a backup abo using dotatiom ihe user able, SELECT INTO usersBackup2020 FROM user Copies dais om one ate end inserts Into ancov. Example: Returns a esunvies trom te user table. removing ery dupes values (which would be igh Iai). SELECT DISTINCT county from userCREATE TABLE DEFAULT DELETE DESC (reales anew tte Example: Creates a now ttle called sersin the websitesetup databace CREATE TABLE ueors aim, frst nama varchar2ss), surname varena(255), address varenar25s), contact number iat x ‘Ses a dafaut value fora eoluran Example MySQL): Creates 9 new tabie caled Products which as nama column wir defaut value of Placeholder Name fed an avadabie_from column th 8 fetal value of foday's date CREATE TABLE products iain ame varchar(285) DEFAULT Piacsholder avalabie rom date DEFAULT GETOATEQ x Example 2{MySQL): The same as above, butedtng an existing tbe ALTER TABLE products ALTER name SET DEFAULT Placeholder Name! ALTER avalable_fom SET DEFAULT GETDATED: Delete data rom atabe Example: Removes a user wit a user orera DELETE FROM users WHERE user = 674 Used with ORDER BY to retu the data in descending ede. Example: Resilsh, Peaches, Bananas. les. SELECT TOP ser SOME ToP TRUNGATE TABLE UNION UNION ALL. ‘records to eure fom table, Example: Retums the top 3 ars-forn the SELECT TOP 3" FROM cars Used alongside UPDATE to update existing datainatable Example: Updatas the value and quantity values for an order wih an got 622 inthe orders tae UPDATE orders SET value # 19.49, uanity «2 WHERE id= 042; dena! ANY Used alongside SELECT o rotum a st number ef records rom a table. Example: Retums the top § users tom the ‘ere fable SELECT TOP 5* FROM users: ‘Simin’ to ORR, but instead of deieing the {able and da da, i doleten only ne daa Example: Emptes the sessions table, but eaves Ine table ase ntact TRUNCATE TASLE sessions Combines the results fom 2 of more SELECT siatements and retums only dsinel vaiues, Example: Returns the cites ram he events and subscbers lables, SELECT cty FROM avents UNION SELECT cy trom subscribers: ‘Tho [Link] UNION but includes duptcato valvesUNIQUE UPDATE VALUES WHERE ‘This constraint engures all values in ‘column are unique. Example 1 (MySQL) Adds a unique ‘constraint tthe id column when craating a new users table, CREATE TABLE users id imt NOT NULL, ‘name varchar(255) NOT NULL. ‘UNIQUE (id) i Example 2 (MySQL): Attars an existing ‘column fo add a UNIQUE constraint ALTER TABLE users ADD UNIQUE (ich, Updates existing data in a table, Example: Updates the mileage and service ‘Due values fora vehicle with an ‘UPDATE ears ‘Used alongside the INSERT INTO keyword to add new welues fo a table, Example: Adds a new car to the-cars table. SET mieage = 23800, senviceDue = 8 INSERT INTO cars (name, madel, ye8r) VALUES (Fort. ‘Fiesta’, 2010); Filters results to only include data which meets the given ecndition, Example: Retums orders with 3 quantity ‘of mote than 1 fom SELECT* FROM anders WHERE quantity > 4COMMENTS Bean eons ee ker eS ‘Sng ine cemnetsit wi Ary tentfire 2 charac © Mutne comers stat wit ant endwih7. Tey sec oss Be encotthe Ine wilbe nod ‘multe Ine nt re dog eharacrs neve been foun SELECT “FROM weer Ts sy sleet quan Iegats ans cea temine wie ane ‘ss ahr slat vey. eh ant want xen yet SSELECT® ROMs Senne (CREATE TABLE wer hot name varchar) * Bop anaes Reta Gn cas DATATYPE DESCRIPTION DATATYPE DESCRIPTION Fx ung sting hie can conan italic tse wih defo of. The Teter number ard sod choca tac mba fs avails Sete CHARISZE) | Trestasarret ets the nano ores {be oe parameter whieh con ho valves sing nga torn 2E5 wih laut font ies an ‘Aver sal meger wha sige ange oF ‘arisen stig enter AR. aoteta,ances unagned ge of vanciangeizey | site anda mc mama | eaiaisiaaeants reciaronaeem wart [A Bmarvisize) | Sheps tO oe BOOLEAN | Sime as8001 VARCHAR(SIZE) | fiygy "ONROMEson en ive Serra ater ee e an ssMaLLntsin) | lena tombe vate san Lage cect 108 wh Sate Sans ay em ‘amaxlength of 255 bytes. wt Vlas ago naan a 8 sed at rwvrexr | Seen he aS ‘ssacinettarr aa tases tenon MEDIUMINT(sizo] | ‘tos ton ot ferret oe Motes mech ene size) | Grancer ects te marr sowes {dd Een aiaeTEXT(size) BLOB(size) Holds sting with a maximur lang of 655535 bytes. Again, beter to use, \VARCHARO Holds Binary Large Objects (BLOBS) with INT(sizo} Ssmaxiengh of 65535 byes MEDIUMBLOB. LONGTEXT LONGBLOB ENUM(a, b, ©, ate...) SET \TE/TIME DATA TYPES DATATYPE DATE DATETIME(tsp) Holds Gary Large Objects (BLOBS) with sa maxlengih of 16,777,215 bytes INTEGER(etze) Holds a string with a maximum longi of 4.294.967 295 charactors Holds Binary Large Objects (BLOBS) with BIGINT(size) ‘Ss maslanghh 4 204,967.285 bytes ‘Airing objact hat only has one value, ‘which s chosen from alist of values which youdefin, uss 8 waxinuun oF S535 (alues, Ia value ts added which lent on ‘His ists replaced wah a blank value instead, Think of ENUM being sar to HTL radio boxes in this regard FLOATIP) CREATE TABLE tehirs (color ENUM ‘est ‘green, ‘ue, yetow purgle), ‘A hing abject hatcan have 0 or more values, which le chosen fom alist ot values which you define, up toa assirtarh af Gt values. Thatk of SET being sniarta HTIAL checktoxes inthis regard DECIMAL(size, d) DESCRIPTION A pimple date in YYYY-AUM-00 format wit a supported range from" 1900001-0" ta ‘Dopa.t2.34 DECIsze, d) date ime in YYYYARN-0D hh rmeEs fermat with @ supgorted range from 2359:50 By acing DE FAULT and ON UPOATE to tm column deinen, aamsaily sete tothe current detahime. display width, which is 255. ‘A madiue integer van a signed range ot “2147462648 102147489647, and an Unsigned range from 9 to 4204957285, Here, the sie parameter specties ne maximum alowed dsplay with, which s 266. Samer INT, ‘Amadium integer wah a signed rangect 92233 7203686477 5908 10 9223072036854775807. and an unsigned rangefion! 0 1 18486744073700551815 Here, the eiza parameter apocdias ne ‘maximum alowed display wisth. which ie, ‘Aflosting point number value. ths precision (p) parameter is between 010.24. then the dala type is set fo FLOAT), whist its fram 25 to Saha date type at to DOUBLE). This behaviours tomake tha storage of values mare efcsent ‘Aflosting point number value where the total digs are set by ne size parameter, and the umber of digi aftr the decimal point is setby thed parameter ‘an exact Fixe point number where the total umber of cigs et by the size parameters. and ha ola numbar of igs ‘er the dacimal points sat by tha d parameter. For sze, te maximum numbers 88 and the dotaul is 10, whist forthe mavamum unibeeia 30 and the dala 20, Same 98 DECIMALTIMESTANP(fs9) [Atm hnmess format, wth 2 TINE(fsp) AUroc Timestamp whichis avaiue flatveto the nureer seconds sce $e Una epon (1970-01 co 00:0" Ufo) This has a tppored ange fom 1970-01-01 0.0004 UTC t203891-08 oxtear ure 2y aaang OEFAULT (CURRENT. TIMESTAMP and ON Column dfn. tautimatally et Stpporea range Hom 085059 Ayear wih a supponesrang of 101 Ete llArbitrage OPERATORS ARITHMETIC OPERATORS. aegis) OPERATOR: DESCRIPTION OPERATOR: DESCRIPTION if lowe = ttS*~™ OPERATOR: DESCRIPTION OPERATOR: DESCRIPTION = ‘Subtract equals:< Less thar = (Modulo equats > eterna vee aD ent = {csr oro = Bove ecu ie ° Notequtto r= Bevis OR eas SkillArbitrage FUNCTIONS STRING FUNCTIONS NUMERIC FUNCTIONS: DATATYPE DESCRIPTION DATA TYPE DESCRIPTION aia Rema aquense vat tre as eur sei ns eprn CHAR_LENGTH | Retuns the character anghof a sting ACOs ‘Retums the [Link] ofthe given number. ‘CHARACTER ———J LENGTH Sen mG ever ASIN Returns tne are sine of the given number, ConcaT | Aaeernasnors pera amnum = Rete ear lnget of enor hee GONCAT_WS | ZopaStortemceneaale ATAN Retum thear tangent of 2 given numbors Rete an neva tie oe Reta vrage aun ftom FIELD positon of value within 9 list of values. —_ expression Wes passed a une rete at numer ets dost wc ute (nee rorMat | lant aco conn oy 500305) oan. “wats agen dena port muro ‘tous you's intone ag ine ances a INSERT Sting fr tan manta CEILING se cos Reten ne othe fe given uber =“ Seseopein nie coT -Retums the colengen' of 8 given number Lease ‘Comer ating lowercase ‘Returns tne amcunto eco that are ‘COUNT ‘ratumed by a SELECT quary.uert suoo' cheats ron a ngand DEGREES —_| comets dans va odegees LENGTH -Retuns the length ofa siring, but is bytes, ow, see ceeara, | Sesianeurcsconraaneang BP et to be pve f he gee ua Furth done wt rumba LOWER: ‘Same as LCASE, FLOOR downwards om a given decima poiet Lan Let gator sting winanater 2 ‘pester nearest | Reumnennensaielnatte urea Rove ny ei sic for fe en LEAST armenia a0 Eten sig to roe, rig iemany ston ~ 7 feu at orinnct ne geen PosiTion | Ren eee tine ee —_ ‘Returns the natural loganthm of he given Los anoo ortelogrin the greater REPEAT | Alowsyoutoreposta srg ‘Sie ceennese ‘tows you aca sce ‘pesone a 103,bulobe 10 ence rang uaraaergun Teer Loc1o Dees: L0G, bt oa iba, Los Does sane 105 bot REVERSE —_| Rewrresne song. feu eight ave for eset Storing fram the right, extract the given MAX vaues. RIGHT Sumo chaaces am wagon Sahnes -Retums the lowest value from set igh pos one srg win anne 08 RAD Sean sem bo-onandoarve curs heroine shen nba in ae divided by the otter given eumber. coves ay alg epee mine gen rei Ss a ete eton atin io aceneqe to he ‘SPACE amount you pass it Pow ‘Returns the value ofthe given number Teoaattepver tine chor wen, nme sTROMP | conperer2 tvgsordtirmese Extracts ene subsirng from another, starting ROWER Somes Pow SuBSTR | fomanygouton RADIANS — | conversa cages i trator SUBSTRING | Sarwassueste WAND Taunus‘suaSTRING_ INDEX usAse UPPER euns asubermgtton a srg torent s10N sed stg furdthe ur ‘SiN fevoustaiingaudleng saces ton SORT Tran hvRiMopee sum conven aig upon TAN TRUNCATE elu ese ne gen unter eure neve tthe ovenset yas med tune tenga af nga nue NUMERIC FUNCTIONS NUMERIC FUNCTIONS DATA TYPE DESCRIPTION DATA TYPE. DESCRIPTION hata rating 109 " rai cena ADDDATE ‘(eg 20/01/20) ana ratare me resut eg a aneny | rameenarvaneanaraia ‘als al oneamm want aoorme renege waar | emmaon apres amma |eanomaam coatesce | Feast ton mtn CURRENT_DATE — [Link] CURDATE CONNECTION |D fers carerscowmcion tne CURRENT_ Get toe exeruen cate ar Conrart ne gen number from ane rumeric: TIMESTAMP — ‘bate eveiem inte ancther curtis | sewenscumter ru cowent | Sotneaue grote eae a cunrent_usen | Srotssiearcaa Dacor | eagermineaeasbaaea sean DATABASE | ouneranectts suet Sian Sane ss A600ATE Used songsise oegegntefunetons Serer ee eaten mamgcreat | rnernansee enna ox ano oy | Ene eran vn mate oe are sue paairatemeenens ar colonia pay pairs bey bra concur oy ate. Co | eeerecmean somes ponennishieienenetanenTReturns the index forthe wake forthe HAVING more han 3 active orders pavorweex | etm SELECT COUNT ave ses FROM uses Davorvean | ire tty ae surtrnghn Sebi orecusros avne COUNTtus@ > xc te eal ra gn pa 0 EXTRACT MONTH for 26/01/2001). ie cndion sea avai F Corsa un anabaraee pRompars | Sein badeton tou mac ae iNULL Une erin vl tun tems HOUR Return the hour from the given date, return, Fer betas was aed or LAST DAY Gate ley oft meth forth gin LASTLINSERTID | tpiotesina tate return tne auto LocaLrme | setsnecurenttosal ete nde T Conearene2avn oaesions ey NULUF eeu Meera cet LocaLTimestaup | sone [Link] fehommoonte warned waxenwre | Deca cane SESSON USER | enema sn MIGROSECOND | Sstinasemeessons ragnen ner SYSTEM_USER | samesssesson user amare fers ernie Be gen er VERSION —_| Retire ne cre veo ote MySOL MonTH etre te mono te gh te, WILDCARDS MONTHNAME | Est ne ran teen el eon AE DESCRIETION nut tozeroor mere hate now Same as LOCALTIME. ‘Example 4: Find all users with surnames brain eon PERIOD_ADD cane ane EE Ser SELECT * FROM users, % HERE sane UE ioe PERIOD DIFF | Seuns Necreeze teen en Lgl Flos anes QUARTER | noua ne yearqumrtarinegndate ‘WHERE cy Le cee Rear he conta hen tine of Ss aiaaiaia ssteime ‘Equates toany singe character, Example Fa atuses tng ces SE¢_TO_TIME | seunsctine based on tne gn sean, Era ih oy 3c, wey SELECT *FROW usoPERIOD_DIFF | Reture me citwensa baueen 2 gen amp 2: Fin all were ih aan moe somite EET FrOM wen quarter | rem meyeraureriorte gente vrtnvcn une eave om, datetime Equates to ary single character, nepal ce SEC_TO_TME | reunsoimereeionne henseaesn . ieee E1ECT*FroM wen STRTOLDATE | Sensi ana fora WHERE ty Leese SUBOATE Saiiens OATELAUB, ‘Equates to ary single character nthe list. ‘Subacta a ue interval fg: 02.9) 8 Example t: Find all users with fret rames suetime pons SE fa Sogn ws ori sxucT Proton Feta SYSDATE | sanesstocarme oro | Emmi re ee enticneiertons errs wna IP TiMe_FORMAT — | rumen an nein an fomat nrepes ecto wtnene Time_T0_SEC | conans an mums ane sess ena ERE tare LE eb mmepier | Sega mens peer 2 phen Tears fa ane al on srvesrame | Een ’ TODAYS ‘passed fram ‘06-00- D000'lo he gven date, WEEK| Rum teva be tragic vaca | Remeron ences WEEKOFYEAR | Feu be wet unter grease year Far feof be a, ae | venditaBSS they: 9 KEYS FOREIGN KEY (CREATE TABLE eve ( Id Canaiaate Key int first_name varchar A: ‘ADD FOREIGN KEY ater.) REFERENCES ues, Rol ae) PRODUCTS Id CancidatoKey int Aeseripton text stock_count int rice fost eu)Pag EXAMPLE 1 (MYSQL) EXAMPLE 2 (MYSQL) (CREATE TABLE wa ( ATER TABLE users lee NOT NULL AUTO INCREMENT, |ABD PRMARY KEY itr) tea same varenar2s5, Ins name areha2$8) NOT NULL enaivarctar255, PRIMARY HE ce) x BSS they: 9 Perera} VIEWS DATA TYPE DESCRIPTION CREATING VIEWS: Cusine otenin AM exEE pe 8 pose nencem ce See canis) | eeenoanenenerene : sae foes cee CCREATEINDEX ix tst ‘SELECT* FROM Ioerty_usen) create. eel REPLACING VIEWS es COneATE UntQUEINOEX met ‘Guor ne eontrane (CREATE OR REPLACE VIEWIpnoty_ ver} AS MERE couny = Untied Kegon OR ounty-"USA Crostes annex rare onthe oo teenie ec tee INDEX DELETING VIEWS ATER TABLE users [DROP INDEX ets ‘onOF view prony sesIS pores | we eo fi ) SS y » \ 1 ‘be anon — | 1049. Kio tovene-. | Labo. ~§- === ~() a Zh = 4 Chetson ‘Simoven, ‘1S, Seam Doningo... Cecksone0e... | ete eee | . lili a ¢ ¥ 7] meres | semen | sz sim nenoan, | mereaaorn@, Geet | thors | smn | 242, uany donee Saten| miners. ; jy) Se = = z 10, ‘Byory, ‘Erown ‘165, FrstAve Usa bycey8754@- ORDERS DCTS id user_id product_id agent_logged id fama doscription | stock_count prico iam pam, ete a i is ee ae = 2S Ss Na a a er ee 2 a Ee a a a TE = wo 1 10 ' so | sues. | cote a0 BSS they: 9PROUDCTS ‘SELECT orders id, users first_name, users surname, products. name as = SSS wae | coo. | vty ° a8 INNER JON sens onerdesuser id= userid = a ae 1 | 300 INNER JOIN products on orders. product_id = products. a | ane. | f ast 5s eS es eon issu esse a ™ era . 49 id | firstnamo| sumamo | product namo ASS 5) Seas [tear | ere fai || ease, | toa z a Fae || see cal ee es ope 2 ia ieies_ | _ Oa ‘© i Seo oe sw | sacs. | cote | am 2 | ue are cor. r= = 7 | ute | te sevens | rom | cae a | cw | tae cen 5 eal cs ae BSS they: 9