Write A Pl Sql Block To Display Number Of Employees For Each Department
a)select deptno,count(deptno)from emp group by deptno; 43)Display the various jobs and total number of employees within each job group. doc - This demo in Microsoft Word 2003 format. SQL Server Tips, Articles and Training. Click me to see the solution. The clouds give greater prominence to words that appear more frequently in the source text. Each of these is unit tested, as described below, and in addition there is a driver script, api_driver. The INSERT statement allows you to: Insert a single row into a table; Insert multiple rows into a table; Copy rows from a table to another table. PL/SQL Declaration Section. The Syntax for SQL UPDATE Command is: UPDATE table_name SET column_name1 = value1, column_name2 = value2, [WHERE condition] table_name - the table name which has to be updated. 1: Function to determine if a string is a has the format of a valid social security number: -- for each element in the PL/SQL. Today we. It allows you to execute any SQL statement from an application program. The outer query looks at these values and determines which employee’s salaries are greater than or equal to any highest salary by department. Select into takes the values name, address and phone number out of the table employee, and places them into the variables v_employee_name, v_employee_address, and v_employee_phone_number. Last update on April 14 2018 06:14:58 (UTC/GMT +8 hours) Write a query to get the department name and number of employees in the department. PL/SQL and SQL go hand in hand so there would be no need of any translation required between PL/SQL and SQL. write a stored function that accepts a department ID and returns the number of employees working in that 6. What is PL/SQL block? In PL/SQL, the code is not executed in single line format, but it is always executed by grouping the code into a single element called Blocks. Use the departments and employees tables. SQL SELECT – querying all columns example. The SQL language is also used for the administration of the database for the creation and modification of tables, users and access privileges. 42)Display depart numbers and total number of employees working in each department. For example, how would you implement an SQL query that should fetch the employees with the maximum salary for each department from the employees table?. Write a PL/SQL block to store an employee number, the new department number, and the percentage increase in the salary in iSQL*Plus Process each row from the cursor, and if the salary is greater than 15,000 and the hire date is greater than 01-FEB-1988, display the employee name. (b) Copy all 5 test scores to another list. SQL provides the INSERT statement that allows you to insert one or more rows into a table. To write a query that performs an outer join of tables A and B and returns all rows from A( left outer join), apply the outer join operator will select all departments even if there is no employee for a particular dept. Everything we offer helps students bridge the gap between the classroom and clinical practice, while supporting health care. Following are some Complex SQL Queries Examples with answers in detail. Learn about subprogram storage and execution. DNAME from emp inner join dept on emp. In Sybase it is represented as "int". We keep track of the start date when the employee began managing the dept. SET SERVEROUTPUT ON DEFINE myvar1 NUMBER(8) DEFINE myvar2 VARCHAR(15) ACCEPT myvar1 PROMPT 'Enter department no' ACCEPT myvar2 PROMPT 'Enter department Location'. OFF means that SQL*Plus recognizes no PL/SQL block terminator. Try for FREE. Use cursor for getting data and make use of 2. (d) Write one example each of URL and IP address. Use a loop to examine each test score in the new list. Summarizing Values: GROUP BY Clause and Aggregate Functions. Re: How to check to see if two rows in sql have the same data as one another? Nov 23, 2010 12:22 AM | KumarHarsh | LINK The query needs to return data or a row or some value to help me determine if there is any difference between the 8 columns of data in the Employee table vs. Write 2 advantages of this topology. You can define it explicitly by --- table tag or omit it. A stored procedure is a set of SQL statements that can be executed on the database. We will use the employees table in the sample database for the demonstration purposes. Whenever you write your SQL statements, you should write with performance in. SQL*Plus provides administrators and programmers with. You can tweak your clouds with different fonts, layouts, and color schemes. The example table function I have created, sf_gather_cost_centers, in Listing 1. SELECT empno, ename FROM emp WHERE deptno IN ( SELECT deptno. Introduction to Interbase Stored Procedures and Triggers. sql is structured query language ,various queries are used to handle the. Hiring? Post a job for free. of blocks, wait time, etc) since SQL Server was last restarted. SQL stands for Structured Query Language, and is the language used to communicate with a database. lastName) FROM employee, department WHERE employee. Oracle defines placeholders to store data temporarily, which are used to manipulate data during the execution of a PL SQL block. There will be a separate column for each job. This is basically a column whith numeric values -1, 0, 1, null. Display average salary of employees in each department who have commission percentage. Cursors Sample 3. It allows you to execute any SQL statement from an application program. A stored procedure allows for code that is run many times to be saved on the database and run at a later time, making it easier for yourself and other developers in the future. Data Visualization News R and Python bring new look to SQL Server. Display all columns for everyone that is over 40 years old. SQL Server Tips, Articles and Training. This function enables us to aggregate data into a collection, retaining multiple records of data within a single row (like a nested table). If you have a PL/SQL engine on the client side, calls from client to server impose no restrictions. In this section, you will create a PL/SQL stored function to calculate and display the total remuneration for each employee. Balashiha is a city, and 141980 is the postal code of the city. We can use DESC keyword after the column name in query if we want to arrange the display in decreasing order. if no employees are working in the department ,then display a message that no employees are working. avg_sal NUMBER (8,2) hp_num NUMBER Then write a PL/SQL block to populate the department table statistics into the “dept_stat” table using the tables employees and department. #Write a query in SQL to display the first name, last name, department number, and department name for each employee. Do we normally write house number prior to the street name? For example, would it be correct to write the address in the following way (this address is outsite the US, but I need to write it according to the English norms): 155 Joliot Str. The sample programs illustrate several important PL/SQL concepts and features. SQL UPDATE Statement. SQL query that retrieves the department name with maximum number of employees SELECT TOP 1 DepartmentName FROM Employees JOIN Scenario asked in the SQL Server Interview Based on the above two tables write a SQL Query to get the name of the Department that has got the. Write a Python program to do the following: (a) Ask the user to enter 5 test scores. Write a PL/SQL block to display the country_name and area of all countries in a chosen region. Declare a PL/SQL record to store the employee number, employee name, department number and the department name of the. If there are a huge number of rows in a table and we do not want all the records to fill our display screen, then SQL gives us an option to view only selected rows. The datatype to hold XML in PL/SQL or in the database is XMLTYPE. As leaders in online education and learning to code, we’ve taught over 45 million people using a tested curriculum and an interactive learning environment. Lets say you have the below table. Create a query to display the employee name and department number for employee number 7566. Each PL/SQL article offers a quiz to test your knowledge of the information provided in it. This is the first of a series discusses defining variables, and using the IFELSE logic. In a procedural language the user instructs the system to do a sequence of operations on database to compute the desired result. Write a pl sql block to accept to numbers from user and print the qube of even numbers between that range. The CREATE TRIGGER statement defines a PL/SQL trigger in the database. SQL Server resources to solve real world problems for DBAs, Developers and BI Pros - all for free. This variable will be used to store the rows coming from the query in the main body of the function. Use procedure to update table. DECLARE Employee_Cursor CURSOR FOR SELECT EmployeeID, Title FROM AdventureWorks2012. Can you provide some cursor examples? Can you give any. In this chapter, we will discuss the Basic Syntax of PL/SQL which is a block-structured language; this means that the PL/SQL programs are divided and written in logical blocks of code. deptid%type; cursor c1(pdid emp_cur. Write a PL/SQL block to display the country_name and area of all countries in a chosen region. Explicit Cursor: Which Should You Use? I assign values to the my_n variable and then display its value at the end of the block to the PL/SQL optimizer works on anonymous. Can read OLD and NEW values, but cannot change them. So you can use the generated XML in PL/SQL, store it in table (XMLTYPE column), transform it to a CLOB (using the XMLTYPE getClobVal member function which I use in the examples) and write it to a file. In this tutorial, you are going to learn about these blocks. Today we. If it is required, the SQL Server Database Engine detects the number of threads and distribute the execution of the tasks among these threads. Learn the technical skills you need for the job you want. Oracle's go-forward strategy is to have data warehousing/data integration to be done through Oracle data integrator and phase-out Oracle Warehouse builder to ODI by providing migration tools. Based on the departments table below, create a SQL table called employees that stores employee number, employee name, department, and salary information. Display a message indicating whether the total salary is greater than or less than $19,000. Take the Challenge. Each department may contain NULL salaries; in dept. In this particular example, there are two related tables, Departments and Employees, with the stored procedure deleting a specific department. Part 8 SQL Query to find department with highest number of employees two tables write a SQL Query to get the name of the Department that has got the maximum number of Employees. Write a PL/SQL anonymous block with exception handling to print your name 10 times?. 923746 wrote: How SQL%ROWCOUNT works plz help PL/SQL code is: SET SERVEROUTPUT ON DECLARE avg_1 NUMBER; avg_2 NUMBER; var_rows NUMBER; BEGIN SELECT AVG(salary) INTO avg_1 FROM employees where department_id=10; SELECT AVG(salary) INTO avg_1 FROM employees where department_id=20;Did you mean to say avg_2 in one of the SELECT. Thursday, November 19, 2009. This trace file is from an 11. 2nd highest salary in Oracle using ROW_NUMBER and RANK in Oracle and MSSQL This is the second article about calculating 2nd highest salary in SQL. If you want to learn more about RECORD, you can refer to PL/SQL User's Guide and Reference chapter 5. The slide example displays the number of employees in departme'nt 30 who can earn a commission. You can find out more by throwing in one or more COUNT () columns. Official MapQuest website, find driving directions, maps, live traffic updates and road conditions. Count and display the frequency of each letter in the string. Each Employees record has a foreign key constraint to the Departments table. sql and run the query. Each department may contain NULL salaries; in dept. Quickly build, test, and deploy transaction-enabled apps that accelerate workflows and change how employees work, wherever they are. preview shows page 1 out of 470 pages. The body of an SQL function must be a list of SQL statements separated by semicolons. Learn software, creative, and business skills to achieve your personal and professional goals. In a cursor FOR loop, retrieve and display the department_id and department_name for each department, and display a second line containing. preview shows page 1 out of 470 pages. a)select job,count(job)from emp group by job; 44)Display the depart numbers and total salary for each department. PL/SQL is a block-structured language whose code is organized into blocks. 50+, CUBRID, MariaDB 10. Viewing only selected records from a table. An SQL aggregate function calculates on a set of values and returns a single value. For the employee at the top of the organization chart, leave this field blank. Summarizing Values: GROUP BY Clause and Aggregate Functions. Oracle - 3072 Oracle interview questions and 4063 answers by expert members with experience in Oracle subject. Oracle Functions: Version 11. Learn the structure of a PL/SQL program block. Select department_ID,count(*) as "Number of employees" from employees group by department_ID having count(*)>5. Quickly build, test, and deploy transaction-enabled apps that accelerate workflows and change how employees work, wherever they are. empid%type:=1; --empid for which address is required. The deptno will be passed as parameter. The following stored procedure walks the tree structure and returns the record hierarchy. select employee. For example, a department may be associated with a number of employees. And, you are doing this every day. So far, the examples presented have shown how to retrieve and manipulate values from individual rows in a table. Pinal Dave is a SQL Server Performance Tuning Expert and an independent consultant. An Oracle Database 12c enhancement allows PL/SQL declarations in the WITH clause. empid group by d. PL/SQL block to raise the salary by 20% of given employee on following table. Learn about subprogram storage and execution. - the columns that gets changed. Because nearly all implementations of SQL allow you to include an ORDER BY clause at the end of a SELECT statement that you can save in a view, we invented the term SELECT query to describe this type of statement. Viewing only selected records from a table. SQL is a standard query language used for maintaining the relational database and perform many different operations of data manipulation on the data. ID Name Department DOB State Country Salary. Today PL/SQL is a standard feature (not a cost option anymore) of the database and one of Oracle's premier development languages. If you want to learn more about RECORD, you can refer to PL/SQL User's Guide and Reference chapter 5. Write 2 advantages of this topology. Join today to get access to thousands of courses. How to Display the Manager, Having Maximum Number of Employee? May I know that how can I display the manager who is having maximum number of employees working under him?. Database Programming with SQL-Section 12 Quiz Deletes employee number 100. Display all employee name,department number and name? Select emp. sql is: REM ***** REM Create the REGIONS table to hold region information for locations REM HR. David Hilbert One of the most mindboggling values in the Oracle database is the NULL value. Id in the WHERE clause). Store the scores in a list. There are 49 records for this table. Good morning to all I have to write function that receives department name and an aggregation operation (average, maximum, minimum) and apply the operation on the salary of employees working on the given department and return the result. A fixed number of named columns are defined for a table with a variable number of rows. (use the table EMP and DEPT) 3. Relational Algebra A query language is a language in which user requests information from the database. In the data file, index record appears only for a few items. In my T-SQL code I always use set based operations. 115) Find out the number of employees whose salary is greater than their manager salary? SQL>SELECT E. There is given sql interview questions and answers that has been asked in many companies. For example, the following statement retrieves all data from the employees table:. sql to display the last name and salary of employees who earn between $5,000 10. What is the basic structure of PL/SQL? PL/SQL uses block structure as its basic structure. Thus, it has limited applicability to columns with high cardinality. Here is the basic syntax of the CREATE TRIGGER statement:. 2000, 2005, 2008, 2008R2, 2012 and 2014. Learn the structure of a PL/SQL program block. Q3: Write a PL/SQL code block to find area of circles with radius greater than 3 and less than equal to 7 and store the result in a table with attributes radius and area. DepartmentName, COUNT(employee. Use the departments and employees tables. Display unique Jobs from EMP table?. CASE Expressions And Statements in Oracle. Display number of employees getting NULL comission. Pages 470 ; This preview shows page 1 out of 470 pages. Id) FROM [Order] O WHERE O. Do we normally write house number prior to the street name? For example, would it be correct to write the address in the following way (this address is outsite the US, but I need to write it according to the English norms): 155 Joliot Str. Display the first and last names for everyone whose last name ends in an "ay". Each employee has more than one territories. In Pl/Sql it has a 32760 byte limit. SQL Inner Join - examples and explanations The nature of relational database design means that we will often have related data that is stored in different tables. ENAME FROM EMP ,EMP E WHERE EMP. In contrast bucketBy distributes data across a fixed number of buckets and can be used when a number of unique values is unbounded. The airport has a number of tests that are used periodically to ensure that air-planes are still airworthy. write a PL/SQL procedure to display the details of employee who is having the highest. Obtain the number of appointments that have taken place in each examination room. You can't really declare your own "user-defined" scalars, though you can define subtypes from those scalars, which can be very helpful from the perspective…. Use the departments and employees tables. 2000, 2005, 2008, 2008R2, 2012 and 2014. Create your free account today with Microsoft Azure. There will be a separate column for each job. Oracle 9i extended its support to PL/SQL to allow CASE to be used as an expression or statement. Introduction to PL/SQL WHILE Loop. Write a query to display the employee last name, department name, location ID, and city of all 6. Display department numbers and maximum salary for each department. */ declare. The %ROWCOUNT attribute returns the number of rows fetched from the cursor. However, this is not the way to write SQL for performance. To display each player, their manager, and their team name for all teams with an id value greater than 5000. Take a look at how the PL/SQL Challenge uses table functions to avoid code redundancy and minimize the number of interactive reports needed to provide rankings. Find nearby businesses, restaurants and hotels. department_id department. Variables of XMLType can be used in PL/SQL stored procedures as parameters, return values, and so on. Occupation = Management, and if it is TRUE then it will assign Administrator as Department Name. The certification number must be included on business advertising material, display advertisements in telephone directories, letterhead, business cards, local newspaper advertising, and contracts. SANDIP PATEL(LDRP-ITR) Page 4 Emp_Salary (eno, ename, city Program 10:Write a Cursor to display the employee number, name, department and salary of first employee getting the highest salary. How to get top 5 salary data in sql query now i want a single query which returns all the employees who have top 5 salaries. To retrieve data from two or more tables in one query we use the SQL JOIN statement. All employees and tenants will participate. Each statement ends with a semi-colon. COUNT(expr) returns the number of nonnull rows in the column identified by expr. PL/SQL supports only three types of records - table based, cursor based and programmer defined. Also, handle an exception if there is no salary. Read Pl/Sql books like Oracle Advanced PL/SQL Developer Professional Guide and Oracle SQL and PL/SQL for free with a free 30-day trial. It needs more space to store index record itself. oracle prep for pl/sql certification test 1z0-147 Program with PL/SQL Oracle PL/SQL Developer Certified Associate (There are many different certifications out there - I think this is the one that this course is for) This set is incomplete - this is from one of 2 midterms for the class - there are also 2 final exams which I would assume have about a 50 questions each as well. SQL*Plus provides administrators and programmers with. Modify lab_02_03. example, SUM, AVG, DIGITS etc. SQL is designed to do one thing and do it well—provide you with a simple and efficient way to read and write data from a database. Display department number and its employee count if it has more than 4 employees : HAVING « Query Select « Oracle PL/SQL Tutorial. deptid%type) is select * from emp_cur where deptid=pdid; begin vdid := &vdid; for i in c1(vdid) loop. For example, if you pass a host cursor variable to a PL/SQL block embedded in a Pro*C program, the work area to which the cursor variable points remains accessible after the block completes. XML-SQL Utility for PL/SQL. Table is a default renderer for SQL blocks. Balashiha is a city, and 141980 is the postal code of the city. This is the same way that indexes work in Oracle (and other SQL databases). PUT_LINE procedure to display the "Hello World" message on the screen. Assume the record is a variable which holds a table row or some columns of a table. If it is required, the SQL Server Database Engine detects the number of threads and distribute the execution of the tasks among these threads. select employee. The highest paid employee. Display the first name, last name, and city for everyone that's not from Payson. What happens if a training school fails to maintain, at all times, current liability coverage in at least the minimum amount prescribed by the state. This program takes two inputs one for each variable and adds the result to a third variable and prints it. The anonymous block has three basic sections that are the declaration, execution, and exception handling. The reason it is called a correlated subquery is because the subquery uses a value from the outer query in its WHERE clause. street, employee. The following short example of a PL/SQL anonymous block prints the names of all employees in department 20 in the Emp_tab table. However, keep in mind that PL/SQL was created for working with data in an Oracle database and that it does an excellent job. The view must not contain any of the following constructs:. Let's look at some Oracle AVG function examples and explore how to use the AVG function in Oracle/PLSQL. You can also use XMLType in PL/SQL, SQL and Java, and through JDBC and OCI. NULL value will not be counted. Write a query in SQL to display the first name, last name, department number, and department name for each employee. SQL Server resources to solve real world problems for DBAs, Developers and BI Pros - all for free. Use the following command to check if the table. sqlauthority. Just tell the PL/SQL engine you want to work with each row returned by the query. When we find a match, we record the earnings information from the Form W-2 in the employee's earnings history. v_assoc assoc; CURSOR c IS SELECT ename, sal FROM emp; --elem emp. In this particular example, there are two related tables, Departments and Employees, with the stored procedure deleting a specific department. Q1: Write a PL/SQL code block to find sum and average of three numbers. Starting with Oracle 8, PL/SQL's version numbers were syncronized with the database version numbers. If there are a huge number of rows in a table and we do not want all the records to fill our display screen, then SQL gives us an option to view only selected rows. A dept controls a number of projects, each of which has a name, unique number, and single location. SQL, SQL Server, Tutorials, Oracle, PL/SQL, Interview Questions & Answers, Joins, Multiple Choice Questions, Quiz, Stored 1) Retrieving the records from the emp table and displaying them one by one using cursors 2) Create a PL/SQL block to increase salary of employees in department 17. Oracle's go-forward strategy is to have data warehousing/data integration to be done through Oracle data integrator and phase-out Oracle Warehouse builder to ODI by providing migration tools. A PL/SQL procedure would not be useful unless there is a way to populate the variables with a value. Then here it goes. There are many improvements in this release, so this wiki page covers many of the more important changes in detail. The INSERT statement allows you to: Insert a single row into a table; Insert multiple rows into a table; Copy rows from a table to another table. I have tried to explain each and every query in detail so that everyone will get idea of how it is executed step-by-step. Some territories are not covered (53-49=4). (2) 2 (a) While working in Netbeans, Ms. Without bulk bind, PL/SQL sends a SQL statement to the SQL engine for each updated employee, leading to context switches that slow performance. I've loaded the data into sql but now I want to replace the second row data into 1st row with respective column values. Thursday, September 22, 2011. Write a PL/SQL block to display the country_id and country_name values from the WF_COUNTRIES table for country_id whose values For each of these departments, display the department name and the number of employees. You can find out more by throwing in one or more COUNT () columns. Today we. The PL/SQL block in Example 7-7 increases the salary for employees whose manager's ID number is 7902, 7698, or 7839, with and without bulk binds. So you can use the generated XML in PL/SQL, store it in table (XMLTYPE column), transform it to a CLOB (using the XMLTYPE getClobVal member function which I use in the examples) and write it to a file. Depending on the value row will be highlighted with a certain color:. write a stored function that accepts a department ID and returns the number of employees working in that 6. There is an ANSI-standard collection of SQL and each database vendor has expanded on it for their own purposes. Can you please tell me the SQL query to get the details of employees having maximum salary from each department without using analytic functions? EMPLOYEE Table Columns EMP_ID EMP_NAME EMP_SALARY DEPT_ID It should display all the columns from employee table. That is exactly how this query is evaluated: SQL Server considers each row of the Employee table for inclusion in the results by substituting the value in each row into the inner query. Checking for NULL with Oracle SQL The art of doing mathematics consists in finding that special case which contains all the germs of generality. 2000, 2005, 2008, 2008R2, 2012 and 2014. Search millions of jobs and get the inside scoop on companies with employee reviews, personalized salary tools, and more. (use the table EMP and DEPT) 3. HumanResources. Each employee will appear multiple times in the table, based upon the last x number (this number varies) of pay periods that was processed for this employee, so, I'll have something like this: ssn, employee_num, last_name, first_name, change_date. All employees and tenants will participate. A join condition (ON table1. What happens if a training school fails to maintain, at all times, current liability coverage in at least the minimum amount prescribed by the state. II) Display the appropriate messages by using exceptions such as 'no_data_found'. Whether you are building a stored procedure or writing a small Query Analyzer script you will need to know the basics of T-SQL programming. Display the message how many records are updated at the end. A SQL Developer with late-career experience which includes employees with greater than 20 years of experience can expect to earn an average total compensation of $91,000 based on 39 salaries. What is the basic structure of PL/SQL? PL/SQL uses block structure as its basic structure. Join today to get access to thousands of courses. All subsequent explanations on join types in this article make use of the following two tables. PL/SQL Program To Add Two Numbers. Create a foreign key on the employees table that references the departments table based on the department_id field. Q3: Write a PL/SQL code block to find area of circles with radius greater than 3 and less than equal to 7 and store the result in a table with attributes radius and area. Display unique Jobs from EMP table?. These examples will help you to create valid triggers and to troubleshoot PL/SQL trigger compilation errors. Run your query. Here Mudassar Ahmed Khan has provided a simple Cursor tutorial with Syntax example in SQL Server. Sql queries with answers 1. Select the item and price for all of the items in the items_ordered table that the price is greater than 10. SELECT ename, deptno FROM emp WHERE empno = 7566 3. Other changes are also on tap for SQL Server users. Example - With Single Field. The following example uses @@FETCH_STATUS to control cursor activities in a WHILE loop. If you're looking for SQL Server Interview Questions for Experienced or Freshers, you are at right place. A Cartesian product always generates many rows and is rarely useful. to take columns in sql. In this section, you will create a PL/SQL stored function to calculate and display the total remuneration for each employee. This document showcases many of the latest developments in PostgreSQL 9. Display the last name concatenated with the job ID separated by a comma and space and name the column Employee and Title. Order your output so that the department with the. Which of the following SQL statements can be included in a PL/SQL block only by using Dynamic SQL? employees in department 50. One department has no, one, or more employees. SQL COUNT Syntax SELECT COUNT(expression) AS resultName FROM tableName WHERE conditions The expression can be *, column name or DISTINCT column name. The below approach explains how a function can be declared in an anonymous block without creating them permanently in the database. This variable will be used to store the rows coming from the query in the main body of the function. UT Austin Fire Prevention Services and the City of Austin Fire Department will assist with the drill. Based on the departments table below, create a SQL table called employees that stores employee number, employee name, department, and salary information. Of course, the below query works just fine, returning the correct results. Write a PL/SQL block to help user to insert salary of an employee. empid group by d. Lets say you have the below table. Just tell the PL/SQL engine you want to work with each row returned by the query. 5% (expressed as a whole number) for each employee. Create a foreign key on the employees table that references the departments table based on the department_id field. An SQL aggregate function calculates on a set of values and returns a single value. If you have a big table of data, like employee information, you might need to filter it by certain criteria in order to generate reports. Discuss each question in detail for better understanding and in-depth knowledge of Oracle. In this post, we will focus on removing records from a database. We may require to display total of each class in a table so here we have to use GROUP BY clause to display totals of each class. Learn software, creative, and business skills to achieve your personal and professional goals. This control file loads Accounting as department number 1, Personnel as 2, and Manufacturing as 3. write a stored function that accepts a department ID and returns the number of employees working in that 6. department_id , d. SQL Server Tips, Articles and Training. The source code for the load_sample.