Create the following table named "Charity "and write SQL queries … - Vidyadip

Question

Create the following table named "Charity "and write SQL queries for the tasks that follow:

Table: Charity

P_Id	LastName	FirstName	Address	City	Contribution
1	Bindra	Jaspreet	5B, Gomti Nagar	Lucknow	3500.50
2	Rana	Monica	21 A, Bandra	Mumbai	2768.00
3	Singh	Jatinder	8, Punjabi Bagh	Delhi	2000.50
4	Arora	Satinder	K/1, Shere Punjab Colony	Mumbai	1900.00
5	Krishnan	Vineeta	A-75, Adarsh Nagar

(i) Display all first names in lowercase.

(ii) Display all last names of people of Mumbai city in uppercase.

(iii) Display Person Id along with First 3 characters of his/her name.

(iv) Display first name concatenated with last name for all the employees.

(v) Display length of address along with Person Id.

(vi) Display last 2 characters of City and Person ID.

(vii) Display Last Names and First names of people who have "at "in the second or third position in their first names.

(viii) Display the position of 'a' in Last name in every row.

(ix) Display Last Name and First name of people who have "a "as the last character in their First names.

(x) Display the first name and last name concatenated after removing the leading and trailing blanks.

(xi) Display Person Id, last names and contribution rounded to the nearest rupee of all the persons.

(xii) Display Person Id, last name and contribution with decimal digits truncated of all the persons.

(xiii) Display Last name, contribution and a third column which has contribution divided by 10. Round it to two decimal points.

✓

Answer

Get the step-by-step solution for this question inside the Vidyadip app.

Get the answer in the app

Need a full question paper?

Generate a complete, print-ready paper with questions like this in minutes — across 16+ boards, with answer keys.

Start Generating Free

Explore more

More "4 Marks Each" from Structured Query Language (SQL) – II→Structured Query Language (SQL) – II — all question types→Computer Science STD 12 Science question papers→CBSE Board STD 12 Science question papers→CBSE Board question paper generator→

Similar questions

Write SQL commands for the following:

Table: TEACHER

TID	NAME	AGE	DEPT	DATEOFJOIN	SAL	SEX
T118	Navin	40	Computer	2010-01-10	12000	M
T107	Chetna	37	History	2008-03-24	20000	F
T105	Sandeep	46	Maths	2006-12-12	30000	M
T110	Sangeeta	35	History	2010-07-01	25000	F
T101	Rudransh	42	Maths	2004-09-05	40000	M
T121	Neeraj	38	Physics	2011-04-01	28000	M

(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.

Write a program to count no. of records in csv file and display the records in tabular form. Admno, Name, Eng, Hindi, Maths, Sci, Ssc 1101 1102 1103 1104 1105 Tejas Swati Sam Imran Heena 80 90 90 75 85 85 85 80 76 90 95 94 64 80 90 94 96 85 80 75 86 80 70

Write the function Bubble() to sort the list using Bubble sort technique. The function will take the list as parameter.

Write a menu driven python program using function Push (), Pop () and Display() to implement the stack. The program will store the Employee details i.e. Employee number, Employee name and Salary.

Write a function readall() the record from the binary file “student” and display all the records

Answer the following questions based on dataset given below:

	TNO	Tname	Tadd	Salary
0	T01	Amit	123 Paschim Vihar	23000
1	T02	Rajesh	6/11 Ramesh Nagar	34000
2	T03	Binny	5 West Punjabhi Bagh	12000
3	T04	Charu	23Malviya Nagar	45000
4	T05	Meenakshi	19 Meera Bagh	34000

(i) To write the statement to import the required library.

(ii) To create the data frame from the above dictionary.

(iii) To print the data frame.

(iv) To print the name columns.

(v) To print the complete information of the data frame.

(vi) To print the index of data frame.

(vii) To print the various attributes of the data frame i.e Shape, Dimensions, Columns and number of records.

(viii) To print the records whose salary is greater than 5000.

(ix) To create another data frame which will store all the records of the teacher whose salary is greater than 5000.

(x) To display the Salary.

(xi) To set the index to the column 'Tname'.

(xii) To display the record of 'Amit'.

(xiii) To display the record fo 'Amit' and 'Binny'

(xiv) To display the Salary and Address of 'Amit' and 'Binny'.

(xv) To display the records of the teachers whos are earning salary more than 10000.

(xvi) To display the records of the teachers whos are earning salary less than 40000.

(xvii) To add new column Dearness Allowance with any values.

(xviii) To add new column "HRA" using .loc().

(xix) To add new column tax using assign().

(xx) To display the HRA column.

(xxi) To display the records of the Teachers who have HRA of more than 500.

(xxii) To add a new column Total Salary which is to be calculated as Salary + HRA - Tax.

(xxiii) To fill all NaN values with 5000.

(xxiv) To replace all the NaN with space.

(xxv) To Update the new colum with values as Salary + HRA + Dearness Allowance - Tax

Write the output of the given commands:

WAP using function to change the alternate index value of the element.

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 (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)

Write a script to create a list of 10 random floating point numbers and then
(a) Display this list of numbers
(b) Find the largest and smallest numbers present in the list (without using max() and min() functions)

Write a script to create a list of 10 random integers and display it. Then input an integer from the user and check whether it is present in the list (use in operator). If it is present in the list, then remove it from the list. If the element is present in the list multiple times then all its occurrences should be removed. (use remove() method). If the element is not present, then display an appropriate message.