CBSE Class 11 Information Practices Worksheet

Q1) What is DBMS? Explain advantages of DBMS?

Q2) Explain dfferent View of Data?

Q3) Explain Different Type of Data Model?

Q4) Define Following ?

a) Primary key b) Foreign key c) Candidate Key d) tuple

Q5) Explain Different Data Type of My SQL?

Q6) Differentiate between Distinct and All clause?

Q7) Differentiate between IN and BETWEEN Operator using an example?

Q8) Explain DCL Command With Example?

Q9) Explain E-Learning, E-governance And E-learning.

Q10) a. Consider the following tables EMPLOYEE and write the SQL query for the following

requirements.

Eid Name Deptid Qualification Sex Points Basic DA HRA Bonus

1 Sunil 101 MCA F 20 6000 2000 2300 200

2 Virender 101 BCA M 15 2000 300 300 30

3 Mukesh 102 B.A M 15 1000 300 300 40

4 Rakesh 102 M.A M 40 1500 390 490 30

5 Sumit 103 B.Tech M 32 8000 900 900 80

6 Jyoti 101 M.Tech F 12 10000 300 490 89

1. Create the structure of the above table

2. Insert any dummy record

3. Display the Names begin with alphabet “V”

4. Display the records who are females.

5. Display the name and qualification who are “B. Tech.”

6. Add one more field for the employee table -DOJ (date of joining)

7. Do not display the records of “Jyoti”, “Mukesh”.

8. To insert a new record in the EMPLOYEE table with following data:

7,’Vikas’, 103,’MCA’,’M’, 36,12000,1000,500,90.

9. Display the Name, deptid, Basic whose date of joining is after 23/09/2000.

10. Display the records according to their Names.

1. Give the output of the following code fragment.
(a) int ch=10;
System.out.println (ch++) ;
System.out.println (++ch) ;
System.out.print (ch) ;
(i) What output does the above code fragment produce?
(ii) What is the effect of replacing ++ch with ch+1?
(b) int val,res,n=1000;
res= (n+val) >1500?200:400;
System.out.println (res) ;
(i) If the input is 400
(ii) If the input is 1000
(iii) If the input is 1500

2. What will be the result of the following two expressions if i=15 initially?
(i) ++i<=15 (ii) i++<=15

3. Given the two following expressions:
(i) a=4 (ii) a==4
(i) How are these two different?
(ii) What will be the result of the expression if the value of a is 6 initially?

4. Will the value of y be the same for the two cases given below if x=42?
(i) y=++x; (ii) y=x++;

5. Consider the following code snippet:
int i=10;
int n=i++%5;
What are the values of i and n after the code is executed?

6. What will be the value of p=p*++j where j is 22 and p=3 initially?

7. What will be the result of the following expression
If (i) age=25 (ii) age=65 (iii) age=85 
age>65?350:100

8. Develop an application that accepts principal amount, rate of interest and Time from the user then it computes the simple interest and displays it.

9. Design an application for four function calculator.

10. What is a character constant in Java? How are non graphic characters represented in java?

11. Which of the following will produce a value of 22 if x=22.9?
(i) Math.ceil(x) (ii) Math.round(x)
(iii) Math.abs(x) (iv)Math.floor(x)

12. What is a “fall through”?

13. Give the output of the following code.
int m=100;
while(m>0)
{ if (m<10)
break;
m=m-10;
}
System.out.println(“m is”+m);

14. Write a program to input a digit and print it in words.

15. Compare an if and? : operator.

16. Rewrite the following code fragment using switch.
if(ch==’E’)
eastern++;
if(ch==’W’)
western++;
if(ch==’N’)
northern++;
if(ch==’S’)
southern++;
else
unknown++; 

1) What is Constraint? Explain all available in MYSQL.

2) What is MYSQL?

3) What is Degree and Cardinality?

4) What are the Data types available in MYSQL?

5) What is SQL? Explain its Categories.

6) How to create a Table in MYSQL

7) What is Select Command? Explain with where clause.

8) What is the order by Clause? Explain with examples

9) What is operator? Explain all operators with Examples.

10) What is LIMIT clause in MYSQL?

11) What is Alter Command? Explain with Examples

I. Consider a table Emp with the following attributes:

Emp ( empno, ename, job, hiredate, sal, comm., deptt)

Write the SQL commands for the following queries:

(a) Write a query to display employee name, salary and department who are not getting commission.

(b) How many types of jobs are offered to employees.

(c) List the details of those employees who have five lettered names.

(d) Write a query to display all the columns whose name contains ‘p’ as the forth alphabets.

