YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και...

39
- Relational Languages, SQL, QBE Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

Transcript of YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και...

Page 1: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 1

Κεφάλαιο 5Κεφάλαιο 5

SQL Data Definition Language

και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

Page 2: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 2

SQL Data DefinitionSQL Data Definition

The Data Definition Language (DDL) of SQL is used to CREATE, DROP and ALTER the descriptions of the relations in the database

CREATE TABLE DEPARTMENT

( DNumber integer keymember 0 not null,

DName varchar(12) keymember 1 not null,

MgrSSN char(9) references EMPLOYEE.SSN,

MgrSD char(9) );

In several (older) SQL systems, there is no support for REFERENCES (foreign key) and KEYMEMBER (key)

Page 3: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 3

SQL DDL - continuedSQL DDL - continued

The command:

DROP TABLE DEPENDENT

deletes the full table DEPENDENT and its definition (can no longer be used in queries, updates, etc.)

The command:

ALTER TABLE EMPLOYEE ADD JOB VARCHAR(15)

adds a new attribute, named JOB, in relation EMPLOYEE

– All values for this new attribute will initially be NULL – Ôhey can be changed in each tuple with the UPDATE command.

Page 4: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 4

Creating Indexes in SQLCreating Indexes in SQL

In most cases, a base relation corresponds to a stored file The CREATE INDEX command is used to specify an index

which always has a name (index name)CREATE INDEX LN_INDEX on EMPLOYEE (Name);

To specify a key constraint on the indexing attribute (or combination of attributes) the keyword UNIQUE is usedCREATE UNIQUE INDEX S_IND on EMPLOYEE (SSN);

To specify that an index is a clustering index, the keyword CLUSTER is used CREATE INDEX D_IN on EMPLOYEE(DNumber) CLUSTER;

In most DBMSs, a variation of the B+-tree is used as the implementation data structure for indexes

Page 5: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 5

Relational Views and SQLRelational Views and SQL

ExternalSchema 1

ExternalSchema 2

ExternalSchema N.....

Conceptual Schema

The three-level database architecture implies that different users see different external schemas - this is accomplished with the concept of VIEW in the relational model.

InternalSchema

RELATIONALVIEWS

RELATIONALDATABASESCHEMA (RELATIONS)

Page 6: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 6

Relational Views - DefinitionRelational Views - Definition

A VIEW is a relation which is not part of the conceptual schema (the base relations) but is made visible to the user as a virtual relation– A user is not aware if a relation is a view or a base relation– A view is not stored (materialized) in the database – The contents of the view are determined by its stored definition as

a function of the contents of the stored database– A view is defined on base tables or other views via a query– Queries and updates on views are translated into queries and

updates on their defining base relations – There are no limitations on querying a view– Only few updates on views are allowed– A view changes dynamically with the database

Page 7: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 7

Relational Views - FormatRelational Views - Format

CREATE VIEW table_name

[ ( view_column_list ) ]

AS query_expression

[ WITH CHECK OPTION ]

V1: Project workers

CREATE VIEW PROJWORKER(EName, Address, Project)

AS select Name, Address, PName from EMPLOYEE, PROJECT, WORKS_ON where PROJECT.PNumber = WORKS_ON.PNumber and WORKS_ON.SSN=EMPLOYEE.SSN ;

Page 8: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 8

Relational Views - Examples Relational Views - Examples

When the view_column_list is missing, the view attributes are inherited from the attribute names in the defining query

V2: Employees that are well-paid

CREATE VIEW BIGSHARKS

AS select *

from EMPLOYEE

where Salary > 50000 and BirthDate > 31.12.65;

– The user may ask questions directly on this new table called BIGSHARKS, which has exactly the same attribute names as the base relation EMPLOYEE

Page 9: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 9

View Examples (2) View Examples (2)

A view can have different units (attributes) than the conceptual schema

V3: Information data on departments (average salary, etc.)

CREATE VIEW DEPTINFO (Name, NoOfEmpl, AvgSalary)

AS select DName, COUNT(*), AVG(Salary)

from DEPARTMENT d, EMPLOYEE e

where d.DNumber = e.DNumber

group by DName ;

Page 10: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 10

View Examples (3)View Examples (3)

V4: Find the number of employees and average salary in the research department (also print the department name)

select *

from DEPTINFO

where Name = “research”

V5: Increase by 1 the number of employees in the research department

– Such an update is NOT allowed (why?)

Page 11: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 11

