Week 4

Question 1

What are four basic SQL operations?

Answer 1

Create, Read, Update, and Delete (CRUD)

Question 2

What does DDL stand for?

Answer 2

Data definition language - used to define, change, or drop database objects

Question 3

What does DML stand for?

Answer 3

Data manipulation language - used to read and modify data

Question 4

What does DCL stand for?

Answer 4

Data control language - used to grant and revoke authorizations

Question 5

How can you create a table in SQL?

Answer 5

create table myTable (myColumn1 integer)

Question 6

What are some 7 basic data types in SQL?

Answer 6

integer, float, decimal, char, date, time, blob

Question 7

What does a UNIQUE constraint do?

Answer 7

A UNIQUE constraint prevents duplicate values in a table. This is implemented using unique indexes and is specified in the CREATE TABLE statement using the keyword UNIQUE. A NULL is part of the UNIQUE data values domain.

Question 8

What does a PRIMARY KEY constraint do?

Answer 8

A PRIMARY KEY constraint is similar to a UNIQUE constraint, however, it excludes NULL as valid data. Primary keys always have an index associated with them. This defines a primary key for the table.

Question 9

What does a REFERENTIAL constraint do?

Answer 9

A REFERENTIAL constraint is used to support referential integrity which allows you to manage relationships between tables.

Question 10

What does a A CHECK constraint do?

Answer 10

A CHECK constraint ensures the values you enter into a column are within the rules specified in the constraint.

Question 11

Can Referential integrity be defined during and after a table is created?

Answer 11

Referential integrity can be defined during table definition or after the table has been
created.

Question 12

What is the name of a table whose column values depend on the values of other tables?

Answer 12

A table whose column values depend on the values of other tables is called a dependent, or
child table

Question 13

What is a base or parent table?

Answer 13

A table being referenced by a child table or dependent.

Question 14

When the constraint name is not specified what will happen in DB2?

Answer 14

The DB2 system will generate the name automatically

Question 15

What are some delete action types?

Answer 15

A delete action type can be a CASCADE, SET NULL, NO ACTION, or RESTRICT.

Question 16

What are some update action types?

Answer 16

An update action type can be a NO ACTION or RESTRICT.

Question 17

What is a schema?

Answer 17

A schema in DB2 is a database object that allows you to group related database objects together

Question 18

How do you create a schema?

Answer 18

create schema mySchema

Question 19

What is a view?

Answer 19

A view is a virtual table derived from one or more tables or other views. It is virtual because
it does not contain any data, but a definition of a table based on the result of a SELECT
statement

Question 20

Can views be used for security?

Answer 20

Views allow you to hide data or limit access to a select number of columns; therefore, they
can also be used for security purposes.

Question 21

What does * represent?

Answer 21

All columns from a table

Question 22

what does SELECT do in SQL?

Answer 22

Selecting data in SQL is an operation that allows you to read (retrieve) rows and columns
from a relational table.

Question 23

What is a cursor in SQL?

Answer 23

A cursor is a resulting set from your selection

Question 24

What are the three things cursors can do in SQL?

Answer 24

Open (set/file/result), Fetch line by line, and Close (set/file/result)

Question 25

With INSERT can you insert one row, multiple rows per insert, or all rows of a query?

Answer 25

Yes, INSERT can be used one, many or all rows of a query

Question 26

With DELETE if you do not use a WHERE statement it will delete all rows from the table?

Answer 26

Yes, you need to use WHERE to avoid this like so - DELETE FROM myTable WHERE col1 > 1000

Question 27

With UPDATE if you do use a WHERE statement will it update all rows from the table?

Answer 27

Without a WHERE statement UPDATE will affect all rows

Question 28

What are table joins?

Answer 28

Ways that tables can be grouped together.

Question 29

What are the two types of table joins?

Answer 29

Inner Join (tables are equal or have matching names) and Outer Join (tables are joined in a particular direction left to right, both, right to left)

Question 30

Does the Union operator remove duplicate rows?

Answer 30

Yes, and the data sets must have the same column count in the same column definitions in the same order.

Question 31

What does the INTERSECT return?

Answer 31

INTERSECT returns common data between both sets

Question 32

What does the ALL keyword do?

Answer 32

It will allow duplicate data to show in Union, Intersect, Except

Question 33

What is the difference operator and what does it do?

Answer 33

The Except operator will return data that exists in A but not B, with no deplicates.

Question 34

What are 5 types of relational operators?

Answer 34

Basic mathematical operations like ‘+’, ‘-‘, ‘*’ and ‘/’
 Logical operators like ‘AND’, ‘OR’, and ‘NOT’
 String manipulation operators like ‘CONCATENATE’, ‘LENGTH’, ‘SUBSTRING’
 Comparative operators like ‘=’, ‘<’, ‘>’, ‘>=’, ‘<=’ and ‘!=’
 Grouping and aggregate operators
 Other miscellaneous operations like DISTINCT

Question 35

What do grouping operators do?

Answer 35

Perform operations on two or more rows (or columns) of data and provide a summary of the ouput

Question 36

What are aggregate operators?

Answer 36

Operators, which perform on two or more tuples or rows, and return a scalar result set, Like COUNT, SUM, AVERAGE, MINIMUM, etc

Question 37

What is a subquery?

Answer 37

When a query is applied within a query, the inner query is the subquery.

Question 38

Are subqueries executed first?

Answer 38

Yes, the result is then returned to the parent query.

Question 39

What are Scalar values?

Answer 39

Scalar values represent a single value of any attribute or entity, for example, Name, Age,
Course, Year, and so on

Question 40

What are vector values?

Answer 40

Vector values can be results returned from multiple data from a column or many columns

Question 41

What are the initials for the 5 keys?

Answer 41

 PRIMARY KEY: pk_
 UNIQUE: uq_
 DEFAULT: df_
 CHECK: ck_
 FOREIGN KEY: fk_