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);

select * from student;


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

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


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;

create table course_bkup as 
  select * from course;

select * from student
  left join assign on student.StudentID = assign.StudentID;

select * from student
 left join assign on student.StudentID = assign.StudentID
 left join course on assign.CourseID = course.CourseID;
  
select sname, subjcrs, title, address 
  from 
     student left join assign 
    on student.StudentID = assign.StudentID
 left join course 
   on assign.CourseID = course.CourseID;

select sname, subjcrs, title, address from student
  join assign on student.StudentID = assign.StudentID
  join course on assign.CourseID = course.CourseID; 
 
 select sname, subjcrs, title, address from course
  left join assign on course.courseID = assign.courseID
  left join student on assign.studentID = student.studentID; 
 
select subjcrs, sname from course, assign, student
where assign.courseid = course.courseid
  and assign.studentid = student.studentid; 

select subjcrs, sname from course, assign, student
where assign.courseid = course.courseid
  and assign.studentid = student.studentid
  order by subjcrs, sname; 
  
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;  
  

create table employee
(
    EmployeeID     int,
    ename   varchar (70),
    Address varchar (70),
    Phone   varchar (20),
   constraint student_PK primary key (EmployeeID)
) engine = innodb;


select * 
  from student
 limit 3;

insert into employee
  (select * 
    from student
   limit 3);

 insert into employee(employeeid,ename,Address,Phone) values (8,'John Jones','123 Main St','(215)555-1212');  
   
 select * from employee;   
   
 select studentid,sname,s.Address,s.Phone, employeeid,ename,e.Address,e.Phone from student s
   right join employee e
    on s.studentid = e.employeeid;
   
 select * from student
UNION
 select * from employee;

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

-- INTERSECT
 select StudentID, sName from student
 inner join employee
 on studentid = employeeid and sname = ename;

-- MINUS
  select distinct studentid, sname
   from student
  where (studentid, sname) not in
   ( select employeeid, ename from employee);
   

