Scribd Logo
Upload

Welcome to Scribd!

  • 5 views

    sql2

    Uploaded by

    bump.stillarya

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd

    sql2

    Uploaded by

    bump.stillarya
    5 views7 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    TXT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    5 views7 pages

    sql2

    Uploaded by

    bump.stillarya

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    You are on page 1of 7

    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;

    You might also like