(e) List the detail of all employee who are in computer department sorted by ename.

(f) Display the ename and hiredate of employee who are not in history department.

(g) Display the employee who are getting salary 10000, 12000 and 14000.

(h) Display name and salary of employee who don’t have their commission in the range of 2500 to 4000.

(i) Insert any five records in the above table.

(j) List detail of physics department in descending order of employee number.

II.                                                    CLUB

Write the output for the following queries:

(a) select Coach_Name, Lcase(Sports) from CLUB;

(b) select Mod(age,5) from CLUB where Sex = ‘F’;

(c) select power(3,2) ;

(d) select substr(Coach_Name,1,2) from CLUB where DateofApp>’1996-04-12’;

(e) select concat(Coach_Name,Age) from CLUB where Pay > 1500;

(f) select month(DateofApp), Sports from CLUB where Age < 40;

(g) select Lower(Coach_Name), Length(Sports) from CLUB;

(h) select Dayofweek(‘2009-04-14’);

III. Create table Emp with the following structure:

Ensure the following specification in created table:

(a) ID should be declared as primary key.

(b) User_Id must be unique.

(c) Salary must be greater than 5000.

(d) First_Name & Last_Name must not remain blank.

(e) Increase the size of First_Name to 50 characters.

(f) Delete the record whose Last_Name is Arora.

1. How is compiler different from interpreter?

2. What is the difference between impact and non impact printer? Give example?

3. What is MICR?

4. What do you understand by PC Intrusion?

5. Differentiate between real time and time sharing operating systems?

6. Compare Packages and Utilities?

7. Define the terms Adware,Spyware.

8. Differentiate between Alternate Key and Candidate Key.

9. Prasanth a student of XI created a table ‘Employee’. Subject is one of the columns of this table.To find the details of Employees whose subjects have not been entered, he wrote the following MySQL query, which did not give the desired result.
Select * from Employee where subject=”NULL”;
Write the correct query.

10. Compare CHAR and VARCHAR data types?

11. Write MySQL command that will be used to open an already existing database ‘Test’?

12. A table ‘CUSTOMER’ in a database has cardinality 10 and degree 6. What is the no.of rows and columns in it?

13. Differentiate between ALTER TABLE command and UPDATE command with example.

14. Consider the table STUDENT given below. Write commands in MySQL for (i) to (v) and output for (vi) to (viii)

Table: STUDENT

(i).To list the names of those students who have obtained grade ‘A’ in Tennis.
(ii).To display the different games offered in the school.
(iii).To display the SUPW taken up by the students, whose name starts with ‘S’.
(iv).To insert a new row in the table ‘STUDENT’.
(v).To count the no.of students who have grade ‘A’ either in Game or SUPW.
(vi).Select count(distinct(Game)) from STUDENT;
(vii).Select name, class from student where name like “_e%”;
(viii).Select name from STUDENT where Grade1=”B” or Grade=”B”;
(ix).Write Query to display the current date.
(x). What is the default value of column for which no default value is defined?
(xi).Which keyword is used to substitute null values in a query result?
(xii).Which keyword is used to retrieve unique values in a query result?
(xiii).Which keyword is used to retrieve duplicate values in a query result?

15. What will be the output of the following?
(i) SELECT CHAR(65,67.3,’69.3’);
(ii) SELECT ROUND( 2.378,1);
(iii) SELECT MOD(50,4);
(iv) SELECT MID(‘Compliment’,5,6);
(v) SELECT TRIM(BOTH ‘X’ FROM ‘XXXXBRIGHTXX’);
(vi) SELECT DAYOFMONTH(‘2012-12-02’);
(vii) SELECT RIGHT(‘IP/54/15/46’,2);
(b).The ItemName column of a table “ITEM” is given below.
ItemName
Shampoo
Bath Soap
Moisturizer
Lens Solution
Sanitizer

Q1. Design an application in JAVA to accept Name as input and display the name with title i.e. If male add Mr to it and if Female add Ms to it (Use radio buttons for selection input)

Q2. Design an application in JAVA to accept two names from user and display a short message about Friendship or colleagues according to the button clicked. (Use the Text Area component for message display)

Q3. Design an application in JAVA which accepts the price and quantity from user and display the total amount. (Give user the facility to accept numbers with decimals as input.)

Q4. Design an application in JAVA to accept marks in English, Hindi, Maths from user and display the total marks obtained. (Use the concept of variables while coding)

