PostgreSQL Tutorial for Beginners

Using SQL to Analyze Transactions and Customer Spending

To analyze transactions and customer spending, we first need to create a table that stores transaction data. This table should include columns for customer ID, rental ID, amount spent, and any other relevant information.

Once we have created our table, we can use various SQL functions and operators to extract useful insights from the data. For example, we can use the COUNT function to count the number of transactions made by each customer. However, in order to get a total spend amount for each customer, we need to use the AS operator.

The AS operator is used to create an alias for a column or expression in our query. In this case, we want to rename the "amount" column to something more meaningful, such as "total spent". To do this, we can use the AS keyword followed by the new name of the column.

For example, let's say that we have a table called "transactions" with columns for customer ID, rental ID, and amount spent. We want to see how much each customer spends on average. To do this, we can use the SUM function to calculate the total spend amount for each customer.

We can then group our results by customer ID using the GROUP BY clause. This will allow us to see the total spend amount for each customer.

To further analyze our data, we can use various operators and functions such as the COUNT function to count the number of transactions made by each customer, the SUM function to calculate the total spend amount, and the GROUP BY clause to group our results by customer ID.

One important thing to note is that the AS operator gets executed at the end of a query, meaning we cannot use it inside another statement. This means that if we want to use the AS operator with other functions such as SUM or COUNT, we need to use the having clause instead.

For example, let's say that we want to see how much each customer spends on rentals that cost more than $100. We can use the SUM function followed by the HAVING clause to filter our results and get only the rows where the total spent amount is greater than 100.

We can then rename the "total spent" column to something more meaningful, such as "amount", using the AS operator.

Using the correct functions and operators in SQL requires practice and experience. In this article, we have discussed some common functions and operators used in SQL queries, including COUNT, SUM, GROUP BY, and the AS operator.

Let's take a look at an example query that uses these functions and operators to analyze transactions and customer spending:

Delete thispart and Let's Select the customerID gna sum the amount to see how muchID gna sum the amount to see how muchID gna sum the amount to see how mucheach customerspendspendspendamount and grou buycustomerID this we see that each see the totalID this we see that each see the totalID this we see that each see the totalamount which which each customer spendamount which which each customer spendamount which which each customer spendthe customer 184 spend 80 but We don'tthe customer 184 spend 80 but We don'tthe customer 184 spend 80 but We don'tsee specific What This is because This issee specific What This is because This issee specific What This is because This issimply called su so Let's rename itsimply called su so Let's rename itsimply called su so Let's rename itand to use the as operator I'm as andand to use the as operator I'm as andand to use the as operator I'm as andtotal spend to more specific What thattotal spend to more specific What thattotal spend to more specific What thatis have total SP here speci is total SPis have total SP here speci is total SPis have total SP here speci is total SPtotal amount spent by each c here Nowcan use the having herehaving and typehaving and typehaving and typehere amount greater thanhere amount greater thanhere amount greater than100100100amer than100 greater than 100 and One important100 greater than 100 and One important100 greater than 100 and One importantthing to No is that as operator Getsthing to No is that as operator Getsthing to No is that as operator Getsexecuted at the end of a query meaningexecuted at the end of a query meaningexecuted at the end of a query meaningthat you cannot use the Alas the inside thethat you cannot use the Alas the inside thethat you cannot use the Alas the inside thewe statement so if this is calledwe statement so if this is calledwe statement so if this is calledtotal spent Ok so if Let's say that Itotal spent Ok so if Let's say that Itotal spent Ok so if Let's say that Iputhere al so let say that I put herehere al so let say that I put herehere al so let say that I put hereinstead of some amount I'm gonna putinstead of some amount I'm gonna putinstead of some amount I'm gonna puttostudent to see how much each student spends on average. We can use the SUM function followed by the HAVING clause to filter our results and get only the rows where the total spent amount is greater than 100.

Using the correct functions and operators in SQL requires practice and experience. In this article, we have discussed some common functions and operators used in SQL queries, including COUNT, SUM, GROUP BY, and the AS operator.

