Basic SQL Commands

problem-4.txt

BEGIN TRANSACTION;

CREATE TABLE Students (ID integer PRIMARY KEY, Name varchar(255));
CREATE TABLE Friends (ID integer PRIMARY KEY, Friend_ID integer);
CREATE TABLE Packages (ID integer PRIMARY KEY, Salary float(23,19));

INSERT INTO Students (ID,Name)
VALUES (1,'Ashley');
INSERT INTO Students (ID,Name)
VALUES (2,'Samantha');
INSERT INTO Students (ID,Name)
VALUES (3,'Julia');
INSERT INTO Students (ID,Name)
VALUES (4,'Scarlet');

INSERT INTO Friends (ID,Friend_ID)
VALUES (1,2);
INSERT INTO Friends (ID,Friend_ID)
VALUES (2,3);
INSERT INTO Friends (ID,Friend_ID)
VALUES (3,4);
INSERT INTO Friends (ID,Friend_ID)
VALUES (4,1);

INSERT INTO Packages (ID,Salary)
VALUES (1,15.20);
INSERT INTO Packages (ID,Salary)
VALUES (2,10.06);
INSERT INTO Packages (ID,Salary)
VALUES (3,11.55);
INSERT INTO Packages (ID,Salary)
VALUES (4,12.12);

SELECT Students.Name 
FROM Students
INNER JOIN Friends
ON Students.ID = Friends.ID
INNER JOIN Packages as p1
ON Friends.ID = p1.ID
INNER JOIN Packages as p2
ON Friends.Friend_ID = p2.ID
WHERE P2.Salary > p1.Salary ORDER BY p2.Salary ASC