tugas-1zxz
DESCRIPTION
zxzTRANSCRIPT
TUGAS-1A. BUATLAH TABEL EMPLOYEES dan TABEL DEPARTMENTS, dengan sruktur sebagai berikut :
B. KETIKAN DATA BERIKUT KE TABEL EMPLOYEES dan TABEL DEPARTMENTS BERIKUT INI:
Tugas : Sebutkan untuk apa perintah sql di bawah dan tampilkan hasilnya dari perintah tersebut ? Tugas di email ke [email protected] Tugas dikerjakan maksimal 4 kelompok
1. select last_name, salary,12*(salary+100) from employees;
2. select last_Name "MYName", salary*10 "Akumulasi Salary" from employees;
3. select last_Name || ' Code Is ' ||job_id from employees;
4. select Department_Name || q'[,it's assigned manager id: ]' ||manager_Id as "Department and Manager" from departments;
5. select employees_id, First_name,salary, department_id from employees where department_id=50;
6. select last_name,salary from employees where salary between 10000 and 24000;
7. select employees_id, last_name,salary, manager_id from employees where manager_id in(100,101,102);
8. select last_name from employees where last_name LIKE 'K%';
9. select last_name from employees where last_name LIKE '_o%';
10. select employees_id,last_name, job_id, salary from employees where salary >=10000 and job_id LIKE '%VP%';
11. select employees_id,last_name, job_id, salary from employees where salary >=10000 and job_id LIKE '%VP%';
12. select last_name,Job_idfrom employeeswhere job_id not in ('ST_MAN','AD_VP')
13. select last_name, job_id, salaryfrom employeeswhere job_id=IT_PROGor job_id='ST_MAN'and salary >17000;
14. select last_name, salary, mod(salary,1000)from employeeswhere job_id='AD_VP';
15. select last_name,job_id,salary, CASE job_id when 'IT-PROG' then 1.10*salary when 'AD-VP' then 1.15*salary when 'ST-MAN' then 1.20*salary else salary end "REVISED_SALARY" from employees
15. select to_char(salary,'$99,999.00')salary from employees where last_name='King';
16. select DEPARTMENT_ID,MAX(SALARY) from employees GROUP BY dEPARTMENT_ID HAVING MAX(SALARY)>10000;
17. Select MAX(AVG(salary)) from employees Group by department_id;
18. Select e.employees_id, e.last_name,e.department_id, d.department_id, d.location_id from employees e JOIN departments d ON (d.department_id=d.department_id);19, Select e.last_name emp, m.last_name mgrfrom employees e JOIN employees mON (e.manager_id=m.employees_id);
20. SELECT e.employees_id, e.last_name, e.department_id d.department_id, d.location_id FROM employees e JOIN departments d ON (d.department_id = d.department_id) AND d.manager_id= 200;