drop database schooldb;

create database schooldb;

use schooldb;

Create table Student
(
    StudentID     int auto_increment,
    Sname   varchar (70),
    Address varchar (70),
    Phone   varchar (20),
   constraint student_PK primary key (StudentID)
) engine = innodb;

Create table Course
(
    CourseID      int auto_increment,
    SubjCrs  varchar(20),
    Title    varchar(50),
    CrHrs    int,
  constraint course_PK primary key (CourseID)
) engine = innodb;

Create table Assign
(
   StudentID     int,
   CourseID     int,
   ActivityDate timestamp default current_timestamp,
 constraint assign_PK primary key (StudentID,CourseID),
 constraint assign_FK1 foreign key(StudentID) references Student (StudentID),
 constraint assign_FK2 foreign key(CourseID) references Course (CourseID)
) engine = innodb;

insert into Student (sname,Address,Phone) values ('Sam Jones','123 Main St','(215)555-1212');
insert into Student (sname,Address,Phone) values ('Sally Jones','123 Main St','(215)555-1212');
insert into Student (sname,Address,Phone) values ('Paul Smith','123 Main St','(215)555-1212');
insert into Student (sname,Address,Phone) values ('Angie Samuals','123 Main St','(215)555-1212');
insert into Student (sname,Address,Phone) values ('John Franken','123 Main St','(215)555-1212');
insert into Student (sname,Address,Phone) values ('Marshall Williams','123 Main St','(215)555-1212');

insert into Course (SubjCrs,Title,CrHrs) values ('Acct 101', 'Introduction To Accounting',3);
insert into Course (SubjCrs,Title,CrHrs) values ('Acct 102', 'Advanced Accounting', 4);
insert into Course (SubjCrs,Title,CrHrs) values ('Biol 101', 'Introduction To Biology',4);
insert into Course (SubjCrs,Title,CrHrs) values ('Biol 102', 'Advanced Biology', 4);
insert into Course (SubjCrs,Title,CrHrs) values ('Math 101', 'General Mathematics', 2);
insert into Course (SubjCrs,Title,CrHrs) values ('Math 102', 'Advanced Mathematics', 3);
insert into Course (SubjCrs,Title,CrHrs) values ('CIS 120', 'Computer Math', 3);

insert into assign (StudentID,CourseID) values (1,1);
insert into assign (StudentID,CourseID) values (1,3);
insert into assign (StudentID,CourseID) values (1,5);
insert into assign (StudentID,CourseID) values (1,6);
insert into assign (StudentID,CourseID) values (2,1);
insert into assign (StudentID,CourseID) values (2,3);
insert into assign (StudentID,CourseID) values (2,4);
insert into assign (StudentID,CourseID) values (2,6);

insert into assign (StudentID,CourseID) values (3,1);
insert into assign (StudentID,CourseID) values (3,3);
insert into assign (StudentID,CourseID) values (3,5);
insert into assign (StudentID,CourseID) values (3,6);
insert into assign (StudentID,CourseID) values (4,1);
insert into assign (StudentID,CourseID) values (4,3);
insert into assign (StudentID,CourseID) values (4,4);
insert into assign (StudentID,CourseID) values (4,6);


/*
Display the full contents of the Student TABLE

*/
select * from student;



/*  
Using the reserve word LIKE provided a tool to find patterns 
in Attributes that are Text Data Type.  In the First Example
below the elements in the attribute sname that have the 
letter S as the first value will display in the result set  
   */
SELECT s.sname , s.address, s.phone
FROM   student s
WHERE s.sname LIKE 'S%';

/* Using the NOT Logical Operator, you can reverse the poles
of the expression. Instead of displaying the students that 
have  names beginning with S, the students whose name does
not begin with S can be displayed 
  */

SELECT s.sname , s.address, s.phone
FROM   student s
WHERE s.sname NOT LIKE ('S%');



/*
Placing the S on the opposite side of the wild card symbol, 
you can now display the students that have an S at the end of 
their of names.  
*/

