project.sql

*CREATE TABLE PROOF
(
PID INT PRIMARY KEY,
PNAME varchar(20),
PLINK varchar(20),
TRAVEL_ALLOWANCE INT
);
CREATE TABLE ACHIEVEMENTS
(
AID INT PRIMARY KEY,
ANAME VARCHAR(20)
);

CREATE TABLE ACHIEVERS
(
ID INT PRIMARY KEY,
NAME VARCHAR(20),
SorF VARCHAR(20)
);
CREATE TABLE MAIN
(
ID INT ,
AID INT,
PID INT UNIQUE NOT NULL, 
PRIMARY KEY(ID,AID), 
FOREIGN KEY(PID) REFERENCES PROOF (PID),
FOREIGN KEY(AID) REFERENCES ACHIEVEMENTS(AID)
);

insert into PROOF(PID,PNAME,PLINK,TRAVEL_ALLOWANCE) values (200,'CERTIFICATE','78.4.4.3',5000);
insert into PROOF(PID,PNAME,PLINK,TRAVEL_ALLOWANCE) values (201,'EVENT_PASS','102.3.4.5',2000);
insert into PROOF(PID,PNAME,PLINK,TRAVEL_ALLOWANCE) values (202,'PRIZE','201.4.5.3',2500);
insert into PROOF(PID,PNAME,PLINK,TRAVEL_ALLOWANCE) values (203,'DIGITAL_EVIDENCE','90.0.0.1',10000);
insert into PROOF(PID,PNAME,PLINK,TRAVEL_ALLOWANCE) values (204,'CERTIFICATE','23.6.8.5',55000);
insert into PROOF(PID,PNAME,PLINK,TRAVEL_ALLOWANCE) values (205,'CERTIFICATE','30.6.7.1',25000);

insert into ACHIEVEMENTS (AID,ANAME) values (100,'INTERSHIP');
insert into ACHIEVEMENTS (AID,ANAME) values (101,'WORKSHOP');
insert into ACHIEVEMENTS (AID,ANAME) values (102,'CONFERENCE');
insert into ACHIEVEMENTS (AID,ANAME) values (103,'GAMES');

insert into MAIN(ID,AID,PID) values (1,100,201);
insert into MAIN(ID,AID,PID) values (1,101,202);
insert into MAIN(ID,AID,PID) values (2,102,200);
insert into MAIN(ID,AID,PID) values (2,103,203);

INSERT INTO MAIN(ID,AID,PID) VALUES (3,100,205);


insert into ACHIEVERS (ID,NAME,SorF) values (1,'VIKAS','S');
insert into ACHIEVERS (ID,NAME,SorF) values (2,'YESWANT','S');
insert into ACHIEVERS (ID,NAME,SorF) values (3,'JASWANT','F');*/

/*aggregate functions,group by,having

select PNAM FROM PROOF GROUP BY PNAME HAVING SUM(TRAVEL_ALLOWANCE)>=2500 ;*/

/*ORDER BY

select DISTINCT(ACHIEVERS.NAME) FROM ACHIEVERS,MAIN WHERE ACHIEVERS.ID = MAIN.ID ORDER BY ACHIEVERS.NAME;*/
/*JOIN%

select PROOF.PNAME,PROOF.PLINK FROM PROOF INNER JOIN MAIN ON MAIN.PID = PROOF.PID;*/
/*OUTER JOIN
select ACHIEVERS.NAME,ACHIEVERS.SorF FROM ACHIEVERS FULL JOIN MAIN ON ACHIEVERS.ID = MAIN.ID;*/
/*BOOLEAN OPERATORS

select PROOF.PNAME,PROOF.TRAVEL_ALLOWANCE FROM PROOF WHERE PNAME='CERTIFICATE' AND TRAVEL_ALLOWANCE>5000;*/

/*ARITHMETIC OPERATORS

select ACHIEVERS.NAME, PROOF.TRAVEL_ALLOWANCE FROM PROOF, ACHIEVERS where MAIN.PID = PRO0F.PID  + (SELECT TRAVEL_ALLOWANCE FROM PROOF)*/

/*SEARCH OPERAIONS
SELECT NAME FROM ACHIEVERS WHERE NAME LIKE '%ANT%';*/

/*BETWEEN*/
SELECT PROOF.PID FROM PROOF WHERE TRAVEL_ALLOWANCE BETWEEN 10000 AND 20000;

/*NOT BETWEEN
SELECT PROOF.PID FROM PROOF WHERE TRAVEL_ALLOWANCE NOT BETWEEN 10000 AND 20000;*/
/*IN
SELECT PID FROM PROOF WHERE PNAME IN ('CERTIFICATE','PRIZE');*/
/*NOT IN
SELECT PID FROM PROOF WHERE PNAME NOT IN ('CERTIFICATE','PRIZE');*/

/*
ALTER  TABLE PROOF ADD DATE_ALLOWANCE DATE;
UPDATE PROOF SET DATE_ALLOWANCE = '2019-4-4' WHERE PID=200;
UPDATE PROOF SET DATE_ALLOWANCE = '2007-1-19' WHERE PID=201;
UPDATE PROOF SET DATE_ALLOWANCE = '2020-7-7' WHERE PID=202;
UPDATE PROOF SET DATE_ALLOWANCE = '2015-8-25' WHERE PID=203;
UPDATE PROOF SET DATE_ALLOWANCE = '2014-11-5' WHERE PID=204;
UPDATE PROOF SET DATE_ALLOWANCE = '2019-12-14' WHERE PID=205;*/


/*EXTRACT
SELECT DATE_ALLOWANCE FROM PROOF WHERE EXTRACT(MONTH FROM DATE_ALLOWANCE)>5;
TOCHAR
SELECT DATE_ALLOWANCE FROM PROOF WHERE TO_CHAR(DATE_ALLOWANCE,'MON')='JAN'
SUBQUERY
SELECT ACHIEVERS.NAME FROM ACHIEVERS,MAIN WHERE ACHIEVERS.ID=MAIN.ID AND MAIN.PID =(SELECT PROOF.PID FROM PROOF WHERE EXTRACT(YEAR FROM DATE_ALLOWANCE)=2020);*/