Question

23 In a database , there are two tables given below : (6*1=6)

Table : Employee

EMPLOYEEID NAME SALES JOBID

E1 SUMIT SINHA 1100000 102

E2 VIJAY SINGH 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 Admin Manager 80000

104 Accounting Manager 70000

105 Accountant 65000

106 Sales Manager 80000

Write SQL queries:

1) To display employee ids, names of employees , job ids with

corresponding job title

2) To display names of employees, sales and corresponding job titles

who have achieved sales more than 1300000.

3) To display names and corresponding job titles of those employees

who have „SINGH‟ (anywhere) in their names.

4) Identify PRIMARY KEY AND foreign key in the tables EMPLOYEE

and Job tables.

5) Write SQL command to change the JOBID to 104 of the EMPLOYEE

with ID as E4 in the table „EMPLOYEE‟.

6) Show the average salary for all the departments with more than 3

people for a job.​

Answer 1

Answer:

select employees, id, name, employee, job tittle from employee,job where employee job id=.job id;