Chapter 10 : SQL Functions

Functions

A function is a predefined command set that performs some operation and returns the single
value.
Numeric Functions

POWER() : Returns the argument raised to the specified power. POW () works the same way.
Example:
(i)POW(2,4): Result:16
(ii)POW(2,- 2): Result: 0.25
(iii)POW(-2,3): Result: -8
ROUND() : ROUND(X) Rounds the argument to the zero decimal place, Where as ROUND(X,d)
rounds the argument to d decimal places.
Example :
(i) ROUND(-1.23); Result: -1
(ii) ROUND(-1.58); Result: -2
(iii) ROUND(1.58); Result: 2
(iv) ROUND(3.798, 1); Result: 3.8
(v) ROUND(1.298, 0); Result: 1
(vi) ROUND(23.298, -1); Result: 20
(vii) ROUND( 25.298,-1); result: 30
TRUNCATE() : Truncates the argument to specified number of decimal places.
Example:
(i) TRUNCATE (7.29,1) Result: 7.2
(ii) TRUNCATE(27.29,-1) Result: 20
SIGN() : Returns sign of a given number.
Example :
(i) SIGN (15) Result : 1 :
(ii) SIGN (-15) Result : -1 :
(iii) SIGN (0) Result : 0.
SQRT : Returns the square root of given number.
Example :
(i) SQRT (25) Result : 5

What is SQL?
Ans. SQL is Non-procedural universal data access language used to access and manipulate data
stored in nearly all the data bases available currently. SQL standards are defined by ANSI
(American National Standards Institute). SQL statements are used to retrieve and update data in a
database. SQL works with database programs like MySQL, MS Access, DB2, Informix, MS SQL
Server, Oracle, Sybase, etc.
Differentiate between DDL and DML?
Ans. Data Definition Language (DDL): This is a category of SQL commands. All the commands
which are used to create, destroy, or restructure databases and tables come under this category.
Examples of DDL commands are - CREATE, DROP, ALTER. Data Manipulation Language (DML): This
is a category of SQL commands. All the commands which are used to manipulate data within
tables come under this category. Examples of DML commands are - INSERT, UPDATE, DELETE.
What is a constraint?
Ans. : A constraints is a condition or check application on a field or set of fields. Example: NOT
NULL (ensure that column con not have null value), CHECK (make sure that all value satisfy
certain criteria), UNIQUE (ensure that all values in a column are different) etc.
What are single row functions ?
Ans. : Single Row Function work with a single row at a time. A single row function returns a result
for every row of a quired table Examples of Single row functions are Sqrt(), Concat(), Lcase(),
Upper(), Day(), etc. Compare CHAR and VARCHAR data types.
Ans. The CHAR data-type stores fixed length strings such that strings having length smaller than
the field size are padded on the right with spaces before being stored. The VARCHAR on the other
hand supports variable length strings and therefore stores strings smaller than the field size
without modification.
What are the differences between DELETE and DROP commands of SQL?
Ans. : DELETE is DML command while DROP is a DDL command. Delete is used to delete rows
from a table while DROP is used to remove the entire table from the database.
What do you understand by MySQL Client?
Ans. : MySQL Clients are programs that connect to MySQL Server and issue queries in predefined
format. Explain with the help of an example that why should a transaction be executed as a whole
or it should be not executed at all.
Ans. : Suppose Raunak's account number is 3246 and his aunt's account number is 5135. In order
to process the cheque presented by Raunak, the following two SQL commands need to be
executed on the database maintained by the bank:
UPDATE Savings SET balance = balance - 2000
WHERE account_no = 5135;
UPDATE Savings SET balance = balance + 2000
WHERE account_no = 3246;
Query Based question & answers
1. The Pincode column of table 'Post' is given below-

Ans. : SELECT Pincode from Post where Pincode LIKE " %1" ;
i. SELECT Pincode from Post where Pincode LIKE " 0%" ; i) 110001 ii) No Output
2. A table "Animals" in a database has 3 columns and 10 records. What is the degree and rdinality
of this table?
Ans. : Degree 3 and Cardinality=10 3. Answer the question based on the table VOTER given
below:
(i) Write the command to delete all the rows of particular voter from the table voter where voter ID
between 10 and 20.
Ans. : Delete from VOTER where V_id between 10 and 20; (ii) Delete the table physically.
4. Write MySql command to create a furniture table including all constraint.
Ans. : Drop table VOTER;

CREATE TABLE FURNITURE ( ITEMNO INT(5) PRIMARY KEY,

ITEMNAME VARCHAR(20) NOT NULL, TYPE VARCHAR(20),
DATE_STOCK DATE DEFAULT '2012/03/19', PRICE INT(6), DISCOUNT INT(2) );
5. Consider a database LO
Ans. with the following table:

