1 / 35

Onsdag

Onsdag. The concepts in a relation data model SQL DDL DML. Concepts. create table Employee (fname varchar(15) not null, minit char, lname varchar(15) not null, ssn char(9), bdate datetime, address varchar(30),

brigit
Download Presentation

Onsdag

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Onsdag The concepts in a relation data model SQL DDL DML

  2. Concepts

  3. 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));

  4. Createtable 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); )

  5. constraints • On attributes • not null • check (dno > 0 and dno < 20) • unique • primary key (defines the PK, implicit not null)

  6. Referentialintegrityconstraints • Foreignkey • foreignkey(dno) references deparment(dnumber) • Cascade • foreignkey(superssn) references Employee(ssn) onupdatecascade

  7. Alter table altertableemployeeaddemailvarchar(40); • altertableemployee • addconstraintfkmrforeignkey (dept) references department(dnumber);

  8. SQL - DML • Modifying the data • insertinto …, • update ... set ... where ..., • delete from … where ... • Query on the data • select ... from ... where ...

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

  10. Update updateemployee setsalary = 25000 wheressn = ’123456789’ updateemployee setsalary = salary * 1.2 wheresalary >= 30000

  11. Deleting data • Delete, deletestuples from the database if the fulfill the wherecondition • Deleting a tuplewith referentiel integritet constraintsdefinedwithcascade, candeletetuples in othertables • If therearenowherecondition, all tuples from at tablearedeleted • delete from department

  12. delete delete fromemployee wheressn = ”123456789” //deletedepartmentswhich has noemployees delete fromdepartment wherednumber not in (selectdno from employee)

  13. Select-From-Where Statements SELECT <attributerlist> FROM <tables> WHERE <condition>

  14. Single-Relation Query • Starts with the FROM clause. • Then the WHERE clause. • At last the attributes from the SELECT clause. • Algerbra operations?

  15. * in SELECT clauses SELECT * FROM Employee WHERE ssn = ‘123456789’;

  16. Renaming Attributes SELECT fname AS firstName FROM employee WHERE ssn = ‘123456789’

  17. 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.

  18. 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).

  19. Use is or is not selectFname, lname from employee wheresuperssn is null Instead of = or != Sincesqlconsiderseachnullvalue as beingdistinct from everyothernullvalue

  20. 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>”

  21. Example select fname from employee, dependent where fname = dependent_name and ssn = essn;

  22. Formal Semantics • Almost the same as for single-relation queries: • Start with the product of all the relations in the FROM clause. • Apply the selection condition from the WHERE clause. • Project onto the list of attributes and expressions in the SELECT clause. • Algebra operations

  23. 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.

  24. 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 s where e.superssn = s.ssn;

  25. 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.

  26. 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.

  27. 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: • Find the price Joe charges for Bud. • Find the bars that serve Miller at that price.

  28. The price at which Joe sells Bud Query + Subquery Solution SELECT bar FROM Sells WHERE beer = ‘Miller’ AND price = (SELECT price FROM Sells WHERE bar = ‘Joe’’s Bar’ AND beer = ‘Bud’);

  29. 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.

  30. The set of beers Fred likes 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’);

  31. 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.

  32. Example Query with EXISTS • selecte.fname, e.lname • from employee e • whereexists (select * • from dependent • where e.ssn = essn and e.sex = sex • and e.fname = dependent_name)

  33. 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.

  34. 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.”

  35. Exercises 8.13 (Company databasen) 8.14 (Company databasen FlereSQLØvelser(worddoc. ) • 8.10 and 8.11 create an database and querieson the database

More Related