----CREATED: For ACUA Roundtable Breakout
----utilizing Toad for Oracle
TRUNCATE TABLE STUDENT;
TRUNCATE TABLE STUDENTCOURSES;
TRUNCATE TABLE COURSETABLE;

DROP TABLE STUDENT;
DROP TABLE STUDENTCOURSES;
DROP  TABLE COURSETABLE;
 
CREATE TABLE STUDENT
(
    STUDENTID        VARCHAR2 (1 CHAR),
    BIRTHDATE        DATE,
    STUDENTNAME      VARCHAR2 (55 CHAR),
    PREFERREDNAME    VARCHAR2 (50 CHAR),
    ADDRESS          VARCHAR2 (125 CHAR),
    CITY             VARCHAR2 (75 CHAR),
    POSTALCODE       VARCHAR2 (10 CHAR),
    COUNTRY          VARCHAR2 (35 CHAR),
    FIN_AID          DECIMAL (15, 2),              --DECIMAL(precision, scale)
    CONSTRAINT PK_STUDENTID PRIMARY KEY (STUDENTID)
--MONEY/DECIMAL data type
--https://www.ibm.com/docs/en/informix-servers/14.10?topic=types-moneyps-data-type
);

--ALL DATA TYPES depending on common DBMS
--https://www.w3schools.com/sql/sql_datatypes.asp


--Retrieve all student records from the Student Table
--using asterisk

SELECT * FROM STUDENT;

--Retrieve all student records from the Student Table using column names

SELECT STUDENTID,
       BIRTHDATE,
       STUDENTNAME,
       PREFERREDNAME,
       ADDRESS,
       CITY,
       POSTALCODE,
       COUNTRY,
       TUITION
  FROM STUDENT;

DROP TABLE STUDENT;
---https://docs.oracle.com/cd/B14117_01/server.101/b10759/statements_9003.htm

TRUNCATE TABLE STUDENT;
-- https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10007.htm#SQLRF01707



--INSERT INTO

