Slides from the SQL Lecture

I will probably show only some of these slides.

Example 1

SQL> select * from member;                   SQL> select * from participates;
4 rows selected                                 5 rows selected
-------------------------------			---------------------
| MEMBER_NO | NAME |TELEPHONE |                 |  MEMBER   |SECTION|
-------------------------------                 ---------------------
|     2     |Stina |  282677  |                 |     1     |   A   |
|     3     |Saddam|  260088  |                 |     1     |   B   |
|     4     |Lotta |  174590  |                 |     1     |   C   |
|     1     | Olle |  260088  |                 |     2     |   C   |
-------------------------------                 |     3     |   A   |
                                                ---------------------
SQL> select * from section;
3 rows selected
-------------------------------------
|SECTION_CODE|   NAME   |  LEADER   |
-------------------------------------
|     A      | Bowling  |     4     |
|     C      | Swimming |     2     |
|     B      |Kickboxing|     4     |
-------------------------------------

Example 2

SQL> select name from member;

4 rows selected
--------
| NAME |
--------
|Stina |
|Saddam|
|Lotta |
| Olle |
--------

Example 3

SQL> select * from member
where name = 'Lotta';

1 row selected
-------------------------------
| MEMBER_NO | NAME |TELEPHONE |
-------------------------------
|     4     |Lotta |  174590  |
-------------------------------

Example 4

SQL> select * from member
where first_name = 'Lotta';

[interbase.interclient] Dynamic SQL Error
SQL error code = -206
Column unknown
FIRST_NAME


SQL> select * from memmber
where first_name = 'Lotta';

[interbase.interclient] Dynamic SQL Error
SQL error code = -204
Table unknown
MEMMBER

Example 5

SQL> select name, telephone from member
where name like 'S%';

2 rows selected
-------------------
| NAME |TELEPHONE |
-------------------
|Stina |  282677  |
|Saddam|  260088  |
-------------------

Example 6

SQL> select name, telephone from member
where name = 'S%';

No rows selected
-------------------
| NAME |TELEPHONE |
-------------------

Example 7

SQL> select * from member
where name like 'S%' and member_no <= 2
or telephone = '174590';

2 rows selected
-------------------------------
| MEMBER_NO | NAME |TELEPHONE |
-------------------------------
|     2     |Stina |  282677  |
|     4     |Lotta |  174590  |
-------------------------------

Example 8

SQL> select * from member
where name like 'S%' and (member_no <= 2
or telephone = '174590');

1 row selected
-------------------------------
| MEMBER_NO | NAME |TELEPHONE |
-------------------------------
|     2     |Stina |  282677  |
-------------------------------

Example 9

SQL> select * from section;

3 rows selected
-------------------------------------
|SECTION_CODE|   NAME   |  LEADER   |
-------------------------------------
|     A      | Bowling  |     4     |
|     C      | Swimming |     2     |
|     B      |Kickboxing|     4     |
-------------------------------------


SQL> select * from member;

4 rows selected
-------------------------------
| MEMBER_NO | NAME |TELEPHONE |
-------------------------------
|     2     |Stina |  282677  |
|     3     |Saddam|  260088  |
|     4     |Lotta |  174590  |
|     1     | Olle |  260088  |
-------------------------------

Example 10

SQL> select leader from section
where name = 'Bowling';

1 row selected
-------------
|  LEADER   |
-------------
|     4     |
-------------

Example 11

SQL> select name from member
where member_no = 4;

1 row selected
--------
| NAME |
--------
|Lotta |
--------

Example 12

SQL> select name from member
where member_no = (select leader from section
where name = 'Bowling');

1 row selected
--------
| NAME |
--------
|Lotta |
--------

Example 13

SQL> select * from section, member;

