test98SQL

CREATE DATABASE test98;

USE test98;

CREATE TABLE Student(
	stid INT,
	name CHAR(50),
	surname CHAR(50),
	semester INT,
	PRIMARY KEY (stid)
  );

CREATE TABLE Professor(
	pid INT,
	name CHAR(50),
	surname CHAR(50),
	PRIMARY KEY (pid)
  );

CREATE TABLE Course(
	cid INT,
	title CHAR(50),
	pid INT,
	PRIMARY KEY (cid),
	FOREIGN KEY (pid) REFERENCES Professor (pid) ON UPDATE CASCADE ON DELETE RESTRICT
  );

CREATE TABLE Exams(
	stid INT,
	cid INT,
	grade FLOAT,
	PRIMARY KEY (stid,cid),
	FOREIGN KEY (stid) REFERENCES Student (stid) ON UPDATE CASCADE ON DELETE RESTRICT,
	FOREIGN KEY (cid) REFERENCES Course (cid) ON UPDATE CASCADE ON DELETE RESTRICT
  );

CREATE TABLE Req(
	cid1 INT,
	cid2 INT,
	PRIMARY KEY (cid1,cid2),
	FOREIGN KEY (cid1) REFERENCES Course (cid) ON UPDATE CASCADE ON DELETE RESTRICT,
	FOREIGN KEY (cid2) REFERENCES Course (cid) ON UPDATE CASCADE ON DELETE RESTRICT
  );

LOAD DATA LOCAL INFILE 'C:\\Users\\username\\Desktop\\loadStudent.txt'
  INTO TABLE Student;

LOAD DATA LOCAL INFILE 'C:\\Users\\username\\Desktop\\loadProfessor.txt'
  INTO TABLE Professor;

LOAD DATA LOCAL INFILE 'C:\\Users\\username\\Desktop\\loadCourse.txt'
  INTO TABLE Course;

LOAD DATA LOCAL INFILE 'C:\\Users\\username\\Desktop\\loadExams.txt'
  INTO TABLE Exams;

LOAD DATA LOCAL INFILE 'C:\\Users\\username\\Desktop\\loadReq.txt'
  INTO TABLE Req;

/////////////////////////////////////////////////////////////////////////////////////////////////
SELECT *
FROM Student NATURAL JOIN Exams;

SELECT stid,name,surname,semester
FROM Student NATURAL JOIN Exams
WHERE grade>=5 AND
cid=101;

SELECT Student.stid,name,surname,semester
FROM Student,Exams
WHERE grade>=5 AND
cid=101 AND
Student.stid=Exams.stid;

SELECT Student.stid,Student.name,Student.surname,Exams.grade,Exams.cid,Course.title
FROM Student,Exams,Course
WHERE Student.stid=Exams.stid AND
Course.cid=Exams.cid;


SELECT *
FROM (Student,Course) NATURAL JOIN Exams;

SELECT *
FROM (Student,Course) NATURAL LEFT JOIN Exams
ORDER BY stid,cid;

SELECT *
FROM (SELECT * FROM Student,Course) T
NATURAL LEFT JOIN Exams
ORDER BY stid,cid;