View ExecutionView Execution

As said before, the system translates retrievals (updates) on views into retrievals (updates) on the base relation(s). HOW is this done?– NAIVE Effort 1: Materialize the view (temporary table) and

then run the query on it.– NAIVE Effort 2: Maintain the view at all times -- that is,

keep it materialized (called, a snapshot) and whenever relevant updates on base relations are made then propagate them on the view. Run the query on the snapshot.

– CORRECT Effort: Modify the query by replacing the view with its defining query on base relations, and run the query on these base relations.

Page 12: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 12

View Execution ExampleView Execution Example

For instance, given the query:

select Name, Address

from BIGSHARKS

where DNumber = 5

we modify it accordingly and we execute the query:

select Name, Address

from EMPLOYEE

where Salary > 50000 and BirthDate > 31.12.65

and DNumber = 5

Page 13: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 13

Updating on ViewsUpdating on Views

In general, a view has restrictions on updates A view update is unambiguous if one update on the

base relations can accomplish the desired update effect on the view

OBSERVATIONS:– A view with a single defining table is updatable if the view

attributes contain the primary key– Views defined on multiple tables are generally not updatable– Views defined with aggregate functions are not updatable

View updating is still an open research area

Page 14: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 14

VIEWS - CommentsVIEWS - Comments

The “WITH CHECK OPTION” is used with updatable views to deal with the vanishing rows problemFor instance consider the view:

CREATE VIEW V AS select * from R where A = “X”

Now, assume the update:

UPDATE V set A = “Y”

The update will go on without a problem, but the rows (tuples) that were previously seen in V will disappear! (they no longer meet the WHERE-clause condition)

Views should be used whenever certain subqueries or expressions are used very frequently

Views are also used as a security and authorization mechanism

Page 15: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 15

Integrity ConstraintsIntegrity Constraints

The relational model allows for the specification of 6 types of integrity constraints: (a) entity integrity, (b) referential integrity, (c) primary key integrity, (d) column integrity, (e) domain integrity, and (f) user-defined.

Most commercial relational database systems do not enforce all integrity constraints (enforcing means that if an update violates a constraint, it is not allowed to execute)

We have seen how entity, referential and key integrity are specified - they are the inherent to the model constraints ---The other three constraints are explicitly specified. Modern DBMSs enforce many of these constraints, with notable example the system: Access 2.0 of Microsoft.

Page 16: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 16

Relational Model ConstraintsRelational Model Constraints

When enforcing (supporting) referential integrity, several actions must be taken by the DBMS. These are usually the cascade delete and the cascade update.

Some DBMSs support directly the cascade options (Access) Other DBMSs require from the user to write triggers

(procedures) in order to support the options (SQL Server) Column and Domain constraints are partially supported (not

always by strong-typing or other powerful mechanisms) User-defined constraints were introduced in the newest SQL

standard (SQL-92) with ASSERTIONS (stand-alone rules)

Page 17: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 17

Assertions -- ExamplesAssertions -- Examples

Every employee is either male or femaleASSERT gender ON EMPLOYEE: Sex=“m” OR Sex=“f”

Salaries have to be above 10000ASSERT salary_bound ON EMPLOYEE: Salary > 10000

If someone attempts to insert a tuple in EMPLOYEE such as:

insert into EMPLOYEE (select Name=“tom”,.., Salary=12000..)

the update is modified from the assert rule to:

insert into EMPLOYEE (select Name=“tom”,.., Salary=12000..

where 12000 > 10000 )

Page 18: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 18

Embedding SQL in a Programming LanguageEmbedding SQL in a Programming Language

DML commands are often embedded into host language programs (these commands amount to calls to the DBMS)

The embedded SQL statement is distinguished from the programming language statements with a special “prefix”

There are two ways to “embed” a DML:

1.- Extend the Programming Language (Compiler)

For example, RIGEL, MODULA-R, Gemstone, Orion, etc.)

These are called database programming languages

2.- Use a pre-processor for the DML statements

The pre-processor replaces all DML commands with calls to the host language, which are then executed (the usual case)

Page 19: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 19

Example with C as HostExample with C as Host

When SQL is embedded in C, all DML statements and all declarations of variables are preceded by a “##”.

## char ename[30]

## int salary;

...

prog () {

## sql companydb

## select ename=Name, salary=Salary from EMPLOYEE

## where DNumber=5

## {

/* process returns values */

## }

}

Page 20: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 20

Embedded SQLEmbedded SQL

