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