Societal Impacts — Computer Science STD 12 Science — Question

Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables: [Delhi 2018(C)]

TABLE: SALESPERSON

CODE	NAME	SALARY	ITCODE
1001	TANDEEP JHA	60000	I2
1002	YOGRAJ SINHA	70000	I5
1003	TENZIN JACK	45000	I2
1005	ANOKHI RAJ	50000	I7
1004	TARANA SEN	55000	I7

TABLE: ITEM

ITCODE	ITEMTYPE	TURNOVER
I5	STATIONARY	3400000
I7	HOISTERY	6500000
I2	BAKERY	10090000

(a) To display the CODE and NAME of all salesperson having “I7” item Type Code from the table SALESPERSON.

(b) To display all details from table SALESPERSON in descending order of SALARY.

(c) To display the number of SALESPERSON dealing in each TYPE of ITEM.(Use ITCODE for the same)

(d) To display NAME of all the salesperson table along with their corresponding ITEMTYPE from the ITEM table.

(e) Select max(salary) from salesperson;

(f) Select distinct itcode from salesperson;

(g) select code,name,i.itcode from sales person s, item i where s.itcode=i.itcode and turnover>=7000000;

(h) Select sum (salary)from salesperson where itcode=”i2”;

Consider the following tables CARHUB and CUSTOMER and answer (a) and (b) parts of this question: [Delhi 2012]

Table: CARHUB

Vcode	Vehicle Name	Make	Color	Capacity	Charges
100	Innova	Toyota	WHITE	7	15
102	SX4	Suzuki	BLUE	4	14
104	C Class	Mercedes	RED	4	35
105	A-Star	Suzuki	WHITE	3	14
108	Indigo	Tata	SILVER	3	12

Table: CUSTOMER

CCode	CName	VCode
1	Hemant Sahu	101
2	Raj Lal	108
3	Feroza Shah	105
4	Ketan Dhal	104

(a) Write SQL commands for the following statements:

(i) To display the names of all white colored vehicles

(ii) To display name of vehicle, make and capacity of vehicles in ascending order of their sitting capacity

(iii) To display the highest charges at which a vehicle can be hired from CARHUB.

(iv) To display the customer name and the corresponding name of the vehicle hired by them.

(b) Give the output of the following SQL queries:

(i) Select count(distinct make) from cabhub;

(ii) Select max(charges), min(charges) from carhub;

(iii) Select count(*), make from carhub;

(iv) Select vehiclename from carhub where capacity = 4;

Consider the following table Student & Stream.

Table: Student

Admno	Sname	Class	Sec	Fee	Mobile	Area	S_ID
1001	RAMESH	XII	A	2500	987654321	Madipur	10
1078	KRISHNA	XII	B	2400	999911111	Jawala Heri	30
1006	FARDEEN	XII	C	2600	987654321	Paschim Puri	40
1004	SUBHAM	XII	A	2500	963025874	Madipur	20
1029	KRITIKA	XI	C	2700	987456210	Madipur	30
1008	SAMEEKSHA	XII	A	2450	987123456	Mangol Puri	20
1025	SALMA	XII	B	2580	998877445	Madipur	30
1036	AMANDEEP	XII	B	2600	999333555	Khyala	40
1037	TEJAS	XI	C	2650	987951357	Paschim Puri	40
1029	HIMANSHU	XII	A	2750	951369874	Jawala Heri	10

Table : Stream

S_ID	Stream_name
10	MEDILCAL
20	NON MEDICAL
30	COMMERCE WITH MATH
40	COMMERCE WITH IP
50	HUMANITIES

Write SQL commands for the statements (a) to (h) on the above table: Student and Stream

(a) Identify Primary Keys and Foreign Key in the table student and primary key in Stream table given above.

(b) Display stream id and stream-wise total fee collected.

(c) Count no of students from each area.

(d) Display all the student details those who belongs to Madipur Area.

