MySQL Practical Questions

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

Table: Charity

(Contribution is in Rs.)

a) Display all first names in lowercase.

b) Display all last names of people of Mumbai city in uppercase.
c) Display Person Id along with First 3 characters of his/her name.
d) Display first name concatenated with last name for all the employees.
e) Display length of address along with Person Id.
f) Display last 2 characters of City and Person Id.
g) Display Last Names and First names of people who have "at" in the second or third position in their
first names.
h) Display the position of 'a' in Last name in every row.
i) Display Last Name and First name of people who have "a" as the last character in their First names.
j) Display the first name and last name concatenated after removing the leading and trailing blanks.
k) Display Person Id, last names and contribution rounded to the nearest rupee of all the persons.
l) Display Person Id, last name and contribution with decimal digits truncated of all the persons.
m) Display Last name, contribution and a third column which has contribution divided by 10. Round it
to two decimal points.

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

Table: Grocer

(Unit Price is per kg price)

a) Display Item name, unit price along with Date of purchase for all the Items.
b) Display Item name along with Month (in number) when it was purchased for all the items.
c) Display Item name along with year in which it was purchased for all the items.
d) Display Item Id, Date of Purchase and day name of week (e.g. Monday) on which it was purchased
for all the items.
e) Display names of all the items that were purchased on Mondays or Tuesdays.
f) Display the day name of the week on which Rice was purchased.
g) Display the Item name and unit price truncated to integer value (no decimal digits) of all the items.
h) Display current date.

3. Write SQL statements to do the following:

a) Using the three separate words "We," "study," and "MySQL," produce the following output:
"We study MySQL"
b) Use the string "Internet is a boon" and extract the string "net".
c) Display the length of the string "Informatics Practices".
d) Display the position of "My" in "Enjoying MySQL".
e) Display the name of current month.
f) Display the date 10 years from now. Label the column "Future."
g) Display the day of week on which your birthday will fall or fell in 2010.

4. Consider the following table named "GYM" with details about Fitness products being sold in
the store.
Table Name : GYM
PrCode stores Codes of Products
PrName stores names of Products
(UnitPrice is in Rs.)

Write SQL statements to do the following:

a) Display the names of all the products in the store.
b) Display the names and unit price of all the products in the store.
c) Display the names of all the products with unit price less than Rs.20000.00
d) Display details of all the products with unit price in the range 20000 to 30000
e) Display names of all products by the manufacturer "Fit Express".
f) Display all rows sorted in descending order of unit price.
g) Add a new row for product with the details: "P106","Vibro Exerciser", 23000, manufacturer : "Avon
Fitness".
h) Change the Unit Price data of all the rows by applying a 10% discount reduction on all the products.
i) Display details of all products with manufacturer name starting with "A".
5. Consider the following tables Employee and Department.

Write SQL statements to do the following:

a) Display the last names and first names of all employees.
b) Display the Department names of all employees, without duplicates.
c) Display all the details of employees with last name as "Lakshmi".
d) Display all the details of employees whose last name is ""Rajlani" or "Sharma".
e) Display the codes and first names of all employees of 'Accounts' department.
f) Display department names of departments with budget above 18000.
g) Display all the details of employees whose First name begins with "S".
h) Display department details(from Department table) in descending order of Budget amount.
i) Change the Department name "Sales" to "Marketing" everywhere in the table "Employee" and
"Department"
j) Add a new row with appropriate data values in Department table.
k) Create the table Department with columns of appropriate data types.

6. Consider a database LOANS with the following table:

Write SQL commands for the tasks 1 to 35 and write the output for the SQL commands 36 to 40:
Create Database and use it
1. Create the database LOANS.
2. Use the database LOANS.
Create Table / Insert Into
3. Create the table Loan_Accounts and insert tuples in it.
Simple Select
4. Display the details of all the loans.
5. Display the AccNo, Cust_Name, and Loan_Amount of all the loans.
Conditional Select using Where Clause
6. Display the details of all the loans with less than 40 instalments.
7. Display the AccNo and Loan_Amount of all the loans started before 01-04-2009.
8. Display the Int_Rate of all the loans started after 01-04-2009.

