CREATE TABLE employee (
    empno INT,
    empname VARCHAR(255),
    DOB DATE,
    salary INT,
    designation VARCHAR(20)
);

INSERT INTO employee(empno, empname, DOB, salary, designation) 
VALUES (100, 'John', '1999-07-02', 50000, 'Manager');

INSERT INTO employee(empno, empname, DOB, salary, designation) 
VALUES (101, 'Greg', '1994-06-10', 15000, 'Clerk');

SELECT * FROM employee;

UPDATE employee SET salary = salary + 1000;

DELETE FROM employee WHERE empno = 100;

CREATE TABLE employee (
    empno INT,
    empname VARCHAR(25),
    dob DATE,
    salary INT,
    designation VARCHAR(20)
);

ALTER TABLE employee ADD department VARCHAR(50);

ALTER TABLE employee MODIFY COLUMN department VARCHAR(20);

ALTER TABLE employee DROP COLUMN department;

RENAME TABLE employee TO emp1;

DROP TABLE emp1;

CREATE TABLE student (
    studentID INT PRIMARY KEY,
    sname VARCHAR(30) NOT NULL,
    department CHAR(5),
    sem INT,
    dob DATE,
    email_id VARCHAR(20) UNIQUE,
    college VARCHAR(20) DEFAULT 'MEC'
);

CREATE TABLE exam (
    examID INT,
    studentID INT REFERENCES student(studentID),
    department CHAR(5) NOT NULL,
    mark1 INT CHECK (mark1<=100 AND mark1>=0)
);

ALTER TABLE student ADD address VARCHAR(100);

ALTER TABLE student DROP address;

DROP TABLE exam;

INSERT INTO student VALUES (101,'RUPESH','IT', 5,'1996-04-18','rupesh@gmail.com','MEC');
INSERT INTO student VALUES (102,'BALA','CSE',7,'1995-10-17','bala@gmail.com','IIT');

INSERT INTO exam(examid, studentid, department, mark1, mark2) 
VALUES (2222, 101, 'IT', 98, 87);

UPDATE student SET college='MEC' WHERE studentid=102;

UPDATE exam SET total=(mark1+mark2);

DELETE FROM exam WHERE examid=2222;

-- SELECT ALL COLUMNS
SELECT * FROM student;

-- SELECT MULTICOLUMN
SELECT sname, department, sem FROM student;

-- SELECTION WITH ALIAS COLUMN NAME
SELECT sname AS StudentName, department, sem FROM student;

-- SELECTION WITH ARITHMETIC OPERATION
SELECT sname, department, sem+1 "Next_Sem" FROM student;

-- DISTINCT RECORD SELECTION
SELECT DISTINCT college FROM student;

-- SELECTION WITH WHERE CLAUSE
SELECT * FROM student WHERE college='MEC';

-- BETWEEN COMMAND
SELECT studentid, sname, department FROM student WHERE sem BETWEEN 5 AND 6;

-- IN COMMAND
SELECT studentid, sname FROM student WHERE department IN ('CSE','IT');

-- LIKE COMMAND
SELECT studentid, sname FROM student WHERE sname LIKE 'R%';
SELECT studentid, sname FROM student WHERE sname LIKE '%SH%';

-- RELATIONAL OPERATORS
SELECT studentid, sname FROM student WHERE studentid > 100 AND department='CSE';

-- ORDER BY
SELECT department, sem, sname FROM student ORDER BY department;
SELECT department, sem, sname FROM student ORDER BY department DESC, sem DESC;

-- AGGREGATE FUNCTIONS
SELECT COUNT(examid) AS STUDENTS_REGISTERED FROM exam;
SELECT MAX(total) AS HIGHEST_MARK FROM exam;
SELECT MIN(total) AS LOWEST_MARK FROM exam;

-- GROUP BY
SELECT department, SUM(total) AS SUM_DEPARTMENT FROM exam GROUP BY department;
SELECT department, AVG(total) AS AVERAGE FROM exam GROUP BY department;

-- INNER JOIN
SELECT c.studentID, c.sname, p.company, p.salary 
FROM cseitstudent c INNER JOIN placement p ON c.studentID = p.studentID;

-- LEFT OUTER JOIN
SELECT c.sname, p.company 
FROM cseitstudent c LEFT OUTER JOIN placement p ON c.studentID = p.studentID;

-- RIGHT OUTER JOIN
SELECT c.sname, p.company 
FROM cseitstudent c RIGHT OUTER JOIN placement p ON c.studentID = p.studentID;

-- EQUI JOIN
SELECT * FROM cseitstudent, placement WHERE cseitstudent.studentID = placement.studentID;

-- SELF JOIN
SELECT e1.empname AS Employee, e2.empname AS Manager 
FROM employee1 e1, employee1 e2 WHERE e1.reportingid = e2.empid;

-- UNION
SELECT studentid FROM student 
UNION 
SELECT studentid FROM exam;

-- UNION ALL
SELECT studentid FROM student 
UNION ALL 
SELECT studentid FROM exam;

