Computer Science 4 Marks Each

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;

Table: Bank

Write SQL commands for the statements (a) to (h) on the table Bank

(a) Display the details of all FD whose rate of interest is in the range 6% to 7%.

(b) Display the Customer Name and FD Amount for all the loans for which the number of Months is 24, 36, or 48(using IN operator).

(c) Display the Account Number, Customer Name and FD Amount for all the FD for which the Customer Name ends with “Sharma”.

(d) Delete the records of “Rupinder Kaur”.

(e) Add another column Maturity_Amt of type Integer in the Bank table.

(f) To find the average FD amount. Label the column as “Average FD Amount”.

(g) To find the total FD amount which started in the year 2018?

(h) Update Maturity Amount of all bank customers.

a. Maturity Amount = (FD_Amount*Months* Int_rate)/(12*100)

Table: Bank

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.

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.

(a) To insert a new row in the HOSPITAL table with the following data: 11,’ Kasif’, 37,’ENT’,’2018-02-25’, 300, ’M’.

(b) To set charges to NULL for all the patients in the Surgery department.

(c) To display patient details who are giving charges in the range 300 and 400 (both inclusive).

(d) To display the details of that patient whose name second character contains ‘a’.

(e) To display total charges of ENT Department.

(f) To display details of the patients who admitted in the year 2019.

(g) To display the structure of the table hospital.

(h) Write the command to create the above table.

Table: HOSPITAL

Note: PNo is the primary key in the above table.

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

(a) To display the details of all the patients whose name starts with the alphabet ‘Z’.

(b) To change the age of the patient Kush to 20.

(c) To increase the charges of all the patients by 5%.

(d) To remove the record of the patient whose Name is Tarun.

(e) To add another column DocName(Doctor Name) of the type varchar in the above table.

(f) To display patient detail whose age is missing(null).

(g) To decrease the charges by 5% of all the patients admitted to the ENT department.

Table: HOSPITAL

Note: PNo is the primary key in the above table.

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

(a) To show all the information about the patients of the cardiology department.

(b) To list the names of female patients who are either in the orthopaedic or surgery department.

(c) To list the name of all the patients with their date of admission in ascending order.

(d) To display the patient’s name, charges, the age for female patients only.

(e) To count the number of patients with age > 30.

(f) To display various departments.

(g) To display the number of patients in each department.

Table: TEACHER

(i) To show information about the teachers of the history department.

(ii) To list the names of teachers earning a salary between 20000 and 30000.

(iii) To count the number of male teachers.

(iv) Display gender wise total number of teachers.

(v) To list the name and age of teachers of female teachers in descending order of date of join.

(vi) Increase the salary by 10% for Maths departments.

( vii) To delete the record of teacher Neeraj.