/* 039- VIEW */ --create table "students" and insert records -- CREATE TABLE students( id INT NOT NULL, name VARCHAR(50) NOT NULL, percentage INT NOT NULL, dob DATE NOT NULL, age INT NOT NULL, gender VARCHAR(1) NOT NULL, city INT NOT NULL, courses INT NOT NULL, PRIMARY KEY (id), FOREIGN KEY (city) REFERENCES City (cid), FOREIGN KEY (courses) REFERENCES Courses (course_id) ); INSERT INTO students(id,name,percentage,dob,age,gender,city,courses) VALUES (1,"Ram Kumar","45","2000-05-10","19","M",1,1), (2,"Sarita Kumari","85","1997-02-03","22","F",2,2), (3,"Salman Khan","29","1999-11-12","20","M",1,1), (4,"Juhi Chawla","47","2001-07-16","18","F",3,1), (5,"Anil Kapoor","74","1997-01-03","22","M",1,3), (6,"John Abraham","64","1998-08-10","21","M",2,2), (7,"Shahid Kapoor","62","1999-12-08","20","M",1,3); -- create table "courses" and insert records -- CREATE TABLE courses( course_id INT NOT NULL AUTO_INCREMENT, course_name VARCHAR(50) NOT NULL, PRIMARY KEY (course_id) ); INSERT INTO courses(course_name) VALUES('Btech'), ('BCA'), ('MBA'); -- create table "city" and insert records -- CREATE TABLE city( cid INT NOT NULL AUTO_INCREMENT, cityname VARCHAR(50) NOT NULL, PRIMARY KEY (cid) ); INSERT INTO city(cityname) VALUES('Agra'), ('Delhi'), ('Bhopal'), ('Jaipur'), ('Noida'); -- VIEW Examples -- -- Create view -- CREATE VIEW studentdata AS SELECT id,name,course_name FROM students s INNER JOIN courses c ON s.courses = c.course_id; -- show view -- SELECT * FROM studentdata; -- Alter view -- ALTER VIEW studentdata AS SELECT id,name,course_name,cityname FROM students s INNER JOIN courses c ON s.courses = c.course_id INNER JOIN city ci ON s.city = ci.cid; -- Another way to alter -- CREATE OR REPLACE VIEW studentdata AS SELECT id,name,course_name,cityname FROM students s INNER JOIN courses c ON s.courses = c.course_id INNER JOIN city ci ON s.city = ci.cid; -- rename view -- RENAME TABLE studentdata TO studentcourse; -- show view -- SELECT * FROM studentcourse; -- Delete/Drop view -- DROP VIEW studentcourse;