12 rows selected
-------------------------------------------------------------------
|SECTION_CODE|   NAME   |  LEADER   | MEMBER_NO | NAME |TELEPHONE |
-------------------------------------------------------------------
|     A      | Bowling  |     4     |     2     |Stina |  282677  |
|     C      | Swimming |     2     |     2     |Stina |  282677  |
|     B      |Kickboxing|     4     |     2     |Stina |  282677  |
|     A      | Bowling  |     4     |     3     |Saddam|  260088  |
|     C      | Swimming |     2     |     3     |Saddam|  260088  |
|     B      |Kickboxing|     4     |     3     |Saddam|  260088  |
|     A      | Bowling  |     4     |     4     |Lotta |  174590  |
|     C      | Swimming |     2     |     4     |Lotta |  174590  |
|     B      |Kickboxing|     4     |     4     |Lotta |  174590  |
|     A      | Bowling  |     4     |     1     | Olle |  260088  |
|     C      | Swimming |     2     |     1     | Olle |  260088  |
|     B      |Kickboxing|     4     |     1     | Olle |  260088  |
-------------------------------------------------------------------

Example 14

SQL> select * from section, member
where leader = member_no;

3 rows selected
-------------------------------------------------------------------
|SECTION_CODE|   NAME   |  LEADER   | MEMBER_NO | NAME |TELEPHONE |
-------------------------------------------------------------------
|     C      | Swimming |     2     |     2     |Stina |  282677  |
|     A      | Bowling  |     4     |     4     |Lotta |  174590  |
|     B      |Kickboxing|     4     |     4     |Lotta |  174590  |
-------------------------------------------------------------------

Example 15

SQL> select * from section, member
where leader = member_no
and section.name = 'Bowling';

1 row selected
-------------------------------------------------------------------
|SECTION_CODE|   NAME   |  LEADER   | MEMBER_NO | NAME |TELEPHONE |
-------------------------------------------------------------------
|     A      | Bowling  |     4     |     4     |Lotta |  174590  |
-------------------------------------------------------------------

Example 16

SQL> select member.name from section, member
where leader = member_no
and section.name = 'Bowling';

1 row selected
--------
| NAME |
--------
|Lotta |
--------

Example 17

SQL> select member_no
from member
where name = 'Olle';

1 row selected
-------------
| MEMBER_NO |
-------------
|     1     |
-------------

Example 18

SQL> select section from participates
where member = 1;

3 rows selected
---------
|SECTION|
---------
|   A   |
|   B   |
|   C   |
---------

Example 19

SQL> select name from section
where section_code = 'A'
or section_code = 'B'
or section_code = 'C';

3 rows selected
------------
|   NAME   |
------------
| Bowling  |
| Swimming |
|Kickboxing|
------------

Example 20

SQL> select name from section
where section_code in ('A', 'B', 'C');

3 rows selected
------------
|   NAME   |
------------
| Bowling  |
| Swimming |
|Kickboxing|
------------

Example 21

SQL> select name from section
where section_code = (select section from participates
where member = 1);

[interbase.interclient] multiple rows in singleton select


SQL> select name from section
where section_code = (select section from participates
where member = (select member_no from member
where name = 'Olle'));

[interbase.interclient] multiple rows in singleton select

Example 22

SQL> select name from section
where section_code in (select section from participates
where member = (select member_no from member
where name = 'Olle'));

3 rows selected
------------
|   NAME   |
------------
| Bowling  |
| Swimming |
|Kickboxing|
------------

Example 23

SQL> select name from section, participates, member
where section_code = section
and member = member_no
and name = 'Olle';

3 rows selected
--------
| NAME |
--------
| Olle |
| Olle |
| Olle |
--------

Example 24

SQL> select section.name
from section, participates, member
where section_code = section
and member = member_no
and member.name = 'Olle';

3 rows selected
------------
|   NAME   |
------------
| Bowling  |
|Kickboxing|
| Swimming |
------------

Example 25

SQL> select * from member
where member_no in (select member from participates);

3 rows selected
-------------------------------
| MEMBER_NO | NAME |TELEPHONE |
-------------------------------
|     2     |Stina |  282677  |
|     3     |Saddam|  260088  |
|     1     | Olle |  260088  |
-------------------------------

Example 26

4 SQL> select * from member
where member_no not in (select member from participates);

