query by design so far one should be very comfortable with that using the design view and also we learnt how to make SQL commands using access using the SQL screen and we learned how to make SQL statements using sequel server and we learned that about the presidents of the operations in any SQL statement and today and what we have done so employee I’ve learned how to come back or make SQL commands physically one thing to really prove that only one here so today we will learn how to retrieve data from more than one table using hplc cell let’s see so when we say that SQL statements can be retrieved from one table it doesn’t mean only from a table object it can be retrieved from other phrase at will so basically we can run a query based on another query so to do that let’s open the access file please all of you that’s open up access file and we will be writing SQL commands just to show or to the practice how to retrieve an SQL command or an LA data from another school for us from another query so let’s open the pit store the fish store database access as well as the web query so we wanted could be handy ever so using the access file so we will write at least you so at least you can see that the dismal screens because you already have this file so you should be able to do that so we will select and then all which is the asterisk from there is a query here that is called qry net so select all from qry net so let’s do that and it should be available to you in the slides here including it even note in the spies on Chapter seven so if you select all some query net to run you will see that point now we’re able you were successfully able to achieve better or under quick using SQL so if I ask you a question whether SQL commands are based on the on tables would that be true it was false or two sports if you can be also contort that via table and as we learn previously today we will also review some of the command or a feel that we have done before so this is kind of the same text or the basic syntax for SQL commands so you select use the man keyword with a select and then you name the columns that we want to select and then you use another keyword called on to specify the table or the object that you get it from which is the source of your data and then you use where to specify an endpoint what exactly you want to retreat and we have learned that but keep it order Luster’s so if you want to know the lines so we look at the basic syntax this is the long one of the long syntax that you can

use for simple commands but this is that this doesn’t confirm head orderly include all the keywords that you might be here is it so this is just to give you an idea that there aren’t some key words that can be used such as this thing and we will learn how to do that so distinct if we have in one table for example the same record repeated let’s say that when we will try to retrieve carry goods from animal table we have cats dogs frogs maybe whatever other animals we have but you will see the therapy but if we really want to know those categories and the distinct relatives how many distinct help we wanted then we can use distinct as a keyword adverse effect and we will not have to borrow top is another keyword that you after some life let’s say that you want to select the thought record 10 records for that table from whatever shows that you want the top 10 records listed for the top 10% so when you say ten percent you would have to fill out the percent don’t use that purse if excite so you can do that and really that at some point we want only to some let those ten percent most profitable customers box or those ten percent free admissions that we get in our office so you might get that person from a manager so tau is very useful keyword that we used by artists unlike the select keyword into this is if you want to create a copy using a query into another table we use insert into and we have used that before joining so today since you will be learning how to retrieve data from more than one source for more than one let’s say table more than one query at the same time then we would use this keyword called join so we’ll say some left all from animal inner join thats a sales on and then we specify the third key that is common between those two tables and then we will specify where state where by class and we will learn how to do that we would be also with reviewing the order by so we did some query or design view queries we order by a number order ascending ascending today we will include the holder bind as a keyword at the end of the SQL statement just to make it organized with me for us the group x is mainly used where we are using functions so can remind us what are those functions examples that we have been doing average some minimum maximum count those are kind of examples of functions so when we use those functions then we will do by another keyword that comes right after is added so if you are using aggregate functions and you are using goodbye then you use the having to specify what condition you want so let’s say select average list price for animal where or from animal goodbye SI category having this price greater than 53 and we’ll make some examples as well a little bit but now let’s look at those in general so so starting result we have learned about using the equal sign and logic mathematical operators we learnt about logical errors and we learn about comparison so we have done that I’ve used it so I’m not going to fit in here so managing result sets so as we

