query

Best Way to Write Basic SQL Queries



Sharing buttons:

[Music]

when we want information from a database

we write a query the syntax is select

field list from table name here we are

using the DB basics database context and

we're requesting all records in all

fields from the shopping list table in

our result set we see seven rows and

three columns the keyword select

requests information to be displayed and

the asterisk sign is a shortcut for

saying we want all field names the

keyword from chooses a specific table we

are able to run this query to retrieve

the information either by clicking the

red execute button in the toolbar above

or by pressing the f5 button on the

keyboard

to start make sure you've run your setup

scripts and have opened a new query

window

want to run a query against the DB

basics database one way to do that is to

click this drop down arrow and change it

to DB basics now sometimes you'll notice

that this drop-down arrow is disabled

that happens if you clicked on the left

side of the screen if you're clicked on

the right side of the screen it will be

enabled just fine

now we want to run a query where we're

going to select all fields from the

shopping list table and we see we get

seven rows and three columns we could

say we have seven records and three

fields now if we weren't in the DV

basics database and we were in the

master database this query would not

work so we need to switch context to the

proper database before we can run a

query now oftentimes your query window

starts in the master database and you

don't want to have to manually select

this drop-down if that's the case it's

very common for the first statement to

be a used statement which switches you

to the correct database context I'll

type use DB basics and go as I highlight

this code and hit execute look just to

the left of the execute button and watch

it will flip the database context now

it's DB basics now querying from the

shopping this table works even if you

start out in the wrong database context

now table names can optionally have

square brackets around them if we run

this it's not going to change the result

although one query uses square brackets

and the other one does not they get the

exact same result set because it's the

same table when coding we rarely take

the time to put square brackets around

because it's more work one of the times

this is needed is when we can't tell

where a table name starts and where it

stops for this example let's expand

databases let's look in DB basics and

under tables let's find there's a

shopping list table that we query from

and there's also a shopping list table

with a space in the name now to sequel

server this looks like you're querying

from the shopping table and there's an

extra list option on the end which it

doesn't understand there for this query

air is out but if you have a table with

a space in the name and you want to

delimit it so you know this is one

object with a space in the middle then

you would be required to use the square

brackets now we can get the information

out of the shopping space list table

when coding we rarely use square

brackets because it requires extra

typing the only time it's helpful is

when we can't tell if a table name

starts or stufs or at least that's one

of the ways this shopping list table was

not accessible by using its name without

the square brackets using the square

brackets made the data in this table

accessible and we were able to get the

result set let's query from a different

table of DB basics let's query from the

employee table and let's get rid of our

used statement at the top so this is our

only statement and notice that Sarah

Smith is our first employee and the last

one is Rick Morlan we have a total of 16

employees now without

the code this query is going to run

differently because the DB basics

database has an employee table but so

does a pro co run this in the J proko

context and notice we only have 14

employees Alex Adams is the first

employee and Jenna Smith is the last

employee employee is actually a great

name for a table that holds all of your

employees a bad name for a table would

be this select all fields from from

don't name your table from select star

from from another bad name would be

select star from select yeah even if

you're Campbell Soup and you're trying

to track your select soups don't name

your table select another not-so-good

name would be if you're tracking charity

grants and you called it grant notice

with these last three queries they all

turn blue for the table name the reason

they turn blue is these are sequel

keywords employee is not a keyword so it

made a wonderful name for a table but

here's a little secret the grant table

actually exists in J proko and we can't

get data out of it let's take a look at

J proko tables and notice the grant

table sits right here well if we want to

specify that this is the grant object in

other words the table and not the grant

keyword we can put square brackets

around it and now we can get the data

out

so you see a list of very

bad names for tables because all of

these names are keywords in sequel

server but is that necessarily because

somebody was just being silly are not

very farsighted nah maybe the database

is very old and you know the vocabulary

of sequel server has grown over the

years each version of sequel server adds

new keywords who knows maybe someday in

the future employee will wind up being a

keyword

grant has already been added as a

keyword a long time ago and we already

have a grant table so the way we got the

records out of the grant table was to

put square brackets around it that's our

way of telling sequel this is an object

not a keyword

please query from the grant table do not

try to use the grant keyword rather than

deleting this code and starting over

let's just open a new query window that

way we can go back to this one anytime

we want this query window will be set to

the DB basics database context and let's

start off by looking at all the records

again from the shopping lists table we

want to find all of the items that are

exactly a dollar 25 it's a small enough

table where we can just look at

ourselves and see that two of the seven

records are exactly a dollar 25 but what

if there was two records to find out of

a million that might take a little more

work from us let's let's sequel server

do it we will put criteria on this query

by typing where price is equal to 1.25

and run it and there's the two records

we're looking for great let's go back to

our first query window the one that has

the employee table and let's look at all

14 employees how many of these employees

have the first name of David

let's put where first-name equals now

this is not a number this is a string or

a set of characters so we're gonna

surround it in single quotes and type

d-a-v-i-d how we run that and we see we

have two David's okay if we have 14

employees and two her name David how

many are not named David that'll

probably be twelve and two do not equal

to we just put an explanation point in

front of the equal sign and we're

finding everybody who is not named David

well let's go back to finding everybody

who is named David and we're looking for

all the David's or all the leases so

we'll type or first-name equals Lisa and

it looks like we have one Lisa and to

David's now we could continue this we

can type or first-name equals Eric and

we see we have a larger list now what if

we wanted to list exact matches without

having to type or first-name all over

again for each match in other words we

want it to look like this we're going to

list every exact match in its own set of

quotes inside of parentheses this won't

work because the equal sign does not

look for multiple exact matches it only

allows one exact match but if we change

this operator tin it'll find Lisa or

David or Eric since we rarely need to

see all the records in a table often we

use the where Clause to limit the result

set to just the records were interested

in seeing we simply don't want more

records to sift through than is

necessary and we just want to see what

really matters to us similarly it's also

possible to see too many fields in your

result set when this happens we can

limit the number of columns returned in

our result set by affecting the Select

list here in the Jay pro code database

let's change to looking at our employee

table we're getting a few fields and

quite a few records we still want all 14

employees but we only want the first

name and last name fields so let's put

first name comma last name right after

the select we get our 14 records but

only two fields by listing first name

and last names separated by a comma

we're just getting an explicit field

select list the ones we asked for doing

star gets all the fields from that table

optionally we can say this first name

field is from the employee table by

doing employee dot first-name

and an employee dot last name we get the

same result we're being more explicit

this requires extra typing but is

sometimes needed when we have two tables

or more in a query and two of those

tables have a field with the exact same

name and you don't know which one you

mean for example we might have a

contractor table that has last name and

an employee table that has last name and

we need to differentiate which one we're

looking for

so here's the anatomy of a sequel query

now above the query we can use a used

statement just to set the right database

context but let's look at the query

itself after the keyword select you're

choosing the fields or columns you want

after the from you're choosing the table

or tables you want and the optional

where Clause is for criteria to filter

for just the records you're looking for

time for lab 3 skill check 1 take a

moment to pause this video and challenge

yourself to do this skill check skill

check two take a moment to pause this

video and challenge yourself to do this

skill check skill check 3 take a moment

to pause this video and challenge

yourself to do this skill check skill

check four take a moment to pause this

video and challenge yourself to do this

skill check

[Music]