MYSQL FUNCTIONS

QUERIES BASED ON CHARITY MYSQL TABLE

 GRADE XII

Link : Refer this for only MYSQL

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

that follow:

Table: Charity

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.

SOLUTION

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.

ASSIGNMENT - II

Consider the table "Grocer" and write SQL queries for the tasks that follow:

Table: Grocer

I. Display Item name, unit price along with Date of purchase for all the Items.

II. Display Item name along with Month (in number) when it was purchased for

all the items.

III. Display Item name along with year in which it was purchased for all the items.

IV. Display Item Id, Date of Purchase and day name of week (e.g. Monday) on

which it was purchased for all the items.

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

VI. Display the day name of the week on which Rice was purchased.

VII. Display the Item name and unit price truncated to integer value (no decimal

digits)of all the items.

VIII. Display current date

SOLUTION

mysql> SELECT ItemName,UnitPrice,Date_Purchase FROM grocer;

+----------+-----------+---------------+

| ItemName | UnitPrice | Date_Purchase |

+----------+-----------+---------------+

| Rice     |     52.20 | 2021-02-01    |

| Wheat    |     25.40 | 2010-03-09    |

| Corn     |     50.80 | 2010-03-11    |

| Semolina |     28.90 | 2010-01-15    |

+----------+-----------+---------------+

4 rows in set (0.01 sec)

mysql> SELECT ItemName, YEAR(DATE_PURCHASE) FROM Grocer;

+----------+---------------------+

| ItemName | YEAR(DATE_PURCHASE) |

+----------+---------------------+

| Rice     |                2021 |

| Wheat    |                2010 |

| Corn     |                2010 |

| Semolina |                2010 |

+----------+---------------------+

4 rows in set (0.00 sec)

mysql> SELECT ItemName, MONTH(DATE_PURCHASE) FROM Grocer;

+----------+----------------------+

| ItemName | MONTH(DATE_PURCHASE) |

+----------+----------------------+

| Rice     |                    2 |

| Wheat    |                    3 |

| Corn     |                    3 |

| Semolina |                    1 |

+----------+----------------------+

4 rows in set (0.00 sec)

mysql> SELECT Item_ID,Date_Purchase,DAYNAME(Date_Purchase) FROM Grocer;

+---------+---------------+------------------------+

| Item_ID | Date_Purchase | DAYNAME(Date_Purchase) |

+---------+---------------+------------------------+

| 1       | 2021-02-01    | Monday                 |

| 2       | 2010-03-09    | Tuesday                |

| 3       | 2010-03-11    | Thursday               |

| 4       | 2010-01-15    | Friday                 |

+---------+---------------+------------------------+

4 rows in set (0.00 sec)

mysql> SELECT Item_ID, ItemName FROM Grocer WHERE DAYNAME(DATE_PURCHASE) IN ('MONDAY','TUESDAY');

+---------+----------+

| Item_ID | ItemName |

+---------+----------+

| 1       | Rice     |

| 2       | Wheat    |

+---------+----------+

2 rows in set (0.00 sec)

mysql> SELECT DAYNAME(DATE_PURCHASE) FROM GROCER WHERE ITEMNAME='RICE';

+------------------------+

| DAYNAME(DATE_PURCHASE) |

+------------------------+

| Monday                 |

+------------------------+

1 row in set (0.00 sec)

mysql> SELECT ITEMNAME, TRUNCATE(UNITPRICE,0) FROM GROCER;

+----------+-----------------------+

| ITEMNAME | TRUNCATE(UNITPRICE,0) |

+----------+-----------------------+

| Rice     |                    52 |

| Wheat    |                    25 |

| Corn     |                    50 |

| Semolina |                    28 |

+----------+-----------------------+

4 rows in set (0.00 sec)

mysql> SELECT ITEMNAME, TRUNCATE(UNITPRICE,1) FROM GROCER;

+----------+-----------------------+

| ITEMNAME | TRUNCATE(UNITPRICE,1) |

+----------+-----------------------+

| Rice     |                  52.2 |

| Wheat    |                  25.4 |

| Corn     |                  50.8 |

| Semolina |                  28.9 |

+----------+-----------------------+

4 rows in set (0.00 sec)

mysql> SELECT CURDATE();

+------------+

| CURDATE()  |

+------------+

| 2020-12-03 |

+------------+

1 row in set (0.01 sec)

X-------------------------------------------------------X-----------------------------------------------X