mentioned that so far we have been creating some if you magnify other people how about if we want to select specific column from a table then we will have to spit on that exact car so let’s create an STL man or an SQL statement where we want to treat the animal ID and the list price and we want the list we want to increase the list price by fifty percent so we want to increase the list price but fifty percent and we want to put that in the power that way you show the old eyes best price here these days and we do that and we all create the SQL command to do that so let’s let’s do that using either one either way so we want to select the animal ID and then the list price and the new list price increase by per se so what I will do can you see can you see off I already have that the statement for you there but I wanted practice because I just want to put all the statements that I can include just for you that way you can have to remove but let’s practice by entering this by yourself so let’s say we want to select as we mentioned select animal ID this price and also we want to select what we call it this price times 1.5 so in order to increase the time five fifty percent we will multiply it by 1.5 it that way if any x 85 / staff 5.54 it wouldn’t that mean I’m time anything happens to so this is a important thing and then I would give it a name so to give it a name would say as and then increase price or one word and then from animal then try to write see what we get so now do we all see what we got you all get that one what so now you see how we can get a new home for that reason we’re wantin so we recreated a new home because we want a piece of pie five fifty percent so what do you want to put down any Oh so now we saw that it is very helpful to be able to create a new column and so that was one way but did we specify the rose do we want to select how many rows did we how many records did we have one 191 what if your manager if you from you I just want to see top 10 step up so let’s put the earth good the 32 to select only or to show only the top 10 as we mentioned we would use that top keyword so we’ll put top 10 right after that I say and then run it now do you see we have 10 only 10 rows what if you what if you manage your wants you to

select the top 10% remember okay we hired what I want really into congressional black here on 91 for you so let’s put the ten percent so the percent we need to fill it up so what if we want to create whatever we have now and here they go increase prices and we want to create a table based on what we just created based on this trail so now let’s create a table and we wanted to name this table TBL increase price can we do that so we will just use the in to find out for us oh and SQL / and ask you I mean in in access because SQL they say immediate that conscience where we can create table particularly this table or this file is safely their servers and a package can easily get into the system if they allowed that creation of the table so let’s use access and we will let’s practice from scratch rather than copy and pasting so so lightly and Mel ID this price and then this price times 1.5 as please rise into TBL that’s the name on the table that we want to create increase price from animal so let’s see what to throw up to do so now it is asking us that we are going to paste this serves yes so now you will see that we have our table it is created so that’s one another way to create SQL now how about the distinct viewer but we talked about so far we have talked about top to specify top and now let’s use the distinct keyword now we want to know what am i doing having our database so to know that order to do that we want to select category first just to show without this taste first so let’s select category from animal or I have 191 but DC now and have a lot of bird repeating but if I want to know only what those single carry good i will use the distinct keyword so select is 10 category from animal now how many and most the way you have so it is easily that I can answer those questions if i matter so everyone did it work for all of them all of you so how about sorting and order so we want to short and over by other descending or ascending so now we can actually order every column that we have by listing them attack so let’s look at the example that we just created here

for our for our list price category an example that we just use so now we are ordering or we are ordering by category and then this price so category means an expert and it is ordered by default in ascending so the default order by is ascending so from low to high or from A to Z so this so I I got it from here from this orphans file so this is what I apps on that category list price from animal order by category list price so this one lists ascending both columns what if I want one to be in the same day let’s say i want the list price to be sending i want to put the high price first and for every category can I do that also so i will put right after price list output disk des see so and then i will run now you will see that the bird is on ah the highest value to the lowest so that’s that’s helpful so now we know how to use ascending and descending only 21 seriously dislike on the x-axis yes if we wanted to apply to both that’s a very good question if you want it to apply to both of them then we will put this in front each one of them so this so let’s put de SC in front of both columns and run now you will see we have the spiders first so we are starting from the Isle of the highest say alphabetic order to the lowest so that’s a real good question ok yeah so everyone died he had from Colombia vs with waiting for everyone that where the pepper tomato was just busy being a problem but this units kidneys a database pacify digital what people so we have learned how to use order bind for sorting how about Google so remember when we say gumaan if it mainly food or aggregates or actions so functions but we did examples like some pound max minimum average and so on here is that education so now let’s say that we want to list all the categories and we want for every category won’t enlist the maximum price for that category and the minimum price for that

head remember this plus that one’s called costs table calculation let Phil its distal end we will give one arm for three or four categories then one for maximum best buys another problem in their spice and we want them to be good by category so when we say grouped by category means the Rose will be based on happy so let’s do that so I would say some length category and then max this price as Max and then min minimum this cars as minimum as good on annual and then what P word one year now goodbye so goodbye Carrie book starts revival now let’s find this guy so now you see we have we know now which one has maximum or what is the maximum and minimum price what if we want to go the maximum price which has the highest maximum I found all of those from all such a debate it’s not easily we can easily spot it for the family over it is it is already there for this family it is asked a organized that means the bird maximum price seems to be the highest to survive and what is the lowest price for which category can you see the fish so 3 guess what so now in order to remove not to be getting an error let’s use a keyword that way we can avoid the air so for that what we can do here but can we do we say to be very specific and we say order by order by let’s see if this will help us what do we want ascending or descending descending so now you’re right so we got that the cat now has the maximum so now we understand why it is very important that we use those keywords that way we don’t make any mistakes so what if I want to submit all of those categories is their maximum and minimum fine I have a condition I want to select only those categories and their maximum and minimum where the minimum is for dinner ecology greater or equal to 50 so what the minimum price is greater or equal to’ system can use Ethan out you’re able because we’re me not so in this case since they have goodbye i will use having to specify what i want so i put here having before order by having then this price greater or equal to 15 it’s now you see stage that six can