SELECT s.sname , s.address, s.phone
FROM   student s
WHERE s.sname LIKE '%S';

 /*
Just as before, by inserting the NOT Logical Operator into the
expression, you can invert the logic of the expression.  The 
query not displays the student names that do not have an S at 
the end of their names.  
 
 */

 
SELECT s.sname , s.address, s.phone
FROM   student s
WHERE s.sname NOT LIKE ('S%');

 
 
/*  
This is an applied example of using the AS Alias modifier
in A SELECT Query. It will use Student Name as the 
Column Header Label 
*/
 

SELECT sname AS 'STUDENT NAME', address, phone
FROM   student;


/*
The query below will display only the students who are enrolled
in a course and only the courses that have students enrolled.
Students not enrolled on a course and courses that do not have
students enrolled in them will not be displayed in this result set
*/


SELECT s.sname , s.address, s.phone , a.courseid, c.title
FROM   student s ,assign a, course c
Where   a.studentid = s.studentid and c.courseid = a.courseid;




SELECT s.sname , s.address, s.phone , a.courseid
FROM   student s ,assign a, course c
Where   a.studentid = s.studentid;



select * from course;

select * from assign;

/*
We have been studying the concepts of Intersection, 
Unions, and Differences. These are concepts in Relational
Algebra. RDBMS are the implementation of the concepts of 
Relational Algebra. Below is a series of code to setup a 
test environment to conduct illustrate these concepts
implemented in a RDBMS 
*/




/*
To setup this test environment, first, you must have two tables
with similar elements and different elements between them.  The
SQL statement below will create and load a table from the structure
and elements of an existing table.  After the statement is execute
there will be two tables with identical attribute signatures and
identical instances of data in them.
*/


create table course_bkup as 
  select * from course;


select * from course;
select * from course_bkup;
  
  
 /*
As was illustrated in the Relational Algebra Module,a left 
join will display the items that match and the items that do
not match.  Since it is a left join, all of the items in the
left table will list.  The rows from the right table that do 
not match, the attributes from the right table will load with
null values
*/ 
  
  select * from student
  left join assign on student.StudentID = assign.StudentID;


/*
This example illustrates the left join across three tables. 
The Assign table is used as a mapping tables between Student Table
and the Course Table.  The two attributes listed from the course 
table that do not match are filled with null values 
*/
  
  select sname, subjcrs, title, address 
  from 
     student left join assign 
    on student.StudentID = assign.StudentID
 left join course 
   on assign.CourseID = course.CourseID
   
   /*
   As you can see from the example below, when the left join 
   is not used and a natural join is used, John Franken and 
   William Marshall do not appear in the result set
   */
   
select sname, subjcrs, title, address from student
  join assign on student.StudentID = assign.StudentID
  join course on assign.CourseID = course.CourseID; 


/*
 Now let us look at this from the perspective of making the 
Course Table the left table and the Student Table the right 
table.  When this SELECT query with this structure is executed, 
it will display the courses that do not have any students 
registered in them.   
*/

 select sname, subjcrs, title, address from course
  left join assign on course.courseID = assign.courseID
  left join student on assign.studentID = student.studentID; 
 
/*
Once again to visually see the difference, run the query below
to see the result set of a join based on equality 

*/


 select subjcrs, sname from course, assign, student
where assign.courseid = course.courseid
  and assign.studentid = student.studentid; 

/*
This example simply illustrates using an ascending sort
The primary key sorting attribute is subcrs, the secondary 
sorting attribute is student name.  
*/
  
  
select subjcrs, sname from course, assign, student
where assign.courseid = course.courseid
  and assign.studentid = student.studentid
  order by subjcrs, sname; 
  
  /*
This a very interesting query This example simply illustrates
code that displays student name and subjcrs of students that
have not taken courses.  The student that have not taken 
courses will not match in a join.  This it is a left join, the
attributes from the rows of the table on the right that do 
match will fill with null values. If any of those attributes 
test be be null, it indicates a student that has not taken 
courses    
*/
  
  
select sname, subjcrs from student
 left join assign on student.StudentID = assign.StudentID
 left join course on assign.CourseID = course.CourseID
