SQL Queries and Functions: EMP Table Examples for Practice

The emp table in SQL is a standard two-table practice schema used to learn relational database concepts. It stores 14 employee records across eight columns: empno, ename, job, mgr, hiredate, sal, comm and deptno. Paired with the DEPT table, it lets you practise joins, aggregations, date filters, conditional logic and update queries in Oracle, MySQL and SQL Server without needing a production database.

• Key Takeaway 1: The EMP and DEPT tables are the standard starting point for SQL practice in Oracle, MySQL and SQL Server environments.
• Key Takeaway 2: DECODE is Oracle-specific syntax; CASE is the ANSI-standard equivalent that works across all major databases.
• Key Takeaway 3: A primary key constraint enforces uniqueness and prevents null values, making it the backbone of every well-designed table.
• Key Takeaway 4: Date range filtering with BETWEEN, percentage calculations using arithmetic expressions and the DESCRIBE command are all skills tested in SQL interviews at Indian IT companies like TCS, Infosys and Wipro.
• Key Takeaway 5: Every query example in this article is runnable on Oracle Live SQL (livesql.oracle.com, free and browser-based) or any local MySQL or SQL Server instance.

Setting Up the EMP and DEPT Tables in SQL

Before you can run any sql queries on emp table examples, you need a working schema. The classic emp table in sql has 14 rows and eight columns. You create it with a primary key constraint on the employee number so no two employees share the same ID.

Creating the DEPT Table First

You create DEPT before EMP because the emp table in sql references DEPT via a foreign key. The DEPT table holds three columns: deptno (primary key), dname and loc. Standard departments are ACCOUNTING (10), RESEARCH (20), SALES (30) and OPERATIONS (40).

CREATE TABLE dept (
  deptno NUMBER(2)    CONSTRAINT pk_dept PRIMARY KEY,
  dname  VARCHAR2(14),
  loc    VARCHAR2(13)
);

INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO dept VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');
COMMIT;

You name the constraint explicitly (pk_dept) so it appears clearly in error messages and schema documentation.

Creating the EMP Table with a Primary Key

The emp table in sql uses empno as its primary key. You add a foreign key on deptno that references the DEPT table. The hiredate column stores a DATE type, which is essential for the date range queries you will write later.

CREATE TABLE emp (
  empno    NUMBER(4)    CONSTRAINT pk_emp PRIMARY KEY,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)    REFERENCES dept(deptno)
);