(e) Increase the fees of all students by10%.

(f) Display unique area from the student table.

(g) Display details of those students whose area contains ‘Puri’.

(h) Display the information of those students who are in class XII and section is either B or C.

Consider the table LOANS given below:

Table: LOANS

AccNo	Name	Loan Amt	EMI	IntRate	Start Date	Interest
1001	R.K. Gupta	300000	36	12.00	19-07-2009	1200
1002	S. P. Shanna	500000	48	10.00	22-03-2008	1800
1003	K.P. Jain	300000	36	NULL	08-03-2007	1600
1004	M.P. Yadav	800000	60	10.00	06-12-2008	2250
1005	S.P. Sinlia	200000	36	12.50	03-01-2010	4500
1006	P. Shanna	700000	60	12.50	05-06-2008	3500
1007	K.S. Dhall	500000	48	NULL	05-03-2008	3800

(a) State the command that will give the output as:

Name

S. P. Shanna

K.S. Dhall

M.P. Yadav

P. Shanna

K.S. Dhall

(i) Select name from LOANS where Name >‘D ’;

(ii) Select name from LOANS where AccNo >1001;

(iii) Select name from LOANS where Loan_Amt >= 500000;

(iv) Select name from LOANS where Loan_Amt >500000 order Loan_AmtDESC;

(b) What will be the output of the following command:

Select name, EMI from LOANS where Loan_Amt >500000 and Int_Rate is NULL;

(c) John has given the following command to display the count of all loan holders whose name ends with “Sharma ”:

Select count() from LOANS where Name like ‘Sharma%’;

But he is not getting the desired result. Help him by finding out the correct command fromthe followings:

(i) Select count(*) from LOANS where name Like ‘Sharma%’;

(ii) Select count(*) from LOANS where name Like ‘%Sharma ’;

(iii) Select count( ) from LOANS where name like ‘%sharma ’;

(iv) Select count(Name) from LOANS where name ‘Sharma%’;

(d) State the command to display the maximum Loan_Amt and Cust_Name :-

(i) Select Max(Cust_Name, Loan_Amt) from LOANS;

(ii) Select Cust_Name, max(LoanAmt) from LOANS;

(iii) Select Cust_Name, max(Loan_Amount) from LOANS;

(iv) Select Cust_name, max(Loan_Amt) from LOANS;

(e) Help Ramesh to display the count of all loan holders whose interest is NULL.

(i) Select Count(*) from LOANS where Int_Rate is NULL.

(ii) Select Count() from LOANS where Interest is NULL.

(iii) Select Count(*) from LOANS where Int_rate = NULL;

(iv) Select Count( ) from LOANS where Interest = NULL;

Krishna is confused in Python code given below. Please help him to answer the following questions.

(a) Which line in the above code check for capital letter?

(b) Which line in the above code read the file “story. txt”?

(c) Which line in the above code does not affect the execution of program?

(d) Which line is the above code coverts capital letter to small letter?

(e) Which line is the above code opens the file in write mode?

(f) Which line is the above code saves the data?

(g) Which line(s) is/are the part of selection statement.

(h) Which line(s) is/are used to close story1.txt?

Explain how technology is making our society simpler and better?

Consider the following Data Frame ‘‘emp’’ and answer any four questions from (i) – (v).

Ecode	Name	Age	Fav_Color	Salary
101	Rohit	20	Blue	45000
102	Mohanti	24	Red	36000
103	Tushar Koul	23	Green	42000
104	Rupali	22	Yellow	38000
105	Gurpreet	21	Pink	40000

(a) Select the command from the given options that will give the following output:-

(i) print(emp.max)

(ii) print(emp.max(axis=1))

(iii) print(emp.max,axis=1)

(iv) print(emp.max())

(b) A manager wants to know the Favourite colour of the employee with the employee code 103. Help him to identify the correct set of statements from the given options:

(i) df1=emp[emp[‘Ecode’]==103]

print(df1)