Using NULL
9. Display the details of all the loans whose rate of interest is NULL.
10. Display the details of all the loans whose rate of interest is not NULL.
Using DISTINCT Clause
11. Display the amounts of various loans from the table Loan_Accounts. A loan amount should appear
only once.
12. Display the number of instalments of various loans from the table Loan_Accounts. An instalment
should appear only once.
Using Logical Operators (NOT, AND, OR)
13. Display the details of all the loans started after 31-12-2008 for which the number of instalments
are more than 36.
14. Display the Cust_Name and Loan_Amount for all the loans which do not have number of
instalments 36.
15. Display the Cust_Name and Loan_Amount for all the loans for which the loan amount is less than
500000 or int_rate is more than 12.
16. Display the details of all the loans which started in the year 2009.
17. Display the details of all the loans whose Loan_Amount is in the range 400000 to 500000.
18. Display the details of all the loans whose rate of interest is in the range 11% to 12%.
Using IN Operator
19. Display the Cust_Name and Loan_Amount for all the loans for which the number of instalments are
24, 36, or 48. (Using IN operator)
Using BETWEEN Operator
20. Display the details of all the loans whose Loan_Amount is in the range 400000 to 500000. (Using
BETWEEN operator)
21. Display the details of all the loans whose rate of interest is in the range 11% to 12%. (Using
BETWEEN operator)
Using LIKE Operator
22. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name ends
with 'Sharma'.
23. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name ends
with 'a'.
24. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name
contains 'a'
25. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name does
not contain 'P'.
26. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name
contains 'a' as the second last character.
Using ORDER BY clause
27. Display the details of all the loans in the ascending order of their Loan_Amount.
28. Display the details of all the loans in the descending order of their Start_Date.
29. Display the details of all the loans in the ascending order of their Loan_Amount and within
Loan_Amount in the descending order of their Start_Date.
Using UPDATE, DELETE, ALTER TABLE
30. Put the interest rate 11.50% for all the loans for which interest rate is NULL.
31. Increase the interest rate by 0.5% for all the loans for which the loan amount is more than 400000.
32. For each loan replace Interest with (Loan_Amount*Int_Rate*Instalments) 12*100.
33. Delete the records of all the loans whose start date is before 2007.
34. Delete the records of all the loans of 'K.P. Jain'
35. Add another column Category of type CHAR(1) in the Loan table.

Find the Output of the following queries

36. SELECT cust_name, LENGTH(Cust_Name), LCASE(Cust_Name), UCASE(Cust_Name) FROM
Loan_Accounts WHERE Int_Rate < 11.00;
37. SELECT LEFT(Cust_Name, 3), Right(Cust_Name, 3), SUBSTR(Cust_Name, 1, 3) FROM
Loan_Accounts WHERE Int_Rate > 10.00;
38. SELECT RIGHT(Cust_Name, 3), SUBSTR(Cust_Name, 5) FROM Loan_Accounts;
39. SELECT DAYNAME(Start_Date) FROM Loan_Accounts;
40. SELECT ROUND(Int_Rate*110/100, 2) FROM Loan_Account WHERE Int_Rate > 10;
Write the output produced by the following SQL commands:
41. SELECT POW(4,3), POW(3,4);
42. SELECT ROUND(543.5694,2), ROUND(543.5694), ROUND(543.5694,-1);
43. SELECT TRUNCATE(543.5694,2), TRUNCATE(543.5694,-1);
44. SELECT LENGTH("Prof. M. L. Sharma");
45. SELECT CONCAT("SHEIKH", " HAROON") "FULL NAME";
46. SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAY(CURDATE());
47. SELECT DAYOFYEAR(CURDATE()), DAYOFMONTH(CURDATE()), DAYNAME(CURDATE());
48. SELECT LEFT("Unicode",3), RIGHT("Unicode",4);
49. SELECT INSTR("UNICODE","CO"), INSTR("UNICODE","CD");
50. SELECT MID("Informatics",3,4), SUBSTR("Practices",3);