SQL commands can be called from within a host language (e.g., C or COBOL) program.– SQL statements can refer to host variables (including

special variables used to return status).– Must include a statement to connect to the right database.– Requires compiler preprocessing– SQL relations are (multi-) sets of records, with no a priori

bound on the number of records. No such data structure in C.

– SQL supports a mechanism called a cursor to handle this.

Page 21: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 21

CursorsCursors

Can declare a cursor on a relation or query statement (which generates a relation).

Can open a cursor, and repeatedly fetch a tuple then move the cursor, until all tuples have been retrieved.

– Can use ORDER BY to control the order in which tuples are returned.

» Fields in ORDER BY clause must also appear in SELECT clause.

Can also modify/delete tuple pointed to by a cursor.

Page 22: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 22

Cursor that gets names of sailors who’ve reserved a red boat, in alphabetical order

Cursor that gets names of sailors who’ve reserved a red boat, in alphabetical order

DECLARE sinfo CURSOR FORSELECT S.sname

FROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ORDER BY S.sname;

FETCH 5 IN sinfo;

Note that it is illegal to replace S.sname by, say, S.sid in the ORDER BY clause! (Why?)

Can we add S.sid to the SELECT clause and replace S.sname by S.sid in the ORDER BY clause???

Page 23: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 23

Embedding SQL in C -- An ExampleEmbedding SQL in C -- An Example

char SQLSTATE[6];

EXEC SQL BEGIN DECLARE SECTION

char c_sname[20]; short c_minrating; float c_age;

EXEC SQL END DECLARE SECTION

c_minrating = random();

EXEC SQL DECLARE sinfo CURSOR FOR

SELECT S.sname, S.age FROM Sailors S

WHERE S.rating > :c_minrating

ORDER BY S.sname;

do {

EXEC SQL FETCH sinfo INTO :c_sname, :c_age;

printf(“%s is %d years old\n”, c_sname, c_age);

} while (SQLSTATE != ‘02000’);

EXEC SQL CLOSE sinfo;

Page 24: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 24

Database APIs: alternative to embeddingDatabase APIs: alternative to embedding

Rather than modify compiler, add library with database calls (API)– special procedures/objects– passes SQL strings from language, presents result sets in a

language-friendly way– Microsoft’s ODBC becoming C/C++ standard on Windows– Sun’s JDBC a Java equivalent– Supposedly DBMS-neutral

» a “driver” traps the calls and translates them into DBMS-specific code

» database can be across a network

Page 25: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 25

SQL API in Java (JDBC)SQL API in Java (JDBC)

Connection con = // connect DriverManager.getConnection(url, ”login", ”pass"); Statement stmt = con.createStatement(); // set up stmtString query = "SELECT COF_NAME, PRICE FROM COFFEES";ResultSet rs = stmt.executeQuery(query);try { // handle exceptions // loop through result tuples while (rs.next()) { String s = rs.getString("COF_NAME"); Float n = rs.getFloat("PRICE"); System.out.println(s + " " + n); }} catch(SQLException ex) { System.out.println(ex.getMessage () + ex.getSQLState () + ex.getErrorCode ());}

Page 26: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 26

Disadvantages of having Host Programming Languages

Disadvantages of having Host Programming Languages

Mixing Procedural and Declarative languages (the famous language mismatch problem)

Different pre-processors are required for different languages Relations are not treated as 1st class objects in the language

(e.g., cannot pass a relation as a parameter to a procedure) The host language may not support required constructs (e.g.,

FORTRAN does not support records) The alternative, that is database programming languages are

gaining ground, especially in object-oriented DBMSs

Page 27: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 27

Shortcomings of SQLShortcomings of SQL

SQL does not support strong typing, inheritance, etc. SQL tables are NOT relations (they allow duplicate rows) SQL tables can not be nested (be values of another table) SQL does not support several relational operators, like:

generalized restriction, division, forall SQL does not support 3-V logic (at least, not correctly) SQL does not support transition constraints SQL does not provide support for declaring (and using)

functional dependencies SQL does not support integrity constraints on views

DESPITE ALL THAT, SQL is the STANDARD LANGUAGE

Page 28: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 28

Query-by-Example (QBE)Query-by-Example (QBE)

A Query Language developed at IBM (by Moshe Zloof) found in a product called QMF (interface option to DB2)

Easier to use than SQL for the average end-user

(VISUAL and TWO-DIMENSIONAL) IDEA: The system provides the user with a skeleton of the

