simpleSelectQueries.sql

create database college;
use college;

create table student(
rollno int primary key,
name varchar(50),
marks int not null,
grade varchar(1),
city varchar(20));

create table dept(
id int primary key,
name varchar(50));
create table teacher(
id int primary key,
name varchar(50),
dept_id int,
foreign key(dept_id) references dept(id)
on delete cascade
on update cascade);


insert into student(rollno, name, marks, grade, city)
values
(101, "anil", 78, "C", "Pune"),
(102,"bhumika", 93, "A", "Mumbai"),
(103, "chetan", 85, "B", "Mumbai"),
(104, "dhruv", 96, "A", "Delhi"),
(105, "emanuel", 12, "F", "Delhi"),
(106, "farah", 82, "B", "Delhi");

select name, rollno from student;
select distinct city from student;
select * from student where marks>80 and city="mumbai";
select * from student order by marks desc limit 3;
select max(marks) from student;
select count(name) from student; 
select city, avg(marks) from student group by city;
select city, avg(marks) from student group by city order by city;

select mode, count(customer) from payment group by mode; #Question

select grade, count(name) from student group by grade order by grade;
select city, count(rollno) from student group by city having max(marks)>90;
select city from student where grade="A" group by city having max(marks)>=90 order by city desc;

update student set grade="O" where grade = "A";
SET SQL_SAFE_UPDATES=0;
update student set marks="92" where rollno=105;
update student set grade="B" where marks between 80 and 90;
select * from student;
update student set marks=marks+1;
update student set marks=12 where rollno=105;

delete from student where marks<33;

insert into dept values(101, "English"),(102,"IT");
insert into teacher values(101, "Adam", 101),(102,"Eve", 102);
select * from teacher;
drop table teacher;

update dept set id=111 where id=101;
select * from dept;

select avg(marks) from stu;
select full_name, marks from stu where marks>89;

select full_name, marks from stu where marks>(select avg(marks)from stu);

select full_name, rollno from stu where rollno%2=0;

select max(marks) 
from 
(select * from stu where city="Delhi") as temp;

select(select max(marks) from stu), full_name from stu;

create view view1 as select rollno, full_name, marks from stu;

select * from view1 where marks>90;
drop view view1;

select * from view1;