-- INTERSECT
SELECT studentid FROM student 
INTERSECT 
SELECT studentid FROM exam;

-- EXCEPT (MINUS)
SELECT studentid FROM student 
EXCEPT 
SELECT studentid FROM exam;

-- BASIC NESTED QUERY
SELECT * FROM employee WHERE designation = (SELECT designation FROM employee WHERE empname='RUPESH');

-- IN OPERATOR
SELECT studentID, sname FROM student WHERE studentID IN (SELECT studentID FROM placement);

-- NOT IN OPERATOR
SELECT studentID, sname FROM student WHERE studentID NOT IN (SELECT studentID FROM placement);

-- ALL OPERATOR
SELECT studentID, company, salary FROM placement WHERE salary > ALL(SELECT salary FROM placement WHERE company='infosys');

-- EXISTS OPERATOR
SELECT * FROM placement WHERE EXISTS (SELECT * FROM placement WHERE salary>20000);

-- VIEW CREATION
CREATE VIEW studview AS (SELECT studentid, department, company FROM placement WHERE salary > 25000);

-- SELECT FROM VIEW
SELECT * FROM studview;

-- COMPLEX VIEW WITH JOIN
CREATE VIEW studetails AS (
    SELECT c.studentID, c.sname, c.sem, p.company, p.salary 
    FROM cseitstudent c INNER JOIN placement p ON c.studentID = p.studentID
);

-- QUERY THE VIEW
SELECT * FROM studetails;

-- DROP VIEW
DROP VIEW studetails;

SET autocommit=0;
START TRANSACTION;

INSERT INTO emp VALUES(106, 'John', 'manager', 25000);
COMMIT;

START TRANSACTION;
SAVEPOINT s1;
UPDATE emp SET salary=50000 WHERE emp_ID=104;

SAVEPOINT s2;
UPDATE emp SET salary=45000 WHERE emp_ID=103;

ROLLBACK TO s2;
ROLLBACK TO s1;

-- CREATE USER
CREATE USER student IDENTIFIED BY 'password123';

-- GRANT PRIVILEGES
GRANT ALL PRIVILEGES ON emp TO student;

-- REVOKE PRIVILEGES
REVOKE ALL PRIVILEGES FROM student;

-- DROP USER
DROP USER student;

-- STRING FUNCTIONS
SELECT CHAR_LENGTH("SQL Tutorial");
SELECT CONCAT("SQL", "Tutorial");
SELECT SUBSTRING('Welcome to Tutorialspoint', 11);

-- DATE FUNCTIONS
SELECT ADDDATE("2024-03-17 09:00:00", INTERVAL 15 MINUTE);
SELECT CURRENT_DATE();
SELECT DAY("2024-03-17");

-- MATH FUNCTIONS
SELECT ABS(-243.5);
SELECT POWER(4, 2);
SELECT ROUND(345.156, 0);
SELECT SQRT(64);

CHAR_LENGTH: 12
CONCAT: SQLTutorial

CURRENT_DATE: 2024-03-17

SQRT(64): 8

DELIMITER //
CREATE PROCEDURE WhileLoopExample()
BEGIN
    DECLARE n INT;
    DECLARE i INT;
    SET n = 5;
    SET i = 0;
    
    WHILE i <= n DO
        SELECT i AS Iteration;
        SET i = i + 1;
    END WHILE;
END//
DELIMITER ;

CALL WhileLoopExample();

DELIMITER //
CREATE FUNCTION getSalary(s INT) RETURNS INT READS SQL DATA
BEGIN
    DECLARE Salary INT;
    SELECT sal INTO Salary FROM employee WHERE eid = s;
    RETURN Salary;
END//
DELIMITER ;

DELIMITER //
CREATE PROCEDURE PrintSalary()
BEGIN
    DECLARE sal INT;
    DECLARE eno INT;
    SET eno = 103;
    SET sal = getSalary(eno);
    SELECT CONCAT('Salary: ', sal) AS Result;
END//
DELIMITER ;

CALL PrintSalary();

DELIMITER //
CREATE PROCEDURE process_table_data()
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE emp_id INT;
    
    DECLARE cursor_name CURSOR FOR SELECT eid FROM employee;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cursor_name;
    read_loop: LOOP
        FETCH cursor_name INTO emp_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
    END LOOP;
    CLOSE cursor_name;
END//
DELIMITER ;

CALL process_table_data();

CREATE TABLE emplog (
    eid INT,
    ename VARCHAR(10),
    idate DATE,
    nsal DECIMAL(10, 2)
);

DELIMITER //
CREATE TRIGGER logsal AFTER UPDATE ON employ
FOR EACH ROW
BEGIN
    INSERT INTO emplog (eid, ename, idate, nsal) 
    VALUES (NEW.eid, NEW.ename, CURDATE(), NEW.salary);
END//
DELIMITER ;

UPDATE employ SET salary = salary + 2500.0 WHERE expe > 3 AND expe < 7;

SELECT * FROM emplog;