DBMS - Restricting - Sorting

  • 1. 	Write a query to display the name (first_name, last_name) and salary for all 
    	employees whose salary is not in the range $10,000 through $15,000. 
    
    					    						

    	SELECT first_name, last_name, salary
    	FROM employees
    	WHERE salary NOT BETWEEN 10000 AND 15000;
    		
    				OR
    				
    	SELECT first_name, last_name, salary
    	FROM employees
    	WHERE salary<10000 OR salary>15000;
    						

  • 2.	Write a query to display the name (first_name, last_name) and department_ID of all 
    	employees in departments 30 or 100 in ascending order. 
    
    					    						

    	SELECT first_name, last_name, department_id
    	FROM employees
    	WHERE department_id IN (30, 100)
    	ORDER BY  department_id  ASC;
    
    				OR
    	
    	SELECT first_name, last_name, department_id
    	FROM employees
    	WHERE department_id = 30 OR department_id = 100
    	ORDER BY  department_id  ASC;
    						

  • 3. 	Write a query to display the name (first_name, last_name) and salary for all employees 
    	whose salary is not in the range $10,000 through $15,000 and are in department 30 or 100. 
    
    					    						

    	SELECT first_name, last_name, salary, department_id
    	FROM employees
    	WHERE salary NOT BETWEEN 10000 AND 15000 
    	AND department_id IN (30, 100);
    						

  • 4. 	Write a query to display the name (first_name, last_name) and hire date for all employees 
    	who were hired in 1987. 
    
    					    						

    	SELECT first_name, last_name, hire_date 
    	FROM employees 
    	WHERE YEAR(hire_date)  LIKE '1987';
    	
    			OR
    			
    	select A,B,C from emps where hired_date like '1987%'
    						

  • 5. 	Write a query to display the first_name of all employees who have both "b" and "c" in 
    	their first name. 
    
    					    						

     
    	SELECT first_name
    	FROM employees
    	WHERE first_name LIKE '%b%'
    	AND first_name LIKE '%c%';
    
    			OR 
    			
    	SELECT first_name
    	FROM employees
    	WHERE first_name REGEXP '%[bc]%';
    						

  • 6. 	Write a query to display the last name, job, and salary for all employees whose job is 
    	that of a Programmer or a Shipping Clerk, and whose salary is not equal to $4,500, $10,000, 
    	or $15,000. 
    
    					    						

    	SELECT last_name, job_id, salary
    	FROM employees
    	WHERE job_id IN ('IT_PROG', 'SH_CLERK')
    	AND salary NOT IN (4500,10000, 15000);
    						

  • 7. 	Write a query to display the last name of employees whose names have exactly 6 characters. 
    
    					    						

    	SELECT last_name FROM employees WHERE last_name LIKE '______';
    						

  • 8. 	Write a query to display the last name of employees having 'e' as the third character.
    
    					    						

    	SELECT last_name FROM employees WHERE last_name LIKE '__e%';
    						

  • 9. 	Write a query to display the jobs/designations available in the employees table.
    
    					    						

    	SELECT DISTINCT job_id  FROM employees;
    						

  • 10.	Write a query to display the name (first_name, last_name), salary and PF (15% of salary)
    	of all employees. 
    
    					    						

    	SELECT first_name, last_name, salary, salary*.15 as PF from employees;
    	
    							OR
    							
    	SELECT first_name, last_name, salary, salary*.15 PF from employees;
    						

  • 11.	Write a query to select all record from employees where last name 
    	in 'BLAKE','SCOTT', 'KING' and 'FORD','Jones'. 
    
    					    						

    	SELECT * 
    	FROM employees 
    	WHERE last_name IN('JONES', 'BLAKE', 'SCOTT', 'KING', 'FORD');