1 row selected
-------------------------------
| MEMBER_NO | NAME |TELEPHONE |
-------------------------------
|     4     |Lotta |  174590  |
-------------------------------

Example 27

SQL> select max(member_no) from member;

1 row selected
-------------
|    MAX    |
-------------
|     4     |
-------------

Example 28

SQL> select * from member
where member_no = max(member_no);

[interbase.interclient] Dynamic SQL Error
SQL error code = -104
Invalid aggregate reference

Example 29

SQL> select * from member
where member_no = (select max(member_no) from member);

1 row selected
-------------------------------
| MEMBER_NO | NAME |TELEPHONE |
-------------------------------
|     4     |Lotta |  174590  |
-------------------------------

Example 30

SQL> select max(member_no) from member 
where name like 'S%';

1 row selected
-------------
|    MAX    |
-------------
|     3     |
-------------

Example 31

SQL> select section.name, member.name
from section, member
where leader = member_no;

3 rows selected
-------------------
|   NAME   | NAME |
-------------------
| Swimming |Stina |
| Bowling  |Lotta |
|Kickboxing|Lotta |
-------------------

Example 32

SQL> create view leadership
as select section.name, member.name
from section, member
where leader = member_no;

[interbase.interclient] unsuccessful metadata update
STORE RDB$RELATION_FIELDS failed
attempt to store duplicate value (visible to active transactions) in unique index "RDB$INDEX_15"

Example 33

SQL> create view leadership (section_name, member_name)
as select section.name, member.name
from section, member
where leader = member_no;

Ok

Example 34

SQL> select * from leadership;

3 rows selected
--------------------------
|SECTION_NAME|MEMBER_NAME|
--------------------------
|  Swimming  |   Stina   |
|  Bowling   |   Lotta   |
| Kickboxing |   Lotta   |
--------------------------


SQL>  select MEMBER_NAME
from leadership
where section_name = 'Bowling';

1 row selected
-------------
|MEMBER_NAME|
-------------
|   Lotta   |
-------------

Example 35

SQL> select * from member;

4 rows selected
-------------------------------
| MEMBER_NO | NAME |TELEPHONE |
-------------------------------
|     2     |Stina |  282677  |
|     3     |Saddam|  260088  |
|     4     |Lotta |  174590  |
|     1     | Olle |  260088  |
-------------------------------


SQL> insert into member
values (4, 'Isaac', 281000);

[interbase.interclient] violation of PRIMARY or UNIQUE KEY constraint "INTEG_16" on table "MEMBER"

Example 36

create table member
(member_no integer not null,
name varchar(6),
telephone varchar(10),
primary key (member_no));
Ok

Example 37

SQL> insert into member
values (7, 'Isaac', 281000);

Ok (1 row affected)


SQL> select * from member;

5 rows selected
-------------------------------
| MEMBER_NO | NAME |TELEPHONE |
-------------------------------
|     2     |Stina |  282677  |
|     3     |Saddam|  260088  |
|     4     |Lotta |  174590  |
|     1     | Olle |  260088  |
|     7     |Isaac |  281000  |
-------------------------------

Example 38

4 SQL> insert into member values  (8, 'Nelson');

[interbase.interclient] Dynamic SQL Error
SQL error code = -804
Count of columns does not equal count of values

Example 39

SQL> insert into member (member_no, name)
values  (8, 'Nelson');

Ok (1 row affected)


SQL> select * from member;

6 rows selected
-------------------------------
| MEMBER_NO | NAME |TELEPHONE |
-------------------------------
|     2     |Stina |  282677  |
|     3     |Saddam|  260088  |
|     4     |Lotta |  174590  |
|     1     | Olle |  260088  |
|     7     |Isaac |  281000  |
|     8     |Nelson|          |
-------------------------------

Example 40

SQL> select * from member where telephone is null; 

1 row selected
-------------------------------
| MEMBER_NO | NAME |TELEPHONE |
-------------------------------
|     8     |Nelson|          |
-------------------------------

Example 41

SQL>  select * from member where telephone = '';

No rows selected
-------------------------------
| MEMBER_NO | NAME |TELEPHONE |
-------------------------------

