GTU MCA RDBMS Set 1 Solution: DEPARTMENT Table Oracle SQL Queries

Official Set 1

Set 1 — DEPARTMENT Table (Basic SQL: CREATE, INSERT, SELECT, UPDATE, DELETE)

• CREATE TABLE
• DESC
• INSERT
• WHERE
• LIKE
• BETWEEN
• UPDATE
• DELETE

Subject

MC01094031 RDBMS

Tool

Oracle 10g or above

Questions

10

Status

Question list: official GTU PDF

Source check: Question wording, numbering and the seven department records are taken from the official GTU MCA RDBMS practical list (w.e.f. 2024-25). Department 60 (TRG) has no location in the PDF, so it is inserted with NULL.

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

This is the first practical of the GTU MCA Semester 1 RDBMS lab. You create a single table, put a few department records in it and then ask the database simple questions. Every later practical builds on the commands you practise here.

Learning objectives

• Create a table using Oracle data types NUMBER and VARCHAR2.
• View a table's structure with the SQL*Plus DESC command.
• Insert, display, filter, update and delete rows.
• Use the WHERE, LIKE and BETWEEN operators.

Original question list

1. Create the Simple DEPARTMENT Table.
2. Display structure of department table.
3. Insert below records into Department Table: (10, Account, NY), (20, HR, NY), (30, Production, DL), (40, Sales, NY), (50, EDP, MU), (60, TRG, –), (110, RND, AH).
4. Display all records of Department table.
5. Display all department belonging to location 'NY'.
6. Display details of Department 10.
7. List all department names starting with 'A'.
8. List all departments whose number is between 1 and 100.
9. Delete 'TRG' department.
10. Change department name 'EDP' to 'IT'.

Database schema

DEPARTMENT

Column	Oracle type	Meaning / constraint
dept_no	NUMBER(3)	Department number (primary key)
dept_name	VARCHAR2(20)	Department name
location	VARCHAR2(20)	City / location code

Prerequisites

• Oracle 10g or above (SQL*Plus, SQL Developer or Oracle Live SQL).
• A user account with CREATE TABLE privilege.
• If a DEPARTMENT table already exists from an earlier attempt, drop it first: DROP TABLE department;

Solutions

Q1 Create the Simple DEPARTMENT Table.

Oracle SQL

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

Explanation

NUMBER(3) stores up to three digits (enough for 110). VARCHAR2(n) is Oracle's variable-length string type. The question asks for a *simple* table, so only a primary key and a NOT NULL constraint are added; constraints are studied in detail in Set 2.

Expected Output

Table created.

Q2 Display structure of department table.

SQL*Plus command

DESC department

Explanation

DESC (short for DESCRIBE) is a **SQL*Plus command**, not a SQL statement, so it does not need a semicolon. In SQL Developer you can run it the same way, or use the query on USER_TAB_COLUMNS shown in the note.

Oracle note: Equivalent SQL using the data dictionary: SELECT column_name, data_type, data_length, nullable FROM user_tab_columns WHERE table_name = 'DEPARTMENT'; — table names are stored in UPPERCASE in the dictionary.

Expected Output

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

Q3 Insert below records into Department Table: (10, Account, NY), (20, HR, NY), (30, Production, DL), (40, Sales, NY), (50, EDP, MU), (60, TRG, –), (110, RND, AH).

Assumption: The PDF leaves the location of TRG blank, so NULL is inserted. You can also omit the column: INSERT INTO department (dept_no, dept_name) VALUES (60, 'TRG');

Oracle SQL

INSERT INTO department VALUES (10,  'Account',    'NY');
INSERT INTO department VALUES (20,  'HR',         'NY');
INSERT INTO department VALUES (30,  'Production', 'DL');
INSERT INTO department VALUES (40,  'Sales',      'NY');
INSERT INTO department VALUES (50,  'EDP',        'MU');
INSERT INTO department VALUES (60,  'TRG',        NULL);
INSERT INTO department VALUES (110, 'RND',        'AH');
COMMIT;

Explanation

Each INSERT adds one row; the values must be listed in the same order as the columns were created. String values go inside single quotes. COMMIT makes the inserted rows permanent so that other sessions can see them.

Expected Output

1 row created.  (printed 7 times)

Commit complete.

Q4 Display all records of Department table.

Oracle SQL

SELECT * FROM department;

Explanation

* means *all columns*. Without a WHERE clause, every row is returned.

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

DEPT_NO	DEPT_NAME	LOCATION
10	Account	NY
20	HR	NY
30	Production	DL
40	Sales	NY
50	EDP	MU
60	TRG
110	RND	AH

7 rows selected.

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

Q5 Display all department belonging to location 'NY'.

Oracle SQL

SELECT * FROM department
WHERE  location = 'NY';

