select * from EMP where SAL > 1500 and EMPNO in (select MGR from EMP);

select * from EMP where SAL between 1200 and 2000;

select * from EMP where SAL in (1600, 800, 1900);

select * from EMP where ENAME REGEXP '[Rr].$';

select * from EMP where ENAME REGEXP '^[Aa].*[Nn]$';

-- another one

select * from emp where sal > 1251 and deptno = 30;

select * from emp where sal between 1250 and 3000;

select * from emp where sal between 1251 and 2999;

select * from emp where sal in (3000, 1250, 2500);

select * from emp where ename = 'SMITH';

select * from EMP where ENAME REGEXP '^[Ss]';

select * from EMP where ENAME REGEXP '[Ss]$';

select * from emp where ename regexp '^.l';

select * from emp where ename regexp '^[Aa].*[Ll].*[Nn]$';

select * from emp where ename regexp '^[Aa].[Bb].*[Pp].$';

select * from emp where ename regexp '^[AaSsWw]';

-- another one

select job, max(sal), min(sal) from emp group by job;

select count(*) from emp where comm is null;

select sum(sal) from emp where deptno = 10;

select deptno, job, max(sal), min(sal), avg(sal) from emp group by deptno, job
Order by deptno asc;

select deptno, max(sal) from emp group by deptno having deptno > 15 order by deptno
asc;

select deptno, sum(sal) from emp group by deptno having sum(sal) > 3000;

select deptno, count(*) from emp group by deptno having count(*) > 4;

select job, count(*) from emp where sal> 2000 group by job;

select lower(ename) from emp;

select lpad(ename, 15, ' '), job from emp;

select mgr, max(sal) , min(sal) , avg(sal) from emp group by mgr;

select deptno, sum(sal +ifnull(comm, 0)), avg(sal +ifnull(comm, 0)) from emp where
sal > 2000 group by deptno having deptno in (10, 20);

select * from emp where extract(month from hiredate) = 8 and extract(year from
hiredate) = 1980 and sal between 1500 and 2500;

select * from emp where extract(month from hiredate) in (8, 5, 12);

select ename, date_format(hiredate, '%d/%m/%y') from emp where job='clerk' and comm
is not null;

select concat(substring(ename, 3, 5), right(job, 2)) as EMPCODE, EMPNO, ENAME, JOB

from emp;

select concat('$',format(ifnull(comm, 0), 2)) as comm_formatted, ename from emp

where ename regexp '^A.*N$';

select empno, ename, comm,

case
when comm >= 600 then 'Excellent keep it up'
when comm < 600 and comm is not null then 'good'
else 'need improvement'
end
as remark from emp;

select empno, ename, deptno,

case
when deptno = 10 then 'Hr'
when deptno = 20 then 'admin'
when deptno = 30 then 'accounts'
else 'purchase'
end
as deptname from emp;

create table mydept_DBDA

(
deptid int(4),
deptname varchar(20) not null unique,
dloc varchar(20),
constraint pk_deptid primary key (deptid)
);
insert into mydept_DBDA values(30,'purchase','Mumbai');
create table myemployee (
empno int(5) primary key,
fname varchar(15) not null,
mname varchar(15),
lname varchar(15) not null,
sal decimal(9,2) check (sal >= 1000),
doj date,
passportnum varchar(15) unique,
deptno int,
constraint fk_deptno foreign key (deptno) references mydept_DBDA(deptid) on
delete cascade
);

create table student

(
sid int primary key,
sname varchar(25)
);
create table course
(
cid int primary key,
cname varchar(25)
);
create table marks
(
studid int,
courseid int,
marks decimal(7, 2),
constraint pk_sidcid primary key (studid, courseid),
constraint fk_studid foreign key (studid) references student(sid),
constraint fk_courseid foreign key (courseid) references course(cid)
);
insert into student values (1, 'Arya') ,(2, 'megha');
insert into course values (1, 'computers'), (2, 'robotics'), (3, 'electronics');
insert into marks values (1, 1, 99), (1, 3, 98), (2, 1, 95), (2, 2, 97);

alter table emp

add constraint pk_emp
primary key (empno);
alter table dept
add constraint pk_dept
primary key (deptno);
alter table salgrade
add constraint pk_salgrade
primary key (grade);
alter table emp
add constraint fk_emp_deptno
foreign key (deptno) references dept(deptno);
alter table emp
add netsal double default 1000;

update emp
set sal = sal + (sal + ifnull(comm, 0))*0.15;
update emp
set job = 'manager', mgr = 7777
where deptno = 10;'

update emp
set job = 'senior clerk'
where ename = 'smith';

update emp
set sal = sal + 0.15*(sal)
where comm is not null and comm != 0;

select * from emp

where sal > (select sal from emp where ename = 'smith')

select * from emp

where deptno = (select deptno from emp where ename='smith');

select * from emp

where sal between
(select sal from emp where ename='revati') and (select sal from emp where ename =
'rajan');

DELETE FROM emp

WHERE deptno = (
SELECT deptno
FROM (SELECT deptno FROM emp WHERE ename = 'Allen') AS temp
);

set sql_safe_updates = 0;

update emp
set sal = (select sal from (select sal from emp where ename='Miller') as temp_sal)
where ename = 'Allen';

