show databases;
create database learners;
create table learners(id int, name varchar(20), dob date);
insert into learners values(1,"suman",'29-2-24');
insert into learners values(2,"riya",'25-2-24');
insert into learners values(3,"vishal",'11-1-24');
insert into learners values(4,"punit",'13-2-23');
insert into learners values(5,"ram",'14-5-25');
select * from learners;

set sql_safe_updates=0;
update learners set name="mohan" where id=5;
delete from learners where id=2;
update learners set dob='2001-6-23' where id=4;
alter table learners change name sname varchar(20);
alter table learners add column duration int default 35;
alter table learners modify column date datetime;
alter table learners modify column dob datetime;



show databases;
create database people;
create table people(pid int, name varchar(20), city varchar(20), fees int);
insert into people values(1,"nitin","indore",500);
insert into people values(2,"tushar","jammu",100);
insert into people values(3,"money","kanpur",200);
insert into people values(4,"joy","pune",150);
insert into people values(5,"aditi","raipur",50);
select * from people;
alter table people add column age int;
alter table people change column pid id int;
alter table people drop column fees;
describe people;
alter table people modify column name varchar(30);
create table people1 as select * from people;
select * from people1;
create table people2 as select * from people;
truncate people1;
describe people1;
select * from people2;
drop table people2;
describe people2; 

select 30+ 15 as total;
select 75-39 as subtraction;
select 20*4 as multipication;
select 50/5 as division;
select 50%4 as modulus;
select 5 as number1, 3 as number2, 5+3 as total,5-3 as diff, 5*3 as multiply;
select salary*0.1 as increment from learners;
select * from learners;
select id,sname from learners;
select * from learners limit 2;
-- limit helps u to excute the numbers of rows u wanna see..
select * from learners order by sname desc;
select * from learners order by dob desc;
-- show me detail of 2 person who joined recently
select * from learners order by id desc limit 2;
-- show me detail of 3 persons who is in the last of your list as per the name in the dictionary order
select * from learners order by sname desc limit 3; -- hw
select * from learners where id=1;
select * from learners where id=1 or id=4;-- here you can id of people according to your choice
-- or, and not 
select * from learners where id=1 or sname='punit';
select * from learners where id=1 and sname='punit';
select * from learners where id between 1 and 3;-- this feature of between is utilze to see the few people you wanna see
select id=2 from learners;-- to check whether the id is agreeing the conditions u mentioned
select id,id>3 from learners;
select id,id<3 from learners;
select id, id!=3 from learners;
select id <>3 from learners;
select id!=3 from learners;
select id, id>3, not(id>3) from learners;

-- constraint rules
create table rules(id int unique, name varchar(20)not null, age int check(age>18), country varchar(20) default("India"));
insert into rules values (1,"suman",15,"USA");
insert into rules values(1,"riya",19,"USA");
insert into rules values(2,"rani",25);
insert into rules(id,name,age) values(2,"rani",30);
select * from rules;
select * from learners;
alter table learners modify id int unique;
insert into learners (id,sname)values(6,"hello");
alter table learners modify sname varchar(20) not null;
insert into learners (id,dob)values(6,"2014-03-05");

select * from members;
select * from movies;
select * from movies join members on movies.id=members.movieid;
select * from movies left join members on movies.id=members.movieid;
select * from movies right join members on movies.id=members.movieid;

select * from books;
select * from book_sales;
select * from authors;
select * from books join book_sales on books.bookid=book_sales.bookid;
select * from books right join book_sales on books.bookid=book_sales.bookid;
select * from books left join book_sales on books.bookid=book_sales.bookid;

select * from books join authors on books.authorid=authors.authorid;
select * from books right join authors on books.authorid=authors.authorid;
select* from books left join authors on books.authorid=authors.authorid;

