Midterm

Question 1

Definition: A logical collection of database objects

Answer 1

Schema

Question 2

What keyword eliminated duplicates?

Answer 2

DISTINCT

Question 3

What is the maximum number of columns you can ORDER BY?

Answer 3

246

Question 4

By default, ORDER BY is ________

Answer 4

Ascending

Question 5

What keyword is used to create an alias?

Answer 5

AS

Question 6

T/F: SQL is not case sensitive.

Answer 6

TRUE

Question 7

What is the default format for dates?

Answer 7

YYYY-MM-DDTHH:MM:SS:FF

Question 8

What function returns the current date timestamp?

Answer 8

GetDate()

Question 9

What keyword is used to find data from one date to another?

Answer 9

BETWEEN

Question 10

If you want to avoid using multiple OR statements, what keyword can be use?

Answer 10

IN

ex. WHERE province IN (‘ON’, ‘BC’)

Question 11

What operator is “not equals”

Answer 11

<> OR !=

Question 12

Which keyword is used when specifying a pattern?

Answer 12

LIKE

Question 13

When using LIKE, what does the % mean?

Answer 13

Any number of characters

Question 14

How do you test for NULL?

Answer 14

The keyword IS

ex. IS NULL
ex. IS NOT NULL

Question 15

What order are logical operators tested in?

Answer 15

NOT, AND, OR

Question 16

What is returned if you omit an INNER JOIN or an EQUI-JOIN?

Answer 16

Cartesian Product

ex. FROM products, customers

Question 17

Why is it bad to use an equality join?

Answer 17

Ambiguity leads to weird results

Question 18

What keyword can be used to find the cartesian product?

Answer 18

CROSS JOIN

Question 19

How many INNER JOINs are needed for “n” number of tables?

Answer 19

n-1

Question 20

What is DML?

Answer 20

Data Manipulation Language

Question 21

What is DDL?

Answer 21

Data Definition Language

Question 22

What is DCL?

Answer 22

Data Control Language

Question 23

When creating tables, how many characters can be in the name?

Answer 23

1-128

Question 24

Why are you able to make a table without a Primary Key specified?

Answer 24

Artificial Key

Question 25

What is an artificial key?

Answer 25

A hidden key made up of a physical address (page# + fileid)

Question 26

What keyword is used to delete a table?

Answer 26

DROP

Question 27

What keyword is used to make a table?

Answer 27

CREATE

Question 28

How many characters can a VARCHAR be?

Answer 28

8000

Question 29

What is a VARCHAR datatype?

Answer 29

Variable length string

Question 30

What's the difference between VARCHAR and CHAR?

Answer 30

CHAR adds padding to unused space

Question 31

How do you specify an apostrophe when specifying a CHAR variable?

Answer 31

Two single quotes, NOT a double quote

Question 32

How many bits in an INT?

Answer 32

32

Question 33

How do you get the data dictionary?

Answer 33

INFORMATION_SCHEMA.

Question 34

What is a data dictionary?

Answer 34

Metadata on the database

Question 35

How do you create a table with a SELECT statement?

Answer 35

SELECT column INTO new_table FROM existing_table

Question 36

What are the 3 commands that can be used with ALTER

Answer 36

1) ADD 2) MODIFY 3) DROP

Question 37

What do constraints do?

Answer 37

Throw exceptions

Question 38

What are the two ways to specify constraints?

Answer 38

1) Column | 2) Table

Question 39

Which type of constraint can ONLY be specified through a column constraint?

Answer 39

NOT NULL

Question 40

What are the 5 types of constraints?

Answer 40

1) Primary Key 2) Foreign Key 3) Unique 4) Check 5) NOT NULL

Question 41

What is the syntax for a primary key table constraint?

Answer 41

CONSTRAINT table_name_column_PK PRIMARY KEY(column)

Question 42

What is the syntax for a foreign key table constraint?

Answer 42

CONSTRAINT table_name_column_FK FOREIGN KEY (column) REFRENCES refrenced_table (column)

Question 43

What is the syntax for a unique table constraint?

Answer 43

CONSTRAINT table_name_column_UK UNIQUE(column)

Question 44

What is the syntax for a check constraint?

Answer 44

CONSTRAINT table_name_column_CK CHECK(boolean check)

Question 45

What is the syntax for table creation?

Answer 45

``` CREATE TABLE table_name ( column_name DATATYPE, column_name DATATYPE, CONSTRAINT ); ```

Question 46

What is the syntax for an INSERT statement?

Answer 46

INSERT INTO table_name(column1, column2...column(n)) VALUES(value1, value2, ... value(n))