-----------------------------------------CREATE DATABASE------

CREATE DATABASE MAGISTER;

USE MAGISTER;
-----------------------------------------CREATE TABLE------------

CREATE TABLE CAR
(CAR_ID INT PRIMARY KEY,
PROIZHOD CHAR(20),
MARKA CHAR(20),
BROI_VRATI  INT,
KUBATURA INT,
CVQT CHAR(10),
GODINA_PR INT,
CENA REAL);

CREATE TABLE KLIENT
(KLIENT_ID INT PRIMARY KEY,
IME CHAR(30),
GRAD CHAR(20) NULL,
BROI_KOLI INT(20),
CENA REAL NULL);

CREATE TABLE QUERY
(QUERY_ID INT PRIMARY KEY,
CAR_ID INT, CONSTRAINT FK_CAR_ID FOREIGN KEY(CAR_ID)
REFERENCES CAR(CAR_ID),
KLIENT_ID INT, CONSTRAINT FK_KLIENT_ID FOREIGN KEY(KLIENT_ID)
REFERENCES KLIENT(KLIENT_ID));

--------------------------------------ALTER TABLE----------------

ALTER TABLE KLIENT
ADD FIRMA CHAR(30);

ALTER TABLE KLIENT
MODIFY FIRMA CHAR(80);

ALTER TABLE KLIENT
DROP FIRMA;

--------------------------------------INSERT INTO----------------

INSERT INTO CAR
(CAR_ID,PROIZHOD,MARKA,BROI_VRATI,KUBATURA,CVQT,GODINA_PR,CENA)
VALUES(4589,'USA','DODGE',5,2.8,'CHERVEN',1982,5000);

INSERT INTO CAR
(CAR_ID,PROIZHOD,MARKA,BROI_VRATI,KUBATURA,CVQT,GODINA_PR,CENA)
VALUES(4590,'GERMANY','AUDI',5,3.0,'CHEREN',2000,14000);

INSERT INTO CAR
(CAR_ID,PROIZHOD,MARKA,BROI_VRATI,KUBATURA,CVQT,GODINA_PR,CENA)
VALUES(4591,'RUSSIA','LADA',5,1.5,'ZELEN',1977,500);

INSERT INTO CAR
(CAR_ID,PROIZHOD,MARKA,BROI_VRATI,KUBATURA,CVQT,GODINA_PR,CENA)
VALUES(4592,'GERMANY','OPEL',3,1.8,'LILAV',1999,3000);

INSERT INTO KLIENT
(KLIENT_ID,IME,GRAD,BROI_KOLI,CENA)
VALUES(1000,'BUNDY','CHICAGO',1,5000);

INSERT INTO KLIENT
(KLIENT_ID,IME,GRAD,BROI_KOLI,CENA)
VALUES(1001,'MARK','BERLIN',2,28000);

INSERT INTO KLIENT
(KLIENT_ID,IME,GRAD,BROI_KOLI,CENA)
VALUES(1002,'ROMAN','MOSKVA',10,5000);

INSERT INTO QUERY
(QUERY_ID,KLIENT_ID,CAR_ID)
VALUES(1,1002,4590);

INSERT INTO QUERY
(QUERY_ID,KLIENT_ID,CAR_ID)
VALUES(2,1000,4592);

-------------------------------------------UPDATE---------------

UPDATE  CAR
SET  CENA=1000
WHERE MARKA='LADA';

UPDATE  KLIENT
SET  BROI_KOLI=5,CENA=100000
WHERE IME='MARK';

UPDATE  QUERY
SET  KLIENT_ID=1001,CAR_ID=4589
WHERE QUERY_ID=2;

-------------------------------------------SELECT----------------
SELECT * FROM CAR;

SELECT * FROM KLIENT;

SELECT * FROM QUERY;

SELECT PROIZHOD,CENA
FROM CAR
WHERE PROIZHOD='USA';

SELECT PROIZHOD,CENA
FROM CAR
WHERE PROIZHOD<>'CENA';

SELECT  BROI_VRATI,CENA
FROM CAR
WHERE CENA>2
AND BROI_VRATI<5;

SELECT KLIENT_ID,IME
FROM KLIENT
WHERE KLIENT_ID BETWEEN 1001 AND 2000;

SELECT*
FROM CAR
WHERE BROI_VRATI IS NULL;

SELECT CVQT,MARKA
FROM CAR
WHERE CAR_ID IN
(SELECT CAR_ID
FROM CAR
WHERE CVQT='ZELEN');

SELECT * FROM CAR
WHERE EXISTS
(SELECT * FROM QUERY
WHERE QUERY.CAR_ID=CAR.CAR_ID);

--------------------------------------------DELETE----------------

DELETE FROM QUERY
WHERE QUERY_ID>0;

DELETE FROM CAR
WHERE MARKA='LADA';

DELETE FROM KLIENT
WHERE KLIENT_ID=1002;

--------------------------------------------DROP------------------

DROP TABLE QUERY;

DROP TABLE CAR;

DROP TABLE KLIENT;

DROP DATABASE MAGISTER;