select * from myemp;
select sum(salary)as total_salary from myemp;
select avg(salary)as average_salary from myemp;
select max(salary) as maximum_salary from myemp;
select min(salary)as minimum_salary from myemp;
select count(emp_id) as no_of_emp from myemp;
select count(*) as no_of_emp from myemp;
select dep_id,count(emp_id) from myemp group by dep_id;
select job_id,count(job_id)from myemp group by job_id;
select dep_id,sum(salary)from myemp group by dep_id;
select job_id,sum(salary) from myemp group by job_id;
select job_id,sum(salary),max(salary),min(salary) from myemp group by job_id;-- to see the table together--
select job_id,sum(salary),max(salary),min(salary),count(job_id) from myemp where salary>10000 group by job_id;-- where clause condition--
select job_id,sum(salary),max(salary),min(salary),count(emp_id)from myemp group by job_id having sum(salary)>10000;
select job_id,sum(salary),max(salary),min(salary),count(emp_id) from myemp where salary>5000 group by job_id having sum(salary)>10000;
select dep_id,max(hire_date),min(hire_date) from myemp group by dep_id;-- first and last hire date
select dep_id,count(emp_id),min(hire_date),max(hire_date),sum(salary) from myemp group by dep_id;-- hw--

select * from movies;
select * from members;
select id from movies union select movieid from members;
select id from movies union all select movieid from members;
select * from movies left join members on movies.id=members.movieid union select * from movies right join members on movies.id=members.movieid;
select * from global;

call seetable();
select * from employees;

select emp_id,first_name, salary,rank()over(order by salary desc)from myemp;-- rank--
select emp_id,first_name,salary,dense_rank()over(order by salary desc)from myemp;-- dense rank--
select emp_id,first_name,salary,row_number() over() from myemp;
select emp_id,count(emp_id)over ()from myemp;
select officecode, count(employeenumber) from employees group by officecode;-- in each office how many employees are working --
with a as(select *,dense_rank()over(order by salary desc)as rnk from myemp)select* from a where rnk=2;
with a as(select*,dense_rank()over(order by salary desc)as rnk from myemp)select* from a where rnk=10;-- hw--
select* from people;

set sql_safe_updates=0;
update people set age=-30 where id between 1 and 5;
select * from people where id between 1 and 5;

select ucase(name) from people  where id between 1 and 5;-- hw--

select * from people;-- tcl--
start transaction;
insert into people values(6,"ana","patna",35);
rollback;
set sql_safe_updates=0;
update people set name="alia" where id=1;
rollback;
delete from people where id=3;
rollback;
commit;



-- saveponit--
create table tcltable(id int, name varchar(10));
insert into tcltable values(1,"ash"),(2,"isha");
select * from tcltable;
start transaction;
insert into tcltable values(3,"sheena"),(4,"veena");
savepoint s1;
insert into tcltable values(5,"mayank"),(6,"aryan");
savepoint s2;
insert into tcltable values(7,"aditi"),(8,"bhooma");
rollback to s2;
rollback to s1;
rollback;

select * from employees;

select * from myemp where salary=(select max(salary) from myemp);-- person with high salary--
select * from myemp where salary=(select min(salary) from myemp);-- person with less salary--

select * from orders;
select * from orderdetails;
select * from customers;-- remark customers--
alter table customers add column remarks varchar(20);
set sql_safe_updates=0;
update customers set remarks= ' high credit limit' where creditlimit>70000;
update customers set remarks=' average climit' where creditlimit <=70000 and creditlimit>= 10000;
update customers set remarks =' low credit limit' where creditlimit<10000;
select * from orders;
select * from customers join orders on customers.customernumber=orders.customernumber;
select customernumber, sum(quantityOrdered) from customers join orders using(customernumber) join orderdetails using (ordernumber) group by customernumber order by sum(quantityOrdered) desc ;-- when u combine 2 tables toegther--
select sum(quantityOrdered) as total_quantityOrdered from orderdetails;
select quantityordered * priceeach as sales from orderdetails;
select customernumber, sum(quantityordered*priceeach)as sales from customers join orders using(customernumber) join orderdetails using (ordernumber)group by customernumber;-- here we need to put sum of sales that is quantity ordered and price esch to get seprate sales about each members--

