Lecture Materials Week 9 (File Name)

Question 1

SQL stands for

Answer 1

Structured Query Language

Question 2

SQL was developed by this company in the late 70s

Answer 2

IBM

Question 3

SQL is not a full-featured ___________ ________

Answer 3

programming language

Question 4

SQL is a ____ ___________ for creating and processing database data/metadata

Answer 4

data sublanguage

Question 5

SQL statements can be divided into 5 categories, they are:

Answer 5

DDL: Data definition language

DML: Data manipulation language

DCL: Data control language

TCL: Transaction control language

and SQL/PSM: Persistent stored modules statements

Question 6

The ______ _____ statement is used to create relations

Answer 6

CREATE TABLE

Question 7

Each column in a table is described with 3 parts:

Answer 7

Column name, data type, and optional constraints

Question 8

Place these in the correct order

DataType
ColumnName
OptionalConstraint

Answer 8

ColumnName Datatype Optional Constraint

Question 9

Place these in the correct order

Optional table constraint

CREATE TABLE newTableName

ColumnName DataType OptionalConstraint,

Answer 9

CREATE TABLE newTableName

ColumnName DataType Optional Constraint,

Optional table constraint

Question 10

Constraints can be ______ __ the CREATE TABLE statement or can be added _____ using the _____ table statement

Answer 10

placed in, added after, ALTER Table

Question 11

5 Different Column/table constraints

Answer 11

Primary key
Foreign key
NULL/NOT NULL
Unique
Check

Question 12

The _______ keyword can be used to set initial values

Answer 12

Default

Question 13

Implementing Cardinalities:

1:N relationship, parent optional

Answer 13

Set foreign key to NULL.

Question 14

Implementing Cardinalities:

1:N relationship, parent required

Answer 14

Set foreign key to NOT NULL.

Question 15

Implementing Cardinalities:

1:1 relationship, parent optional

Answer 15

Specify a foreign key UNIQUE constraint. Set foreign key to NULL.

Question 16

Implementing Cardinalities:

1:1 relationship, parent required:

Answer 16

Specify foreign key UNIQUE constraint. Set foreign
key NOT NULL.

Question 17

Casual relationship

Answer 17

Create a foreign key column, but do not
specify FOREIGN KEY constraint. If
relationship is 1:1, specify a foreign key
UNIQUE constraint.

Question 18

You are making the attribute “WorkID” a primary key in the “Work” table, what would the statement be to create it?

Answer 18

CONSTRAINT WorkPK PRIMARY KEY( WorkdID )

Question 19

You are making the “Title” and “Copy” attributes a unique key in the “Work” table, what would the statement be to do this?

Answer 19

CONSTRAINT WorkUK1 UNIQUE( Title, Copy )

Question 20

You are making “ManufacturerID” a foreign key in the “Work” table, it will be referencing the “Manufacturer” table. What is the statement to do this?

Answer 20

CONSTRAINT ManfacturerFK FOREIGN KEY( ManfacturerID )
REFERENCES Manufacturer( ManufacturerID )

Question 21

The SQL ALTER TABLE statement is used to

Answer 21

Change the structure of an existing table.

Question 22

The ALTER TABLE statement can not only add, remove or change columns, but can also

Answer 22

add or remove constraints

Question 23

ALTER TABLE statement that will add a column named MyColumn to the Customer table - Char(5), NULL

Answer 23

ALTER TABLE Customer
ADD myColumn Char(5) NULL:

Question 24

ALTER TABLE statement that will drop MyColumn from the customer table

Answer 24

ALTER TABLE Customer
DROP COLUMN MyColumn;

Question 25

ALTER TABLE statement that will add myConstraint that will check that a customers LastName is not in the AddressBook

Answer 25

ALTER TABLE CUSTOMER ADD CONSTRAINT myConstraint CHECK ( LastName NOT IN ( AddressBook )

Question 26

ALTER TABLE statement that will drop myConstraint from the customer table

Answer 26

ALTER TABLE CUSTOMER Drop Constraint myConstraint;

Question 27

Put this in order to change the last_name column to surname in the student table student RENAME COLUMN surname ALTER TABLE last_name TO

Answer 27

ALTER TABLE student RENAME COLUMN last_name TO surname;

Question 28

Truncate table

Answer 28

Removes all data from a table while leaving the structure intact - Can't be used with a table that is references by a foreign key in another table

Question 29

An _____ is a special data structure that is created to improve database performance

Answer 29

Index

Question 30

3 types of index statements

Answer 30

CREATE, ALTER, DROP

Question 31

To speed up inserting data you can use a ____ ______ statement to insert entire imports

Answer 31

Bulk INSERT

Question 32

If you do not include a WHERE statement in an update

Answer 32

A bulk update will execute and change every row in the entire table

Question 33

UPDATE CUSTOMER SET AreaCode = '303' WHERE City = 'Denver' Explain how this results in a bulk update, despite the included WHERE statement

Answer 33

All AreaCodes in Denver will be set to 303

Question 34

The SQL MERGE statement combines the ______ and ______ statements into one, and will do one or other depending on conditions

Answer 34

INSERT, UPDATE

Question 35

If you omit the WHERE clause from the DELETE statement

Answer 35

You will delete every row in the table

Question 36

T/F - The SQL DELETE statement resets surrogate key values

Answer 36

False