degrade before now i have only for now we we are comfortable with using group by and adding those keywords are we all comfortable with that and order by and we talked about the operator precedence before and who can remind us which they’re in the same level which operators will be executed first or confirm the president’s only be the from right to left or left right pair i mighta left to right that’s what we say exactly so from left to right and what other we call it mnemonics that we have been using for magnetic resonance please let’s please excuse my dear Aunt Sally you’ll remember that I think that’s how I she said she even heard it before but so it is for me I never heard about it before so maybe talk two thousand seven hahaha definitely that but I know most of the students here Verity the commentary or ill for monthly but but now we know the presence so from left to right and the president’s also is dependent on the catwalk database management system they have different presidents depending on their system how about combining to select statements can we do that yes we can use we can be dust we can use alien number believe the wind right one to remember the pin diagram but we all remember the pin diagram for this once what’s that in seventh grade I remember my seventh grade that would refer us that this point my cousin used to teach me and they hated that class principles my cousin took my parents hold on Howard oh I’ve a little not good anyways let’s look at that how we can combine to select statements using Union intersect and whatever except for those are that’s the key words that would be so if we are using the Venn diagram just to refresh I think it’s what Derek was drawing so this kind of the vin diagram a be so if we say again it will be everything is alright everything in both if we say intersect it is going to be only those guys that are enfamil that intersect you both going to be this kind only those values that are common for a and B what if we say except so if we say a except be so it’s going to be I can tell this would be a little bit tricky yeah so a except v what will be the batteries in which area one two three so I will call this 30 one area to or using so if I say a except me so it is going to be number one because they want only those values that are na but not in me remember we would too is part of me too so we don’t want it so a except they would be able t really so now are we clear of us so maybe we haven’t think in your royal college degree maybe never touched on this and that’s why I don’t come to Alaska why are we having to take this

inside that are high Pacific please a battle but in some cases you might leave this College your work to remember them so now let’s refresh our memories so now let’s use select statement to select all from the city table so we have a city table that has advocated as States it goes and so on we want to select all compazine a table where the state is excess and we want to interstate that for two mini intact which survived all on clinic where the state is excess power now can we do that so let’s use through select statement so I will select all from City which is a table so we’re say like four equal to not be a problem and then one single quotation Texas and then you can either go to one line or you just keep it in the same line should not be a problem and then type Union and then some back all from city where stay by Kansas so now we’re on this guy so did you see it work and you showed us everything that is from Kansas and fix us today work for the one I grow worse because it off your face sometimes the simple probation and different formats so tightly hardware innovation and see good work yeah I went here fine we got it Sam so now what if we do accept so select all from sitting where statist excess except answers so now we got yeah we go on it fixes to your hands anymore and then intersect would be the same if we do intersect I believe there would be some bad listen so except we just so the ex cpt maybe the spelling the scissors asset tactical and ones we want on the same context so let’s oh you so you are using a access or SQL access so let’s see for access so when we just this keyword is not compatible and access but at least in the word principal server is working so we’ll just stick to that one so now we have learned how to use Union intersect