select * from offices;
select country, count(officecode) from offices group by country;
select * from employees;
select count(employeenumber) as total_employees from employees;
select employeenumber, count(officecode) from employees group by employeenumber;
select officecode, count(employeenumber) from employees group by officecode;-- in one office how many employees are working --
-- in one country how many employees are working do join first then find out--
select * from offices join employees on offices.officecode=employees.officecode;
select country, count(employeenumber) from offices join employees on offices.officecode=employees.officecode group by country;


































-- finding out sales by multiplying 1*2 from the table--



-- project --
show databases;
create database pms;
create table pms(tid int primary key, name varchar(50), mobile_no varchar(15), email_id varchar(100), course varchar(50));
insert into pms values(101,"sunaina",'9891953953','hello@henryharvin.com','Google Analytics');
insert into pms values(102,"anamika",'9830029766','info@ctpdasia.com','Advanced SE0 Course');
insert into pms values(103,"deepti",'9620420270','info@indianleadershipacademy.com','Google Training');
insert into pms values(104,"ira",'8595798072','info@bafel.co.in','Advanced CEO course');
insert into pms values(105,"richa",'8071279841','info@theknowledgeacademy','Data analytics');
insert into pms values(106,"meena",'01147349723','info@skills.in','human anatomy');
select * from pms;
delete from pms where tid=1;
delete from pms where tid=2;
delete from pms where tid=3;


show databases;
create database KMS;
--  q1 create a trainers table with atleast 5 trainers details --
create table trainers(tid int primary key, tname varchar(50), mobile_no varchar(15), email_id varchar(100), course varchar(50));
insert into trainers values(101,"sunaina",'9891953953','hello@henryharvin.com','Google Analytics');
insert into trainers values(102,"anamika",'9830029766','info@ctpdasia.com','Advanced SE0 Course');
insert into trainers values(103,"deepti",'9620420270','info@indianleadershipacademy.com','Google Training');
insert into trainers values(104,"ira",'8595798072','info@bafel.co.in','Advanced CEO course');
insert into trainers values(105,"richa",'8071279841','info@theknowledgeacademy','Data analytics');
insert into trainers values(106,"meena",'01147349723','info@skills.in','human anatomy');
select * from trainers;