Q5. Design an application in JAVA to accept user’s name as input. Display it surrounded by special character according to user’s requirement. (You can use $, #,∗,?as the special characters.)

Q6. Design a basic calculator in JAVA which accepts two numbers from user and calculates sum, difference, product, quotient as per the user’s choice. (Based on the use of Arithmetic operators)

Q7. Design an application to calculate Simple Interest. Accept Principle, Rate and Time from user. (Hint: S.I. = (P ∗ R ∗ T)/100)

Q8. Design an Area Calculator capable of calculating the area of a circle, square and Rectangle.(Use Radio buttons to accept figure input and use the visibility, border, enabled and editable properties to make the application presentable .)

Delhi Public School, Bokaro Steel City
Assignment for the Session 2013-2014
Class: XI - Subject: Informatics Practices - Assignment No. 3

Question 1. What is DBMS? Explain advantages of DBMS?
Answer:
A Database Management System (DBMS) is a software package designed to define, manipulate, retrieve, and manage data in a database. It serves as an interface between the database and its end-users or application programs, ensuring that data is consistently organized and easily accessible.

Advantages of a DBMS:
* Reduction of Data Redundancy: It minimizes duplicate data by storing information in a single, centralized location.
* Data Sharing: Multiple authorized users can access and share the same database simultaneously from different locations.
* Data Consistency: Since redundancy is controlled, any update to a data item is automatically reflected globally, preventing contradictory or inconsistent records.
* Data Security: It enforces access control and security permissions to restrict unauthorized users from viewing or modifying sensitive details.
* Backup and Recovery: It manages automated backups and recovery of data to protect against hardware failures or system crashes.
In simple words: A DBMS is like a smart digital filing cabinet that helps save, organize, and protect all your data in one place so multiple people can use it safely without creating duplicate files.

Exam Tip: When describing advantages, use standard technical keywords like "controlling data redundancy," "data consistency," and "data security" as examiners look for these specific terms.

Question 2. Explain dfferent View of Data?
Answer:
Database systems hide complex details of how data is stored and maintained through different levels of abstraction, commonly referred to as the 3-schema architecture or Views of Data:
1. Physical Level (Internal Level): The lowest level of abstraction that describes *how* the data is actually stored in the physical storage blocks (e.g., on hard disks, memory blocks, etc.).
2. Logical Level (Conceptual Level): The next higher level of abstraction that describes *what* data is stored in the database and what relationships exist among those data. It defines the tables, columns, constraints, and data types.
3. View Level (External Level): The highest level of abstraction that describes only a specific part of the entire database. It provides customized, simplified views of the database to different users based on their access permissions (e.g., an employee viewing only their personal profile vs. an HR manager viewing salary tables).
In simple words: Databases have three layers: the physical layer (how files sit on the hard drive), the logical layer (how tables and columns are structured), and the view layer (what the end-user actually sees on their screen).

Exam Tip: Draw a simple three-tier diagram (External View -> Conceptual Schema -> Physical Schema) to visually explain the concept and secure maximum marks.

Question 3. Explain Different Type of Data Model?
Answer:
A data model is a conceptual tool used to describe the structure of a database, including its data, relationships, and constraints. The main types of data models are:
* Relational Model: Data is organized into two-dimensional tables consisting of rows (tuples) and columns (attributes). This is the most widely used model in modern databases (e.g., MySQL, Oracle, PostgreSQL).
* Hierarchical Model: Data is organized into a tree-like structure with a parent-child relationship, where a child node can have only one parent.
* Network Model: Similar to the hierarchical model, but it allows a child node to have multiple parent nodes, forming a graph-like structure.
* Object-Oriented Data Model: Data is represented as objects, combining both the data structure (attributes) and the operations or behaviors associated with it.
In simple words: Data models are different ways to organize files: tables (relational), tree structures (hierarchical), web/graph layouts (network), or as object-oriented entities.

Exam Tip: Clearly emphasize that the "Relational Model" is the most popular model in modern database design and forms the basis of SQL.

Question 4. Define Following ?
a) Primary key b) Foreign key c) Candidate Key d) tuple
Answer:
* a) Primary key: An attribute or set of attributes that uniquely identifies each row (tuple) in a table. It cannot contain NULL values and must contain unique values.
* b) Foreign key: A non-key attribute in one table whose values are derived from the primary key of another table, used to establish a link or relationship between the two tables.
* c) Candidate Key: A set of one or more attributes that can uniquely identify a row in a table. All candidate keys are eligible to become the primary key of the table.
* d) Tuple: A single row in a relational database table, which represents a complete record of related data.
In simple words: A primary key is a unique ID (like a roll number), a candidate key is any column that *could* be a unique ID, a foreign key connects one table to another, and a tuple is just a single row of data.