Answer the following questions. Create Database and use it

1. Create the database LO
Ans.
Mysql> Create Database LO
Ans. ;
2. Use the database LO
Ans. .
Mysql> Use LO
Ans. ; Create Table / Insert Into
3. Create the table Loan_Accounts and insert tuples in it.

Mysql>Create table Loan_Acc

(AccNo int primary key, Cust_Name varchar(30),
Loan_Amount int, Installment int, Int_Rate number(5,3),
Start_Date date, Interest number(7,2));

Mysql> Insert into Loan_Acc values(1,'R.K. GUPTA',300000,36,12.0.'2009-07-19');

Simple Select
4. Display the details of all the lo
Ans. .
Mysql> Select * from Loan_Acc;
5. Display the AccNo, Cust_Name, and Loan_Amount of all the lo
Ans. .
Mysql> Select Acc_No,Cust_Name,Loan_Amount from Loan_Acc;
Conditional Select using Where Clause
6 Display the details of all the lo
Ans. with less than 40 instalments.

Mysql> Select * from Loan_Acc where Instalment <40;

7. Display the AccNo and Loan_Amount of all the lo
Ans. started before 01-04-2009.
Mysql> Select AccNo, Loan_Amount from Loan_Acc where Start_Date <'2009-04-01'; 8.
8. Display the Int_Rate of all the lo
Ans. started after 01-04-2009.

Mysql> Select Int_Rate from Loan_Acc where Start_date>'2009-04-01'; Using NULL

9. Display the details of all the lo
Ans. whose rate of interest is NULL.

Mysql> Select * from Loan_Acc where Int_rate is NULL;

10.Display the details of all the lo
Ans. whose rate of interest is not NULL.

Mysql> Select * from Loan_Acc where Int_rate is not NULL;

Using DISTINCT Clause
11. Display the amounts of various lo
Ans. from the table Loan_Accounts. A loan amount should appear only once.

Mysql> Select DISTINCT Loan_Amount from Loan_Acc;

12. Display the number of instalments of various lo
Ans. from the table Loan_Accounts. An instalment should appear only once.

Mysql> Select DISTINCT Instalment from Loan_Acc;

Using Logical Operators (NOT, AND, OR)
13. Display the details of all the lo
Ans. started after 31-12-2008 for which the number of instalments are more than 36.

Mysql> Select * from Loan_Acc where Start_Date>'2008-12-31' and Instalment>36;

14. Display the Cust_Name and Loan_Amount for all the lo
Ans. which do not have number of instalments 36.

Mysql> Select Cust_Name, Loan_Amount from Loan_Acc where Instalment <>36;

15. Display the Cust_Name and Loan_Amount for all the lo
Ans. for which the loan amount is less than 500000 or int_rate is more than 12.

Mysql> Select Cust_Name, Loan_Amount from Loan_Acc where Loan_Amount <500000 or

Int_rate>12;
16. Display the details of all the lo
Ans. which started in the year 2009.
Mysql> Select * from Loan_Acc where Year(Start_Date)=2009;
17. Display the details of all the lo
Ans. whose Loan_Amount is in the range 400000 to 500000.
Mysql> Select * from Loan_Acc where Loan_Amount between 400000 and 50000;
18. Display the details of all the lo
Ans. whose rate of interest is in the range 11% to 12%.
Mysql> Select * from Loan_Acc where Int_Rate between 11 and 12; Using IN Operator
19. Display the Cust_Name and Loan_Amount for all the lo
Ans. for which the number of instalments are 24, 36, or 48 (Using IN operator)
Mysql> Select Cust_Name, Loan_Amount from Loan_Acc where Instalment IN(24,36,48); UR
Using LIKE Operator
20. Display the AccNo, Cust_Name, and Loan_Amount for all the lo
Ans. for which the Cust_Name ends with 'Sharma'.
Mysql> Select AccNo, Cust_name from Loan_Acc where Cust_Name like'%Sharma';
21. Display the AccNo, Cust_Name, and Loan_Amount for all the lo
Ans. for which the Cust_Name ends with 'a'.
Mysql> Select AccNo, Cust_name,Loan_Amount from Loan_Acc where Cust_Name like '%a';
22. Display the AccNo, Cust_Name, and Loan_Amount for all the lo
Ans. for which the Cust_Name contains 'a'
Mysql> Select AccNo, Cust_name,Loan_Amount from Loan_Acc where Cust_Name like'%a%';
Using ORDER BY clause
23. Display the details of all the lo
Ans. in the ascending order of their Loan_Amount.
Mysql> Select * from Loan_Acc ORDER BY Loan_Amount;
28. Display the details of all the lo
Ans. in the descending order of their Start_Date.
Mysql> Select * from Loan_Acc ORDER BY Start_date DESC;
Using UPDATE, DELETE, ALTER TABLE