Example 42

SQL> commit;

Transaction committed

Example 43

SQL> delete from member where name = 'Isaac';

Ok (1 row affected)


SQL> select * from member;

5 rows selected
-------------------------------
| MEMBER_NO | NAME |TELEPHONE |
-------------------------------
|     2     |Stina |  282677  |
|     3     |Saddam|  260088  |
|     4     |Lotta |  174590  |
|     1     | Olle |  260088  |
|     8     |Nelson|          |
-------------------------------

Example 44

SQL> rollback;

Transaction aborted


SQL>  select * from member;

6 rows selected
-------------------------------
| MEMBER_NO | NAME |TELEPHONE |
-------------------------------
|     2     |Stina |  282677  |
|     3     |Saddam|  260088  |
|     4     |Lotta |  174590  |
|     1     | Olle |  260088  |
|     7     |Isaac |  281000  |
|     8     |Nelson|          |
-------------------------------

Example 45

SQL> delete from member where name = 'Lotta';

[interbase.interclient] violation of FOREIGN KEY constraint "INTEG_36" on table "SECTION"

Example 46

alter table section add foreign key (leader) references member (member_no);

Example 47

SQL> update section
set name = 'Skydiving'
where name = 'Bowling';

Ok (1 row affected)


SQL> select * from section;

3 rows selected
-------------------------------------
|SECTION_CODE|   NAME   |  LEADER   |
-------------------------------------
|     A      |Skydiving |     4     |
|     C      | Swimming |     2     |
|     B      |Kickboxing|     4     |
-------------------------------------

Example 48

create table member
(member_no integer not null,
name varchar(6),
telephone varchar(10),
primary key (member_no));

create table section
(section_code character(1) not null,
name varchar(10),
leader integer,
primary key (section_code));

create table participates
(member integer not null,
section character(1) not null,
primary key (member, section));

alter table section add foreign key (leader) references member (member_no);
alter table participates add foreign key (member) references member (member_no);
alter table participates add foreign key (section) references section (section_code);

Example 49

insert into member (member_no, name, telephone)
values (2, 'Stina', '282677');
insert into member (member_no, name, telephone)
values (3, 'Saddam', '260088');
insert into member (member_no, name, telephone)
values (4, 'Lotta', '174590');
insert into member (member_no, name, telephone)
values (1, 'Olle', '260088');

insert into section (section_code, name, leader)
values ('A', 'Bowling', 4);
insert into section (section_code, name, leader)
values ('C', 'Swimming', 2);
insert into section (section_code, name, leader)
values ('B', 'Kickboxing', 4);

insert into participates (member, section)
values (1, 'A');
insert into participates (member, section)
values (1, 'B');
insert into participates (member, section)
values (1, 'C');
insert into participates (member, section)
values (2, 'C');
insert into participates (member, section)
values (3, 'A');

Example 50

SQL> select * from employee;

7 rows selected
------------------------------------------------------------------
|  NUMBER   | NAME |TELEPHONE |  SALARY   |   BOSS    |DEPARTMENT|
------------------------------------------------------------------
|     2     |Stina |   2677   |   30000   |     0     |    H     |
|     3     |Saddam|   1088   |   22000   |     2     |    S     |
|     4     |Lotta |   4590   |   28000   |     2     |    H     |
|     1     | Olle |   2688   |   20000   |     3     |    S     |
|     8     |Maria |   2690   |   25000   |     4     |    C     |
|     9     |Ulrik |   2698   |   26000   |     8     |    C     |
|    10     |Petter|   2645   |   22000   |     8     |    C     |
------------------------------------------------------------------


SQL> select * from department;

3 rows selected
------------------------------------------
|DEPARTMENT_CODE|    NAME    |RESPONSIBLE|
------------------------------------------
|       H       |Headquarters|     2     |
|       S       |  Security  |     3     |
|       C       |  Computer  |     8     |
------------------------------------------

Example 51

SQL> select department.name, employee.name
from department, employee
where department.responsible = employee.number;

