SQL

Question 1

what is DDL?

Answer 1

Data Definition Language
done at the table level

Question 2

what is DML?

Answer 2

Data Manipulation Language
done at the data level

Question 3

what does CREATE do?

Answer 3

creates a table

Question 4

what does ALTER do?

Answer 4

changes a table

Question 5

what does DROP do?

Answer 5

deletes an entire table

Question 6

what does INSERT do?

Answer 6

inserts data into a table

Question 7

what does UPDATE do?

Answer 7

makes changes to tuples

Question 8

what does DELETE do?

Answer 8

deletes tuples

Question 9

what does SELECT do?

Answer 9

retrieves data from one or more tables

Question 10

what does the NOT NULL constraint do?

Answer 10

No entries in that column can be null

Question 11

what does the UNIQUE constraint do?

Answer 11

each entry in that specific column must have a unique value, can be NULL

Question 12

what does the PRIMARY KEY do?

Answer 12

ensures that a column (or combination of two or more columns) have a unique identity which helps to find a particular record in a table more quickly and easily

Question 13

what does a FOREIGN KEY do?

Answer 13

ensures the referential integrity of the data in one table to match values in another table
cannot be NULL

Question 14

what does a CHECK do?

Answer 14

ensures that a value in a column meets a specific condition

Question 15

what constraints make it so no entry in the column can be NULL?

Answer 15

PRIMARY KEY
FOREIGN KEY
NOT NULL

Question 16

does a modification command in DML return a result?

Answer 16

NO

Question 17

what happens if you do not specify a WHERE clause in your DELETE FROM statement?

Answer 17

it will delete EVERY entry from the table, but NOT the table. Basically, you will get an empty table.

Question 18

what does an underline indicate in a database schema?

Answer 18

a PRIMARY KEY

Question 19

what does SELECT * FROM table do?

Answer 19

prints the entire table

Question 20

how do you rename attributes in a SELECT FROM WHERE statement?

Answer 20

SELECT name1 AS name2
FROM table
WHERE condition

Question 21

what is the not equal to operator in SQL?

Answer 21

<>

Question 22

how would you find the entries that begin with Bee?

Answer 22

attributeName LIKE ‘Bee%’

Question 23

how would you find the entries that end with Bee?

Answer 23

attributeName LIKE ‘%Bee’

Question 24

how do you compare a value with NULL?

Answer 24

columnName IS NULL
columnName IS NOT NULL

Question 25

what is the result of comparing any value with NULL?

Answer 25

UNKNOWN

Question 26

