SQL
FUNCTION
S
0|Page
NUMERIC FUNCTIONS
TABLE1: ITEM
INSERTING VALUES INTO THE TABLE:-
1|Page
SHOWING TABLE ITEM:
Q1) Write SQL commands to perform the following
operation in the table ITEM.
1. Display Salesman name , bonus after rounding off to
zero decimal places.
2|Page
2. Display remainder of salary and bonus of Salesman
whose SNO starting with ‘A’.
3. Display position of occurrence of string “ta” in
salesmen name.1040.
3|Page
4|Page
DATE AND TIME FUNCTION
Q1) WRITE SQL COMMAND TO PERFORM THE
FOLLOWING OPERATIONS IN THE TABLE EMPLOYEE.
1.Display month name for the date of join of salesman.
2. Display name of the weekday for the DATEOFJOIN of
SALESMAN.
5|Page
3) Display currentdate and day of the year of current
date.
AGGREGATE FUNCTIONS
Table4: STUDENT
INSERTING VALUES INTO THE TABLE:
6|Page
SHOWING TABLE:- STUDENT
Q1) WRITE SQL COMMAND TO PERFORM THE
FOLLOWING OPERATIONS IN THE TABLE STUDENT
1. To display the sum total of all marks.
7|Page
2. To display Average marks of all marks
3. To display the maximum marks
4. To display lowest marks
5. To display count of students with marks (excluding
NULL)
8|Page
6. To count all students( regardless of NULL in marks)
7.To display count (distinct stream)-count of unique
stream
9|Page
SQL CLAUSES
Table5: STUDENT
INSERTING VALUES INTO THE TABLE:
SHOWING TABLE GAMES:
10 | P a g e
Q1) WRITE SQL COMMANDS TO PERFORM THE
FOLLOWING OPERATION IN THE STUDENT TABLE.
1) To display the roll no, name and marks of student on
the basis of their names in ascending order.
2) To display the roll no, name and marks of student in
descending order of their marks and ascending order of
their name.
11 | P a g e
3) To display the stream, and count the total number
of students who have secured more than 90 in their
respective streams.
12 | P a g e
TABLE 6: CLUB
INSERTING VALUES IN THE TABLE:-
SHOWING TABLE ITEM:-
13 | P a g e
Q1) Write SQL commands for the following on the basis
of the given table club.
a) To show all information about the swimming
coaches in the club.
b) To list names of all coaches with their date of
appointment (DATE_OF_APP) in decreasing order.
14 | P a g e
c) To display a report showing coach name,pay,age
and bonus (15% of pay)for all coaches.
d) Give output of the following:
1.Select lcase(sports) from club;
15 | P a g e
2.Select mod(age,5) from club where sex=”f”.
3.Select pow(3,2) from club where sports=”karate”.
Q2) Write the output of the following SQL commands:-
a)Select pow(2,3);
16 | P a g e
b) Select round (123.2345,2), round (342.9234,-1);
c)Select length (“Informatics Practices”);
d) Select year (“1979-11-26”),month(“1979-11-26”);
17 | P a g e
e)Select left (“INDIA”,3), right(“computer science”,4);
f) Select mid (“informatics”,3,4), substr
(“practices”,3);
g) Select concat(“you scored”, length
(“123”),”rank”);
18 | P a g e
h) Select abs(-67.89);
i) Select sqrt(625)+round(1234.89,-3);
j) Select mod(56,8);
19 | P a g e
TABLE 7: STUDENT
INSERTING VALUES IN THE TABLE:
SHOWING THE TABLE:
Q3) Consider the given student table and write output of
the following commands:-
a)To display name like “% i %”.
20 | P a g e
b) To display month name where class=3.
c) To display voc_subj and count them.
d) To display adm_no in order.
21 | P a g e
e) Display the name and age of every student
(age=current year- year of birth)
TABLE 8: FACULTY
22 | P a g e
INSERTING VALUES IN THE TABLE:-
SHOWING THE TABLE:-
Q4) Consider the given table FACULTY and answer the
following questions:-
a)To display details of those faculty members whose
salary is higher than 12000.
23 | P a g e
b) To display details of faculty members whose
salary is in the range of 8000 to 12000.
c)Count number of different IDs from FACULTY.
d) Count the number of faculty members getting
salary as 10000.
e) Display names of faculty members whose
name starts with “s”.
24 | P a g e
f) Display all records in descending order of hire data.
g)Find maximum and minimum salary.
h)SELECT concat (F_Name, L_Name ) from faculty;
25 | P a g e
i)Select Month (Hire_ date) from faculty;
26 | P a g e
27 | P a g e