7. Write SQL commands for the following on the basis of given table relation Teacher.
 a) To show all information about the teacher of history department.
b) To list the names of female teachers who are in Hindi department.
c) To list names of all teachers with their date of joining in ascending order.

8. Consider the Empl table and write SQL command to get the following:

a) Write a query to display EName and Sal of employees whose salary are greater than or equal to
2200?
b) Write a query to display details of employs who are not getting commission?
c) Write a query to display employee name and salary of those employees who don’t have their
salary in range of 2500 to 4000?
d) Write a query to display the name, job title and salary of employees who don’t have manager?
e) Write a query to display the name of employee whose name contains “A” as third alphabet?
f) Write a query to display the name of employee whose name contains “T” as last alphabet?
g) Write a query to display the name of employee whose name contains ”M” as First and “L” as
third alphabet?
h) Write a query to display details of employees with the text “Not given”, if commission is null?

Full Solutions

1. Solutions for Charity Table Queries

a) Display all first names in lowercase.
• SELECT LOWER(First_Name) FROM Charity;
This query converts all first names to lowercase using the LOWER() function.
b) Display all last names of people from Mumbai city in uppercase.
• SELECT UPPER(Last_Name) FROM Charity WHERE City = 'Mumbai';
The UPPER() function converts last names to uppercase for entries where the city is 'Mumbai'.
c) Display Person Id along with First 3 characters of his/her name.
• SELECT Person_ID, LEFT(First_Name, 3) FROM Charity;
The LEFT() function extracts the first three characters of the first name.
d) Display first name concatenated with last name for all the employees.
• SELECT CONCAT(First_Name, ' ', Last_Name) FROM Charity;
The CONCAT() function merges first and last names with a space in between.
e) Display length of address along with Person Id.
• SELECT Person_ID, LENGTH(Address) FROM Charity;
 The LENGTH() function returns the number of characters in the address.
f) Display last 2 characters of City and Person Id.
• SELECT RIGHT(City, 2), Person_ID FROM Charity;
The RIGHT() function extracts the last two characters of the city name.
g) Display Last Names and First names of people who have 'at' in the second or third position in their
first names.
• SELECT Last_Name, First_Name FROM Charity WHERE First_Name LIKE '_at%' OR First_Name LIKE
'__at%';
The _ underscores represent single character wildcards in the LIKE pattern.
h) Display the position of 'a' in Last name in every row.
• SELECT Last_Name, First_Name, LOCATE('a', Last_Name) FROM Charity;
The LOCATE() function finds the position of 'a' in the last name.
i) Display Last Name and First name of people who have 'a' as the last character in their First names.
• SELECT Last_Name, First_Name FROM Charity WHERE First_Name LIKE '%a';
The % wildcard allows any preceding characters, ensuring names ending in 'a' are selected.
j) Display the first name and last name concatenated after removing the leading and trailing blanks.
• SELECT TRIM(CONCAT(First_Name, ' ', Last_Name)) FROM Charity;
The TRIM() function removes extra spaces from the concatenated names.
k) Display Person Id, last names, and contribution rounded to the nearest rupee.
• SELECT Person_ID, Last_Name, ROUND(Contribution) FROM Charity;
The ROUND() function rounds contributions to the nearest whole number.
l) Display Person Id, last name, and contribution with decimal digits truncated.
• SELECT Person_ID, Last_Name, TRUNCATE(Contribution, 0) FROM Charity;
The TRUNCATE() function removes decimal values without rounding.
m) Display Last name, contribution, and a third column which has contribution divided by 10, rounded
to two decimal points.
• SELECT Last_Name, Contribution, ROUND(Contribution / 10, 2) FROM Charity;
The ROUND() function ensures that the result is limited to two decimal places.
2. Solutions for Grocer Table Queries
a) Display Item name, unit price along with Date of purchase for all the Items.
SELECT Item_Name, Unit_Price, Date_of_Purchase FROM Grocer;
Retrieves the item name, price, and purchase date for all grocery items.
b) Display Item name along with Month (in number) when it was purchased for all the items.
SELECT Item_Name, MONTH(Date_of_Purchase) FROM Grocer;
The MONTH() function extracts the numeric month from the purchase date.
c) Display Item name along with year in which it was purchased for all the items.
SELECT Item_Name, YEAR(Date_of_Purchase) FROM Grocer;
The YEAR() function extracts the year of purchase.
d) Display Item Id, Date of Purchase and day name of week (e.g. Monday) on which it was purchased.
SELECT Item_ID, Date_of_Purchase, DAYNAME(Date_of_Purchase) FROM Grocer;
The DAYNAME() function returns the name of the day (Monday, Tuesday, etc.).
e) Display names of all the items that were purchased on Mondays or Tuesdays.
SELECT Item_Name FROM Grocer WHERE DAYNAME(Date_of_Purchase) IN ('Monday', 'Tuesday');
Filters items purchased on specified weekdays.
f) Display the day name of the week on which Rice was purchased.
SELECT DAYNAME(Date_of_Purchase) FROM Grocer WHERE Item_Name = 'Rice';
Retrieves the day of the week when Rice was bought.
g) Display the Item name and unit price truncated to integer value (no decimal digits).
SELECT Item_Name, TRUNCATE(Unit_Price, 0) FROM Grocer;
The TRUNCATE() function removes decimal values without rounding.
h) Display current date.
SELECT CURDATE();
The CURDATE() function returns the system’s current date.