does NULL contribute to SUM, AVG, or COUNT (or any other aggregation?

Answer 26

NO

Question 27

can NULL be the minimum or maximum of a column?

Answer 27

NO

Question 28

what is the result of an aggregation if all of the values in the column are NULL?

Answer 28

NULL

Question 29

what tuples result from R OUTER JOIN S

Answer 29

all tuples from R and S

Question 30

what tuples result from R LEFT OUTER JOIN S

Answer 30

All tuples from R and the ones from S that have a match

Question 31

what tuples result from R RIGHT OUTER JOIN S

Answer 31

All tuples from S and the ones from R that have a match

Question 32

what tuples result from R FULL OUTER JOIN S

Answer 32

all tuples from R and S

Question 33

when is the IN operator true?

Answer 33

if an only if the tuple is a member of the relation produced by the subquery

Question 34

when is the ANY operator true?

Answer 34

if and only if x equals at least one tuple in the subquery

Question 35

what is the condition to use the ANY operator?

Answer 35

the subquery must produce a table with ONLY one attribute

Question 36

when is x <> ALL (subquery) true?

Answer 36

if and only if for every tuple t in the relation, x is not equal to t (<> is not equal operator)

Question 37

by default, the ORDER BY sorts data in which order?

Answer 37

ascending

Question 38

SQL is a ____-_____ level language

Answer 38

high-level

Question 39

instead of telling SQL how to do something, you tell it what?

Answer 39

what to do

Question 40

what is query optimization?

Answer 40

the database management system figures out the best way to execute the query

Question 41

what are the two main components of SQL?

Answer 41

DDL and DML

Question 42

what is a general example of creating a table?

Answer 42

CREATE TABLE tablename( column_name datatype, );

Question 43

what are valid table and variable names in SQL?

Answer 43

any alphanumeric combination that doesn't start with a digit (CAN start with an underscore)

Question 44

what special character can a table name start with in SQL?

Answer 44

an underscore _

Question 45

what are the four categories of datatypes?

Answer 45

text, number, date, binary

Question 46

what does this statement do in a table declaration? check (born > 1950)

Answer 46

it allows only people born after 1950 to be entered into the table

Question 47

who came up with the idea of foreign keys?

Answer 47

Chris Date

Question 48

what is SQL THE language for?

Answer 48

accessing databases

Question 49

what is a generic example of adding a primary key constraint to an already created table?

Answer 49

ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey PRIMARY KEY(col1, col2)

Question 50

what is a generic example of removing a constraint from a table?

Answer 50

ALTER TABLE table_name DROP CONSTRAINT MyPrimaryKey

Question 51

what are the three kinds of data modifications in DML?

Answer 51

insert delete update

Question 52

what is a generic example of inserting values into a table?

Answer 52

INSERT INTO table_name (list of columns) VALUES (list of values)

Question 53

do you have to specify the column names when inserting data into a table?

Answer 53

YES!

Question 54

what are the reasons for specifying attributes in INSERT?

Answer 54

1) we forgot the order of the attributes for the table 2) So that, if we don't have a value for a certain column in a tuple, SQL will put NULL in for that value

Question 55

how do you insert many tuples into a table using a query?

Answer 55

INSERT INTO tableName ();

Question 56

what is the generic syntax to change certain attributes in certain tuples of a table?

Answer 56

UPDATE table_name SET WHERE condition;

Question 57

what is the generic syntax to delete certain tuples from a table?

Answer 57

DELETE FROM table_name WHERE condition;

Question 58

what are the common cases for NULL values in a table?

Answer 58

1) Missing value: we know Joe's bar has some address, but don't know what it is 2) inapplicable: the values of attribute died for an alive person

Question 59

what happens when you compare NULL with any value including itself using an operator (=, <>, etc.)

Answer 59

you get UNKNOWN

Question 60

what are the three values SQL can produce when you compare something?

Answer 60

true false unknown

Question 61

what kind of semantics does a SELECT-FROM-WHERE statement use?

Answer 61

bag semantics, which allow repeat data

Question 62

where are aggregations applied when a GROUP BY statement is used?

Answer 62

inside each group

Question 63

How do you distinguish attributes of the same name?

Answer 63

TableName.attributeName

Question 65

what is the statement for a natural join in SQL?

Answer 65

R NATURAL JOIN S

Question 66

what is the statement to perform a cross join in SQL?

Answer 66

R CROSS JOIN S

Question 67

what is the definition of a sub query?

Answer 67

A parenthesized s SELECT FROM WHERE statement that can be used in the FROM or WHERE clauses in the outer SELECT FROM WHERE statement

Question 68

If the sub query is guaranteed to produce one tuple, then the subquery can be used as what?

Answer 68

A value

Question 69

What happens if you try to use a subquery as a value and it has more than one tuple or no tuples?

Answer 69

A runtime error occurs

Question 70

When is the IN operator true?

Answer 70

If and only if the tuple is a member of the relation produced by the subquery

Question 71

When is the IN operator true?

Answer 71

If and only if the tuple is a member of the relation produced by the subquery

Question 72

When should you use IN?

Answer 72

When you don't know how many tuples your subquery will return

Question 73

When is x=ANY true?

Answer 73

If and only if x equals at least one tuple in the subquery result

Question 74

When is x <> ALL (subquery) true?

Answer 74

If and only if, for every tuple t in the subquery result, x is not equal to t.

Question 75

If any aggregation is used, then each attribute in the SELECT clause must be either what?

Answer 75

Aggregated An attribute on the GROUP BY list