and except now we can also insert mean values into an existing table and this one here just helped us insert a new select stay in a row in the table so will not do that because as I mentioned most of that of the select statements that would be using our poetry we are not create per se it wouldn’t be written clearly on data manipulation operations that are specific to retrieval on it so I will skip this one but if you want this would work the first state in Access so now let’s look at joints so this is what we will need to concentrate on in this chapter this chapter is all about joining tables so so far we have been retrieving data from only one table which is animal or sales or city only one table now we want to retrieve data from more than one table so as long as they are related through foreign keys would be able to treat that data and that’s why we have those foreign keys and we need to specify those foreign keys and there are some queries that are sub queries as well so we will have two queries with it each other so we have nested query one query that has another query within it and we’ll see an example building so joins so we don’t want select let’s look at this example here if we want to list the name of customers who bought animals between 41 and 531 so we want to select animals go to show the names of those customers who bought animals in that surfing day so for that what are we going to do the first thing we will first decide I want to see which tables are relevant to this steak so they want to list the names of customers who bought something between April and May so which tables do you think we should look up to list the first and last name of customers up to Bosh assaulting during between April and May up to thousands work so what from looking at this one which tables do you think that we should look at so which you all look at it and let me know so take a minute do you think sandy boy quanta jumping yes so customer merchandize will be do we do we have date here that we can no wait so into a big order will be one first does the word functionalized oh you mean this one and this one that would be too long of state that would be like Connecticut ages if we do that but that’s a good a good point because we want to see where we have closer to that table please to work two tables within closer to that to this so customer that end so great the customer table definitely will be there but another another table we need to see where they is mentioned in someone programming sale perfect so now we have customer and we have saved those are the two tables that we want to retrieve our

data and it is it is already there in the PowerPoint slides but let’s look at how we can retrieve this using its jaw joint state so to retrieve that data so we said we want to select first name or let’s say a last name first name prom what table are we on retrieve the first name last name from customer and then we would do that Elias a little in a little bit so from customer now we can use inner joint so inner one word by itself and then join by itself inertia win what is the next table that we need to deal with that is connected to customer sale energy wind sail on now another keyword that we have used on now we need to match those foreign keys in the foreign key with the primary key of those two tables so what was the matching foreign key and and finally for those two tables are smart so for this one and we’ll say on now we would need to name to put the table name sir and then the ID the common ID so the table name in the stairs can discuss tomorrow she’ll remember which one instructed to only say customer dot and then the ideal the common rd that in between those two tables equal the name of the other table by the finder’s fee or foreign key that is making them see table and then we think of one so let’s say so we say it select last name first name from the first table was customer inner join sale on so customer is the name of the table lit dot that was the common key between both start customer ID and then equal so what is the name of the other table sale and the common key is customer ID so now we had this done again to remind us what was the main question there to let the name of those customers who bought something going or between april and may tell what it was so now we can use where the where stage so where what was the name of that field sale date now you can use that where and sale date in which table with it is here so we need to specify the table dot then the keyword the name of the field so we can say sale dot sale bait it will work even if you didn’t mention it but it’s always good to be safe and will mention the name of the table first and then talk so sale dot sale date between Zelda will use between we go to so a prime was for isn’t it 41 2004 and 571 consultants for did that work cgc now we have the first name last name of everybody who bought something between those two days did it work for everyone so now that we know how to do that inner join thing so there is another thing that is called Elias so that means we can use an abbreviation for the names of the tables so how to do that for example here when we use when we define customer we need to give it kind of a letter that

will kind of be abbreviation for it so in this case what do you think would be one letter that represent Testament commute see so i’ll put C right after customer and what about the table sale s s will be to output s here now I will replace i would have to replace the customer and sale names of tables with their illnesses so say oh just use s where sale and then if i run it it should work so it’s still work so do do you have any question about this the use of aliases so sound in a person everyone grace three word Lissa so this Oh whoo guards wonderful sure ok good so now I mean a little painful even fine we’re using inner joins so what if we want to use three or more tables what if you want even feel it working within you the same in you and this is an example here so list the last name and phone number of anyone who bought a registered white cat between those two dates so these are very long question isn’t it so list the last name and phone number of every customer who bought something football what white cats so that means the animal table will be here is that right white cat visiting the animal and color we want it to be why that means from animal so we the data that we will need to retrieve will be from customer and they say also they want the phone number what about the phone number where are we going to get it from so what do you think the phone numbers will be from customer here yeah the first name and phone number from customer and the date what are we going to give the date on which table so do you think go ahead tale yes exactly so we will get it from sales that right because that’s the closest that has date in it so we are customer and we have sale what else

