GTU MCA RDBMS Set 2 Solution: EMPLOYEE & DEPARTMENT Constraints and Queries in Oracle

Official Set 2

Set 2 — EMPLOYEE and DEPARTMENT (Integrity Constraints, Joins, Sequences)

• PRIMARY KEY
• FOREIGN KEY
• CHECK
• UNIQUE
• ALTER TABLE
• Trigger
• JOIN
• SEQUENCE

Subject

MC01094031 RDBMS

Tool

Oracle 10g or above

Questions

23

Status

Question list: official GTU PDF

Source check: Question wording and numbering follow the official GTU PDF. In the PDF, items 2–3 and 4–5 are each one instruction split across two numbers; the numbering is kept and each number gets its own step. The PDF calls the tables EMP and DEPT in the questions but EMPLOYEE and DEPARTMENT in the schema — this solution uses EMPLOYEE and DEPARTMENT. Employee and department rows are sample data (the PDF asks for "proper data").

On this page

1. Introduction
2. Learning objectives
3. Original question list
4. Database schema
5. Prerequisites
6. Solutions
7. Important concepts
8. Common mistakes
9. Viva questions
10. Summary
11. Download script
12. Related practicals

Introduction

Set 2 introduces a second table and the relationship between them. Each employee belongs to a department, so EMPLOYEE.dept_no refers to DEPARTMENT.dept_no. You will protect the data with integrity constraints — some added at creation time, some added later with ALTER TABLE — then query it with joins, pattern matching, aggregate functions, date functions and a sequence.

Learning objectives

• Define PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL and CHECK constraints at creation time and with ALTER TABLE.
• Understand why a rule based on SYSDATE (age > 18) needs a trigger instead of a CHECK constraint.
• Join two tables, concatenate output text and group the result.
• Use date functions such as TO_CHAR, MONTHS_BETWEEN and SYSDATE.
• Generate key values with an Oracle sequence.

Original question list

Because EMPLOYEE refers to DEPARTMENT, the foreign key cannot be created before DEPARTMENT exists. Q1 therefore creates EMPLOYEE with every other constraint, and the foreign key is added in Q3 right after the DEPARTMENT keys are defined.

1. Create the EMP Table with all necessary constraints such as In EMP TABLE: Employee id should be primary key, Department no should be Foreign key, employee age (birth_date) should be greater than 18 years, salary should be greater than zero, email should have (@ and dot) sign in address, designation of employee can be “manager”, “clerk”, “leader”, “analyst”, “designer”, “coder”, “tester”.
2. Create DEPT table with neccessary constraint such as
3. Department no should be primary key, department name should be unique.
4. After creation of above tables, modify Employee table by adding the constraints as
5. ‘Male’ or ‘Female’ in gender field and display the structure.
6. Insert proper data (at least 5 appropriate records) in all the tables.
7. Describe the structure of table created
8. List all records of each table in ascending order.
9. Delete the department whose loction is Ahmedabad.
10. Display female employee list
11. Display Departname wise employee Names
12. Find the names of the employee who has salary less than 5000 and greater than 2000.
13. Display the names and the designation of all female employee in descending order.
14. Display the names of all the employees who names starts with ‘A’ ends with ‘A’.
15. Find the name of employee and salary for those who had obtain minimum salary.
16. Add 10% raise in salary of all employees whose department is ‘IT’.
17. Count total number of employees of ‘IT’ department.
18. List all employees who born in the current month.
19. Print the record of employee and dept table as “Employee works in department ‘MBA’.
20. List names of employees who are fresher’s (less than 1 year of experience).
21. List department wise names of employees who has more than 5 years of experience.
22. Crete Sequence to generate department ID
23. List department having no employees

Database schema

EMPLOYEE

Column	Oracle type	Meaning / constraint
emp_id	NUMBER(5)	Primary key
emp_name	VARCHAR2(30)	NOT NULL
birth_date	DATE	Employee must be older than 18 (trigger)
gender	VARCHAR2(6)	CHECK: 'Male' or 'Female' (added in Q4)
dept_no	NUMBER(3)	Foreign key → DEPARTMENT(dept_no) (added in Q3)
address	VARCHAR2(50)
designation	VARCHAR2(15)	CHECK: manager, clerk, leader, analyst, designer, coder, tester
salary	NUMBER(10,2)	CHECK: salary > 0
experience	NUMBER(4,1)	Years; DEFAULT 0
email	VARCHAR2(40)	CHECK: contains @ and a dot

