sql zadacha posleden proekt 15.12

CREATE TABLE CS(id INTEGER PRIMARY KEY, username TEXT, STATUS TEXT, backgroundid INTEGER, total_friends REAL, gamemodeid INTEGER, active_status TEXT);

CREATE TABLE Background(id INTEGER PRIMARY KEY, background TEXT);
INSERT INTO Background(id, background)
    VALUES (1, "Dark");
INSERT INTO Background(id, background)
    VALUES (2, "Light");

CREATE TABLE Gamemode(id INTEGER PRIMARY KEY, gamemode TEXT);
INSERT INTO GAMEMODE(id, gamemode)
    VALUES(1, "Competitive");
INSERT INTO GAMEMODE(id, gamemode)
    VALUES(2, "Casual");
INSERT INTO GAMEMODE(id, gamemode)
    VALUES(3, "Normal");

CREATE TABLE total_friends(id INTEGER PRIMARY KEY, total REAL);
INSERT INTO total_friends(id, total)
    VALUES(1, 5);

CREATE TABLE Active_Status(id INTEGER PRIMARY KEY,act_stat TEXT);
INSERT INTO Active_Status(id, act_stat)
    VALUES(1,"online");
INSERT INTO Active_Status(id, act_stat)
    VALUES(2,"away");
INSERT INTO Active_Status(id, act_stat)
    VALUES(3,"invisible");

INSERT INTO CS(id, username, STATUS, backgroundid, total_friends, gamemodeid, active_status)
    VALUES(1,"Bokata","igraq",1,5,1,1);
INSERT INTO CS(id, username, STATUS, backgroundid, total_friends, gamemodeid, active_status)
    VALUES(2,"Bojidar","cukam",2,5,2,2);
SELECT * FROM CS;

SELECT CS.id, CS.username,total_friends.total
FROM CS
JOIN total_friends;

UPDATE total_friends
SET total = 6;
/*sled update-a*/
SELECT CS.id, CS.username,total_friends.total
FROM CS
JOIN total_friends;

SELECT CS.id, CS.username,CS.STATUS,total_friends.total
FROM CS
JOIN total_friends;

UPDATE CS
SET STATUS = "ne igraq";
/*sled update-a*/
SELECT CS.id, CS.username,CS.STATUS,total_friends.total
FROM CS
JOIN total_friends;

/*DELETE*/
DELETE FROM CS WHERE username = "Bojidar";
SELECT * FROM CS;