3 rows selected
---------------------
|    NAME    | NAME |
---------------------
|Headquarters|Stina |
|  Security  |Saddam|
|  Computer  |Maria |
---------------------

Example 52

SQL> select avg(salary)
from employee;

1 row selected
-------------
|    AVG    |
-------------
|   24714   |
-------------

Example 53

SQL> select boss, avg(salary)
from employee
group by boss;

5 rows selected
-------------------------
|   BOSS    |    AVG    |
-------------------------
|     2     |   25000   |
|     3     |   20000   |
|     4     |   25000   |
|     8     |   24000   |
|     0     |   30000   |
-------------------------

Example 54

SQL> select department, avg(salary)
from employee
group by department;

3 rows selected
------------------------
|DEPARTMENT|    AVG    |
------------------------
|    C     |   24333   |
|    H     |   29000   |
|    S     |   21000   |
------------------------

Example 55

SQL> select department, avg(salary)
from employee
where salary > 20000
group by department;

3 rows selected
------------------------
|DEPARTMENT|    AVG    |
------------------------
|    C     |   24333   |
|    H     |   29000   |
|    S     |   22000   |
------------------------

Example 56

SQL> select department, avg(salary)
from employee
group by department
having avg(salary) > 22000;

2 rows selected
------------------------
|DEPARTMENT|    AVG    |
------------------------
|    C     |   24333   |
|    H     |   29000   |
------------------------

Example 57

SQL> select department, avg(salary)
from employee
where salary > 20000
group by department
having avg(salary) > 22000;

2 rows selected
------------------------
|DEPARTMENT|    AVG    |
------------------------
|    C     |   24333   |
|    H     |   29000   |
------------------------

Example 58

SQL> select name, salary, name, salary
from employee, employee
where boss = number
and salary > salary;

No rows selected
---------------------------------------
| NAME |  SALARY   | NAME |  SALARY   |
---------------------------------------

Example 59

SQL> select proletarian.name, proletarian.salary, boss.name, boss.salary 
from employee proletarian, employee boss
where proletarian.boss = boss.number
and proletarian.salary > boss.salary;

1 row selected
---------------------------------------
| NAME |  SALARY   | NAME |  SALARY   |
---------------------------------------
|Ulrik |   26000   |Maria |   25000   |
---------------------------------------

Example 60

create table employee
(number integer not null,
name varchar(6),
telephone varchar(10),
salary integer,
boss integer,
department character(1),
primary key (number));

create table department
(department_code character(1) not null,
name varchar(12),
responsible integer,
primary key (department_code));

alter table employee add constraint employee_to_boss
foreign key (boss) references employee (number);
alter table employee add constraint employee_to_dept
foreign key (department) references department (department_code);
alter table department add constraint dept_to_responsible
foreign key (responsible) references employee (number);

Example 61

aste17.ida.liu.se tompa slides >>> ty Example\ 61
insert into employee (number, name, telephone, salary, department)
values (2, 'Stina', '2677', 30000, 'H');
insert into employee (number, name, telephone, salary, boss, department)
values (3, 'Saddam', '1088', 22000, 2, 'S');
insert into employee (number, name, telephone, salary, boss, department)
values (4, 'Lotta', '4590', 28000, 2, 'H');
insert into employee (number, name, telephone, salary, boss, department)
values (1, 'Olle', '2688', 20000, 3, 'S');
insert into employee (number, name, telephone, salary, boss, department)
values (8, 'Maria', '2690', 25000, 4, 'C');
insert into employee (number, name, telephone, salary, boss, department)
values (9, 'Ulrik', '2698', 26000, 8, 'C');
insert into employee (number, name, telephone, salary, boss, department)
values (10, 'Petter', '2645', 22000, 8, 'C');

insert into department (department_code, name, responsible)
values ('H', 'Headquarters', 2);
insert into department (department_code, name, responsible)
values ('S', 'Security', 3);
insert into department (department_code, name, responsible)
values ('C', 'Computer', 8);
aste17.ida.liu.se tompa slides >>> 


Thomas Padron-McCarthy (e-post: tpm@ida.liu.se), 6 september 1999