do we need we st. color and catagory with or four cans will be from animal so how do you need to do we have connection from customer to say up we do but do we have any connection between customer or sale and animal how are they connected the different ways how are they connected yeah how are they connected sale and customer how are they connected to nville Andy how easy how are they connected so you have customer per meter per customer sir we have to come how are they going to be connected we want them to be connected I don’t know how are you for each animal because there is no direct link between animal and those two tables is that right so sale animal has an Associated table or in yes so that means we will be using for daily so we’ll be using customer and then inner join customer to sail and inner join a sale to time it sale and book and inner joining they live in ultra light so that’s how its get over ok so now do we know which tables at least that we are looking at so we can do so now do you want to start writing this this the other payment so we will use the same statement that we had before so select last name first name from and better also any phone number phone from customer inner joint sale on customer I need for this customer ID in both sales and animal and that customer and then we’ll delete the where for now now we would need to have another energy in our joint so remember how which table that we are going to connect now sale to animal fair that right so energy win animal sale on so on what they are going to be joining joined on one so we can tell me how they are going to beat nerds on the sale animal and Cl yes so the sale idea is a common yes so let’s do that on did we have we need to have an an alias here for animal sale what do you can do it x0x so on x dot sale ID equals s dot l ID and now we need to to connect wish and unknown sale paper to the animal table is that right now we need to make those two so now we will use another inner children inner joint animal on so how are they going to be joined other so how do you how do you think that they are going to be showing those two tables with animal ID is it exists in both so now so which which one Audrey so how they are going to be connected with animal like because it is common between those two so in a joint animal on what

do we want to put an alias for animal a a will be representative inner joint animal a on animal ideas that we will say a dot and multi d equals x dot animal highly now what was our condition they were the collective name and phone number of the customer robots on whether they bond white cat is that way there’s just a white cat so those are some of the conditions that we need to use aware keyword so where the resistor is knowledge our registered is not not just heard is that should we use the A’s or not so registered not know and catagory bike can’t and Tyler by white sorry it’s a long one isn’t it did we miss any of those conditions that we meet all of them you think and there’s a date is in it so the date and sale date between what was that question between those you guys so now it’s fun and see to work so it didn’t work we need to specify the tables so cell they came from ss-sorry Escott sale date what about color did it come from animal eight dot category thing from eight dot this is hurt hey dot recover and so we have to use is so that is keyword should be used when we are specifying is not wrong so now it seems to me I add some spelling just to avoid finding on we cannot those spelling mistakes I’ll just pop it in place what I just did this morning and run it and still not working on so so will i will send you the one that will work in an email yes it seems to me that there is a mistake oh there is a spelling mistake or error but at least so far we have learnt how to connect those tables using in our joints and for one or two tables for three or more tables so we’ll use that one now let’s say that we have a nested query so we have one query that is within other grill so for example in this example here wait says find the name of the employee who made sale number ten so now

let’s look at this one we want to know the name the last name of them to lead who made sale number ten so which tables are we going to use that’s the first step so Audrey what do you think which they will should be used so tell it in your life nice name the employee find the name of the employee Oh mainsail number thing so it is employee and sale yes that makes a lot of sense isn’t it so now to do that we can create a nested query so that means we will select this same last name first name from employee now we’re employee ID equals now we’ll open another nested query so what I did so finally somebody the first name last name from employee but we said we want to select this employee who made sail number number 10 ok so now let’s create a statement that will select for us the employee domain cell number thing can we do that also I’ll go select statement so let’s say select employee ID on you space which table sale where cell I deep it goes again so now if you notice that the innermost the one the Select statement that is it inside this really will be executed first so this will be in first with us the output that would be you Spartacus ok so now if we run it did it work and now we know that Travis Wood the one who did it there is another way also we can do this by using inner going to there are several ways that you can do some of those waivers but now we take the energy so there are some are content in the back that deals with programming and coding like programming languages at the end of this chapter we will not cover that that’s to advance and it’s not the spell in the scope of this portion so let’s look at another things that I i added here is an alternative to this so this is the statement that did not work for us let’s look at the alternative that I put for for you in the milk here let’s see if that would toward so now what we did here we did an alternative way of running queries that retreat that on one of that three tables so what I did here so we’ll go step by step or to analyze the statement of that we use for this one so what I did here is we selected we selected the cart that columns that we need so that’s the first step that we did what we did select a customer last name and first name and phone number because means the content that we need to retreat so that’s what we did now the second thing that for this alternative way of find a scarecrow mask is to list the name of the tables that are involved to believe in that now you see now we have the customer carry both sale they go sell animal an animal so we listed

them one after one with a comma between them and then we use the where state we use the where keyword where we join those tables by using the foreign keys so we make sure that sale is connected with customer to customer ID and and then we use the end statement keeping up each one of those in table with there for me until we get into our condition here we say you can’t have dy that’s all so is it does it make sense now how we can

You Want To Have Your Favorite Car?

We have a big list of modern & classic cars in both used and new categories.