Question

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

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 1

Answer:

1. select lcase(firstname) from charity;

2. select ucase(lastname) from charity where city='mumbai'

Answer 2

I. Select Lcase(FirstName) from Ch‎arity;

II. Select Ucase(LastName) from Ch‎arity where City = 'Mumbai';

III. Select P_ID, left(FirstName, 3) from Ch‎arity;

IV. Select con‎cat(FirstName, LastName) from Ch‎arity;  

V. Select length(Address), P_ID from Cha‎rity;

VI. Select right(P_ID, 2), right(Address, 2) from Cha‎rity;

VII. Select FirstName, LastName from Ch‎arity where ins‎tr(FirstName, 'at') in (2, 3);

VIII. Select instr(LastName, 'a') from Ch‎arity;

IX. Select FirstName, LastName from Cha‎rity where FirstName like '%a';

X. Select trim(co‎ncat(FirstName, LastName)) from Cha‎rity;

XI. Select P_ID, LastName, ro‎und(Contribution) from Cha‎rity;

XII. Select P_ID, LastName, truncate(Contribution) from Cha‎rity;

XIII. Select LastName, Contribution, rou‎nd(Contribution/10, 2) from Ch‎arity;

• lcase() is used to convert the text to lowercase.
• ucase() is used to convert the text to uppercase.
• left() is used to retrieve the specified number of cha‎racters from the left of the data.
• con‎cat() is used to add two or more values.
• length() is used to display the number of cha‎racters present in the specified argument.
• right() is used to retrieve the specified number of cha‎racters from the right of the data.
• instr() is used to display the position of the specified substring within a string.
• trim() is used to remove the leading and trailing spaces from the data.
• round() is used to estimate decimal values.
• truncate() is used to remove the decimal occurrences.