Exam Tip: When defining keys, always mention that a Primary Key can never have duplicate or NULL values, whereas a Foreign Key can accept duplicate or NULL values.

Question 5. Explain Different Data Type of My SQL?
Answer:
MySQL supports several standard data types categorized into three main families:
1. Numeric Types:
* INT / INTEGER: Used for whole numbers (integers) without decimal points.
* DECIMAL(M,D) / FLOAT: Used for precise fractional/decimal numbers, where M is total digits and D is decimal places.
2. String/Text Types:
* CHAR(size): Fixed-length character string (pads spaces for shorter inputs).
* VARCHAR(size): Variable-length character string (efficiently manages memory for variable-length text).
3. Date and Time Types:
* DATE: Stores date values in 'YYYY-MM-DD' format.
* TIME: Stores time values in 'HH:MM:SS' format.
In simple words: MySQL uses `INT` for whole numbers, `DECIMAL` for decimals, `CHAR` and `VARCHAR` for text, and `DATE` for calendar dates.

Exam Tip: Be prepared to differentiate between `CHAR` (fixed-length) and `VARCHAR` (variable-length) as it is a very common sub-question.

Question 6. Differentiate between Distinct and All clause?
Answer:
* DISTINCT Clause: It is used in a SELECT statement to remove duplicate rows from the query result, displaying only unique values.
* ALL Clause: It is the default option in a SELECT statement that retains and displays all retrieved rows, including duplicate values.
Example:
* SELECT DISTINCT Deptid FROM EMPLOYEE; (Returns only unique department IDs: 101, 102, 103).
* SELECT ALL Deptid FROM EMPLOYEE; (Returns all department IDs including duplicates: 101, 101, 102, 102, 103, 101).
In simple words: `DISTINCT` filters out duplicates so you only see unique values once, while `ALL` shows absolutely everything, including repeated values.

Exam Tip: Since `ALL` is the default behavior, writing `SELECT Name FROM Table` is exactly the same as writing `SELECT ALL Name FROM Table`.

Question 7. Differentiate between IN and BETWEEN Operator using an example?
Answer:
* IN Operator: It allows you to specify multiple discrete values in a WHERE clause. It matches any value that is present in the specified list.
* BETWEEN Operator: It matches values within a continuous range (inclusive of both boundary values).

Examples:
* IN Operator Example:
SELECT * FROM EMPLOYEE WHERE Deptid IN (101, 103);
(This selects employees whose department ID is exactly 101 or 103).
* BETWEEN Operator Example:
SELECT * FROM EMPLOYEE WHERE Basic BETWEEN 2000 AND 6000;
(This selects employees whose Basic salary is anywhere from 2000 to 6000, inclusive).
In simple words: `IN` checks if a value matches any item in a specific list, while `BETWEEN` checks if a value falls inside a continuous range of numbers or dates.

Exam Tip: Remember that `BETWEEN` is inclusive, meaning `BETWEEN 10 AND 20` includes both 10 and 20.

Question 8. Explain DCL Command With Example?
Answer:
DCL stands for Data Control Language. DCL commands are used to manage database access permissions, security, and authorization privileges for users. The two main DCL commands are:
1. GRANT: Gives specific access privileges to users.
* Example: GRANT SELECT, INSERT ON EMPLOYEE TO user1;
(Allows user1 to view and insert records in the EMPLOYEE table).
2. REVOKE: Removes previously granted privileges from users.
* Example: REVOKE INSERT ON EMPLOYEE FROM user1;
(Takes away the permission to insert records from user1).
In simple words: DCL commands are security tools used by database administrators to hand out permissions (`GRANT`) or take them back (`REVOKE`).

Exam Tip: Always categorize SQL commands clearly: DDL (Data Definition), DML (Data Manipulation), and DCL (Data Control) to demonstrate structured knowledge.

Question 9. Explain E-Learning, E-governance And E-learning.
Answer:
This describes the standard electronic services provided over the internet (assuming the third term is E-commerce/E-business as per standard curriculum):
1. E-Learning (Electronic Learning): The delivery of educational content, training, or instruction through digital platforms, websites, and online courses (e.g., online school portals, virtual classrooms).
2. E-governance (Electronic Governance): The application of information and communication technology (ICT) by the government to deliver public services, share information, and manage administrative transactions transparently and efficiently to citizens (e.g., online tax filing, digital passport applications).
3. E-commerce / E-business: The buying, selling, or trading of products and services over the internet using digital transaction portals (e.g., online shopping websites).
In simple words: E-learning lets you study online, E-governance lets you access government services on websites, and E-commerce lets you buy and sell things on the internet.