show databases;
create database KMS;
-- q2 create a student/ enrolle table with atleast 50 records who are being taught by same trainers of trainer table--
create table enrolle(sid int, sname varchar(50), email_id varchar(30), course varchar(55), tid int, foreign key(tid) references trainers(tid));
insert into enrolle values(1,"jones",'jones@gr.edu','Google analytics',101);
insert into enrolle values(2,"smith",'smith@gr.edu','Google analytics',101);
insert into enrolle values(3,"kevin",'kevin@gr.edu','Google Training',102);
insert into enrolle values(4,"madayan",'madayan@gr.edu','Advanced SEO course',103);
insert into enrolle values(5,"guldy",'guldy@gr.edu','Advanced SEO course',104);
insert into enrolle values(6,"lakshay",'lakshay@gr.edu','Data analytics',105);
insert into enrolle values(7,"isha",'isha@gr.edu','Human anatomy',106);
insert into enrolle values(8,"rea",'rea@gr.edu','Google analytics',106);
insert into enrolle values(9,"Aditi",'aditi@gr.edu','Human anatomy',105);
insert into enrolle values(10,"deepak",'deepak@gr.edu','Google training',105);
insert into enrolle values(11,"deepanshu",'deepanshu@gr.edu','Advanced SEO course',101);
insert into enrolle values(12,"sahil",'sahil@gr.edu','Advanced CEO course',102);
insert into enrolle values(13,"parth",'parth@gr.edu','Data analytics',103);
insert into enrolle values(14,"saksham",'saksham@gr.edu','Data analytics',103);
insert into enrolle values(15,"sumit",'sumit@gr.edu','Data analytics',104);
insert into enrolle values(16,"vanshika",'vanshika@gr.edu','Google analytics',104);
insert into enrolle values(17,"rounak",'rounak@gr.edu','Google training',104);
insert into enrolle values(18,"vansh",'vansh@gr.edu','Google training',106);
insert into enrolle values(19,"gurdeep",'gurdeep@gr.edu','Advanced CEO course',106);
insert into enrolle values(20,"harsh",'harsh@gr.edu','Advanced SEO course',102);
insert into enrolle values(21,"harshil",'harshil@gr.edu','Human anatomy',102);
insert into enrolle values(22,"arnav",'arnav@gr.edu','human anatomy',102);
insert into enrolle values(23,"raghav",'raghav@gr.edu','Human anatomy',101);
insert into enrolle values(24,"adil",'adil@gr.edu','Data analytics',104);
insert into enrolle values(25,"adeel",'adeel@gr.edu','Google training',105);
insert into enrolle values(26,"ankush",'ankush@@gr.edu','Advanced CEO course',106);
insert into enrolle values(27,"aryan",'aryan@gr.edu','Advanced SEO course',101);
insert into enrolle values(28,"arya",'arya@gr.edu','Data analytics',102);
insert into enrolle values(29,"angela",'angela@gr.edu','Google training',103);
insert into enrolle values(30,"abhi",'abhi@gr.edu','Google training',104);
insert into enrolle values(31,"jatin",'jatin@gr.edu','Google analytics',105);
insert into enrolle values(32,"jeena",'jeena@gr.edu','Google analytics',106);
insert into enrolle values(33,"kirti",'kirti@gr.edu','Google analytics',106);
insert into enrolle values(34,"anjali",'anajali@gr.edu','Human anatomy',105);
insert into enrolle values(35,"kanika",'kanika@gr.edu','Human anatomy',104);
insert into enrolle values(36,"bhooma",'bhooma@gr.edu','Advanced CEO course',103);
insert into enrolle values(37,"ritika",'ritika@gr.edu','Advanced SEO course',102);
insert into enrolle values(38,"riya",'riya@gr.edu','Google training',101);
insert into enrolle values(39,"rakhi",'rakhi@gr.edu','Google analytics',102);
insert into enrolle values (40,"ariv",'ariv@gr.edu','Google training',103);
insert into enrolle values(41,"danyal",'danyal@gr.edu','Google analytics',104);
insert into enrolle values(42,"ayesha",'ayesha@gr.edu','Human anatomy',105);
insert into enrolle values(43,"dhruv",'dhruv@gr.edu','Data analytics',106);
insert into enrolle values(44,"avi",'avi@gr.edu','Google training',101);
insert into enrolle values(45,"basit",'basit@gr.edu','Google training',106);
insert into enrolle values(46,"baseel",'baseel@gr.edu','Human anatomy',104);
insert into enrolle values(47,"ariana",'ariana@gr.edu','Human anatomy',103);
insert into enrolle values(48,"abella",'abella@gr.edu','Human anatomy',102);
insert into enrolle values(49,"neo",'neo@gr.edu','Data analytics',101);
insert into enrolle values(50,"lucky",'lucky@gr.edu','Google training',103);
select * from enrolle;
-- q3 how many students being taught by which trainer--
select tid, count(sid) from trainers join enrolle using (tid) group by tid;
-- q4 how many trainers do we have --
select  count(tid) from trainers;
-- q5 show me the name of unique course--
select count(distinct course) from enrolle;


