Partial preview of the text
Download SQL Fundamentals – Student-Friendly & Exam-Oriented Notes and more Study notes Advanced Data Analysis in PDF only on Docsity!
me What dso Database? | ® NAatabare cs vam organised olbeation, ef data, dhat da stowed. managed. 2 ch » PEA peal is ot os ee 3 Dotabose = Coltestion of, data + Orgontend + DBMs) 2 ( oid + Sat none B * Typa of Databaser:- 2 Databases =) ; >» | | 2 Rulotionol. Dotahase Non-Rikdhional Database we (SAL Datahaa) (Nosal. Databorc) © falational Dataloss ® 1. Dolo Bored din nboles (sows amd, columns). 2. Cah dae Ras a. wmique oma cnt, com fhe Linked soother. 3. eats eee ia 2 4. Coctumple AL, Grane, Sal Sense ds > Beimne.ctne daa Non- Relational, Databases’- 1. Dolo J Moved din i doowmonts 3; a 6 laccbse format: (¢ 2 ey value pales, graphs or | 2. Ne [loud schema Mike tobe, 3: Works well sith Datge, wnstuulurad oF sumi- burstuned data. , @ +. Covarmples Mongo 0B ( Docume Based.) Radia (Key Vala) ; ; ‘ rf 5 Cassamdna (Cetummn fased! 5: Bak foe Soria Muda fig data, dist Wiha peedectana ( ) WoVVVoVFdvoo vase ssees eS tte ustth détabases . SAL da a. apedal Lomguage wsted, cto communicate Sk alps ws shore, siteloue ,wiplate and dlc, om. a, Rulstional.Ontaeee SQL vis powerful, stondosdind , Arosa , pottalele ond. eftetent it thu mast wid wused atalose, fomquage . mt SQL Commands - 4. DDL pana wenden ee ae * Gust > Genk o database or table * Ade Modify, eacisting Sable Dructire. * Dxop ~ Delite alee ox database "Tumarte > Qulcte al data. fom cine Jac up sSiuchana) ample | CREATE TABLE Students (RoWWNe. INT, Nome VARCHAR (SO) , Maxbs INT): | ALTER TABLE Students ADD Age INT; DROP TABLE Shuderits 5 > DNL (Data. Manipulation langunge) — Manibutstes Data, ‘Whee commands add, change ox somove data. + Inset > Add now date. + Delite + Removes data, Sxample :- INSERT INT Student VALUES (4, ‘Riya’, 8); UPDATE Studer SET Marks = = WHERE RethNe-= 2; DELETE Brom Sher WHERE Rolie = 15 a . 3. Updating Data. (Upset: Query) “N. Delating Data (Dele Quy . (nesting adetalsass | calle (POL qung) Tr ute comand. Jb ated delat ate datas aha you aint a a USE dotabose nome ; 1% Schama. = | A atluma is 0. shusboe] Muprint ofa databos, A difimus how ata. da orgamisnd. ~ dhe Sadler, columns, vAla-types, sulaionahips, | \Huss, Undooces, ee. ieee > da thoted.. (Lous avd. storage datas 2. logical schema. > eal el i 3. Views Sehemo = Hows atta. dn prasentad dy uses. (Arent. sattniched aoceas) ae i dk oo column (0% 0 columns) i ata le ad of Dein Sable hat und ad isnt Fs |. Unique - Ne cove sous om Kove dhe Some Valu. 2+ Net NULL - fang tour mutt auc a value (ne NULL empty allow) 3 Orly, ome primary. Ay. or fable (fit St com. be made Sle columns, Called 0. compesite Ruy) oa ee PRP PPD BBP PAHPO84F OF OHAIIIGADAADAAADRA ARDS @SIVVVL Shee mec <@ Aleut vis 0. column dim ore doable thet Lins ste dhe put Auy of fou Soattes undewstamding., Piarons, hey = Oxiginat ID Cand oratgn, fray. = A copy, eh tat TD cond feapst im amother place te onmect thu chwe. SQL Cocarmpee > CREATE TABLE Students ( Rol Ne INT PRIMARY Key, Name VARCHAR (0), Coss VARCHAR (lo) ry é VN nate CREATE TABLE Masks ( MaxRID INT PRIMARY KEY, DUVET OEVeEEEdcdddeOeEELCdA RoliNe INT, Subject VARCHAR (So) , Maske INT, FORETGN KEY (Roll Ne) REFERENCES Students (Rettne) ) 1 fin ae a 1. A dalle com rouse rubtints forcign eye. > forin fog (am aneept dupa valu, oe 8 Folin Koy com Awe roll vals, NM ensusen Reon Sogty ne you can’ dnt a, RaltNe- dn Rants le Ghat down ext tn shunt dbl. © Signed data, com io hos potion and wagon chi white unsigned. dat, : Cam store only positive twaluus ( including gore) 2 Decimal Dakatispes - 1, Decimal (m,4) { Vania’ (frst, (aud) ™> dotal, digits dig os sin 2. Uoat (p>8) {4 Lytert (Apprens) 3. Doulse (py) { 8 types (Nigh precnion) J : CREATE DATABASE Cotte D8 ; p USE Cotfers08 , CREATE TABLE Shuderte ( Student IO INT AVTO-INCREMENT PRIMARY KEY, NAME VARCHAR(lo0), Age TINYINT, (mail VARCHAR (loo), ToinDat. DATE )s SELECT * from Students, Wess s Ss BB BBS DB INSERT INTO Students (Nae, Age, Gmail , TeueDate) VALUES E pik? 62" 9 Onnsk gmail.com, “2025-02-22") (Adak, €04", © adik @ qwoit com’, *2524-01-22'); A\TER TABLE Sudesh — FALTER = fanae in abe} ADD COLUMN City VARCHAR(So) , ALTER TABLE Students MODIFY AGE SrnpQdinik 5 ALTER TABLE Students RENAME COLUMN Ernoil te Gadert Email ; DROP TABLE Students; (Diop » Comply deltas the stable TRUNCATE TABLE Students 5 {Deletes dhe dota of, the stnbte 4 | ) | r Frotine «Aber Tabet Deep Table Dunedte Tal. 5 le Bue ee eee be t ics ' | dalite tale, x red x . a : ed aah ji} | dulce ofL x y , | HOU > ee JI —— | eases 2a) ‘Reap Sobte INSERT INTO (human, Column 2, - --) ALVES ( yalurt, wolluer, ...) - dmp. foints = > Valu should te Jmrextod orden - usise. * 4h JOU. One dunseriing a Bhrin ho ude dmwoded. commas - 4 > Thow vis we tink de rst ID, valua, Act them ab aute— inerxoment. CREATE DATABASE IF NOT EXISTS ShepbdB; USE ShopdB;, CREATE TABLE Customers ( Customer TD INT AUTOLINCREMENT PRIMARY KEY 5 Name VARCHAR (loo), fmail VARCHAR (loo) UNTQUE, Addscss VARCHAR (200) Ds INSERT INTO Customers (Name, Umoil , Addsxese) VALUES (6 Aik’, © amik © emoil .tom’y “Delhi’) (Eneha? , neha Qemail. com’, §Bune”) SELECT * FROM Gusteruss , FRPP PRANPRHHLOHS FLENOVDVIFFIIISIDAIIIZ 2000000 CLI , * Rete, ing Data {* + To solock oll columns SELECT columma, column2 FROM table name 5 + SELECT Nome FROM Custemens * SELECT DISTINCT Addsess FROM Customers To xarmeue duplicates, SELECT column 1, column 2 FROM vtoble nome WHERE Condition ) ) ) ) ) ) ) ) J } } | Imp - AND > Both Suu | Ubes here, and , or , nat. imate: | : e NOT > Cacebude Jon, ones Fe *« ,* SELECT * FROM Customers WHERE Addoeas LIKE [Delhi]. 5 ) ) + SELECT * FROM Customers WHERE Nome = Rahul Sosa? AND Addsuss LIKE op MUMBAT. 7.” > * SELECT * FROM Customers WHERE Addsweas NOT (TKE ¢ “f-Mumbot'.? 5 SELECT column’, Column? FROM table nome ORDER BY Colummr_name ASC ; ‘SELECT * FROM Custorness ORDER BY Name ASC ; SELECT * FROM Custorsa ORDER BY Customu TD Desc ; UPDATE See SCT column 4 = valued , Column? = yaluer WHERE Condition 5 ) ) , ) =. 4 Undasing peeing. | ®. . “= a Gnbuping Doin . < SELECT Category, SUM (Amount) SELECT catnn-mamnts Sain ot > FROM Sales i able rare (nROUP BY Cahir rare = GROUP BY Category 9 > > * SELECT Catogor » Sum (Amount) FROM Soles —) 2 GiROUP BY Coleqory. 4 HAVING SUM (Amount) > looo 5 2+ Relationships omd Joins din MySQL > wm A sulstionship deinas ow dakle ove conusted 26 cask other swsing Bays. ° (0) One So Many. (b) Owe te One ©) Mammy, co "amy. FOTN ds used de Combine sous from, cise omer cabler Lared ono selotod column . (a) INNER JOIN SELECT A. column , B. colwnvn FROM TobA A TAWER Jo INNER JOIN TobhB BON A.cormmon—colwmm = 8. Common —columur 5 SELECT Customers. Custom Nome, Orders. Rroduct , Orders. Amount FROM Custormme, wm [INNER TOIN Onder. ON Customers, CustomerID = Orde. CustomerID 5 , \b) ett Toim SELECT A. column, 8. Column, FROM Toten A LEFT JOIN TablB @ ON A. Common. celwmm. = 8. common. colwmn } SELECT Customers, Curtorats. Naum 5 Orders. Product « Orders. Amount ) Exor Customers | LEFT JOIN Orders ON Customers. Customer TD = Orders. Custom IDS mp = Ratusums 0 wocords [row Ltt table and ‘wateRing xecorda om sight Jiable. CS) Right Join SELECT A, colwmm , B. Column FROM Taber A RIGHT JOIN Table & ON A. common —columm = B. common _celumn ' Timp’ Reliwens al, sucorda ory sight dinble amd ‘matahing. seconds Kom, Luft doble. (h) Euld Qiitese, Join (Left Tein Syston) UNION (Rigt Thin Syntone) “Tmp'- Revs aft xecords pom Loth the dables A CTE (Common. Table Expression) vs 0 damperasy named. xeautk set thet Ujou Com Jise Umside a SAL query. H us cated wing a WITH Clouse. Basie Spt = WITH cle-mome AS ( SQLECT column’ , COQwmm 2 FROM table name WHERE condition. ) SELECT * FROM ce-nome > Cocomphe > WITH Coleen Totals, AS ( Select Category , SUM( Amount) AS Total Sales FROM Soles GRovP BY Category. ) SELECT Category, . etal Sales Fkom Cohegery Totals WHERE TotalSales >looo ; # Multiple CTEs com Le vised together. SRP RPRPRFPRZARPHPAKLAEALAAA SEO ABO OCO SEIS © € SBS. S£.2. 2 2 o O & » © * 6 > >>> 7 a Saige bhi Ss eee » A VIEW dno wisdunl calste ereatod wising a SQl query. & dows met stow > ta pga, Losey SQ yond ign, dain. pon 5 Seas calles. ) : 5 comple) CREATE VIEW Catenin Sales AS lew name SELECT Category. 5 SUM (Amount) AS Total Sales FROM Soles GROUP By Category. , SELECT * FRom Contagion Satan WHERE “Total Sales >S00 5 p * VEewS do not stom omy data. D x We com jon VIEWS uth TABLES . HD dale ob updated , then view wilt be alse updated. . SGeeoe0cvwcwese 