INSERT INTO STUDENT (STUDENTID,
                     BIRTHDATE,
                     STUDENTNAME,
                     PREFERREDNAME,
                     ADDRESS,
                     CITY,
                     POSTALCODE,
                     COUNTRY,
                     
                     FIN_AID)
     VALUES ('1',
             TO_DATE ('12/17/1997 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
             'Alfreds Futterkste',
             'Maria Anders',
             'Obere Str. 57',
             'Berlin',
             '12209',
             'Germany',
            
             '1000');

INSERT INTO STUDENT (STUDENTID,
                     BIRTHDATE,
                     STUDENTNAME,
                     PREFERREDNAME,
                     ADDRESS,
                     CITY,
                     POSTALCODE,
                     COUNTRY,
                      
                     FIN_AID)
     VALUES ('2',
             TO_DATE ('02/14/1987 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
             'Ana Trujillo Emparedados y helados',
             'Ana Trujillo',
             'Avda. de la Constitution 2222',
             'Mexico D.F.',
             '05021',
             'Mexico',
             
             '25000');

INSERT INTO STUDENT (STUDENTID,
                     BIRTHDATE,
                     STUDENTNAME,
                     PREFERREDNAME,
                     ADDRESS,
                     CITY,
                     POSTALCODE,
                     COUNTRY,
                     
                     FIN_AID)
     VALUES ('3',
             TO_DATE ('10/14/1977 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
             'Antonio Moreno Taqueria',
             'Antonio Moreno',
             'Mataderos 2312',
             'Mexico D.F.',
             '05023',
             'Mexico',
              
             '50000');

INSERT INTO STUDENT (STUDENTID,
                     BIRTHDATE,
                     STUDENTNAME,
                     PREFERREDNAME,
                     ADDRESS,
                     CITY,
                     POSTALCODE,
                     COUNTRY,
                      
                     FIN_AID)
     VALUES ('4',
             TO_DATE ('11/16/1967 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
             'Around the Horn',
             'Thomas Hardy',
             '120 Hanover Sq.',
             'London',
             'WA1 1DP',
             'UK',
              
             '2500');

INSERT INTO STUDENT (STUDENTID,
                     BIRTHDATE,
                     STUDENTNAME,
                     PREFERREDNAME,
                     ADDRESS,
                     CITY,
                     POSTALCODE,
                     COUNTRY,
                      
                     FIN_AID)
     VALUES ('5',
             TO_DATE ('01/11/1987 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
             'Berglunds snabbkop',
             'Christina Berglund',
             'Berguvsvagen 8',
             'Lulea',
             'S-958 22',
             'Sweden',
              
             '300');

--********-----TO_DATE https://www.foxinfotech.in/2018/07/how-to-insert-data-in-table-using-toad.html


SELECT * FROM STUDENT;
 
--Aggregate functions
--https://docs.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql?view=sql-server-ver15
--https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions003.htm#SQLRF20035

 

--ALIAS

  SELECT STUDENTID,
         STUDENTNAME,
         PREFERREDNAME,
         ADDRESS,
         CITY,
         POSTALCODE,
         COUNTRY,
         FIN_AID
    FROM STUDENT ST                                          --the alias is ST
GROUP BY STUDENTID,
         STUDENTNAME,
         PREFERREDNAME,
         ADDRESS,
         CITY,
         POSTALCODE,
         COUNTRY,
         FIN_AID
  HAVING STUDENTID > 1
ORDER BY STUDENTNAME DESC;



DROP TABLE STUDENTCOURSES;

CREATE TABLE STUDENTCOURSES
(
    STUDENTID    VARCHAR2 (1 CHAR),
    COURSEID     VARCHAR2 (10 CHAR),
    CONSTRAINT PK_STUDENTCOURSES PRIMARY KEY (STUDENTID, COURSEID),
    CONSTRAINT FK_STUDENTID FOREIGN KEY (STUDENTID)
        REFERENCES STUDENT (STUDENTID),
    CONSTRAINT FK_COURSETABLE FOREIGN KEY (COURSEID)
        REFERENCES COURSETABLE (COURSEID)
);

SELECT * FROM STUDENTCOURSES;

INSERT INTO STUDENTCOURSES (STUDENTID, COURSEID)
     VALUES ('1', '000000001');

INSERT INTO STUDENTCOURSES (STUDENTID, COURSEID)
     VALUES ('1', '000000002');

INSERT INTO STUDENTCOURSES (STUDENTID, COURSEID)
     VALUES ('1', '000000003');

INSERT INTO STUDENTCOURSES (STUDENTID, COURSEID)
     VALUES ('2', '000000002');

INSERT INTO STUDENTCOURSES (STUDENTID, COURSEID)
     VALUES ('2', '000000003');

INSERT INTO STUDENTCOURSES (STUDENTID, COURSEID)
     VALUES ('3', '000000001');

INSERT INTO STUDENTCOURSES (STUDENTID, COURSEID)
     VALUES ('3', '000000002');

DROP TABLE COURSETABLE;

CREATE TABLE COURSETABLE
(
    COURSEID      VARCHAR2 (10 CHAR),
    CLASSROOM     VARCHAR2 (4 CHAR),
    FACULTY       VARCHAR2 (75 CHAR),
    COLLEGE       VARCHAR2 (75 CHAR),
    COURSENAME    VARCHAR2 (125 CHAR),
    TUITION       DECIMAL (10, 2),                 --DECIMAL(precision, scale)
    CONSTRAINT PK_COURSEID PRIMARY KEY (COURSEID)
);

SELECT * FROM COURSETABLE;

DROP TABLE COURSETABLE;

INSERT INTO COURSETABLE (COURSEID,
                         CLASSROOM,
                         FACULTY,
                         COLLEGE,
                         COURSENAME,
                         TUITION)
     VALUES ('000000001',
             '143',
             'Dr. Smith',
             'College of Engineering',
             'Intro to Computer Programming',
             '5000');

INSERT INTO COURSETABLE (COURSEID,
                         CLASSROOM,
                         FACULTY,
                         COLLEGE,
                         COURSENAME,
                         TUITION)
     VALUES ('000000002',
             '144',
             'Dr. Jones',
             'College of Analytics',
             'Intro to Data Science',
             '6000');

INSERT INTO COURSETABLE (COURSEID,
                         CLASSROOM,
                         FACULTY,
                         COLLEGE,
                         COURSENAME,
                         TUITION)
     VALUES ('000000003',
             '145',
             'Dr. Torres',
             'College of Business',
             'Accounting for Audit',
             '4500');

SELECT * FROM STUDENT;

SELECT * FROM COURSETABLE;

SELECT * FROM STUDENTCOURSES;

SELECT * FROM COURSETABLE;

--How many courses?

SELECT * FROM COURSETABLE;

SELECT COUNT (COURSEID) FROM COURSETABLE;

--Name the different colleges

SELECT DISTINCT COLLEGE
  FROM COURSETABLE;

--SELECT All courses per student and tuition cost

  SELECT DISTINCT STUDENT.STUDENTID,
                  STUDENT.STUDENTNAME,
                  STUDENT.PREFERREDNAME,
                  STUDENT.ADDRESS,
                  STUDENT.CITY,
                  STUDENT.POSTALCODE,
                  STUDENT.COUNTRY,
                  STUDENTCOURSES.COURSEID
    FROM STUDENT
         LEFT JOIN STUDENTCOURSES
             ON STUDENT.STUDENTID = STUDENTCOURSES.STUDENTID
--WHERE CourseID IS NOT NULL
ORDER BY STUDENTID;

--SELECT All courses per student
--EXCLUDE students that do are not taking courses this semester

  SELECT DISTINCT STUDENT.STUDENTID,
                  STUDENT.STUDENTNAME,
                  STUDENT.PREFERREDNAME,
                  STUDENT.ADDRESS,
                  STUDENT.CITY,
                  STUDENT.POSTALCODE,
                  STUDENT.COUNTRY,
                  STUDENTCOURSES.COURSEID
    FROM STUDENT
         LEFT JOIN STUDENTCOURSES
             ON STUDENT.STUDENTID = STUDENTCOURSES.STUDENTID
   WHERE COURSEID IS NOT NULL
ORDER BY STUDENTID;