DEPARTMENT

Column	Oracle type	Meaning / constraint
dept_no	NUMBER(3)	Primary key
dept_name	VARCHAR2(20)	UNIQUE
location	VARCHAR2(20)	City

Prerequisites

• Complete Set 1 (basic CREATE / INSERT / SELECT).
• Set 2 uses its own DEPARTMENT table. If Set 1's DEPARTMENT table exists, drop it first with the clean-up script below.

Optional clean-up — run this first if you have attempted the practical before. Errors such as *ORA-00942: table or view does not exist* are harmless on the first run.

Optional clean-up

DROP TABLE employee CASCADE CONSTRAINTS;
DROP TABLE department CASCADE CONSTRAINTS;
DROP SEQUENCE dept_seq;

Solutions

Q1 Create the EMP Table with all necessary constraints such as In EMP TABLE: Employee id should be primary key, Department no should be Foreign key, employee age (birth_date) should be greater than 18 years, salary should be greater than zero, email should have (@ and dot) sign in address, designation of employee can be “manager”, “clerk”, “leader”, “analyst”, “designer”, “coder”, “tester”.

Oracle PL/SQL

CREATE TABLE employee (
    emp_id       NUMBER(5)     CONSTRAINT pk_emp PRIMARY KEY,
    emp_name     VARCHAR2(30)  CONSTRAINT nn_emp_name NOT NULL,
    birth_date   DATE          CONSTRAINT nn_emp_bdate NOT NULL,
    gender       VARCHAR2(6),
    dept_no      NUMBER(3),                         -- foreign key added in Q3
    address      VARCHAR2(50),
    designation  VARCHAR2(15)  CONSTRAINT ck_emp_desig
                 CHECK (designation IN ('manager', 'clerk', 'leader', 'analyst',
                                        'designer', 'coder', 'tester')),
    salary       NUMBER(10,2)  CONSTRAINT ck_emp_salary CHECK (salary > 0),
    experience   NUMBER(4,1)   DEFAULT 0,
    email        VARCHAR2(40)  CONSTRAINT ck_emp_email CHECK (email LIKE '%_@_%._%')
);

-- age > 18 cannot be a CHECK constraint (SYSDATE is not allowed there),
-- so it is enforced with a trigger
CREATE OR REPLACE TRIGGER trg_emp_age
BEFORE INSERT OR UPDATE OF birth_date ON employee
FOR EACH ROW
BEGIN
    IF MONTHS_BETWEEN(SYSDATE, :NEW.birth_date) / 12 <= 18 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Employee must be older than 18 years.');
    END IF;
END;
/

Explanation

Primary key, salary, designation. Named constraints (CONSTRAINT pk_emp ...) make error messages clear, e.g. *ORA-02290: check constraint (SCOTT.CK_EMP_SALARY) violated*. The designation list is written exactly as in the question (lower case), so inserted values must be lower case too.

E-mail. LIKE '%_@_%._%' requires at least one character, an @, at least one character, a dot and at least one more character — so both the @ and the dot must be present. On Oracle 10g you could use the stricter REGEXP_LIKE(email, '^[^@]+@[^@]+\.[^@]+$').

Age > 18. A CHECK constraint cannot use SYSDATE, because its result changes every day — Oracle rejects it with *ORA-02436: date or system variable wrongly specified in CHECK constraint*. The BEFORE INSERT OR UPDATE row trigger checks the rule instead: :NEW.birth_date is the value being inserted and MONTHS_BETWEEN(SYSDATE, birth_date) / 12 is the age in years.

Foreign key. It is added in Q3, after DEPARTMENT and its primary key exist.

