CREATE TABLE Department(
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(20) NOT NULL 
);

CREATE TABLE Student(
    student_id INT PRIMARY KEY,
    student_name VARCHAR(20) NOT NULL,
    email VARCHAR(30) UNIQUE,
    marks INT ,
    age INT CHECK(age>=18),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);

INSERT INTO Department VALUES(1101,'IT');
INSERT INTO Department VALUES(1102,'ENTC');
INSERT INTO Department VALUES(1103,'COMP');
INSERT INTO Department VALUES(1104,'IT');

SELECT * FROM Department;

INSERT INTO Student VALUES(1,'Riya','riya@gmail.com',80,20,1101);
INSERT INTO Student VALUES(2,'Ram','ram@gmail.com',70,19,1102);
INSERT INTO Student VALUES(3,'Sham','sham@gmail.com',50,18,1103);
INSERT INTO Student VALUES(4,'Vivek','vivek@gmail.com',60,21,1104);

SELECT * FROM Student;
SELECT*FROM Student WHERE dept_id=1101;   
SELECT*FROM Student WHERE age<19;
SELECT*FROM Student WHERE age>19;
SELECT*FROM Student WHERE age>=19;
SELECT*FROM Student WHERE age<=19;
SELECT*FROM Student WHERE age=19 AND dept_id=1102;   
SELECT*FROM Student WHERE age=19 OR dept_id=1101;
SELECT*FROM Department WHERE NOT dept_name='IT';
SELECT*FROM Student WHERE student_name LIKE 'R%';   
SELECT*FROM Student WHERE student_name LIKE '%m';
SELECT*FROM Student WHERE student_name LIKE '%a%';
SELECT COUNT(*) AS total_students FROM Student;     
SELECT AVG(marks) AS average_marks FROM Student;
SELECT MAX(marks) AS highest_marks FROM Student;
SELECT MIN(marks) AS lowest_marks FROM Student;
SELECT SUM(marks) AS total_marks FROM Student;
SELECT UPPER(student_name) FROM Student;
SELECT LOWER(student_name) FROM Student;
SELECT student_name,LENGTH(student_name) FROM Student;