29. Put the interest rate 11.50% for all the lo

Ans. for which interest rate is NULL.

Mysql> Update Loan_Acc SET Int_Rate =11.50 Where Int_Rate IS NULL:

30. Delete the records of all the lo
Ans. of 'K.P. Jain'
Mysql> Delete From Loan_Acc Where Cust_Name='K.P.Jain';
31. Add another column Category of type CHAR(1) in the Loan table.
Mysql> Alter Table Loan_Acc ADD (Category CHAR(1) );
SQL PRACTICAL ASSIGNMENT
Lab Activity 1: Create a table STUDENT with under mentioned structure by using SQL Statement:
Step 1: Open MySQL, Open Database and create table as:
CREATE TABLE Student ( StdID INT(4) PRIMARY KEY, StdName VARCHAR(30) NOT NULL,
Sex VARCHAR(1), Percentage DECIMAL(5,2), SClass INT ,
Sec VARCHAR(1), Stream VARCHAR(10), DOB DATE );
Step 2: Press Enter key to complete create table:
Step 3: Insert records into STUDENT table.
INSERT INTO Student VALUES (1001, ‘AKSHRA AGARWAL,'FEMALE',70,11,’A’, ‘10/11/1996’);
Step 4: As you press enter key after typing above statement, 1 record will be stored into
STUDENT table.
Step 5: Similarly like step 3, enter other records of the following table.

Lab Activity 2: Open school database, then select student table and use following SQL
statements.
TYPE THE STATEMENT, PRESS ENTER AND NOTE THE OUTPUT
1.To display all the records form STUDENT table.
SELECT * FROM student ;
2. To display ony name and date of birth from the table STUDENT.
SELECT StdName, DOB FROM student ;\
3. To display all students record where percentage is greater of equal to 80 FROM student table.
SELECT * FROM student WHERE percentage >= 80;
4. To display student name, stream and percentage where percentage of student is more than
80SELECT StdName, Stream, Percentage WHERE percentage > 80;
5. To display all records of science students whose percentage is more than 75 form student table.
SELECT * FORM student WHERE stream = ‘Science’ AND percentage > 75;
Lab Activity 3: Open school database, then select student table and use following SQL
statements
 TYPE THE STATEMENT, PRESS ENTER AND NOTE THE OUTPUT
1. To display the STUDENT table structure.
DESCRIBE Student;
2. To add a column (FIELD) in the STUDENT table, for example TeacherID as VARCHAR(20);
ALTER TABLE Student ADD TeacherID VARCHAR(20);
3. Type the statement
DESC Student;
Press enter key, now note the difference in table structure.
4. Type the statement and press enter key, note the new field that you have added as TeacherID
SELECT * FROM student;
5. To modify the TeacherID data type form character to integer.
ALTER TABLE Student MODIFY TeacherID INTEGER ;
DESC Student;
SELECT * FROM student;
Lab Activity 4
1. To Drop (Delete) a field form a table. For e.g you want to delete TeacherID field.
ALTER TABLE Student DROP Teacher ID;
2. To subtract 5 form all students percentage and display name and percentage.
SELECT name, percentage - 5 FROM Student;
3. Using column alise for example we want to display StdName as Student Name and DOB as Date
of Birth then the statement will be.
SELECT StdName AS "Student Name",
DOB As “Date of Birth” FROM Student;
4. Display the name of all students whose stream is not Science
SELECT StdName FROM student
WHERE Stream <> ‘Science’;
5. Display all name and percentage where percentage is between 60 and 80
SELECT StdName, percentage FROM student WHERE percentage >=60 AND
percentage<=80 ;
Lab Activity 5:
1. To change a student name from SWATI MISHRA to SWATI VERMA whose StdID is 1014 andalso
change percentage 86.
UPDATE Student SET StdName = ‘SWATI VERMA’, percentage = 86 WHERE StdId = 1014;
2. To delete the records form student table where StdId is 1016.
DELETE FROM Student WHERE StdID = 1016;
3. Type the following SQL statement and note the output.
SELECT * FROM Student WHERE StdName LIKE 'G_' ;
SELECT * FROM Student WHERE StdName='G';
SELECT * FROM Student WHERE StdName LIKE 'G%' ;
SELECT * WHERE Student WHERE StdName='%G%' ;
4. Display all the streams in student table.

SELECT DISTINCT Stream FROM Student;

5. Note the output of the following statement.
SELECT StdName, Sex, Stream FROM Student WHERE percentage BETWEEN 70 AND 80;
1. 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 employs with the text “Not given”, if commission is null?