Functions — Computer Science STD 12 Science — Question

Table: Bank

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

(a) To create the table Bank (Primary Key: AccNo)

(b) Display the structure of the table Bank.

(c) Display the details of all the bank.

(d) Display the AccNo, Cust_Name, and FD_Amount.

(e) Display the details of all the FD’s having maturity time is less than 40 months.

(f) Display the AccNo and FD amount which started before 01-04-2018.

(g) Display details of all FD whose rate of interest are NULL.

Write SQL commands for the following queries based on the relation Teacher given below:

Table: Teacher

(a) To show all information about the teacher of Computer department.

(b) To list the names of female teachers who are in Maths department.

(c) To list the names of all teachers with their date of joining in ascending order.

(d) To display teacher ’s name, salary, age for male teachers only.

(e) To count the number of teachers with Age >23.

Write a program to sort the (name, age, height) tuples by ascending order where name is string, age and height are numbers. The tuples are input by the user. The sort criteria is:
1: Sort based on name;
2: Then sort based on age;
3: Then sort by score.
The priority is that name >age >score.
If the following tuples are given as input to the
program:
ram,19,20
Shyam,15,67
Raju,13,90
Rakesh,17,78
Then, the output of the program should be:
[('Raju', '13', '90'), ('Rakesh', '17', '78'), ('Shyam', '15', '67'), ('ram', '19', '20')]

Answer the (a) and (b) on the basis of the following tables STORE and ITEM:

Table: STORE

Table: ITEM

(a) Write the SQL queries (1 to 4):

(i) To display IName and Price of all the items in the ascending order of their Price.

(ii) To display the SNo and SName or all stores located in CP.

(iii) To display the minimum and maximum price of each IName from the table Item.

(iv) To display the IName, price of all items and their respective SName where they are available.

(b) Write the output of the following SQL commands (i) to (iv):

(i) Select distinct iname from item where price >= 5000;

(ii) Select area, count(*) from store group by area;

(iii) Select count(distinct area) from store;

(iv) Select iname, price*0.05 discount from item where sno in (‘s02 ’, ‘s03 ’);

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: EMPSalary

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;