SQL CODE.txt

//create courses

create table courses(
	CID		varchar(4) not null primary key,
	title		varchar(20)not null,
	entry_year	int not null,
	course_duration	int not null,
);

//create modules

create table modules(
	MID		varchar(6) not null primary key,
	title		varchar(20) not null,
	descr	varchar(100),
	CID		varchar(4) foreign key references courses(CID)
);

//create books

CREATE TABLE Books(
	BID		varchar(4)  not null primary key,
	title		varchar(15) not null,
	first_name	varchar(15),
	surname 	varchar(15) not null,
	publisher	varchar(10),
	year		int,
	MID		varchar(6) foreign key references Modules(MID),
	keyword 	varchar(100) not null,
);

//all courses

Select *
From Courses
group by Cid

//all modules

Select * 
from Modules
group by Mid

//all books

Select * 
from Books
group by Bid

//specific course

Select *
from courses
where CID = $cid

//specific module

Select *
from Modules 
where mid = $mid

//specific book

select * 
from books 
where bid = $bid

//modules from course

Select Modules.Mid,Modules.title,modules.descr,modules.cid
from Modules
left join courses
on Modules.CID = $cid
order by modules.mid

//books from module

select books.bid,books.title,books.firstname,books.surname,books.publisher,books.year
from books 
left join module
on books_mid = $mid
order by books.bid

//books from module from course

select books.bid,books.title,books.first_name,books.surname,books.publisher,books.year
from books 
left join modules
left join courses
on modules.cid =courses.cid 
on books.MID =modules.mid
order by bid

//add to courses

insert into Courses
Values($CID,$title,$year,$duration)

//add to books

insert into books
Values($bid,$title,$firstName,$surname,$publisher,$year,$MID,$keyword);

//add to modules

insert into modules
Values($MID,$title,$desc,$CID);

//edit course

update course 
set title = $title, year =$year, duration = $duration
where cid = $cid;

//edit book

update books 
set title = $title, firstname = $firstname, surname = $surname, publisher = $publiser, year =$year, mid = $MID,keyword = $keyword
where BID = $BID;

//edit module

update module
set title = $title, description = $desc, cid = $CID
where mid=$mid;

//remove from courses

delete from courses 
where cid = $cid;

//remove from books

delete from books
where bid= $bid;

//remove from module

delete from module
where mid = $mid;