-- project 1--
show databases;
create database project;
create table emp(employee_id int primary key , employee_name varchar(50), project_id int, department varchar(50), salary int, experience int, projects varchar(50));
insert into emp values(1,"mohit",2,"IT",50000,2,"Project A");
insert into emp values(2,"reema",3,"SALES",30000,1,"Project B");
insert into emp values(3,"harish",2,"IT",50000,2,"Project A");
insert into emp values(4,"paras",2,"IT",95000,4,"Project A");
insert into emp values(5,"shivam",1,"HR",77000,4,"Project C");
insert into emp values(6,"harsh",3,"SALES",30000,1,"Project B");
insert into emp values(7,"dorthy",1,"HR",65000,3,"Project C");
insert into emp values(8,"archana",3,"SALES",18000,null,"Project B");
insert into emp values(9,"vishal",3,"SALES",18000,null,"Project B");
insert into emp values(10,"shubendu",1,"HR",150000,8,"Project C");
insert into emp values(11,"drigga",4,"Finance",48000,3,"Project D");
insert into emp values(12,"manider",2,"IT",145000,7,"Project A");
insert into emp values(13,"nithish",4,"Finance",115000,6,"Project D");
insert into emp values(14,"shubham",4,"Finance",74000,5,"Project D");
insert into emp values(15,null,null,"Finance",17000,null,null);
insert into emp values(16,"prince",null,null,null,null,null);
select * from emp;

create database project;
create table per(performance_id int primary key, employee_id int, project_id int,performance_rating decimal(10,2), feedback text,foreign key(employee_id) references emp(employee_id));
insert into per values(1,1,2,9,"EXCELLENT");
insert into per values(2,2,3,8,"GOOD");
insert into per values(3,3,2,9,"EXCELLENT");
insert into per values (4,4,2,9.3,"EXCELLENT");
insert into per values(5,5,1,9,"EXCELLENT");
insert into per values(6,6,3,8.5,"GOOD");
insert into per values(7,7,1,8,"GOOD");
insert into per values(8,8,3,5,"AVERAGE");
insert into per values(9,9,3,6,"AVERAGE");
insert into per values(10,10,1,9.4,"EXCELLENT");
insert into per values(11,11,4,7,"AVERAGE");
insert into per values(12,12,2,9.5,"AVERAGE");
insert into per values(13,13,4,8.5,"GOOD");
insert into per values(14,14,4,8,"GOOD");
insert into per values(15,15,null,null,"Worst");
insert into per values(16,16,null,null,"Worst");
select * from per;
-- easy level, medium level--
-- q1--
insert into emp values(22,"john",1,"IT",50000,2,"Project A");
-- q2--
alter table emp add column mananger_id int;
-- q3--
select sum(salary) as total_salary_expenditure from emp;
-- q4--
select department,count(*) as num_employees from emp group by department;
-- q5--
select * from emp where experience >5;
-- q6--
select* from emp where salary>(select avg(salary) from emp);
-- q7--
insert into emp(employee_id,projects)values(25,"Project B");
-- q8--
select department,avg(experience) from emp group by department;
-- q9--
alter table emp modify column salary decimal;
-- q10--
select max(salary) from emp where department="IT";
-- Q1--
select department,count(*) from emp group by department;
select department, max(salary) from emp group by department;
-- q2--
select* from emp where salary>(select max(salary) from emp);
-- q3--
alter table per add constraint fk_project_id foreign key (project_id) references emp(project_id);
-- q4--
set sql_safe_updates=0;
delete from emp where salary< 40000 and department ="IT";
-- q5--
select emp.employee_name, emp.department, per.performance_rating from emp join per on emp.employee_id=per.employee_id;
-- q6--
select emp.employee_name, per.feedback from emp join per on emp.employee_id=per.employee_id where emp.employee_id between 11 and 16;
-- q7--
select emp.employee_name from emp join per on emp.employee_id=per.employee_id where salary>50000 and performance_rating>8 and department="sales";
-- q8--
select employee_id from per where feedback="average" and project_id=3;
-- q10--
select performance_id from per where performance_rating >6 and performance_rating<9 and feedback="good";

