Gagnasafnsfræði

What's the difference between a check constraint and an assertion?

Constraints are rules that apply to a single row only. Assertions can instead apply to multiple rows or multiple tables.

What are some common Data Manipulation Language DML commands?

INSERT, DELETE, UPDATE

What are the four types of JOINs?

(OUTER) JOIN, LEFT JOIN, RIGHT JOIN, and INNER JOIN

Aside from the equality operator, what are some other operators that one can use in a WHERE clause? Write them down.

Comparisons: <> (inequality), less than, equal to, etc. Boolean operators: AND, NOT, OR

What is * used for in queries?

* means "all attributes of the relation", so it will return all columns that satisfy the query

Write an example of a db schema

-Student(StudentID int, LastName varchar(255), FirstName varchar(255), Sex char(1))

Write the skeleton of a set query that shows its syntax.

<QUERY 1> UNION/INTERSECT/EXCEPT <QUERY 2>

What's a Syntactic Constraint?

A static constraint mitigating the relationship between elements in a column. Ex: All values in a column must be different.

What is a subquery?

A subquery is a query within another query. The outer query is called as main query, and inner query is called subquery. SubQuery is always executed first, and the result of subquery is passed on to the main query as a relation.

What is a superkey?

A the smallest set of columns (tuples) that are unique, must contain a key.

What's Data Definition Language DDL?

Allows the creation of complex schemas and their maintenance.

What is relational algebra?

An algebra whose operands are relations or variables that represent relations.

What's a Semantic Constraint?

An explicit static constraint. Ex: A student cannot register for more than 36 credits

What does the expression "NULL =/= NULL" refer to?

An instance value can be NULL, but you cannot search for it as if it were an actual value. You need to use the words IS NULL.

Module 0 - Terms: What does a column store?

Attributes (eiginði)

What's the difference between a bag and a set?

Bags are just sets that can hold duplicates

C.R.I.V. is the acronym for the most common attribute data types. What are they?

CHAR(n), INT, REAL, VARCHAR(n)

Using the schema EMP(EmpNo, Job, Hire_date, Termination_date) and Emp_Vacation(EmpNo, First_date, Last_date) Write a statement that creates an ASSERTION demanding that an employee's vacation records cannot be outside their employment period.

