Lecture 9- Other Features and Commands

Question 1

What type of query is this?

emp(sin, name, phone, city)

Answer 1

INSERT

Question 2

What type of query is this?

emp(sin, name, phone, city)
Edmonton_phonebook(name, phone)

Answer 2

Bulk INSERT

Question 3

What type of query is this?

R(a1,…an)

Answer 3

DELETE

Question 4

What type of query is this?

R(a1,…,an)

Answer 4

UPDATE

Question 5

What is this query doing?

customer(cname, street, city)
deposit(accno, cname, bname, balance)

Answer 5

Insert a new customer record for John Smith who lives on 345 Jasper Ave, Edmonton

Question 6

What is this query doing?

customer(cname, street, city)
deposit(accno, cname, bname, balance)

Answer 6

Delete all customers who have less than $1000 in their accounts

Question 7

What is this query doing?

customer(cname, street, city)
deposit(accno, cname, bname, balance)

Answer 7

Increase by 5% the balance of every customer who lives in Edmonton and has a balance of more than $5000

Question 8

What is VIEW?

Answer 8

• View = query(~) = table
• A derived table whose definition, not the table itself is sorted
• Provides a degree of data independence
• Queried like a table

Question 9

Create this query: create a view of customers who live in Jasper and name it Jasper customers

customer(cname, street, city)

Answer 9

Question 10

Create this query: list the names of all customers in Jasper

customer(cname, street, city)

Answer 10

Question 11

In queries, ____ is exactly like a base table

Answer 11

A VIEW

Question 12

What is this query doing?

customer(cname, street, city)
deposit(accno, cname, bname, balance)

Answer 12

Create a view (called cust info) which gives for each customer the name, city, the number of deposit accounts awned and the total balance

Question 13

What is this query doing?

customer(cname, street, city)
deposit(accno, cname, bname, balance)

Answer 13

Create a view (called deposit holders) which includes the name and the city of every deposit account holder

Question 14

Views defined on ____ are generally not updatable

Answer 14

Multiple tables using joins

Question 15

A view defined on a single table is updatable if?

Answer 15

The view attributes contain the primary key or some other candidate key

Question 16

Views defined using ____ are not updatable

Answer 16

Aggregate functions

Question 17

Each row and each column in an updatable view must?

Answer 17

Must correspond to a distinct row and column in a base table

Question 18

SQLite only supports?

Answer 18

Only supports read-only views

Question 19

Give an example of left outer join

Answer 19

Question 20

Give an example of right inner join

Answer 20

Question 21

Give an example of full outer join

Answer 21

Question 22

Give an example of left outer join implementation in SQLite

Answer 22

Question 23

What is an alternative syntax to left outer join?

Answer 23

• Join columns can have different names
• More general conditions are possible
• Two copies of column B

Question 24

When is an unknown null value useful?

Answer 24

Useful when we don’t know a column value

Question 25

Give some examples of queries that use null values

Answer 25

* WHERE phone IS NULL * WHERE phone IS NOT NULL * SELECT cname FROM CUSTOMER WHERE city IS NOT NULL

Question 26

What are the three valued logics to use if the WHERE clause consists od several predicates?

Answer 26

1. TRUE 2. FALSE 3. UNKNOWN

Question 27

What is a NULL value?

Answer 27

* SQL uses NULL values to represent unknown/inapplicable values * The presence of NULL values complicates many issues (comparison using NULL values, using logical operators AND, OR, NOT, impacts of SQL constructs

Question 28

What operators to use to deal with unknown situations?

Answer 28

* is NULL * is not NULL

Question 29

What are some set operations?

Answer 29

* UNION * INTERSECT * EXCEPT

Question 30

By default, duplicates are removed from the result of a?

Answer 30

Set operation

Question 31

To keep duplicate use?

Answer 31

UNION ALL

Question 32

What are some allowed integrity constraints?

Answer 32

* Not null * Unique * Check(predicate)

Question 33

What are NOT NULL/Domain constraints?

Answer 33

* Specifies that an attribute cannot contain null values * Should be specified for all primary keys (if its not default) * The NOT NULL constraint prohibits the insertion of NULL value for specified attribute

Question 34

Give an example of NOT NULL/Domain constraints

Answer 34

Question 35

What are UNIQUE constraints?

Answer 35

* Specifies the alternate keys * It ensures that each value in a column or set of columns is unique across all rows in the table * Unlike the PRIMARY key constraint, a UNIQUE constrain allows NULL values * A table can have more than one UNIQUE constraint, unlike a PRIMARY key constrain where a table can have only one

Question 36

Give an example of a UNIQUE constraint

Answer 36

Question 37

What are CHECK constraints?

Answer 37

* A common use of the CHECK clause is to ensure that attributes values satisfy specified conditions * The clause CHECK(P) specifies a predicate P that must be satisfied by every tuple in a relation

Question 38

Give an example of CHECK constraint

Answer 38

Question 39

Give an example of CREATE DOMAIN constraints

Answer 39

Question 40

What are domain constraints?

Answer 40

Specifies the condition that each row has to satisfy

Question 41

Give an example of a domain constraint

Answer 41

Question 42

What are tuple constraints?

Answer 42

* Checked everytime a tuple is inserted or updated, violations are rejected * For when we want to set constraints for multiple fields

Question 43

What do you need before a tuple constraint?

Answer 43

Need the comma before a tuple constraint

Question 44

What type of constraint is this?

Answer 44

Tuple constraint

Question 45

What are assertions and why use them?

Answer 45

* Global constraints of the form CREATE ASSERTION name CHECK (condition) * For when we want to set constraints over all tables (global constraint) * Before any insertions or update can be committed to the database perform the check and apply the specified condition

Question 46

Give an example of an assertion

Answer 46

Question 47

What are triggers in SQLite?

Answer 47

* A set of actions available in the form of stored program * These set of actions are invoked automatically when certain actions occur * A trigger is created using the CREATE TRIGGER statement followed by the name of your trigger

Question 48

How do you use TRIGGER statements?

Answer 48

* Make sure each trigger has a unique name * Next specify which table it must be assigned to * Specify the triggers logic, what it must acheive * If it requires multiple statements, then these must be enclosed within a BEGIN-END block

Question 49

Complete this task: Joe Smiths sales team are adding discounts to products, however, any discount over 25% must be reviewed by a manager

Answer 49

Question 50

What is this TRIGGER statement doing?

Answer 50

* The purpose of this trigger is to log the old address information into an addressing table whenever there is a change in the address * This is creating a trigger called log-addresses * The trigger will activate after an UPDATE operation on the customer table * The trigger will only fire (execute) when there is a change in the street or city columns * If the stree or city changes the trigger will proceed * This inserts a new record into the addressing table, logging the details of the customers old and new addresses

Question 51

SQLite does not support updatable view, therefore ____ can be used instead

Answer 51

Triggers