3. String and Date Functions

a) Using the three separate words 'We,' 'study,' and 'MySQL,' produce the following output: 'We study
MySQL'.
• SELECT CONCAT('We', ' ', 'study', ' ', 'MySQL');
b) Use the string 'Internet is a boon' and extract the string 'net'.
• SELECT SUBSTRING('Internet is a boon', 3, 3);
c) Display the length of the string 'Informatics Practices'.
• SELECT LENGTH('Informatics Practices');
d) Display the position of 'My' in 'Enjoying MySQL'.
• SELECT LOCATE('My', 'Enjoying MySQL');
e) Display the name of the current month.
• SELECT MONTHNAME(CURDATE());
f) Display the date 10 years from now. Label the column 'Future'.
• SELECT DATE_ADD(CURDATE(), INTERVAL 10 YEAR) AS Future;
g) Display the day of the week on which your birthday fell in 2010.
• SELECT DAYNAME('2010-<your-birthday-month>-<your-birthday-day>');

4. Solutions for GYM Table Queries

a) Display the names of all the products in the store.
SELECT PrName FROM GYM;
b) Display the names and unit price of all the products in the store.
SELECT PrName, UnitPrice FROM GYM;
c) Display the names of all the products with unit price less than Rs.20000.00
SELECT PrName FROM GYM WHERE UnitPrice < 20000;
d) Display details of all the products with unit price in the range 20000 to 30000.
SELECT * FROM GYM WHERE UnitPrice BETWEEN 20000 AND 30000;
e) Display names of all products by the manufacturer "Fit Express".
SELECT PrName FROM GYM WHERE Manufacturer = 'Fit Express';
f) Display all rows sorted in descending order of unit price.
SELECT * FROM GYM ORDER BY UnitPrice DESC;
g) Add a new row for a product with the details: "P106","Vibro Exerciser", 23000, manufacturer: "Avon
Fitness".
INSERT INTO GYM VALUES ('P106', 'Vibro Exerciser', 23000, 'Avon Fitness');
h) Change the Unit Price data of all the rows by applying a 10% discount reduction on all the products.
UPDATE GYM SET UnitPrice = UnitPrice * 0.9;
i) Display details of all products with manufacturer name starting with "A".
SELECT * FROM GYM WHERE Manufacturer LIKE 'A%';
5. Solutions for Employee and Department Table Queries
a) Display the last names and first names of all employees.
SELECT Last_Name, First_Name FROM Employee;
b) Display the Department names of all employees, without duplicates.
SELECT DISTINCT Department_Name FROM Employee;
c) Display all the details of employees with last name as "Lakshmi".
SELECT * FROM Employee WHERE Last_Name = 'Lakshmi';
d) Display all the details of employees whose last name is "Rajlani" or "Sharma".
SELECT * FROM Employee WHERE Last_Name IN ('Rajlani', 'Sharma');
e) Display the codes and first names of all employees of 'Accounts' department.
SELECT Emp_Code, First_Name FROM Employee WHERE Department_Name = 'Accounts';
f) Display department names of departments with budget above 18000.
SELECT Department_Name FROM Department WHERE Budget > 18000;
g) Display all the details of employees whose First name begins with "S".
SELECT * FROM Employee WHERE First_Name LIKE 'S%';
h) Display department details (from Department table) in descending order of Budget amount.
SELECT * FROM Department ORDER BY Budget DESC;
i) Change the Department name "Sales" to "Marketing" everywhere in the table "Employee" and
"Department".
UPDATE Employee SET Department_Name = 'Marketing' WHERE Department_Name = 'Sales';
UPDATE Department SET Department_Name = 'Marketing' WHERE Department_Name = 'Sales';
j) Add a new row with appropriate data values in the Department table.
INSERT INTO Department VALUES (<appropriate_values>);
k) Create the table Department with columns of appropriate data types.
CREATE TABLE Department (
Dept_Code INT PRIMARY KEY,
Dept_Name VARCHAR(50),
Budget DECIMAL(10,2)
);

