Data Structure — Computer Science STD 12 Science — Question

Write SQL commands for the following queries on the basis of Club relation given below:

Relation: Club

Coach-ID	CoachName	Age	Sports	date_of_app	Pay	Sex
1	Kukreja	35	Karate	27/03/1996	1000	M
2	Ravina	34	Karate	20/01/1998	1200	F
3	Karan	34	Squash	19/02/1998	2000	M
4	Tarun	33	Basketball	01/01/1998	1500	M
5	Zubin	36	Swimming	12/01/1998	750	M
6	Ketaki	36	Swimming	24/02/1998	800	F
7	Ankita	39	Squash	20/02/1998	2200	F
8	Zareen	37	Karate	22/02/1998	1100	F
9	Kush	41	Swimming	13/01/1998	900	M
10	Shailya	37	Basketball	19/02/1998	1700	M

(a) To show all information about the swimming coaches in the club.

(b) To list the names of all coaches with their date of appointment (date_of_app) in descending order.

(c) To display a report showing coach name, pay, age, and bonus (15% of pay) for all coaches.

(d) To insert a new row in the Club table with ANY relevant data:

(e) Give the output of the following SQL statements:

(i) Select COUNT(Distinct Sports) from Club;

(ii) Select Min(Age) from Club where SEX = “F ”;

Write a program that accepts a comma separated sequence of words as input and prints the words in a comma-separated sequence after sorting them alphabetically.
Suppose the following input is supplied to the program:
hi,we,are,learning
Then, the output should be:
are,hi,learning,we

Quick Learn University is setting up its academic blocks at Prayag nagar and is planning to set up a network. The University has 3 academic blocks and one Human Resource Center as shown in the diagram below:

Center to Center distances between various blocks/center is as follows:

Law Block to business Block	40m
Law block to Technology Block	80m
Law Block to HR center	105m
Business Block to technology Block	30m
Business Block to HR Center	35m
Technology block to HR center	15m

Number of computers in each of the blocks/Center is as follows:

Law Block	15
Technology Block	40
HR center	115
Business Block	25

(a) Suggest the most suitable place (i.e., Block/Center) to install the server of this University with a suitable reason.

(b) Suggest an ideal layout for connecting these blocks/centers for a wired connectivity.

(c) Which device will you suggest to be placed/installed in each of these blocks/centers to efficiently connect all the computers within these blocks/centers.

(d) The university is planning to connect its admission office in the closest big city, which is more than 250km from university. Which type of network out of LAN, MAN, or WAN will be formed? Justify your answer.

Consider the following table Bank.

Table: Bank

AccNo	Cust_name	FD_Amount	Months	Int_Rate	FD_Date
1001	Arti Gupta	30000	36	6.00	2018-07-01
1002	Dilip Lal	50000	48	6.75	2018-03-22
1003	Navin Gupta	30000	36	NULL	2018-03-01
1004	D.P. Yadav	80000	60	8.25	2017-06-12
1005	Jyoti Sharma	20000	36	6.50	2017-01-31
1006	Rakesh Kumar	70000	60	8.25	2018-06-15
1007	K.D. Singh	50000	48	NULL	2018-07-05
1008	Anjali Sharma	60000	48	6.75	2017-04-02
1009	Swati Garg	40000	42	6.50	2018-06-15
1010	Rupinder Kaur	25000	36	6.50	2018-09-27

Write SQL commands for the statements (a) to (g) on the table BANK

(a) Display details of all the FD whose rate of interest is NOT NULL.

(b) Display amounts of various FD from the table Bank. An FD Amount should appear only once.

(c) Display the number of months of various loans from the table Bank. A month should appear only once.

(d) Display the Customer Name and FD Amount for all the Bank which do not have a number of months is 36.

(e) Display the Customer Name and FD Amount for which the FD amount is less than 500000 or int_rate is more than 7.

(f) Display the details of all FD which started in the year 2018.

(g) Display the details of all FD whose FD_Amount is in the range 40000 to 50000.

Suppose we want to sort the following list of 7 elements in ascending order using bubble sort:

Write the complete steps to sort the above along with each pass explanation.

Write algorithm for POP(Remove) operation in stack.

Write the output for the following commands:

Table- Loan_accounts

account	cust_name	loan_amount	installment	int_rate	start_rate	interest
1	R.K. Gupta	300000	36	12.00	2009-07-19	Null
2	S.P. Sharma	500000	48	10.00	2008-03-22	Null
3	K.P. Jain	300000	36	Null	2007-08-03	Null
4	M.P. Yadav	800000	60	10.00	2008-12-06	Null
5	S.P. Sinha	200000	36	12.50	2010-01-03	Null
6	P. Sharma	700000	60	12.50	2008-06-05	Null
7	K.S. Dhall	500000	48	Null	2008-03-05	Null

(a)

(b)

(c)

(d)

(e)

Write the SQL query questions from (i) to (iv) and write the output of SQL command for questions from (v) to (vii) given below:

Table: EMPLOYEES

EMPID	FIRST NAME	LAST NAME	ADDRESS	CITY
010	George	Smith	83 First Street	Howard
105	Mary	Jones	842 Vine Ave.	Losantiville
152	Sam	Tones	33 Elm St.	Paris
215	Sarah	Ackerman	440 U.S. 110	Upton
244	Manila	Sengupta	24 Friends Street	New Delhi
300	Robert	Samuel	9 Fifth Cross	Washington
335	Henry	Williams	12 Moore Street	Boston
400	Rachel	Lee	121 Harrison St.	New York
441	Peter	Thompson	11 Red Road	Paris

Table: EMPSALARY

EMPID	SALARY	BENEFITS	DESIGNATION
010	75000	15000	Manager
105	65000	15000	Manager
152	80000	25000	Director
215	75000	12500	Manager
244	50000	12000	Clerk
300	45000	10000	Clerk
355	40000	10000	Clerk
4000	32000	7500	Salesman
441	28000	7500	Salesman

Write the SQL commands for the following :

(i) To show firstname, lastname, address and city of all employees living in paris.

(ii) To display the content of Employees table in ascending order of Firstname.

(iii) To display the firstname,lastname and total salary of all managers from the tables employee and empsalary, where total salary is calculated as salary+benefits.

(iv) To display the maximum salary among managers and clerks from the table Empsalary.

Give the Output of following SQL commands:

(v) Select firstname, salary from employees, empsalary where designation = ‘Salesman ’and Employees.

empid=Empsalary.empid;

(vi) Select count(distinct designation) from empsalary;

(vii) Select designation, sum(salary) from empsalary group by designation having count(*) >2;

Write a program to enter a string and remove all punctuation characters from the following string.

Write AddClient(Client) and DeleteClient(Client) methods in python to add a new Client and delete a Client from a List of Client Names, considering them to act as insert and delete operations of the queue data structure.