--Assignment 1
--Name: Samantha Wijesinha
--Student Number: 1461079

--TASK 1 ERD Description
--ONE Team MAY be made up of MANY Student
--ONE Student MUST belong to ONE Team
--ONE Team MUST present MANY Information Session
--ONE Ticket MUST be made by PURCHASER
--ONE Purchaser MAY have MANY Tickets
--ONE Information Session MUST sells MANY Tickets

--TASK 2 Relational Schema

--TEAM(TeamNo, TeamName)
--PK TeamNo

--PURCHASER(PFirstName, PSurname, Address)
--PK PFirstName, PSurname

--INFORMATION_SESSION (SessNo, SessTitle, Duration, Price)
--PK SessNo
--FK TeamNo REFERENCES TEAM

--STUDENT (StuId, SFirstName, SSurname, Gender)
--PK StuId
--FK TeamNo REFERENCES Team

--TICKET(TicketNumber)
--PK TicketNumber
--FK SessNo REFERENCES Information_Session



--TASK 3 SQL DROP Statements

DROP TABLE Student;
DROP TABLE Ticket;
DROP TABLE Team;
DROP TABLE Information Session;
DROP TABLE Purchaser;

--TASK 4 CREATE TABLE Statements

CREATE TABLE TEAM
(
TeamNo NUMBER(4,1),
TeamName VARCHAR2(30),
PRIMARY KEY (TeamNo)
);

CREATE TABLE PURCHASER
(
PFirstName VARCHAR2(30) NOT NULL,
PSurname VARCHAR2(30) NOT NULL,
Address VARCHAR2(3) NOT NULL,
PRIMARY KEY (PFirstName, PSurname)
);

CREATE TABLE INFORMATION_SESSION
(
SessNo NUMBER(4,1),
SessTitle VARCHAR2(30) NOT NULL,
Duration VARCHAR2(30) NOT NULL,
Price NUMBER(4,2) NOT NULL,
PRIMARY KEY (SessNo),
FOREIGN KEY (TeamNo) REFERENCES TEAM
);


CREATE TABLE STUDENT
(
StuId NUMBER(4,1),
SFirstName VARCHAR2(30) NOT NULL,
SSurname VARCHAR2(30) NOT NULL,
Gender VARCHAR(30) NOT NULL,
PRIMARY KEY (StuId),
FOREIGN KEY (TeamNo) REFERENCES TEAM);

CREATE TABLE TICKET
(
TicketNumber NUMBER(4,1),
PRIMARY KEY (TicketNumber),
FOREIGN KEY (SessNo) REFERENCES INFORMATION_SESSION,
FOREIGN KEY (PFirstName, PSurname) REFERENCES PURCHASER
);



--TASK 5 SQL INSERT

INSERT INTO student ( StuId, SFirstName, SSurname, Gender ) VALUES (1, 'Barry', 'Bloggs', 'Male student') ;
INSERT INTO student ( StuId, SFirstName, SSurname, Gender ) VALUES (2, Carol, Crisp, Female student) ;
INSERT INTO student ( StuId, SFirstName, SSurname, Gender ) VALUES (3, Di, Dealy, Female student) ;
INSERT INTO student ( StuId, SFirstName, SSurname, Gender ) VALUES (4, Eric. Eden, Male student) ;
INSERT INTO student ( StuId, SFirstName, SSurname, Gender ) VALUES (5, Fiona, Fooph, Female student) ;
INSERT INTO student ( StuId, SFirstName, SSurname, Gender ) VALUES (6, Greg, Grant, Male student) ;
INSERT INTO student ( StuId, SFirstName, SSurname, Gender ) VALUES (7, Harry, Harris, Male student) ;
INSERT INTO student ( StuId, SFirstName, SSurname, Gender ) VALUES (8, Ingrid, Ink, Female student) ;

INSERT INTO Team ( TeamNo, TeamName ) VALUES (1, 'Wiz Kids') ;
INSERT INTO Team ( TeamNo, TeamName ) VALUES (2, 'Know It Alls') ;
INSERT INTO Team ( TeamNo, TeamName ) VALUES (3, 'Oomph') ;
INSERT INTO Team ( TeamNo, TeamName ) VALUES (43, 'Champions') ;