where subjcrs is null;  
  
/*
The Code below contains a series of statements that creates
a framework to illustrate and implement Union, Intersection
and Difference queries in MySQL.  


In order to set this up two tables with similar data type structures
must be in place.  This is why the Employee Table is created. It will
pair with the Student Table.  It has the identical data type structure
as the Student Table   
*/  
  
create table employee
(
    EmployeeID     int,
    ename   varchar (70),
    Address varchar (70),
    Phone   varchar (20),
   constraint student_PK primary key (EmployeeID)
) engine = innodb;


/*
The Select Query below contains the reserve word limit.  limit is a 
modifier to the SELECT query that allows you to specify the maximum 
number of rows permitted in the result set. The Query below limits the
number of rows in the result set to 3 rows  
*/


select * 
  from Student
 limit 3;

 /*
 We can use the limit modifier in the SELECT query in combination with a
 INSERT INTO statement to load the first three students in the Student
into the Employee Table. After this statement is executed, both tables will
have similar rows to locate when we execute an Intersection Query on them  
 
 */
 
 
insert into Employee
  (select * 
    from Student
   limit 3);

   
 /*
The statement below inserts a unique row into the Employee Table. By 
inserting this row into the Employee Table, a testing environment is 
established that allow you to run a query to perform a Difference Query
in both directions.  Their are now at least one or more different records
in both the Student Table and the Employee Table.  You now run a Difference
Query from Employee Table to Student Table and from Student Table to 
Employee Table  
*/
 
 insert into employee(employeeid,ename,Address,Phone) 
        values (8,'John Jones','123 Main St','(215)555-1212');  
   
/*
This Query is executed to visually confirm the contents in the Employee Table 

*/ 
select * from employee;   
   

/*
The code below illustrates and displays the result set of a right join between the 
Student Table and the Employee Table.  From our lesson on Relational Algebra, a 
you may recall that a Right Join will join rows where the keys from both table
match.  It will also display the values of the attributes in the Right Sided Table 
that does not match the equality test. The values in the attributes on the Left 
Table are filled with null values.     


The null values reveal the employees that are not students.  The rows with the null
values for the student attributes are the employees that are not students. 
*/


   select studentid,sname,s.Phone, employeeid,ename,e.Phone 
   from student s
   right join employee e
    on s.studentid = e.employeeid;
  
/*
The SELECT Statements with the reserve word UNION between them combine the rows from
two tables into a third table containing the rows from both tables.  This UNION mofifier
will adhere to the rules of the theoretical Union Operation discussed in the 
Relational Algebra  Module. The Attribute Data Type signature of both tables must be  
similar. The duplicated Items will be removed from the Result Set  
*/ 
  
 select * from student
UNION
 select * from employee;
 
 /*
 In the example below, the reserve words UNION ALL are used.  Use these reserve words in
this sequence will also perform a union of both tables.  However, in this case the union 
Result Set will contain the intersected duplicated rows from both tables.    
 
 */

 select *, 'Student' Source from student 
UNION ALL
 select *, 'Employee' from employee;

 
 /*
 The Example below is another illustration of code, that when run, will produce an intersection
between two tables.  This query will also produce a Result Set that contains Students that 
are also employees. This is accomplished through the fact the the join will only take place
when there is a student id in the student table that matches an employee with the same ID. 
 
 */
-- INTERSECT
 select StudentID, sName from student
 inner join employee
 on studentid = employeeid and sname = ename;

 
 /*
 Below the outer query will produce a set of rows that contain student ids and other
 descriptive data about a student, the inner/sub query will produce a set of rows 
 that contain employee ids and other descriptive data about an employee.  The NOT IN
modifier will enforce a filter that will only display the ID and Name of students that
are not in the Employee Table. 
 */
 
-- MINUS
  select distinct studentid, sname
   from student
  where (studentid, sname) not in
   ( select employeeid, ename from employee);
   

