set operators, nested queries, join queries.sql

-- union
SELECT DNUMBER FROM DEPARTMENT UNION SELECT DNO FROM EMPLOYEE
SELECT DNUMBER FROM DEPARTMENT UNION ALL SELECT DNO FROM EMPLOYEE
INSERT INTO DEPARTMENT VALUES ('Marketing', 6, NULL, NULL)
SELECT DNUMBER FROM DEPARTMENT MINUS SELECT DNO FROM EMPLOYEE
SELECT DNO FROM EMPLOYEE MINUS SELECT DNUMBER FROM DEPARTMENT

-- nested query
SELECT EMPNAME, BASICSALARY
FROM EMP
WHERE BASICSALARY > (SELECT MIN(BASICSALARY) FROM EMP)
  AND DESIGNATION LIKE 'E%';

-- join operations

CREATE TABLE students (roll_no INT, name VARCHAR(255), address VARCHAR(255), phone VARCHAR(255), age INT);
INSERT INTO students VALUES (1, 'HARSH', 'DELHI', 'XXXXXXXXXX', 18);
INSERT INTO students VALUES (2, 'PRATIK', 'BIHAR', 'XXXXXXXXXX', 19);
INSERT INTO students VALUES (3, 'RIYANKA', 'SILIGURI', 'XXXXXXXXXX', 20);
INSERT INTO students VALUES (4, 'DEEP', 'RAMNAGAR', 'XXXXXXXXXX', 18);
INSERT INTO students VALUES (5, 'SAPTARSHI', 'KOLKATA', 'XXXXXXXXXX', 19);
INSERT INTO students VALUES (6, 'DHANRAJ', 'BARABAJAR', 'XXXXXXXXXX', 20);
INSERT INTO students VALUES (7, 'ROHIT', 'BALLURGHAT', 'XXXXXXXXXX', 18);
INSERT INTO students VALUES (8, 'NIRAJ', 'ALIPUR', 'XXXXXXXXXX', 19);

CREATE TABLE STUDENT_COURSE (COURSE_ID NUMBER(10), ROLL_NO NUMBER(10));

INSERT INTO STUDENT_COURSE VALUES (1,1)
INSERT INTO STUDENT_COURSE VALUES (2,2)
INSERT INTO STUDENT_COURSE VALUES (2,3)
INSERT INTO STUDENT_COURSE VALUES (3,4)
INSERT INTO STUDENT_COURSE VALUES (1,5)
INSERT INTO STUDENT_COURSE VALUES (4,9)
INSERT INTO STUDENT_COURSE VALUES (5,10)
INSERT INTO STUDENT_COURSE VALUES (4,11)

SELECT STUDENT_COURSE.COURSE_ID, STUDENTS.NAME, STUDENTS.AGE FROM STUDENTS
INNER JOIN STUDENT_COURSE
ON STUDENTS.ROLL_NO = STUDENT_COURSE.ROLL_NO


SELECT STUDENTS.NAME,STUDENT_COURSE.COURSE_ID
FROM STUDENTS
LEFT JOIN STUDENT_COURSE
ON STUDENT_COURSE.ROLL_NO = STUDENTS.ROLL_NO;


SELECT STUDENTS.NAME,STUDENT_COURSE.COURSE_ID
FROM STUDENTS
RIGHT JOIN STUDENT_COURSE
ON STUDENT_COURSE.ROLL_NO = STUDENTS.ROLL_NO;

SELECT STUDENTS.NAME,STUDENT_COURSE.COURSE_ID
FROM STUDENTS
FULL JOIN STUDENT_COURSE
ON STUDENT_COURSE.ROLL_NO = STUDENTS.ROLL_NO;