"WEBVTTKind: captionsLanguage: enWelcome to this comprehensive videoWelcome to this comprehensive videoWelcome to this comprehensive videocourse about postgresql One of thecourse about postgresql One of thecourse about postgresql One of theworld's most advanced and robust Openworld's most advanced and robust Openworld's most advanced and robust Opensource relational database Systemssource relational database Systemssource relational database Systemswhether You're a beginner looking to setwhether You're a beginner looking to setwhether You're a beginner looking to setup your first database or a seasonup your first database or a seasonup your first database or a seasonDeveloper aiming to scale and optimizeDeveloper aiming to scale and optimizeDeveloper aiming to scale and optimizecomplex data structures this course hascomplex data structures this course hascomplex data structures this course hassomething valuable for you Dive in assomething valuable for you Dive in assomething valuable for you Dive in asAlex unpack the essentials advancedAlex unpack the essentials advancedAlex unpack the essentials advancedfeatures and best practices to harnessfeatures and best practices to harnessfeatures and best practices to harnessthe full Power of post resq in yourthe full Power of post resq in yourthe full Power of post resq in yourapplicationsapplicationsapplicationsWelcome to the exciting world ofWelcome to the exciting world ofWelcome to the exciting world ofpostquel if you like me and you're eagerpostquel if you like me and you're eagerpostquel if you like me and you're eagerto unlock The power of One of the mostto unlock The power of One of the mostto unlock The power of One of the mostrobust and versatile relational databaserobust and versatile relational databaserobust and versatile relational databasemanagement Systems in existence you'vemanagement Systems in existence you'vemanagement Systems in existence you'veCome to the right place in today's dataCome to the right place in today's dataCome to the right place in today's datadriven landscape post gr stands As Adriven landscape post gr stands As Adriven landscape post gr stands As Apillar of real ability scale ability andpillar of real ability scale ability andpillar of real ability scale ability andextens ability whether You're a seisextens ability whether You're a seisextens ability whether You're a seisdatabase Professional looking to enhancedatabase Professional looking to enhancedatabase Professional looking to enhanceyour skills or like me a newcomer intriguedyour skills or like me a newcomer intriguedyour skills or like me a newcomer intriguedby the possibilities of managing andby the possibilities of managing andby the possibilities of managing andcing data efficiently this course is ourcing data efficiently this course is ourcing data efficiently this course is ourgateway to mastering poic over thegateway to mastering poic over thegateway to mastering poic over thecourse of our program we embark on acourse of our program we embark on acourse of our program we embark on acomprehensive journey through thecomprehensive journey through thecomprehensive journey through thefundamentals and advanced aspects of postfundamentals and advanced aspects of postfundamentals and advanced aspects of postgresul from setting up our firstgresul from setting up our firstgresul from setting up our firstdatabase to optimizing complex queriesdatabase to optimizing complex queriesdatabase to optimizing complex querieswill equip ourselves with the knowledgewill equip ourselves with the knowledgewill equip ourselves with the knowledgeand practical skill Needed to work withand practical skill Needed to work withand practical skill Needed to work withdataeffectively i am dedicated to Guide youeffectively i am dedicated to Guide youeffectively i am dedicated to Guide youevery Step of the way ensuring that weevery Step of the way ensuring that weevery Step of the way ensuring that wenot only grasp the Core concepts but alsonot only grasp the Core concepts but alsonot only grasp the Core concepts but alsogain hands on experience to practicalgain hands on experience to practicalgain hands on experience to practicalexercises and real scenarios By The Endexercises and real scenarios By The Endexercises and real scenarios By The EndOf this course you be proficient inOf this course you be proficient inOf this course you be proficient indesigning databases leverag pog Sdesigning databases leverag pog Sdesigning databases leverag pog SAdvance features and solving real WorldAdvance features and solving real WorldAdvance features and solving real Worldchallenges whether you pursue a career in datachallenges whether you pursue a career in datachallenges whether you pursue a career in datamanagement web development or simplymanagement web development or simplymanagement web development or simplylooking to Boost our database expertiselooking to Boost our database expertiselooking to Boost our database expertisepog is a must know Technology so join uspog is a must know Technology so join uspog is a must know Technology so join uson this educational journey and Let'son this educational journey and Let'son this educational journey and Let'sexplore the Dynamic World of post equexplore the Dynamic World of post equexplore the Dynamic World of post equtogether Get ready to un the potentialtogether Get ready to un the potentialtogether Get ready to un the potentialof your data and Open Doors to array ofof your data and Open Doors to array ofof your data and Open Doors to array ofcareer opportunitiescareer opportunitiescareer opportunitieshello everyone Welcome to thehello everyone Welcome to thehello everyone Welcome to theinstallation and setup lecture In thisinstallation and setup lecture In thisinstallation and setup lecture In thisvideo We are going to install postgresvideo We are going to install postgresvideo We are going to install postgressql and PG admin Now You May besql and PG admin Now You May besql and PG admin Now You May bewondering What are those Let's startwondering What are those Let's startwondering What are those Let's startwith pog sel also know as postgres Whichwith pog sel also know as postgres Whichwith pog sel also know as postgres Whichis an Open source relational databaseis an Open source relational databaseis an Open source relational databasemanagement System rdbms that is knownmanagement System rdbms that is knownmanagement System rdbms that is knownfor its robust readability and advancedfor its robust readability and advancedfor its robust readability and advancedfeatures it provides a powerful andfeatures it provides a powerful andfeatures it provides a powerful andscalable platform for managing largescalable platform for managing largescalable platform for managing largevolumes of structured data you can make mistakesvolumes of structured data you can make mistakesvolumes of structured data you can make mistakessupport Wide Range of data types indexingsupport Wide Range of data types indexingsupport Wide Range of data types indexingoptions and quer optimization Techniquesoptions and quer optimization Techniquesoptions and quer optimization Techniquesmaking it suitable for variousmaking it suitable for variousmaking it suitable for variousapplication it offersapplication it offersapplication it offersacad atomy consistency isolation andacad atomy consistency isolation andacad atomy consistency isolation anddurability compliance and supportsdurability compliance and supportsdurability compliance and supportstransaction which ensure data integritytransaction which ensure data integritytransaction which ensure data integrityand readability with its extensibility andand readability with its extensibility andand readability with its extensibility andsupport for various programmingsupport for various programmingsupport for various programminglanguages pog is widely used inlanguages pog is widely used inlanguages pog is widely used inEnterprise environments and byEnterprise environments and byEnterprise environments and bydevelopers for Building High performancedevelopers for Building High performancedevelopers for Building High performancedata driven applications nowdata driven applications nowdata driven applications nowWhat is PG admin PG admin is a free OpenWhat is PG admin PG admin is a free OpenWhat is PG admin PG admin is a free Opensource administration and developmentsource administration and developmentsource administration and developmentplatform for managing post grleplatform for managing post grleplatform for managing post grledatabases it provides a graphical usingdatabases it provides a graphical usingdatabases it provides a graphical usinginterface that allows user to interactinterface that allows user to interactinterface that allows user to interactwith the database and perform variouswith the database and perform variouswith the database and perform variousAdministrative task PG admin offers aAdministrative task PG admin offers aAdministrative task PG admin offers aRange of features including databaseRange of features including databaseRange of features including databaseobject Management such as creatingobject Management such as creatingobject Management such as creatingtables views and indexes cing andtables views and indexes cing andtables views and indexes cing andediting data monitoring databaseediting data monitoring databaseediting data monitoring databaseactivity and managing server settings itactivity and managing server settings itactivity and managing server settings itprovides user Friendly environment forprovides user Friendly environment forprovides user Friendly environment fordatabase administration and developersdatabase administration and developersdatabase administration and developersto efficiently work with pog databasesto efficiently work with pog databasesto efficiently work with pog databasesallowing them to visually designallowing them to visually designallowing them to visually designdatabase scheme Write an sql queries anddatabase scheme Write an sql queries anddatabase scheme Write an sql queries andmonitor databasemonitor databasemonitor databaseperformers PG admin is available forperformers PG admin is available forperformers PG admin is available formultiple operating Systems and is widelymultiple operating Systems and is widelymultiple operating Systems and is widelyused As A primary tool for post gradeused As A primary tool for post gradeused As A primary tool for post gradedatabasedatabasedatabaseadministration a shorter definition foradministration a shorter definition foradministration a shorter definition forwhat pog sql and PG admin is is that pogwhat pog sql and PG admin is is that pogwhat pog sql and PG admin is is that pogsql is the Engine that source data andsql is the Engine that source data andsql is the Engine that source data andqueries and returns information and thequeries and returns information and thequeries and returns information and thePG admin is graphical user interface forPG admin is graphical user interface forPG admin is graphical user interface forconnecting with postconnecting with postconnecting with postgr note please make sure to follow eachgr note please make sure to follow eachgr note please make sure to follow eachStep in Order carefully common confusionStep in Order carefully common confusionStep in Order carefully common confusionpoints do not open the DVD rental datarpoints do not open the DVD rental datarpoints do not open the DVD rental datarfile directly And also another veryfile directly And also another veryfile directly And also another veryimportant thing is to not forget the pogimportant thing is to not forget the pogimportant thing is to not forget the pogsql password so This is very importantsql password so This is very importantsql password so This is very importantto have into have into have inMind and to Help avoid Any Miss steps iMind and to Help avoid Any Miss steps iMind and to Help avoid Any Miss steps inumbered them and I will Present them innumbered them and I will Present them innumbered them and I will Present them inOrder in the correct Order for you soOrder in the correct Order for you soOrder in the correct Order for you soLet's Get started Step one Let'sLet's Get started Step one Let'sLet's Get started Step one Let'sdownload and install post gr sequel sodownload and install post gr sequel sodownload and install post gr sequel soOpen a browser and type postgres sleOpen a browser and type postgres sleOpen a browser and type postgres slepresenter and we need to go to this pagepresenter and we need to go to this pagepresenter and we need to go to this pagepostgresql dorg Click on thispostgresql dorg Click on thispostgresql dorg Click on thislink and this page May change from timelink and this page May change from timelink and this page May change from timeto time but important thing here is toto time but important thing here is toto time but important thing here is togo to the downloading pagego to the downloading pagego to the downloading pagecan to the downloading pck on thiscan to the downloading pck on thiscan to the downloading pck on thisbutton clicking up here on download I'mbutton clicking up here on download I'mbutton clicking up here on download I'mtock up here ontock up here ontock up here ondownload and here we have differentdownload and here we have differentdownload and here we have differentversions for different operating Systemsversions for different operating Systemsversions for different operating Systemslike Linux Mac and Windows I havelike Linux Mac and Windows I havelike Linux Mac and Windows I haveWindows so I'm going to Select WindowsWindows so I'm going to Select WindowsWindows so I'm going to Select Windowsforforforme and down here we have pog sqlme and down here we have pog sqlme and down here we have pog sqlversions and we have the versions for 64versions and we have the versions for 64versions and we have the versions for 64bit of Windows and as you can see downbit of Windows and as you can see downbit of Windows and as you can see downhere there is also version for a bithere there is also version for a bithere there is also version for a bitversion of Windows so if you have a 32 bitversion of Windows so if you have a 32 bitversion of Windows so if you have a 32 bitversion of Windows you need to downloadversion of Windows you need to downloadversion of Windows you need to downloadthis version that you see here right nowthis version that you see here right nowthis version that you see here right nowGo up and cck download TheGo up and cck download TheGo up and cck download Theinstaller and I will go on the rightinstaller and I will go on the rightinstaller and I will go on the righthand side here down here Where it sayshand side here down here Where it sayshand side here down here Where it saysWindowsWindowsWindowsx64 Click on thisx64 Click on thisx64 Click on thisbutton and now going to be downloadingbutton and now going to be downloadingbutton and now going to be downloadingas you can see down here and if you haveas you can see down here and if you haveas you can see down here and if you havea different browser you see this in aa different browser you see this in aa different browser you see this in adifferent placeal right Once the downloading is finishal right Once the downloading is finishal right Once the downloading is finishJust Open this file G Open thisfile Let's Close the browser becausefile Let's Close the browser becausefile Let's Close the browser becausedon't need the browseranymore If the file didn't Open for foranymore If the file didn't Open for foranymore If the file didn't Open for foryou like in my case Just go to downloadsyou like in my case Just go to downloadsyou like in my case Just go to downloadsto the folder where youto the folder where youto the folder where youdownload The file let's go to downloadsdownload The file let's go to downloadsdownload The file let's go to downloadsLet's Double Click on postLet's Double Click on postLet's Double Click on postgr G Open Click ongr G Open Click ongr G Open Click ons make changes device Click ons right here have it installation windowpogand here we need to Click on next hereand here we need to Click on next hereand here we need to Click on next hereis the location it's going to beis the location it's going to beis the location it's going to beinstalled I'm going to Click on next I'minstalled I'm going to Click on next I'minstalled I'm going to Click on next I'mgoing to keep the defaultgoing to keep the defaultgoing to keep the defaultlocation these are the software that belocation these are the software that belocation these are the software that beinstalled with post I'm going to keepinstalled with post I'm going to keepinstalled with post I'm going to keepthe default selection so Click on nextthe default selection so Click on nextthe default selection so Click on nextnext we have the data directory and herenext we have the data directory and herenext we have the data directory and hereI'm going to keep the default directoryI'm going to keep the default directoryI'm going to keep the default directoryI'm going to Click on next and now weI'm going to Click on next and now weI'm going to Click on next and now weneed to create a password and this isneed to create a password and this isneed to create a password and this isextremely important not to forget theextremely important not to forget theextremely important not to forget thenot forget the password that we typenot forget the password that we typenot forget the password that we typehere so I 'm G ty here password as thepassword Click onpassword Click onpassword Click onnext and here we have the port and thisnext and here we have the port and thisnext and here we have the port and thisis the default as you can see here 5432is the default as you can see here 5432is the default as you can see here 5432then I'm going to Click on next here andthen I'm going to Click on next here andthen I'm going to Click on next here andhere you have Select the local to behere you have Select the local to behere you have Select the local to beused By The New database cluster I'mused By The New database cluster I'mused By The New database cluster I'mgoing to keep the default locale Clickgoing to keep the default locale Clickgoing to keep the default locale Clickonononnext gna Click on next again Click onnext gna Click on next again Click onnext gna Click on next again Click onnext Again and now post gna Be installednext Again and now post gna Be installednext Again and now post gna Be installedon mycomputer al right on the installation iscomputer al right on the installation iscomputer al right on the installation isfinish Just un check this stu Builderfinish Just un check this stu Builderfinish Just un check this stu Builderbecause don' t need to Open that andbecause don' t need to Open that andbecause don' t need to Open that andClick onClick onClick onfinish now gna move to Step Two Which is tofinish now gna move to Step Two Which is tofinish now gna move to Step Two Which is todownload and install PG admin I'm Gdownload and install PG admin I'm Gdownload and install PG admin I'm Gtype PGtype PGtype PGadmin need to go hpg admin dorg Click onadmin need to go hpg admin dorg Click onadmin need to go hpg admin dorg Click onthislink Let's Click onlink Let's Click onlink Let's Click onagree we go toagree we go toagree we go todownload and we gna Select here Windowsdownload and we gna Select here Windowsdownload and we gna Select here Windowsif you have a different and have someif you have a different and have someif you have a different and have somevendors here of Linux and we have I'mvendors here of Linux and we have I'mvendors here of Linux and we have I'mgoing to Select Windows because I havegoing to Select Windows because I havegoing to Select Windows because I haveWindows so cck on Windows as you can seeWindows so cck on Windows as you can seeWindows so cck on Windows as you can seethere is also no no longer ver but wethere is also no no longer ver but wethere is also no no longer ver but wegna use PG admin 4 so Click ongna use PG admin 4 so Click ongna use PG admin 4 so Click onWindows and here we have differentWindows and here we have differentWindows and here we have differentversions I'm going to Click on thisversions I'm going to Click on thisversions I'm going to Click on thisversion forversion forversion forv74 so Click Onv74 so Click Onv74 so Click OnThat and here need to download thisThat and here need to download thisThat and here need to download thisexecutable file so I'm going to Click onexecutable file so I'm going to Click onexecutable file so I'm going to Click onthis executablethis executablethis executablefile and now PG is going to be downloadedfile and now PG is going to be downloadedfile and now PG is going to be downloadedon mycomputer the downloading is finishedcomputer the downloading is finishedcomputer the downloading is finishedjust Openfile Let's Close thebrowser Again If It doesn't open thebrowser Again If It doesn't open thebrowser Again If It doesn't open thefile for you like in my case go to thefile for you like in my case go to thefile for you like in my case go to thefolder Where downloaded PG admin go todownloads and have here PGdownloads and have here PGdownloads and have here PGadmin gna Double Click on itI'm G to Click here install for me onlyI'm G to Click here install for me onlyI'm G to Click here install for me onlyrecommended I'm G Click on next I'm Grecommended I'm G Click on next I'm Grecommended I'm G Click on next I'm Gaccept the license agreement I'm G Clickaccept the license agreement I'm G Clickaccept the license agreement I'm G Clickon next This is the location Where going toon next This is the location Where going toon next This is the location Where going tobe installed I'm to Click on next nowbe installed I'm to Click on next nowbe installed I'm to Click on next nowI'm to Click here on next to install PGI'm to Click here on next to install PGI'm to Click here on next to install PGadmin so Click on next andinstallnow the installation is finish Let'snow the installation is finish Let'snow the installation is finish Let'sClick onfinish Let's move to Step Step 3finish Let's move to Step Step 3finish Let's move to Step Step 3download but do not directly Open DVDdownload but do not directly Open DVDdownload but do not directly Open DVDrentar file and DVD rent file it's given arentar file and DVD rent file it's given arentar file and DVD rent file it's given acompressed version of a database that Gcompressed version of a database that Gcompressed version of a database that Gto useto useto usethisthisthisand in downloads If I go indownloads downloaddownloads downloaddownloads downloadasdasdasdnot and What I need to do is to go tonot and What I need to do is to go tonot and What I need to do is to go tothis particular video Under resourcesthis particular video Under resourcesthis particular video Under resourcesClick on resources and You See DVDClick on resources and You See DVDClick on resources and You See DVDrental file and Click on the file andrental file and Click on the file andrental file and Click on the file andthat file is going to be downloaded inthat file is going to be downloaded inthat file is going to be downloaded incomputer this is what you need to do andcomputer this is what you need to do andcomputer this is what you need to do andafter that Step f is to restart yourafter that Step f is to restart yourafter that Step f is to restart yourcomputer so let restart our computerStep number Five restore the databaseStep number Five restore the databaseStep number Five restore the databaseand there is a Quick note here and thatand there is a Quick note here and thatand there is a Quick note here and thatis to ignore the fail code If It appearsis to ignore the fail code If It appearsis to ignore the fail code If It appearsand this can appear because it willand this can appear because it willand this can appear because it willthink that you restore the databasethink that you restore the databasethink that you restore the databasetwice so Let's Open PGtwice so Let's Open PGtwice so Let's Open PGadmin PGadmin Let's Click on Run asadministrator this aom changes to deviceadministrator this aom changes to deviceadministrator this aom changes to deviceClick ons it says starting PG admins it says starting PG admins it says starting PG admin4 right it says loading PG admin 4 v744 right it says loading PG admin 4 v744 right it says loading PG admin 4 v74and this is not This is not going toand this is not This is not going toand this is not This is not going towork It will show this loading PJ adminwork It will show this loading PJ adminwork It will show this loading PJ adminindefinitely and this happens becauseindefinitely and this happens becauseindefinitely and this happens becauseWhen we installed the post gr sql weWhen we installed the post gr sql weWhen we installed the post gr sql wealso install p admin and after that instalso install p admin and after that instalso install p admin and after that instinstalled PG admin Again and Because ofinstalled PG admin Again and Because ofinstalled PG admin Again and Because ofthat generated an error and it cannot openthat generated an error and it cannot openthat generated an error and it cannot openthe PG admin So what you need to do is tothe PG admin So what you need to do is tothe PG admin So what you need to do is togo to controlpanel so go to controlpanel so go to controlpanel so go to controlpanel go to an install program let forpanel go to an install program let forpanel go to an install program let forpost Let's Click here install it and Wepost Let's Click here install it and Wepost Let's Click here install it and Weare going to aare going to aare going to aspecific install PG admin 4 because that is the thespecific install PG admin 4 because that is the thespecific install PG admin 4 because that is the theproblem that Is What generated theproblem that Is What generated theproblem that Is What generated theerror so Let's Select individualerror so Let's Select individualerror so Let's Select individualcomponents Click on next and make surecomponents Click on next and make surecomponents Click on next and make sureto check PG admin 4 and Click on nextto check PG admin 4 and Click on nextto check PG admin 4 and Click on nextnow Wait a littlebitbitbitright the installation on installationright the installation on installationright the installation on installationis completed now Let's close this Let'sis completed now Let's close this Let'sis completed now Let's close this Let'stype PG admin PGtype PG admin PGtype PG admin PGadmin and Let's Run it as administratoradmin and Let's Run it as administratoradmin and Let's Run it as administratoras you can see here you can Run itas you can see here you can Run itas you can see here you can Run itbecause you have it installed twice sobecause you have it installed twice sobecause you have it installed twice soRun it asRun it asRun it asadministrator Click on s because it'sadministrator Click on s because it'sadministrator Click on s because it'sasking us if you allow this to makeasking us if you allow this to makeasking us if you allow this to makechangesdevicedevicedevicestarting PG admin4 and here need to create Master4 and here need to create Master4 and here need to create Masterpassword and this password Can Bepassword and this password Can Bepassword and this password Can Beanything you want and this is theanything you want and this is theanything you want and this is thepassword for the PG admin not for pogpassword for the PG admin not for pogpassword for the PG admin not for pogand I'm G to type as the passwordpassword and then I'm going to Click onpassword and then I'm going to Click onpassword and then I'm going to Click onOk then on the left hand side we haveOk then on the left hand side we haveOk then on the left hand side we haveservers and I'm going to Click onservers and I'm going to Click onservers and I'm going to Click onservers and now you see this pop up andservers and now you see this pop up andservers and now you see this pop up andhere need to type the password that wehere need to type the password that wehere need to type the password that wecreated When install post I'm G tye herecreated When install post I'm G tye herecreated When install post I'm G tye herepassword and Click onpassword and Click onpassword and Click onOk and that is gna open this I alreadyOk and that is gna open this I alreadyOk and that is gna open this I alreadyhave here actually I don't have thehave here actually I don't have thehave here actually I don't have thedatabase What I need to do next is todatabase What I need to do next is todatabase What I need to do next is toright Clickright Clickright Clickhere go to create actuallyhere go to create actuallyhere go to create actuallyabove createabove createabove createdatabase Let's Call itdatabase Let's Call itdatabase Let's Call itDVDDVDDVDrental Let's Click on Saveso now have a database DVD rental nowso now have a database DVD rental nowso now have a database DVD rental nowwhat you need to do here is right Clickwhat you need to do here is right Clickwhat you need to do here is right Clickand Go toand Go toand Go torestore and now gna Reference our DVDrestore and now gna Reference our DVDrestore and now gna Reference our DVDrental datar file G Clickrental datar file G Clickrental datar file G Clickhere and instead of custom files herehere and instead of custom files herehere and instead of custom files hereSelect AllSelect AllSelect Allfiles go to downloads and here we havefiles go to downloads and here we havefiles go to downloads and here we haveDVD rental or you can copy this locationDVD rental or you can copy this locationDVD rental or you can copy this locationdirectly from it is in your PC and pastedirectly from it is in your PC and pastedirectly from it is in your PC and pasteit here and Click on restore and now gnait here and Click on restore and now gnait here and Click on restore and now gnarestore the database Which is gna haverestore the database Which is gna haverestore the database Which is gna havesome information so we gna have somesome information so we gna have somesome information so we gna have somedata that gna use to explain many thingsdata that gna use to explain many thingsdata that gna use to explain many thingsso Let's Click on restore and it saysso Let's Click on restore and it saysso Let's Click on restore and it saysprocess started back on server pog andprocess started back on server pog andprocess started back on server pog andit says process completed that meansit says process completed that meansit says process completed that meanseverything worked fine let's give it aeverything worked fine let's give it aeverything worked fine let's give it arefresh so Let's Click on refresh nowrefresh so Let's Click on refresh nowrefresh so Let's Click on refresh nownext very important thing is to rightnext very important thing is to rightnext very important thing is to rightClick here go to query tool and now gnaClick here go to query tool and now gnaClick here go to query tool and now gnaOpen a window here can CloseOpen a window here can CloseOpen a window here can Closethose and Let's test our database I'm Gthose and Let's test our database I'm Gthose and Let's test our database I'm Gty here Select gna look at All of thosety here Select gna look at All of thosety here Select gna look at All of thosein more detail In The next videos Asterin more detail In The next videos Asterin more detail In The next videos Asterx from and I typex from and I typex from and I typefilm semicolon Again I'm gna explain Allfilm semicolon Again I'm gna explain Allfilm semicolon Again I'm gna explain AllOf The next videos and Click on this RunOf The next videos and Click on this RunOf The next videos and Click on this Runbutton and now have data being outputbutton and now have data being outputbutton and now have data being outputdown here as you can see so everythingdown here as you can see so everythingdown here as you can see so everythingworksfine welcome back now time to start afine welcome back now time to start afine welcome back now time to start adiscussion about the Select statement Sodiscussion about the Select statement Sodiscussion about the Select statement Sowhat is the Select statement the Selectwhat is the Select statement the Selectwhat is the Select statement the Selectstatement is used to retrieve data fromstatement is used to retrieve data fromstatement is used to retrieve data fromOne or more database tables it allowsOne or more database tables it allowsOne or more database tables it allowsyou to specify the columns you want toyou to specify the columns you want toyou to specify the columns you want toretrieve the tables from which you want toretrieve the tables from which you want toretrieve the tables from which you want toretrieve the data and Any conditionsretrieve the data and Any conditionsretrieve the data and Any conditionsthat must be met for the data to bethat must be met for the data to bethat must be met for the data to beincluded in the result set later weincluded in the result set later weincluded in the result set later welearn How to combine Select statementlearn How to combine Select statementlearn How to combine Select statementwith other sql Select statements towith other sql Select statements towith other sql Select statements toperform more complex queries here is howperform more complex queries here is howperform more complex queries here is howthe syntax of Select statement Looksthe syntax of Select statement Looksthe syntax of Select statement Lookslike so we have Select then we have Thelike so we have Select then we have Thelike so we have Select then we have Thecolumn Name then we have the keywordcolumn Name then we have the keywordcolumn Name then we have the keywordfrom and the table namefrom and the table namefrom and the table nameand now this This is how this will lookand now this This is how this will lookand now this This is how this will lookLet's say that we have a Few tablesLet's say that we have a Few tablesLet's say that we have a Few tableswhich are i created here a Few tableswhich are i created here a Few tableswhich are i created here a Few tableswhich are populated data and Let's saywhich are populated data and Let's saywhich are populated data and Let's saythat you want to retrieve some data fromthat you want to retrieve some data fromthat you want to retrieve some data fromthose tables based on whatthose tables based on whatthose tables based on whati told you what you need to do here isi told you what you need to do here isi told you what you need to do here isto type Select first but first of Allto type Select first but first of Allto type Select first but first of Allyou need to think about What from Whatyou need to think about What from Whatyou need to think about What from Whattable you want to reeve the data so thetable you want to reeve the data so thetable you want to reeve the data so thetable name Let's say that I want to reevetable name Let's say that I want to reevetable name Let's say that I want to reevethe data from the table One so with thatthe data from the table One so with thatthe data from the table One so with thatin mind you type the Select keyword Thein mind you type the Select keyword Thein mind you type the Select keyword Thecolumn Name Let's say C1 and It willcolumn Name Let's say C1 and It willcolumn Name Let's say C1 and It willretrieve All the data in the column C1retrieve All the data in the column C1retrieve All the data in the column C1from the table table One so in This Wayfrom the table table One so in This Wayfrom the table table One so in This WayYou will Get All the dataYou will Get All the dataYou will Get All the datafor for the column one C1 in from thefor for the column one C1 in from thefor for the column one C1 in from thetable One now Let's say that you want totable One now Let's say that you want totable One now Let's say that you want toretrieve Two cols you can do that by puttingretrieve Two cols you can do that by puttingretrieve Two cols you can do that by puttingcan tycan tycan tySelect type The colum Name let say C1Select type The colum Name let say C1Select type The colum Name let say C1and C2 and that now It will return backand C2 and that now It will return backand C2 and that now It will return backAll the data fromAll the data fromAll the data fromthe column one and column Two from thethe column one and column Two from thethe column one and column Two from theC1 and C2 Let's say that you want toC1 and C2 Let's say that you want toC1 and C2 Let's say that you want toretrieve data from a different table inretrieve data from a different table inretrieve data from a different table inOrder to that Again type Select C1 andOrder to that Again type Select C1 andOrder to that Again type Select C1 andC2 AgainC2 AgainC2 Againwe datawe datawe dataforums now we CH the table name type sayforums now we CH the table name type sayforums now we CH the table name type saytable now Let's say that you want totable now Let's say that you want totable now Let's say that you want toreve all the data from a table Of coursereve all the data from a table Of coursereve all the data from a table Of coursethat you can type Select and you typethat you can type Select and you typethat you can type Select and you typeThe The column Name c1 c2 and C3 fromThe The column Name c1 c2 and C3 fromThe The column Name c1 c2 and C3 fromthe table name but what if you havethe table name but what if you havethe table name but what if you havehundreds or thousands of tables in thathundreds or thousands of tables in thathundreds or thousands of tables in thatWayWayWaythat Because of that you not be not bethat Because of that you not be not bethat Because of that you not be not bepracticalalls ty Select Aster x from and thealls ty Select Aster x from and thealls ty Select Aster x from and thetable name table One and that is gnatable name table One and that is gnatable name table One and that is gnareturn All the data from the table Onereturn All the data from the table Onereturn All the data from the table OneAll the columns so thisWhatnow one more thing the Select keyword isnow one more thing the Select keyword isnow one more thing the Select keyword isused to specify as I said the columnsused to specify as I said the columnsused to specify as I said the columnsyou want to retrieve and you can specifyyou want to retrieve and you can specifyyou want to retrieve and you can specifyindividual column names separated by commasindividual column names separated by commasindividual column names separated by commasas I said or you can use an Aster toas I said or you can use an Aster toas I said or you can use an Aster toSelect All the columns from theSelect All the columns from theSelect All the columns from thespecified tablesspecified tablesspecified tablesbut in practice it is better not to use thebut in practice it is better not to use thebut in practice it is better not to use theAsterix When You don't need to use theAsterix When You don't need to use theAsterix When You don't need to use theAsterix because it will Get a lot ofAsterix because it will Get a lot ofAsterix because it will Get a lot ofdata and It will make the communicationdata and It will make the communicationdata and It will make the communicationbetween the database and the softwarebetween the database and the softwarebetween the database and the softwareusing slower alr now Let's move to postusing slower alr now Let's move to postusing slower alr now Let's move to postand Let's type Selectstatements Open PGadmin Run asadministrator Click onswe Wait a little bit until PG adminStarsPG admin Open Let's Click on here onPG admin Open Let's Click on here onPG admin Open Let's Click on here onthis greater Arrow in front ofthis greater Arrow in front ofthis greater Arrow in front ofservers for the password Let's typepassword and Let's Click here to Savepassword and Let's Click here to Savepassword and Let's Click here to Savepassword to not type it Again time Openpassword to not type it Again time Openpassword to not type it Again time Openserver now gna open the server we haveserver now gna open the server we haveserver now gna open the server we haveour database here I'm gna Click there haveour database here I'm gna Click there haveour database here I'm gna Click there haveDVD rental the database that weDVD rental the database that weDVD rental the database that wecreated and pog the database that wascreated and pog the database that wascreated and pog the database that wasprovided implic for us now Let's sayprovided implic for us now Let's sayprovided implic for us now Let's saythat I want to query and I want to querythat I want to query and I want to querythat I want to query and I want to queryso I'm gna right Click on the databaseso I'm gna right Click on the databaseso I'm gna right Click on the databasethat I want to query go to querythat I want to query go to querythat I want to query go to querytool that is open the querywindow here I can type statement butwindow here I can type statement butwindow here I can type statement butfirst Before I do that let's go to schemefirst Before I do that let's go to schemefirst Before I do that let's go to schemeto see how our how the structure of ourto see how our how the structure of ourto see how our how the structure of ourdatabase look Click ondatabase look Click ondatabase look Click onscheme go to Let me see tables to seescheme go to Let me see tables to seescheme go to Let me see tables to seeWhat tables we have and as you can seeWhat tables we have and as you can seeWhat tables we have and as you can seehere we have the tables actor addresshere we have the tables actor addresshere we have the tables actor addresscategory City country and so on nowcategory City country and so on nowcategory City country and so on nowLet's say that I want toLet's say that I want toLet's say that I want toSelect All the columns from the tableSelect All the columns from the tableSelect All the columns from the tableactor type Select with capital lettersactor type Select with capital lettersactor type Select with capital lettersfromfromfromactor semicolon now Let's Run this nowactor semicolon now Let's Run this nowactor semicolon now Let's Run this nowIt's returning All the actors fromIt's returning All the actors fromIt's returning All the actors fromfrom It's returning All the actors fromfrom It's returning All the actors fromfrom It's returning All the actors fromthe table actor now Let's say that Ithe table actor now Let's say that Ithe table actor now Let's say that Iwant to get Only the first name and thewant to get Only the first name and thewant to get Only the first name and thelast name in Order to do that I'm gonnalast name in Order to do that I'm gonnalast name in Order to do that I'm gonnadeldeldelthis I'm gna type and to see What kindthis I'm gna type and to see What kindthis I'm gna type and to see What kindof structure the table has you can rightof structure the table has you can rightof structure the table has you can rightClick in front of it and YouClick in front of it and YouClick in front of it and YouSee what columns it has has actor IDSee what columns it has has actor IDSee what columns it has has actor IDfirst name and last name Let's typefirst name and last name Let's typefirst name and last name Let's typefirst name first undfirst name first undfirst name first undname and actually Ask For the first namename and actually Ask For the first namename and actually Ask For the first namefrom Asterixfrom Asterixfrom Asterixactor semicolonactor semicolonactor semicolonLet's increasethis Let's Click onthis Let's Click onthis Let's Click onRun and We Get All the first names ofRun and We Get All the first names ofRun and We Get All the first names ofthe all the actors in our table actor asthe all the actors in our table actor asthe all the actors in our table actor asyou can see down here in the data outputyou can see down here in the data outputyou can see down here in the data outputso everything works fine and you can put aso everything works fine and you can put aso everything works fine and you can put ahere and you can Get the last name toohere and you can Get the last name toohere and you can Get the last name tooso type lastso type lastso type lastname because as last nameand al right i should putand al right i should putand al right i should putthere Let's Run itthere Let's Run itthere Let's Run itAgain so have the first name can seeAgain so have the first name can seeAgain so have the first name can seehere and the last name now the firsthere and the last name now the firsthere and the last name now the firstlast name don't need to bein first name doesn't have to be Beforein first name doesn't have to be Beforein first name doesn't have to be Beforethe last name can last namethe last name can last namethe last name can last nameKama firstKama firstKama firstname now Click on Run this will stillname now Click on Run this will stillname now Click on Run this will stillwork so everything is working the samework so everything is working the samework so everything is working the samenow as you you see herenow as you you see herenow as you you see herethethethesql keywords are capital but you can typesql keywords are capital but you can typesql keywords are capital but you can typethem lower CAS letters andthem lower CAS letters andthem lower CAS letters andwork but a good convention to capitalizework but a good convention to capitalizework but a good convention to capitalizethem to make sure that you have you havethem to make sure that you have you havethem to make sure that you have you haveyou can see the distinction between theyou can see the distinction between theyou can see the distinction between thesql statements and the column namessql statements and the column namessql statements and the column namesright so Always capitalize thesql keywords if you want to call themsql keywords if you want to call themsql keywords if you want to call themlike that All Right so This is ourlike that All Right so This is ourlike that All Right so This is ourdiscussion for this video I hope thatdiscussion for this video I hope thatdiscussion for this video I hope thatyou enjoyed and see you In The nextyou enjoyed and see you In The nextyou enjoyed and see you In The nextvideo welcome back in this video We arevideo welcome back in this video We arevideo welcome back in this video We aregoing to do Challenge with a Selectgoing to do Challenge with a Selectgoing to do Challenge with a Selectstatement and here is how the Challengestatement and here is how the Challengestatement and here is how the Challengestructure is going to look like firststructure is going to look like firststructure is going to look like firstgoing to have a business situationgoing to have a business situationgoing to have a business situationbecause Often in When working with sqbecause Often in When working with sqbecause Often in When working with sqdatabases you have a business situationdatabases you have a business situationdatabases you have a business situationand you need to translate to get thatand you need to translate to get thatand you need to translate to get thatbusiness situation and to Translate itbusiness situation and to Translate itbusiness situation and to Translate itinto a query so this is what will dointo a query so this is what will dointo a query so this is what will dofirst will see The Business situationfirst will see The Business situationfirst will see The Business situationthen we have the Challenge Question andthen we have the Challenge Question andthen we have the Challenge Question andthis is moreand actand actand actCheng next have the expected Answer andCheng next have the expected Answer andCheng next have the expected Answer andhere I'm going to Show You what thehere I'm going to Show You what thehere I'm going to Show You what theexpected Answerexpected Answerexpected Answerwith is going to look like next we havewith is going to look like next we havewith is going to look like next we havethe hints so I'm going to give you somethe hints so I'm going to give you somethe hints so I'm going to give you somehints about the Challenge so It will behints about the Challenge so It will behints about the Challenge so It will beeasier for you to solve and finally showeasier for you to solve and finally showeasier for you to solve and finally showthethethesolution the first the situation So whatsolution the first the situation So whatsolution the first the situation So whatwe want to see is the payment date ofwe want to see is the payment date ofwe want to see is the payment date ofour existingour existingour existingcustomers so This is the situationcustomers so This is the situationcustomers so This is the situationnext we have the Challenge you need tonext we have the Challenge you need tonext we have the Challenge you need touse the Select statement to grab theuse the Select statement to grab theuse the Select statement to grab thecustomer ID of every customer and thecustomer ID of every customer and thecustomer ID of every customer and thepayment date and the payment date andpayment date and the payment date andpayment date and the payment date andthe amount need this is Challengethe amount need this is Challengethe amount need this is Challengeand know how to use this How to solveand know how to use this How to solveand know how to use this How to solvethis Challenge you can Jump In and trythis Challenge you can Jump In and trythis Challenge you can Jump In and tryto identify the table from which you canto identify the table from which you canto identify the table from which you canGet this data and run The The The RunGet this data and run The The The RunGet this data and run The The The Runthe query statement so this what youthe query statement so this what youthe query statement so this what yougoing to do and now I'm going to showgoing to do and now I'm going to showgoing to do and now I'm going to showyou the expected answers and this isyou the expected answers and this isyou the expected answers and this isvery important this might not bevery important this might not bevery important this might not bedisplayed ex Same Orderdisplayed ex Same Orderdisplayed ex Same Orderbecause How squ works is that itbecause How squ works is that itbecause How squ works is that itdata efficient but later see how candata efficient but later see how candata efficient but later see how cancontr how the data is ordered but we arecontr how the data is ordered but we arecontr how the data is ordered but we aregoing to look at that in In The nextgoing to look at that in In The nextgoing to look at that in In The nextvideos now we are not going to do that herevideos now we are not going to do that herevideos now we are not going to do that hereand next we have the hints so the hintand next we have the hints so the hintand next we have the hints so the hintis to use the payment table and you canis to use the payment table and you canis to use the payment table and you canuse the table to Drop down touse the table to Drop down touse the table to Drop down toview Drop down to view Whats areview Drop down to view Whats areview Drop down to view Whats areavailable This isavailable This isavailable This isdo next we have the solution and thedo next we have the solution and thedo next we have the solution and thesolution is Let's Open PG admin so Let'ssolution is Let's Open PG admin so Let'ssolution is Let's Open PG admin so Let'sOpen PGadmin so Let's Open PGadmin so Let's Open PGadmin so Let's Open PGadmin Let's Run it asadmin Let's Run it asadmin Let's Run it asadministrator Click onadministrator Click onadministrator Click onYes now PG admin GstartPG been started Let's Open our serverPG been started Let's Open our serverPG been started Let's Open our serverand now It's not asking us for theand now It's not asking us for theand now It's not asking us for thepassword because previously we saved thepassword because previously we saved thepassword because previously we saved thepassword and Let's open the query toolpassword and Let's open the query toolpassword and Let's open the query toolbecause gna type abecause gna type abecause gna type aqueryqueryqueryand if you figure out How to solve thisand if you figure out How to solve thisand if you figure out How to solve thisChallenge Write me in the coms the thingChallenge Write me in the coms the thingChallenge Write me in the coms the thingthat you need to do is to go to paymentthat you need to do is to go to paymentthat you need to do is to go to paymentbecause here we gna have our columns I'mbecause here we gna have our columns I'mbecause here we gna have our columns I'mgna Click on this creat in front ofgna Click on this creat in front ofgna Click on this creat in front ofcolumns and here have customer ID amountcolumns and here have customer ID amountcolumns and here have customer ID amountand the payment date so in the Challengeand the payment date so in the Challengeand the payment date so in the ChallengeI said that you needI said that you needI said that you needto get G typeto get G typeto get G typeSelect and I'll type customerIDIDIDComaamount andamount andamount andpaymentdate from and the name of the tabledate from and the name of the tabledate from and the name of the tableWhich isWhich isWhich ispayment payment and I put a semicolon topayment payment and I put a semicolon topayment payment and I put a semicolon toindicate that this is the end of theindicate that this is the end of theindicate that this is the end of thequery but this can work also withoutquery but this can work also withoutquery but this can work also withoutsemicolonsemicolonsemicolonI'm gon and Let's Run ourI'm gon and Let's Run ourI'm gon and Let's Run ourquery and we have the customer ID wequery and we have the customer ID wequery and we have the customer ID wehave as you can see custom ID the amounthave as you can see custom ID the amounthave as you can see custom ID the amountand the payment date so everything worksand the payment date so everything worksand the payment date so everything worksfine I hope that you figure out How tofine I hope that you figure out How tofine I hope that you figure out How tosolve this Challenge and see you In Thesolve this Challenge and see you In Thesolve this Challenge and see you In Thenextnextnextvideo welcome back now time to start avideo welcome back now time to start avideo welcome back now time to start adiscussion about the Select Distinctdiscussion about the Select Distinctdiscussion about the Select Distinctstatement in sql so in squ the Distinctstatement in sql so in squ the Distinctstatement in sql so in squ the Distinctstatement is used to filter result setstatement is used to filter result setstatement is used to filter result setof a query and retrieve only Unique rowsof a query and retrieve only Unique rowsof a query and retrieve only Unique rowsfrom the specified column or combinationfrom the specified column or combinationfrom the specified column or combinationofofofcolumns it is particularly useful Whencolumns it is particularly useful Whencolumns it is particularly useful WhenYou want to eliminate duplicate recordsYou want to eliminate duplicate recordsYou want to eliminate duplicate recordsfrom the query results so this is whatfrom the query results so this is whatfrom the query results so this is whatthe Distinct is going to do now thethe Distinct is going to do now thethe Distinct is going to do now thesyntax for using the Distinct statementsyntax for using the Distinct statementsyntax for using the Distinct statementis straight forward you simply place Theis straight forward you simply place Theis straight forward you simply place TheKey or Distinct immediately after theKey or Distinct immediately after theKey or Distinct immediately after theSelect Close Followed By The column NameSelect Close Followed By The column NameSelect Close Followed By The column Nameor names for which you want to retrieveor names for which you want to retrieveor names for which you want to retrievethe Distinct values ​​ here is the basicthe Distinct values ​​here is the basicthe Distinct values ​​here is the basicyou have Select Distinct column from andyou have Select Distinct column from andyou have Select Distinct column from andthe table name This is the syntax of thethe table name This is the syntax of thethe table name This is the syntax of theDistinct statement now if you want toDistinct statement now if you want toDistinct statement now if you want toclarifying is being applied to We canclarifying is being applied to We canclarifying is being applied to We canalso use parentheses for that so canalso use parentheses for that so canalso use parentheses for that so cantype Select Distinct parentheses thetype Select Distinct parentheses thetype Select Distinct parentheses thename of the column from and the tablename of the column from and the tablename of the column from and the tablename in This Way You Make it more Clearname in This Way You Make it more Clearname in This Way You Make it more ClearWhat data you want to receive now Let'sWhat data you want to receive now Let'sWhat data you want to receive now Let'sBreak downBreak downBreak downstat first the sql query is executed asstat first the sql query is executed asstat first the sql query is executed asusing Thea from the specified table orusing Thea from the specified table orusing Thea from the specified table ortables secondly the query Engine thentables secondly the query Engine thentables secondly the query Engine thenreturns Any duplicate rows from the resultreturns Any duplicate rows from the resultreturns Any duplicate rows from the resultset based on the column specified afterset based on the column specified afterset based on the column specified afterthe Distinct keyword and third Only thethe Distinct keyword and third Only thethe Distinct keyword and third Only thefirst occurrence of each Uniquefirst occurrence of each Uniquefirst occurrence of each Uniquecombination of the specified columncombination of the specified columncombination of the specified columnthe not have DCthe not have DCthe not have DCLet's consider this table calledLet's consider this table calledLet's consider this table calledemployees with the following data so weemployees with the following data so weemployees with the following data so wehave the employee ID the first name lasthave the employee ID the first name lasthave the employee ID the first name lastname and the department and as you canname and the department and as you canname and the department and as you cansee In this table The The John issee In this table The The John issee In this table The The John isPresent twice so we have John Smith johPresent twice so we have John Smith johPresent twice so we have John Smith johSmith and we have also the sameSmith and we have also the sameSmith and we have also the samedepartmentdepartmentdepartmentnow this can be a duplicate but it can also notnow this can be a duplicate but it can also notnow this can be a duplicate but it can also notbe a duplicate maybe there arebe a duplicate maybe there arebe a duplicate maybe there areTwo Persons with the same name workingTwo Persons with the same name workingTwo Persons with the same name workingin the same department thekeyworddo to the Question What are the Uniquedo to the Question What are the Uniquedo to the Question What are the Uniquefirst names in the table say that youfirst names in the table say that youfirst names in the table say that youwant to retrieve the first name and thewant to retrieve the first name and thewant to retrieve the first name and thelast name so if if you use the Distinctlast name so if if you use the Distinctlast name so if if you use the Distinctkeyword It will Answer to the Questionkeyword It will Answer to the Questionkeyword It will Answer to the QuestionWhat are the Unique first name and lastWhat are the Unique first name and lastWhat are the Unique first name and lastnames in the table so this is what isnames in the table so this is what isnames in the table so this is what isdoingandandandso as I said given this example We don'tso as I said given this example We don'tso as I said given this example We don'treally know the person with the samereally know the person with the samereally know the person with the samename John was a duplicate entry or Twoname John was a duplicate entry or Twoname John was a duplicate entry or Twodifferent People with the same firstdifferent People with the same firstdifferent People with the same firstname last name and department so Againname last name and department so Againname last name and department so AgainthethetheDistinct returns Only the Unique entriesDistinct returns Only the Unique entriesDistinct returns Only the Unique entriesso this What Distinct is doingso this What Distinct is doingso this What Distinct is doingnow if you Run the following query sonow if you Run the following query sonow if you Run the following query soyou type Selectemployees first name John last nameemployees first name John last nameemployees first name John last nameSmith haveSmith haveSmith haveJane have first name for the Alice andJane have first name for the Alice andJane have first name for the Alice andJohnsonJohnsonJohnsonthis result if Distinct statement Nowthis result if Distinct statement Nowthis result if Distinct statement Nowlet Mo to to worklet Mo to to worklet Mo to to workwith Open PGadminadministrator OPG admin will start inadministrator OPG admin will start inadministrator OPG admin will start inshortmoment alr Let's open theserver Let's open the curittool curittool and type here Selecttool and type here Selecttool and type here Selectso gna Select All from the filmso gna Select All from the filmso gna Select All from the filmtable see the film table downhere Click onhere Click onhere Click onRun have the film ID the title andRun have the film ID the title andRun have the film ID the title anddescription and if you go to the rightdescription and if you go to the rightdescription and if you go to the rightalso have the release here language IDalso have the release here language IDalso have the release here language IDrental dire All ofrental dire All ofrental dire All ofthis rental rate length All of thosethis rental rate length All of thosethis rental rate length All of thosecols and if Watch What column the filmcols and if Watch What column the filmcols and if Watch What column the filmhas here simplyhas here simplyhas here simplyby Click on this greater Arrow greaterby Click on this greater Arrow greaterby Click on this greater Arrow greaterArrow front of column and the cols hereArrow front of column and the cols hereArrow front of column and the cols herenow Let's say that I want tonow Let's say that I want tonow Let's say that I want toSelect the release year so I want to seeSelect the release year so I want to seeSelect the release year so I want to seeWhat are the Unique release years forWhat are the Unique release years forWhat are the Unique release years forall of our cantyDistinctrelease Just to make sure the same asrelease Just to make sure the same asrelease Just to make sure the same asthe table namethe table namethe table nameyear fromyear fromyear fromfilm nowthis al rightthis al rightthis al rightso I type this wrong Let me see how it'sso I type this wrong Let me see how it'sso I type this wrong Let me see how it'sreleaseRunRunRunAgain and have only 2006 urofofoftheur parpar and I can Run this and I get thepar and I can Run this and I get thepar and I can Run this and I get thesamesamesameoutput right Let's also see howoutput right Let's also see howoutput right Let's also see howcan Get the rentalcan Get the rentalcan Get the rentalrate so releaserate so releaserate so releaseDistinct del that Let's type here SelectDistinct del that Let's type here SelectDistinct del that Let's type here Selectdistantdistantdistantfrom Let's type but Let's type Againfrom Let's type but Let's type Againfrom Let's type but Let's type AgainSelect All from feld Because I want toSelect All from feld Because I want toSelect All from feld Because I want tosee the columnssee the columnssee the columnstypetypetypeAgain Let's RunAgain Let's RunAgain Let's Runit let's go to theit let's go to theit let's go to theright and have rental rate type hereright and have rental rate type hereright and have rental rate type heredel typeSelect gna see What How many UniqueSelect gna see What How many UniqueSelect gna see What How many Uniquerental rates have for this rentalrental rates have for this rentalrental rates have for this rentalund und rate from filmund und rate from filmund und rate from filmLet's RunLet's RunLet's Runthis and we have All of those that canthis and we have All of those that canthis and we have All of those that cansee here so Unique rentalrates Oh sorry I forgot to put Distinctrates Oh sorry I forgot to put Distinctrates Oh sorry I forgot to put Distinctbecause here have as you can seebecause here have as you can seebecause here have as you can seeduplicate typeduplicate typeduplicate typeDistinct D so let Distinct Let's RunDistinct D so let Distinct Let's RunDistinct D so let Distinct Let's Runitititnow the we havenow the we havenow the we havethis RAL rate This one and This one wethis RAL rate This one and This one wethis RAL rate This one and This one wehave three rental Unique three Uniquehave three rental Unique three Uniquehave three rental Unique three Uniquerental rates for our movies All Rightrental rates for our movies All Rightrental rates for our movies All RightI'm going to end this video here and seeI'm going to end this video here and seeI'm going to end this video here and seeyou In The nextvideo Welcome to the Select Distinctvideo Welcome to the Select Distinctvideo Welcome to the Select DistinctChallenge In this Challenge you're goingChallenge In this Challenge you're goingChallenge In this Challenge you're goingto follow the same structure asto follow the same structure asto follow the same structure aspreviously so to have first thepreviously so to have first thepreviously so to have first thesituation then I'm having the Challenge thesituation then I'm having the Challenge thesituation then I'm having the Challenge theexpedited results then I'm giving someexpedited results then I'm giving someexpedited results then I'm giving somehints and finally have the sohints and finally have the sohints and finally have the solet start the situation Let's saylet start the situation Let's saylet start the situation Let's saythat you are a data analyst working forthat you are a data analyst working forthat you are a data analyst working forsome video rental Store and thesome video rental Store and thesome video rental Store and themanagement has noticed that somemanagement has noticed that somemanagement has noticed that somecustomers have similar names and Theycustomers have similar names and Theycustomers have similar names and Theywant to identify the Unique first nameswant to identify the Unique first nameswant to identify the Unique first namesacross All customers In Theacross All customers In Theacross All customers In Thedatabase so This is the situation thatdatabase so This is the situation thatdatabase so This is the situation thatwe gave the sequel Challenge is write awe gave the sequel Challenge is write awe gave the sequel Challenge is write asel query to retrieve all distinct firstsel query to retrieve all distinct firstsel query to retrieve all distinct firstnames of customers from the customernames of customers from the customernames of customers from the customertable so This is the Challenge that needtable so This is the Challenge that needtable so This is the Challenge that needto do now the expected results your sleto do now the expected results your sleto do now the expected results your slequery should result in a list of Uniquequery should result in a list of Uniquequery should result in a list of Uniquefirst names from the customer table likefirst names from the customer table likefirst names from the customer table likeyou can see In this example without Anyyou can see In this example without Anyyou can see In this example without Anyspecificsorting and hints we have the hints usesorting and hints we have the hints usesorting and hints we have the hints usethe Select statement along with thethe Select statement along with thethe Select statement along with theDistinct keyword to retrieve UniqueDistinct keyword to retrieve UniqueDistinct keyword to retrieve Uniquevalues ​​ and to Select Distinct valuevalues ​​and to Select Distinct valuevalues ​​and to Select Distinct valuecolumn simply specify that column after thecolumn simply specify that column after thecolumn simply specify that column after theDistinct keyword in the SelectDistinct keyword in the SelectDistinct keyword in the Selectclause so those are theclause so those are theclause so those are thehints and the solution is this Selecthints and the solution is this Selecthints and the solution is this SelectDistinct first name from customer soDistinct first name from customer soDistinct first name from customer soThis is the solution to ourThis is the solution to ourThis is the solution to ourChallenge and In this Challenge we useChallenge and In this Challenge we useChallenge and In this Challenge we usethe Select statement with a Distinctthe Select statement with a Distinctthe Select statement with a Distinctkeyword to retrieve all Unique firstkeyword to retrieve all Unique firstkeyword to retrieve all Unique firstnames from the customer table the querynames from the customer table the querynames from the customer table the queryreturns a list of Distinct first namesreturns a list of Distinct first namesreturns a list of Distinct first nameswithout Any particular sorting allowingwithout Any particular sorting allowingwithout Any particular sorting allowingthe database to return the results inthe database to return the results inthe database to return the results inits natural Orderits natural Orderits natural Orderthe database May the results in thethe database May the results in thethe database May the results in theOrder inserted or in Any other internalOrder inserted or in Any other internalOrder inserted or in Any other internalOrder right I'm this video here and seeOrder right I'm this video here and seeOrder right I'm this video here and seeyou theyou theyou thevideo welcome back now time to startvideo welcome back now time to startvideo welcome back now time to startdiscussion about the count function butdiscussion about the count function butdiscussion about the count function butfirst let's go through each aspect offirst let's go through each aspect offirst let's go through each aspect ofthe count function and squ Step by Stepthe count function and squ Step by Stepthe count function and squ Step by Stepusing some simple tables to gras theusing some simple tables to gras theusing some simple tables to gras theinformation better so the count functioninformation better so the count functioninformation better so the count functionin squ the count functionin squ the count functionin squ the count functionis agre that allows to the number of RSis agre that allows to the number of RSis agre that allows to the number of RSmat specif condition with a table ormat specif condition with a table ormat specif condition with a table orresult set it a single valueresult set it a single valueresult set it a single valuerepresenting the count of rows that Meetrepresenting the count of rows that Meetrepresenting the count of rows that Meetthe criteria so this is what the countthe criteria so this is what the countthe criteria so this is what the countfunction is why is count function usefulfunction is why is count function usefulfunction is why is count function usefulthe count function is valuable forthe count function is valuable forthe count function is valuable forvarious data analysis task it helps youvarious data analysis task it helps youvarious data analysis task it helps youto obtain statistical information aboutto obtain statistical information aboutto obtain statistical information aboutdata such as counting the number ofdata such as counting the number ofdata such as counting the number ofrecords table calculating the occurrencerecords table calculating the occurrencerecords table calculating the occurrenceof a specific values ​​ in a column Findingof a specific values ​​in a column Findingof a specific values ​​in a column Findingthe number of rows that met certainthe number of rows that met certainthe number of rows that met certainconditions determine the number of Uniqueconditions determine the number of Uniqueconditions determine the number of Uniquevalues ​​ in a column When used with avalues ​​in a column When used with avalues ​​in a column When used with aDistinct statement so This is Why countDistinct statement so This is Why countDistinct statement so This is Why countfunction is useful for thisfunction is useful for thisfunction is useful for thisreasons When to use the count functionreasons When to use the count functionreasons When to use the count functionyou should use the count function Whenyou should use the count function Whenyou should use the count function Whenyou need to determine the size of ayou need to determine the size of ayou need to determine the size of atable the number of of rows it containstable the number of of rows it containstable the number of of rows it containscount occurrences of specific values ​​ incount occurrences of specific values ​​incount occurrences of specific values ​​ina column Get the summary sta statistica column Get the summary sta statistica column Get the summary sta statisticabout your data so This is where youabout your data so This is where youabout your data so This is where youshould use the countshould use the countshould use the countfunction How to use the count functionfunction How to use the count functionfunction How to use the count functionwith the Distinct statement thiswith the Distinct statement thiswith the Distinct statement thisDistinct keyword is used to eliminateDistinct keyword is used to eliminateDistinct keyword is used to eliminateduplicate rows from the result set Whenduplicate rows from the result set Whenduplicate rows from the result set Whencombined with count function it allowscombined with count function it allowscombined with count function it allowsyou to count the number of Unique valuesyou to count the number of Unique valuesyou to count the number of Unique valuesin a specific column here is How we canin a specific column here is How we canin a specific column here is How we canuse it Let's consider a simple tableuse it Let's consider a simple tableuse it Let's consider a simple tablecalled employees as you can see herecalled employees as you can see herecalled employees as you can see herehave this table have the employee ID Thehave this table have the employee ID Thehave this table have the employee ID Theemployee Name the departmentand and as you can see In this table weand and as you can see In this table weand and as you can see In this table wehave five rows and you have multiplehave five rows and you have multiplehave five rows and you have multipledepartments but you have duplicatedepartments but you have duplicatedepartments but you have duplicatedepartments as you can see here we havedepartments as you can see here we havedepartments as you can see here we haveThe Human resources twice and the alsoThe Human resources twice and the alsoThe Human resources twice and the alsoyou have it twice now Let's say that Iyou have it twice now Let's say that Iyou have it twice now Let's say that Iwantemployeestable and gna becausetable and gna becausetable and gna becausehave five rows this is what the data returnhave five rows this is what the data returnhave five rows this is what the data returnnow example Two Let's count the numbernow example Two Let's count the numbernow example Two Let's count the numberof Unique departments in the employeeof Unique departments in the employeeof Unique departments in the employeetable usingtable usingtable usingDistinct keywordDistinct keywordDistinct keywordso type Selectand dothe one because have humanthe one because have humanthe one because have humanresource for Financeand Uniquedepartmentdepartmentdepartmentdepartment Row so This is how you candepartment Row so This is how you candepartment Row so This is how you canuse thatand Let'sWait In this example we use the countWait In this example we use the countWait In this example we use the countfunction with afunction with afunction with aDistinct statement to count the UniqueDistinct statement to count the UniqueDistinct statement to count the Uniquedepartment the employee table it returnsdepartment the employee table it returnsdepartment the employee table it returnsthe count as three because there arethe count as three because there arethe count as three because there arethree Unique departmentsthree Unique departmentsthree Unique departmentshuman resources it and Finance I hopehuman resources it and Finance I hopehuman resources it and Finance I hopethat this example Help to illustrate thethat this example Help to illustrate thethat this example Help to illustrate theusage of the usage and useful of theusage of the usage and useful of theusage of the usage and useful of thecount function and squ particularly Whencount function and squ particularly Whencount function and squ particularly Whencombine with Distinct statement itcombine with Distinct statement itcombine with Distinct statement itallows you to perform various dataallows you to perform various dataallows you to perform various dataanalysis task and retrieve valuableanalysis task and retrieve valuableanalysis task and retrieve valuableinformation from yourinformation from yourinformation from yourdatabase alr now Let's move to post gruldatabase alr now Let's move to post gruldatabase alr now Let's move to post grulto to typeto to typeto to typesome code but Before Open PG admin Letsome code but Before Open PG admin Letsome code but Before Open PG admin Letme first explain difference betweenme first explain difference betweenme first explain difference betweencount with Asterix and count withoutcount with Asterix and count withoutcount with Asterix and count withoutAster so count with the column name andAster so count with the column name andAster so count with the column name andin squ the usage of count function within squ the usage of count function within squ the usage of count function withand without Aster depends on What Youand without Aster depends on What Youand without Aster depends on What Youwant to count Let's explore thewant to count Let's explore thewant to count Let's explore thedifferences and When to use this formdifferences and When to use this formdifferences and When to use this formcount with parentheses and Asterix Whencount with parentheses and Asterix Whencount with parentheses and Asterix WhenYou use count with parentheses andYou use count with parentheses andYou use count with parentheses andAsterix it Counts All the rows in theAsterix it Counts All the rows in theAsterix it Counts All the rows in thespecified table regardless of the valuesspecified table regardless of the valuesspecified table regardless of the valuesin the columns here is the syntax wein the columns here is the syntax wein the columns here is the syntax wehavehavehaveSelect count pares is Asterix from andSelect count pares is Asterix from andSelect count pares is Asterix from andthe table name now Let's see thethe table name now Let's see thethe table name now Let's see theusage use count with parentheses Asterusage use count with parentheses Asterusage use count with parentheses AsterWhen You want to count the total numberWhen You want to count the total numberWhen You want to count the total numberof rows in a table irr of whether thereof rows in a table irr of whether thereof rows in a table irr of whether thereare null values ​​ or not it Counts Allare null values ​​or not it Counts Allare null values ​​or not it Counts Allrows including those with null values ​​ inrows including those with null values ​​inrows including those with null values ​​inAny column so this is what count asAny column so this is what count asAny column so this is what count asdoes Let's consider an example Let's saydoes Let's consider an example Let's saydoes Let's consider an example Let's saythat we havethat we havethat we havea tablea tablea tablefor table students and we have thefor table students and we have thefor table students and we have thestudent ID student name and H as you canstudent ID student name and H as you canstudent ID student name and H as you cansee for the student name we have heresee for the student name we have heresee for the student name we have herevaluevaluevalueso provide a name now that not that isso provide a name now that not that isso provide a name now that not that isbecause by using countbecause by using countbecause by using countwith It will count evenwith It will count evenwith It will count evenif you want to count the total numbersif you want to count the total numbersif you want to count the total numbersthe total number of of student names Itthe total number of of student names Itthe total number of of student names Itwill count also the n One if you usewill count also the n One if you usewill count also the n One if you usecount withcount withcount withAsterix count with out Aster x so WhenAsterix count with out Aster x so WhenAsterix count with out Aster x so Whento use count with the column name so youto use count with the column name so youto use count with the column name so youspecify a column name inside them inside thespecify a column name inside them inside thespecify a column name inside them inside theparentheses of the count and itparentheses of the count and itparentheses of the count and itCounts the number of non null values ​​ inCounts the number of non null values ​​inCounts the number of non null values ​​inthat particularsynx number of non n valessynx number of non n valessynx number of non n valesusage use count with column name when youusage use count with column name when youusage use count with column name when youwant to count the occurrence of nonwant to count the occurrence of nonwant to count the occurrence of nonvalues ​​ in a specific column it ignores nvalues ​​in a specific column it ignores nvalues ​​in a specific column it ignores nvalues ​​ in the column and only Counts nonvalues ​​in the column and only Counts nonvalues ​​in the column and only Counts nonentriesentriesentriesexample and continuing with the studenttable Selectcount Let mecount Let mecount Let mehere if type Select count the studenthere if type Select count the studenthere if type Select count the studentname in parentheses from students thename in parentheses from students thename in parentheses from students thecount is gna return f because the countcount is gna return f because the countcount is gna return f because the countwithout Aster is returning all the nonwithout Aster is returning all the nonwithout Aster is returning all the nonnull values ​​ so because the student namenull values ​​so because the student namenull values ​​so because the student namef with the ID is n not gna count gnaf with the ID is n not gna count gnaf with the ID is n not gna count gnareturn this isreturn this isreturn this iswhat count with the column columnwhat count with the column columnwhat count with the column columnname is doing alri Let's move to Thename is doing alri Let's move to Thename is doing alri Let's move to Thenext now In this query count studentnext now In this query count studentnext now In this query count studentname only Counts the null values ​​ in thename only Counts the null values ​​in thename only Counts the null values ​​in thestudent name column exclude in the Rowstudent name column exclude in the Rowstudent name column exclude in the Rowwith null name hence it returns thewith null name hence it returns thewith null name hence it returns thecount of four When to use each form usecount of four When to use each form usecount of four When to use each form usecount without ascount without ascount without asWhen want to allWhen want to allWhen want to allrows with Aster Sorry including thoserows with Aster Sorry including thoserows with Aster Sorry including thosewith n valwith n valwith n valWhen When You don't have a specificWhen When You don't have a specificWhen When You don't have a specificcolumn to count but you still need tocolumn to count but you still need tocolumn to count but you still need toretrieve the total number of rows in theretrieve the total number of rows in theretrieve the total number of rows in thetable use count column name when youtable use count column name when youtable use count column name when youwant to count the occurrences of nonwant to count the occurrences of nonwant to count the occurrences of nonnull values ​​ in a particular columnnull values ​​in a particular columnnull values ​​in a particular columnignoring null entries This is usefulignoring null entries This is usefulignoring null entries This is usefulWhen to analyze Distribution of frequencyWhen to analyze Distribution of frequencyWhen to analyze Distribution of frequencyof attribute in summary the decision toof attribute in summary the decision toof attribute in summary the decision touse the count without or with or withoutuse the count without or with or withoutuse the count without or with or withouta depends On whatever You want to counta depends On whatever You want to counta depends On whatever You want to countAll rows including ns or you want toAll rows including ns or you want toAll rows including ns or you want tocount Only the accces of non null valuescount Only the accces of non null valuescount Only the accces of non null valuesin a specific column and you can choosein a specific column and you can choosein a specific column and you can choosethe appropriate form bas on yourthe appropriate form bas on yourthe appropriate form bas on youranalysisanalysisanalysisneeds al right Let's move to The nextneeds al right Let's move to The nextneeds al right Let's move to The nextslide in summary count with Aster Countsslide in summary count with Aster Countsslide in summary count with Aster CountsAll rows In The table including rowsAll rows In The table including rowsAll rows In The table including rowswith null values ​​ in Any column and countwith null values ​​in Any column and countwith null values ​​in Any column and countwith column name Counts the number ofwith column name Counts the number ofwith column name Counts the number ofnon values ​​ in the specifi columnnon values ​​in the specifi columnnon values ​​in the specifi columnignoring rows with n null values ​​ in thatignoring rows with n null values ​​in thatignoring rows with n null values ​​in thatcolumn choose the appropriate form of thecolumn choose the appropriate form of thecolumn choose the appropriate form of thecount function based on yourcount function based on yourcount function based on yourspecific requirements if you want tospecific requirements if you want tospecific requirements if you want tocount All the rows in the tablecount All the rows in the tablecount All the rows in the tableregardless of the null values ​​ use countregardless of the null values ​​use countregardless of the null values ​​use countwith Asterix if you want to count thewith Asterix if you want to count thewith Asterix if you want to count thenumber of non values in a specifiednumber of non values in a specifiednumber of non values in a specifiedcolumn use count with The column Namecolumn use count with The column Namecolumn use count with The column Namealri now Let's Open PG admin al rightalri now Let's Open PG admin al rightalri now Let's Open PG admin al rightLet's Open PG adminnow Let's Run it asadministratorLet's open theserver Let's Open deytool type I want to get All the datatool type I want to get All the datatool type I want to get All the datafrom the payment table typeSelectfrom paymentsemicolonsemicolonsemicolonLet's RunLet's RunLet's Runthis right now Let's say that I want tothis right now Let's say that I want tothis right now Let's say that I want toknow the number of rows In this tableknow the number of rows In this tableknow the number of rows In this tablewhat i can do is I can typewhat i can do is I can typewhat i can do is I can typehereherehereSelectSelectSelectcountcountcountAsterix putParisParisParisAsterix paresAsterix paresAsterix paresagainagainagainAsterixAsterixAsterixfrompayment and that is G the number ofpayment and that is G the number ofpayment and that is G the number ofrows on the Run and we have4596 Now can do another amount the name4596 Now can do another amount the name4596 Now can do another amount the nameof ofof ofof oftable not the name of the name of Let metable not the name of the name of Let metable not the name of the name of Let meOpen this of a column and that will G toOpen this of a column and that will G toOpen this of a column and that will G toreturn the same result as you can seereturn the same result as you can seereturn the same result as you can seehere everything works finehere everything works finehere everything works finenow next Let's say that I want tonow next Let's say that I want tonow next Let's say that I want toretrieve the actual number of the Uniqueretrieve the actual number of the Uniqueretrieve the actual number of the Uniqueamounts so I'm gna type hereamounts so I'm gna type hereamounts so I'm gna type hereSelectDistinct amount from payment so if youDistinct amount from payment so if youDistinct amount from payment so if youRun thisdo that this part is gna the number ofdo that this part is gna the number ofdo that this part is gna the number ofamounts so need to put here Beforeamounts so need to put here Beforeamounts so need to put here Beforecountcountparesparesparesputtin paresputtin paresputtin paresam amountam amountam amountof paymentsof paymentsof paymentsand now if Runand now if Runand now if Runthis Get 19 and you can also put alsothis Get 19 and you can also put alsothis Get 19 and you can also put alsothis in parentheses to make it morethis in parentheses to make it morethis in parentheses to make it moreClear another parentheseshere now if you Runhere now if you Runhere now if you Runthis Get also 19 so this returns thethis Get also 19 so this returns thethis Get also 19 so this returns thenumber of Unique amounts in our paymentnumber of Unique amounts in our paymentnumber of Unique amounts in our paymentand the count is counting them so I'm going toand the count is counting them so I'm going toand the count is counting them so I'm going toend this video here and see youend this video here and see youend this video here and see youIn The nextIn The nextIn The nextvideo welcome back now time to start avideo welcome back now time to start avideo welcome back now time to start adiscussion about the Select Wherediscussion about the Select Wherediscussion about the Select Wherestatement in squ so in squ Wherestatement in squ so in squ Wherestatement in squ so in squ Wherestatement is a crucial part of quer andstatement is a crucial part of quer andstatement is a crucial part of quer andfiltering data in relational database itfiltering data in relational database itfiltering data in relational database itallows you to reve specific rows from aallows you to reve specific rows from aallows you to reve specific rows from atable that much certain conditions Intable that much certain conditions Intable that much certain conditions Inthis detail Step by Step explanation Ithis detail Step by Step explanation Ithis detail Step by Step explanation Iwill cover everything you need tostat basic synx the Where cluse isstat basic synx the Where cluse isstat basic synx the Where cluse istypically used in combination with thetypically used in combination with thetypically used in combination with theSelect statement to filter RS based onSelect statement to filter RS based onSelect statement to filter RS based onspecific conditions the basic syntax isspecific conditions the basic syntax isspecific conditions the basic syntax isas follows Select the name of the columnas follows Select the name of the columnas follows Select the name of the columnor the name of the columns from the nameor the name of the columns from the nameor the name of the columns from the nameof the table Where and then Pass The Thecondition nextcondition nextcondition nextSelect specif the columns you want toSelect specif the columns you want toSelect specif the columns you want toreturn from the table from indicates thereturn from the table from indicates thereturn from the table from indicates thename of the table that you are cingname of the table that you are cingname of the table that you are cingwhere introduces the condition thatwhere introduces the condition thatwhere introduces the condition thatfilters The rows The condition Thefilters The rows The condition Thefilters The rows The condition Thecondition is evaluated for each Row in thecondition is evaluated for each Row in thecondition is evaluated for each Row in thetable and Only the rows that meet thetable and Only the rows that meet thetable and Only the rows that meet thecondition will be included in the resultcondition will be included in the resultcondition will be included in the resultset so It's very important to have thisset so It's very important to have thisset so It's very important to have thisinininmind and May be wondering What is amind and May be wondering What is amind and May be wondering What is acondition condition is logicalTS to determine Row to include in theTS to determine Row to include in theTS to determine Row to include in theresultresultresultset common operators using conditionsset common operators using conditionsset common operators using conditionsinclude equ to not equal to greater thaninclude equ to not equal to greater thaninclude equ to not equal to greater thanless than greater than or equal to lessless than greater than or equal to lessless than greater than or equal to lessthan or equal to between Range of valuesthan or equal to between Range of valuesthan or equal to between Range of valuesinclusive and we have like for patterinclusive and we have like for patterinclusive and we have like for pattermatching in for which matchesmatching in for which matchesmatching in for which matchesAnyAnyAnynot logical operators and In The next Thenot logical operators and In The next Thenot logical operators and In The next Thenextminutes next Let's look at each operatorminutes next Let's look at each operatorminutes next Let's look at each operatorStep by Step so Let's start with theStep by Step so Let's start with theStep by Step so Let's start with theequal to operator the equal to operatorequal to operator the equal to operatorequal to operator the equal to operatorchecks whether Two values ​​ are exactlychecks whether Two values ​​are exactlychecks whether Two values ​​are exactlyequal it is used to filter rows whereequal it is used to filter rows whereequal it is used to filter rows wherethe specified column is equal to a giventhe specified column is equal to a giventhe specified column is equal to a givenvalue example we have Select fromvalue example we have Select fromvalue example we have Select fromemployees where department equ salesemployees where department equ salesemployees where department equ salesthis qu retrieve All employees for thethis qu retrieve All employees for thethis qu retrieve All employees for thefrom the employees tables whosefrom the employees tables whosefrom the employees tables whosedepartmentdepartmentdepartmentsales next we have the not equal tosales next we have the not equal tosales next we have the not equal tooperator and the not equal to operatoroperator and the not equal to operatoroperator and the not equal to operatorchecks whether Two values ​​ are not equalchecks whether Two values ​​are not equalchecks whether Two values ​​are not equalit is used to filter rows where theit is used to filter rows where theit is used to filter rows where thespecifi column is not equal to a givenspecifi column is not equal to a givenspecifi column is not equal to a givenvalue example we have Select fromvalue example we have Select fromvalue example we have Select fromemployees Where department is not equalemployees Where department is not equalemployees Where department is not equalto Finance this quy will retrieve Allto Finance this quy will retrieve Allto Finance this quy will retrieve Allemployees from the employees table whoseemployees from the employees table whoseemployees from the employees table whosedepartment is notdepartment is notdepartment is notFinance next we have the greater thanFinance next we have the greater thanFinance next we have the greater thanoperator the greater than operatoroperator the greater than operatoroperator the greater than operatorchecks If the left hand side value ischecks If the left hand side value ischecks If the left hand side value isgreater than the right hand side valuegreater than the right hand side valuegreater than the right hand side valueit is used to filter rows where theit is used to filter rows where theit is used to filter rows where thespecify column is greater than a givenspecify column is greater than a givenspecify column is greater than a givenvalue example and we have Select fromvalue example and we have Select fromvalue example and we have Select fromemployees Where age is greater than 30employees Where age is greater than 30employees Where age is greater than 30this query will retrieve All employeesthis query will retrieve All employeesthis query will retrieve All employeesfrom the employees tables whose age isfrom the employees tables whose age isfrom the employees tables whose age isgreater than 30 next we have less thangreater than 30 next we have less thangreater than 30 next we have less thanoperator and the less than operatoroperator and the less than operatoroperator and the less than operatorchecks to see If the left hand sidechecks to see If the left hand sidechecks to see If the left hand sidevalue is less than the right hand sidevalue is less than the right hand sidevalue is less than the right hand sidevalue it is used to filter rows wherevalue it is used to filter rows wherevalue it is used to filter rows wherethe specified column is less than athe specified column is less than athe specified column is less than agiven value example we have Select fromgiven value example we have Select fromgiven value example we have Select fromemployees Where salary is less thanemployees Where salary is less thanemployees Where salary is less than500050005000500000 this qu will retrieve All500000 this qu will retrieve All500000 this qu will retrieve Allemployees from the employees table Whoemployees from the employees table Whoemployees from the employees table Whosalary is less thansalary is less thansalary is less than555000 next to have greater than or equal000 next to have greater than or equal000 next to have greater than or equalto operator and the greater than orto operator and the greater than orto operator and the greater than orequal to operator checks to see If theequal to operator checks to see If theequal to operator checks to see If theleft hand side value is greater than orleft hand side value is greater than orleft hand side value is greater than orequal to the right hand side value it isequal to the right hand side value it isequal to the right hand side value it isused to filter rows where the specifyused to filter rows where the specifyused to filter rows where the specifycolumn is greater than or equal to acolumn is greater than or equal to acolumn is greater than or equal to agiven value so it has example Selectgiven value so it has example Selectgiven value so it has example Selectfrom employees Where experience isfrom employees Where experience isfrom employees Where experience isgreater than Five this will retrieve Allgreater than Five this will retrieve Allgreater than Five this will retrieve Allemployees from the employees tablesemployees from the employees tablesemployees from the employees tableswhose experience is greater or equal towhose experience is greater or equal towhose experience is greater or equal to555yearsyearsyearsless than or equal to operator the lessless than or equal to operator the lessless than or equal to operator the lessthan or equal to operator check to seethan or equal to operator check to seethan or equal to operator check to seethe left hand side values ​​ less than orthe left hand side values ​​less than orthe left hand side values ​​less than orequal to the right hand side value it isequal to the right hand side value it isequal to the right hand side value it isused to filter rows where the specifiused to filter rows where the specifiused to filter rows where the specificolumn is less than or equal to a givencolumn is less than or equal to a givencolumn is less than or equal to a givenvalue example we have Select fromvalue example we have Select fromvalue example we have Select fromemployees Where rating is less than oremployees Where rating is less than oremployees Where rating is less than orequal to 4.5 this query will retrieveequal to 4.5 this query will retrieveequal to 4.5 this query will retrieveAll employees from the employees tableAll employees from the employees tableAll employees from the employees tablewhose rating is less than or equal towhose rating is less than or equal towhose rating is less than or equal to4.5 Five the between4.5 Five the between4.5 Five the betweenoperator the between operator checks ifoperator the between operator checks ifoperator the between operator checks ifa value lies within a specify Rangea value lies within a specify Rangea value lies within a specify Rangeinclusive it is used to filter rowsinclusive it is used to filter rowsinclusive it is used to filter rowswhere the specified column value iswhere the specified column value iswhere the specified column value iswithin a Range example we have Selectwithin a Range example we have Selectwithin a Range example we have Selectfrom employees were Age between 20 andfrom employees were Age between 20 andfrom employees were Age between 20 and40 this query will retrieve All40 this query will retrieve All40 this query will retrieve Allemployees from the employees Stableemployees from the employees Stableemployees from the employees Stablewhose age is between 20 and 40 inclusivewhose age is between 20 and 40 inclusivewhose age is between 20 and 40 inclusiveand this is very important inclusiveand this is very important inclusiveand this is very important inclusivelike operator the like operator is usedlike operator the like operator is usedlike operator the like operator is usedfor Pattern matching with wild cardfor Pattern matching with wild cardfor Pattern matching with wild cardcharacters like percent and sign orcharacters like percent and sign orcharacters like percent and sign orunderscore Where percent Sign representsunderscore Where percent Sign representsunderscore Where percent Sign representszero One or multiple characters whilezero One or multiple characters whilezero One or multiple characters whileunderscore represents a single characterunderscore represents a single characterunderscore represents a single characterexample we have Select from Selectexample we have Select from Selectexample we have Select from SelectAsterix from employees Where first nameAsterix from employees Where first nameAsterix from employees Where first namelike and put in a single quotation Marklike and put in a single quotation Marklike and put in a single quotation Markj andpery All employees from the empe tablepery All employees from the empe tablepery All employees from the empe tablewhose first name with letterwhose first name with letterwhose first name with letterj the in operator the in operator checksj the in operator the in operator checksj the in operator the in operator checksif value matches Any value in List of aif value matches Any value in List of aif value matches Any value in List of aspecify values ​​ it is used to filter rowsspecify values ​​it is used to filter rowsspecify values ​​it is used to filter rowswhere the specify column value is one ofwhere the specify column value is one ofwhere the specify column value is one ofthe given values ​​ so we have for examplethe given values ​​so we have for examplethe given values ​​so we have for exampleSelect Asterix from employees departmentSelect Asterix from employees departmentSelect Asterix from employees departmentin human resource Finance or admin andin human resource Finance or admin andin human resource Finance or admin andthis query will retrieve All employeesthis query will retrieve All employeesthis query will retrieve All employeesfrom the employees tables whosefrom the employees tables whosefrom the employees tables whosedepartment is either human resourcedepartment is either human resourcedepartment is either human resourceFinance orFinance orFinance oradmin next we have the is nal operatoradmin next we have the is nal operatoradmin next we have the is nal operatorand the is n operator checks if a valueand the is n operator checks if a valueand the is n operator checks if a valueis null nois null nois null novalue it is used to filter rows wherevalue it is used to filter rows wherevalue it is used to filter rows wherethe specified column value is null examplethe specified column value is null examplethe specified column value is null exampleSelect ASX from employees Where phoneSelect ASX from employees Where phoneSelect ASX from employees Where phonenumber isnumber isnumber isnull query will retrieve All employeesnull query will retrieve All employeesnull query will retrieve All employeesfrom the employees tables Who have notfrom the employees tables Who have notfrom the employees tables Who have notprovided a phone number where the phoneprovided a phone number where the phoneprovided a phone number where the phonenumber is null logicalnumber is null logicalnumber is null logicaland and logical or operator the logicaland and logical or operator the logicaland and logical or operator the logicallogical operators and and or are used tological operators and and or are used tological operators and and or are used tocombine multiple conditions in the workcombine multiple conditions in the workcombine multiple conditions in the workclause and requires all conditions to beclause and requires all conditions to beclause and requires all conditions to betrue for a Row to be included while ortrue for a Row to be included while ortrue for a Row to be included while orrequires AT least one condition to berequires AT least one condition to berequires AT least one condition to betrue example we have Select as fortrue example we have Select as fortrue example we have Select as foremployees department equs human resourceemployees department equs human resourceemployees department equs human resourceand age is greater thanand age is greater thanand age is greater than30 this query will reeve All employees30 this query will reeve All employees30 this query will reeve All employeesfrom the employees table whosefrom the employees table whosefrom the employees table whosedepartment human resource and the AG isdepartment human resource and the AG isdepartment human resource and the AG is30 or30 or30 orolder so next we have Select fromolder so next we have Select fromolder so next we have Select fromemployees Where department equalsemployees Where department equalsemployees Where department equalsFinance or department equsFinance or department equsFinance or department equsmarketing All employeesmarketing All employeesmarketing All employeesdepartment is Finance ordepartment is Finance ordepartment is Finance ormarketing so this are All the operatorsmarketing so this are All the operatorsmarketing so this are All the operatorsthat you can use in squ I'm gna end thisthat you can use in squ I'm gna end thisthat you can use in squ I'm gna end thisvideo here and see you In The nextvideo now Let's Open PG admin and letvideo now Let's Open PG admin and letvideo now Let's Open PG admin and letsome examples using the Select Wheresome examples using the Select Wheresome examples using the Select Wherestatement Let's Open PG admin I 'm anstatement Let's Open PG admin I 'm anstatement Let's Open PG admin I 'm anadministratoradministratoradministratornow p gna start we Wait a littlenow p gna start we Wait a littlenow p gna start we Wait a littlebitstartingp and start it Let's increase thisp and start it Let's increase thisp and start it Let's increase thiswindow let's go towindow let's go towindow let's go toservers have post gr we have our DVDservers have post gr we have our DVDservers have post gr we have our DVDrental and Let's right Click let's go torental and Let's right Click let's go torental and Let's right Click let's go tocurittool and we have the customer table heretool and we have the customer table heretool and we have the customer table hereand Let's say that I want to get All theand Let's say that I want to get All theand Let's say that I want to get All thedata from the customer so Let's Get Alldata from the customer so Let's Get Alldata from the customer so Let's Get Allthe data from the customer tablethe data from the customer tablethe data from the customer tablefromfromfromcustomer semicolon now if Run this ascustomer semicolon now if Run this ascustomer semicolon now if Run this asyou can see have customer ID Store IDyou can see have customer ID Store IDyou can see have customer ID Store IDfirst name last name email have addressfirst name last name email have addressfirst name last name email have addressID an active Bull If the user is activeID an active Bull If the user is activeID an active Bull If the user is activeor not created Edit last update andor not created Edit last update andor not created Edit last update andactive and have One andactive and have One andactive and have One andzer if notzer if notzer if notfilters what i can do I can removefilters what i can do I can removefilters what i can do I can removethe semicolonthe semicolonthe semicoloncan Select from customerWhere first name say that I want to getWhere first name say that I want to getWhere first name say that I want to getfrom the Store the person with the firstfrom the Store the person with the firstfrom the Store the person with the firstname called Mary put equs singlename called Mary put equs singlename called Mary put equs singlequotation markstyif Let me Push It back now Let's Run itif Let me Push It back now Let's Run itif Let me Push It back now Let's Run itcan semon here if you want It will stillcan semon here if you want It will stillcan semon here if you want It will stillwork with semicolon and have as you canwork with semicolon and have as you canwork with semicolon and have as you cansee the first name Mary and the lastsee the first name Mary and the lastsee the first name Mary and the lastname me and we have the email address IDname me and we have the email address IDname me and we have the email address IDthe active bullenthe active bullenthe active bullenand so on so Let's now explore the fieldand so on so Let's now explore the fieldand so on so Let's now explore the fieldtable to to to show you how What elsetable to to to show you how What elsetable to to to show you how What elseyou can do with Stats because gna lookyou can do with Stats because gna lookyou can do with Stats because gna lookalso at the logical we G look at How wealso at the logical we G look at How wealso at the logical we G look at How wecan combine condition with the logicalcan combine condition with the logicalcan combine condition with the logicaland logical operator so let changeand logical operator so let changeand logical operator so let changeSelect fromSelect fromSelect fromfilm sofilm sofilm soLet's Let's increase this Let's see theLet's Let's increase this Let's see theLet's Let's increase this Let's see thecolumns of thecolumns of thecolumns of thefilm Let's Runfilm Let's Runfilm Let's Runit and we have title description haveit and we have title description haveit and we have title description haverelease language rental duration rentalrelease language rental duration rentalrelease language rental duration rentalrate let say that I want to get Fromrate let say that I want to get Fromrate let say that I want to get Fromthis filmthis filmthis filmWhereWhereWherewhere rentalwhere rentalwhere rentalrate rental rate is greater thanrate rental rate is greater thanrate rental rate is greater than4 semicolonshould beshould beshould berental Run it again and now gna get All therental Run it again and now gna get All therental Run it again and now gna get All theFS where the rental rate is greaterFS where the rental rate is greaterFS where the rental rate is greaterthan 4 as you can see here have 499 499than 4 as you can see here have 499 499than 4 as you can see here have 499 499499 now If I want499 now If I want499 now If I wantto add more conditions here I can useto add more conditions here I can useto add more conditions here I can usethe logical end operator type Wherethe logical end operator type Wherethe logical end operator type Whererental rate is greater than 4rental rate is greater than 4rental rate is greater than 4and I can ty replacement let say Let'sand I can ty replacement let say Let'sand I can ty replacement let say Let'ssay that I want to filter to see What tosay that I want to filter to see What tosay that I want to filter to see What tosee the ones which have a higher PR forsee the ones which have a higher PR forsee the ones which have a higher PR forthe replacement for the replacement Castthe replacement for the replacement Castthe replacement for the replacement CasttypetypetypereplacementCast Cast make sure that you type thatCast Cast make sure that you type thatCast Cast make sure that you type thatcorrectly is greater than equal to 0 99correctly is greater than equal to 0 99correctly is greater than equal to 0 99semicolon if yousemicolon if yousemicolon if youwant Let's Run thes and if you go on the right here yous and if you go on the right here yous and if you go on the right here yougna see that have All replacement bso f this filing Now I Can anotherso f this filing Now I Can anotherso f this filing Now I Can anothercondition here using the end operatorcondition here using the end operatorcondition here using the end operatorand I don't need to type this Whereand I don't need to type this Whereand I don't need to type this WhereAgain because it will be applied to all theAgain because it will be applied to all theAgain because it will be applied to all theconditions which i stick themconditions which i stick themconditions which i stick themtogether the end operator so I can typetogether the end operator so I can typetogether the end operator so I can typecan type On The next linecan type On The next linecan type On The next lineand another end Let's say that I want toand another end Let's say that I want toand another end Let's say that I want toget from film where the R is greaterget from film where the R is greaterget from film where the R is greaterthan the replacement Iserthan the replacement Iserthan the replacement Iser1999 and Let's say that I want to get1999 and Let's say that I want to get1999 and Let's say that I want to getAll the movies which have the rating RAll the movies which have the rating RAll the movies which have the rating Rso I'm gna type ratingso I'm gna type ratingso I'm gna type ratingR single quotationR single quotationR single quotationmarks single quotation marks R semicolonmarks single quotation marks R semicolonmarks single quotation marks R semicolonnow Let's Run this but first Let me shownow Let's Run this but first Let me shownow Let's Run this but first Let me showyou that now we have those which alsoyou that now we have those which alsoyou that now we have those which alsodon't have R because we don't have thatdon't have R because we don't have thatdon't have R because we don't have thatcondition Let me see Where itcondition Let me see Where itcondition Let me see Where itis as can see have nc 17is as can see have nc 17is as can see have nc 17have PG have PG 13 but now if i Runhave PG have PG 13 but now if i Runhave PG have PG 13 but now if i Runthis If I go on the right now gna havethis If I go on the right now gna havethis If I go on the right now gna haveOnly the movies which have the R ratingOnly the movies which have the R ratingOnly the movies which have the R ratingas you can see here so only those moviesas you can see here so only those moviesas you can see here so only those moviesare return backare return backare return backso this condition this entire with Whereso this condition this entire with Whereso this condition this entire with Whereis gna return All the results for whichis gna return All the results for whichis gna return All the results for whichthis condition that we define thosethis condition that we define thosethis condition that we define thoseconditions that are defined here areconditions that are defined here areconditions that are defined here aretrue so for fortrue so for fortrue so for forfor all offor all offor all ofthem al right so this is what We can dothem al right so this is what We can dothem al right so this is what We can donow Let's say that I want to get sonow Let's say that I want to get sonow Let's say that I want to get soLet's say that I want to count How manyLet's say that I want to count How manyLet's say that I want to count How manymovies are with the rating are so Howmovies are with the rating are so Howmovies are with the rating are so Howmany movies are Let's saymany movies are Let's saymany movies are Let's saywith which much this particularwith which much this particularwith which much this particularcondition so I want to get All thecondition so I want to get All thecondition so I want to get All themovies so All the films for which Thismovies so All the films for which Thismovies so All the films for which Thiscondition is true so so I want to seecondition is true so so I want to seecondition is true so so I want to seehow many there are so Let's Run and Wehow many there are so Let's Run and Wehow many there are so Let's Run and WeGet 34 so there are 34 movies whichGet 34 so there are 34 movies whichGet 34 so there are 34 movies whichwhich are rating as R have the ratwhich are rating as R have the ratwhich are rating as R have the ratgreater than 4 and the replacement wasgreater than 4 and the replacement wasgreater than 4 and the replacement wasgreater than 1999 And also has Again igreater than 1999 And also has Again igreater than 1999 And also has Again irepeat my i repeat myself here ratingrepeat my i repeat myself here ratingrepeat my i repeat myself here ratingwhere the rating is a and can also putwhere the rating is a and can also putwhere the rating is a and can also puthere if youhere if youhere if youwant title It will still work becausewant title It will still work becausewant title It will still work becauseit4 rows All Right so this how canit4 rows All Right so this how canit4 rows All Right so this how cancan use the logical operatorcan use the logical operatorcan use the logical operatorSelectSelectSelectsay Let's tysay Let's tysay Let's tyherehereheretitletitletitlefromfromfromfilmWhere actually type Select from filmWhere actually type Select from filmWhere actually type Select from filmAgain Because I want to see colsAgain Because I want to see colsAgain Because I want to see colsfrom Select from film because thefrom Select from film because thefrom Select from film because thecolumns Let's Runit that I want to get the ones whichit that I want to get the ones whichit that I want to get the ones whichhave the rating R mpg or the rating pg1have the rating R mpg or the rating pg1have the rating R mpg or the rating pg1I can type hereWhere whereWhere whereWhere whereratingratingratingequ r of ty logical or operator orequ r of ty logical or operator orequ r of ty logical or operator orrating equalstototoPGPGPGDash 13 so Let's RunDash 13 so Let's RunDash 13 so Let's Runthis and notice that I don't add thethis and notice that I don't add thethis and notice that I don't add thesemicolon it still works if you don'tsemicolon it still works if you don'tsemicolon it still works if you don'twant the semicolon and now have All thewant the semicolon and now have All thewant the semicolon and now have All themovies which have the rating either R ormovies which have the rating either R ormovies which have the rating either R orp and you can also use count here countp and you can also use count here countp and you can also use count here countAll the cols to putAll the cols to putAll the cols to putcount you know this from the previouscount you know this from the previouscount you know this from the previousvideos to see how many movies are wevideos to see how many movies are wevideos to see how many movies are wehave the rating R and or P13 so if youhave the rating R and or P13 so if youhave the rating R and or P13 so if youRunRunRunthis Getthis Getthis Get4 All Right so this discussion for this4 All Right so this discussion for this4 All Right so this discussion for thisvideo and see you In The nextvideo and see you In The nextvideo and see you In The nextvideo now Do a Challenge using thevideo now Do a Challenge using thevideo now Do a Challenge using theSelect statement and this video theSelect statement and this video theSelect statement and this video theprevious videos going to have the sameprevious videos going to have the sameprevious videos going to have the samestructure so going to have first thestructure so going to have first thestructure so going to have first thebusiness situation the expected resultbusiness situation the expected resultbusiness situation the expected resultthe hints and finally going to have thethe hints and finally going to have thethe hints and finally going to have thesolution so Let's start with our firstsolution so Let's start with our firstsolution so Let's start with our firstChallenge so Challenge number one Let'sChallenge so Challenge number one Let'sChallenge so Challenge number one Let'ssay that you run a DVD rental Storesay that you run a DVD rental Storesay that you run a DVD rental Storeand want to find the titles of filmsand want to find the titles of filmsand want to find the titles of filmswhich are released in or after the yearwhich are released in or after the yearwhich are released in or after the year2000 that have a rating of PG 13 and you2000 that have a rating of PG 13 and you2000 that have a rating of PG 13 and youalso need to know the replacement costalso need to know the replacement costalso need to know the replacement costof those fms so Let's see the expectedof those fms so Let's see the expectedof those fms so Let's see the expectedresult this What see in retrieve All theresult this What see in retrieve All theresult this What see in retrieve All theand replacement c of films that wereand replacement c of films that wereand replacement c of films that werereleased in or after the year 2000 andreleased in or after the year 2000 andreleased in or after the year 2000 andhave a rating of PG 13 so this is whathave a rating of PG 13 so this is whathave a rating of PG 13 so this is whatyou should see after running thatyou should see after running thatyou should see after running thatparticularparticularparticularquery now Let's look at hints you needquery now Let's look at hints you needquery now Let's look at hints you needto filter the films based on the releaseto filter the films based on the releaseto filter the films based on the releaseyear and rating and the rating columnsyear and rating and the rating columnsyear and rating and the rating columnsthe rating column STS the films ratingthe rating column STS the films ratingthe rating column STS the films ratingthe release year columnthe release year columnthe release year columnthe film was released so those are thethe film was released so those are thethe film was released so those are thehints and Now let me show you Thehints and Now let me show you Thehints and Now let me show you Thesolution The solution is to type Selectsolution The solution is to type Selectsolution The solution is to type Selecttitle replacement Cast from film Wheretitle replacement Cast from film Wheretitle replacement Cast from film Whererelease Series greater than equal to andrelease Series greater than equal to andrelease Series greater than equal to andso the end operator rating equals toso the end operator rating equals toso the end operator rating equals topg1 so This is the solution now Let'spg1 so This is the solution now Let'spg1 so This is the solution now Let'smove to Challenge number Two Let's saymove to Challenge number Two Let's saymove to Challenge number Two Let's saythat you want to see the titles of filmsthat you want to see the titles of filmsthat you want to see the titles of filmswhich have a rating of PG andwhich have a rating of PG andwhich have a rating of PG andreplacement cost ofreplacement cost ofreplacement cost of99 so Let's see theresult achieving of PG and replacement c ofresult achieving of PG and replacement c ofresult achieving of PG and replacement c of10 repeat and the replacement c of 1099thisthisthisoutput Show solutionoutput Show solutionoutput Show solutionnot the solution Let me give you thenot the solution Let me give you thenot the solution Let me give you thehint you need to filter films based onhint you need to filter films based onhint you need to filter films based onthe rating and replacement Cast firstthe rating and replacement Cast firstthe rating and replacement Cast firstthe rating column ST the film rating andthe rating column ST the film rating andthe rating column ST the film rating andthe replacement c column represent thethe replacement c column represent thethe replacement c column represent thecost to replace a film so these are thecost to replace a film so these are thecost to replace a film so these are thehints to solve this Challenge now Thehints to solve this Challenge now Thehints to solve this Challenge now Thesolution The solution is to Select titlesolution The solution is to Select titlesolution The solution is to Select titlefrom film Where rating is PG andfrom film Where rating is PG andfrom film Where rating is PG andreplacement equ 1099 and gna solvereplacement equ 1099 and gna solvereplacement equ 1099 and gna solveChallengeChallengeChallengenumber Challenge 3 you want to find thenumber Challenge 3 you want to find thenumber Challenge 3 you want to find thetitles of releases either Before the yeartitles of releases either Before the yeartitles of releases either Before the year2000 or with a replacement cost less2000 or with a replacement cost less2000 or with a replacement cost lessthan 10than 10than 10sososoeither or Let's look at the expectedeither or Let's look at the expectedeither or Let's look at the expectedresult The gna retrieve The titles ofresult The gna retrieve The titles ofresult The gna retrieve The titles offilm that released Before the year 2000film that released Before the year 2000film that released Before the year 2000or have a replacement cost less than 10or have a replacement cost less than 10or have a replacement cost less than 10thisthisthisoutput now Let's see theoutput now Let's see theoutput now Let's see thesolution see the hint so you need tosolution see the hint so you need tosolution see the hint so you need tofilter films based on released year andfilter films based on released year andfilter films based on released year andreplacement c the release year columnreplacement c the release year columnreplacement c the release year columnstor the years the year of film wasstor the years the year of film wasstor the years the year of film wasreleased and the replacement c columnreleased and the replacement c columnreleased and the replacement c columnrepresents the cost of a repl to replacerepresents the cost of a repl to replacerepresents the cost of a repl to replacea film so the hands let see The solutiona film so the hands let see The solutiona film so the hands let see The solutionThe solution is to type Select titleThe solution is to type Select titleThe solution is to type Select titlefrom film Where release year is lessfrom film Where release year is lessfrom film Where release year is lessthan 2000 or replacement cost less thanthan 2000 or replacement cost less thanthan 2000 or replacement cost less than10 so I'm going to end this video here10 so I'm going to end this video here10 so I'm going to end this video hereand see you The nextvideo welcome back now it's time tovideo welcome back now it's time tovideo welcome back now it's time tostart a discussion about the countstart a discussion about the countstart a discussion about the countfunction but first let's go through eachfunction but first let's go through eachfunction but first let's go through eachaspect of the count function and sequelaspect of the count function and sequelaspect of the count function and sequelStep by Step using some simple tables toStep by Step using some simple tables toStep by Step using some simple tables togras the information better so the countgras the information better so the countgras the information better so the countfunction in sql the count function infunction in sql the count function infunction in sql the count function insql is an aggregate function that allowssql is an aggregate function that allowssql is an aggregate function that allowsyou to count the number of rows thatyou to count the number of rows thatyou to count the number of rows thatmatch specifi condition within a tablematch specifi condition within a tablematch specifi condition within a tableor a result set it returns a singleor a result set it returns a singleor a result set it returns a singlevalue representing the count of rowsvalue representing the count of rowsvalue representing the count of rowsthat Meet the criteria so this is whatthat Meet the criteria so this is whatthat Meet the criteria so this is whatthe count function is why is countthe count function is why is countthe count function is why is countfunction useful the count function isfunction useful the count function isfunction useful the count function isvaluable for various data analysis taskvaluable for various data analysis taskvaluable for various data analysis taskit helps you to obtain statisticalit helps you to obtain statisticalit helps you to obtain statisticalinformation about your data such asinformation about your data such asinformation about your data such ascounting the number of records in acounting the number of records in acounting the number of records in atable calculating the occurrence of atable calculating the occurrence of atable calculating the occurrence of aspecific values ​​ in a column Finding thespecific values ​​in a column Finding thespecific values ​​in a column Finding thenumber of rows that certain conditionsnumber of rows that certain conditionsnumber of rows that certain conditionsdetermine the number of Unique values ​​ indetermine the number of Unique values ​​indetermine the number of Unique values ​​ina column When used with a Distincta column When used with a Distincta column When used with a Distinctstatement so This is Why count functionstatement so This is Why count functionstatement so This is Why count functionis useful for thisis useful for thisis useful for thisreasons When to use the count functionreasons When to use the count functionreasons When to use the count functionyou should use the count function whenyou should use the count function whenyou should use the count function whenyou need to determine the size of ayou need to determine the size of ayou need to determine the size of atable the number of of rows it containstable the number of of rows it containstable the number of of rows it containscount occurrences of specific values ​​ incount occurrences of specific values ​​incount occurrences of specific values ​​ina column Get the summary sta statistica column Get the summary sta statistica column Get the summary sta statisticabout your data so This is where youabout your data so This is where youabout your data so This is where youshould use the countshould use the countshould use the countfunction How to use the count functionfunction How to use the count functionfunction How to use the count functionwith the Distinct statement thiswith the Distinct statement thiswith the Distinct statement thisDistinct keyword is used to eliminateDistinct keyword is used to eliminateDistinct keyword is used to eliminateduplicate rows from the result set Whenduplicate rows from the result set Whenduplicate rows from the result set Whencombined with count function it allowscombined with count function it allowscombined with count function it allowsyou to count the number of Unique valuesyou to count the number of Unique valuesyou to count the number of Unique valuesin a specific column here is How we canin a specific column here is How we canin a specific column here is How we canuse it Let's consider a simple tableuse it Let's consider a simple tableuse it Let's consider a simple tablecalled employees as you can see here youcalled employees as you can see here youcalled employees as you can see here youhave this table have the employee ID Thehave this table have the employee ID Thehave this table have the employee ID Theemployee Name the department andand as you can see In this table we haveand as you can see In this table we haveand as you can see In this table we havefive rows and you have multiplefive rows and you have multiplefive rows and you have multipledepartments but you have duplicatedepartments but you have duplicatedepartments but you have duplicatedepartments as you can see here you havedepartments as you can see here you havedepartments as you can see here you haveThe Human resources twice and the itThe Human resources twice and the itThe Human resources twice and the italso you have it twice now Let's sayalso you have it twice now Let's sayalso you have it twice now Let's saythat I want to count the numberthat I want to count the numberthat I want to count the numberofofofemployees table If I do that the countemployees table If I do that the countemployees table If I do that the countis gna return Five and it's gna returnis gna return Five and it's gna returnis gna return Five and it's gna returnFive because have have five Five rows soFive because have have five Five rows soFive because have have five Five rows sothis is what that will return nowthis is what that will return nowthis is what that will return nowexample Two Let's count the number ofexample Two Let's count the number ofexample Two Let's count the number ofUnique departments in the employee tableUnique departments in the employee tableUnique departments in the employee tableusingusingusingDistinct keyword so we gna type SelectDistinct keyword so we gna type SelectDistinct keyword so we gna type Selectcountcountcountparentheses distinct department fromparentheses distinct department fromparentheses distinct department fromemployees and when you gna do thatemployees and when you gna do thatemployees and when you gna do thatthe result is gna be One because we havethe result is gna be One because we havethe result is gna be One because we havehuman resource Two for Finance and threehuman resource Two for Finance and threehuman resource Two for Finance and threefor it so we have three Uniquefor it so we have three Uniquefor it so we have three Uniquedepartments in ourdepartments in ourdepartments in ourdepartment table on our dep departmentdepartment table on our dep departmentdepartment table on our dep departmentRow so This is how you can use thatand Let'sWait In this example use the countWait In this example use the countWait In this example use the countfunction withfunction withfunction withDistinct statement to count the Unique departmentDistinct statement to count the Unique departmentDistinct statement to count the Unique departmentemployee table it return theemployee table it return theemployee table it return thecount as because there are three Uniquecount as because there are three Uniquecount as because there are three Uniquedepartments human resources it anddepartments human resources it anddepartments human resources it andFinance us of us and useful of the countFinance us of us and useful of the countFinance us of us and useful of the countfunction and particularly When combinefunction and particularly When combinefunction and particularly When combinewith distin statement it allows you towith distin statement it allows you towith distin statement it allows you toperform various data analysis task andperform various data analysis task andperform various data analysis task andretrieve valuable information from yourretrieve valuable information from yourretrieve valuable information from yourdatabase alr now Let's move to post thedatabase alr now Let's move to post thedatabase alr now Let's move to post themistake to to type some some code butmistake to to type some some code butmistake to to type some some code butBefore you open PG admin Let me firstBefore you open PG admin Let me firstBefore you open PG admin Let me firstexplain What is the difference betweenexplain What is the difference betweenexplain What is the difference betweencount with as and count without so countcount with as and count without so countcount with as and count without so countwithwithwithand us of count function with andand us of count function with andand us of count function with anddepends on to let explore thedepends on to let explore thedepends on to let explore thedifferences and When to use form countdifferences and When to use form countdifferences and When to use form countwith parentheses and Aster When You usewith parentheses and Aster When You usewith parentheses and Aster When You usecount with parentheses and Aster itcount with parentheses and Aster itcount with parentheses and Aster itCounts All the rows in the specifiedCounts All the rows in the specifiedCounts All the rows in the specifiedtable regardless of the values ​​ in thetable regardless of the values ​​in thetable regardless of the values ​​in thecolumns here is the syntax wecolumns here is the syntax wecolumns here is the syntax wehave count pares Aster from and thehave count pares Aster from and thehave count pares Aster from and thetabletabletablename thename thename theusage use count with pares When You wantusage use count with pares When You wantusage use count with pares When You wantto count the total number of rows in ato count the total number of rows in ato count the total number of rows in atable irr of whether there are nulltable irr of whether there are nulltable irr of whether there are nullvalues ​​ or not it Counts All rowsvalues ​​or not it Counts All rowsvalues ​​or not it Counts All rowsincluding those with null values ​​ in Anyincluding those with null values ​​in Anyincluding those with null values ​​in Anycolumn so this is what countcolumn so this is what countcolumn so this is what countdoes Let's consider an example let saydoes Let's consider an example let saydoes Let's consider an example let saythatthatthathave a tablehave a tablehave a tablefor tables called students and we havefor tables called students and we havefor tables called students and we havethe student ID student name and Age andthe student ID student name and Age andthe student ID student name and Age andas you can see for the student name weas you can see for the student name weas you can see for the student name wehave here an value so didn't provide ahave here an value so didn't provide ahave here an value so didn't provide aname now that not that isname now that not that isname now that not that isbecause by using count withbecause by using count withbecause by using count withASX It will count even if you count thatASX It will count even if you count thatASX It will count even if you count thatyou want to count the total numbers theyou want to count the total numbers theyou want to count the total numbers thetotal number oftotal number oftotal number ofof student names It will count also Oneof student names It will count also Oneof student names It will count also Oneif use countif use countif use countwith count with out Aster x so When towith count with out Aster x so When towith count with out Aster x so When touse count with the column name so youuse count with the column name so youuse count with the column name so youspecify a column name inside them insidespecify a column name inside them insidespecify a column name inside them insidethe parentheses of the count and itthe parentheses of the count and itthe parentheses of the count and itCounts the number of non null values ​​ inCounts the number of non null values ​​inCounts the number of non null values ​​inthat particular column here is the syntaxthat particular column here is the syntaxthat particular column here is the syntaxso This is this only number of non nullso This is this only number of non nullso This is this only number of non nullvalues ​​ usage you use count with columnvalues ​​usage you use count with columnvalues ​​usage you use count with columnname when you want to count thename when you want to count thename when you want to count theoccurrence of non null values ​​ in aoccurrence of non null values ​​in aoccurrence of non null values ​​in aspecific column it ignores n values ​​ in thespecific column it ignores n values ​​in thespecific column it ignores n values ​​in thecolumn and only Counts non null entriescolumn and only Counts non null entriescolumn and only Counts non null entriesexample and continuing with the studentsexample and continuing with the studentsexample and continuing with the studentstable Let me show you let Wait a littletable Let me show you let Wait a littletable Let me show you let Wait a littlebit if you type Selectbit if you type Selectbit if you type Selectcount Let's make this Let me somethingcount Let's make this Let me somethingcount Let's make this Let me somethinghere if you type Select count thehere if you type Select count thehere if you type Select count thestudent name in parentheses fromstudent name in parentheses fromstudent name in parentheses fromstudents the count is gonna return 4students the count is gonna return 4students the count is gonna return 4because the count without Aster x isbecause the count without Aster x isbecause the count without Aster x isreturning all the non null values ​​ soreturning all the non null values ​​soreturning all the non null values ​​sobecause the student name Five with thebecause the student name Five with thebecause the student name Five with theide Five is null It's not gna count iside Five is null It's not gna count iside Five is null It's not gna count isgna return for so this isgna return for so this isgna return for so this iswhat count with the column columnwhat count with the column columnwhat count with the column columnname is doing All Right Let's move to Thename is doing All Right Let's move to Thename is doing All Right Let's move to Thenext now In this query count studentnext now In this query count studentnext now In this query count studentname only Counts the n values ​​ in thename only Counts the n values ​​in thename only Counts the n values ​​in thestudent name column excluded in the Rowstudent name column excluded in the Rowstudent name column excluded in the Rowwith n name hence it returns the countwith n name hence it returns the countwith n name hence it returns the countof four When to use eachof four When to use eachof four When to use eachform use count without Asterix When Youform use count without Asterix When Youform use count without Asterix When Youwant to count All rows with with asterixwant to count All rows with with asterixwant to count All rows with with asterixSorry including those with null valuesSorry including those with null valuesSorry including those with null valuesor or When You want When You don't haveor or When You want When You don't haveor or When You want When You don't havea specific column to count but you stilla specific column to count but you stilla specific column to count but you stillneed to retrieve the total number ofneed to retrieve the total number ofneed to retrieve the total number ofrows in the table use count column namerows in the table use count column namerows in the table use count column namewhen you want to count the occurrenceswhen you want to count the occurrenceswhen you want to count the occurrencesof non null values ​​ in a particularof non null values ​​in a particularof non null values ​​in a particularcolumn ignoring null entries This iscolumn ignoring null entries This iscolumn ignoring null entries This isuseful When You want to analyze theuseful When You want to analyze theuseful When You want to analyze theDistribution of frequency of non nullDistribution of frequency of non nullDistribution of frequency of non nullvalues ​​ in a specific attribute invalues ​​in a specific attribute invalues ​​in a specific attribute insummary the decision to use the countsummary the decision to use the countsummary the decision to use the countwithoutwithoutwithoutwithwithwithororordepend All RS including or to Only thedepend All RS including or to Only thedepend All RS including or to Only theoccurrences of non values ​​ in a specificoccurrences of non values ​​in a specificoccurrences of non values ​​in a specificcolumn and you can choose the appropriatecolumn and you can choose the appropriatecolumn and you can choose the appropriateform based on your analysisform based on your analysisform based on your analysisneeds right Let's move to The nextneeds right Let's move to The nextneeds right Let's move to The nextslide in summary count with Counts Allslide in summary count with Counts Allslide in summary count with Counts Allrows In The table including rows with nrows In The table including rows with nrows In The table including rows with nvalues ​​ Any column and count with columnvalues ​​Any column and count with columnvalues ​​Any column and count with columnname Counts the number of non nullname Counts the number of non nullname Counts the number of non nullvalues ​​ in the specified column ignoringvalues ​​in the specified column ignoringvalues ​​in the specified column ignoringrows with n null values ​​ in that columnrows with n null values ​​in that columnrows with n null values ​​in that columnchoose the appropriate form of the countchoose the appropriate form of the countchoose the appropriate form of the countfunction based on your specificfunction based on your specificfunction based on your specificrequirements if you want to count All therequirements if you want to count All therequirements if you want to count All therows in the table regardless of therows in the table regardless of therows in the table regardless of thenull values ​​ use count with Asterix ifnull values ​​use count with Asterix ifnull values ​​use count with Asterix ifyou want to count the number of non nullyou want to count the number of non nullyou want to count the number of non nullvalues ​​ in a specified column use countvalues ​​in a specified column use countvalues ​​in a specified column use countwith the column name All Right now Let'swith the column name All Right now Let'swith the column name All Right now Let'sOpen PG admin right Let's Open PG adminnow Let's Run it asadministrator Let's open the serverLet's open the curytool here I want to get All the datatool here I want to get All the datatool here I want to get All the datafrom the payment table typeSelectfrompayment semicolonRun right now say that I want to knowRun right now say that I want to knowRun right now say that I want to knowthe number of rows In this table what ithe number of rows In this table what ithe number of rows In this table what ican do is I can typecan do is I can typecan do is I can typehereherehereSelectSelectSelectcountcountcountAsterix putParisParisParisAsterix so ParisAsterix so ParisAsterix so ParisAgainAgainAgainAsterixAsterixAsterixfrompayment and that is gna the number ofpayment and that is gna the number ofpayment and that is gna the number ofrows let cck on the Run and we have4596 Now can do another can ty amount4596 Now can do another can ty amount4596 Now can do another can ty amountthe name of of athe name of of athe name of of atable not the name of Lettable not the name of Lettable not the name of Letme column and that will gna return theme column and that will gna return theme column and that will gna return thesame result as you can see heresame result as you can see heresame result as you can see hereeverything works fineeverything works fineeverything works finenow next Let's say that I want tonow next Let's say that I want tonow next Let's say that I want toretrieve the actual number of the Uniqueretrieve the actual number of the Uniqueretrieve the actual number of the Uniqueamounts so I'm gna type hereamounts so I'm gna type hereamounts so I'm gna type hereSelectDistinct amount Chrome payment so if youDistinct amount Chrome payment so if youDistinct amount Chrome payment so if youRun this Get our Distinct amounts nowRun this Get our Distinct amounts nowRun this Get our Distinct amounts nowLet's say that I want to get the numberLet's say that I want to get the numberLet's say that I want to get the numberofofofthosethosethoseamounts do that this part isna theamounts do that this part isna theamounts do that this part isna thenumber of amounts so we need to put herenumber of amounts so we need to put herenumber of amounts so we need to put hereBeforeBeforeBeforecountcountcountcountparentheses you put our Distinct insideparentheses you put our Distinct insideparentheses you put our Distinct insidethe parentheses Distinct amount becausethe parentheses Distinct amount becausethe parentheses Distinct amount becauseG return the Distinct amountG return the Distinct amountG return the Distinct amountof payments andnow 19 and you can also put also this innow 19 and you can also put also this innow 19 and you can also put also this inpares to make it more Clear anotherpares to make it more Clear anotherpares to make it more Clear anotherpareshere now if Runhere now if Runhere now if Runthis Get also 19 so this returns thethis Get also 19 so this returns thethis Get also 19 so this returns thenumber of Unique amounts in our paymentnumber of Unique amounts in our paymentnumber of Unique amounts in our paymentand the count is counting them so I'm going toand the count is counting them so I'm going toand the count is counting them so I'm going toend this video here and see In Theend this video here and see In Theend this video here and see In Thenext video now time to start anext video now time to start anext video now time to start adiscussion about the Order by Close sodiscussion about the Order by Close sodiscussion about the Order by Close sothe Order by Close in post grle is usedthe Order by Close in post grle is usedthe Order by Close in post grle is usedto sort the resolve set of a query in ato sort the resolve set of a query in ato sort the resolve set of a query in aspecified Order it is used When You wantspecified Order it is used When You wantspecified Order it is used When You wantthe queries output to be presented in athe queries output to be presented in athe queries output to be presented in aparticular sequence based on One or moreparticular sequence based on One or moreparticular sequence based on One or morecolumns values ​​ this Can Be crucial forcolumns values ​​this Can Be crucial forcolumns values ​​this Can Be crucial formaking query results More meaningful andmaking query results More meaningful andmaking query results More meaningful andeasier toeasier toeasier tointerpret now Why do you need to use theinterpret now Why do you need to use theinterpret now Why do you need to use theOrder by first we have data presentationOrder by first we have data presentationOrder by first we have data presentationso When display query res results toso When display query res results toso When display query res results touser it is Often important to have datauser it is Often important to have datauser it is Often important to have datapresented in a logical and readablepresented in a logical and readablepresented in a logical and readableOrder for example if display a list ofOrder for example if display a list ofOrder for example if display a list ofProducts you might want to show them inProducts you might want to show them inProducts you might want to show them ina sending or descending Order of pricea sending or descending Order of pricea sending or descending Order of pricesecond we have analysis in cases Wheresecond we have analysis in cases Wheresecond we have analysis in cases Wherewe performing data analysis sorting thewe performing data analysis sorting thewe performing data analysis sorting theresults can help you identify trendsresults can help you identify trendsresults can help you identify trendsoutliers or patterns more easily foroutliers or patterns more easily foroutliers or patterns more easily forinstance When analyzing sales datainstance When analyzing sales datainstance When analyzing sales datasorting by the date Help identify salessorting by the date Help identify salessorting by the date Help identify salespatterns over time next we havepatterns over time next we havepatterns over time next we havepagination so paginationof proper sting Essential to ensure dataof proper sting Essential to ensure dataof proper sting Essential to ensure dataconsistency between pagesconsistency between pagesconsistency between pagesso this p now usage of Order by the of theso this p now usage of Order by the of theso this p now usage of Order by the of theOrder by cl follow we have SelectOrder by cl follow we have SelectOrder by cl follow we have Selecthave the colum or the cols from wherehave the colum or the cols from wherehave the colum or the cols from wherecan put a condition if you want and typecan put a condition if you want and typecan put a condition if you want and typeOrder bu and by colum and we define theOrder bu and by colum and we define theOrder bu and by colum and we define theOrder being ascending or descending andOrder being ascending or descending andOrder being ascending or descending andcan also use Two columns if you want socan also use Two columns if you want socan also use Two columns if you want soThis is the syntax of the Order byThis is the syntax of the Order byThis is the syntax of the Order bynow here Is What each component does sonow here Is What each component does sonow here Is What each component does soSelect specify the columns you want toSelect specify the columns you want toSelect specify the columns you want toretrieve from the table from specify theretrieve from the table from specify theretrieve from the table from specify thetable from which you retrieve data Where istable from which you retrieve data Where istable from which you retrieve data Where isop an optional condition to filter theop an optional condition to filter theop an optional condition to filter therows Before sorting Order by specify therows Before sorting Order by specify therows Before sorting Order by specify thecolumns by the res result set should becolumns by the res result set should becolumns by the res result set should besortedand can sending is default or descendingand can sending is default or descendingand can sending is default or descendingOrder so now Let's look at an example inOrder so now Let's look at an example inOrder so now Let's look at an example inPGadmin now Let's work with some examplesadmin now Let's work with some examplesadmin now Let's work with some examplesin PG admin and for this purpose I'm gnain PG admin and for this purpose I'm gnain PG admin and for this purpose I'm gnause the customer table I'm gna Selectuse the customer table I'm gna Selectuse the customer table I'm gna Selecteverything from the customer table I'm Geverything from the customer table I'm Geverything from the customer table I'm GtypetypetypeSelectSelectSelectfrom customer and the optional semicolonfrom customer and the optional semicolonfrom customer and the optional semicolonLet's Run thisLet's Run thisLet's Run thisand you you see in the data output weand you you see in the data output weand you you see in the data output wehave the customer ID Store ID first namehave the customer ID Store ID first namehave the customer ID Store ID first namelast name email address and active Bulllast name email address and active Bulllast name email address and active Bulland create it so but now the data is notand create it so but now the data is notand create it so but now the data is notbeing the entries are not beingbeing the entries are not beingbeing the entries are not beingdisplayed in Any particular Order Let'sdisplayed in Any particular Order Let'sdisplayed in Any particular Order Let'ssay that I want to display All thesay that I want to display All thesay that I want to display All thecustomers In this table and I want tocustomers In this table and I want tocustomers In this table and I want toOrder them by their first name in Order toOrder them by their first name in Order toOrder them by their first name in Order todo that I go at the end of ourdo that I go at the end of ourdo that I go at the end of ourstatement or On The next line if youstatement or On The next line if youstatement or On The next line if youwant but I'm gna Go here I'm gna typewant but I'm gna Go here I'm gna typewant but I'm gna Go here I'm gna typeOrder byOrder byOrder byand now I'm gna specify the colum gnaand now I'm gna specify the colum gnaand now I'm gna specify the colum gnawith gna perform the ord first und namewith gna perform the ord first und namewith gna perform the ord first und nameand optional semicolonand optional semicolonand optional semicolonsemicolon runsemicolon runsemicolon runs and now you can see They are sorteds and now you can see They are sorteds and now you can see They are sortedascending by their first name as can seeascending by their first name as can seeascending by their first name as can seea and now have the names starting with ba and now have the names starting with ba and now have the names starting with band so on and have this ID keep in mindand so on and have this ID keep in mindand so on and have this ID keep in mindID because we are going to use this anotherID because we are going to use this anotherID because we are going to use this anotherexample but Now let me show you thatexample but Now let me show you thatexample but Now let me show you thathere you can type ascending Order but as Ihere you can type ascending Order but as Ihere you can type ascending Order but as Isaid This is by default the Order usedsaid This is by default the Order usedsaid This is by default the Order usedif you don't specify Anyif you don't specify Anyif you don't specify AnyOrder Now You May be wondering Why youOrder Now You May be wondering Why youOrder Now You May be wondering Why youshould type this if this is by defaultshould type this if this is by defaultshould type this if this is by defaultuse this Can Be helpful if say someoneuse this Can Be helpful if say someoneuse this Can Be helpful if say someoneelse comes to look at your code and seeelse comes to look at your code and seeelse comes to look at your code and seethat you want to specify your Order to bethat you want to specify your Order to bethat you want to specify your Order to bein ascending Order and youin ascending Order and youin ascending Order and youcan specify descending or herecan specify descending or herecan specify descending or heredescending now if Run this as you candescending now if Run this as you candescending now if Run this as you cansee now starting from z and have w v tsee now starting from z and have w v tsee now starting from z and have w v tand so on so you can do this if you wantand so on so you can do this if you wantand so on so you can do this if you wantnow Let's Let's say that I want to have tonow Let's Let's say that I want to have tonow Let's Let's say that I want to have tohave our our customer being ST beinghave our our customer being ST beinghave our our customer being ST beingbeing sorted by the Store ID What canbeing sorted by the Store ID What canbeing sorted by the Store ID What cancan Order by Storecan Order by Storecan Order by StoreID so StoreID so StoreID so StoreID and the semicolon if you want or canID and the semicolon if you want or canID and the semicolon if you want or candelete that because work Let's Run it sodelete that because work Let's Run it sodelete that because work Let's Run it sonow They are by the Store ID but as younow They are by the Store ID but as younow They are by the Store ID but as youcan see youcan see youcan see youhave you have so on and if keep Rollinghave you have so on and if keep Rollinghave you have so on and if keep Rollingeventually get to Store ID so have hereeventually get to Store ID so have hereeventually get to Store ID so have herewe have Store ID as you can see nowwe have Store ID as you can see nowwe have Store ID as you can see nowstarting Now They are They are sortedstarting Now They are They are sortedstarting Now They are They are sortedby Store ID but the problem is are notby Store ID but the problem is are notby Store ID but the problem is are notwith the ID not with the Store withwith the ID not with the Store withwith the ID not with the Store withStore because as you can see haveStore because as you can see haveStore because as you can see havemultiple customers belonging to to themultiple customers belonging to to themultiple customers belonging to to thesame Store so I can do here I can typesame Store so I can do here I can typesame Store so I can do here I can typeOrder by Store ID And also Order byOrder by Store ID And also Order byOrder by Store ID And also Order byfirst undfirst undfirst undname semicolon if you want so Let'sname semicolon if you want so Let'sname semicolon if you want so Let'sRun now have One and now They are s withRun now have One and now They are s withRun now have One and now They are s withthe Store sorted first by the Store IDthe Store sorted first by the Store IDthe Store sorted first by the Store IDand then by the first name as you canand then by the first name as you canand then by the first name as you cansee here have One and we have a and thensee here have One and we have a and thensee here have One and we have a and thenb so everything works fine Let's keepb so everything works fine Let's keepb so everything works fine Let's keepscrolling and gna see the number Twoscrolling and gna see the number Twoscrolling and gna see the number Twobeing shown as you can see here now sobeing shown as you can see here now sobeing shown as you can see here now soeverything works fine Now let see that Ieverything works fine Now let see that Ieverything works fine Now let see that Iwant to get in our Select statement Onlywant to get in our Select statement Onlywant to get in our Select statement Onlythe Storethe Storethe StoreID so Only the StoreID the firstID the firstID the firstnamenamenameand the lastname right so Let's Run this Let's pname right so Let's Run this Let's pname right so Let's Run this Let's pthis On The nextthis On The nextthis On The nextline Let's Push It only this right Let'sline Let's Push It only this right Let'sline Let's Push It only this right Let'sRunRunRunit now we have the Store ID first nameit now we have the Store ID first nameit now we have the Store ID first nameand last name so now We Get in thoseand last name so now We Get in thoseand last name so now We Get in thosecolumns that specify in the Selectcolumns that specify in the Selectcolumns that specify in the Selectstatement and Get the data we Order itstatement and Get the data we Order itstatement and Get the data we Order itby the Store ID and then by the firstby the Store ID and then by the firstby the Store ID and then by the firstname and can specify here ascending andname and can specify here ascending andname and can specify here ascending andsay descending if you want so Let's typesay descending if you want so Let's typesay descending if you want so Let's typewith upper case letters Let's Run it andwith upper case letters Let's Run it andwith upper case letters Let's Run it andnow They are sort as can here firstnow They are sort as can here firstnow They are sort as can here firstascending so the ID is ascending so youascending so the ID is ascending so youascending so the ID is ascending so youhave One then Two as you can see herehave One then Two as you can see herehave One then Two as you can see hereLet's scroll you have Two now If I goLet's scroll you have Two now If I goLet's scroll you have Two now If I goback as you can see you have the firstback as you can see you have the firstback as you can see you have the firstname sortedname sortedname sortedsorted and the sorted descending ST fromsorted and the sorted descending ST fromsorted and the sorted descending ST fromso Let's also type here alsoso Let's also type here alsoso Let's also type here alsosending so Let's Run itsending so Let's Run itsending so Let's Run itso now we have One and so on so nowso now we have One and so on so nowso now we have One and so on so noweverything is is fine one more thingeverything is is fine one more thingeverything is is fine one more thingthat you can do is that if you don'tthat you can do is that if you don'tthat you can do is that if you don'tspecify your Select statement columnspecify your Select statement columnspecify your Select statement columnLet's say But I sort using that columnLet's say But I sort using that columnLet's say But I sort using that columnIt will still work so if you Run this asIt will still work so if you Run this asIt will still work so if you Run this asyou can see the Store ID is not Presentyou can see the Store ID is not Presentyou can see the Store ID is not Presentin the Select statement but Present herein the Select statement but Present herein the Select statement but Present herein the Order by and it's ordered by thein the Order by and it's ordered by thein the Order by and it's ordered by theStore ID and everything works fine AllStore ID and everything works fine AllStore ID and everything works fine AllRight this our discussion about theRight this our discussion about theRight this our discussion about theOrder buy and see you In The nextOrder buy and see you In The nextOrder buy and see you In The nextvideovideovideowelcome back now start a discussionwelcome back now start a discussionwelcome back now start a discussionabout the limit command in post gresulabout the limit command in post gresulabout the limit command in post gresulSo what is the limit command in postSo what is the limit command in postSo what is the limit command in postgresul the limit command post grul is usgresul the limit command post grul is usgresul the limit command post grul is usto restrict the number of rows return byto restrict the number of rows return byto restrict the number of rows return bya query it allows you to specify aa query it allows you to specify aa query it allows you to specify amaximum number of rows to be included in themaximum number of rows to be included in themaximum number of rows to be included in theresult set this Can Be particularlyresult set this Can Be particularlyresult set this Can Be particularlyuseful When we have a large data set anduseful When we have a large data set anduseful When we have a large data set andonly need to retrieve a subset of theonly need to retrieve a subset of theonly need to retrieve a subset of thedata for analysis or display purposes sodata for analysis or display purposes sodata for analysis or display purposes sothis is what the limit command doesthis is what the limit command doesthis is what the limit command doesnow H is how the limit Command Selectnow H is how the limit Command Selectnow H is how the limit Command Selectkeyword specify the column column onekeyword specify the column column onekeyword specify the column column onecolumn two from the table name and wecolumn two from the table name and wecolumn two from the table name and wehave limit and the number of rows sohave limit and the number of rows sohave limit and the number of rows sospecify the number of rows that you wantspecify the number of rows that you wantspecify the number of rows that you wantto have return so In this sql quy weto have return so In this sql quy weto have return so In this sql quy wehave the column one column two this thehave the column one column two this thehave the column one column two this thecolumn you want to retrieve the datacolumn you want to retrieve the datacolumn you want to retrieve the datafrom next we have the table name thefrom next we have the table name thefrom next we have the table name thename of the table you want to query andname of the table you want to query andname of the table you want to query andthe number of rows and the maximumthe number of rows and the maximumthe number of rows and the maximumnumber of rows you want to retrieve fornumber of rows you want to retrieve fornumber of rows you want to retrieve forexample if you have a table nameexample if you have a table nameexample if you have a table nameemployees and You want to retrieve theemployees and You want to retrieve theemployees and You want to retrieve thefirst 10 employee you use the followingfirst 10 employee you use the followingfirst 10 employee you use the followingqu so tye Select first name last namequ so tye Select first name last namequ so tye Select first name last namefrom employees limit 10 so This is gonnafrom employees limit 10 so This is gonnafrom employees limit 10 so This is gonnaretrieve Only the first 10 entries from theretrieve Only the first 10 entries from theretrieve Only the first 10 entries from thetable now the limit command istable now the limit command istable now the limit command isimportant for several reasons first weimportant for several reasons first weimportant for several reasons first wehave performance optimization Whenhave performance optimization Whenhave performance optimization Whendealing with large data sets fetchingdealing with large data sets fetchingdealing with large data sets fetchingAll rows can consume significantAll rows can consume significantAll rows can consume significantresources and slow down your databaseresources and slow down your databaseresources and slow down your databaseusing limit allows you to FCH Only theusing limit allows you to FCH Only theusing limit allows you to FCH Only thenecessary amount of data improving querynecessary amount of data improving querynecessary amount of data improving queryperformance second we have pagination inperformance second we have pagination inperformance second we have pagination inwe application you Often need to displaywe application you Often need to displaywe application you Often need to displaydata in chunks such as in pages bydata in chunks such as in pages bydata in chunks such as in pages bycombining limit with offset Close youcombining limit with offset Close youcombining limit with offset Close youcan implement pagination easily thecan implement pagination easily thecan implement pagination easily theoffset specif Where to start fetchingoffset specif Where to start fetchingoffset specif Where to start fetchingrows from resources next we haverows from resources next we haverows from resources next we haveresources management so three byresources management so three byresources management so three bylimiting the number of Row return youlimiting the number of Row return youlimiting the number of Row return youcan reduce the memory and Networkcan reduce the memory and Networkcan reduce the memory and Networkresources required to process andresources required to process andresources required to process andtransmit the query results so these are thetransmit the query results so these are thetransmit the query results so these are thethree now What is pagination i alsothree now What is pagination i alsothree now What is pagination i alsoshowed in the last video I think I spokeshowed in the last video I think I spokeshowed in the last video I think I spokeabout pagination But I didn't say Whatabout pagination But I didn't say Whatabout pagination But I didn't say Whatis so pagination pagination is ais so pagination pagination is ais so pagination pagination is atechnique using web application andtechnique using web application andtechnique using web application anddatabases to display and Navigatedatabases to display and Navigatedatabases to display and Navigatethrough large set of data in smallerthrough large set of data in smallerthrough large set of data in smallermanage chunks of pages it involvesmanage chunks of pages it involvesmanage chunks of pages it involvesBreaking down large data set intoBreaking down large data set intoBreaking down large data set intosmaller smaller subset typically with asmaller smaller subset typically with asmaller smaller subset typically with afixed number of items per page tofixed number of items per page tofixed number of items per page toimprove user experience and performanceimprove user experience and performanceimprove user experience and performancewhen you encounter a web page with awhen you encounter a web page with awhen you encounter a web page with alist of items such as search res bloglist of items such as search res bloglist of items such as search res blogpost or Products you Often see links orpost or Products you Often see links orpost or Products you Often see links orbuttons at the button to Navigatebuttons at the button to Navigatebuttons at the button to Navigatebetween pages of the list thisbetween pages of the list thisbetween pages of the list thisnavigation is a form of pagination eachnavigation is a form of pagination eachnavigation is a form of pagination eachpage typically displays a subset of thepage typically displays a subset of thepage typically displays a subset of thetotal data mak it easier for users tototal data mak it easier for users tototal data mak it easier for users tobrowse browse to the content withoutbrowse browse to the content withoutbrowse browse to the content withoutbeing over so pagination basically meansbeing over so pagination basically meansbeing over so pagination basically meansto f only a limit amount of data andto f only a limit amount of data andto f only a limit amount of data andthis Can Be useful Let's say that youthis Can Be useful Let's say that youthis Can Be useful Let's say that youhave a list and You want to display somehave a list and You want to display somehave a list and You want to display somedata and that list you will displaydata and that list you will displaydata and that list you will displayLet's say specific item of specificLet's say specific item of specificLet's say specific item of specificnumber of items in that list and Whennumber of items in that list and Whennumber of items in that list and Whenthe user reaches the butrequest some more data and so on so thisrequest some more data and so on so thisrequest some more data and so on so thisin This Way You don't FCH the whole datain This Way You don't FCH the whole datain This Way You don't FCH the whole dataat Once you get Only the Get only chunksat Once you get Only the Get only chunksat Once you get Only the Get only chunksof data When and Get the data When Theof data When and Get the data When Theof data When and Get the data When Theusers reaches The bottom of the list sousers reaches The bottom of the list sousers reaches The bottom of the list sothis is what pagination basically a verythis is what pagination basically a verythis is what pagination basically a verypowerful thing Which is very used inpowerful thing Which is very used inpowerful thing Which is very used inprogramming All Rightnext now Let's Select everything fromnext now Let's Select everything fromnext now Let's Select everything fromthe payment table ty Selectthe payment table ty Selectthe payment table ty Selecteverything fromeverything fromeverything frompayment semicolon Let's Runpayment semicolon Let's Runpayment semicolon Let's Runit right and we have the payment ID theit right and we have the payment ID theit right and we have the payment ID thecustom ID staff ID rental amount andcustom ID staff ID rental amount andcustom ID staff ID rental amount andpayment date now Let's say that I wantpayment date now Let's say that I wantpayment date now Let's say that I wantto get everything from payment butto get everything from payment butto get everything from payment butordered by the Pay date can here Selectordered by the Pay date can here Selectordered by the Pay date can here Selecteverything from payment Order bu and Ieverything from payment Order bu and Ieverything from payment Order bu and Ican type herepayment und date and here you canpayment und date and here you canpayment und date and here you canspecify as sending or descending Let'sspecify as sending or descending Let'sspecify as sending or descending Let'slet sending Let's Click on Run and nowlet sending Let's Click on Run and nowlet sending Let's Click on Run and nowas can see have everything Order by theas can see have everything Order by theas can see have everything Order by themost recent payment date by the paymentmost recent payment date by the paymentmost recent payment date by the paymentdate Now I Can Write this descending Ifdate Now I Can Write this descending Ifdate Now I Can Write this descending IfI want so I can this descending If II want so I can this descending If II want so I can this descending If Iwant so now if Runwant so now if Runwant so now if Runthis Now can see we have 51 andthis Now can see we have 51 andthis Now can see we have 51 andpreviously Let me go back toascending lowercase letters Let's Runascending lowercase letters Let's Runascending lowercase letters Let's Runnow we have Two 14 so This is the monthnow we have Two 14 so This is the monthnow we have Two 14 so This is the monthmonth and this is themonth and this is themonth and this is thedate so this is what We can do now Let'sdate so this is what We can do now Let'sdate so this is what We can do now Let'ssee how We can use the limit functionsee how We can use the limit functionsee how We can use the limit functionLet's say that I want to retrieve Only theLet's say that I want to retrieve Only theLet's say that I want to retrieve Only thelast most last 10 most recent paymentslast most last 10 most recent paymentslast most last 10 most recent paymentsso in Order to achieve that I need to goso in Order to achieve that I need to goso in Order to achieve that I need to gohere and I can typehere and I can typehere and I can typehere limit you can type this on the samehere limit you can type this on the samehere limit you can type this on the sameline or On The next limit and put 10 andline or On The next limit and put 10 andline or On The next limit and put 10 andcan put optional semicolon if want nowcan put optional semicolon if want nowcan put optional semicolon if want nowif Runif Runif Runthis as you can seethis as you can seethis as you can seehave have our data but only have 10have have our data but only have 10have have our data but only have 10entries now the most recent 10 paymentsentries now the most recent 10 paymentsentries now the most recent 10 paymentsbased on the payment date now Let's saybased on the payment date now Let's saybased on the payment date now Let's saythat as you can see here Let me checkthat as you can see here Let me checkthat as you can see here Let me checkthethetheamount the payment amount here we haveamount the payment amount here we haveamount the payment amount here we have09 check Let's put here09 check Let's put here09 check Let's put hereanother statement and I'm gna Go up hereanother statement and I'm gna Go up hereanother statement and I'm gna Go up hereand Before the Order by gna typewherewherewhereamount not equ tozer Get our data and f buteliminatedeliminatedeliminatedCASwaswaswas9 andnow the 099 is not Present as you cannow the 099 is not Present as you cannow the 099 is not Present as you cansee here right I'm gna end this videosee here right I'm gna end this videosee here right I'm gna end this videohere and see you In The nexthere and see you In The nexthere and see you In The nextvideo welcome back now time to start avideo welcome back now time to start avideo welcome back now time to start adiscussion about the Bin command in postdiscussion about the Bin command in postdiscussion about the Bin command in postgrul So what is the Bin command thegrul So what is the Bin command thegrul So what is the Bin command theButin command in post gresul is used toButin command in post gresul is used toButin command in post gresul is used tofilter data within a specified Range offilter data within a specified Range offilter data within a specified Range ofvalues ​​ it is typically used invalues ​​it is typically used invalues ​​it is typically used inconjunction with work to retrieve thatconjunction with work to retrieve thatconjunction with work to retrieve thatfall with a specified Range of valuesfall with a specified Range of valuesfall with a specified Range of valuesfor a particularfor a particularfor a particularcolumn so first we have here's acolumn so first we have here's acolumn so first we have here's abreakdown of its uses and significancebreakdown of its uses and significancebreakdown of its uses and significancefirst we have the basic syntax of thefirst we have the basic syntax of thefirst we have the basic syntax of thebetween command is as follows we havebetween command is as follows we havebetween command is as follows we havethe Select the column so we have thethe Select the column so we have thethe Select the column so we have theSelect statement the column name of orSelect statement the column name of orSelect statement the column name of ornames from table name Where column namenames from table name Where column namenames from table name Where column nameis and now use the between command valueis and now use the between command valueis and now use the between command valueOne and valueOne and valueOne and valueTwo next we have Two we have usage soTwo next we have Two we have usage soTwo next we have Two we have usage sothe command is particularly useful Whenthe command is particularly useful Whenthe command is particularly useful WhenYou want to filter rows based on a RangeYou want to filter rows based on a RangeYou want to filter rows based on a Rangeof values ​​ for a specified column Forof values ​​for a specified column Forof values ​​for a specified column Forexample You might want to retrieve allexample You might want to retrieve allexample You might want to retrieve allorders made within a certain date Rangeorders made within a certain date Rangeorders made within a certain date Rangeor find employees whose salaries fallor find employees whose salaries fallor find employees whose salaries fallwithin a certain bracket including Onewithin a certain bracket including Onewithin a certain bracket including Oneimportant aspect of to note is that theimportant aspect of to note is that theimportant aspect of to note is that thebetween command is inclusive meaning itbetween command is inclusive meaning itbetween command is inclusive meaning itincludes both the specified start andincludes both the specified start andincludes both the specified start andend values ​​ in the Range for example ifend values ​​in the Range for example ifend values ​​in the Range for example ifyou use between 10 and 20 will includeyou use between 10 and 20 will includeyou use between 10 and 20 will includerows with values ​​ 10 and 20 androws with values ​​10 and 20 androws with values ​​10 and 20 andeverythingeverythingeverythingWhy use it so Why do you need to use theWhy use it so Why do you need to use theWhy use it so Why do you need to use thecommand the command provides a concisecommand the command provides a concisecommand the command provides a conciseway to filter data within a Rangeway to filter data within a Rangeway to filter data within a Rangeeliminates the need to write separateeliminates the need to write separateeliminates the need to write separateconditions for each boundary it enhancesconditions for each boundary it enhancesconditions for each boundary it enhancesthe readability and maintain ability ofthe readability and maintain ability ofthe readability and maintain ability ofyour squyour squyour sququeries next we have performance Whenqueries next we have performance Whenqueries next we have performance Whenusing the command make sure that theusing the command make sure that theusing the command make sure that thecolumn applying it has an appropriatecolumn applying it has an appropriatecolumn applying it has an appropriateindex indexing the columnindex indexing the columnindex indexing the columncanic improve querycanic improve querycanic improve queryperformance down the RS with the specifperformance down the RS with the specifperformance down the RS with the specifRange examples retrieve orders within aRange examples retrieve orders within aRange examples retrieve orders within aspecified date Range have Select Orderspecified date Range have Select Orderspecified date Range have Select OrderID Order date from orders or Order thatID Order date from orders or Order thatID Order date from orders or Order thatOrder underscore date between 20230 andOrder underscore date between 20230 andOrder underscore date between 20230 and202062020620206so next we have Finding employees withso next we have Finding employees withso next we have Finding employees withsalaries between a certain Range havesalaries between a certain Range havesalaries between a certain Range haveSelect employees ID first name last nameSelect employees ID first name last nameSelect employees ID first name last namesalary fromsalary fromsalary fromemployees salary between 400 and 600 inemployees salary between 400 and 600 inemployees salary between 400 and 600 insummary the command post is a powerfulsummary the command post is a powerfulsummary the command post is a powerfultool for filtering data with speci Rangetool for filtering data with speci Rangetool for filtering data with speci Rangemaking your queries More concise andmaking your queries More concise andmaking your queries More concise andreadable it is commonly used to retrievereadable it is commonly used to retrievereadable it is commonly used to retrieverows that me certain criteria withoutrows that me certain criteria withoutrows that me certain criteria withouthaving to write multiple separatehaving to write multiple separatehaving to write multiple separateconditions now Let's work on someconditions now Let's work on someconditions now Let's work on someexamples in PG admin therefore I'm gnaexamples in PG admin therefore I'm gnaexamples in PG admin therefore I'm gnause the payment table Select everythinguse the payment table Select everythinguse the payment table Select everythingfrom the paymentfrom the paymentfrom the paymenttable I'm gna limit only Five entries toRun and have f have payment customerRun and have f have payment customerRun and have f have payment customerstuff R amount and paymentstuff R amount and paymentstuff R amount and paymentdate that I want to Select everythingdate that I want to Select everythingdate that I want to Select everythingfrom payment where the amount is betweenfrom payment where the amount is betweenfrom payment where the amount is between8 and 9 I'm G8 and 9 I'm G8 and 9 I'm Gtype Select from paymenttype Select from paymenttype Select from paymentWhereWhereWhereamountamountamountbetweene and n semicolonas have All Pay am can 99 haveas have All Pay am can 99 haveas have All Pay am can 99 haveeverything and and if you Which is noteverything and and if you Which is noteverything and and if you Which is notbetween put not keywordbetween put not keywordbetween put not keywordhereGet 7 and if you want to see the GetGet 7 and if you want to see the GetGet 7 and if you want to see the Get7997997997999 selecting everything But not 8 and7999 selecting everything But not 8 and7999 selecting everything But not 8 andn everything Whichn everything Whichn everything Whichis not and nowis not and nowis not and nowLet'sLet'sLet'sLet's let see How many transaction weLet's let see How many transaction weLet's let see How many transaction wehaveSelect All see How manySelect All see How manySelect All see How manytransactions amount is between R so wetransactions amount is between R so wetransactions amount is between R so wehave 439 transactions and if you want tohave 439 transactions and if you want tohave 439 transactions and if you want tosee which are not put the not keywordsee which are not put the not keywordsee which are not put the not keywordhere case letters and we have as you canhere case letters and we have as you canhere case letters and we have as you cansee we have a big number here147 say that I147 say that I147 say that IAll the transactionsAll the transactionsAll the transactionspayment back here frompayment back here frompayment back here frompayment Let's say Let's Run this to seepayment Let's say Let's Run this to seepayment Let's say Let's Run this to seehow table was payment date the paymenthow table was payment date the paymenthow table was payment date the paymentdate see All the transactions in thedate see All the transactions in thedate see All the transactions in thefirst half of January in Order to dofirst half of January in Order to dofirst half of January in Order to dothat I'm Go at the end of our statementthat I'm Go at the end of our statementthat I'm Go at the end of our statementandandandhere andhere andhere andpaymentpaymentpaymentpayment datebetween and I put here single quotationbetween and I put here single quotationbetween and I put here single quotationMars 2007 now the mon Dash 0 One and theMars 2007 now the mon Dash 0 One and theMars 2007 now the mon Dash 0 One and theday 0 Oneandandandand single quotation marks200720072007Dash 0Dash 0Dash 0OneOneOne15 Let's Run15 Let's Run15 Let's RunthisandandandGet not many transactions so Let meGet not many transactions so Let meGet not many transactions so Let mechange this to let say first half ofchange this to let say first half ofchange this to let say first half offebruary chang to zer to Let'sfebruary chang to zer to Let'sfebruary chang to zer to Let'sRun and as you can see we have All theRun and as you can see we have All theRun and as you can see we have All thepayments but as you can see here They arepayments but as you can see here They arepayments but as you can see here They arein the first Half as you can seein the first Half as you can seein the first Half as you can seehave 2007 the amount is 02 and the datehave 2007 the amount is 02 and the datehave 2007 the amount is 02 and the dateis 14 as you can see here so nowis 14 as you can see here so nowis 14 as you can see here so noweverything works fine previously weeverything works fine previously weeverything works fine previously wedidn't Got anything because there is nodidn't Got anything because there is nodidn't Got anything because there is notransaction in the in January but wetransaction in the in January but wetransaction in the in January but wehave in February between Day One and 15have in February between Day One and 15have in February between Day One and 15All Right I'm gna end this video hereAll Right I'm gna end this video hereAll Right I'm gna end this video hereand see you In The nextand see you In The nextand see you In The nextvideo welcome back now time to start avideo welcome back now time to start avideo welcome back now time to start adiscussion about the in Command in postdiscussion about the in Command in postdiscussion about the in Command in posterror So what is the in Command in post error the inerror So what is the in Command in post error the inerror So what is the in Command in post error the inCommand in post error isCommand in post error isCommand in post error isused to filter data based on a specifyused to filter data based on a specifyused to filter data based on a specifylist of values ​​ for a particular columnlist of values ​​for a particular columnlist of values ​​for a particular columnit allows you to retrieve rows Where ait allows you to retrieve rows Where ait allows you to retrieve rows Where aspecified columns value matches Any ofspecified columns value matches Any ofspecified columns value matches Any ofthe value in the given list here is athe value in the given list here is athe value in the given list here is adetailed explanation of the in Commanddetailed explanation of the in Commanddetailed explanation of the in Commandand itsand itsand itssignificancesignificancesignificanceso first we have The syntax The basicso first we have The syntax The basicso first we have The syntax The basicsyntax of the in Command is as followssyntax of the in Command is as followssyntax of the in Command is as followsyou have the Select The column Name oryou have the Select The column Name oryou have the Select The column Name ornames from the table name where columnnames from the table name where columnnames from the table name where columnand we have the in Command and putand we have the in Command and putand we have the in Command and putparentheses value One value two and soparentheses value One value two and soparentheses value One value two and soon so This is the syntax of the inon so This is the syntax of the inon so This is the syntax of the inCommand now Now at the point Two we haveCommand now Now at the point Two we haveCommand now Now at the point Two we haveusage the in Command is particularlyusage the in Command is particularlyusage the in Command is particularlyuseful When You want to filter rowsuseful When You want to filter rowsuseful When You want to filter rowsbased on a list of values ​​ it slif yourbased on a list of values ​​it slif yourbased on a list of values ​​it slif yourquery by allowing you to specifyquery by allowing you to specifyquery by allowing you to specifymultiple acceptable values ​​ in a Compactmultiple acceptable values ​​in a Compactmultiple acceptable values ​​in a Compactmanner This is especially handy When wemanner This is especially handy When wemanner This is especially handy When wehave a long List of potential values ​​ tohave a long List of potential values ​​tohave a long List of potential values ​​tofilter for next we havefilter for next we havefilter for next we havecan dat types including numeric valcan dat types including numeric valcan dat types including numeric valstrings and even sub queries thisstrings and even sub queries thisstrings and even sub queries thisflexibility makes it a versatile toolflexibility makes it a versatile toolflexibility makes it a versatile toolforing data and for we have Why use itforing data and for we have Why use itforing data and for we have Why use itso there are Few key reasons Why youso there are Few key reasons Why youso there are Few key reasons Why youmight want to use the in Command firstmight want to use the in Command firstmight want to use the in Command firsthave reduced query complexity Whenhave reduced query complexity Whenhave reduced query complexity Whendealing with multiple Possible valuesdealing with multiple Possible valuesdealing with multiple Possible valuesfor a column using in can simplify yourfor a column using in can simplify yourfor a column using in can simplify yourcompare to writing separate or conditionscompare to writing separate or conditionscompare to writing separate or conditionsfor each value improve readabilityfor each value improve readabilityfor each value improve readabilityby using in you make your sql queriesby using in you make your sql queriesby using in you make your sql queriesMore concise and easier to readMore concise and easier to readMore concise and easier to readespecially When dealing with long Listespecially When dealing with long Listespecially When dealing with long Listof values ​​ efficiencyof values ​​efficiencyof values ​​efficiencypog pog query optimizer can Oftenpog pog query optimizer can Oftenpog pog query optimizer can Oftenoptimize in queries More effectivelyoptimize in queries More effectivelyoptimize in queries More effectivelyThan complex combinations of Allconditions alrnext now Let's on some examples in PGnext now Let's on some examples in PGnext now Let's on some examples in PGadmin therefore I'm gna Selectadmin therefore I'm gna Selectadmin therefore I'm gna Selecteverything from paymentand I'm gna type limitand I'm gna type limitand I'm gna type limitf Let's Runf Let's Runf Let's Runthis and as you can see we have herethis and as you can see we have herethis and as you can see we have hereFive entries and we have payment IDFive entries and we have payment IDFive entries and we have payment IDcustomer stuff id rental id amount andcustomer stuff id rental id amount andcustomer stuff id rental id amount andpayment date now Let's say that I wantpayment date now Let's say that I wantpayment date now Let's say that I wantto get the Distinct amounts which are Into get the Distinct amounts which are Into get the Distinct amounts which are Inthis table in Order to do that I'm herethis table in Order to do that I'm herethis table in Order to do that I'm hereSelect distinctamount from payment Let's del theamount from payment Let's del theamount from payment Let's del thelimit Let's Runlimit Let's Runlimit Let's Runit and now have the Distinct amountsit and now have the Distinct amountsit and now have the Distinct amountswhich are In this table we have 199 1099which are In this table we have 199 1099which are In this table we have 199 1099and so on and now Let's say that I wantand so on and now Let's say that I wantand so on and now Let's say that I wantto get the amounts only the amount Let'sto get the amounts only the amount Let'sto get the amounts only the amount Let'ssay Select everything from paymentamountamountamountParis or amount inParis or amount inParis or amount inParis Let's putz99numeric Onenumeric Onenumeric One888andandandOneOneOne999n Let's put this in quotation marksactuallyactuallyactuallyalri now Let's Runalri now Let's Runalri now Let's Runit and as you can you have All theit and as you can you have All theit and as you can you have All theamounts which areamounts which areamounts which are19999 and19999 and19999 and8 as can see here now have this using8 as can see here now have this using8 as can see here now have this usingthe inthe inthe inoperatoroperatoroperatornow next Let's say that I want to getnow next Let's say that I want to getnow next Let's say that I want to getthe amount Which is not inthe amount Which is not inthe amount Which is not inthat this list Which is not 099 198 andthat this list Which is not 099 198 andthat this list Which is not 099 198 and199 so Let's Run199 so Let's Run199 so Let's Runthis now All the entries which are notthis now All the entries which are notthis now All the entries which are notInInInthis this List of entries so as you canthis this List of entries so as you canthis this List of entries so as you can799 299 but don't have 099 199 or 98 or799 299 but don't have 099 199 or 98 or799 299 but don't have 099 199 or 98 or199 as you can see here let's give one199 as you can see here let's give one199 as you can see here let's give onemore example with the but now use themore example with the but now use themore example with the but now use thecustomercustomercustomertabletabletableSelect everythingSelect everythingSelect everythingfromfromfromcustomercustomercustomerWhere first und name inWhere first und name inWhere first und name inparenthesesJohnJohnJake andJake andJake andJulie letR have Two customers Julie and John alrR have Two customers Julie and John alrR have Two customers Julie and John alrI'm G video here and see nextI'm G video here and see nextI'm G video here and see nextvideo welcome back about the like and Ivideo welcome back about the like and Ivideo welcome back about the like and Ilike commands in post gres So what are thelike commands in post gres So what are thelike commands in post gres So what are thelike and I like commands in postlike and I like commands in postlike and I like commands in postgres the like and I like commands ingres the like and I like commands ingres the like and I like commands inpost gres are used for Pattern matchingpost gres are used for Pattern matchingpost gres are used for Pattern matchingwithin text columns They allow you towithin text columns They allow you towithin text columns They allow you tofilter rows based on patterns definedfilter rows based on patterns definedfilter rows based on patterns definedusing Wi color characters the Mainusing Wi color characters the Mainusing Wi color characters the Maindifference between the Two is that likedifference between the Two is that likedifference between the Two is that likeperforms case sensitive matching while Iperforms case sensitive matching while Iperforms case sensitive matching while Ilike performs case insensitive matchinglike performs case insensitive matchinglike performs case insensitive matchinghere is a detailed explanation of thehere is a detailed explanation of thehere is a detailed explanation of thelike and I commands and theirlike and I commands and theirlike and I commands and theirsignificance so have the sysignificance so have the sysignificance so have the sytax and the basic syntax of the liketax and the basic syntax of the liketax and the basic syntax of the likecommand is you have a query Select fromcommand is you have a query Select fromcommand is you have a query Select fromcolumn name or names fromcolumn name or names fromcolumn name or names fromtable Where column name like and have thetable Where column name like and have thetable Where column name like and have thePattern define the Pattern next wePattern define the Pattern next wePattern define the Pattern next wehave the basic syntax for the command ishave the basic syntax for the command ishave the basic syntax for the command isand the basic syntax for the il commandand the basic syntax for the il commandand the basic syntax for the il commandis Select from column name or names fromis Select from column name or names fromis Select from column name or names fromtable name work column name is I liketable name work column name is I liketable name work column name is I likeand we define the Pattern so whateverand we define the Pattern so whateverand we define the Pattern so whateverPattern you are searching for now wePattern you are searching for now wePattern you are searching for now wehave Wild c characters and Wild cardhave Wild c characters and Wild cardhave Wild c characters and Wild cardcharacters are special characters usedcharacters are special characters usedcharacters are special characters usedto represent One or more characters in ato represent One or more characters in ato represent One or more characters in aPattern the percent Sign represents AnyPattern the percent Sign represents AnyPattern the percent Sign represents Anysequence of characters including zerosequence of characters including zerosequence of characters including zerocharacters the underscore represents acharacters the underscore represents acharacters the underscore represents asingle character and the square bracketssingle character and the square bracketssingle character and the square bracketsrepresents A character Class allowingrepresents A character Class allowingrepresents A character Class allowingyou to match Any single character withinyou to match Any single character withinyou to match Any single character withinthe specifithe specifithe specifiset next we have usage and like and Iset next we have usage and like and Iset next we have usage and like and Ilike are particularly useful When Youlike are particularly useful When Youlike are particularly useful When Youwant to search for values ​​ in a columnwant to search for values ​​in a columnwant to search for values ​​in a columnthat match a certain Pattern This isthat match a certain Pattern This isthat match a certain Pattern This iscommon When dealing with textual datacommon When dealing with textual datacommon When dealing with textual datasuch as names addresses or descriptionssuch as names addresses or descriptionssuch as names addresses or descriptionsThey are especially handy When You wantThey are especially handy When You wantThey are especially handy When You wantto perform partial searches retrieveto perform partial searches retrieveto perform partial searches retrieverecords that contain a specificrecords that contain a specificrecords that contain a specificsubstring or filter database on specificsubstring or filter database on specificsubstring or filter database on specificpatterns next we have case sensitivitypatterns next we have case sensitivitypatterns next we have case sensitivitylike performs case sensitivity Patternlike performs case sensitivity Patternlike performs case sensitivity Patternmatching example ifmatching example ifmatching example iflikelikelikelike Rose with the valle apple Burnlike Rose with the valle apple Burnlike Rose with the valle apple BurnApple und AApple und AApple und Aso I like and I like perform case inso I like and I like perform case inso I like and I like perform case insensitive Pattern matching It will matchsensitive Pattern matching It will matchsensitive Pattern matching It will matchRose regardless of the case for exampleRose regardless of the case for exampleRose regardless of the case for exampleI like with match apple Apple su andI like with match apple Apple su andI like with match apple Apple su andApplejuice next we have examples and rev Alljuice next we have examples and rev Alljuice next we have examples and rev Allcustomers with name starting with j wecustomers with name starting with j wecustomers with name starting with j wehave the following query for doing thathave the following query for doing thathave the following query for doing thatSelect from customer nameSelect from customer nameSelect from customer namecontact und email from customers orcontact und email from customers orcontact und email from customers orcustomer name like and put singlecustomer name like and put singlecustomer name like and put singlequotation marks j m per sence so itquotation marks j m per sence so itquotation marks j m per sence so itreeve all customers starting with thereeve all customers starting with thereeve all customers starting with thelater j next we have Finding Productslater j next we have Finding Productslater j next we have Finding Productswith the name names containing Blue typewith the name names containing Blue typewith the name names containing Blue typeSelect product name category fromSelect product name category fromSelect product name category fromProducts Where product name and percentProducts Where product name and percentProducts Where product name and percentsign we have first single quotation persign we have first single quotation persign we have first single quotation perSign Blue and percent sign so This isSign Blue and percent sign so This isSign Blue and percent sign so This isfor Finding Products next we havefor Finding Products next we havefor Finding Products next we havesearching for employees is with lastsearching for employees is with lastsearching for employees is with lastname ending in s case insensitive soname ending in s case insensitive soname ending in s case insensitive sohave Select for this particular examplehave Select for this particular examplehave Select for this particular examplehave Select first name last name fromhave Select first name last name fromhave Select first name last name fromemployees Where last name I like percentemployees Where last name I like percentemployees Where last name I like percentSignSignSignsun so gna return The employees next wesun so gna return The employees next wesun so gna return The employees next wehave w car characters so Wild carhave w car characters so Wild carhave w car characters so Wild carcharacters are special symbols usingcharacters are special symbols usingcharacters are special symbols usingPattern margin within sql queries TheyPattern margin within sql queries TheyPattern margin within sql queries Theyare extremely useful for performingare extremely useful for performingare extremely useful for performingflexible searches and filteringflexible searches and filteringflexible searches and filteringfiltering based on specific patternsfiltering based on specific patternsfiltering based on specific patternswithin text columns here is anwithin text columns here is anwithin text columns here is anexplanation of the common with wild carexplanation of the common with wild carexplanation of the common with wild carcharacters their benefits and When tocharacters their benefits and When tocharacters their benefits and When tousethem we have the percent first and thethem we have the percent first and thethem we have the percent first and thepercent represents Any sequence ofpercent represents Any sequence ofpercent represents Any sequence ofcharacters including zero characterscharacters including zero characterscharacters including zero characterscases searching for values ​​ that startcases searching for values ​​that startcases searching for values ​​that startwith with or end with a specificwith with or end with a specificwith with or end with a specificsequence of characters Finding recordssequence of characters Finding recordssequence of characters Finding recordsthat contain a particular sub stringthat contain a particular sub stringthat contain a particular sub stringwithin a larger string and we have anwithin a larger string and we have anwithin a larger string and we have anexample we have Select product name fromexample we have Select product name fromexample we have Select product name fromProducts or product name like we haveProducts or product name like we haveProducts or product name like we havesingle quotation marks percent Signsingle quotation marks percent Signsingle quotation marks percent SignApple percenter Sign and singleApple percenter Sign and singleApple percenter Sign and singlequotation Mark this query reves Allquotation Mark this query reves Allquotation Mark this query reves AllProducts with the names containing theProducts with the names containing theProducts with the names containing theword Apple so This is very important toword Apple so This is very important toword Apple so This is very important tohave in mind All Righthave in mind All Righthave in mind All Rightnext we have underscore and rorenext we have underscore and rorenext we have underscore and rorerepresents a single character use casesrepresents a single character use casesrepresents a single character use casessearching for values ​​ to specificsearching for values ​​to specificsearching for values ​​to specificcharacter at a certain position you cancharacter at a certain position you cancharacter at a certain position you cando that or Finding record with ado that or Finding record with ado that or Finding record with aparticular PN where you know theparticular PN where you know theparticular PN where you know thecharacter at a specificposition example you have Selectposition example you have Selectposition example you have Selectcustomer name from customers Wherecustomer name from customers Wherecustomer name from customers Wherecustomer name like and have singlecustomer name like and have singlecustomer name like and have singlecation mar j und so have the und Wildcation mar j und so have the und Wildcation mar j und so have the und Wildcard n and we have percent Sign andcard n and we have percent Sign andcard n and we have percent Sign andclosing single quotation Mark and thisclosing single quotation Mark and thisclosing single quotation Mark and thisquy reves customers names that startquy reves customers names that startquy reves customers names that startwith j and have Any second characterwith j and have Any second characterwith j and have Any second characterFollowed by n so this is what This is going toFollowed by n so this is what This is going toFollowed by n so this is what This is going todo next we have square bracketdo next we have square bracketdo next we have square bracketand square brackets represent Aand square brackets represent Aand square brackets represent Acharacter Class allowing you to matchcharacter Class allowing you to matchcharacter Class allowing you to matchAny single character within the specifiAny single character within the specifiAny single character within the specifiset searching for values ​​ that can haveset searching for values ​​that can haveset searching for values ​​that can havevariations on specific charactervariations on specific charactervariations on specific characterposition for exampleposition for exampleposition for exampleif for alternate spelling or form exampleif for alternate spelling or form exampleif for alternate spelling or form examplewe have Select product und name fromwe have Select product und name fromwe have Select product und name fromProducts Where product und name like andProducts Where product und name like andProducts Where product und name like andwe have single cation marks col and havewe have single cation marks col and havewe have single cation marks col and havethe u in in square brackets R andthe u in in square brackets R andthe u in in square brackets R andpercent size in closing single quotationpercent size in closing single quotationpercent size in closing single quotationMarkMarkMarkand we have another we have the sameand we have another we have the sameand we have another we have the sameexample and this query that I presentedexample and this query that I presentedexample and this query that I presentedwill match Products with the namewill match Products with the namewill match Products with the namestarting with Color and accounts forstarting with Color and accounts forstarting with Color and accounts forvariationEnglish This is G do now benefits ofEnglish This is G do now benefits ofEnglish This is G do now benefits ofwildcard characters flexibility Wildwildcard characters flexibility Wildwildcard characters flexibility Wildcards make your searches more flexiblecards make your searches more flexiblecards make your searches more flexibleby allowing you to match a variety ofby allowing you to match a variety ofby allowing you to match a variety ofPattern without patterns without specifyingPattern without patterns without specifyingPattern without patterns without specifyingexact values ​​ next we have efficiency youexact values ​​next we have efficiency youexact values ​​next we have efficiency youcan perform complex searches with acan perform complex searches with acan perform complex searches with asingle query eliminate the Need forsingle query eliminate the Need forsingle query eliminate the Need formultiple separate queries reduce querymultiple separate queries reduce querymultiple separate queries reduce queryreduce query complexity simplify queryreduce query complexity simplify queryreduce query complexity simplify queryConstruction by Ena you to performConstruction by Ena you to performConstruction by Ena you to performPattern based filtering without writingPattern based filtering without writingPattern based filtering without writinglength in conditional statements next welength in conditional statements next welength in conditional statements next wehave Dynamic filtering Wild cards arehave Dynamic filtering Wild cards arehave Dynamic filtering Wild cards areespecially useful when you need toespecially useful when you need toespecially useful when you need tosearch for Pattern within user generatedsearch for Pattern within user generatedsearch for Pattern within user generatedinput or When the exact values ​​ areunknown When to use w c characters Whenunknown When to use w c characters Whenunknown When to use w c characters Whenyou need to search for PN within textyou need to search for PN within textyou need to search for PN within textdat columns When You want to performdat columns When You want to performdat columns When You want to performpartial searches or reve records thatpartial searches or reve records thatpartial searches or reve records thatcontain specific substcontain specific substcontain specific substWhen need to variations in spellingWhen need to variations in spellingWhen need to variations in spellingformatting or positions construc Dynamicformatting or positions construc Dynamicformatting or positions construc Dynamicqu where the exact values ​​ are not knownqu where the exact values ​​are not knownqu where the exact values ​​are not knowninininAdvance Now let See Now how combiningAdvance Now let See Now how combiningAdvance Now let See Now how combiningthe person Science underscore and squarethe person Science underscore and squarethe person Science underscore and squarebrackets allows you to create even morebrackets allows you to create even morebrackets allows you to create even morecomplex and specific Pattern bascomplex and specific Pattern bascomplex and specific Pattern bassearches within squ queries Thissearches within squ queries Thissearches within squ queries Thiscombination is particularly useful When tocombination is particularly useful When tocombination is particularly useful When toneed when you need to search for dataneed when you need to search for dataneed when you need to search for datawith a specific STR Pattern whilewith a specific STR Pattern whilewith a specific STR Pattern whileaccounting for variations here is anaccounting for variations here is anaccounting for variations here is anexample How to use this combinationexample How to use this combinationexample How to use this combinationalong with use cases we have syntax foralong with use cases we have syntax foralong with use cases we have syntax forcombining Wild car characters we havecombining Wild car characters we havecombining Wild car characters we havethe percent which represents Anythe percent which represents Anythe percent which represents Anysequence of characters including zerosequence of characters including zerosequence of characters including zerocharacters you have the underscore whichcharacters you have the underscore whichcharacters you have the underscore whichrepresents a single character and werepresents a single character and werepresents a single character and wehave score brackets which representhave score brackets which representhave score brackets which representcharacter Class allowing you to matchcharacter Class allowing you to matchcharacter Class allowing you to matchAny single character within theAny single character within theAny single character within thespecifiedspecifiedspecifiedset cor right and we have use casesset cor right and we have use casesset cor right and we have use casessearching for email domains suppose wesearching for email domains suppose wesearching for email domains suppose wehave a database table contain email addhave a database table contain email addhave a database table contain email addand reve All email addresses from aand reve All email addresses from aand reve All email addresses from aspecific domain while allowing forspecific domain while allowing forspecific domain while allowing forvariation the domain name such as subvariation the domain name such as subvariation the domain name such as subdomains or Top Level domains td tlddomains or Top Level domains td tlddomains or Top Level domains td tldLet's say you're interested in ringLet's say you're interested in ringLet's say you're interested in ringemail addresses from both example.comemail addresses from both example.comemail addresses from both example.comand example net for that you need toand example net for that you need toand example net for that you need totype the following query Select emailtype the following query Select emailtype the following query Select emailund address from users from users Whereund address from users from users Whereund address from users from users Whereemail und address like and we haveemail und address like and we haveemail und address like and we havesingle quotationsingle quotationsingle quotationexample and have square und and haveexample and have square und and haveexample and have square und and havethat Again square brackets Open squarethat Again square brackets Open squarethat Again square brackets Open squarebracket left square brack you havebracket left square brack you havebracket left square brack you havecno right square bracket and we have mcno right square bracket and we have mcno right square bracket and we have mnow let's go nextnow let's go nextnow let's go nextand the explanation for this query theand the explanation for this query theand the explanation for this query thepercentpercentpercentpercent Sign example much is any emailpercent Sign example much is any emailpercent Sign example much is any emailaddress that ends with exampleaddress that ends with exampleaddress that ends with examplethethetheunderscore square brackets in squareunderscore square brackets in squareunderscore square brackets in squarebrackets matches Any single characterbrackets matches Any single characterbrackets matches Any single characterwhich Can Be Any character including awhich Can Be Any character including awhich Can Be Any character including adot in the email address do and havedot in the email address do and havedot in the email address do and havescore brackets cno matches a singlescore brackets cno matches a singlescore brackets cno matches a singlecharacter from the character set cnocharacter from the character set cnocharacter from the character set cnowhich covers variations of the tld likewhich covers variations of the tld likewhich covers variations of the tld likec n or oc n or oc n or ofor com net.orgfor com net.orgfor com net.orgresults this query would retrieve emailresults this query would retrieve emailresults this query would retrieve emailaddresses like user example.com adminaddresses like user example.com adminaddresses like user example.com adminexample net and Any other variationsexample net and Any other variationsexample net and Any other variationsthat match the specified Pattern so thisthat match the specified Pattern so thisthat match the specified Pattern so thisis what is gnais what is gnais what is gnado benefits and use cases versatiledo benefits and use cases versatiledo benefits and use cases versatileversatile filtering this combination ofcversatile filtering this combination ofcversatile filtering this combination ofccharacters allows you to filter databasecharacters allows you to filter databasecharacters allows you to filter databaseon complex patterns with a higher levelon complex patterns with a higher levelon complex patterns with a higher levelof flexibility next we have Patternof flexibility next we have Patternof flexibility next we have Patternvariations you can Capture variations invariations you can Capture variations invariations you can Capture variations indata such as different spelling formatsdata such as different spelling formatsdata such as different spelling formatsorororseparateseparateseparateconditions to have Dynamic searches Thisconditions to have Dynamic searches Thisconditions to have Dynamic searches Thistechnique is valuable When dealing withtechnique is valuable When dealing withtechnique is valuable When dealing withuser input when you need to adaptuser input when you need to adaptuser input when you need to adaptqueries to much evolving data patternsqueries to much evolving data patternsqueries to much evolving data patternsnext we have efficient query instead ofnext we have efficient query instead ofnext we have efficient query instead ofconstructing multiple queries you canconstructing multiple queries you canconstructing multiple queries you canachieve theachieve theachieve theDesire Wish filtering in a singleDesire Wish filtering in a singleDesire Wish filtering in a singlequery and next Where to use first toquery and next Where to use first toquery and next Where to use first tohave user generated input When usershave user generated input When usershave user generated input When usersprovide input that May have varyingprovide input that May have varyingprovide input that May have varyingstructure or Pattern example namesstructure or Pattern example namesstructure or Pattern example namesaddresses phone numbers data clean upaddresses phone numbers data clean upaddresses phone numbers data clean upwhen you need to end identify andwhen you need to end identify andwhen you need to end identify andcorrect In consistent and data formatscorrect In consistent and data formatscorrect In consistent and data formatsdomain filtering as demonstrated In thisdomain filtering as demonstrated In thisdomain filtering as demonstrated In thisexample When You search for specificexample When You search for specificexample When You search for specificdomain variations in a mail addresses orurls and in summary combining the percurls and in summary combining the percurls and in summary combining the percand underscore and score brackets andand underscore and score brackets andand underscore and score brackets andsql allows you to perform sophisticatedsql allows you to perform sophisticatedsql allows you to perform sophisticatedPattern watching for specific use casesPattern watching for specific use casesPattern watching for specific use casesthis technique enhances your ability tothis technique enhances your ability tothis technique enhances your ability tofilter and retrieve data while accafilter and retrieve data while accafilter and retrieve data while accavariations in patterns making yourvariations in patterns making yourvariations in patterns making yourqueries more veralefficient now Let's some examples in PGefficient now Let's some examples in PGefficient now Let's some examples in PGadmin and I'm going to use the customeradmin and I'm going to use the customeradmin and I'm going to use the customertable for this reason I'm ty Selecttable for this reason I'm ty Selecttable for this reason I'm ty Selecteverything from customer to see how iteverything from customer to see how iteverything from customer to see how itLooks semicolon RunLooks semicolon RunLooks semicolon Runthis and the c ID Store ID first namethis and the c ID Store ID first namethis and the c ID Store ID first namelast name email address ID active activelast name email address ID active activelast name email address ID active activeBull and so on so Let's say that I wantBull and so on so Let's say that I wantBull and so on so Let's say that I wantto get All the customers whose nameto get All the customers whose nameto get All the customers whose namestarts with the uppercase letter j sostarts with the uppercase letter j sostarts with the uppercase letter j soI'm gna type here at the end of ourI'm gna type here at the end of ourI'm gna type here at the end of ourstatement I'm gna delete the semicolon istatement I'm gna delete the semicolon istatement I'm gna delete the semicolon itypetypetypeWhere first undWhere first undWhere first undname dou typename dou typename dou typeLike I will put single quotation marksLike I will put single quotation marksLike I will put single quotation marksupper case j and I will put percentupper case j and I will put percentupper case j and I will put percentand now Rand now Rand now Rthis now get back All thethis now get back All thethis now get back All thePersons whose first name starts with thePersons whose first name starts with thePersons whose first name starts with theletter j as you can see here if you want toletter j as you can see here if you want toletter j as you can see here if you want tosee how many People there are cansee how many People there are cansee how many People there are canscroll down gna see 65 or can type herecount count and if Rcount count and if Rcount count and if Rthis It will return 65 are 65 Personsthis It will return 65 are 65 Personsthis It will return 65 are 65 Personswhose wither letter j and can type herewhose wither letter j and can type herewhose wither letter j and can type hereand Let's say last undand Let's say last undand Let's say last undname like I'm gna put like singlename like I'm gna put like singlename like I'm gna put like singlequotation marks I'm gna putquotation marks I'm gna putquotation marks I'm gna puts per on Sign so now if you Runs per on Sign so now if you Runs per on Sign so now if you Runs as you can see you get Five firsts as you can see you get Five firsts as you can see you get Five firstPersons back whose first name starts withPersons back whose first name starts withPersons back whose first name starts withupper case letter j and whose last nameupper case letter j and whose last nameupper case letter j and whose last namestarts with the upper case letter s asstarts with the upper case letter s asstarts with the upper case letter s asyou can see here and this is caseyou can see here and this is caseyou can see here and this is casesensitive if i put lower case j here andsensitive if i put lower case j here andsensitive if i put lower case j here andif i Runif i Runif i Runthis now not anything back because nowthis now not anything back because nowthis now not anything back because nowthe like CAS sensitive so It will matchthe like CAS sensitive so It will matchthe like CAS sensitive so It will matchthe exact letter so if it's an upperthe exact letter so if it's an upperthe exact letter so if it's an uppercase j It will match Only the upper CAScase j It will match Only the upper CAScase j It will match Only the upper CASj in first name so if you want to Don'tj in first name so if you want to Don'tj in first name so if you want to Don'tbe CAS sensitive you type here I likebe CAS sensitive you type here I likebe CAS sensitive you type here I likeand here also Iand here also Iand here also Ilikenownownowj Jeffj Jeffj Jeffjonan andjimy and thisjimy and thisjimy and thisis this On The next line This is How weis this On The next line This is How weis this On The next line This is How wecan use now say that I want to getcan use now say that I want to getcan use now say that I want to getback letback letback letsay firstsay firstsay firstname and Let's putname and Let's putname and Let's putlike letters like I want to get All thelike letters like I want to get All thelike letters like I want to get All thePersons whose first namePersons whose first namePersons whose first namehave in it has the er Present So whathave in it has the er Present So whathave in it has the er Present So whatwhat can i do to achieve that in Orderwhat can i do to achieve that in Orderwhat can i do to achieve that in Orderto achieve that I'm gna put singleto achieve that I'm gna put singleto achieve that I'm gna put singlequotation marks I'm gna putles G put percent Sign so G say Get Allles G put percent Sign so G say Get Allles G put percent Sign so G say Get Allthethethecharacters which are Before er and whichcharacters which are Before er and whichcharacters which are Before er and whichare after So what This is gna do is gnaare after So what This is gna do is gnaare after So what This is gna do is gnareturn All the names which have in theirreturn All the names which have in theirreturn All the names which have in theircharacters which hasn't their name thecharacters which hasn't their name thecharacters which hasn't their name thePresent er e and R and you will not carePresent er e and R and you will not carePresent er e and R and you will not careabout What is Before the and What isabout What is Before the and What isabout What is Before the and What isafter the so this is what you're sayingafter the so this is what you're sayingafter the so this is what you're sayinghere so now if you Run this as you canhere so now if you Run this as you canhere so now if you Run this as you cansee you have Jennifer you have Kimberlysee you have Jennifer you have Kimberlysee you have Jennifer you have Kimberlycerine so those All of those nam havecerine so those All of those nam havecerine so those All of those nam havePresent in their Construction theirPresent in their Construction theirPresent in their Construction theirfirst name theput undput undput undundoperator accept single letter Beforeoperator accept single letter Beforeoperator accept single letter Beforethe Tera Berta Veronica havethe Tera Berta Veronica havethe Tera Berta Veronica haveletterandandandafter the saying We don't care What Whatafter the saying We don't care What Whatafter the saying We don't care What Whatis the number of of characters whichis the number of of characters whichis the number of of characters whichcame Before the er so you can also putcame Before the er so you can also putcame Before the er so you can also puthere another underscore and now we G tohere another underscore and now we G tohere another underscore and now we G toaccept only a letter Before the er and aaccept only a letter Before the er and aaccept only a letter Before the er and aletter after the er so now if you onletter after the er so now if you onletter after the er so now if you onthis Get only this because This is thethis Get only this because This is thethis Get only this because This is theonly which has a letter Before the and theonly which has a letter Before the and theonly which has a letter Before the and theletter after the so you get onlyletter after the so you get onlyletter after the so you get onlyVera as you can see here so this can useVera as you can see here so this can useVera as you can see here so this can usethe like and I like and you can also putthe like and I like and you can also putthe like and I like and you can also puthere not like so can put not like so nowhere not like so can put not like so nowhere not like so can put not like so nowif Runif Runif Runthis Get All the first names which whichthis Get All the first names which whichthis Get All the first names which whichdon't have The The er Present in theirdon't have The The er Present in theirdon't have The The er Present in theirname don't have this Constructionname don't have this Constructionname don't have this Constructionspecific a letter Before the er andspecific a letter Before the er andspecific a letter Before the er andletter after the er so Get as can a lotletter after the er so Get as can a lotletter after the er so Get as can a lotof names which are which are matchingof names which are which are matchingof names which are which are matchingthis Pattern right I'm gna end thisthis Pattern right I'm gna end thisthis Pattern right I'm gna end thisvideo here and see you In The nextvideo here and see you In The nextvideo here and see you In The nextvideo welcome back now it's time to dovideo welcome back now it's time to dovideo welcome back now it's time to dosome challenges with the knowledge thatsome challenges with the knowledge thatsome challenges with the knowledge thatwe have a Community so far and the firstwe have a Community so far and the firstwe have a Community so far and the firstChallenge is to retrieve the first nameChallenge is to retrieve the first nameChallenge is to retrieve the first nameand last name and email from alland last name and email from alland last name and email from allcustomers so This is the first Challengecustomers so This is the first Challengecustomers so This is the first Challengeand next I'm gna give you some hints andand next I'm gna give you some hints andand next I'm gna give you some hints andthe hints are to use the Selectthe hints are to use the Selectthe hints are to use the Selectstatement to choose specific columnsstatement to choose specific columnsstatement to choose specific columnsfrom the customer table and now I'm gnafrom the customer table and now I'm gnafrom the customer table and now I'm gnashow The solution The solution is toshow The solution The solution is toshow The solution The solution is totype Select first name last name emailtype Select first name last name emailtype Select first name last name emailfromfromfromright now Let's do the secondright now Let's do the secondright now Let's do the secondChallenge the second Challenge is calledChallenge the second Challenge is calledChallenge the second Challenge is calledconditional retrieval so challenges toconditional retrieval so challenges toconditional retrieval so challenges toretrieve the payment id customer id andretrieve the payment id customer id andretrieve the payment id customer id andamount from payments With An amountamount from payments With An amountamount from payments With An amountgreater than 100 and I'm gna give yougreater than 100 and I'm gna give yougreater than 100 and I'm gna give yousome hints now hints use the work clusesome hints now hints use the work clusesome hints now hints use the work cluseto filter R rows based on a conditionto filter R rows based on a conditionto filter R rows based on a conditionand now the solution solution is toand now the solution solution is toand now the solution solution is toSelect payment id customer id amountSelect payment id customer id amountSelect payment id customer id amountfrom payment Where amount is greaterfrom payment Where amount is greaterfrom payment Where amount is greaterthan 100 so This Is The solution Thethan 100 so This Is The solution Thethan 100 so This Is The solution Thenext Challenge we have sorting and limitnext Challenge we have sorting and limitnext Challenge we have sorting and limitlimiting and the Challenge is to reevelimiting and the Challenge is to reevelimiting and the Challenge is to reevethe first name last name and theil ofthe first name last name and theil ofthe first name last name and theil ofcustomers sorted by last name incustomers sorted by last name incustomers sorted by last name inascending Order and limit the results toascending Order and limit the results toascending Order and limit the results tofive so This is the Challenge now let'sfive so This is the Challenge now let'sfive so This is the Challenge now let'sgive you give you some hints and thegive you give you some hints and thegive you give you some hints and thehints is to use the Order by Close tohints is to use the Order by Close tohints is to use the Order by Close tosol the result and the limit Close tosol the result and the limit Close tosol the result and the limit Close torestrict the number of rowsrestrict the number of rowsrestrict the number of rowsreturned now the solution is to typereturned now the solution is to typereturned now the solution is to typeSelect first name last name email fromSelect first name last name email fromSelect first name last name email fromcustomercustomercustomerOrder by last name ascendingOrder by last name ascendingOrder by last name ascendingascending limit to f this now Challengeascending limit to f this now Challengeascending limit to f this now Challengenumbernumbernumberf data filtering Challenge retrieve thef data filtering Challenge retrieve thef data filtering Challenge retrieve thepayment ID customer and payment datepayment ID customer and payment datepayment ID customer and payment datefrom payments made in the year2022 give H hints use the and this is2022 give H hints use the and this is2022 give H hints use the and this iswhatwhatwhatandandandto use the extract function ex specificto use the extract function ex specificto use the extract function ex specificcomponents like here from a date andcomponents like here from a date andcomponents like here from a date andthis is aggregate function I'm gna lookthis is aggregate function I'm gna lookthis is aggregate function I'm gna lookat functions In The next chapter But Iat functions In The next chapter But Iat functions In The next chapter But Ithought that's a good to give you athought that's a good to give you athought that's a good to give you aChallenge with this right solutionChallenge with this right solutionChallenge with this right solutionSelect from payment Select payment idSelect from payment Select payment idSelect from payment Select payment idcustomer id payment date from paymentcustomer id payment date from paymentcustomer id payment date from paymentWhere and we have the extract functionWhere and we have the extract functionWhere and we have the extract functionhere from payment datehere from payment datehere from payment dateequs2022 next CH number Five is called2022 next CH number Five is called2022 next CH number Five is calledPattern matching and the Challenge is toPattern matching and the Challenge is toPattern matching and the Challenge is toreceive the first name last name andreceive the first name last name andreceive the first name last name andemail of customers whose email containsemail of customers whose email containsemail of customers whose email containstheGmail I'm gna give you some hints NowGmail I'm gna give you some hints NowGmail I'm gna give you some hints Nowhints is to use the like operator alonghints is to use the like operator alonghints is to use the like operator alongwith the wild cards percent and sign towith the wild cards percent and sign towith the wild cards percent and sign toperform Patternperform Patternperform Patternmatching now I'm gna give you thematching now I'm gna give you thematching now I'm gna give you thesolution and the solution is Select thesolution and the solution is Select thesolution and the solution is Select thefirst name last name email from customerfirst name last name email from customerfirst name last name email from customerWhere email like and single quotationWhere email like and single quotationWhere email like and single quotationMark per side Gmail perMark per side Gmail perMark per side Gmail perSign and closing single quotation MarkSign and closing single quotation MarkSign and closing single quotation Markso This is the solution now Challengeso This is the solution now Challengeso This is the solution now Challengenumber six is ​​ to is called Range querynumber six is ​​to is called Range querynumber six is ​​to is called Range queryand here you need to retrieve theand here you need to retrieve theand here you need to retrieve thepayment id customer id and amount frompayment id customer id and amount frompayment id customer id and amount frompayments With An amount between 50 andpayments With An amount between 50 andpayments With An amount between 50 and100100100inclusive hence use the between operatorinclusive hence use the between operatorinclusive hence use the between operatorto specify Range and now the solution is toto specify Range and now the solution is toto specify Range and now the solution is totype Select payment id customer idtype Select payment id customer idtype Select payment id customer idamount from payment where mount isamount from payment where mount isamount from payment where mount isbetween 50 and 100 All Right I'm G tobetween 50 and 100 All Right I'm G tobetween 50 and 100 All Right I'm G toend this video here and see you In Theend this video here and see you In Theend this video here and see you In Thenextvideo welcome back now it's time tovideo welcome back now it's time tovideo welcome back now it's time tostart a discussion about aggregatestart a discussion about aggregatestart a discussion about aggregatefunctions So what are aggregatefunctions So what are aggregatefunctions So what are aggregatefunctions in post gr aggregate functionsfunctions in post gr aggregate functionsfunctions in post gr aggregate functionsare special functions that performare special functions that performare special functions that performcalculations on a set of values ​​ andcalculations on a set of values ​​andcalculations on a set of values ​​andreturn a single value As A result thesereturn a single value As A result thesereturn a single value As A result thesefunctions are commonly used to summarizefunctions are commonly used to summarizefunctions are commonly used to summarizeor derive meaningful in size from largeor derive meaningful in size from largeor derive meaningful in size from largedatas in a database instead of operatingdatas in a database instead of operatingdatas in a database instead of operatingon individual rows functions work on aon individual rows functions work on aon individual rows functions work on aGroup of rows and return a single valueGroup of rows and return a single valueGroup of rows and return a single valuefor each Group the are crucial for tasfor each Group the are crucial for tasfor each Group the are crucial for tascalculating averes sums Counts minimumcalculating averes sums Counts minimumcalculating averes sums Counts minimumand maximum values ​​ andand maximum values ​​andand maximum values ​​andmore next wemore next wemore next wehave Key aspects of aggregate functionshave Key aspects of aggregate functionshave Key aspects of aggregate functionsand first have grouping Grow aggregateand first have grouping Grow aggregateand first have grouping Grow aggregatefunctions are Often used in conjunctionfunctions are Often used in conjunctionfunctions are Often used in conjunctionwith the Group by cluse that we gnawith the Group by cluse that we gnawith the Group by cluse that we gnastudy In The next videos and the Groupstudy In The next videos and the Groupstudy In The next videos and the Groupby Close divide the result set intoby Close divide the result set intoby Close divide the result set intogroups based on One or more colsgrpgrpgrpand have aggregate functions and provideand have aggregate functions and provideand have aggregate functions and providea Wide Range of aggregate functions thata Wide Range of aggregate functions thata Wide Range of aggregate functions thatCarry to various analytical needs someCarry to various analytical needs someCarry to various analytical needs someof the most common aggregate functionsof the most common aggregate functionsof the most common aggregate functionsinclude sum which calculated the sum of ainclude sum which calculated the sum of ainclude sum which calculated the sum of anumeric column within a Group averagenumeric column within a Group averagenumeric column within a Group averagewhich compute the average of a numericwhich compute the average of a numericwhich compute the average of a numericcolumn within a Group count which thecolumn within a Group count which thecolumn within a Group count which thenumber of Group use this meum value fromnumber of Group use this meum value fromnumber of Group use this meum value fromcolumn return Sorry so mean which returnscolumn return Sorry so mean which returnscolumn return Sorry so mean which returnsthe minimum value from a column within athe minimum value from a column within athe minimum value from a column within aGroup and Max which returns the maximumGroup and Max which returns the maximumGroup and Max which returns the maximumvalue from a column within a Group rightvalue from a column within a Group rightvalue from a column within a Group rightnow Let's export those functions in PGnow Let's export those functions in PGnow Let's export those functions in PGadmin and I'm gna use the FM table I'm Gadmin and I'm gna use the FM table I'm Gadmin and I'm gna use the FM table I'm GtytytySelect everything fromSelect everything fromSelect everything fromfilm semicolon so Let's Run it and wefilm semicolon so Let's Run it and wefilm semicolon so Let's Run it and wehave film ID title description and youhave film ID title description and youhave film ID title description and youhave release here language ID rentalhave release here language ID rentalhave release here language ID rentalduration rental length and replacement cdeldeldelSelectSelectSelectpares speciftablecccfromfilmfilmfilmGet 99 so This is the minimum value andGet 99 so This is the minimum value andGet 99 so This is the minimum value andyou can also Get the maximum value canyou can also Get the maximum value canyou can also Get the maximum value candel this me type heredel this me type heredel this me type hereMax replacement from Let's Run it andMax replacement from Let's Run it andMax replacement from Let's Run it andGet 29 and 99 so This is the maximumGet 29 and 99 so This is the maximumGet 29 and 99 so This is the maximumreplacement c for a movie and What Youreplacement c for a movie and What Youreplacement c for a movie and What Youcan't do is to Select another columnherethe can Select table a table you cannotthe can Select table a table you cannotthe can Select table a table you cannotSelect a column here because thisSelect a column here because thisSelect a column here because thisaggregate function returns Only oneaggregate function returns Only oneaggregate function returns Only onevalue and we only Get that value backvalue and we only Get that value backvalue and we only Get that value backand Because I put here also Select theand Because I put here also Select theand Because I put here also Select therating from film It will Select All therating from film It will Select All therating from film It will Select All theratings from from film and here we sayratings from from film and here we sayratings from from film and here we saythat want value back One single value soWhatWhatWhatcan and I can ty herecan and I can ty herecan and I can ty heremean Endless typemean Endless typemean Endless typereplacementreplacementreplacementCast from now Run this now this willCast from now Run this now this willCast from now Run this now this willworkworkworkget9 maximum value and the minimum valueget9 maximum value and the minimum valueget9 maximum value and the minimum value99 as you can see here now Let's look at99 as you can see here now Let's look at99 as you can see here now Let's look atthe average function delsthe average function delsthe average function delsand to get the age AVG also for theand to get the age AVG also for theand to get the age AVG also for thereplacementCast Let's Runit8 and have a lot of decimal points nowit8 and have a lot of decimal points nowit8 and have a lot of decimal points nowWe can round this to only a specificWe can round this to only a specificWe can round this to only a specificamount of decimal points by puttingamount of decimal points by puttingamount of decimal points by puttingroundroundroundparentheses and In this round we need toparentheses and In this round we need toparentheses and In this round we need tospecify first the value that you want tospecify first the value that you want tospecify first the value that you want toround and the number of digits that youround and the number of digits that youround and the number of digits that youwant the value to be round the Two so I'm going towant the value to be round the Two so I'm going towant the value to be round the Two so I'm going toput here Let's say knowput here Let's say knowput here Let's say knowthree closing parentheses and now if youthree closing parentheses and now if youthree closing parentheses and now if youRunRunRunthis We Get 90this We Get 90this We Get 909 9 84 All Right so This is how you can9 9 84 All Right so This is how you can9 9 84 All Right so This is how you canuse the round and you can also Let's sayuse the round and you can also Let's sayuse the round and you can also Let's saythat I want to replacemy l and I want to see how much it costs memy l and I want to see how much it costs memy l and I want to see how much it costs meto replace all those Movies in Order toto replace all those Movies in Order toto replace all those Movies in Order tothat need to use the functionthat need to use the functionthat need to use the functionI'mtye sureplacementreplacementreplacementCast All thecolumn884 as can here al right I 'm G end this884 as can here al right I 'm G end this884 as can here al right I 'm G end thisvideo here and see you The nextvideo here and see you The nextvideo here and see you The nextvideo welcome back now time to start avideo welcome back now time to start avideo welcome back now time to start adiscussion about the Group by command indiscussion about the Group by command indiscussion about the Group by command inpost gr So what is the Group by commandpost gr So what is the Group by commandpost gr So what is the Group by commandin post gr or more specific What isin post gr or more specific What isin post gr or more specific What isGroup by and why is necessary in theGroup by and why is necessary in theGroup by and why is necessary in theGroup by cluse is used to Group R fromors grouping enables application ofors grouping enables application ofors grouping enables application ofaggregate functions such as some countaggregate functions such as some countaggregate functions such as some countaverage mean and Max on subset of dataaverage mean and Max on subset of dataaverage mean and Max on subset of datasharing common values ​​ in the specifisharing common values ​​in the specifisharing common values ​​in the specificolumns essentially it transformscolumns essentially it transformscolumns essentially it transformsindividual rows into summarizeindividual rows into summarizeindividual rows into summarizeinformation aid in the analysis of linformation aid in the analysis of linformation aid in the analysis of llarge data sets and revealing trends andlarge data sets and revealing trends andlarge data sets and revealing trends andins that might not be immediatelyins that might not be immediatelyins that might not be immediatelyapparent for the Group by CL to beapparent for the Group by CL to beapparent for the Group by CL to beeffective it's Essential to Selecteffective it's Essential to Selecteffective it's Essential to Selectcategorical or discrete columns thesecategorical or discrete columns thesecategorical or discrete columns thesecolumns contain discrete values ​​ Oftencolumns contain discrete values ​​Oftencolumns contain discrete values ​​Oftenrepresenting category category classes orrepresenting category category classes orrepresenting category category classes orlabels This distinction is cruciallabels This distinction is cruciallabels This distinction is crucialbecause aggregate functions arebecause aggregate functions arebecause aggregate functions aremeaningful only when applied tomeaningful only when applied tomeaningful only when applied tocategorical data as attempting tocategorical data as attempting tocategorical data as attempting toaggregate continuous data could toaggregate continuous data could toaggregate continuous data could tononsenseresults now the Group by operationresults now the Group by operationresults now the Group by operationfollows a Split apply combine processfollows a Split apply combine processfollows a Split apply combine processLet's consider hypothetical sales tableLet's consider hypothetical sales tableLet's consider hypothetical sales tablewith column region product and saleswith column region product and saleswith column region product and salesamount to analyze the total sales foramount to analyze the total sales foramount to analyze the total sales foreach region we apply the following stepseach region we apply the following stepseach region we apply the following stepsweweweSplit first Step is Split and theSplit first Step is Split and theSplit first Step is Split and thedataset is basically divided into groupsdataset is basically divided into groupsdataset is basically divided into groupsbased on Unique values ​​ In The regionbased on Unique values ​​In The regionbased on Unique values ​​In The regioncolumn The apply aggregate functionscolumn The apply aggregate functionscolumn The apply aggregate functionssuch as some sales amount are applied tosuch as some sales amount are applied tosuch as some sales amount are applied tothe Sal amount column within region Groupthe Sal amount column within region Groupthe Sal amount column within region Groupcombine the agre are combined creating acombine the agre are combined creating acombine the agre are combined creating asummary table that displays the total ssummary table that displays the total ssummary table that displays the total sfor each region as you will immediatelyfor each region as you will immediatelyfor each region as you will immediatelysee In this diagram so suppose you havesee In this diagram so suppose you havesee In this diagram so suppose you havethe following sales table and you havethe following sales table and you havethe following sales table and you haveThe region The PR the sales amount andThe region The PR the sales amount andThe region The PR the sales amount andyou have the region North South and weyou have the region North South and weyou have the region North South and wehave the product Aa b and we have thehave the product Aa b and we have thehave the product Aa b and we have thesales amount and we have 1000 50,000sales amount and we have 1000 50,000sales amount and we have 1000 50,00012,000 and 1800012,000 and 1800012,000 and 18000and by applying the Group by region weand by applying the Group by region weand by applying the Group by region weobtain North and South and North it hasobtain North and South and North it hasobtain North and South and North it has22000 and South is22000 and South is22000 and South is32000 because we apply some on sales32000 because we apply some on sales32000 because we apply some on salesamount column soamount column soamount column sonow This is how that will look It willnow This is how that will look It willnow This is how that will look It willdivide our our and the categorical datadivide our our and the categorical datadivide our our and the categorical datahere is the region because we Group byhere is the region because we Group byhere is the region because we Group bythat One now When and How to use Groupthat One now When and How to use Groupthat One now When and How to use Groupby best practices and considerations theby best practices and considerations theby best practices and considerations theGroup by clause serves various purposesGroup by clause serves various purposesGroup by clause serves various purposesand scenarios is this particularlyand scenarios is this particularlyand scenarios is this particularlyvaluable When aggregate data to computevaluable When aggregate data to computevaluable When aggregate data to computesummary statistics such as total agessummary statistics such as total agessummary statistics such as total agesor Counts for Distinct categories withor Counts for Distinct categories withor Counts for Distinct categories within a dataset segmentation to analyzein a dataset segmentation to analyzein a dataset segmentation to analyzedata by discrete attributes aid in thedata by discrete attributes aid in thedata by discrete attributes aid in theidentifying patterns and trendsidentifying patterns and trendsidentifying patterns and trendsreporting for generating summarizereporting for generating summarizereporting for generating summarizereports and dashboards simply Comp datareports and dashboards simply Comp datareports and dashboards simply Comp datafor decisionfor decisionfor decisionmakers When using gr severalmakers When using gr severalmakers When using gr severalconsideration and warrant attentionconsideration and warrant attentionconsideration and warrant attentioncolumn selection choose categoricalcolumn selection choose categoricalcolumn selection choose categoricalcolumns relevant to the analysiscolumns relevant to the analysiscolumns relevant to the analysisgrouping by too many columns can lead togrouping by too many columns can lead togrouping by too many columns can lead toexcessive complexity and informationexcessive complexity and informationexcessive complexity and informationoverload aggregate functions Selectoverload aggregate functions Selectoverload aggregate functions Selectappropriate aggregate functions based onappropriate aggregate functions based onappropriate aggregate functions based oninsights you seek applying incorrectinsights you seek applying incorrectinsights you seek applying incorrectfunctions can dist the interpretationfunctions can dist the interpretationfunctions can dist the interpretationofofofsortingsortingsortingspecif sting Order using Order by cl arspecif sting Order using Order by cl arspecif sting Order using Order by cl argroups ors in meaningful filteringgroups ors in meaningful filteringgroups ors in meaningful filteringincorporate filtering criteria using theincorporate filtering criteria using theincorporate filtering criteria using thehaving cl gna look at this In The nexthaving cl gna look at this In The nexthaving cl gna look at this In The nextvideos to Focus on specific data subsvideos to Focus on specific data subsvideos to Focus on specific data subswithingroups common pfs and mistakes to avoidgroups common pfs and mistakes to avoidgroups common pfs and mistakes to avoidwhile gr is a powerful tool manding itwhile gr is a powerful tool manding itwhile gr is a powerful tool manding itcan lead toing or incorrectcan lead toing or incorrectcan lead toing or incorrectcommon mistakes to avoid missing columnscommon mistakes to avoid missing columnscommon mistakes to avoid missing columnsWhen selecting columns ensure that TheyWhen selecting columns ensure that TheyWhen selecting columns ensure that Theyare either part of the Group by clauseare either part of the Group by clauseare either part of the Group by clauseor are being used with the appropriateor are being used with the appropriateor are being used with the appropriateaggregate functions overlooking nullsaggregate functions overlooking nullsaggregate functions overlooking nullsby Be mindful on of null values ​​ as Theyby Be mindful on of null values ​​as Theyby Be mindful on of null values ​​as Theycan affect grouping and aggregationcan affect grouping and aggregationcan affect grouping and aggregationresults use functions like cols toresults use functions like cols toresults use functions like cols tohandle nulls appropriate gonna look athandle nulls appropriate gonna look athandle nulls appropriate gonna look atthis command In The next videos now nextthis command In The next videos now nextthis command In The next videos now nexthave mpl we and heav closes placehave mpl we and heav closes placehave mpl we and heav closes placefiltering criteria and the we Close tofiltering criteria and the we Close tofiltering criteria and the we Close tofilter data Before grouping use havingfilter data Before grouping use havingfilter data Before grouping use havingCLACLACLAG to filter aggregate results afterG to filter aggregate results afterG to filter aggregate results aftergrouping inconsistent aggregation begrouping inconsistent aggregation begrouping inconsistent aggregation beconsistent with aggregate functions andconsistent with aggregate functions andconsistent with aggregate functions andgrouping columns mixing levels ofgrouping columns mixing levels ofgrouping columns mixing levels ofaggregation within the same query canaggregation within the same query canaggregation within the same query canlead tolead tolead toconfusion in conclusion the Group byconfusion in conclusion the Group byconfusion in conclusion the Group bycommand inic is an indispensable toolcommand inic is an indispensable toolcommand inic is an indispensable toolfor analyzing and summar data transs Rfor analyzing and summar data transs Rfor analyzing and summar data transs Rdata meaningful by applying aggregatedata meaningful by applying aggregatedata meaningful by applying aggregatefunctions to categorical cols as you Sawfunctions to categorical cols as you Sawfunctions to categorical cols as you Sawin that previous example byin that previous example byin that previous example byunderstanding the Split apply combineunderstanding the Split apply combineunderstanding the Split apply combineprocess and ad hering to the bestprocess and ad hering to the bestprocess and ad hering to the bestpractices users can harness The power ofpractices users can harness The power ofpractices users can harness The power ofGroup by to uncover hidden patterns andGroup by to uncover hidden patterns andGroup by to uncover hidden patterns andtrends within datasets however cautiontrends within datasets however cautiontrends within datasets however cautioncaution must be exercised to avoid commoncaution must be exercised to avoid commoncaution must be exercised to avoid commonpfs that can compromise the accuracy andpfs that can compromise the accuracy andpfs that can compromise the accuracy andreabil of the results with proper usereabil of the results with proper usereabil of the results with proper usethe Group Group bu Close can become anthe Group Group bu Close can become anthe Group Group bu Close can become aninvaluable Asset for Any databaseinvaluable Asset for Any databaseinvaluable Asset for Any databaseanalyst or dataanalyst or dataanalyst or dataProfessional enhancing their ability toProfessional enhancing their ability toProfessional enhancing their ability todistill complex data into Actiondistill complex data into Actiondistill complex data into Actionknowledge All Right I'm gna end thisknowledge All Right I'm gna end thisknowledge All Right I'm gna end thisvideo here and see you In The nextvideo welcome back now time to start avideo welcome back now time to start avideo welcome back now time to start adiscussion about the Group by statementdiscussion about the Group by statementdiscussion about the Group by statementin Greek post but first Let's explorein Greek post but first Let's explorein Greek post but first Let's explorethe payment table to see What we havethe payment table to see What we havethe payment table to see What we havethere I'm G typ Select everything fromthere I'm G typ Select everything fromthere I'm G typ Select everything frompayment Let's Run this and we have thepayment Let's Run this and we have thepayment Let's Run this and we have thepayment id customer id staff id rental idpayment id customer id staff id rental idpayment id customer id staff id rental idand amount and we have the paymentand amount and we have the paymentand amount and we have the paymentdate So what we have here and thisdate So what we have here and thisdate So what we have here and thiscustomer ID is categorical G to use thiscustomer ID is categorical G to use thiscustomer ID is categorical G to use thisto Group Group by statement to Group ourto Group Group by statement to Group ourto Group Group by statement to Group ourcustomers now What we have here is thecustomers now What we have here is thecustomers now What we have here is thecustomer ID the stuff with which thecustomer ID the stuff with which thecustomer ID the stuff with which thecustomer intera so have the customer IDcustomer intera so have the customer IDcustomer intera so have the customer IDand it intera with differentand it intera with differentand it intera with differentstuffstuffstuffand different movies and later see howand different movies and later see howand different movies and later see howcan join this rental ID to get AC moviecan join this rental ID to get AC moviecan join this rental ID to get AC movieand have the amount spend on rentingand have the amount spend on rentingand have the amount spend on rentingparticularparticularparticulartransactiontransactiontransactionnow's Select the customernow's Select the customernow's Select the customerID Run simple queryID Run simple queryID Run simple queryhere fromhere fromhere frompayment Let'sgr customergr customergr customerIDand Let's Runand Let's Runand Let's Runthis and now I have the customer ids asthis and now I have the customer ids asthis and now I have the customer ids asyou can see here and this is the sameyou can see here and this is the sameyou can see here and this is the samething as using thething as using thething as using theDistinct stat keyword So what we haveDistinct stat keyword So what we haveDistinct stat keyword So what we havehere is the customer ID and this Grouphere is the customer ID and this Grouphere is the customer ID and this Groupby the also by the customer ID say thatby the also by the customer ID say thatby the also by the customer ID say thatI want to see The amount The totalI want to see The amount The totalI want to see The amount The totalamount spend byamount spend byamount spend bytotal am per customer ID put typetotal am per customer ID put typetotal am per customer ID put typeG use the aggregate function G theG use the aggregate function G theG use the aggregate function G theamount Group by customer ID and Let'samount Group by customer ID and Let'samount Group by customer ID and Let'sRun this to see What to get back and nowRun this to see What to get back and nowRun this to see What to get back and nowget the total amount spend by eachget the total amount spend by eachget the total amount spend by eachcustomer IDcustomer IDcustomer ID10084 spend probably This10084 spend probably This10084 spend probably Thisis 8 and 0 and you have customer ID 87is 8 and 0 and you have customer ID 87is 8 and 0 and you have customer ID 87we have this amount and so on so now Wewe have this amount and so on so now Wewe have this amount and so on so now Weare getting the total amount spend perare getting the total amount spend perare getting the total amount spend percustomer ID now Let's say that that Icustomer ID now Let's say that that Icustomer ID now Let's say that that Iwant to Order this I can Order by the Iwant to Order this I can Order by the Iwant to Order this I can Order by the Ican type here Order buy and I can't typecan type here Order buy and I can't typecan type here Order buy and I can't typehere simply Order by amount becausehere simply Order by amount becausehere simply Order by amount becausethis column that we have here is the sumthis column that we have here is the sumthis column that we have here is the sumamount so is the total su amount It'samount so is the total su amount It'samount so is the total su amount It'snot simply su so you need to use herenot simply su so you need to use herenot simply su so you need to use hereOrder byOrder byOrder bysum and amount because this is what Thissum and amount because this is what Thissum and amount because this is what Thiscolumn is that we see here so now if Runcolumn is that we see here so now if Runcolumn is that we see here so now if Runthis as you can see now Order ascendingthis as you can see now Order ascendingthis as you can see now Order ascendingwe have 318 you have 27 so on and herewe have 318 you have 27 so on and herewe have 318 you have 27 so on and hereyou can specify ascending or descendingyou can specify ascending or descendingyou can specify ascending or descendingso if you Run this you get Same outputso if you Run this you get Same outputso if you Run this you get Same outputand if i typeand if i typeand if i typedescending Let's Rundescending Let's Rundescending Let's Runit nowit nowit nowget now get which customer whichget now get which customer whichget now get which customer whichcustomers spend the most amount oncustomers spend the most amount oncustomers spend the most amount onrenting so This is ordering descendingrenting so This is ordering descendingrenting so This is ordering descendingnow Let's say that I want to Selectnow Let's say that I want to Selectnow Let's say that I want to Selectmultiple columns in our in our query howmultiple columns in our in our query howmultiple columns in our in our query howi can do that what i can do is I cani can do that what i can do is I cani can do that what i can do is I cantype Let'stype Let'stype Let'sdeletedeletedeletedel this Let's Select Again from paymentdel this Let's Select Again from paymentdel this Let's Select Again from paymentbecause want to see the table Again letbecause want to see the table Again letbecause want to see the table Again letRun it and let say that I want to getRun it and let say that I want to getRun it and let say that I want to getthe customerthe customerthe customerID I want to get theID I want to get theID I want to get thecustomercustomercustomerID the staffID andID andID andaggregate suaggregate suaggregate suamount fromamount fromamount frompayment G type here Grouppayment G type here Grouppayment G type here Groupbuy and gna Group by the customerbuy and gna Group by the customerbuy and gna Group by the customerID customer und ID and by staffID customer und ID and by staffID customer und ID and by staffID and gnaID and gnaID and gnaOrder Order byOrder Order byOrder Order bysome amountI'm gna explain immediately What haveI'm gna explain immediately What haveI'm gna explain immediately What havehere here should be stuff stuff ID sohere here should be stuff stuff ID sohere here should be stuff stuff ID solet Runlet Runlet Runthis now What we have here is we havethis now What we have here is we havethis now What we have here is we havethe we have the sum we have the staff IDthe we have the sum we have the staff IDthe we have the sum we have the staff IDand the custom ID and How we will Readand the custom ID and How we will Readand the custom ID and How we will Readthis you Read this in the following waythis you Read this in the following waythis you Read this in the following waywe have the total amount spent with thiswe have the total amount spent with thiswe have the total amount spent with thisstuff ID by this customerstuff ID by this customerstuff ID by this customerID total amount SP by staff per staff IDID total amount SP by staff per staff IDID total amount SP by staff per staff IDand per customer ID and as you can seeand per customer ID and as you can seeand per customer ID and as you can seehere we have another customer ID spendhere we have another customer ID spendhere we have another customer ID spendwith this stuff ID and have the amountwith this stuff ID and have the amountwith this stuff ID and have the amountso Again we have here we have the someso Again we have here we have the someso Again we have here we have the someamount spent per staffamount spent per staffamount spent per staffID the amount spent which thisID the amount spent which thisID the amount spent which thisparticular staff staff ID by per by thisparticular staff staff ID by per by thisparticular staff staff ID by per by thiscustomer ID thishere al right now Let's say Let's Let'shere al right now Let's say Let's Let'shere al right now Let's say Let's Let'sdel this and one more thing to Pointdel this and one more thing to Pointdel this and one more thing to Pointhere is that you can change the Orderhere is that you can change the Orderhere is that you can change the Orderhere so Ihere so Ihere so Ican I can put first staff ID and thencan I can put first staff ID and thencan I can put first staff ID and thencustomercustomercustomerstaff staff und ID and then the customerstaff staff und ID and then the customerstaff staff und ID and then the customerID and this isID and this isID and this isnot have the same data displaynot have the same data displaynot have the same data displayhavehavehavedat display have staff ID and then thedat display have staff ID and then thedat display have staff ID and then thecustomer ID and the amount so you cancustomer ID and the amount so you cancustomer ID and the amount so you canchange this if you want It will notchange this if you want It will notchange this if you want It will notaffect the query stuffaffect the query stuffaffect the query stuffID now Let's deletethis and Let's explore Again the paymentthis and Let's explore Again the paymentthis and Let's explore Again the paymenttable Let's Runtable Let's Runtable Let's Runititital right and Let's say that I want toal right and Let's say that I want toal right and Let's say that I want toLet me show you first a function WhichLet me show you first a function WhichLet me show you first a function Whichis the date function to use function onis the date function to use function onis the date function to use function onthis payment dat that we have here andthis payment dat that we have here andthis payment dat that we have here anduse that Because I want to see the someuse that Because I want to see the someuse that Because I want to see the someamount Of of of some amount ofamount Of of of some amount ofamount Of of of some amount oftransactions pertransactions pertransactions pereach in Order toa type here date Select date I'm gnaa type here date Select date I'm gnaa type here date Select date I'm gnatype heretype heretype herepayment date Frompayment date Frompayment date Frompayment type here Grouppayment type here Grouppayment type here Groupbuy Groupbuy so Group by datebuy so Group by datebuy so Group by dateto G use also paymentto G use also paymentto G use also paymentdate and we use this payment use thedate and we use this payment use thedate and we use this payment use thedate and the payment date so you usedate and the payment date so you usedate and the payment date so you usethis thing that we have here becausethis thing that we have here becausethis thing that we have here becauseThis is and I want to get Only the dayThis is and I want to get Only the dayThis is and I want to get Only the dayto get the year the month and the day Ito get the year the month and the day Ito get the year the month and the day Idon't want to get the Hour byusing do that and you can alsousing do that and you can alsousing do that and you can alsoOrder Order buy OrderOrder Order buy OrderOrder Order buy Orderbuy Again bybuy Again bybuy Again bysome and typesome and typesome and typeamountamountamountLet's RunLet's RunLet's Runthis and we have as you can seethis and we have as you can seethis and we have as you can seehere have now we have the dat and have Ihere have now we have the dat and have Ihere have now we have the dat and have Ithink I can increase this Let me see ithink I can increase this Let me see ithink I can increase this Let me see ior Let meor Let meor Let mecheck anyway we have the year the monthcheck anyway we have the year the monthcheck anyway we have the year the monthand the Day and Let's also aggregate soand the Day and Let's also aggregate soand the Day and Let's also aggregate soLet's use the su function here to getLet's use the su function here to getLet's use the su function here to getthe totalthe totalthe totalamounts amountamounts amountamounts amountGet the amount per each Day so Get theGet the amount per each Day so Get theGet the amount per each Day so Get thetotal amount whichtotal amount whichtotal amount whichwas spent by each Day so if you Run thisnow as you can see we have on thisnow as you can see we have on thisnow as you can see we have on thisparticular date 2007 02 have 116 and soparticular date 2007 02 have 116 and soparticular date 2007 02 have 116 and soon so now get the su amount so Get theon so now get the su amount so Get theon so now get the su amount so Get thesu amount per Day and Again we use datesu amount per Day and Again we use datesu amount per Day and Again we use datepayment date Order to get the only topayment date Order to get the only topayment date Order to get the only toget only this to get the date In thisget only this to get the date In thisget only this to get the date In thisparticular format Which is the year theparticular format Which is the year theparticular format Which is the year themonth and the day so not to not includemonth and the day so not to not includemonth and the day so not to not includenot include The The Hour because I don'tnot include The The Hour because I don'tnot include The The Hour because I don'twant towant towant tosee the total amount on eachsee the total amount on eachsee the total amount on eachtransactions per minute I want to seetransactions per minute I want to seetransactions per minute I want to seethem by the day so this Why use Day herethem by the day so this Why use Day herethem by the day so this Why use Day hereand this is our discussion one moreand this is our discussion one moreand this is our discussion one morething that I forgot to show is thatcan Select total amount Select Let'scan Select total amount Select Let'scan Select total amount Select Let'stypehere customerID just a second customerID just a second customerID just a second customerID and instead of some amount I'm G gnaID and instead of some amount I'm G gnaID and instead of some amount I'm G gnauseuseusecount count amount to see the totalcount count amount to see the totalcount count amount to see the totalnumber of transactions per customer IDnumber of transactions per customer IDnumber of transactions per customer IDso Groupso Groupso GroupbuybuybuyGroupGroupGroupbuy and I will typehere Group by customerID Orderbuybuybuycountcountcountamount istotal am of transactions and pertotal am of transactions and pertotal am of transactions and percustomer ID and previously it was askingcustomer ID and previously it was askingcustomer ID and previously it was askingthe Question What is the total amountthe Question What is the total amountthe Question What is the total amountper customerper customerper customernow get this in the output so see thenow get this in the output so see thenow get this in the output so see thecustomer ID 318 had s transactions thecustomer ID 318 had s transactions thecustomer ID 318 had s transactions thecustomer 281 had 10 and alr I'm ending thiscustomer 281 had 10 and alr I'm ending thiscustomer 281 had 10 and alr I'm ending thisvideo here and see you In The nextvideo here and see you In The nextvideo here and see you In The nextvideovideovideowelcome back now time to do somewelcome back now time to do somewelcome back now time to do somechallenges using the knowledge that wechallenges using the knowledge that wechallenges using the knowledge that wehave so far and the first Challenge ishave so far and the first Challenge ishave so far and the first Challenge isto find the total amount collected byto find the total amount collected byto find the total amount collected byeach staffeach staffeach staffmember This is the expected result Listmember This is the expected result Listmember This is the expected result Listof staff ids along with the total amountof staff ids along with the total amountof staff ids along with the total amountThey haveThey haveThey havecollected hint is consider using thecollected hint is consider using thecollected hint is consider using theGroup by clause and aggregate functionsGroup by clause and aggregate functionsGroup by clause and aggregate functionslike sum and the solution is to typelike sum and the solution is to typelike sum and the solution is to typeSelect staff ID column some amount fromSelect staff ID column some amount fromSelect staff ID column some amount frompayment Group buy stuff ID This is thepayment Group buy stuff ID This is thepayment Group buy stuff ID This is thesolution to our first Challenge oursolution to our first Challenge oursolution to our first Challenge oursecond Challenge to list All thesecond Challenge to list All thesecond Challenge to list All thepayments made on a specificpayments made on a specificpayments made on a specificdate expected result a list of paymentsdate expected result a list of paymentsdate expected result a list of paymentsids amounts and payment dates forids amounts and payment dates forids amounts and payment dates forpayment made on specifiedpayment made on specifiedpayment made on specifieddate hint use the we Close to filterdate hint use the we Close to filterdate hint use the we Close to filterrows based on payment AE and this is therows based on payment AE and this is therows based on payment AE and this is thesolution Select payment ID amountsolution Select payment ID amountsolution Select payment ID amountpayment date from payment Where paymentpayment date from payment Where paymentpayment date from payment Where paymentdate equals and we have the year monthdate equals and we have the year monthdate equals and we have the year monthand Day All Right I'm G end this videoand Day All Right I'm G end this videoand Day All Right I'm G end this videohere and see you In The nextvideo welcome back now to start avideo welcome back now to start avideo welcome back now to start adiscussion about the having command indiscussion about the having command indiscussion about the having command inpost gresul So what is the havingpost gresul So what is the havingpost gresul So what is the havingcommand the having command in post grlecommand the having command in post grlecommand the having command in post grleis used in conjunction with the Group byis used in conjunction with the Group byis used in conjunction with the Group byclause to filter the results of a queryclause to filter the results of a queryclause to filter the results of a querybased on the aggregate values ​​ of abased on the aggregate values ​​of abased on the aggregate values ​​of acolumn or columns it allows you to applycolumn or columns it allows you to applycolumn or columns it allows you to applyconditions to the resol seft afterconditions to the resol seft afterconditions to the resol seft aftergrouping has been performed in othergrouping has been performed in othergrouping has been performed in otherwords while the we clos filterswords while the we clos filterswords while the we clos filtersindividual rows Before They are groupedindividual rows Before They are groupedindividual rows Before They are groupedthe having clos filters groups of rowsthe having clos filters groups of rowsthe having clos filters groups of rowsafter They have been grouped here is aafter They have been grouped here is aafter They have been grouped here is abreakdown of its keybreakdown of its keybreakdown of its keyaspects first we haveaspects first we haveaspects first we havepurpose the having clos is used topurpose the having clos is used topurpose the having clos is used tofilter rows in the results afterfilter rows in the results afterfilter rows in the results aftergrouping and aggregation specific Whengrouping and aggregation specific Whengrouping and aggregation specific WhenYou want to filter groups based onYou want to filter groups based onYou want to filter groups based onaggregate values ​​ usage it follows theaggregate values ​​usage it follows theaggregate values ​​usage it follows theGroup by clause and comes after The sqlGroup by clause and comes after The sqlGroup by clause and comes after The sqlquery The basic syntax is Select thequery The basic syntax is Select thequery The basic syntax is Select thecolumn name the aggregate function oncolumn name the aggregate function oncolumn name the aggregate function onthe column from the table Group by thethe column from the table Group by thethe column from the table Group by thecolumn and having at the end having andcolumn and having at the end having andcolumn and having at the end having andput the conditionput the conditionput the conditionso next we have Need for having whileso next we have Need for having whileso next we have Need for having whilethe wear Close filters individual rowsthe wear Close filters individual rowsthe wear Close filters individual rowsBefore grouping the having Close filtersBefore grouping the having Close filtersBefore grouping the having Close filtersgroups of Row based on the aggregategroups of Row based on the aggregategroups of Row based on the aggregateresults This is especially especiallyresults This is especially especiallyresults This is especially especiallyuseful When You want to filter Alluseful When You want to filter Alluseful When You want to filter Allgroups that met certain criteriagroups that met certain criteriagroups that met certain criteriascenario to use you should use havingscenario to use you should use havingscenario to use you should use havingWhen you need to filter out groups onWhen you need to filter out groups onWhen you need to filter out groups onaggregate functions like count sumaggregate functions like count sumaggregate functions like count sumaverage etc for example Finding theaverage etc for example Finding theaverage etc for example Finding theaverage salary of departments With Anaverage salary of departments With Anaverage salary of departments With Anaverage salary salary greater than aaverage salary salary greater than aaverage salary salary greater than acertaincertaincertainvalue next we have comparison with Wherevalue next we have comparison with Wherevalue next we have comparison with Whereuse Where to filter individual rowsuse Where to filter individual rowsuse Where to filter individual rowsbefore grouping and having to filterbefore grouping and having to filterbefore grouping and having to filtergroups aftergroups aftergroups afteraggregation avoid unnecessary complexityaggregation avoid unnecessary complexityaggregation avoid unnecessary complexitywhile having is powerful avoid overwhile having is powerful avoid overwhile having is powerful avoid overusing it or making it too complexusing it or making it too complexusing it or making it too complexsometimes it might be more efficient tosometimes it might be more efficient tosometimes it might be more efficient touse sub queries or other methods touse sub queries or other methods touse sub queries or other methods toachieve the desired result and gna lookachieve the desired result and gna lookachieve the desired result and gna lookat sub queries In The next In The nextat sub queries In The next In The nextat sub queries In The next In The nextchapter now performance considerationchapter now performance considerationchapter now performance considerationWhen using having consider theWhen using having consider theWhen using having consider theperformance impact especially Whenperformance impact especially Whenperformance impact especially Whendealing with large data improper use ofdealing with large data improper use ofdealing with large data improper use ofhaving can lead to slower queryhaving can lead to slower queryhaving can lead to slower queryexecution timesexecution timesexecution timesexamples say that you want to find theexamples say that you want to find theexamples say that you want to find thedepartments with More Than 10 employeesdepartments with More Than 10 employeesdepartments with More Than 10 employeesyou type Selectyou type Selectyou type Selectdepartment count input Aster x fromdepartment count input Aster x fromdepartment count input Aster x fromemployees Group by department having weemployees Group by department having weemployees Group by department having weput having count greater than 10 andput having count greater than 10 andput having count greater than 10 andwill find All the departments with Morewill find All the departments with Morewill find All the departments with MoreThan 10 employees now Let's say that youThan 10 employees now Let's say that youThan 10 employees now Let's say that youwant to find the average salary ofwant to find the average salary ofwant to find the average salary ofdepartments With An average salarydepartments With An average salarydepartments With An average salaryabove 500 so Let's say that you in Orderabove 500 so Let's say that you in Orderabove 500 so Let's say that you in Orderto do that typeto do that typeto do that typeSelectSelectSelectdepartment use average salary fromdepartment use average salary fromdepartment use average salary fromemployees grou by department having andemployees grou by department having andemployees grou by department having anduse the average salary greater than000 and in summary the having clos is000 and in summary the having clos is000 and in summary the having clos isused to filter Group and aggregateused to filter Group and aggregateused to filter Group and aggregateresult based on certain conditions it isresult based on certain conditions it isresult based on certain conditions it isparticularly helpful When You want toparticularly helpful When You want toparticularly helpful When You want toapply condition to the groups of dataapply condition to the groups of dataapply condition to the groups of dataafter They been grouped and aggregateafter They been grouped and aggregateafter They been grouped and aggregateusing the Group by Close however it'susing the Group by Close however it'susing the Group by Close however it'simportant to use it jud and considerimportant to use it jud and considerimportant to use it jud and considerimpact on query performance now Let'simpact on query performance now Let'simpact on query performance now Let'sexplore the payment table to see how Weexplore the payment table to see how Weexplore the payment table to see how Wecan use the having command I'm gna typecan use the having command I'm gna typecan use the having command I'm gna typeSelect gna Select everything fromSelect gna Select everything fromSelect gna Select everything frompayment frompayment frompayment frompayment Let's Run this and you have thepayment Let's Run this and you have thepayment Let's Run this and you have thepayment id customer id staff id rental idpayment id customer id staff id rental idpayment id customer id staff id rental idamount and payment date now Let's sayamount and payment date now Let's sayamount and payment date now Let's saythat I want to seethat I want to seethat I want to seeamount per customer ID in Order to doamount per customer ID in Order to doamount per customer ID in Order to dothat I'm G ty here Select customerID puta function sum onID puta function sum onID puta function sum onamount fromamount fromamount frompayment Groupbuy Group buybuy Group buybuy Group buycustomer so this return the amount percustomer so this return the amount percustomer so this return the amount percustomer ID so if R this We Get 184 andcustomer ID so if R this We Get 184 andcustomer ID so if R this We Get 184 andit has 80 87 have 137 so now we are gettingit has 80 87 have 137 so now we are gettingit has 80 87 have 137 so now we are gettingthe amount so the total amount perthe amount so the total amount perthe amount so the total amount percustomer ID now Let's say that I want tocustomer ID now Let's say that I want tocustomer ID now Let's say that I want tofilter this Of course that I can putfilter this Of course that I can putfilter this Of course that I can puthere aw Close command I can can type itwhere aw Close command I can can type itwhere aw Close command I can can type itwand I can type Let's say customerand I can type Let's say customerand I can type Let's say customerund ID not in parentheses Let's put 184und ID not in parentheses Let's put 184und ID not in parentheses Let's put 184to see that It will filter that out and87 and Let's Run this and as you can see87 and Let's Run this and as you can see87 and Let's Run this and as you can seenow 184 and 87 are ski because you usenow 184 and 87 are ski because you usenow 184 and 87 are ski because you usethis Nothing but what if I want tothis Nothing but what if I want tothis Nothing but what if I want tofilter the based on the su amount I havefilter the based on the su amount I havefilter the based on the su amount I havethe column amount here and I want tothe column amount here and I want tothe column amount here and I want tofilter based on the amount column how ifilter based on the amount column how ifilter based on the amount column how ican do that I can type here Select Wherecan do that I can type here Select Wherecan do that I can type here Select Wherecustomer and typesumamount this seems Ok but if you Run thisamount this seems Ok but if you Run thisamount this seems Ok but if you Run thisIt will not work I explain I willIt will not work I explain I willIt will not work I explain I willexplain Why so we can't do this becauseexplain Why so we can't do this becauseexplain Why so we can't do this becauseThis is this aggregate this a functionThis is this aggregate this a functionThis is this aggregate this a functionwhich aggregates that column and thiswhich aggregates that column and thiswhich aggregates that column and thissome amountsome amountsome amountLet's keep this this some amount isLet's keep this this some amount isLet's keep this this some amount iscalled only when you Call the Group bycalled only when you Call the Group bycalled only when you Call the Group byso in Order to filter our Group resultsso in Order to filter our Group resultsso in Order to filter our Group resultswe need to add a having command afterwe need to add a having command afterwe need to add a having command afterthe Group bu and need will filter thethe Group bu and need will filter thethe Group bu and need will filter theaggregate results so If I go down hereaggregate results so If I go down hereaggregate results so If I go down hereand I typeand I typeand I typehaving and I will put here someamount Let's putamount Let's putamount Let's putgreater has to be a good expression sogreater has to be a good expression sogreater has to be a good expression soit has to be true orit has to be true orit has to be true orfalse greater than 100 now will work andfalse greater than 100 now will work andfalse greater than 100 now will work andwe have the su amount as have All thewe have the su amount as have All thewe have the su amount as have All theamounts here is the some amount isamounts here is the some amount isamounts here is the some amount isgreater than 100 because now it'sgreater than 100 because now it'sgreater than 100 because now it'sfiltering the aggregate results usingfiltering the aggregate results usingfiltering the aggregate results usingthis havingthis havingthis havingbut again You can'tbut again You can'tbut again You can'tuse amount in the because the amountuse amount in the because the amountuse amount in the because the amounthere so this part here is going to behere so this part here is going to behere so this part here is going to becalled When the Group bu is reach socalled When the Group bu is reach socalled When the Group bu is reach soWhen this command It will Call someWhen this command It will Call someWhen this command It will Call someamount and it aggregate the results alramount and it aggregate the results alramount and it aggregate the results alrnow Let's explore the customer tablenow Let's explore the customer tablenow Let's explore the customer tableSelect everything fromcustomer fromcustomer fromcustomer fromcustomer and Let's Run it and have thecustomer and Let's Run it and have thecustomer and Let's Run it and have thecustomer ID Store ID first name lastcustomer ID Store ID first name lastcustomer ID Store ID first name lastname email address ID active buname email address ID active buname email address ID active bulast last updatelast last updatelast last updateAC to total number of customers perAC to total number of customers perAC to total number of customers perStore how i can do that in Order to thatStore how i can do that in Order to thatStore how i can do that in Order to thatGo here type SelectGo here type SelectGo here type Selectfrom Select StoreID put count G count the customerID put count G count the customerID put count G count the customerID the customerID the customerID the customerID fromID fromID fromcustomer Groupcustomer Groupcustomer Groupby Store ID gna Group by the Store ID toby Store ID gna Group by the Store ID toby Store ID gna Group by the Store ID toget the total number of customer perget the total number of customer perget the total number of customer perStore and Let's Run it like this andStore and Let's Run it like this andStore and Let's Run it like this andthen G use the having and as can havethen G use the having and as can havethen G use the having and as can have326 and326 and326 and273 so we have the total number of273 so we have the total number of273 so we have the total number ofcustomers per Store Store number one hascustomers per Store Store number one hascustomers per Store Store number one has32 332 332 3326 customers and Store number Two has326 customers and Store number Two has326 customers and Store number Two has273 customers now If I want to filter273 customers now If I want to filter273 customers now If I want to filterthis aggregate result what i can do is Ithis aggregate result what i can do is Ithis aggregate result what i can do is Ican type herecan type herecan type herehaving and I will typehaving and I will typehaving and I will typecount customer ID because that column iscount customer ID because that column iscount customer ID because that column isthe count customer ID not only customerthe count customer ID not only customerthe count customer ID not only customerID if i put customer IDID if i put customer IDID if i put customer IDhere greater Let'shere greater Let'shere greater Let'sput 300 You Only have One greater thanput 300 You Only have One greater thanput 300 You Only have One greater than300 This one so now if you Run this as300 This one so now if you Run this as300 This one so now if you Run this asyou can see we have One andyou can see we have One andyou can see we have One and300 26 and you can also put here an300 26 and you can also put here an300 26 and you can also put here anAsterix because This is actually sayingAsterix because This is actually sayingAsterix because This is actually sayinghere How many rows are per Store ID sohere How many rows are per Store ID sohere How many rows are per Store ID soyou can put Asterixyou can put Asterixyou can put Asterixhere and Aster need tohere and Aster need tohere and Aster need towork sowork sowork sothis Get the resol but by using thethis Get the resol but by using thethis Get the resol but by using thecustomer ID here I can see specific thatcustomer ID here I can see specific thatcustomer ID here I can see specific thatI am i am aggregate us the customer columnI am i am aggregate us the customer columnI am i am aggregate us the customer columnput customer Readyback alri I'm gna end this video hereback alri I'm gna end this video hereback alri I'm gna end this video hereand see you In The nextand see you In The nextand see you In The nextvideo now to start a discussion aboutvideo now to start a discussion aboutvideo now to start a discussion aboutthe as statement in post grle in pog youthe as statement in post grle in pog youthe as statement in post grle in pog youcan think of the statement As A toolcan think of the statement As A toolcan think of the statement As A toolthat lets you give nicknames to tablthat lets you give nicknames to tablthat lets you give nicknames to tablcolumns or calculations within yourcolumns or calculations within yourcolumns or calculations within yourqueries Just Like how you might givequeries Just Like how you might givequeries Just Like how you might giveyour Friends nicknames you can give thisyour Friends nicknames you can give thisyour Friends nicknames you can give thiselement easier to remember names to makeelement easier to remember names to makeelement easier to remember names to makeyour queries less confusing you want toyour queries less confusing you want toyour queries less confusing you want touse the as statement When and we haveuse the as statement When and we haveuse the as statement When and we havefirst you want to give Friendly names tofirst you want to give Friendly names tofirst you want to give Friendly names tocolumns in your query results Two you'recolumns in your query results Two you'recolumns in your query results Two you'redealing with mat or complicated stuffdealing with mat or complicated stuffdealing with mat or complicated stuffand You want to simplify things by usingand You want to simplify things by usingand You want to simplify things by usinga simpler name three you're working witha simpler name three you're working witha simpler name three you're working withthe table and and copy kind of like a tnthe table and and copy kind of like a tnthe table and and copy kind of like a tnand you need to Tell them apart here isand you need to Tell them apart here isand you need to Tell them apart here ishow you do it H typehow you do it H typehow you do it H typeSelect original name as New name fromSelect original name as New name fromSelect original name as New name fromtabletabletablenamenamenamenext remember these four things if younext remember these four things if younext remember these four things if youwant to use spaces of special characterswant to use spaces of special characterswant to use spaces of special charactersin your nickname put the Nick in Doublein your nickname put the Nick in Doublein your nickname put the Nick in Doubleques choose nicknames that make Senseques choose nicknames that make Senseques choose nicknames that make Senseand are easy to Understand don't Go forand are easy to Understand don't Go forand are easy to Understand don't Go forreally Short or confusing nicknamesreally Short or confusing nicknamesreally Short or confusing nicknamesthree be consistent with your nicknamesthree be consistent with your nicknamesthree be consistent with your nicknamesto keep things organized four whileto keep things organized four whileto keep things organized four whilenicknames don't usually SL slow downnicknames don't usually SL slow downnicknames don't usually SL slow downyour query don't Go overboard With themyour query don't Go overboard With themyour query don't Go overboard With themin super complex queries andin super complex queries andin super complex queries andfinally so the as statement is likefinally so the as statement is likefinally so the as statement is likegiving your sql elements Friendly namesgiving your sql elements Friendly namesgiving your sql elements Friendly namesmaking your query less of a puzzle tosolve now Let's work on some examples tosolve now Let's work on some examples tosolve now Let's work on some examples tosee how i can use the as operator so I'm goingsee how i can use the as operator so I'm goingsee how i can use the as operator so I'm goingto type here Selectto type here Selectto type here SelectPay HowPay HowPay Howit payment and runit payment and runit payment and runthis and have the payment customer IDthis and have the payment customer IDthis and have the payment customer IDstuff id rental id and amount and that Istuff id rental id and amount and that Istuff id rental id and amount and that Ito see the total number of transactionsto see the total number of transactionsto see the total number of transactionsin Order to do that use the countin Order to do that use the countin Order to do that use the countfunction typefunction typefunction typecount the columnamountamountamountofas operator to see how We can renameas operator to see how We can renameas operator to see how We can renamethat to make it more Clear Whatthat to make it more Clear Whatthat to make it more Clear Whatty operator andty operator andty operator andtypenumbertransactions number of transactions madetransactions number of transactions madetransactions number of transactions madenow Let's Let's delete this so Let'snow Let's Let's delete this so Let'snow Let's Let's delete this so Let'sdelete thisdelete thisdelete thispart and Let's Select the customerID gna sum the amount to see how muchID gna sum the amount to see how muchID gna sum the amount to see how mucheach customerspendspendspendamount and grou buycustomerID this we see that each see the totalID this we see that each see the totalID this we see that each see the totalamount which which each customer spendamount which which each customer spendamount which which each customer spendthe customer 184 spend 80 but We don'tthe customer 184 spend 80 but We don'tthe customer 184 spend 80 but We don'tsee specific What This is because This issee specific What This is because This issee specific What This is because This issimply called su so Let's rename itsimply called su so Let's rename itsimply called su so Let's rename itand to use the as operator I'm as andand to use the as operator I'm as andand to use the as operator I'm as andtotal spend to more specific What thattotal spend to more specific What thattotal spend to more specific What thatis have total SP here speci is total SPis have total SP here speci is total SPis have total SP here speci is total SPtotal amount spent by each c here Nowtotal amount spent by each c here Nowtotal amount spent by each c here Nowcan use the having herehaving and typehaving and typehaving and typehere amount greater thanhere amount greater thanhere amount greater than100100100amer than100 greater than 100 and One important100 greater than 100 and One important100 greater than 100 and One importantthing to No is that as operator Getsthing to No is that as operator Getsthing to No is that as operator Getsexecuted at the end of a query meaningexecuted at the end of a query meaningexecuted at the end of a query meaningthat you cannot use the Alas the inside thethat you cannot use the Alas the inside thethat you cannot use the Alas the inside thewe statement so if this is calledwe statement so if this is calledwe statement so if this is calledtotal spent Ok so if Let's say that Itotal spent Ok so if Let's say that Itotal spent Ok so if Let's say that Iputhere al so let say that I put herehere al so let say that I put herehere al so let say that I put hereinstead of some amount I'm gonna putinstead of some amount I'm gonna putinstead of some amount I'm gonna puthere totalspent now if i Run this will Get anspent now if i Run this will Get anspent now if i Run this will Get anerror column total SP says does not existerror column total SP says does not existerror column total SP says does not existso This is because the as operator Getsso This is because the as operator Getsso This is because the as operator Getsexecuted at the end of the query meaningexecuted at the end of the query meaningexecuted at the end of the query meaningthat we cannot use it here in Order to tothat we cannot use it here in Order to tothat we cannot use it here in Order to touse the having here need to use suuse the having here need to use suuse the having here need to use suandandandamount so now if Run this everythingamount so now if Run this everythingamount so now if Run this everythingworks All Right I'm this video here andworks All Right I'm this video here andworks All Right I'm this video here andsee you In The nextsee you In The nextsee you In The nextvideo\n"