Skip to main content

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

Labels

Labels:

Comments

Post a Comment

Popular posts from this blog

CS - SORTING/SEARCHING ALGORITHMS

  SORTING ALGORITHMS                       SORTING ALGORITHM PDF LINK #Bubble Sort          ·        The outer loop iterates through the entire array. ·        The inner loop compares adjacent elements and swaps them if they are out of order. ·        The outer loop runs n times, and each pass moves the largest element to its correct position. arr=[3,8,5,2,1] n = len(arr) print(n) for i in range(n):  #traverse through all the elements         # Last i elements are already sorted, no need to check them         for j in range(0, n-i-1):              # Swap if the element found is greater than the next element              if arr[j] > arr[j+1]:               ...
Post a Comment
Read more

GRADE XI - NESTED FOR LOOP

                                                         NESTED FOR LOOP 1. for var1 in range(3):      print(var1,"OUTER LOOP")          # 0 outer loop       1 outer loop      2 outer loop          for var2 in range(2):                  print(var2+1,"INNER LOOP")    #1  2 inner loop     1  2  inner loop   1 2 inner loop  2. Print the following pattern using for loop: 1  1 2  1 2 3  1 2 3 4  Sol: for r in range(1,5):   #ROWS     for c in range(1,r+1):   #COLUMNS         print(c,end=" ")     print() 3. Print the following pattern using for loop: @  @ @  @ @ @...
Post a Comment
Read more

LIST IN PYTHON - 14TH AUG

  LIST IN PYTHON BEGINNERS PRACTICE QUESTIONS 1. Creating List by accepting data from user List1=[] #Empty list n=int(input("Enter number of elements"))    i=0 while i < n:     num= int(input("Enter element " + str(i) + ": "))     List1+=[num]       i+=1 print(List1) OR #Creating List by accepting data from user List1=[ ] n=int(input("Enter number of elements"))   i=0 while i < n:     List1+=[int(input("Enter the element"))]       i+=1     print(List1) 2.Write a program to input the size and the list of elements from user. Count the number of elements  which are divisible by 5 list1= [ ] s =int (input("Enter no of elements")) for i in range(s):     list1 += [int (input("Enter the Elements"))] count=0 for num in list1:         if num % 5 ==0:             count = count+1 print("No. of elements divisible by 5--",c...
Post a Comment
Read more