sql

create table USER(
    Id int NOT NULL PRIMARY KEY,
    User_Number int,
    User_Email varchar(256),
    System_Id int references SYSTEM(Id)
);
create table USER_ACCESS(
    Id int NOT NULL PRIMARY KEY,
    User_Id int references USER(Id),
    Access_Id int references ACCESS(Id),
    Granted date
);
create table SYSTEM(
    Id int NOT NULL PRIMARY KEY,
    System_name varchar(30)
);
create table ACCESS(
    Id int NOT NULL PRIMARY KEY,
    Access_Name varchar(30),
    Access_Level int,
    Description carchar(512)
);

insert into SYSTEM values(1, 'systemik');
insert into SYSTEM values(2, 'kolejny');
insert into SYSTEM values(3, 'kolabcejny');
insert into SYSTEM values(4, 'kolejabcny');

insert into ACCESS values(11, 'accessName1', 1, 'description1');
insert into ACCESS values(12, 'accessName2', 1, 'description2');

insert into USER values(11, 1, 'cokolwiek1', 1);
insert into USER values(12, 2, 'cokolwiek2', 2);
insert into USER values(13, 3, 'cokolwiek3', 2);

insert into USER_ACCESS values(13, 1, 11, '2011-03-03');
insert into USER_ACCESS values(14, 2, 12, '2011-03-03');
select * from USER;
select 'zad a';
select * from USER where System_Id = 2 order by User_Email;
select 'zad b';
select * from SYSTEM where System_Name like '%abc%';
select 'zad c';
insert into USER values(10, 113, 'pkowalski@email.com', 1);
select * from USER where Id = 10;
select 'zad d';
--delete from USER where (System_ID >= 1) and (System_Id <= 10);
select 'zad e';
select USER.Id, USER.User_Number, USER.User_Email, System.System_name from USER, SYSTEM
where USER.System_Id = SYSTEM.Id;
select 'zad f';
select 'Uzytkownik o numerze ' || u.User_Number || ' ma dostep o nazwie '|| a.Access_Name ||
    ' na poziomie: ' || a.Access_Level
from USER u, ACCESS a, USER_ACCESS ua
where ua.User_Id = u.Id and ua.Access_Id = a.Id;
select 'zad g';
create table ZAD as
select s.System_name as 'nazwa_systemu', count(u.Id) as 'ilosc_userow'
from USER u, SYSTEM s
where u.System_Id = s.Id
group by s.System_name;
select * from ZAD;
select 'zad h';
select * from ZAD where ilosc_userow > 5;