query

Oracle SQL Developer: Query Builder Demo



Sharing buttons:

well everyone I wanted to take a few

seconds to give a quick overview of a

feature that many users don't use or

think they don't have any use for it or

might not even be aware of and that is

our query builder which is this guy

right here and you can think of the

query builder if you go back to the days

of when your idea of a database was

maybe Microsoft Access when you go to

create a query you would drag tables

together and use lines to create

relationships and that would build a

select statement for you

sequel developer has the same sort of

interface if you're not super familiar

with are comfortable writing your own

sequel statements which might look a

little something like this

you know so that that one's pretty

simple but if you want to do something a

little bit more complicated like

so if I wanted to add a where clause

here to join those if you know what the

referential integrity is it's pretty

simple if you don't know it can be a

little bit of a challenge so before I

jump right into the query builder I want

to show you a trick here I can actually

take my sales and customers table and

drag them into a worksheet and say give

me a select with the join and we get

this so this assumes that you have

referential integrity defined in the

data dictionary with a foreign key

constraint now mind you this isn't the

most fun thing to read and I don't need

all these columns either this is where

the query builder can come in and make

this a lot easier to deal with so I'm

going to toggle over to query builder

and now I can tell graphically sequel

developer what columns I want to see in

the query so from the sales table I can

say don't show me anything except for

amount sold and quantity sold and then

on the customer side I don't want to see

anything but their name and if we toggle

back to worksheet the query is much

simpler now and I can run this actually

I can run this from the worksheet toggle

so whatever's in the worksheet will get

represented in the query builder and any

change I make into the query builder

gets sent back to the worksheet

so there are some of my data now if I

want to apply column aliases or where

clauses or anything of that nature come

back to the query builder and down below

you have the ability here to add

aggregates or sorts so maybe we want to

do a descending sort on amounts old but

an ascending sort on last name so you

know if we had three customers that each

were buying $10,000 worth of materials

customer Browne would show up before

customers Smith now I want also want to

add a criteria to amount sold some want

to say amount sold is greater than

$2,000 and I could also give these

tables aliases you can right click on

the table to the properties so by

default we do alias the tables so you

can see that here it says B and here it

says a and you have that happening via

that drag and drop because we don't know

if there's going to be any columns

overloaded so that you know if there's

two columns of the same name the

optimizer would get confused so now I

could run this query again and we come

back and it looks like no one has bought

more than $2,000

but we can make adjustments to the query

as needed so the way I normally sell

this feature to a user is let's say

you're not going to join two tables

maybe you're going to join you know 15

tables writing those where clauses for

15 joins would get kind of not fun and

the easiest way to demo that is just to

actually have the tool build up for us

what I'm not going to do 15 I think

maybe this is enough to give you an idea

so again I'm going to say like that a

join so typing this would not be fun but

again I can come over here to the query

builder and explicitly say what columns

I want to see so I just want to see a

unit price I just want to see any

promotion name that was applied for the

sale I just want to see the product name

maybe I want to see a channel ID

customer name

what country they're in

and again the sales info so mountian

quantity sold so i think there are some

of you out there that could type this

faster than i just click that but I

don't think there are very many of you

so this is the I think the appeal of the

query builder it can be a huge time

saver and that's really what tools are

for if it's not saving you time don't

use it the other use case I think for

this is if you're brand new to Oracle if

you're brand new to databases in general

and you can't think in terms of sequel

but you can't think in terms of tables

and columns and relationships and joins

then this can give you a nice kind of

crotch or jumpstart now I'll be honest

too I don't use the query builder much

more than what I've just shown you when

I when I get to this point I'll

generally come down here and add my own

group buys my own having clauses my own

sub queries I just like to use the query

builder to give me the skeleton of the

query to kind of do the the non fun

stuff for me and that's the query

builder thanks for your time