Term 2 IP Practical File Class 12 Free Sample

Share with others

IP Practical File Class 12

Term 2 – Distribution of Theory Marks

Unit No.	Unit Name	Marks
2	Database Query using SQL	25
3	Introduction to Computer Networks	10
	Total	35

IP Practical File Class 12

Unit 2

Database Query using SQL

1. Math functions: POWER (), ROUND (), MOD ().

2. Text functions: UCASE ()/UPPER (), LCASE ()/LOWER (), MID ()/SUBSTRING ()/SUBSTR (), LENGTH (), LEFT (), RIGHT (), INSTR (), LTRIM (), RTRIM (), TRIM ().

3. Date Functions: NOW (), DATE (), MONTH (), MONTHNAME (), YEAR (), DAY (), DAYNAME (). Aggregate Functions: MAX (), MIN (), AVG (), SUM (), COUNT (); using COUNT (*).

4. Querying and manipulating data using Group by, Having, Order by.

Unit 3

Introduction to Computer Networks

1. Introduction to networks, Types of network: LAN, MAN, WAN.

2. Network Devices: modem, hub, switch, repeater, router, gateway.

3. Network Topologies: Star, Bus, Tree, Mesh.

4. Introduction to Internet, URL, WWW and its applications- Web, email, Chat, VoIP.

5. Website: Introduction, difference between a website and webpage, static vs dynamic web page, web server and hosting of a website.

6. Web Browsers: Introduction, commonly used browsers, browser settings, add-ons and plug-ins, cookies.

Distribution of Practical Marks

Topic	Marks
SQL queries (pen and paper)	7
Practical File – 12 SQL Queries	2
Final Project Submission	3
Viva	3
Total	15

IP Practical File Class 12

Suggested Practical List

Data Management

Create a student table with the student id, name, and marks as attributes where the student id is
the primary key.
Insert the details of a new student in the above table.
Delete the details of a student in the above table.
Use the select command to get the details of the students with marks more than 80.
Find the min, max, sum, and average of the marks in a student marks table.
Find the total number of customers from each country in the table (customer ID, customer
Name, country) using group by.
Write a SQL query to order the (student ID, marks) table in descending order of the marks.

Project Work

The aim of the class project is to create tangible and useful IT applications. The learner may
identify a real-world problem by exploring the environment. e.g. Students can visit shops/business
places, communities or other organizations in their localities and enquire about the functioning of
the organization, and how data are generated, stored, and managed.

The learner can take data stored in csv or database file and analyze using Python libraries and
generate appropriate charts to visualize. If an organization is maintaining data offline, then the
learner should create a database using MySQL and store the data in tables.

Data can be imported in Pandas for analysis and visualization. Learners can use Python libraries
of their choice to develop software for their school or any other social good. Learners should be
sensitized to avoid plagiarism and violation of copyright issues while working on projects. Teachers
should take necessary measures for this. Any resources (data, image etc.) used in the project
must be suitably referenced.

The project can be done individually or in groups of 2 to 3 students. The project should be started
by students at least 6 months before the submission deadline

INFORMATICS PRACTICES

PRACTICAL FILE

CLASS 12

2021-2022

INDEX

S. NO	QUERY	DATE	SIGN
1	Write SQL query to create the table Employee
2	Write SQL query to insert any four records in the Employee table.
3	Write SQL query to display all the records of all Female Employees.
4	Write SQL query to display total salary of all the Employees.
5	Write SQL query to display the average salary of Employees.
6	Write SQL query to display the details of Employees with salary more than 40000.
7	Write SQL query to display the total number of male Employees in the table.
8	Write SQL query to display the maximum and minimum Salary of Employees
9.	Write SQL query to display the detail of Employee who joined in year 2015.
10.	Write SQL query to display name of employees in upper case who joined on “Wednesday”.
11.	Write SQL query to display first 4 alphabets of Employee Name and last 3 characters of Department of all Employees.
12.	Write SQL query to display the salary Department wise.

IP Practical File Class 12

QUERY – 1

Create the following table: Employee

