KnowledgeBoat Logo

Computer Science

In a Database, there are two tables given below :

Table : EMPLOYEE

EMPLOYEEIDNAMESALESJOBID
E1SUMIT SINHA1100000102
E2VIJAY SINGH TOMAR1300000101
E3AJAY RAJPAL1400000103
E4MOHIT RAMNANI1250000102
E5SHAILJA SINGH1450000103

Table : JOB

JOBIDJOBTITLESALARY
101President200000
102Vice President125000
103Administration Assistant80000
104Accounting Manager70000
105Accountant65000
106Sales Manager80000

Write SQL Queries for the following :

  1. To display employee ids, names of employees, job ids with corresponding job titles.
  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 foreign key in the table EMPLOYEE.
  5. Write SQL command to change the JOBID to 104 of the EMPLOYEE with ID as E4 in the table 'EMPLOYEE'.

SQL Joins & Grouping

8 Likes

Answer

1.

SELECT EMPLOYEE.EMPLOYEEID, EMPLOYEE.NAME, EMPLOYEE.JOBID, JOB.JOBTITLE
FROM EMPLOYEE, JOB
WHERE EMPLOYEE.JOBID = JOB.JOBID;
Output
+------------+-------------------+-------+--------------------------+
| EMPLOYEEID | NAME              | JOBID | JOBTITLE                 |
+------------+-------------------+-------+--------------------------+
| E1         | SUMIT SINHA       |   102 | VICE PRESIDENT           |
| E2         | VIJAY SINGH TOMAR |   101 | PRESIDENT                |
| E3         | AJAY RAJPAL       |   103 | ADMINISTARTION ASSISTANT |
| E4         | MOHIT RAMNANI     |   102 | VICE PRESIDENT           |
| E5         | SHAILJA SINGH     |   103 | ADMINISTARTION ASSISTANT |
+------------+-------------------+-------+--------------------------+

2.

SELECT EMPLOYEE.NAME, EMPLOYEE.SALES, JOB.JOBTITLE
FROM EMPLOYEE, JOB 
WHERE EMPLOYEE.JOBID = JOB.JOBID
AND EMPLOYEE.SALES > 1300000;
Output
+---------------+---------+--------------------------+
| NAME          | SALES   | JOBTITLE                 |
+---------------+---------+--------------------------+
| AJAY RAJPAL   | 1400000 | ADMINISTARTION ASSISTANT |
| SHAILJA SINGH | 1450000 | ADMINISTARTION ASSISTANT |
+---------------+---------+--------------------------+

3.

SELECT EMPLOYEE.NAME, JOB.JOBTITLE
FROM EMPLOYEE, JOB 
WHERE EMPLOYEE.JOBID = JOB.JOBID
AND EMPLOYEE.NAME LIKE '%SINGH%';
Output
+-------------------+--------------------------+
| NAME              | JOBTITLE                 |
+-------------------+--------------------------+
| VIJAY SINGH TOMAR | PRESIDENT                |
| SHAILJA SINGH     | ADMINISTARTION ASSISTANT |
+-------------------+--------------------------+

4. In the given tables, EMPLOYEE and JOB, the JOBID column in the EMPLOYEE table is a foreign key referencing the JOBID column in the JOB table.

5.

UPDATE EMPLOYEE
SET JOBID = 104
WHERE EMPLOYEEID = 'E4';
Output
SELECT * FROM EMPLOYEE ;

+------------+-------------------+---------+-------+
| EMPLOYEEID | NAME              | SALES   | JOBID |
+------------+-------------------+---------+-------+
| E1         | SUMIT AINHA       | 1100000 |   102 |
| E2         | VIJAY SINGH TOMAR | 1300000 |   101 |
| E3         | AJAY RAJPAL       | 1400000 |   103 |
| E4         | MOHIT RAMNANI     | 1250000 |   104 |
| E5         | SHAILJA SINGH     | 1450000 |   103 |
+------------+-------------------+---------+-------+

Answered By

4 Likes

Related Questions