Scribd
Upload
784 views3 pages

Home Work-2 Function in MySQL (Answers) 13 Apr 2020

This document contains answers to SQL queries on sample tables "Charity" and "Grocer": 1. The queries perform various operations on columns like selecting parts of strings, checking for patterns, performing calculations and aggregations, and formatting outputs. 2. Functions used include LOWER(), UPPER(), LEFT(), RIGHT(), TRIM(), ROUND(), NOW(), DATE(), DAYNAME(), MONTH(), YEAR(), LENGTH(), INSTR(), and others. 3. The queries are designed to return the requested columns, applying the specified conditions, manipulations, and formats.

Uploaded by

vikas_2
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
784 views3 pages

Home Work-2 Function in MySQL (Answers) 13 Apr 2020

This document contains answers to SQL queries on sample tables "Charity" and "Grocer": 1. The queries perform various operations on columns like selecting parts of strings, checking for patterns, performing calculations and aggregations, and formatting outputs. 2. Functions used include LOWER(), UPPER(), LEFT(), RIGHT(), TRIM(), ROUND(), NOW(), DATE(), DAYNAME(), MONTH(), YEAR(), LENGTH(), INSTR(), and others. 3. The queries are designed to return the requested columns, applying the specified conditions, manipulations, and formats.

Uploaded by

vikas_2
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

Answers of

Function in MySQL (Home work-2)


13 Apr 2020
1. Consider the table named "Charity" and write SQL queries for the
tasks that follow:

I. Display all first names in lowercase


Ans. Select lower(firstname) from charity;
II. Display all last names of people of Mumbai city in uppercase
Ans. Select upper(lastname) from charity where city = “Mumbai”;
III. Display Person Id along and First 3 characters of his/her first name.
Ans. Select P_Id, Left(firstname) from charity;
IV. Display length of address column and Person Id
Ans. Select length(address), P_Id from charity;
V. Display last 2 characters of City and Last name
Ans. Select right(city), lastname from charity;
VI. Display Last Names and First names of people who have "at" in the
second or third position in their first names.
Ans. Select lastname, firstname from charity where
Instr(firstname, "at")=2 || Instr(firstname, "at")=3;
or
Select lastname, firstname from charity where
firstname like "_ at%" || firstname like "_ _at%";
VII. Display the position of 'a' in Last name in every row.
Ans. Select Instr(lastname, "a") from charity;
VIII Display Last Name and First name of people who have "a" as the last
character in their First names.
Ans. Select lastname, firstname from charity where
right(firstname,1) = "a";
or
Select lastname, firstname from charity where
firstname Like "%a";
IX. Display the first name after removing the leading and trailing blanks.
Ans. Select Trim(firstname) from charity;
X. Display Person Id, last names and contribution rounded to the nearest
rupee of all the persons.
Ans. Select P_Id, lastname, Round(contribution) from charity;
XI. Display Last name, contribution and a third column which has
contribution divided by 10. Round it to two decimal points.
Ans. Select lastname, contribution, Round(contribution/10, 2) as
"Third Column" from charity;
2. Consider the table "Grocer" and write SQL queries for the tasks that
follow:

I. Display Item name, unit price along with Total amount (i.e. price*qty) for
all the Items.
Ans. Select itemname, unitprice, unitprice*quantity as “Total amount”
from grocer;
II. Display Item name along with Month (in number) when it was purchased
for all the items.
Ans. Select itemname, month(date_purchase) from grocer;

III. Display Item name along with year in which it was purchased for all the
items.
Ans. Select itemname, year(date_purchase) from grocer;

IV. Display Item Id, Date of Purchase and day name of week (e.g. Monday)
on which it was purchased for all the items.
Ans. Select item_id, date_purchase, dayname(date_purchase) from
grocer;

V. Display names of all the items that were purchased on Mondays or


Tuesdays.
Ans. Select itemname from grocer where
dayname(date_purchase) = “Monday” ||
dayname(date_purchase) = “Tuesday”;

VI. Display the day name of the week on which Rice was purchased.
Ans. Select dayname(date_purchase) from grocer where
temname=”Rice”;

VII. Display the Item name and unit price rounded to integer value (no
decimal digits) of all the items.
Ans. Select itemname, round(unitprice, 0) from grocer;

VIII. Display current date and time.


Ans. Select now();

You might also like