INSERT INTO Information Session (SessNo, SessTitle, Duration, Price) VALUES (1, Wireless Networks, 120, 5);
INSERT INTO Information Session (SessNo, SessTitle, Duration, Price) VALUES (2, LAN Party, 90, 6);
INSERT INTO Information Session (SessNo, SessTitle, Duration, Price) VALUES (3, Internet Security, 120, 8);
INSERT INTO Information Session (SessNo, SessTitle, Duration, Price) VALUES (4, Mobile Phone Programming, 60, 8);

INSERT INTO Ticket (TicketNumber) VALUES (1);
INSERT INTO Ticket (TicketNumber) VALUES (2);
INSERT INTO Ticket (TicketNumber) VALUES (3);
INSERT INTO Ticket (TicketNumber) VALUES (4);
INSERT INTO Ticket (TicketNumber) VALUES (5);
INSERT INTO Ticket (TicketNumber) VALUES (6);

INSERT INTO Purchaser (PFirstName, PSurname, Address) VALUES (Stu, Smith, 1 Silver St);
INSERT INTO Purchaser (PFirstName, PSurname, Address) VALUES (Tara, Tippleton, 1 Top Rd);
INSERT INTO Purchaser (PFirstName, PSurname, Address) VALUES (Val, Voss, 1 Vue St);
INSERT INTO Purchaser (PFirstName, PSurname, Address) VALUES (Warren, Wade, 1 Willow Gve);
INSERT INTO Purchaser (PFirstName, PSurname, Address) VALUES (Yvonne, Yang, 1 Yarra St);


--TASK 6 SELECT STATEMENTS

PROMPT Query 1a
SELECT *
FROM STUDENT
ORDER BY StuId ASC;

PROMPT Query 1b
SELECT * 
FROM TEAM
ORDER BY TeamNo ASC;

PROMPT Query 1c
SELECT *
FROM INFORMATION SESSION
ORDER BY SessNo ASC;

PROMPT Query 1d
SELECT *
FROM TICKET
ORDER BY TicketNumber ASC;

PROMPT Query 1e
SELECT *
FROM PURCHASER
ORDER BY pFirstName ASC;


PROMPT Query 2
SELECT *, Team.TeamNo
FROM STUDENT,TEAM
WHERE Team.TeamNo BETWEEN 2,3
AND Student.Gender="F"
ORDER BY Student.StuId ASC;

--JOIN TABLE
PROMPT Query 3
SELECT Student.SFirstName, Student.SSurname, Team.TeamName
FROM STUDENT, TEAM
ORDER BY T.TeamName ASC, s.Surname ASC;

PROMPT Query 4
SELECT Student.SFirstName, Student.Surname, InformationSession.SessTitle
FROM STUDENT, INFORMATION SESSION
ORDER BY S.Surname ASC;

PROMPT Query 5
SELECT Purchaser.PFirstname, Purchaser.PSurname, Purchaser.PAddress and InformationSession.SessTitle
FROM PURCHASER, INFORMATION SESSION

--In Query 5 you need to display Purchaser.PFirstname, Purchaser.PSurname, Purchaser.PAddress and InformationSession.SessTitle.

PROMPT Query 6
SELECT Purchaser.PFirstname, Purchaser.PSurname, Purchaser.PAddress and InformationSession.SessTitle
FROM PURCHASER, INFORMATION SESSION
GROUP BY
ORDER BY
COUNT(*)

--In Query 6 you only need InformationSession.SessTitle, but you also need a Count of the Number of Sessions.

--So even though you don't see an information from the other tables, you still need the information in the background from joining the tables.

--You will need Group By in the Statement also.

--I hope this all helps. If you need more help or info, let me know.

PROMPT Query 7;
SELECT t.PFirstName, t.PSurname, SUM(i.Price), iSessNo;
FROM TICKET t, INFORMATION_SESSION i;
GROUP BY SUM(i.Price);
WHERE t.SessNo = i.SessNo;