Science (English Medium) Class 12 - CBSE Important Questions

Write the output of the queries (i) to (iv) based on the table, WORKER given below:

TABLE: WORKER

1. SELECT F_NAME, CITY FROM WORKER ORDER BY STATE DESC;
2. SELECT DISTINCT (CITY) FROM WORKER;
3. SELECT F_NAME, STATE FROM WORKER WHERE L_NAME LIKE '_HA%';
4. SELECT CITY, COUNT(*) FROM WORKER GROUP BY CITY;

Write the command to view all databases.

Write the outputs of the SQL queries (i) to (iv) based on the relations COMPUTER and SALES given below:

1. SELECT MIN(PRICE), MAX(PRICE) FROM COMPUTER;
2. SELECT COMPANY, COUNT(*) FROM COMPUTER GROUP BY COMPANY HAVING COUNT(COMPANY) > 1;
3. SELECT PROD_NAME, QTY_SOLD FROM COMPUTER C, SALES S WHERE C.PROD_ID=S.PROD_ID AND TYPE = 'INPUT';
4. SELECT PROD_NAME, COMPANY, QUARTER FROM COMPUTER C, SALES S WHERE C.PROD_ID=S.PROD_ID;

Name any two DDL commands.

The code given below deletes the record from the table employee, which contains the following record structure:

E_code - String
E_name - String
Sal - Integer
City - String

Note the following to establish connectivity between Python and MySQL:

• Username is root
• Password is root
• The table exists in a MySQL database named emp.
• The details (E_code, E_name, Sal, City) are the attributes of the table.

Write the following statements to complete the code:

Statement 1 – to import the desired library.

Statement 2 – to execute the command that deletes the record with E_code as 'E101'.

Statement 3 – to delete the record permanently from the database. ____________

import ____________ as mysql #Statement1
def delete():
      mydb=mysql.connect(host="localhost",use r="root", 
      passwd="root",database="emp")

mycursor=mydb.cursor()
____________ #Statement 2
____________ #Statement 3
print ("Record deleted") 

The code given below reads the following records from the table employee and displays only those records that have employees coming from the city 'Delhi':

E_code - String
E_name - String
Sal - Integer
City - String

Note the following to establish connectivity between Python and MySQL:

• Username is root
• Password is root
• The table exists in a MySQL database named emp.
• The details (E_code,E_name,Sal,City) are the attributes of the table.

Write the following statements to complete the code:

Statement 1 - to import the desired library.

Statement 2 - to execute the query that fetches records of the employees/coming from the city ‘Delhi’.

Statement 3 - to read the complete data of the query (rows whose city is Delhi) into the object named details, from the table employee in the database.

import ____________ as mysql  #Statement 1
def display():
   mydb=mysql.connect(host="localhost",user="root"
   passwd="root", database=" emp")
   mycursor=mydb.cursor()
   ____________ #Statement 2
   details = ____________ # Statement 3
   for i in details:
       print (i)

In a table in the MYSQL database, an attribute A of datatype varchar(20) has the value “Keshav”. The attribute B of datatype char(20) has the value “Meenakshi”. How many characters are occupied by attribute A and attribute B?

Ms Shalini has just created a table named “Employee” containing columns Ename, Department and Salary. After creating the table, she realized that she has forgotten to add a primary key column in the table. Help her in writing an SQL command to add a primary key column EmpId of integer type to the table Employee.

Thereafter, write the command to insert the following record in the table:

EmpId- 999
Ename- Shweta
Department: Production
Salary: 26900

Consider the table CLUB given below and write the output of the SQL queries that follow.

1. SELECT COUNT(DISTINCT SPORTS) FROM CLUB;
2. SELECT CNAME, SPORTS FROM CLUB
   WHERE DOAPP<"2006-04-30" AND CNAME LIKE "%NA";
3. SELECT CNAME, AGE, PAY FROM CLUB WHERE GENDER = "MALE" AND PAY BETWEEN 1000 AND 1200;

Consider the table Personal given below:

Based on the given table, write SQL queries for the following:

1. Increase the salary by 5% of personals whose allowance is known.
2. Display Name and Total Salary (sum of Salary and Allowance) of all personals. The column heading ‘Total Salary’ should also be displayed.
3. Delete the record of Supervisors who have salary greater than 25000.

Kabir wants to write a program in Python to insert the following record in the table named Student in MYSQL database,

SCHOOL:

• rno(Roll number) - integer
• name(Name) - string
• DOB (Date of birth) - Date
• Fee - float

Note the following to establish connectivity between Python and MySQL:

• Username - root
• Password - tiger
• Host - localhost

The values of fields rno, name, DOB and fee has to be accepted from the user. Help Kabir to write the program in Python.

Consider the tables PRODUCT and BRAND given below:

Write SQL queries for the following:

1. Display product name and brand name from the tables PRODUCT and BRAND.
2. Display the structure of the table PRODUCT.
3. Display the average rating of Medimix and Dove brands.
4. Display the name, price, and rating of products in descending order of rating.

Zack is working in a database named SPORT, in which he has created a table named “Sports” containing columns SportId, SportName, no_of_players, and category.

After creating the table, he realized that the attribute, category has to be deleted from the table and a new attribute TypeSport of data type string has to be added. This attribute TypeSport cannot be left blank. Help Zack write the commands to complete both the tasks.

Sartaj has created a table named Student in MYSQL database, SCHOOL:

• rno(Roll number )- integer
• name(Name) - string
• DOB (Date of birth) – Date
• Fee – float

Note the following to establish connectivity between Python and MySQL:

• Username - root
• Password - tiger
• Host - localhost

Sartaj, now wants to display the records of students whose fee is more than 5000. Help Sartaj to write the program in Python.

Hi-Standard Tech Traj.ning Ltd. is a Mumbai based organization which is expanding its office set-up to Chennai. At Chennai office compound, they are planning to have 3 different blocks for Admin, Training and Accounts related activities. Each block has a number of computers, which are required to be connected in a network for communication, data and resource sharing.

As a network consultant, you have to suggest the best network related solutions for them for issues/problems raised by them in (i) to (iv), as per the distances between various blocks/locations and other given parameters.

Shortest differences between various blocks/locations:

The number of computers installed at various blocks is as follows:

1) Suggest the most appropriate block/location to house the SERVER in the CHENNAI office (out of the 3 blocks) to get the best and effective connectivity. Justify your answer.

2) Suggest the best-wired medium and draw the cable layout (Block to Block) to efficiently connect various blocks within the CHENNAI office compound.

3) Suggest a device/software and its placement that would provide data security for the entire network of the CHENNAI office.

4) Suggest a device and the protocol that shall be needed to provide wireless Internet access to all smartphone/laptop users in the CHENNAI office

Differentiate between packet switching over message switching

Expand the following:

ARPANET

Explain the following device:

Repeater

Explain the following device:

Router

Quickdev, an IT based firm, located in Delhi is planning to set up network for its four branches within a city with its Marketing department in Kanpur. As a network professional, give solution to the questions, after going through the branches locations and other details which are given below: 

Distance between various branches is a follows:

Number of computers in each of the branches:

1. Suggest the most suitable place to install the server for the Delhi branch with a suitable reason.
2. Suggest an ideal layout for connecting all these branches within Delhi.
3. Which device will you suggest, that should be placed in each of these branches to efficiently connect all the computers within these branches?
4. Delhi firm is planning to connect to its Marketing department in Kanpur which is approximately 300 km away. Which type of network out of LAN, WAN, or MAN will be formed? Justify your answer.
5. Suggest a protocol that shall be needed to provide help for transferring of files between Delhi and Kanpur branch.