Introduction to Databases and SQL

SQL Answers for MySQL

3.1

describe emp;

describe dept;

describe salgrade;

3.2

select * from emp;

select * form dept;

select * from salgrade;

No of departments: 4

No of employees: 14

SMITH's salary is 800

SMITH works in department 20, the research department

5.1

select deptno, loc from dept;

5.2

select * from dept order by loc;

5.3

select ename, deptno from emp order by deptno;

5.4

select ename,mgr,job from emp order by mgr,job;

6.1

select ename, 12*sal + comm from emp order by ename;

6.2

select ename, 12*sal + ifnull(comm,0) from emp order by ename;

6.3

select sqrt(3*3 + 4*4);

6.4

select ename,

concat(substring(job from 1 for 1),

substring(job from length(job) for 1)

)

from emp;

7.1

select * from emp where deptno=20;

7.2

select * from emp where mgr = 7698;

7.3

select * from emp where job = 'ANALYST';

7.4

select * from emp where mgr is null;

7.5

select * from emp where job='ANALYST' and deptno=10;

7.6

select * form emp where sal > 2000;

7.7

select * from emp where job = 'CLERK' and sal between 900 and 1000;

7.8

select * from emp where NOT (deptno = 20);

or

select * from emp where deptno <> 20;

7.9

select * from emp where ename like '%S%';

7.10

select * from emp where ename like 'J%';

7.11

select * from emp where ename like '%S';

7.12

select distinct mgr from emp;

8.1

only rows where the deptno from emp and deptno from dept are meaningful

8.2

select ename, loc from emp, dept where emp.deptno = dept.deptno;

8.3

select ename, dname from emp, dept where emp.deptno = dept.deptno and sal > 1000;

8.4

select * from emp,salgrade;

8.5

select * from emp,salgrade where sal between losal and hisal;

8.6

select e.ename, m.ename from empe , emp m where e.mgr=m.empno

and e.deptno=20;

8.7

select e.ename, m.ename, d.dname

from emp e , emp m , dept d

where e.mgr=m.empno

and m.deptno = d.deptno;

8.8

select e.ename, m.ename, ed.dname, md.dname

from emp e , emp m , dept ed , dept md

where e.mgr=m.empno

and m.deptno = md.deptno

and e.deptno = ed.deptno;

9.1

select min(ename) from emp;

or in MySQL

select ename from emp order by ename limit 1;

9.2

select deptno, count(*), avg(sal), min(sal) , max(sal)

from emp

group by deptno;

9.3

select mgr, count(*), avg(sal), min(sal) , max(sal)

from emp

group by mgr;

9.4

select mgr, deptno from emp group by mgr;

10.1

select * from emp

where job = 'ANALYST'

and hiredate > '96-04-30';

10.2

select round((to_days(current_date) - to_days(hiredate)) / 7)

from emp;

10.3

select deptno, avg((to_days(current_date) - to_days(hiredate)) / 365)

from emp

group by deptno;

11.1

For Oracle only:

select * from emp where deptno=&TheDeptNo;

11.2

select * from emp, dept

where emp.dptno = dept.deptno

and emp.deptno = &TheDeptno;

12

Database should be successfully installed in your own MySQL account.

13.1

insert into emp values(9000,'WALLACE','LECTURER', 7838,current_date,2500,null,10);

13.2

update emp set sal = sal + 1000 where empno=9000;

13.3

delete from emp where empno = 9000;