Querying and SQL Functions
Sample database Database: CARSHOWROOM
Relations:
• INVENTORY (CarID, CarName, Price, Model, YearManufacture, FuelType)
• CUSTOMER (CustID, CustName, CustAdd, Phone, Email)
• SALE (InvoiceNo, CarID, CustID, SaleDate, PaymentMode, EmpID, SalePrice)
• EMPLOYEE (EmpID, EmpName, DOB, DOJ, Designation, Salary)
• MANAGER(MNo, MNAME)
• DANCE(SNo, Name, Class)
• MUSIC(SNo, Name, Class)
SQL Functions
A. Single Row or Scalar
Functions
A.1. Numeric or Math
Functions
A.1.1. POWER(base, exp) SELECT POWER(2, 3); #8
A.1.2. ROUND(num, dec) SELECT ROUND(2912.564, 1); #2912.6
SELECT ROUND(283.2); #283
SELECT ROUND(283.2, -1); #280
SELECT ROUND(283.2, -2); #300
SELECT ROUND(283.2, -3); #0
Calculate GST as 12% of Price and display the result after rounding it off to one decimal
place.
SELECT ROUND(12/100*Price,1) 'GST' FROM INVENTORY;
Add a new column FinalPrice to the table inventory, which will have the value as sum of
Price and 12% of the GST.
ALTER TABLE INVENTORY ADD(FinalPrice Numeric(10,1));
UPDATE INVENTORY SET FinalPrice=Price+Round(Price*12/100,1);
Add a new column Commission to the SALE table. The column Commission should have a
total length of 7 in which 2 decimal places to be there. Calculate commission for sales agents
as 12 per cent of the SalePrice, insert the values to the newly added column Commission and
then display records of the table SALE where commission > 73000.
ALTER TABLE SALE ADD(Commission Numeric(7,2));
UPDATE SALE SET Commission=ROUND(12/100*SalePrice,2);
Display InvoiceNo, SalePrice and Commission such that commission value is rounded off to
0.
SELECT InvoiceNo, SalePrice, Round(Commission,0) FROM SALE;
Display the InvoiceNo and commission value rounded off to zero decimal places.
Display the details of SALE where payment mode is credit card.
A.1.3. MOD(num, den) SELECT MOD(20, 2); #0
SELECT MOD(21, 2); #1
Calculate and display the amount to be paid each month (in multiples of 1000) which is to be
calculated after dividing the FinalPrice of the car into 10 instalments. After dividing the
amount into EMIs, find out the remaining amount to be paid immediately, by performing
modular division.
SELECT CarId, FinalPrice, ROUND((FinalPrice -
MOD(FinalPrice,10000))/10,0) 'EMI', MOD(FinalPrice,10000) 'Remaining
Amount' FROM INVENTORY;
A.2. String or Text Functions
A.2.1. UCASE(string) SELECT UCASE("Informatics Practices"); #INFORMATICS PRACTICES
OR Convert the CarMake to uppercase if its value starts with the letter ‘B’.
UPPER(string)
A.2.2. LOWER(string) SELECT LOWER("Informatics Practices"); #informatics practices
OR Display customer name in lower case and customer email in upper case from table
LCASE(string) CUSTOMER.
SELECT LOWER(CustName), UPPER(Email) FROM CUSTOMER;
A.2.3. MID(string, pos, n) SELECT MID("Informatics", 3, 4); #form
OR SELECT MID('Informatics',7); #atics
SUBSTRING(string, pos, n) Let us assume that four digit area code is reflected in the mobile number starting from
OR position number 3. For example, 1851 is the area code of mobile number 9818511338. Write
SUBSTR(string, pos, n) the SQL query to display the area code of the customer living in Rohini.
SELECT MID(Phone,3,4) FROM CUSTOMER WHERE CustAdd like '%Rohini%';
#1163
A.2.4. INSTR(string, substring) SELECT INSTR("Informatics", "ma"); #6
Display designation of employee and the position of character ‘e’ in designation, if present.
A.2.5. LENGTH(string) SELECT LENGTH("Informatics"); #11
If the length of the car’s model is greater than 4 then fetch the substring starting from
position3 till the end from attribute Model.
Display the length of the email and part of the email from the email ID before the character
‘@’. Do not print ‘@’.
unoconv_2200894909.docx 1
, Function INSTR returns the position of "@" in the email column, so to print email without
"@" use (position-1).
SELECT LENGTH(Email), LEFT(Email, INSTR(Email, "@")-1) FROM CUSTOMER;
LENGTH(Email) LEFT(Email, INSTR(Email, "@")-1)
19 amitsaha2
19 rehnuma
19 charvi123
19 gur_singh
A.2.6. LEFT(string, N) SELECT LEFT("Computer", 4); #Comp
A.2.7. RIGHT(string, N) SELECT RIGHT("SCIENCE", 3); #NCE
Display employee name and the last 2 characters of his EmpId.
A.2.8. LTRIM(string) SELECT LENGTH(" DELHI "),LENGTH(LTRIM(" DELHI ")); #11 7
A.2.9. RTRIM(string) SELECT LENGTH(" DELHI "),LENGTH(RTRIM(" DELHI ")); #11 9
A.2.10. TRIM(string) SELECT LENGTH(" DELHI "),LENGTH(RTRIM(" DELHI ")); #11 5
Display emails after removing the domain name extension “.com” from emails of the
customers.
SELECT TRIM(".com" from Email) FROM CUSTOMER;
TRIM(".com" FROM Email)
amitsaha2@gmail
rehnuma@hotmail
charvi123@yahoo
gur_singh@yahoo
Display details of all the customers having yahoo emails only.
SELECT * FROM CUSTOMER WHERE Email LIKE "%yahoo%";
CustID CustName CustAdd Phone Email
C0003 CharviNayyar 10/9, FF, Rohini 6811635425
C0004 Gurpreet A-10/2,SF, 3511056125
MayurVihar
.
A.3. Date and Time Functions
A.3.1. NOW() SELECT NOW(); #2019-07-11 19:41:17
A.3.2. DATE(date/time) SELECT DATE(NOW()); #2019-07-11
A.3.3. MONTH(date) SELECT MONTH(NOW()); #7
A.3.4. MONTHNAME(date) SELECT MONTHNAME(“2003-11-28”); # November
A.3.5. YEAR(date) SELECT YEAR(“2003-10-03”); #2003
A.3.6. DAY(date) SELECT DAY(“2003-03-24”); #24
A.3.7. DAYNAME(date) SELECT DAYNAME(“2019-07-11”); # Thursday
Select the day, month number and year of joining of all employees.
SELECT DAY(DOJ), MONTH(DOJ), YEAR(DOJ) FROM EMPLOYEE;
DAY(DOJ) MONTH(DOJ) YEAR(DOJ)
12 12 2017
5 6 2016
8 1 1999
2 12 2010
1 7 2012
1 1 2017
23 10 2013
If the date of joining is not a Sunday, then display it in the following format "Wednesday,
26, November, 1979."
SELECT DAYNAME(DOJ), DAY(DOJ), MONTHNAME(DOJ), YEAR(DOJ) FROM
EMPLOYEE WHERE DAYNAME(DOJ)!='Sunday';
DAYNAME(DOJ) DAY(DOJ) MONTHNAME(DOJ) YEAR(DOJ)
Tuesday 12 December 2017
Friday 8 January 1999
Thursday 2 December 2010
Wednesday 23 October 2013
List the day of birth for all employees whose salary is more than 25000.
Aggregate or Group or
Multiple Row Functions
MAX(col) SELECT MAX(Price) FROM INVENTORY; #673112.00
MIN(col) SELECT MIN(Price) FROM INVENTORY; #355205.00
Find the maximum and minimum commission from the SALE table.
AVG(col) SELECT AVG(Price) FROM INVENTORY; #576091.625000
Display the average price of all the cars with Model LXI from table INVENTORY.
SELECT AVG(Price) FROM INVENTORY WHERE Model="LXI"; #548306.500000
SUM(col) SELECT SUM(Price) FROM INVENTORY; #4608733.00
Find sum of Sale Price of the cars purchased by the customer having ID C0001 from table
SALE.
COUNT(col) SELECT COUNT(NAME) FROM MANAGER; #3
Display the total number of different types of Models available in table INVENTORY.
SELECT COUNT(DISTINCT Model) FROM INVENTORY; #6
COUNT(*) SELECT COUNT(*) from MANAGER; #4
unoconv_2200894909.docx 2
Sample database Database: CARSHOWROOM
Relations:
• INVENTORY (CarID, CarName, Price, Model, YearManufacture, FuelType)
• CUSTOMER (CustID, CustName, CustAdd, Phone, Email)
• SALE (InvoiceNo, CarID, CustID, SaleDate, PaymentMode, EmpID, SalePrice)
• EMPLOYEE (EmpID, EmpName, DOB, DOJ, Designation, Salary)
• MANAGER(MNo, MNAME)
• DANCE(SNo, Name, Class)
• MUSIC(SNo, Name, Class)
SQL Functions
A. Single Row or Scalar
Functions
A.1. Numeric or Math
Functions
A.1.1. POWER(base, exp) SELECT POWER(2, 3); #8
A.1.2. ROUND(num, dec) SELECT ROUND(2912.564, 1); #2912.6
SELECT ROUND(283.2); #283
SELECT ROUND(283.2, -1); #280
SELECT ROUND(283.2, -2); #300
SELECT ROUND(283.2, -3); #0
Calculate GST as 12% of Price and display the result after rounding it off to one decimal
place.
SELECT ROUND(12/100*Price,1) 'GST' FROM INVENTORY;
Add a new column FinalPrice to the table inventory, which will have the value as sum of
Price and 12% of the GST.
ALTER TABLE INVENTORY ADD(FinalPrice Numeric(10,1));
UPDATE INVENTORY SET FinalPrice=Price+Round(Price*12/100,1);
Add a new column Commission to the SALE table. The column Commission should have a
total length of 7 in which 2 decimal places to be there. Calculate commission for sales agents
as 12 per cent of the SalePrice, insert the values to the newly added column Commission and
then display records of the table SALE where commission > 73000.
ALTER TABLE SALE ADD(Commission Numeric(7,2));
UPDATE SALE SET Commission=ROUND(12/100*SalePrice,2);
Display InvoiceNo, SalePrice and Commission such that commission value is rounded off to
0.
SELECT InvoiceNo, SalePrice, Round(Commission,0) FROM SALE;
Display the InvoiceNo and commission value rounded off to zero decimal places.
Display the details of SALE where payment mode is credit card.
A.1.3. MOD(num, den) SELECT MOD(20, 2); #0
SELECT MOD(21, 2); #1
Calculate and display the amount to be paid each month (in multiples of 1000) which is to be
calculated after dividing the FinalPrice of the car into 10 instalments. After dividing the
amount into EMIs, find out the remaining amount to be paid immediately, by performing
modular division.
SELECT CarId, FinalPrice, ROUND((FinalPrice -
MOD(FinalPrice,10000))/10,0) 'EMI', MOD(FinalPrice,10000) 'Remaining
Amount' FROM INVENTORY;
A.2. String or Text Functions
A.2.1. UCASE(string) SELECT UCASE("Informatics Practices"); #INFORMATICS PRACTICES
OR Convert the CarMake to uppercase if its value starts with the letter ‘B’.
UPPER(string)
A.2.2. LOWER(string) SELECT LOWER("Informatics Practices"); #informatics practices
OR Display customer name in lower case and customer email in upper case from table
LCASE(string) CUSTOMER.
SELECT LOWER(CustName), UPPER(Email) FROM CUSTOMER;
A.2.3. MID(string, pos, n) SELECT MID("Informatics", 3, 4); #form
OR SELECT MID('Informatics',7); #atics
SUBSTRING(string, pos, n) Let us assume that four digit area code is reflected in the mobile number starting from
OR position number 3. For example, 1851 is the area code of mobile number 9818511338. Write
SUBSTR(string, pos, n) the SQL query to display the area code of the customer living in Rohini.
SELECT MID(Phone,3,4) FROM CUSTOMER WHERE CustAdd like '%Rohini%';
#1163
A.2.4. INSTR(string, substring) SELECT INSTR("Informatics", "ma"); #6
Display designation of employee and the position of character ‘e’ in designation, if present.
A.2.5. LENGTH(string) SELECT LENGTH("Informatics"); #11
If the length of the car’s model is greater than 4 then fetch the substring starting from
position3 till the end from attribute Model.
Display the length of the email and part of the email from the email ID before the character
‘@’. Do not print ‘@’.
unoconv_2200894909.docx 1
, Function INSTR returns the position of "@" in the email column, so to print email without
"@" use (position-1).
SELECT LENGTH(Email), LEFT(Email, INSTR(Email, "@")-1) FROM CUSTOMER;
LENGTH(Email) LEFT(Email, INSTR(Email, "@")-1)
19 amitsaha2
19 rehnuma
19 charvi123
19 gur_singh
A.2.6. LEFT(string, N) SELECT LEFT("Computer", 4); #Comp
A.2.7. RIGHT(string, N) SELECT RIGHT("SCIENCE", 3); #NCE
Display employee name and the last 2 characters of his EmpId.
A.2.8. LTRIM(string) SELECT LENGTH(" DELHI "),LENGTH(LTRIM(" DELHI ")); #11 7
A.2.9. RTRIM(string) SELECT LENGTH(" DELHI "),LENGTH(RTRIM(" DELHI ")); #11 9
A.2.10. TRIM(string) SELECT LENGTH(" DELHI "),LENGTH(RTRIM(" DELHI ")); #11 5
Display emails after removing the domain name extension “.com” from emails of the
customers.
SELECT TRIM(".com" from Email) FROM CUSTOMER;
TRIM(".com" FROM Email)
amitsaha2@gmail
rehnuma@hotmail
charvi123@yahoo
gur_singh@yahoo
Display details of all the customers having yahoo emails only.
SELECT * FROM CUSTOMER WHERE Email LIKE "%yahoo%";
CustID CustName CustAdd Phone Email
C0003 CharviNayyar 10/9, FF, Rohini 6811635425
C0004 Gurpreet A-10/2,SF, 3511056125
MayurVihar
.
A.3. Date and Time Functions
A.3.1. NOW() SELECT NOW(); #2019-07-11 19:41:17
A.3.2. DATE(date/time) SELECT DATE(NOW()); #2019-07-11
A.3.3. MONTH(date) SELECT MONTH(NOW()); #7
A.3.4. MONTHNAME(date) SELECT MONTHNAME(“2003-11-28”); # November
A.3.5. YEAR(date) SELECT YEAR(“2003-10-03”); #2003
A.3.6. DAY(date) SELECT DAY(“2003-03-24”); #24
A.3.7. DAYNAME(date) SELECT DAYNAME(“2019-07-11”); # Thursday
Select the day, month number and year of joining of all employees.
SELECT DAY(DOJ), MONTH(DOJ), YEAR(DOJ) FROM EMPLOYEE;
DAY(DOJ) MONTH(DOJ) YEAR(DOJ)
12 12 2017
5 6 2016
8 1 1999
2 12 2010
1 7 2012
1 1 2017
23 10 2013
If the date of joining is not a Sunday, then display it in the following format "Wednesday,
26, November, 1979."
SELECT DAYNAME(DOJ), DAY(DOJ), MONTHNAME(DOJ), YEAR(DOJ) FROM
EMPLOYEE WHERE DAYNAME(DOJ)!='Sunday';
DAYNAME(DOJ) DAY(DOJ) MONTHNAME(DOJ) YEAR(DOJ)
Tuesday 12 December 2017
Friday 8 January 1999
Thursday 2 December 2010
Wednesday 23 October 2013
List the day of birth for all employees whose salary is more than 25000.
Aggregate or Group or
Multiple Row Functions
MAX(col) SELECT MAX(Price) FROM INVENTORY; #673112.00
MIN(col) SELECT MIN(Price) FROM INVENTORY; #355205.00
Find the maximum and minimum commission from the SALE table.
AVG(col) SELECT AVG(Price) FROM INVENTORY; #576091.625000
Display the average price of all the cars with Model LXI from table INVENTORY.
SELECT AVG(Price) FROM INVENTORY WHERE Model="LXI"; #548306.500000
SUM(col) SELECT SUM(Price) FROM INVENTORY; #4608733.00
Find sum of Sale Price of the cars purchased by the customer having ID C0001 from table
SALE.
COUNT(col) SELECT COUNT(NAME) FROM MANAGER; #3
Display the total number of different types of Models available in table INVENTORY.
SELECT COUNT(DISTINCT Model) FROM INVENTORY; #6
COUNT(*) SELECT COUNT(*) from MANAGER; #4
unoconv_2200894909.docx 2
