Scribd
Upload
9 views5 pages

SQL Queries for Employee and Job Tables

The document contains SQL queries related to two tables: Employee and Job. It includes commands for creating tables, inserting records, and various queries to display, update, and delete data based on specific conditions. The queries cover operations such as counting records, summing salaries, and filtering employee names based on patterns.

Uploaded by

deveshsingh582009
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
9 views5 pages

SQL Queries for Employee and Job Tables

The document contains SQL queries related to two tables: Employee and Job. It includes commands for creating tables, inserting records, and various queries to display, update, and delete data based on specific conditions. The queries cover operations such as counting records, summing salaries, and filtering employee names based on patterns.

Uploaded by

deveshsingh582009
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Practical File

MY SQL

Question:- Consider the following table and perform the queries related to them.

Table: Employee

Emp_ID Name Sales Jobid


E1 Sumit Sinha 1100000 102
E2 Vijay singh tomar 1300000 101
E3 Ajay Rajpal 1400000 103
E4 Rubal 1250000 102
E5 Sunil Singh 1450000 103

Table:-Job

Jobid Jobtitle salary


101 President 200000
102 Vice President 125000
103 Administration 80000
104 Accounting Manager 70000
105 Accountant 65000
106 Sales Manager 80000

Write SQL Queries for the following:

1. Write the command for create table for Job and Employee.
2. Write insert query for all the records of Job table.
3. Write insert query for all the records of Employee table.
4. To display employee id, name of employees, job ids with corresponding job title.
5. To display name of employees, sales and job title who have achieved sales more
than 1300000.
6. To display name and corresponding job titles of those employees whose name
ends with a.
7. Write SQL command to change the jobid to 104 of the employee with employee id
E4 in the table.
8. Find the number of records in a Employee table using aggregate function.
9. Display the total salary of Employee using aggregate function.
10. Display the name of Employee in descending order.
11. Display the jobtitle and the no of employees associated with these jobtitle.
12. Display the maximum sales in Employee table.
13. Update the salary of “Accountant” to 85000.
14. Display the record of those Employee whose name starts with any letter and
second letter must be ‘u’ and other letter can be anything.
15. Delete the record of Job whose jobid is 106.
Ans 4)
select emp_id,name,[Link],jobtitle from employee e,job j where [Link]=[Link];

Ans 5) select name,sales,jobtitle from employee e,job j where [Link]=[Link] and sales>1300000;

Ans 6) select name,jobtitle from employee e,job j where [Link]=[Link]


and name like '%a';

Ans 7. update employee set jobid=104 where emp_id='E4';


Ans 8. select count(*) as "Total Records" from employee;

Ans 9. select sum(salary) as "Total Salary" from employee e,job j where [Link]=[Link];

Ans 10. select name from employee order by name desc;

Ans 11. select jobtitle,count(*) "No of Employee" from employee e,job j where [Link]=[Link] group
by(jobtitle);
Ans 12. select max(sales) from employee;

Ans 13. update job set salary=85000 where jobtitle="Accountant";

Ans 14. select name from employee where name like '_u%';

Ans 15. delete from job where jobid=106;

You might also like