Informatics Practices
Related Questions
Are UNION and UNION ALL the same ? Why ?
What does INTERSECT do ?
In a Database, there are two tables given below :
Table : EMPLOYEE
EMPLOYEEID NAME SALES JOBID E1 SUMIT SINHA 1100000 102 E2 VIJAY SINGH TOMAR 1300000 101 E3 AJAY RAJPAL 1400000 103 E4 MOHIT RAMNANI 1250000 102 E5 SHAILJA SINGH 1450000 103 Table : JOB
JOBID JOBTITLE SALARY 101 President 200000 102 Vice President 125000 103 Administration Assistant 80000 104 Accounting Manager 70000 105 Accountant 65000 106 Sales Manager 80000 Write SQL Queries for the following :
- To display employee ids, names of employees, job ids with corresponding job titles.
- To display names of employees, sales and corresponding job titles who have achieved sales more than 1300000.
- To display names and corresponding job titles of those employees who have 'SINGH' (anywhere) in their names.
- Identify foreign key in the table EMPLOYEE.
- Write SQL command to change the JOBID to 104 of the EMPLOYEE with ID as E4 in the table 'EMPLOYEE'.
Show the average salary for all departments with more than 3 people for a job.
Table: Empl
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 8369 SMITH CLERK 8902 1990-12-18 800 NULL 20 8499 ANYA SALESMAN 8698 1991-02-20 1600 300 30 8521 SETH SALESMAN 8698 1991-02-22 1250 500 30 8566 MAHADEVAN MANAGER 8839 1991-04-02 2985 NULL 20 8654 MOMIN SALESMAN 8698 1991-09-28 1250 1400 30 8698 BINA MANAGER 8839 1991-05-01 2850 NULL 30 8839 AMIR PRESIDENT NULL 1991-11-18 5000 NULL 10 8844 KULDEEP SALESMAN 8698 1991-09-08 1500 0 30 8882 SHIAVNSH MANAGER 8839 1991-06-09 2450 NULL 10 8886 ANOOP CLERK 8888 1993-01-12 1100 NULL 20 8888 SCOTT ANALYST 8566 1992-12-09 3000 NULL 20 8900 JATIN CLERK 8698 1991-12-03 950 NULL 30 8902 FAKIR ANALYST 8566 1991-12-03 3000 NULL 20 8934 MITA CLERK 8882 1992-01-23 1300 NULL 10