SAMPLE SQL ASSIGNMENTS DURING TRAINING

SQL assignments

1. Using select statements

1. Write a query to list all the tables in HR schema

2. Display employeeID, first name, last name, email, phone number from employees table.

3. Display all columns of JOB_HISTORY table.

4. Display the first name, last name concatenated and appearing under the column “EMPLOYEE NAME”.

5. Write a query to display a sentence like Employee name working as JOB_ID for every employee record.

6. Write a query to display names of all the employees with their annual salary.

2. Scoping and ordering of rows

1. Write a query to select rows from the employees table with the department number of 30.

2. Write a query to select the name, job, and salary and department number of all employees except purchase clerks from department number 30.

3. Display the employee name and department number of all employees in dept 10 and 30 in alphabetical order by name.

4. Display the employee name, job_id and email of those employees who were hired between 1 Jan 1993 and 1 Dec 1995.Order the results in the ascending order of hiredate.

5. Write a query to search for employees with the pattern 'A_B' in their name.

3. SQL Functions

1. Write a query to display second string of  job_title in the Jobs table only if the job_title has more than one string.

2. Replace occurrences of Manager in the job_title of jobs table with analyst.

3. Write a query to display the previous job history of the employees with years of experience in each job category.

4. Write a query to display the details of employees with ‘ON BENCH’ value for department ID if no employees have been assigned to that department.

5. Write a query to display revised salary for selected employees

            a. If he is a manager give 50%hike

            b. If he is a representative give 20%hike

            c. if he is a programmer give 30%hike.

(Use case expression)

6. Write a query to display country wise details with the region name.

(Use the data in the region table and use decode function)

4. Joins

1. Write a query to display names of employees and department names of those employees who are working in Europe.
2. Write a query to display department wise details including the Manager’s name and if a particular department does not have any manager, display the message ‘no manager assigned yet’.
3. Display information on all the department managers.

Manager ID, Manager Name, His current department name and previous department name.

1. Display old job title and current job title for each employee in the organization.

5. Group Functions & Group by, Having Clause

1. Find the number of employees working in each country with country name and no of employees.

2. Find the number of employees   reporting to each department head.

3. To return the number of employees and their average yearly salary across all possible combinations of department and job category.

Display department name, job_title, total no of employees, average salary.

6. Sub-Queries

1. Write a query to give all employees in the employees table a 10% raise if they have changed jobs.

2. List the number of employees working in each department with the department name.

3. Find the details of employees who have switched jobs at least twice.

4. Find top 3 earners in the organization with their department names.

5. Display the manager number and the salary of the lowest paid employee for that manager. Exclude anyone whose manager is not known. Exclude any group where the minimum salary is less than 1000. Sort the output in descending order of salary.

6. Suppose you need to know the employee name and department name of all employees that work in a department that has at least 3 employees. Write a SQL query to retrieve this information. Order the list in alphabetical order first by department name, then by employee name

7. DDL and DML statements

1. Create a table manager_data which stores information about all the managers

Manager ID, Manager Name, Phone number, email, department name and location. The department name should be a valid one having reference in the existing dept table in the HR schema.

2. Ensure that all the department names are unique.

3.Insert all the employees contact details having employeeID, location ID and location address into contact table and insert the details of emails of all the employees into email_info table which will store employeeID, employee name and his email_id.

(Using Multitable insert)

4. Write a query to retrieve the details of the employees with employeeID, name,job_id and department name. If they are working in sales department, insert those records into sales_employees table with employeeID, name and job_id. If they are also managers insert those records into sales_managers table with employeeID,name and Job_ID.

5.Insert all employee records into experience wise employee processing tables.

Level 1-having 6 to 10 years of experience i.e.

Level 2- having 11 to 15 years of experience

Level 3- having 16 to 20 years of experience

 8. Views, indexes and synonyms

1. Create a non-unique index on department name of departments table. And query the associated data dictionary to confirm the index creation.

2. Create a view called SALARY_VIEW based on the employee names, department names, job_title, salary and annual salary for all employees.

3. Create a sequence to be used with primary key column of locations table. The sequence should start at 4000 and have a maximum value of 5000.Have your sequence increment by 100 numbers. Insert two rows into locations table. And use the sequence values for the location ID column.