Class 12 IT 802 Unit 1 Database concepts NCERT Book solution

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;

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;

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;

SELECT SUM(TICKETS_SOLD)
FROM SALES
WHERE MACHINE_ID = 122;