relations in the database and the user fills-in the tables with examples of what the answer should look like

QBE1: Find the names of employees in department 4

EMPLOYEESSN Name BDate Address Sex Salary SupSSN DNumber

P.__x 4

Page 29: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 29

QBE SummaryQBE Summary

PRINCIPLES– user does not have to remember names of columns/relations– in query specification, no rigid rules apply– based on domain relational calculus (columns are variables)– relationally complete specification of queries, but also support of

transitive closure

HOW IT WORKS– Tokens with an underscore preceding “_”, are VARIABLES– Tokens with no underscore are CONSTANT VALUES (they

signify an equality selection-condition)– The prefix “P.” is used to indicate which attributes will be printed

(it signifies a projection)

Page 30: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 30

QBE Summary - The ProcessQBE Summary - The Process

QUERY PROCESS– the user first chooses the tables needed to specify the query– templates of the chosen tables are displayed– user moves to the appropriate columns using special keys– example values (variables), constants, etc. are typed-in– other comparison operators than equality (automatic with a

constant value) must be typed-in (like, >, <, etc.)– more complex conditions are entered in a condition box– conditions in the same row, specify the Boolean AND– conditions in distinct rows, specify the Boolean OR

– negation (Boolean NOT) is specified by the symbol “ “

– JOINS are expressed by using common example values in multiple tables

Page 31: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 31

QBE -- ExamplesQBE -- Examples

QBE2: Find the names of employees who work in a department with a manager different than 3334

SSN Name BDate Address Sex Salary SupSSN DNumber

P.__x no

DNumber DName MgrSSN MgrSD

no

EMPLOYEE

DEPARTMENT

Page 32: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 32

QBE -- Examples (2)QBE -- Examples (2)

QBE3: Find names of employees who earn more than 30000 AND work in department 5

QBE4: Find names of employees who earn more than 30000 OR work in department 5

SSN Name BDate Address Sex Salary SupSSN DNumber

P.__x >30000 5

SSN Name BDate Address Sex Salary SupSSN DNumber

P. x 5

P. y >30000

Page 33: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 33

QBE -- Examples (3)QBE -- Examples (3)

QBE5: Find names and addresses of employees in department 4 who earn more than someone who works in department 5

SSN Name BDate Address Sex Salary SupSSN DNumber

P. tom P. kifis 10 4

20 5

EMPLOYEE

CONDITION BOX __10 > __20

Page 34: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 34

QBE -- Examples (4)QBE -- Examples (4)

QBE6: Find names of employees who earn more than their department manager

SSN Name BDate Address Sex Salary SupSSN DNumber

P. tom 10 6

4444 __

15

DNumber DName MgrSSN MgrSD

6 __10>__15

CONDITION BOXDEPARTMENT

EMPLOYEE

Page 35: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 35

QBE -- Examples (5)QBE -- Examples (5)

QBE7: Find all DEPENDENT tuples for employees who have a dependent born in 1972 while no other dependents were born before 1975

ESSN Name Sex BDate Relation

44

44 1972

44 <1975

DEPENDENT

P.

Page 36: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 36

QBE -- Examples (6)QBE -- Examples (6)

QBE8: Find the total salary of all the employees

EMPLOYEE

QBE9: Insert a new employee

EMPLOYEE

SSN Name BDate Address Sex Salary SupSSN DNumber

P.SUM.ALL. x

SSN Name BDate Address Sex Salary SupSSN DNumber

6669 thomas 8.2.65 ekali m 60000 9876 4I.

Page 37: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 37

QBE -- Examples (7)QBE -- Examples (7)

QBE10: Give a 15% salary increase to all employees in department 5

SSN Name BDate Address Sex Salary SupSSN DNumber

44 s 5

44 __

s*1.15

EMPLOYEE

U.

Page 38: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 38

A Peek at MS Access

Page 39: YV - Relational Languages, SQL, QBE 197 Κεφάλαιο 5 SQL Data Definition Language και άλλες γλώσσες Σχεσιακών Βάσεων Δεδομένων (QBE)

YV - Relational Languages, SQL, QBE 39

SummarySummary

QBE is an elegant, user-friendly query language based on DRC.

It is quite expressive (relationally complete, if the update features are taken into account).

Simple queries are especially easy to write in QBE, and there is a minimum of syntax to learn.

Has influenced the graphical query facilities offered in many products, including Borland’s Paradox and Microsoft’s Access.