Practice Questions

PQ1 

CREATE DATABASE  IF NOT EXISTS college;

USE college;

CREATE TABLE teacher(

id INT, name VARCHAR(50),subject VARCHAR(50),salary INT);

INSERT INTO  teacher (id,name,subject,salary) values

(23,"ajay","math",50000),

(47,"bharath","english",60000),

(18,"chetan","chemistry",45000),

(9,"divya","physics",75000);

SELECT name,salary FROM teacher WHERE salary>55000;

ALTER TABLE teacher

CHANGE COLUMN salary ctc INT ;

SELECT * FROM teacher;

SET SQL_SAFE_UPDATES=0;

UPDATE teacher 

SET ctc=ctc+(ctc*0.25);

AlTER TABLE teacher 

ADD COLUMN city VARCHAR(50) default "gurgaon";

ALTER TABLE teacher

 DROP COLUMN ctc;

PQ2

CREATE DATABASE  IF NOT EXISTS college;

USE college;

 CREATE TABLE student(roll_no INT PRIMARY KEY ,

 name varchar(50),

 city VARCHAR(50),

 marks INT);

 INSERT INTO student (roll_no,name,city,marks)

 VALUES (110,"adam","delhi",76),

 (108,"bob","mumbai",56),

 (124,"casey","pune",94),

 (112,"duke","pune",80);

 update student SET marks=65 WHERE roll_no=108;

 SELECT * from student;

 SELECT name,marks FROM student WHERE marks >75;

 SELECT DISTINCT city FROM student;

 SELECT city,max(marks) FROM  student GROUP BY city;

 SELECT avg(marks) FROM  student;

 ALTER TABLE student

ADD COLUMN grade VARCHAR(2);

UPDATE student SET grade="O" WHERE marks>=80;

UPDATE student SET grade="A" WHERE marks<80 AND marks>=70;

UPDATE student SET grade="B" WHERE marks<70 AND marks>=60;