6. Solutions for LOANS Database

Create Database and Use It
1. Create the database LOANS.
CREATE DATABASE LOANS;
2. Use the database LOANS.
USE LOANS;
Create Table / Insert Into
3. Create the table Loan_Accounts and insert tuples in it.
CREATE TABLE Loan_Accounts (
AccNo INT PRIMARY KEY,
Cust_Name VARCHAR(100),
Loan_Amount DECIMAL(10,2),
Instalments INT,
Start_Date DATE,
Int_Rate DECIMAL(5,2)
);
INSERT INTO Loan_Accounts VALUES (101, 'Rahul Sharma', 500000, 48, '2009-03-15', 11.5);
Simple Select
4. Display the details of all the loans.
SELECT * FROM Loan_Accounts;
5. Display the AccNo, Cust_Name, and Loan_Amount of all the loans.
SELECT AccNo, Cust_Name, Loan_Amount FROM Loan_Accounts;
Conditional Select using WHERE Clause
6. Display the details of all the loans with less than 40 instalments.
SELECT * FROM Loan_Accounts WHERE Instalments < 40;
7. Display the AccNo and Loan_Amount of all the loans started before 01-04-2009.
SELECT AccNo, Loan_Amount FROM Loan_Accounts WHERE Start_Date < '2009-04-01';
8. Display the Int_Rate of all the loans started after 01-04-2009.
SELECT Int_Rate FROM Loan_Accounts WHERE Start_Date > '2009-04-01';
Using NULL
9. Display the details of all the loans whose rate of interest is NULL.
SELECT * FROM Loan_Accounts WHERE Int_Rate IS NULL;
10. Display the details of all the loans whose rate of interest is not NULL.
SELECT * FROM Loan_Accounts WHERE Int_Rate IS NOT NULL;
Using DISTINCT Clause
11. Display the amounts of various loans from the table Loan_Accounts. A loan amount should
appear only once.
SELECT DISTINCT Loan_Amount FROM Loan_Accounts;
12. Display the number of instalments of various loans from the table Loan_Accounts. An
instalment should appear only once.
SELECT DISTINCT Instalments FROM Loan_Accounts;
Using Logical Operators (NOT, AND, OR)
13. Display the details of all the loans started after 31-12-2008 for which the number of
instalments are more than 36.
SELECT * FROM Loan_Accounts WHERE Start_Date > '2008-12-31' AND Instalments > 36;
14. Display the Cust_Name and Loan_Amount for all the loans which do not have number of
instalments 36
SELECT Cust_Name, Loan_Amount FROM Loan_Accounts WHERE Instalments <> 36;
15. Display the Cust_Name and Loan_Amount for all the loans for which the loan amount is
less than 500000 or int_rate is more than 12.
SELECT Cust_Name, Loan_Amount FROM Loan_Accounts WHERE Loan_Amount < 500000 OR Int_Rate >
12;
16. Display the details of all the loans which started in the year 2009.
SELECT * FROM Loan_Accounts WHERE YEAR(Start_Date) = 2009;
17. Display the details of all the loans whose Loan_Amount is in the range 400000 to 500000.
SELECT * FROM Loan_Accounts WHERE Loan_Amount BETWEEN 400000 AND 500000;
18. Display the details of all the loans whose rate of interest is in the range 11% to 12%.
SELECT * FROM Loan_Accounts WHERE Int_Rate BETWEEN 11 AND 12;
Using IN Operator
19. Display the Cust_Name and Loan_Amount for all the loans for which the number of
instalments are 24, 36, or 48. (Using IN operator).
SELECT Cust_Name, Loan_Amount FROM Loan_Accounts WHERE Instalments IN (24, 36, 48);