Exam Tip: Naming real-world portals (like online school sites for E-learning and passport portals for E-governance) helps secure top marks.

Question Q10) a. Consider the following tables EMPLOYEE and write the SQL query for the following requirements.
Eid Name Deptid Qualification Sex Points Basic DA HRA Bonus
1 Sunil 101 MCA F 20 6000 2000 2300 200
2 Virender 101 BCA M 15 2000 300 300 30
3 Mukesh 102 B.A M 15 1000 300 300 40
4 Rakesh 102 M.A M 40 1500 390 490 30
5 Sumit 103 B.Tech M 32 8000 900 900 80
6 Jyoti 101 M.Tech F 12 10000 300 490 89

1. Create the structure of the above table
2. Insert any dummy record
3. Display the Names begin with alphabet “V”
4. Display the records who are females.
5. Display the name and qualification who are “B. Tech.”
6. Add one more field for the employee table -DOJ (date of joining)
7. Do not display the records of “Jyoti”, “Mukesh”.
8. To insert a new record in the EMPLOYEE table with following data:
7,’Vikas’, 103,’MCA’,’M’, 36,12000,1000,500,90.
9. Display the Name, deptid, Basic whose date of joining is after 23/09/2000.
10. Display the records according to their Names.
Answer:
Here are the SQL queries for the given requirements:

1. Create the structure of the above table:

CREATE TABLE EMPLOYEE (
    Eid INT PRIMARY KEY,
    Name VARCHAR(30),
    Deptid INT,
    Qualification VARCHAR(20),
    Sex CHAR(1),
    Points INT,
    Basic INT,
    DA INT,
    HRA INT,
    Bonus INT
);

2. Insert any dummy record:

INSERT INTO EMPLOYEE VALUES (7, 'Vikas', 103, 'MCA', 'M', 36, 12000, 1000, 500, 90);

3. Display the Names begin with alphabet “V”:

SELECT Name FROM EMPLOYEE WHERE Name LIKE 'V%';

4. Display the records who are females:

SELECT * FROM EMPLOYEE WHERE Sex = 'F';

5. Display the name and qualification who are “B. Tech.”:

SELECT Name, Qualification FROM EMPLOYEE WHERE Qualification = 'B.Tech';

6. Add one more field for the employee table -DOJ (date of joining):

ALTER TABLE EMPLOYEE ADD DOJ DATE;

7. Do not display the records of “Jyoti”, “Mukesh”:

SELECT * FROM EMPLOYEE WHERE Name NOT IN ('Jyoti', 'Mukesh');

8. To insert a new record in the EMPLOYEE table with following data:

INSERT INTO EMPLOYEE (Eid, Name, Deptid, Qualification, Sex, Points, Basic, DA, HRA, Bonus) 
VALUES (7, 'Vikas', 103, 'MCA', 'M', 36, 12000, 1000, 500, 90);

9. Display the Name, deptid, Basic whose date of joining is after 23/09/2000:

SELECT Name, Deptid, Basic FROM EMPLOYEE WHERE DOJ > '2000-09-23';

10. Display the records according to their Names:

SELECT * FROM EMPLOYEE ORDER BY Name;

In simple words: These are standard SQL queries used to create tables, insert data, filter records, modify table structure, and sort data in a relational database.

Exam Tip: For query 9, always write dates in 'YYYY-MM-DD' format (i.e., '2000-09-23') as this is the standard default format accepted by MySQL.

Question 10. b) Write down the output produced by following queries after execution.
a) SELECT CONCAT (‘VISHAL’,’KUMAR’) FROM DUAL;
b) SELECT SUBSTR (‘INFORMATICS PRACTICES’,3,4) FROM DUAL;
c) SELECT INSTR (‘INDOOR OUTDOOR’,’OR’) FROM DUAL;
d) SELECT LENGTH (‘INFORMATICS PRACTICES’) FROM DUAL;
e) SELECT LOWER (RIGHT (‘JAVA’, 2)) FROM DUAL;
f) SELECT MOD (11,3) FROM DUAL;
g) SELECT SIGN (-67) FROM DUAL;
h) SELECT SQRT (49) FROM DUAL;
i) SELECT DATEOFMONTH (‘2010-02-16’) FROM DUAL;
j) SELECT ROUND (15.193,1) FROM DUAL;
Answer:
The output produced by each query is given below:

In simple words: These single-row functions perform various mathematical, string, and date operations directly on values and output single-line results.

Exam Tip: DUAL is a dummy table automatically created by Oracle/MySQL database systems used to test equations and functions without querying a real data table.