-- project 2--
show databases;
create database projects;
create table employee(employee_id int primary key, performance_id int,manager varchar(50), manager_id int, employee_name varchar(50),project_id int,department varchar(50), salary decimal (10,2), experience int, projects text);
insert into employee values(1,1,"aizen",1,"mohit",2,"IT",50000,2,"Project A");
insert into employee values(2,2,"ichigo",2,"reema",3,"SALES",30000,1,"Project B");
insert into employee values(3,3,"aizen",1,"harish",2,"IT",50000,2,"Project A");
insert into employee values(4,4,"aizen",1,"paras",2,"IT",95000,4,"Project A");
insert into employee values(5,5,"shunsui",3,"shivam",1,"HR",77000,4,"Project C");
insert into employee values(6,6,"ichigo",1,"harsh",3,"SALES",30000,1,"Project B");
insert into employee values(7,7,"shunsui",3,"dorthy",1,"HR",65000,3,"Project C");
insert into employee values(8,8,"ichigo",2,"archana",3,"SALES",18000,null,"Project B");
insert into employee values(9,9,"ichigo",2,"vishal",3,"SALES",18000,null,"Project B");
insert into employee values(10,10,"shunsui",3,"shubendu",3,"HR",150000,8,"Project C");
insert into employee values(11,11,"rukia",4,"drigga",4,"FINANCE",48000,3,"Project D");
insert into employee values(12,12,"aizen",1,"manider",2,"IT",145000,7,"Project A");
insert into employee values(13,13,"rukia",4,"nithish",4,"FINANCE",115000,6,"Project D");
insert into employee values(14,14,"rukia",4,"shubham",4,"FINANCE",74000,5,"Project D");
insert into employee values(15,15,null,null,null,null,"FINANCE",17000,null,null);
insert into employee values(16,16,null,null,"prince",null,null,null,null,null);
select * from employee;

show databases;
create database projects;
create table performance(performance_id int primary key, employee_id int, manager_id int,project_id int,performance_rating int,feedback text);
insert into performance values(1,1,1,2,9,"EXCELLENT");
insert into performance values(2,2,2,3,8,"GOOD");
insert into performance values(3,3,1,2,9,"EXCELLENT");
insert into performance values(4,4,1,2,9.3,"EXCELLENT");
insert into performance values(5,5,3,1,9,"EXCELLENT");
insert into performance values(6,6,1,3,8.5,"GOOD");
insert into performance values(7,7,3,1,8,"GOOD");
insert into performance values(8,8,2,3,5,"AVERAGE");
insert into performance values(9,9,2,3,6,"AVERAGE");
insert into performance values(10,10,3,1,9.4,"EXCELLENT");
insert into performance values(11,11,4,4,7,"AVERAGE");
insert into performance values(12,12,1,2,9.5,"AVERAGE");
insert into performance values(13,13,4,4,8.5,"GOOD");
insert into performance values(14,14,4,4,8,"GOOD");
insert into performance values(15,15,null,null,null,"WORST");
insert into performance values(16,16,null,null,null,"WORST");
select* from performance;
-- advance level--
-- q1--
select employee.department,avg(performance.performance_rating)as avg_performance_rating from employee join performance on employee.employee_id=performance.employee_id group by employee.department;
-- q2--
select* from employee join performance on employee.employee_id=performance.employee_id;
select employee.employee_name,employee.department,performance.performance_rating from employee join performance on employee.employee_id=performance.employee_id where performance.performance_rating>8 and employee.project_id=1;
-- q4--
select employee.employee_name,performance.performance_rating from employee join performance on employee.employee_id=performance.employee_id join(select department,avg(performance_rating) as avg_performance_rating from employee join performance on employee.employee_id=performance.employee_id group by department) where performance.performance_rating>Avgperf.average_performance_rating;
-- q5--
select employee.department,avg(performance.performance_rating)from employee join performance on employee.employee_id=performance.employee_id group by department having avg(performance.performance_rating)>7;
-- q9--
select employee_name,department from employee where project_id>2;


									