update emp
set sal = (select sal from (select sal from emp where ename='Miller') as temp_sal)
where deptno = (select deptno from (select deptno from emp where ename='Wall') as
temp_deptno);

select * from emp

where sal > (select min(sal) from emp where ename in ('smith', 'allen'));

select * from emp

where sal > (select avg(sal) from emp group by deptno having deptno = 10);

select * from emp

where sal > (select avg(sal) from emp where deptno = (select deptno from emp where
ename='allen'));

select * from emp

where deptno = (select deptno from dept where dname = 'purchase');

select * from emp e1 where

e1.sal < (select avg(sal) from emp e2 where e2.deptno = e1.deptno);

select * from emp e1

where sal > (select avg(sal) from emp e2 where e2.job = e1.job);

select ename, (select dname from dept where dept.deptno = emp.deptno) from emp;

select empno, ename, sal, comm, (select grade from salgrade where emp.sal between
salgrade.losal and salgrade.hisal) as sal_grade from emp;

select empno, ename, mgr, (select ename from emp e2 where e2.empno = e1.mgr) as
mgrname from emp e1;

------------

create table salesman

(
sid int primary key,
sname varchar(25),
address text
);

create table category

(
cid int primary key,
cname varchar(25),
category_description text
);

create table product

(
pid int primary key,
pname varchar(25),
price double(9,2),
qty int,
cid int,
sid int,
constraint fk_cid foreign key (cid) references category(cid),
constraint fk_sid foreign key (sid) references salesman(sid)
);

-----------------------------------

select p.pname, c.cname, s.sname

from product p
join category c on c.cid = p.cid
join salesman s on s.sid = p.sid;

select p.pname, s.sname

from product p
join salesman s on s.sid = p.sid
where s.address = 'Pune, Maharashtra';

select p.pname, c.cname

from product p
join category c on c.cid = p.cid;

-----------------------------------------------

create table faculty

(
fid int primary key,
fname varchar(25),
sp_skill1 varchar(10),
sp_skill2 varchar(10)
);

create table room

(
rid int primary key,
rname varchar(25),
rloc text
);
create table courses
(
cid int primary key,
cname varchar(25),
rid int,
fid int,
constraint fk_rid foreign key (rid) references room(rid),
constraint fk_fid foreign key (fid) references faculty(fid)
);

insert into faculty values (10, 'kjzhcjhz', 'a', 'b'), (11, 'sdd', 'x', 'z'),
(12, 'lksjk', 'a', 'x'), (13, 'ksdjlkj', 'a', 'b');
insert into room values (100, 'jasmin', '1st floor'), (101, 'Rose', '2nd floor'),
(105, 'Lotus', '1st floor'), (103, 'Mogra', '1st floor');

insert into courses values (121, 'DBDA', 100, 10), (131, 'DAC', 101, null),
(141, 'DITISS', null, null), (151, 'DIOT', 105, 12);

-- 1
select c.cid, cname
from courses c
where c.rid is null;

-- 2
select f.fid, f.fname
from faculty f
left join courses c on f.fid = c.fid
where c.fid is null;

-- 3
select rid, rname, rloc,
case
when rid in (select rid from courses) then 'not available'
else 'available'
end as availability
from room;

-- 1,2 / 4
select r.rid, r.rname
from room r
left join courses c on r.rid = c.rid
where c.rid is null;

-- 5
select f.fname, c.cname
from faculty f
join courses c on c.fid = f.fid
where f.sp_skill1 = 'database' or f.sp_skill2 = 'database';

-- 6
select c.cid, c.cname, f.fname, r.rname, r.rloc
from courses c
left join faculty f on c.fid = f.fid
left join room r on c.rid = r.rid;

-----------------------------------------------

use iacsd0925;

create table salesman

(
sid int primary key,
sname varchar(25),
city varchar(10)
);
create table category
(
cid int primary key,
cname varchar(25),
description text
);
create table product
(
proid int primary key,
qty int,
price double,
catid int,
sid int,
constraint fk_catid foreign key (catid) references category(cid),
constraint fk_sid foreign key (sid) references salesman(sid)
);

alter table product

add pname varchar(25)
after proid;

insert into salesman values (11, 'Rahul', 'Pune'), (12, 'Kirti', 'Mumbai'), (13,
'Prasad', 'Nashik')
,(14, 'Arnav', 'Amaravati');
insert into category values (1, 'chips', 'very crunchy'), (2, 'choclate', 'very
chocolaty'),
(3, 'snacks', 'yummy'), (4, 'cold drinks', 'Thanda thanda cool cool');
insert into product values (123, 'lays', 30, 30.00, 1, 12), (111, 'pepsi', 40,
50.00, 4, 11),
(134, 'nachos', 50 ,50.00, 1 ,12), (124, 'dairy milk', 40, 60.00, 2, 14),
(125,'pringles', 40, 60.00, 1, 14);

select * from product where catid = (select cid from category where cname = 'chips'
limit 1);

select * from product

where
sid = (select sid from salesman where sname='kirti' limit 1);

select s.sid, s.sname from salesman s

left join product p on p.sid = s.sid
where p.sid is null;

select c.cid ,c.cname

from category c
left join product p on p.catid = c.cid
where p.catid is null;

select * from product

where catid is null;

select * from salesman

where city regexp '^[PN]';

alter table salesman

add credit_limit double;