Expected Output (illustrative, for this article's sample data)

Table created.

Trigger created.

-- test: a 10-year-old employee is rejected
SQL> INSERT INTO employee (emp_id, emp_name, birth_date, salary)
  2  VALUES (999, 'Test Child', ADD_MONTHS(SYSDATE, -120), 1000);
ORA-20001: Employee must be older than 18 years.
ORA-06512: at "SCOTT.TRG_EMP_AGE", line 3
ORA-04088: error during execution of trigger 'SCOTT.TRG_EMP_AGE'

Q2 Create DEPT table with neccessary constraint such as

Oracle SQL

CREATE TABLE department (
    dept_no    NUMBER(3)     NOT NULL,
    dept_name  VARCHAR2(20)  NOT NULL,
    location   VARCHAR2(20)
);

Explanation

The department table is created first with NOT NULL rules; the key constraints required by the next item are added in Q3 with ALTER TABLE, which shows both ways of defining constraints (with CREATE TABLE and with ALTER TABLE).

Expected Output

Table created.

Q3 Department no should be primary key, department name should be unique.

Oracle SQL

ALTER TABLE department ADD CONSTRAINT pk_dept     PRIMARY KEY (dept_no);
ALTER TABLE department ADD CONSTRAINT uq_dept_name UNIQUE (dept_name);

-- now the foreign key of EMPLOYEE (from Q1) can be created
ALTER TABLE employee
ADD CONSTRAINT fk_emp_dept FOREIGN KEY (dept_no) REFERENCES department (dept_no);

Explanation

A primary key implies NOT NULL and UNIQUE and is the target of foreign keys. uq_dept_name prevents two departments with the same name.

The foreign key fk_emp_dept makes Oracle reject an employee whose dept_no does not exist in DEPARTMENT (*ORA-02291: integrity constraint violated - parent key not found*), and reject deleting a department that still has employees (*ORA-02292: child record found*). To remove a constraint later use ALTER TABLE employee DROP CONSTRAINT fk_emp_dept;

Expected Output

Table altered.

Table altered.

Table altered.

Q4 After creation of above tables, modify Employee table by adding the constraints as

Oracle SQL

ALTER TABLE employee
ADD CONSTRAINT ck_emp_gender CHECK (gender IN ('Male', 'Female'));

Explanation

ALTER TABLE ... ADD CONSTRAINT adds a rule to an existing table. Oracle checks existing rows when the constraint is added; if any row broke the rule you would get *ORA-02293: cannot validate - check constraint violated*.

Expected Output

Table altered.

Q5 ‘Male’ or ‘Female’ in gender field and display the structure.

SQL*Plus command

DESC employee

Explanation

Item 5 completes item 4: after adding the gender rule, display the structure. DESC shows column names, NOT NULL and data types — CHECK and foreign-key constraints are listed in USER_CONSTRAINTS (see the note).

Oracle note: List all constraints: SELECT constraint_name, constraint_type, search_condition FROM user_constraints WHERE table_name = 'EMPLOYEE'; (P = primary key, R = foreign key, C = check/not null, U = unique).

Expected Output

 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 EMP_ID                                    NOT NULL NUMBER(5)
 EMP_NAME                                  NOT NULL VARCHAR2(30)
 BIRTH_DATE                                NOT NULL DATE
 GENDER                                             VARCHAR2(6)
 DEPT_NO                                            NUMBER(3)
 ADDRESS                                            VARCHAR2(50)
 DESIGNATION                                        VARCHAR2(15)
 SALARY                                             NUMBER(10,2)
 EXPERIENCE                                         NUMBER(4,1)
 EMAIL                                              VARCHAR2(40)

Q6 Insert proper data (at least 5 appropriate records) in all the tables.

Assumption: Sample data chosen so that every later question returns rows: an Ahmedabad department without employees (Q9), names starting and ending with A (Q14), salaries between 2000 and 5000 (Q12), October birthdays (Q18), freshers (Q20), and a department with no employees (Q23).

Oracle SQL

INSERT INTO department VALUES (10, 'IT',       'Gandhinagar');
INSERT INTO department VALUES (20, 'HR',       'Surat');
INSERT INTO department VALUES (30, 'MBA',      'Vadodara');
INSERT INTO department VALUES (40, 'Sales',    'Rajkot');
INSERT INTO department VALUES (50, 'Admin',    'Ahmedabad');
INSERT INTO department VALUES (60, 'Research', 'Anand');

INSERT INTO employee VALUES (101, 'Amita Verma', TO_DATE('22-10-1995','DD-MM-YYYY'), 'Female', 10, 'Gandhinagar', 'manager', 9000, 8, 'amita.verma@example.com');
INSERT INTO employee VALUES (102, 'Rahul Patel', TO_DATE('15-03-1990','DD-MM-YYYY'), 'Male', 10, 'Ahmedabad', 'coder', 4500, 6, 'rahul.patel@example.com');
INSERT INTO employee VALUES (103, 'Anjana Sinha', TO_DATE('05-10-2000','DD-MM-YYYY'), 'Female', 20, 'Surat', 'clerk', 2500, 0, 'anjana.sinha@example.com');
INSERT INTO employee VALUES (104, 'Karan Mehta', TO_DATE('11-07-1998','DD-MM-YYYY'), 'Male', 30, 'Vadodara', 'analyst', 6000, 3, 'karan.mehta@example.com');
INSERT INTO employee VALUES (105, 'Neha Joshi', TO_DATE('18-12-2001','DD-MM-YYYY'), 'Female', 40, 'Rajkot', 'tester', 3500, 0, 'neha.joshi@example.com');
INSERT INTO employee VALUES (106, 'Vikram Rao', TO_DATE('30-01-1988','DD-MM-YYYY'), 'Male', 40, 'Rajkot', 'leader', 7500, 12, 'vikram.rao@example.com');
INSERT INTO employee VALUES (107, 'Priya Nair', TO_DATE('27-05-1997','DD-MM-YYYY'), 'Female', 30, 'Vadodara', 'designer', 4800, 4, 'priya.nair@example.com');
INSERT INTO employee VALUES (108, 'Harsh Vora', TO_DATE('02-10-2002','DD-MM-YYYY'), 'Male', 10, 'Gandhinagar', 'tester', 2200, 0, 'harsh.vora@example.com');

COMMIT;

Explanation

Departments are inserted first because every employee row must refer to an existing department (parent before child). Dates use TO_DATE with an explicit format so the script works with any NLS date setting. Designations are lower case to satisfy the CHECK constraint from Q1, and every e-mail contains an @ and a dot.

Expected Output

1 row created.  (14 times)

Commit complete.

Q7 Describe the structure of table created

SQL*Plus command

DESC department

Explanation

The EMPLOYEE structure was shown in Q5; this shows DEPARTMENT. DESCRIBE is the full form of DESC.

Expected Output

 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 DEPT_NO                                   NOT NULL NUMBER(3)
 DEPT_NAME                                 NOT NULL VARCHAR2(20)
 LOCATION                                           VARCHAR2(20)

Q8 List all records of each table in ascending order.

Oracle SQL

SELECT * FROM department ORDER BY dept_no;

SELECT emp_id, emp_name, gender, dept_no, designation, salary, experience
FROM   employee
ORDER  BY emp_id;

Explanation

ORDER BY sorts the result; ascending (ASC) is the default. Each table is ordered by its primary key. (Use ORDER BY emp_name to sort by name instead.)

Expected Output (computed from this article's sample data)

DEPT_NO	DEPT_NAME	LOCATION
10	IT	Gandhinagar
20	HR	Surat
30	MBA	Vadodara
40	Sales	Rajkot
50	Admin	Ahmedabad
60	Research	Anand

6 rows selected.

Expected Output (computed from this article's sample data)

EMP_ID	EMP_NAME	GENDER	DEPT_NO	DESIGNATION	SALARY	EXPERIENCE
101	Amita Verma	Female	10	manager	9000	8
102	Rahul Patel	Male	10	coder	4500	6
103	Anjana Sinha	Female	20	clerk	2500	0
104	Karan Mehta	Male	30	analyst	6000	3
105	Neha Joshi	Female	40	tester	3500	0
106	Vikram Rao	Male	40	leader	7500	12
107	Priya Nair	Female	30	designer	4800	4
108	Harsh Vora	Male	10	tester	2200	0

8 rows selected.

Q9 Delete the department whose loction is Ahmedabad.

Oracle SQL

DELETE FROM department
WHERE  location = 'Ahmedabad';

COMMIT;

Explanation

The Admin department in Ahmedabad has no employees, so the delete succeeds.

If the department still had employees, Oracle would refuse with *ORA-02292: integrity constraint (FK_EMP_DEPT) violated - child record found*. That is the foreign key protecting the data. To allow such deletes the foreign key would be defined with ON DELETE CASCADE (delete the employees too) or ON DELETE SET NULL (keep the employees without a department).

Expected Output

1 row deleted.

Commit complete.

Verify the change

Verification query

SELECT * FROM department ORDER BY dept_no;

Expected Output (computed from this article's sample data)

DEPT_NO	DEPT_NAME	LOCATION
10	IT	Gandhinagar
20	HR	Surat
30	MBA	Vadodara
40	Sales	Rajkot
60	Research	Anand

5 rows selected.

Q10 Display female employee list

Oracle SQL

SELECT emp_id, emp_name, dept_no, designation, salary
FROM   employee
WHERE  gender = 'Female'
ORDER  BY emp_id;

Explanation

The value must match the CHECK constraint exactly, including the capital F.

Expected Output (computed from this article's sample data)

EMP_ID	EMP_NAME	DEPT_NO	DESIGNATION	SALARY
101	Amita Verma	10	manager	9000
103	Anjana Sinha	20	clerk	2500
105	Neha Joshi	40	tester	3500
107	Priya Nair	30	designer	4800

4 rows selected.

Q11 Display Departname wise employee Names

Oracle SQL

SELECT d.dept_name, e.emp_name
FROM   department d
JOIN   employee   e ON e.dept_no = d.dept_no
ORDER  BY d.dept_name, e.emp_name;

Explanation

An equi-join combines each employee with the matching department. Sorting by department first groups the names of each department together. The classic Oracle form is FROM department d, employee e WHERE e.dept_no = d.dept_no.

Expected Output (computed from this article's sample data)

DEPT_NAME	EMP_NAME
HR	Anjana Sinha
IT	Amita Verma
IT	Harsh Vora
IT	Rahul Patel
MBA	Karan Mehta
MBA	Priya Nair
Sales	Neha Joshi
Sales	Vikram Rao

8 rows selected.

Q12 Find the names of the employee who has salary less than 5000 and greater than 2000.

Oracle SQL

SELECT emp_name, salary
FROM   employee
WHERE  salary > 2000 AND salary < 5000
ORDER  BY salary;

Explanation

The question uses strict limits (less than / greater than), so > and < are used. BETWEEN 2000 AND 5000 would also include salaries of exactly 2000 and 5000.

Expected Output (computed from this article's sample data)

EMP_NAME	SALARY
Harsh Vora	2200
Anjana Sinha	2500
Neha Joshi	3500
Rahul Patel	4500
Priya Nair	4800

5 rows selected.

Q13 Display the names and the designation of all female employee in descending order.

Oracle SQL

SELECT emp_name, designation
FROM   employee
WHERE  gender = 'Female'
ORDER  BY emp_name DESC;

Explanation

DESC after the column sorts from Z to A. The question does not say which column to sort, so the name is used.

Expected Output (computed from this article's sample data)

EMP_NAME	DESIGNATION
Priya Nair	designer
Neha Joshi	tester
Anjana Sinha	clerk
Amita Verma	manager

4 rows selected.

Q14 Display the names of all the employees who names starts with ‘A’ ends with ‘A’.

Oracle SQL

SELECT emp_name
FROM   employee
WHERE  UPPER(emp_name) LIKE 'A%A';

Explanation

'A%A' means: first letter A, anything in between, last letter A. Names are stored in mixed case (Amita Verma), so UPPER makes the test case-insensitive; without it 'A%A' would not match the lower-case final *a*.

Expected Output (computed from this article's sample data)

EMP_NAME
Amita Verma
Anjana Sinha

2 rows selected.

Row order can differ in your Oracle session because this query has no ORDER BY.

Q15 Find the name of employee and salary for those who had obtain minimum salary.

Oracle SQL

SELECT emp_name, salary
FROM   employee
WHERE  salary = (SELECT MIN(salary) FROM employee);

Explanation

The subquery returns the lowest salary; the outer query returns every employee with that salary (all of them, if several share it). WHERE salary = MIN(salary) is an error because aggregate functions are not allowed in WHERE.

Expected Output (computed from this article's sample data)

EMP_NAME	SALARY
Harsh Vora	2200

1 row selected.

Q16 Add 10% raise in salary of all employees whose department is ‘IT’.

Oracle SQL

UPDATE employee
SET    salary = salary * 1.10
WHERE  dept_no = (SELECT dept_no FROM department WHERE dept_name = 'IT');

COMMIT;

Explanation

A subquery finds the department number of IT, so the statement still works if that number changes. salary * 1.10 adds 10%.

Expected Output

3 rows updated.

Commit complete.

Verify the change

Verification query

SELECT emp_name, salary FROM employee WHERE dept_no = 10 ORDER BY emp_id;

Expected Output (computed from this article's sample data)

EMP_NAME	SALARY
Amita Verma	9900
Rahul Patel	4950
Harsh Vora	2420

3 rows selected.

Q17 Count total number of employees of ‘IT’ department.

Oracle SQL

SELECT COUNT(*) AS it_employees
FROM   employee e
JOIN   department d ON d.dept_no = e.dept_no
WHERE  d.dept_name = 'IT';

Explanation

COUNT(*) counts the joined rows that remain after the WHERE filter.

Expected Output (computed from this article's sample data)

IT_EMPLOYEES
3

1 row selected.

Q18 List all employees who born in the current month.

Oracle SQL

SELECT emp_name, birth_date
FROM   employee
WHERE  TO_CHAR(birth_date, 'MM') = TO_CHAR(SYSDATE, 'MM');

Explanation

TO_CHAR(date, 'MM') extracts the month as two digits. Comparing it with the month of SYSDATE ignores the day and year. The result depends on the day you run it.

Expected Output (computed from this article's sample data)

EMP_NAME	BIRTH_DATE
Amita Verma	22-OCT-95
Anjana Sinha	05-OCT-00
Harsh Vora	02-OCT-02

3 rows selected.

Computed for the month in which this article was generated (October). In another month you will see different employees or no rows.

Q19 Print the record of employee and dept table as “Employee works in department ‘MBA’.

Oracle SQL

SELECT e.emp_name || ' works in department ''' || d.dept_name || '''' AS employee_department
FROM   employee e
JOIN   department d ON d.dept_no = e.dept_no
ORDER  BY e.emp_id;

Explanation

|| joins text. Inside a string, two single quotes ('') produce one quote character, so the department name is printed inside quotes exactly like the example. Add WHERE d.dept_name = 'MBA' to show only the MBA department.

Expected Output (computed from this article's sample data)

EMPLOYEE_DEPARTMENT
Amita Verma works in department 'IT'
Rahul Patel works in department 'IT'
Anjana Sinha works in department 'HR'
Karan Mehta works in department 'MBA'
Neha Joshi works in department 'Sales'
Vikram Rao works in department 'Sales'
Priya Nair works in department 'MBA'
Harsh Vora works in department 'IT'

8 rows selected.

Q20 List names of employees who are fresher’s (less than 1 year of experience).

Oracle SQL

SELECT emp_name, designation, experience
FROM   employee
WHERE  NVL(experience, 0) < 1;

Explanation

experience < 1 matches the question's definition of a fresher. NVL treats a missing (NULL) experience as 0, so such employees are also listed.

Expected Output (computed from this article's sample data)

EMP_NAME	DESIGNATION	EXPERIENCE
Anjana Sinha	clerk	0
Neha Joshi	tester	0
Harsh Vora	tester	0

3 rows selected.

Row order can differ in your Oracle session because this query has no ORDER BY.

Q21 List department wise names of employees who has more than 5 years of experience.

Oracle SQL

SELECT d.dept_name, e.emp_name, e.experience
FROM   employee e
JOIN   department d ON d.dept_no = e.dept_no
WHERE  e.experience > 5
ORDER  BY d.dept_name, e.emp_name;

Explanation

The join supplies the department name and ORDER BY groups the rows department-wise.

Expected Output (computed from this article's sample data)

DEPT_NAME	EMP_NAME	EXPERIENCE
IT	Amita Verma	8
IT	Rahul Patel	6
Sales	Vikram Rao	12

3 rows selected.

Q22 Crete Sequence to generate department ID

Oracle SQL

CREATE SEQUENCE dept_seq
    START WITH   70
    INCREMENT BY 10
    NOCACHE;

INSERT INTO department VALUES (dept_seq.NEXTVAL, 'Marketing', 'Bharuch');
COMMIT;

SELECT dept_seq.CURRVAL FROM dual;

Explanation

A sequence is an Oracle object that generates unique numbers. NEXTVAL returns the next number (70, then 80, ...); CURRVAL returns the last number generated in your session. Oracle 10g has no AUTO_INCREMENT; sequences are used instead.

START WITH 70 avoids clashing with the existing department numbers 10–60. NOCACHE prevents gaps after a restart, which is easier to follow in the lab.

Oracle note: Oracle 12c and later also support GENERATED AS IDENTITY columns, but this practical targets Oracle 10g, so a sequence is used.

Expected Output

Sequence created.

1 row created.

Commit complete.

   CURRVAL
----------
        70

Verify the change

Verification query

SELECT * FROM department ORDER BY dept_no;

Expected Output (computed from this article's sample data)

DEPT_NO	DEPT_NAME	LOCATION
10	IT	Gandhinagar
20	HR	Surat
30	MBA	Vadodara
40	Sales	Rajkot
60	Research	Anand
70	Marketing	Bharuch

6 rows selected.

Q23 List department having no employees

Oracle SQL

SELECT d.dept_no, d.dept_name, d.location
FROM   department d
WHERE  NOT EXISTS (SELECT 1
                   FROM   employee e
                   WHERE  e.dept_no = d.dept_no)
ORDER  BY d.dept_no;

Explanation

NOT EXISTS is true when the correlated subquery finds no employee for the current department. An equivalent is WHERE dept_no NOT IN (SELECT dept_no FROM employee WHERE dept_no IS NOT NULL) — the IS NOT NULL matters, because a NULL in a NOT IN list makes the condition unknown and returns no rows.

Expected Output (computed from this article's sample data)

DEPT_NO	DEPT_NAME	LOCATION
60	Research	Anand
70	Marketing	Bharuch

2 rows selected.

Important concepts used

Primary key

Uniquely identifies each row; implies NOT NULL and UNIQUE.

Foreign key

Value must exist in the referenced parent table (or be NULL). Protects relationships.

CHECK

A condition every row must satisfy. Cannot use SYSDATE, USER or other changing values.

ALTER TABLE ADD/DROP CONSTRAINT

Adds a constraint to an existing table or removes one.

Row trigger

PL/SQL code that runs automatically for each inserted/updated row; used here for the age rule.

Equi-join

Combines rows of two tables where the join columns are equal.

Sequence

Database object that generates unique numbers via NEXTVAL / CURRVAL.

Common mistakes

• Trying CHECK (MONTHS_BETWEEN(SYSDATE, birth_date)/12 > 18) — fails with ORA-02436.
• Creating the EMPLOYEE foreign key before DEPARTMENT exists (ORA-00942) or inserting employees before departments (ORA-02291).
• Inserting 'Manager' when the CHECK constraint lists 'manager' — values are case-sensitive.
• Using WHERE salary = MIN(salary) — aggregate functions must be in a subquery or HAVING.
• Writing LIKE 'A%A' on mixed-case names without UPPER and getting no rows.
• Using NOT IN against a column that contains NULL and getting no rows.

Viva questions

What is the difference between a primary key and a unique key?

Both prevent duplicates. A table can have only one primary key and it cannot be NULL; it can have many unique keys and a unique column may contain NULLs.

What is referential integrity?

The rule that a foreign key value must match an existing primary/unique key value in the parent table, so there are no orphan child rows.

Why can't SYSDATE be used in a CHECK constraint?

A constraint must always give the same result for the same row. SYSDATE changes, so a row valid today could be invalid tomorrow. Oracle raises ORA-02436; a trigger is used instead.

How do you add or drop a constraint on an existing table?

ALTER TABLE t ADD CONSTRAINT name ...; and ALTER TABLE t DROP CONSTRAINT name;

What does ON DELETE CASCADE do?

When a parent row is deleted, the matching child rows are deleted automatically.

What are NEXTVAL and CURRVAL?

Pseudo-columns of a sequence. NEXTVAL generates and returns the next number; CURRVAL returns the current value in the session and works only after NEXTVAL has been used once.

How do you print a single quote inside a string?

Write two single quotes: 'It''s' prints It's.

Summary

Set 2 showed how constraints keep data valid: primary and foreign keys protect identity and relationships, CHECK constraints restrict values, ALTER TABLE adds rules later, and a trigger handles a date-dependent rule that a CHECK cannot. The queries practised joins, concatenation, subqueries, date functions and sequences — the core skills for the larger Set 3.

Download script

set-02-employee-and-department.sql

All CREATE, INSERT and solution statements of Set 2 in one file — run it in SQL*Plus with @set-02-employee-and-department.sql.

Related practicals

• Set 1 — DEPARTMENT Table
• Set 3 — Salesmen, Customers and Orders
• All 10 practical sets
• Viva questions

← Previous practicalSet 1: DEPARTMENT TableNext practical →Set 3: Salesmen, Customers and Orders

Independent educational resource — not an official GTU publication. Verify questions and solutions with your current syllabus and lab instructor. Disclaimer