Field Name	Datatype	Constraint
Empid	Integer	Primary Key
Name	Varchar (20)	Not Null
Department	Varchar (20)	Not Null
Salary	Integer
Gender	Varchar (1)
DOJ	Date

IP Practical File Class 12

SOLUTION

Create table Employee (

Empid Integer Primary Key,

Name Varchar(20) Not Null,

Department Varchar(20) NOT NULL,

Salary Integer,

Gender Varchar(1)

);

QUERY – 2

Insert the following records in the table created above:

Empid	Name	Department	Salary	Gender	DOJ
1001	Aman	Sales	40000	M	2010-11-21
1002	Suman	Accounting	35000	F	2009-09-25
1003	Ravi	Sales	45000	M	2015-05-02
1004	Sakshi	Accounting	35000	F	2016-06-15

IP Practical File Class 12

SOLUTION

INSERT INTO EMPLOYEE VALUES (1001, ‘Aman’, ‘Sales ‘,40000,’M’, ‘2010-11-21’);

INSERT INTO EMPLOYEE VALUES (1002, ‘Suman’, ‘Accounting’, 35000, ‘F’, ‘2009-09-25’);

INSERT INTO EMPLOYEE VALUES (1003, ‘Ravi’, ‘Sales’, 45000, ‘M’, ‘2015-05-02’);

INSERT INTO EMPLOYEE VALUES (1004, ‘Sakshi’, ‘Accounting’, 35000, ‘F’, ‘2016-06-15’);

NOTE: After inserting record table will look like

Empid    Name     Department             Salary         Gender                        DOJ

1001     Aman       Sales                        40000              M                            2010-11-21
1002     Suman     Accounting             35000                F                            2009-09-25
1003     Ravi         Sales                        45000               M                           2015-05-02
1004     Sakshi     Accounting              35000               F                             2016-06-15

QUERY – 3

Display all the records of Female Employees.

SOLUTION

Select * from Employee where Gender = ‘F’;

Output:

Empid        Name         Department           Salary        Gender

2                 Suman          Accounting          35000        F
4                 Sakshi           Accounting          35000        F

QUERY – 4

Display total salary of all the Employees.

SOLUTION

Select sum(Salary) from Employee;

Output:

sum(Salary)
155000

QUERY – 5

Display the average salary of Employees.

SOLUTION

Select avg(Salary) from Employee;

Output:

avg(Salary)
38750.00

QUERY – 6

Display the maximum and minimum Salary of Employees

SOLUTION

Select max(Salary), min(Salary) from Employee;

Output:

max(Salary)     min(Salary)
45000              35000

QUERY – 7

Display total number of male Employees.

SOLUTION

Select count(*) from Employee where Gender = ‘M’;

Output:

count(*)
2

QUERY – 8

Display the average salary of Employees.

SOLUTION

Select avg(Salary) from Employee;

Output:

avg(Salary)
38750.00

QUERY – 9

Display the detail of Employees who joined in year 2015.

SOLUTION

Select * from Employee where year (DOJ)=2015;

Output:

Empid      Name         Department           Salary            Gender     DOJ
3               Ravi            Sales                       45000                M        2015-05-02

QUERY – 10

Write SQL query to display name of employees in upper case who joined on “Wednesday”.

SOLUTION

Select upper(Name) from EMPLOYEE where dayname(DOJ) = “Wednesday”;

Output:

upper(Name)
SAKSHI

QUERY – 11

Write SQL query to display first 4 characters of Employee Name and last 3 characters of Department of all Employees.

SOLUTION

Select left(Name,4), right(Department,3) from Employee;

Output:

left(Name,4)           right(Department,3)
Aman                        les
Suma                        ing
Ravi                          les
Saks                          ing

QUERY – 12

Write SQL query to display the salary Department wise.

SOLUTION

Select sum(Salary), Department from Employee group by Department;

Output:

sum(Salary)      Department
85000                 Sales
70000                Accounting

Click to download file in pdf

IP Practical File Class 12

Disclaimer : I tried to give you the correct format of ” IP Practical File Class 12 ” , but if you feel that there is/are mistakes in the format of “ IP Practical File Class 12 “ given above, you can directly contact me at csiplearninghub@gmail.com.