Lecture 9- Other Features and Commands Flashcards
What type of query is this?
emp(sin, name, phone, city)
INSERT
What type of query is this?
emp(sin, name, phone, city)
Edmonton_phonebook(name, phone)
Bulk INSERT
What type of query is this?
R(a1,…an)
DELETE
What type of query is this?
R(a1,…,an)
UPDATE
What is this query doing?
customer(cname, street, city)
deposit(accno, cname, bname, balance)
Insert a new customer record for John Smith who lives on 345 Jasper Ave, Edmonton
What is this query doing?
customer(cname, street, city)
deposit(accno, cname, bname, balance)
Delete all customers who have less than $1000 in their accounts
What is this query doing?
customer(cname, street, city)
deposit(accno, cname, bname, balance)
Increase by 5% the balance of every customer who lives in Edmonton and has a balance of more than $5000
What is VIEW?
- View = query(~) = table
- A derived table whose definition, not the table itself is sorted
- Provides a degree of data independence
- Queried like a table
Create this query: create a view of customers who live in Jasper and name it Jasper customers
customer(cname, street, city)
Create this query: list the names of all customers in Jasper
customer(cname, street, city)
In queries, ____ is exactly like a base table
A VIEW
What is this query doing?
customer(cname, street, city)
deposit(accno, cname, bname, balance)
Create a view (called cust info) which gives for each customer the name, city, the number of deposit accounts awned and the total balance
What is this query doing?
customer(cname, street, city)
deposit(accno, cname, bname, balance)
Create a view (called deposit holders) which includes the name and the city of every deposit account holder
Views defined on ____ are generally not updatable
Multiple tables using joins
A view defined on a single table is updatable if?
The view attributes contain the primary key or some other candidate key
Views defined using ____ are not updatable
Aggregate functions
Each row and each column in an updatable view must?
Must correspond to a distinct row and column in a base table
SQLite only supports?
Only supports read-only views
Give an example of left outer join
Give an example of right inner join
Give an example of full outer join
Give an example of left outer join implementation in SQLite
What is an alternative syntax to left outer join?
- Join columns can have different names
- More general conditions are possible
- Two copies of column B
When is an unknown null value useful?
Useful when we don’t know a column value
Give some examples of queries that use null values
- WHERE phone IS NULL
- WHERE phone IS NOT NULL
- SELECT cname FROM CUSTOMER WHERE city IS NOT NULL
What are the three valued logics to use if the WHERE clause consists od several predicates?
- TRUE
- FALSE
- UNKNOWN
What is a NULL value?
- 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
What operators to use to deal with unknown situations?
- is NULL
- is not NULL
What are some set operations?
- UNION
- INTERSECT
- EXCEPT
By default, duplicates are removed from the result of a?
Set operation
To keep duplicate use?
UNION ALL
What are some allowed integrity constraints?
- Not null
- Unique
- Check(predicate)
What are NOT NULL/Domain constraints?
- 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
Give an example of NOT NULL/Domain constraints
What are UNIQUE constraints?
- 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
Give an example of a UNIQUE constraint
What are CHECK constraints?
- 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
Give an example of CHECK constraint
Give an example of CREATE DOMAIN constraints
What are domain constraints?
Specifies the condition that each row has to satisfy
Give an example of a domain constraint
What are tuple constraints?
- Checked everytime a tuple is inserted or updated, violations are rejected
- For when we want to set constraints for multiple fields
What do you need before a tuple constraint?
Need the comma before a tuple constraint
What type of constraint is this?
Tuple constraint
What are assertions and why use them?
- 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
Give an example of an assertion
What are triggers in SQLite?
- 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
How do you use TRIGGER statements?
- 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
Complete this task: Joe Smiths sales team are adding discounts to products, however, any discount over 25% must be reviewed by a manager
What is this TRIGGER statement doing?
- 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
SQLite does not support updatable view, therefore ____ can be used instead
Triggers