INSERT INTO emp VALUES (7369,'SMITH', 'CLERK',    7902, DATE '1980-12-17',  800,  NULL, 20);
INSERT INTO emp VALUES (7499,'ALLEN', 'SALESMAN', 7698, DATE '1981-02-20', 1600,  300, 30);
INSERT INTO emp VALUES (7521,'WARD',  'SALESMAN', 7698, DATE '1981-02-22', 1250,  500, 30);
INSERT INTO emp VALUES (7566,'JONES', 'MANAGER',  7839, DATE '1981-04-02', 2975,  NULL, 20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN', 7698, DATE '1981-09-28', 1250, 1400, 30);
INSERT INTO emp VALUES (7698,'BLAKE', 'MANAGER',  7839, DATE '1981-05-01', 2850,  NULL, 30);
INSERT INTO emp VALUES (7782,'CLARK', 'MANAGER',  7839, DATE '1981-06-09', 2450,  NULL, 10);
INSERT INTO emp VALUES (7788,'SCOTT', 'ANALYST',  7566, DATE '1987-04-19', 3000,  NULL, 20);
INSERT INTO emp VALUES (7839,'KING',  'PRESIDENT',NULL, DATE '1981-11-17', 5000,  NULL, 10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN', 7698, DATE '1981-09-08', 1500,    0, 30);
INSERT INTO emp VALUES (7876,'ADAMS', 'CLERK',    7788, DATE '1987-05-23', 1100,  NULL, 20);
INSERT INTO emp VALUES (7900,'JAMES', 'CLERK',    7698, DATE '1981-12-03',  950,  NULL, 30);
INSERT INTO emp VALUES (7902,'FORD',  'ANALYST',  7566, DATE '1981-12-03', 3000,  NULL, 20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',    7782, DATE '1982-01-23', 1300,  NULL, 10);
COMMIT;

According to Oracle’s official documentation, the Scott/Tiger schema containing the emp table in sql and DEPT has been included in Oracle Database since version 5, making it one of the longest-standing teaching tools in relational database history.

Using DESCRIBE to Inspect the EMP Table Structure

Once your tables exist, run DESCRIBE emp; in Oracle or SQL*Plus to see every column name, data type and whether nulls are allowed. In SQL Server, the equivalent is EXEC sp_help ’emp’; or SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ’emp’;.

DESCRIBE is one of the first commands students learn in database courses at universities following Anna University or Mumbai University syllabi. It is simple, but it tells you everything about the emp table in sql structure at a glance.

DECODE, CASE and Conditional Logic on the EMP Table in SQL

Conditional logic is where most SQL learners get confused, especially around the difference between DECODE and CASE. Both solve the same problem, but they are not interchangeable in every database.

How DECODE Works in Oracle SQL

The DECODE function in SQL is Oracle-specific. It works like a series of IF-THEN-ELSE checks written as a single expression. The syntax is: DECODE(expression, search1, result1, search2, result2, default).

A practical example on the emp table in sql: you want to display each employee’s department name without joining the DEPT table.

SELECT ename,
       DECODE(deptno, 10, 'ACCOUNTING', 20, 'RESEARCH', 30, 'SALES', 'OTHER') AS dept_name
FROM emp;

This reads cleanly and runs fast for a small lookup list. The downside is that DECODE only tests equality. You cannot write DECODE(sal > 3000, …).

CASE vs DECODE: A Direct Comparison

CASE is the ANSI SQL standard. It works in Oracle, SQL Server, MySQL, PostgreSQL and every other major database. DECODE works only in Oracle. The table below shows the key differences when running sql queries on emp table data.

The CASE equivalent of the DECODE example above:

SELECT ename,
       CASE deptno
         WHEN 10 THEN 'ACCOUNTING'
         WHEN 20 THEN 'RESEARCH'
         WHEN 30 THEN 'SALES'
         ELSE 'OTHER'
       END AS dept_name
FROM emp;

You can also write a searched CASE for range conditions on the oracle emp table:

SELECT ename, sal,
       CASE
         WHEN sal < 1500              THEN 'LOW'
         WHEN sal BETWEEN 1500 AND 3000 THEN 'MEDIUM'
         ELSE 'HIGH'
       END AS sal_grade
FROM emp;

DECODE simply cannot do this without nesting, which makes CASE the better choice for anything beyond a straight equality lookup.

Calculating a Percentage in SQL Using the EMP Table

Percentage calculations come up constantly in analytics roles. On the emp table in sql, a common task is finding what percentage of total salary each employee earns. The query uses a subquery to get the total:

SELECT ename, sal,
       ROUND((sal / (SELECT SUM(sal) FROM emp)) * 100, 2) AS salary_pct
FROM emp
ORDER BY salary_pct DESC;

The ROUND function keeps the output to two decimal places. You can swap SUM(sal) for COUNT(*) to calculate headcount percentages by department instead. According to the 2023 Stack Overflow Developer Survey, SQL ranks among the top five most-used languages globally, with 51.5% of professional developers reporting they use it regularly. That explains why percentage and aggregation queries appear in almost every SQL interview round at Indian product companies.

If you are building these skills to land your first tech role, check out this guide on entry-level jobs for teens and fresh graduates to understand what employers actually test in technical interviews.

Date Filtering and Update Queries on the EMP Table in SQL

Date handling trips up a lot of learners because syntax varies between Oracle and SQL Server. Both support BETWEEN for date ranges, but the literal format differs.

Filtering EMP Table Records Between Two Dates

On the emp table in sql, hiredate is a DATE column. To find all employees hired between 1980 and 1985 in Oracle:

SELECT ename, hiredate
FROM emp
WHERE hiredate BETWEEN TO_DATE('01-JAN-1980', 'DD-MON-YYYY')
                   AND TO_DATE('31-DEC-1985', 'DD-MON-YYYY');

In SQL Server, the same query uses single-quoted string literals in ISO format:

SELECT ename, hiredate
FROM emp
WHERE hiredate BETWEEN '1980-01-01' AND '1985-12-31';

Always use TO_DATE in Oracle rather than relying on implicit conversion. Implicit conversion breaks when the NLS_DATE_FORMAT session parameter differs across environments, which is a common source of bugs in production Oracle systems.

Writing an Update Query on the Employee Table in SQL

An update query on the employee table in sql follows a simple pattern: specify the table, set the new value and add a WHERE clause so you do not accidentally update every row.

UPDATE emp
SET sal = sal * 1.10
WHERE deptno = 20;

This gives every employee in the RESEARCH department a 10% salary increase. Always run a SELECT with the same WHERE clause before your UPDATE to confirm which rows will be affected. This is standard practice in any professional database environment and prevents costly rollbacks. According to IBM’s 2022 Cost of a Data Breach Report, misconfigured or poorly tested database changes contribute to a significant proportion of data incidents, so disciplined query writing matters beyond the classroom.

Finding Employees by Department Using a Join on the EMP Table

The real power of the emp table in sql shows up in joins.

SELECT e.ename, e.sal, d.dname, d.loc
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE d.loc = 'CHICAGO';

This returns all employees in Chicago (the SALES department). Replacing the JOIN with a subquery or using DECODE to avoid the join entirely are both valid approaches depending on the use case.

SQL is one of the top skills every college student should learn before graduation, and working through EMP and DEPT examples is genuinely the fastest way to build that foundation. Most Indian university syllabi for BCA, B.Tech and MCA programs include these exact emp table in sql examples in their database management modules.

If you are deciding which technical skills to prioritise, the top online courses for high-salary jobs in 2025 list covers SQL alongside Python, cloud computing and cybersecurity as the most employer-demanded skills right now.

According to NASSCOM’s 2024 Tech Talent Report, data skills including SQL, Python and cloud tools are the top three hiring criteria for IT roles in India’s technology sector, ahead of domain-specific programming languages. That makes every query you practise on the emp table in sql directly relevant to your job search.

For students still weighing their options, reading about the best career options in 2026 for students and professionals can help you see where database skills fit into broader tech career paths.

SQL Practice Exercises Using the EMP Table

You have seen how to create tables with primary keys, use DECODE and CASE for conditional logic, filter by date range, calculate percentages and write update queries. The next step is running every example yourself, not just reading it.

Here is a practical weekly practice plan for emp table in sql queries:

1. Day 1-2: Create the DEPT and EMP tables from scratch on Oracle Live SQL or MySQL Workbench. Insert all 14 rows manually so you understand the data.
2. Day 3: Write five SELECT queries using DECODE and five using CASE. Compare the output and note where they differ, especially with NULL values.
3. Day 4: Write date range queries filtering hiredate by year, month and specific date windows. Test both BETWEEN and explicit greater-than or less-than comparisons.
4. Day 5: Write percentage queries showing each employee’s share of total salary and each department’s share of total headcount.
5. Day 6-7: Write UPDATE, DELETE and INSERT statements. Practise rolling back transactions and confirming changes with SELECT before committing.

Free platforms like Oracle Live SQL (livesql.oracle.com) and DB Fiddle let you run all of this without installing anything. If you want structured guidance, the best free online learning platforms for students in 2025 include several that offer SQL courses with hands-on labs.

Once you are comfortable with emp table sql practice queries, the natural progression is learning database design, query optimisation and working with real datasets in cloud environments. That is where certifications start to matter for your resume. The guide to choosing the right career path after graduation in 2026 walks through how database and data engineering roles compare to other tech tracks.

3.0 University’s certification courses in Cybersecurity, Ethical Hacking, AI, Blockchain and Web3 are built for students, working professionals and career switchers who want practical, industry-ready skills without a three-year degree program. If you are ready to move beyond practice tables and into real-world projects, explore the full course catalogue at 3.0 University and find the track that matches where you want to go.

Frequently Asked Questions

What is the emp table in SQL?

The emp table in SQL is a standard practice table originally from Oracle’s Scott/Tiger sample schema. It stores 14 employee records across eight columns: empno, ename, job, mgr, hiredate, sal, comm and deptno. It is used alongside the DEPT table to practise joins, aggregations, date filters and conditional logic in Oracle, MySQL and SQL Server.

How many rows does the emp table have?

The standard emp table in SQL contains 14 rows, each representing one employee. The employees span four departments: ACCOUNTING (deptno 10), RESEARCH (20), SALES (30) and OPERATIONS (40). OPERATIONS has no employees in the default dataset, which makes it useful for practising outer join queries.

How do you create a table with a primary key?

Use the PRIMARY KEY constraint in your CREATE TABLE statement. You can define it inline: empno NUMBER(4) PRIMARY KEY, or as a named table-level constraint: CONSTRAINT pk_emp PRIMARY KEY (empno). The named version is preferred in professional environments because it makes constraint management and error messages clearer. A primary key column cannot contain NULL values or duplicates.

What does the DECODE function do in SQL?

DECODE is an Oracle-specific function that works like an IF-THEN-ELSE chain. It compares an expression against a list of search values and returns the matching result. If no match is found, it returns a default value. Syntax: DECODE(expression, search1, result1, search2, result2, default). It only tests for equality, not ranges or complex conditions.

What is the difference between CASE and DECODE in SQL?

DECODE is Oracle-only and tests equality. CASE is ANSI SQL and works in Oracle, SQL Server, MySQL and PostgreSQL. CASE supports range conditions like WHEN sal > 3000, while DECODE cannot. CASE also handles NULL differently: DECODE treats NULL as equal to NULL, but CASE does not. For any new code or cross-database work, CASE is the better choice.

How do you filter records between two dates in SQL?

Use the BETWEEN operator with proper date literals. In Oracle: WHERE hiredate BETWEEN TO_DATE(’01-JAN-1980′, ‘DD-MON-YYYY’) AND TO_DATE(’31-DEC-1985′, ‘DD-MON-YYYY’). In SQL Server: WHERE hiredate BETWEEN ‘1980-01-01’ AND ‘1985-12-31’. Always use explicit date conversion functions in Oracle to avoid NLS_DATE_FORMAT issues that can cause queries to fail in different environments.

How do you calculate a percentage in SQL?

Divide the individual value by the total and multiply by 100. On the emp table in SQL: SELECT ename, ROUND((sal / (SELECT SUM(sal) FROM emp)) * 100, 2) AS salary_pct FROM emp. The subquery calculates the total salary across all employees. ROUND keeps the result to two decimal places. You can apply the same pattern for row counts, department totals or any other aggregation.

Can I use the emp table in MySQL or SQL Server?

Yes. The emp table in SQL is not exclusive to Oracle. You can create the same schema in MySQL, SQL Server or PostgreSQL using standard CREATE TABLE and INSERT statements. The main differences are date literal syntax and the absence of Oracle-specific functions like DECODE. Use CASE instead of DECODE for full cross-database compatibility.

Last updated: June 2025. Reviewed by the 3University editorial team.