Using BETWEEN Operator

20. Display the details of all the loans whose Loan_Amount is in the range 400000 to 500000.
SELECT * FROM Loan_Accounts WHERE Loan_Amount BETWEEN 400000 AND 500000;
21. Display the details of all the loans whose rate of interest is in the range 11% to 12%.
SELECT * FROM Loan_Accounts WHERE Int_Rate BETWEEN 11 AND 12;
Using LIKE Operator
22. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name ends
with 'Sharma'.
SELECT AccNo, Cust_Name, Loan_Amount FROM Loan_Accounts WHERE Cust_Name LIKE '%Sharma';
23. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the
Cust_Name ends with 'a'.
SELECT AccNo, Cust_Name, Loan_Amount FROM Loan_Accounts WHERE Cust_Name LIKE '%a';
24. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the
Cust_Name contains 'a'
SELECT AccNo, Cust_Name, Loan_Amount FROM Loan_Accounts WHERE Cust_Name LIKE '%a%';
25. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name does
not contain 'P'.
SELECT AccNo, Cust_Name, Loan_Amount FROM Loan_Accounts WHERE Cust_Name NOT LIKE '%P%';
26. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the
Cust_Name contains 'a' as the second last character.
 SELECT AccNo, Cust_Name, Loan_Amount FROM Loan_Accounts WHERE Cust_Name LIKE '%a_';
Using ORDER BY Clause
27. Display the details of all the loans in the ascending order of their Loan_Amount.
SELECT * FROM Loan_Accounts ORDER BY Loan_Amount ASC;
28. Display the details of all the loans in the descending order of their Start_Date.
SELECT * FROM Loan_Accounts ORDER BY Start_Date DESC;
29. Display the details of all the loans in the ascending order of their Loan_Amount and
within Loan_Amount in the descending order of their Start_Date.
SELECT * FROM Loan_Accounts ORDER BY Loan_Amount ASC, Start_Date DESC;
Using UPDATE, DELETE, ALTER TABLE
30. Put the interest rate 11.50% for all the loans for which interest rate is NULL.
UPDATE Loan_Accounts SET Int_Rate = 11.50 WHERE Int_Rate IS NULL;
31. Increase the interest rate by 0.5% for all the loans for which the loan amount is more than
400000.
UPDATE Loan_Accounts SET Int_Rate = Int_Rate + 0.5 WHERE Loan_Amount > 400000;
32. For each loan replace Interest with (Loan_Amount*Int_Rate*Instalments) 12*100.
UPDATE Loan_Accounts SET Int_Rate = (Loan_Amount * Int_Rate * Instalments) / (12 * 100);
33. Delete the records of all the loans whose start date is before 2007.
DELETE FROM Loan_Accounts WHERE Start_Date < '2007-01-01';
34. Delete the records of all the loans of 'K.P. Jain'
DELETE FROM Loan_Accounts WHERE Cust_Name = 'K.P. Jain';
35. Add another column Category of type CHAR(1) in the Loan table.
ALTER TABLE Loan_Accounts ADD Category CHAR(1);

