Answer to Question #267115 in Databases | SQL | Oracle | MS Access for saurav kumar

Question

Q1. B. Consider the Following Table Employee(emp_id, name,dept_id, email, mobile_number, join_date,salary, mgr_id) 1) Query to display the name and salary for all the employees whose salary is not in the range of Rs. 10,000 and 50,000. 2) Make sure that the EmailId of each employee is common and unique to that employee only. 3) Query to display the name and Department number of all the Employees in department number 8 and 12 in an alphabetical order by name. 4) Write a query to display the name of employees who have an ‘a’ and ‘e’ in their name. 5) Write a query to display the name, job, salary of all the employees whose job is clerk or teacher and whose salary is not equal to 15,000.

Answer 1

SOLUTION TO THE ABOVE QUESTION

Q1. B. Consider the Following Table Employee(emp_id, name,dept_id, email,
 mobile_number, join_date,salary, mgr_id) 


1) Query to display the name and salary for all the employees whose salary is not in 
the range of Rs. 10,000 and 50,000.


ANSWER.


SELECT name, salary FROM Employee
WHERE salary < 10000 OR salary > 50000;


 2) Make sure that the EmailId of each employee is common and unique to that employee only. 


ANSWER.


ALTER TABLE Employee
ADD UNIQUE (email);


3) Query to display the name and Department number of all the Employees in department number
 8 and 12 in an alphabetical order by name.


ANSWER.


SELECT name, dept_id FROM Employee WHERE dept_id='8' OR dept_id ='12' ORDER BY name ASC;


 4) Write a query to display the name of employees who have an ‘a’ and ‘e’ in their name.


ANSWER.


SELECT name FROM Employee
WHERE name like '%a%' OR name like '%e%';


 5) Write a query to display the name, job, salary of all the employees whose job is clerk 
or teacher and whose salary is not equal to 15,000.


ANSWER.


SELECT name, job, salary FROM Employee 
WHERE job ='clerk' OR job='teacher' AND salary !='15000';