CREATE ASSERTION Vacation_during_employment_check( NOT EXISTS (SELECT 'Vacation Outside Employment' FROM EMP.e, Emp_Vacation ev WHERE e.EmpNo = ev.EmpNo AND (ev.First_date < e.Hire_Date OR ev.Last_Date > e.Termination_Date)

Write a simple procedure that selects all records from a Customers table

CREATE PROCEDURE SelectAllCustomers AS SELECT * FROM Customers GO;

Say you have a procedure that selects all customers in a given city and postal code. You execute the stored procedure using the following line: EXEC SelectAllCustomers City = 'London', PostalCode = '12345"; How would one actually create that procedure? Write the query.

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10) AS SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode GO;

Write down the template for creating a table, appoint at least 3 attributes.

CREATE TABLE <NAME> ( <list of elements> );

What commands are associated with Data Definition Language DDL?

CREATE TABLE, DROP TABLE, ALTER TABLE, ...

What are some advantages of using procedures?

Code is shared across all applications. They may be used for access control. May give performance benefits. Infinitely reusable.

What are some disadvantages of using procedures?

Code maintenance requirements Very system and version specific

Write down the command to DELETE a relation

DROP TABLE <NAME>;

DDL stands for ________ . Name at least 3 commands associated with DDL.

Data Definition Language CREATE TABLE, DROP TABLE, ALTER TABLE, CREATE DATABASE

DML stands for _______. Name at least 3 commands associated with DML

Data Manipulation Language -INSERT, DELETE, UPDATE

DQL stands for __________. What word is most associated with DQL?

Data Query Language - SELECT

What's the difference between the commands DELETE and TRUNCATE?

Delete can be undone using a rollback, truncate is not undo-able.

What does the EXCEPT keyword do?

EXCEPT returns rows found in the left query that are not found in the right query, no duplicates. Useful for finding rows that are in one but not the other.

How does one call a stored procedure?

EXEC ProcedureName;

Module 0 - Acronyms: What does ER stand for?

Entity Relationship Diagram

Step by step, how exactly does SQL return the results of a simple query? In what order are the following terms applied: SELECT, FROM, WHERE, and JOIN?

First, gathers the product of all relations in the FROM clause. From these, it applies the selection conditions from the JOIN clauses (if applicable). Then applies the selection conditions in the WHERE clause. Finally, returns only those that are in the list of attributes and expressions in the SELECT clause.

In simple Teddy-level terms, what does a key constraint do?

Forbids the duplication of values in a subset of the columns

What's Data Query Language DQL?

Incomplete. Includes the SELECT command.

What's a Dynamic Constraint?

It limits not the data itself, but how it can be changed. For example, salary cannot be raised by more than 5%.

What does ORDER BY do? Write a query line that uses this.

It lists results as either ASC ascending or DESC order. You can use more than one! ORDER BY name ASC, address DESC;

What does an operator do?

It takes one or more relations as input and returns one relation as output.

What does the DISTINCT keyword do, and how is it used?

It turns results into a set by discarding duplicates.

In the context of relational algebra projections, what is an extended projection?

It uses the same πₗᵢₛₜ operator. Allows the list to contain arbitrary expressions involving attributes. For example: Duplicate occurrences of the same attribute Arithmatic: R := πₗᵢₛₜ would then mean A+B--->C

What is the HAVING keyword used for?

It's used to further filter results when using the GROUP BY clause. Any group names that do not fit the criteria are removed, akin to how WHERE filters results from SELECT clauses.

Where does a table Constraint go?

It's used when creating a table. So, for example: CREATE TABLE table_name ( column1 datatype NOT NULL, column2 datatype UNIQUE, column3 datatype PRIMARYKEY, );

Which operation is more efficient? JOINs, EXCEPT, INTERSECT

JOIN

Easy peasy: How do you make certain your view shows you a maximum of 10 results, regardless of actual number?

Just add LIMIT 10 at the end.

Can you give an example that illustrates the relationship between a schema and an instance?

Kennitala --> 160387-4759 Name --> Vianey Nickname --> Fckin' Slow Cat Lady

Keys belong to the _____, not the ______.

Keys belong to the schema, not the instance!

Aside from attributes, what's something else that can be used in a SELECT clause?

Mathematical expressions. For example, converting the price on a table to a different currency by multiplying by the exchange rate.

What is the difference between a UNION and a JOIN?

Much like JOIN is used to combine columns, UNION is used to combine rows and requires no conditions. JOIN combines records from different tables based on the given conditions.

How does a NATURAL JOIN differ from a normal JOIN?

NATURAL JOIN removes duplicate columns because it assumes the joined columns have the same data.

There are at least 7 schema constraints you can use when creating a table. Name them.

NOT NULL, UNIQUE, PRIMARY KEY, CHECK, DEFAULT, INDEX

What detail about NULL values must one keep in mind when using the aggregate functions?

NULL values never contribute to a SUM(), AVG(), or COUNT() and can never be either MIN() or MAX().

What's the difference between a Primary Key and a Unique attribute when used in a Tuple?

No attribute of a Primary Key can ever be NULL in any Tuple. But Unique attributes MAY have several Tuples with Null.

What are the requirements for using UNION?

Number of columns and their data types must match for both SELECT statements.

Let's say a coffee is sold nowhere but we still want it in our results. What term could we include in our query to include it?

OUTER JOIN

Algebra is defined as a mathematical system of operands and operators. What are these?

Official definition: Operands are variables or values from which new values can be constructed. Operators are symbols denoting procedures that construct new values from given values

There are two types of key values, what are they?

Primary and Foreign

What is a procedure?

Procedures are much like functions in python. They allow you to write code once, then call it whenever you need it. Usually stored as SQL files.

"English, please?" The following refers to relational algebra projections. Can you read it in plain language? R1 := πₗᵢₛₜ(R2)

R1 is a list of tuples from a filtered attribute list from R2. It is made by extracting attributes on a given list in the order specified. No duplicates.

"English, please?" The following refers to relational algebra selections. Can you read it in plain language? R1 := σ꜀ (R2)

R1 is all those tuples of R2 that satisfy condition c subscript.

In plain language, what does an INNER JOIN do?

Returns results that match the query, but only if they are found in BOTH the tables being joined.

Write down a SELECT block template

SELECT (DISTINCT) attribute 1, attribute 2, FROM one or more tables WHERE condition about table tuples;

Rewrite this JOIN query so that it doesn't use the ANSI syntax, and therefore doesn't use the word JOIN: SELECT * FROM Likes L JOIN Drinkers D ON L.Drinker = D.Name

SELECT * FROM Likes L, Drinkers D WHERE L.Drinker = D.Name;

In your own words, describe the difference between a LEFT JOIN and a RIGHT JOIN. Write a query or draw a picture if you need to.

SELECT * FROM A LEFT JOIN B ON A.key = B.key The first table named is LEFT, the second is RIGHT. Therefore, this will return all results that fit the search terms, as long as they are found in the table to the LEFT, that is A or BOTH table A and B.

Using Coffees(name, manufacturer) find all pairs of coffees by the same manufacturer. Do not produce pairs like (Maracaibo, Maracaibo). List results in ABC order

SELECT C1.name, C2.name FROM Coffees C1 JOIN Coffees C2 ON C1.Manufacturer = C2.Manufacturer WHERE C1.name = C2.name ORDER BY name;

Using Likes(Drinker, Coffee) and Frequents(Drinker, Coffeehouse) write a query that finds the coffees liked by at least one person who frequents Te of Kaffi

SELECT Coffee FROM Likes JOIN Frequents ON Frequents.Drinker = Likes.Drinker WHERE Coffeehouse = 'Te og Kaffi';

Rewrite the following query so that it uses JOINs instead of EXCEPT: SELECT V.Name FROM Vendor V EXCEPT SELECT C.Name FROM Customer C ORDER BY Name

SELECT DISTINCT V.Name FROM VENDOR V LEFT OUTER JOIN CUSTOMER C ON V.Name = C.Name WHERE C.Name IS NULL ORDER BY V.Name

Renaming relations: Using relations Likes(Drinker, Coffee) and Frequents(Drinker, Coffeehouse) find the coffees liked by at least one person who frequents Joe's.

SELECT L.Coffee FROM Likes L JOIN Frequents F ON F.Drinker = L.Drinker WHERE F.Coffeehouse = "Joe's";

Say you're doing a SELECT query. What keyword does one use to rename how the column appears in the results? Write down a simple query that shows this.

SELECT Name AS Coffee FROM Coffees WHERE Manufacturer = 'CoName';

Rewrite the following query so that it uses INTERSECT instead of INNER JOIN: SELECT DISTINCT V.Name FROM Vendor V INNER JOIN CUSTOMER C ON V.Name = C.Name ORDER BY V.Name; What does this return?

SELECT V.Name FROM VENDOR V INTERSECT SELECT C.Name FROM Customer C ORDER BY Name This returns all Customer and Vendor names that are identical

Using Vendor(Name) and Customer(Name) create a query that returns all Vendor names not shared by any customer. List them in ABC order.

SELECT V.Name FROM Vendor V EXCEPT SELECT C.Name FROM Customer C ORDER BY Name;

Write a query for selecting all coffee names where the manufacturer is called Ottolina

SELECT name FROM coffees WHERE manufacturer = 'Ottolina';

When using the keyword LIKE, we need to specify a pattern. Using Sells(Coffeehouse, coffee, price) Write down a simple query that returns all coffee houses that start with the letter A and all coffees sold in them that have names longer than 4 characters.

SELECT name, coffee FROM Sells WHERE name LIKE 'a%' AND coffee NOT LIKE '____';

Write down the following query: Using Sells(Coffeehouse, coffee, price), find the price Te og Kaffi charges for Maracaibo coffee.

SELECT price FROM Sells WHERE coffeehouse = 'Te og Kaffi' AND coffee = 'Maracaibo";

What are the three crucial words for a SELECT block?

SELECT, FROM, WHERE

What are the five aggregation functions that can be used in a SELECT clause?

SUM(), AVG(), MIN(), MAX(), COUNT()

What's a foreign key?

Show relationship between tables. It must be a key constraint in it's home table.

What's Data Manipulation Language DML?

Simple commands for complicated actions.

Module 0 - Acronyms: What does SQL stand for?

Structured Query Language

There are two kinds of static constraints, what are they?

Syntactic and Dynamic

When using the GROUP BY term, how are results displayed if the SELECT clause included an aggregate function?

The relation is grouped according to the GROUP BY term, and any aggregation is applied only within those groups.

What happens if all values in a query column are null and you use an aggregate function in your SELECT clause?

The result of the aggregation is also NULL. The only exception to this is getting a 0 when counting an empty set.

What does the term database schema formally refer to? Database instance?

The set of all relation schema in the database. The set of all relation instances in the db.

Consider the following statement: SELECT City, Country FROM Customers WHERE Country = 'Germany' UNION City, Country FROM Suppliers WHERE Country ='Germany' ORDER BY City;

The statement returns the German cities (only distinct values) from both the "Customers" and the "Suppliers" table

Assuming you write a correct set query statement, what are other possible reasons to get an error?

The two queries being compared must be "UNION compatible". Results must have matching schema: Same number of attributes, and compared attributes must be of same data type.

Describe what happens to the elements of a bag intersection.

The two sets become one, but only one copy of each element remains. (no duplicates)

Describe what happens to the elements of a bag union.

The two sets become one, keeping all duplicates.

What's the main difference between a Primary Key and a Unique attribute?

There can be several Unique attributes, but only ever one Primary Key for a relation.

How many keys can a relation have?

There's no limit.

When creating a procedure, what are these? @City nvchar(20), @PostalCode nvarchar(10)

These are your parameters

What should you remember about using * in aggregate functions?

Try to avoid it, unless you're using COUNT() or EXISTS.

Module 0 - Terms: What does a row store?

Tuples/records

What is the difference between UNION and UNION ALL?

UNION returns a distinct list of rows, no duplicates. UNION ALL keeps all data regardless of repetition.

Out of the following operators, which one cannot be replaced by a different one? INTERSECT, UNION, EXCEPT

UNION. INTERSECT can be rewritten using INNER JOIN and EXCEPT Can be rewritten using LEFT OUTER JOIN

What does INTERSECT do?

Useful for finding common data between two tables, as it returns only rows they both have.

There are three types of OUTER JOINs. Explain what each does.

Using R OUTER JOIN S LEFT OUTER JOIN = Pad dangling tuples of R with NULL RIGHT = Pad dangling tuples of S with NULL FULL = Pad BOTH. Default option

What is the relationship between INTERSECT and INNER JOIN?

You can use them to do the same thing: Return data two tables have in common.

Using, OrderItem(Id, OrderId, ProductId, UnitPrice, Quantity) and ProductName(Id, ProductName, SupplierId, UnitPrice, Package, isDiscontinued) write a statement that returns products with order quantities greater than 100

You need to use SUBQUERIES! SELECT ProductName FROM Product WHERE Id in ( SELECT ProductId FROM OrderItem WHERE Quantity > 100 )

If any aggregation is used, then each element of the SELECT List must be: 1. An _____________ attribute 2. An attribute on the ____________ list

aggregated, GROUP BY

"A set of columns matching a set of columns with a key constraint in _______ table is a _______ constraint."

another, foreign

Fill in the blanks: "If all the tuple-variables are pointing to tuples that ___________ the __________ and ____________ clauses, send these tuples to the ___________ clause."

satisfy, JOIN, WHERE, SELECT