Explanation

String comparison in Oracle is case-sensitive: 'ny' would not match 'NY'. Use UPPER(location) = 'NY' if your data has mixed case. TRG, whose location is NULL, is never returned by a comparison — NULL is not equal to anything.

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

DEPT_NO	DEPT_NAME	LOCATION
10	Account	NY
20	HR	NY
40	Sales	NY

3 rows selected.

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

Q6 Display details of Department 10.

Oracle SQL

SELECT * FROM department
WHERE  dept_no = 10;

Explanation

Numbers are written without quotes. Because dept_no is the primary key, at most one row can be returned.

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

DEPT_NO	DEPT_NAME	LOCATION
10	Account	NY

1 row selected.

Q7 List all department names starting with 'A'.

Oracle SQL

SELECT dept_name FROM department
WHERE  dept_name LIKE 'A%';

Explanation

In LIKE, % matches any number of characters (including none) and _ matches exactly one character. 'A%' means *starts with A*.

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

DEPT_NAME
Account

1 row selected.

Q8 List all departments whose number is between 1 and 100.

Oracle SQL

SELECT * FROM department
WHERE  dept_no BETWEEN 1 AND 100;

Explanation

BETWEEN a AND b is inclusive of both ends, so it is the same as dept_no >= 1 AND dept_no <= 100. Department 110 (RND) is outside the range and is not shown.

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

DEPT_NO	DEPT_NAME	LOCATION
10	Account	NY
20	HR	NY
30	Production	DL
40	Sales	NY
50	EDP	MU
60	TRG

6 rows selected.

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

Q9 Delete 'TRG' department.

Oracle SQL

DELETE FROM department
WHERE  dept_name = 'TRG';

COMMIT;

Explanation

Always write the WHERE clause before running a DELETE — without it every row is deleted. Run the same condition with SELECT first to see which rows will be affected.

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	Account	NY
20	HR	NY
30	Production	DL
40	Sales	NY
50	EDP	MU
110	RND	AH

6 rows selected.

Q10 Change department name 'EDP' to 'IT'.

Oracle SQL

UPDATE department
SET    dept_name = 'IT'
WHERE  dept_name = 'EDP';

COMMIT;

Explanation

UPDATE ... SET changes column values in the rows that match WHERE. Only department 50 changes.

Expected Output

1 row updated.

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	Account	NY
20	HR	NY
30	Production	DL
40	Sales	NY
50	IT	MU
110	RND	AH

6 rows selected.

Important concepts used

DDL vs DML

CREATE is DDL (defines structure; auto-commits). INSERT, UPDATE and DELETE are DML (change data; need COMMIT).

Case sensitivity

Keywords and table names are not case-sensitive, but data inside quotes is.

Inclusive BETWEEN

BETWEEN 1 AND 100 includes both 1 and 100.

Wildcards

% = any sequence of characters, _ = exactly one character.

Common mistakes

• Using double quotes for string values ("NY"). Oracle treats double quotes as identifiers — use single quotes.
• Writing DELETE FROM department; without a WHERE clause and deleting every row.
• Forgetting COMMIT and then wondering why another session (or a reconnect) cannot see the rows.
• Putting a semicolon after DESC department in some tools and getting an error — DESC is a SQL*Plus command.
• Using LIKE 'A*' — * is not a wildcard in SQL.

Viva questions

What is the difference between CHAR and VARCHAR2?

CHAR(n) is fixed length and pads with spaces up to n characters. VARCHAR2(n) is variable length and stores only the characters entered, so it saves space.

Is DESC a SQL command?

No. DESC is a SQL*Plus (client) command that shows column names, NULL constraint and data types. The SQL equivalent is a query on USER_TAB_COLUMNS.

What is the difference between DELETE and TRUNCATE?

DELETE is DML, can use WHERE, fires triggers and can be rolled back. TRUNCATE is DDL, removes all rows, is faster and cannot be rolled back.

Why do we use COMMIT?

COMMIT ends the transaction and makes all changes made since the last commit permanent and visible to other users.

What does % mean in LIKE?

It matches zero or more characters. 'A%' finds values starting with A; '%A' finds values ending with A.

Is BETWEEN inclusive?

Yes, both boundary values are included.

Summary

You created the DEPARTMENT table, viewed its structure, inserted the seven official departments (one with a NULL location) and practised SELECT with WHERE, LIKE and BETWEEN, followed by a controlled DELETE and UPDATE. Always check the affected rows with a SELECT before running DELETE or UPDATE, and COMMIT when the result is correct.

Download script

set-01-department-table.sql

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

Related practicals

• Set 2 — EMPLOYEE and DEPARTMENT
• All 10 practical sets
• Viva questions

← BackPractical indexNext practical →Set 2: EMPLOYEE and DEPARTMENT

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