w2d3

Question 1

What do queries do

Answer 1

get output in the way you need it

Question 2

subqueries

Answer 2

return a table (special ones can return a single constant)
can act as tables in a from
can be combined into a single table
can be used with special modifiers
ARE MOST USEFUL WHEN DEALING WITH A SINGLE TABLE

Question 3

In clause

Answer 3

used instead of where when you have a relation

because subqueries return tables as a default and you can not use an equality between an attribute and a relation

Question 4

ANY/SOME

Answer 4

• works with subqueries that give a single column

WHERE xxx condition ANY(Is true if xxx meets the condition for one or more results of a query)

Question 5

All

Answer 5

like ANY/some but true if xxx meets the condition for all results of the subquery

Question 6

EXISTS/NOT EXISTS

Answer 6

true if sub query produces one or more rows/records

Question 7

ALIAS/PREFIXES

Answer 7

Each table can be given an alias in the FROM clause
FROM TableA aliasA, TableB aliasB
- aliases typically one or two characters
- alias is used to prefix attributes from a particular table in cases where the same attribute name is used in both tables
- where attribute names are unique they do not need prefixes

Question 8

Simple Join

Answer 8

attributes from both tables

common attributes prefixed

Question 9

Foreign keys always….

Answer 9

point at a unique set of attributes, usually a primary key

Question 10

What can sort a join

Answer 10

any of the attributes defined in the select clause

Question 11

Inner join

Answer 11

contains records in result only where corresponding records were found in both tables

Question 12

Full outer join

Answer 12

Contains records in result where any records were found in either input table, nulls placed in missing attributes

Question 13

Left Outer Join

Answer 13

contains records in results where records were in the first input table
- place nulls in result attributes that are missing because of a missing record in the second input table

Question 14

By making all joins as multi-table queries

Answer 14

ensures you make your intentions explicit

Question 15

joining tables

Answer 15

requires both tables have the same structure

• same # of columns
• corresponding columns are of the same type and length

Question 16

INSERT

Answer 16

enters one or more new records into an exisiting table
- if attribute names not specified insert assumes the values are in the order defined in the table definition
(will fail if attribute not given a value and attribute defined as not null)

Question 17

VALUES clause

Answer 17

allows the addition of a single record based on the values it identifies
- can be replaced with a query to add multiple records

Question 18

attributes in VALUES clause

Answer 18

values specified in same order as attributes in INSERT clause
- have to provide a value for each value specified in INSERT clause

Question 19

UPDATE

Answer 19

changes the contents of one or more existing records in an existing table

Question 20

SET CLAUSE

Answer 20

can specify one or more attributes to be updated
can specify any valid value for the attribute whether specified explicitly, computationally, and/or as the result of a search

Question 21

DELETE

Answer 21

removes one or more existing records from a table

- with no where clause deletes everything