Find the Output of the following queries

36. SELECT cust_name, LENGTH(Cust_Name), LCASE(Cust_Name), UCASE(Cust_Name) FROM
Loan_Accounts WHERE Int_Rate < 11.00;
SELECT cust_name, LENGTH(Cust_Name), LCASE(Cust_Name), UCASE(Cust_Name) FROM Loan_Accounts
WHERE Int_Rate < 11.00;
37. SELECT LEFT(Cust_Name, 3), RIGHT(Cust_Name, 3), SUBSTR(Cust_Name, 1, 3) FROM Loan_Accounts
WHERE Int_Rate > 10.00;
38. SELECT RIGHT(Cust_Name, 3), SUBSTR(Cust_Name, 5) FROM Loan_Accounts;
39. SELECT DAYNAME(Start_Date) FROM Loan_Accounts;
40. SELECT ROUND(Int_Rate * 110 / 100, 2) FROM Loan_Accounts WHERE Int_Rate > 10;
41. SELECT POW(4,3), POW(3,4);
42. SELECT ROUND(543.5694,2), ROUND(543.5694), ROUND(543.5694,-1);
43. SELECT TRUNCATE(543.5694,2), TRUNCATE(543.5694,-1);
44. SELECT LENGTH("Prof. M. L. Sharma");
45. SELECT CONCAT("SHEIKH", " HAROON") AS FULL_NAME;
46. SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAY(CURDATE());
47. SELECT DAYOFYEAR(CURDATE()), DAYOFMONTH(CURDATE()), DAYNAME(CURDATE());
48. SELECT LEFT("Unicode",3), RIGHT("Unicode",4);
49. SELECT INSTR("UNICODE","CO"), INSTR("UNICODE","CD");
50. SELECT MID("Informatics",3,4), SUBSTR("Practices",3);

7. Solutions for Teacher Table Queries

a) To show all information about the teacher of history department.
SELECT * FROM Teacher WHERE Department = 'History';
b) To list the names of female teachers who are in Hindi department.
SELECT Name FROM Teacher WHERE Gender = 'Female' AND Department = 'Hindi';
c) To list names of all teachers with their date of joining in ascending order.
SELECT Name, Date_of_Joining FROM Teacher ORDER BY Date_of_Joining ASC;
8. Solutions for Empl Table Queries
a) Write a query to display EName and Sal of employees whose salary is greater than or equal to 2200.
SELECT EName, Sal FROM Empl WHERE Sal >= 2200;
b) Write a query to display details of employees who are not getting commission.
SELECT * FROM Empl WHERE Commission IS NULL;
c) Write a query to display employee name and salary of those employees who don’t have their salary in
the range of 2500 to 4000.
SELECT EName, Sal FROM Empl WHERE Sal NOT BETWEEN 2500 AND 4000;
d) Write a query to display the name, job title, and salary of employees who don’t have a manager.
SELECT EName, Job_Title, Sal FROM Empl WHERE Manager_ID IS NULL;
e) Write a query to display the name of employees whose name contains 'A' as the third alphabet.
SELECT EName FROM Empl WHERE EName LIKE '__A%';
f) Write a query to display the name of employees whose name contains 'T' as the last alphabet.
SELECT EName FROM Empl WHERE EName LIKE '%T';
g) Write a query to display the name of employees whose name contains 'M' as the first and 'L' as the
third alphabet.
SELECT EName FROM Empl WHERE EName LIKE 'M_L%';
h) Write a query to display details of employees with the text 'Not given' if commission is null.
SELECT EName, Sal, COALESCE(Commission, 'Not given') AS Commission FROM Empl;