onsdag the concepts in a relation data model sql ddl dml

Onsdag

The concepts in a relation data model

SQL

DDL

DML

Concepts

Ansat Ansat Nr

Fornavn Efter navn

Foedsels dato

Uddannelse Maaneds Loen

Afdeling

19 Peter Knudsen 240165 Murer 20000 1 34 Mads Michelsen 081151 Murer 23000 1 2 Anne Andersen 230245 Sekretær 19000 1 123 Marianne Jensen 240165 Ingeniør 25000 2 23 Svend Michelsen 111253 Tømrer 20000 2 102 Hans

Henrik Pedersen 021170 Byggetekniker 23000 3

Afdeling AfdelingNr

AfdelingNavn Chef ChefStartDato

1 Nybygning 34 110164 2 Renovering 123 230671 3 Nedrivning 102 061193

Relation navn Attributter

Records/Tupler

create table Employee (fname varchar(15) not null, minit char, lname varchar(15) not null, ssn char(9), bdate datetime, address varchar(30), sex char, salary decimal(10,2), superssn char(9), dno int not null, primary key (ssn), foreign key(superssn) references Employee(ssn), foreign key (dno) references Department(dnumber));

Create table

create table Employee(……. dno int not null default 1, primary key (ssn), foreign key(superssn) references

Employee(ssn) on update cascade, foreign key (dno) references Department(dnumber) on delete set default

on update cascade);)

constraints On attributes

not null

check (dno > 0 and dno < 20)

unique

primary key (defines the PK, implicit not null)

Referential integrity constraints

Foreign keyforeign key(dno) references deparment(dnumber)

Cascadeforeign key(superssn) references Employee(ssn) on

update cascade

Alter table

alter table employee add email varchar(40);

alter table employee add constraint fkmr foreign key (dept) references

department(dnumber);

SQL - DML

Modifying the data

insert into …,

update ... set ... where ...,

delete from … where ...

Query on the data

select ... from ... where ...

insert

insert into employee values ('James','E','Borg','888665555','19371110','450 Stone, Houston,TX','M',55000,null,1);

Update

update employee

set salary = 25000

where ssn = ’123456789’

update employee

set salary = salary * 1.2

where salary >= 30000

Deleting data

Delete, deletes tuples from the database if the fulfill the where condition

Deleting a tuple with referentiel integritet constraints defined with cascade, can delete tuples in other tables

If there are no where condition, all tuples from at table are deleted

delete from department

delete

delete from employee

where ssn = ”123456789”

//delete departments which has no employees

delete from department

where dnumber not in (select dno from employee)

Select-From-Where Statements

SELECT <attributerlist>

FROM <tables>

WHERE <condition>

Single-Relation Query

Starts with the FROM clause.

Then the WHERE clause.

At last the attributes from the SELECT clause.

Algerbra operations?

* in SELECT clauses

SELECT *FROM EmployeeWHERE ssn = ‘123456789’;

Renaming Attributes

SELECT fname AS firstNameFROM employeeWHERE ssn = ‘123456789’

NULL Values

Tuples in SQL relations can have NULL as a value for one or more components.

Meaning depends on context. Two common cases:

Missing value : e.g., we know Joe’s Bar has some address, but we don’t know what it is.

Inapplicable : e.g., the value of attribute spouse for an unmarried person.

Comparing NULL’s to Values

The logic of conditions in SQL is really 3-valued logic: TRUE, FALSE, UNKNOWN.

When any value is compared with NULL, the truth value is UNKNOWN.

But a query only produces a tuple in the answer if its truth value for the WHERE clause is TRUE (not FALSE or UNKNOWN).

Use is or is not

select Fname, lname

from employee

where superssn is null

Instead of = or !=

Since sql considers each null value as being distinct from every other null value

Multirelation Queries

Interesting queries often combine data from more than one relation.

We can address several relations in one query by listing them all in the FROM clause.

Distinguish attributes of the same name by “<relation>.<attribute>”

Example

select fname

from employee, dependent

where fname = dependent_name

and ssn = essn;

Formal Semantics

Almost the same as for single-relation queries:

1. Start with the product of all the relations in the FROM clause.

2. Apply the selection condition from the WHERE clause.

3. Project onto the list of attributes and expressions in the SELECT clause.

Algebra operations

Explicit Tuple-Variables

Sometimes, a query needs to use two copies of the same relation.

Distinguish copies by following the relation name by the name of a tuple-variable, in the FROM clause.

It’s always an option to rename relations this way, even when not essential.

Example

Foreach employee select the name from the employee and his supervisor

select e.fname,e.lname, s.fname, s.lname

from employee e, employee swhere e.superssn = s.ssn;

Subqueries

A parenthesized SELECT-FROM-WHERE statement (subquery) can be used as a value in a number of places, including FROM and WHERE clauses.

Example: in place of a relation in the Where clause, we can place another query, and then query its result.

Subqueries That Return One Tuple

If a subquery is guaranteed to produce one tuple, then the subquery can be used as a value.

Usually, the tuple has one component.

Also typically, a single tuple is guaranteed by keyness of attributes.

A run-time error occurs if there is no tuple or more than one tuple.

Example

From Sells(bar, beer, price), find the bars that serve Miller for the same price Joe charges for Bud.

Two queries would surely work:

1. Find the price Joe charges for Bud.

2. Find the bars that serve Miller at that price.

Query + Subquery Solution

SELECT bar

FROM Sells

WHERE beer = ‘Miller’ AND

price = (SELECT price

FROM Sells

WHERE bar = ‘Joe’’s Bar’

AND beer = ‘Bud’);The price atwhich Joesells Bud

The IN Operator

<tuple> IN <relation> is true if and only if the tuple is a member of the relation.<tuple> NOT IN <relation> means the

opposite.IN-expressions can appear in WHERE

clauses.The <relation> is often a subquery.

Example

From Beers(name, manf) and Likes(drinker, beer), find the name and manufacturer of each beer that Fred likes.

SELECT *

FROM Beers

WHERE name IN (SELECT beer

FROM Likes

WHERE drinker = ‘Fred’);The set ofbeers Fredlikes

The Exists Operator

EXISTS( <relation> ) is true if and only if the <relation> is not empty.

Being a boolean-valued operator, EXISTS can appear in WHERE clauses.

Example Query with EXISTS

select e.fname, e.lname

from employee e

where exists (select *

from dependent

where e.ssn = essn and e.sex = sex

and e.fname = dependent_name)

Important Points

Two single quotes inside a string represent the single-quote (apostrophe).

Conditions in the WHERE clause can use AND, OR, NOT

SQL is case-insensitive. In general, upper and lower case characters are the same, except inside quoted strings.

Patterns

WHERE clauses can have conditions in which a string is compared with a pattern, to see if it matches.

General form: <Attribute> LIKE <pattern> or <Attribute> NOT LIKE <pattern>

Pattern is a quoted string with % = ‘any string’; _ = “any character.”

Exercises

8.13 (Company databasen)

8.14 (Company databasen

FlereSQLØvelser (word doc. )

8.10 and 8.11 create an database and queries on the database