By Anjeev Kr Singh – Computer Science Educator
Published on : November 20, 2022 | Updated on : January 20, 2024
Unit 1 – Database Concepts
NCERT Book Exercise Solution
Que 1. Consider the following Employee table:
The primary key of this table is Employee_ID and Manager_ID is a foreign key that references
Employee_ID.
Write SQL commands for the following:
(a) Create the above table
Answer: CREATE TABLE Employee (
Employee_ID integer PRIMARY KEY,
Employee_Name VARCHAR(30),
Job_Title VARCHAR(25),
Salary DECIMAL(10),
Bonus INTEGER,
Age INTEGER,
Manager_ID INTEGER,
FOREIGN KEY (Manager_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)
);
(b) Insert values as shown above.
Answer: Inserting values in table Employee as given in the table:-
- INSERT INTO Employee VALUES(1201, ‘DIVYA’, ‘PRESIDENT’, 50000, NULL, 29, NULL);
- INSERT INTO Employee VALUES(1205, ‘AMYRA’, ‘MANAGER’, 300000, 2500, 26, 1201 );
- INSERT INTO Employee VALUES(1211, ‘RAHUL’, ‘ANALYST’, 20000, 1500, 23, 1205 );
- INSERT INTO Employee VALUES( 1213, ‘MANISH’, ‘SALESMAN’, 15000, NULL, 22, 1205);
- INSERT INTO Employee VALUES(1216, ‘MEGHA’, ‘ANALYST’, 22000, 1300, 25, 1201 );
- INSERT INTO Employee VALUES(1217, ‘MOHIT’, ‘SALESMAN’, 16000, NULL, 22, 1205 );
(c) Delete the Employee having Employee_ID 1217.
Answer: DELETE FROM EMPLOYEE
WHERE EMPLOYEE_ID = 1217;
Basics of Database Concepts Question Answer
(d) Update the salary of ‘Amyra’ to 40000.
Answer: UPDATE EMPLOYEE SET SALARY = 40000
WHERE NAME = “AMYRA”;
(e) Alter the table Employee so that NULL values are not allowed for the Age column.
Answer: ALTER TABLE EMPLOYEE
MODIFY AGE INTEGER NOT NULL;
(f) Write a query to display the names and salaries of those employees whose salaries are greater than 20000.
Answer: SELECT EMPLOYEE_NAME, SALARY
FROM EMPLOYEE
WHERE SALARY > 20000;
(g) Write a query to display details of employees who are not getting any bonus.
Answer: SELECT * FROM EMPLOYEE
WHERE BONUS IS NULL;
(h) Write a query to display the names of employees whose name contains “a” as the last
alphabet.
Answer: SELECT EMPLOYEE_NAME
FROM EMPLOYEE
WHERE EMPLOYEE_NAME LIKE “%a”;
(i) Write a query to display the name and Job title of those employees whose Manager_ID is
1201.
Answer: SELECT EMPLOYEE_NAME, JOB_TITLE
FROM EMPLOYEE
WHERE MANAGER_ID = 1201;
Database & Table Creation and Manipulation Commands Question Answer
(j) Write a query to display the name and Job title of those employees whose Manager is “Amyra”.
Answer: SELECT EMPLOYEE_NAME, JOB_TITLE
FROM EMPLOYEE
WHERE MANAGER_ID = 1205;
OR
SELECT EMPLOYEE_NAME, JOB_TITLE
FROM EMPLOYEE
WHERE MANAGER_ID = (
SELECT EMPLOYEE_ID FROM EMPLOYEE WHERE EMPLOYEE_NAME = “AMYRA”);
(k) Write a query to display the name and Job title of those employees aged between 26 years and 30 years (both inclusive).
Answer: SELECT EMPLOYEE_NAME, JOB_TITLE
FROM EMPLOYEE
WHERE AGE BETWEEN 26 AND 30;
OR
SELECT EMPLOYEE_NAME, JOB_TITLE
FROM EMPLOYEE
WHERE AGE >= 26 AND AGE <= 30;
Que 2. A Railway company uses machines to sell tickets. The machine details and daily sales
information are recorded in two tables:
The primary key of the table Machine is Machine_ID. Records in the table Sales are uniquely identified by the fields Machine_ID and Date.
(a) Create the tables Machine and Sales.
Answer: Table – Machine
CREATE TABLE MACHINE (
Machine_ID CHAR(3) PRIMARY KEY,
Station CHAR(30) NOT NULL
);
Table – Sales
CREATE TABLE SALES (
Machine_ID CHAR(3),
Date DATE,
Tickets_Sold INTEGER,
Income DECIMAL(8,2),
PRIMARY KEY(Machine_ID, Date),
FOREIGN KEY(Machine_ID) REFERENCES Machine(Machine_ID)
);
(b) Write a query to find the number of ticket machines in each station.
Answer: SELECT STATION, COUNT(MACHINE_ID)
FROM MACHINE
GROUP BY STATION;
(c) Write a query to find the total ticket income of the station “New Delhi” of each day.
Answer: SELECT DATE, SUM(INCOME)
FROM SALES, MACHINE
WHERE SALES.MACHINE_ID = MACHINE.MACHINE_ID AND STATION = “NEW DELHI”
GROUP BY DATE;
(d) Write a query to find the total number of tickets sold by the machine (Machine_ID = 122)
till date.
Answer: SELECT SUM(TICKETS_SOLD)
FROM SALES
GROUP BY MACHINE_ID
HAVING MACHINE_ID = 122;
OR
SELECT SUM(TICKETS_SOLD)
FROM SALES
WHERE MACHINE_ID = 122;
For 800+Questions based on Database Concepts and other chapters 
Buy Chapter wise Question bank Class 12 Information Technology 802
About the Author