(ii) df1=emp['Ecode'==103]

print(df1)

(iii) df1=emp[emp.Ecode=103]

print(df1)

(iv) df1=emp[emp.Ecode==103]

print(df1)

(c) Which of the following statement will give the names of the employees whose salary is more than 40000.

(i) print(emp.max())

(ii) print(emp[emp["Salary"]>40000])

(iii) print(emp["Salary"]>40000)

(iv) print(emp.max()>40000)

(d) Which of the following command will list only the columns Ename and Salary using loc:

(i) print(emp.loc[:,[0,2]]

(ii) print(emp.loc[:,["Ename","Salary"]])

(iii) print(emp.loc(:["Ename","Salary"]))

(iv) print(emp.loc[["Ename","Salary"]])

(e) Mr. Singh, the manager wants to add a new column, the Rank with the values ‘IV’, ‘II’, ‘III’, ‘IV’, ‘I’, to the data frame Help him to identify the right command from the followings to do so :

(i) emp.column=[‘IV’, ‘II’, ‘III’, ‘IV’, ‘I’ ]

(ii) emp.iloc["Rank"] =[‘IV’, ‘II’, ‘III’, ‘IV’, ‘I’]

(iii) emp["Rank"] =[‘IV’, ‘II’, ‘III’, ‘IV’, ‘I’ ]

(iv) None of the above

(a) Create the Dictionary Student with the following key and add the 5 values. Dictionary will be created from 4 different list:

Column Name	Key
Student Number	sno
Student Name	sname
E-mail	mail
Marks	mark

(b) Create the data frame for the above specification containing index parameter along with column.

(c) Add a new column grade into above Data Frame with at least 3 methods.

(d) Insert the 2 rows.

(e) Create another Data Frame with two rows named DF2.

(f) Add this data frame to your existing data frame.

(g) Display the alternate rows.

(h) Display the name and marks of the students whose marks is greater than equal to 90.

(i) Fill all NaN in the data.

(j) Increase the marks by 10.

An email is a service of sending or receiving emails or messages in the form of text, audio, video, etc over the internet. Various service providers are providing email services to users. The most popular service providers in India are Gmail, Yahoo, Hotmail, Rediff, etc.

(a) The first email service was:

(i)Gmail(ii)Rediff

(iii)Hotmail(iv)Yahoo

(b) Unsolicited e-mail advertising is known as _______

(i)newsgroup

(ii)junk ads

(iii)spam

(iv)none of the above

(c) Which of the following is the correct format of email address?

(i)name@website@info

(ii)name@websiteinfo

(iii)www.nameofwebsitecom

(iv)namewebsitecom

(d) Mail access starts with client when user needs to download e-mail from the______

(i)mail box(ii)mail server

(iii)IP server(iv)internet

(e) When sender and receiver of an email are on same system, we need only two_____

(i)IP (ii)domain

(iii)servers(iv)user agents

The ABC School Management has given a task to Mr. Amit Anand to write the code for the promotion rule/salary increment. He wrote the following code using function. On the basis of code, answer the following question.


(a) Which statement will be executed if Post = “Pgt” and experience = 15?
(i) Statement 1 (ii) Statement 2
(iii) Statement 3 (iv) Statement 4
(b) Which statement will be executed if Post = “Vice Principal” and experience = 15?
(i) Statement 2 (ii) Statement 4
(iii) Statement 6 (iv) Statement 8
(c) Which statement will be executed if Post = “tgt” and experience = 5?
(i) Statement 5 (ii) Statement 6
(iii) Statement 7 (iv) Statement 8
(d) Which statement will be executed if Post = “tgt” and experience = 22?
(i) Statement 5 (ii) Statement 6
(iii) Statement 7 (iv) Statement 8
(e) Which statement will be executed if Post = “Pgt” and experience = 15?
(i) Statement 1 (ii) Statement 2
(iii) Statement 3 (iv) Statement 4