Joins and Statements

Question 1

inner join

Answer 1

pulls together data with matching entry from two tables; excludes data without matching entry

Question 2

left join

Answer 2

shows full left table with records that match from the right table; excludes non-matching records from right table

Question 3

right join

Answer 3

shows full right table with records that match from the left table; excludes non-matching records from left table

Question 4

full outer join

Answer 4

shows both tables’ matched as well as unmatched entries

Question 5

select

Answer 5

extracts data;
select (* or column)
where “a condition is present”

Question 6

from

Answer 6

a place, usually a table name

Question 7

where

Answer 7

used to extract only records that fulfill specific criteria;
SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

Question 8

update

Answer 8

updates data;
update “tablename”
set “columntochange” = “desired value”
where “a condition is present”

Question 9

insert into

Answer 9

inserts new data;
insert into “table” -then blank if entries are in order, if not then (“column1”,”column3”),etc)
values (‘varchar’,int, etc)

Question 10

delete

Answer 10

deletes data;
delete from (table or db)
where "condition"

Question 11

select distinct

Answer 11

returns only distinct values;

select distinct followed by normal query

Question 12

order by

Answer 12

sorts the result of a query;
select
from
order by “column_name” asc or desc

Question 13

like

Answer 13

used with wildcards to search for specified patterns in a column

Question 14

*

Answer 14

brings everything from the selection

Question 15

%

Answer 15

substitutes for zero or more characters eg: to search for cities that start with “S”
SELECT * FROM Customers
WHERE City LIKE ‘s%’

ending with the letter “S”:

SELECT * FROM Customers
WHERE City LIKE ‘%s’

where the customers country contains “land”:

SELECT * FROM Customers
WHERE Country LIKE ‘%land%’

and where the country doesn’t contain “land”:

SELECT * FROM Customers
WHERE Country NOT LIKE ‘%land%’;

Question 16

_

Answer 16

substitutes for 1 character

Question 17

[]

Answer 17

sets and ranges of characters to match eg: this statement selects all customers with a city starting with b, s, or p
SELECT * FROM Customers
WHERE City LIKE ‘[bsp]%’

or

SELECT * FROM Customers
WHERE City LIKE ‘[a-c]%’

selects all customers with a City NOT starting with “b”, “s”, or “p”

SELECT * FROM Customers
WHERE City LIKE ‘[!bsp]%’

or

SELECT * FROM Customers
WHERE City NOT LIKE ‘[bsp]%’

Question 18

!

Answer 18

“does not equal” or “not”; used with wildcards

Question 19

truncate table

Answer 19

deletes a table inside a database eg:

TRUNCATE TABLE table_name