Unit-III Database Query using SQL

SQL FUNCTIONS

Math functions: POWER (), ROUND (), MOD (). 

Mathematical functions –Perform operation over numeric value.

POWER() – power() returns the value of a number raised to the power of another number. The synonym of power() is pow(). 

Syntax - pow(m,n)

where 'm' A number which is the base of the exponentiation and 'n' A number which is the exponent of the exponentiation. 

Example :-

Mysql> select pow(2,3); 

Mysql>8 

Mysql> select pow(2.37,3.45); 

Mysql>19.6282……

ROUND() – the round() function returns a number rounded to a certain number of decimal places. 

Syntax - ROUND(column_name,decimals) 

where column_name -Required the field to round. 

decimals -Required, Specifies the number of decimals to be returned.

Example :-

Mysql> select round(454.352,2); 
Mysql>454.35
Mysql> select round(454.352,0); 
Mysql>454

MOD() – The MOD() function returns the remainder of one number divided by another. The following shows the syntax of the MOD() function: 

Syntax - MOD(dividend,divisor) 

Dividend - is a literal number or a numeric expression to divide. 
Divisor- is a literal number or a numeric expression by which to divide the dividend. 

Example:-

Mysql> SELECT MOD(11,3); 
Mysql>2 
Mysql> SELECT MOD(10.5,3); 
Mysql>1.5

Text functions

Text functions- Perform operation over string values. 

UPPER() – UPPER(str) Returns the string str with all characters changed to uppercase. 

mysql> SELECT UPPER(‘india'); 
Mysql> ‘INDIA' 

Note: UCASE(str)-UCASE() is a synonym for UPPER(). 

LOWER(str)-Returns the string str with all characters changed to lowercase 

mysql> SELECT LOWER('JAIPUR'); 
Mysql> 'jaipur’

Note: LCASE(str) LCASE() is a synonym for LOWER()

SUBSTRING

Syntax 1:
SUBSTRING(str,pos) 
The forms without a len argument return a substring from string str starting at position pos. 

mysql> SELECT SUBSTRING(‘practically',5); 
-> 'tically' 

Syntax 2: 
SUBSTRING(str FROM pos), 
The forms with a len argument return a substring len characters long from string str, starting at position pos. 

mysql> SELECT SUBSTRING('informatics ' FROM 4); 

-> ‘ormatics' 


Syntax 3: 
SUBSTRING(str,pos,len)

mysql> SELECT SUBSTRING('Quadratically',5,6); 
-> 'ratica' 


The forms that use FROM are standard SQL syntax. It is also possible to use a negative value for pos. 
In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. 


mysql> SELECT SUBSTRING(‘Ankita', -3); 
-> 'ita' 


mysql> SELECT SUBSTRING(‘Ankita', -5, 3); 
-> 'nki'

LENGTH(str) - Returns the length of the string str 

mysql> SELECT LENGTH('text'); 
-> 4 

LEFT(str,len) - Returns the leftmost len characters from the string str, or NULL if any argument is NULL. 

mysql> SELECT LEFT(‘Toolbar',4); 
-> ‘Tool‘ 

RIGHT(str,len)-Returns the rightmost len characters from the string str, or NULL if any argument is NULL. 

mysql> SELECT RIGHT(‘Toolbar',3); 
-> 'bar'

INSTR(str,substr)-Returns the position of the first occurrence of substring substr in string str. 

mysql> SELECT INSTR(‘Toobarbar','bar'); 
-> 4
mysql> SELECT INSTR('xbar',‘ybar'); 
-> 0

LTRIM(str)-Returns the string str with leading space characters removed. 

mysql> SELECT LTRIM(' Toolbar'); 
-> ‘Toolbar‘ 

RTRIM(str)-Returns the string str with trailing space characters removed. 

mysql> SELECT RTRIM(‘Toolbar '); 
-> ‘Toolbar‘ 

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)- Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given , BOTH is assumed. 

mysql> SELECT TRIM(' tool '); 
-> 'tool' 
mysql> SELECT TRIM(LEADING 'x' FROM 'xxtoolxx'); 
-> ‘toolxx' 
mysql> SELECT TRIM(BOTH 'x' FROM 'xxtoolxx'); 
-> ‘tool' 
mysql> SELECT TRIM(TRAILING 'x' FROM ‘toolxx'); 
-> ‘tool'

Date functions

Date functions- Perform operation over date values. 

NOW()-Returns the current date and time as a value in 'YYYY-MM-DD hh:mm:ss' or YYYYMMDDhhmmss format, depending on whether the function is used in string or numeric context. 
mysql> SELECT NOW(); 
-> '2020-04-05 23:50:26' 
mysql> SELECT NOW() + 0; 
-> 20200415235026.000000 
Here +0 means +0 second 

DATE(expr)-Extracts the date part of the date or datetime expression expr. 

mysql> SELECT DATE('2003-12-31 01:02:03'); 
-> '2003-12-31'

MONTH(date)-Returns the month for date, in the range 1 to 12 for January to December, or 0 for dates such as '0000-00-00' or '2008-00-00' that have a zero month part. 

mysql> SELECT MONTH('2008-02-03'); 
-> 2 

MONTHNAME (date)-Returns the full name of the month for date. 

mysql> SELECT MONTHNAME('2008-02-03'); 
-> 'February‘ 

YEAR(date)-Returns the year for date, in the range 1000 to 9999, or 0 for the “zero” date. 

mysql> SELECT YEAR('1987-01-01'); 
-> 1987 

DAY(date)-Returns the day of the month for date, in the range 1 to 31, or 0 for dates such as '0000-00-00' or '2008-00-00' that have a zero day part. 

mysql> SELECT DAYOFMONTH('2007-02-03'); 
-> 3 

DAYNAME(date)-Returns the name of the weekday for date. 

mysql> SELECT DAYNAME('2007-02-03'); 
-> 'Saturday'

Aggregate Functions

Aggregate Functions & NULL- Perform operation over set of values. 

Sum()
Min()
Max()
AVG()
Count()

Consider a table Emp having following records as Null values are excluded while (avg)aggregate function is used .
              
              Table EMP
Emp Code         Name      Sal 
E1               Mohak     NULL 
E2               Anuj      4500 
E3               Vijay     NULL 
E4               Vishal    3500 
E5               Anil      4000 

SQL Queries 
mysql> Select Sum(Sal) from EMP; 
-> 12000
mysql> Select Min(Sal) from EMP; 
->3500
mysql> Select Max(Sal) from EMP;
-> 4500 
mysql> Select Count(Sal) from EMP; 
-> 3
mysql> Select Avg(Sal) from EMP;
-> 4000 
mysql> Select Count(*) from EMP;
-> 5 

Querying and manipulating data 

using Group by, Having, Order by clause

Group by: 

Group by clause is used to divide the table into logical groups and we can perform aggregate functions in those groups. in this case aggregate function will return output for each group. 

Consider the following table student1

mysql>select sum(marks) from student1 group by age;

-> 195

   284

HAVING Clause

if we want to filter or restrict some rows from the output produced by GROUP BY then we use HAVING clause. it is used to put condition of group of rows. with HAVING clause we can use aggregate functions also.

WHERE clause is used before GROUP BY . with WHERE  we cannot use aggregate functions.

select max(marks) from student1 group by age having count(*)>1;

-> Max(marks)

   98

   98

ORDER BY 

by default records will come in the output in the same order in which it was entered. To see the output rows in sorted or arragned in ascending or descending order SQL provide order by clause. by default output will be ascending order(ASC) to see output in descending order we use DESC clause with order by.

select * from student1 order by name;

select * from student1 order by name  desc;

Assignment

Quiz1

Previous Years CBSE Board Examination Questions

2 Marks Questions

Question 1.

Write the output of the following SQL queries:
(i) SELECT RIGHT (‘software’, 2);
(ii) SELECT INSTR (‘twelve’, 'l');
(iii) SELECT DAYOFMONTH (‘2014-03-01’);
(iv) SELECT ROUND  (76.987,2); 

Answer:

(i) re
(ii) 4
(iii) 01
(iv) 76.99

Question 2.

There is a column Salary in a Table EMPLOYEE. The following two statements are giving different outputs. What may be the possible reason? 

SELECT COUNT(*) FROM EMPLOYEE;
SELECT C0UNT(Salary) FROM EMPLOYEE;

Answer:

SELECT COUNT (*) FROM EMPLOYEE:

This statement returns the number of records in the table.

SELECT COUNT(Salary) FROM EMPLOYEE;

This statement returns the number of values (NULL values will not be counted) of the specified column.

Question 3.

A table FLIGHT has 4 rows and 2 columns and another table AIRHOSTESS has 3 rows and 4 columns. How many rows and columns will be there if we obtain the cartesian product of these two tables? 

Answer:

Total number of rows will be 12 and total number of columns 6.

Question 4. 

What is the purpose of GROUP BY clause in MySQL? How is it different from ORDER BY clause? 

Answer:

The GROUP BY clause can be used to combine all those records that have identical value in a particular field or a group of fields. Whereas, ORDER BY clause is used to display the records either in ascending or descending order based on a particular field. For ascending order ASC is used and for descending order, DESC is used. The default order is ascending order.

Question 5.  

Shanya Khanna is using a table EMPLOYEE. It has the following columns:

Admno, Name, Agg, Stream [column Agg contains Aggregate marks]

She wants to display highest Agg obtained in each Stream.
She wrote the following statement:

SELECT Stream, MAX(Agg) FROM EMPLOYEE;

But she did not get the desired result. Rewrite the above query with necessary changes to help her get the desired output.

Answer:

SELECT Stream, MAX(Agg)
FROM EMPLOYEE
GROUP BY Stream;

Question 6.
State difference between date functions NOW() and SYSDATE() of MySql.
Answer:
Differences between Now() and SYSDATE() of MySql are as follows:

7 Marks Questions

Question 7.
Consider the following table named SBOP with details of account holders. Write commands of MySql for (i) to (iv) and output for (v) to (vii).

TABLE SBOP

(i) To display Accountno, Name and DateOfopen of account holders having transactions more than 8.
(ii) To display all information of account holders whose transaction value is not mentioned.
(iii) To add another column Address with datatype and size as VARCHAR(25).
(iv) To display the month day with reference to DateOfopen for all the account holders. 

(v) SELECT COUNT (*) FROM SBOP;
(vi) SELECT Name, Balance FROM SBOP WHERE Name LIKE “%i”;
(vii) SELECT ROUND (Balance,-3) FROM SBOP
       WHERE Accountno="SB-5” ;


Answer:
(i) SELECT Account no, Name, DateOfopen FROM SBOP WHERE Transaction > 8:
(ii) SELECT * FROM SBOP WHERE Transaction IS NULL;
(iii) ALTER TABLE SBOP ADD Address VARCHAR(25);
(iv) SELECT DAY0FM0NTH(DateOfopen), Name FROM SBOP:




Question 8.
Consider the following table named EXAM with details of marks. Write command of MySQL for (i) to (iv) and output for (v) to (vii).
Table EXAM

(i) To display all information of the students of humanities in descending order of percentage.
(ii) To display Adno, Name, Percentage and Stream of those students whose name is less than 6 characters long.
(iii) To add another column Bus_Fees with datatype and size as Decimal(8,2).
(iv) To increase percentage by 2% of all the Humanities students. (All India 2014)
(v) SELECT COUNT(*) FROM EXAM;
(vi) SELECT SName, Percentage FROM EXAM WHERE Name LIKE “N%”;
(vii) SELECT ROUND (Percentage ,0) FROM EXAM WHERE Adno=“R005”;



Answer:
(i) SELECT * FROM EXAM WHERE Stream = ‘Humanities’
    ORDER BY Percentage DESC;
(ii) SELECT Adno, SName, Percentage, Stream FROM EXAM
     WHERE LENGTH(SName)<6;
(iii) ALTER TABLE EXAM
      ADD Bus_Fees DECIMALS, 2) ;
(iv) UPDATE EXAM
     SET Percentage = Percentage + 2 
      WHERE Stream = ‘Humanities’;

(vi) The given query will result in an error as there is no column named         Name in table EXAM.



Question 9.
Consider the table SUPPLIER given below. Write commands in MySQL for (i) to (iv) and output for (v) to (vii). (i)

TABLE SUPPLIER

(iii) To count distinct City in the table.
(iv) To insert a new row in the table SUPPLIER.
       110, “Bournvita”,’ ABC’, 170, ‘Delhi’, 40.00 (Delhi 2012)

(v) SELECT Pname FROM SUPPLIER WHERE Supname IN ('Bread', 'Maggt');
(vi) SELECT COUNTCDISTINCT City) FROM SUPPLIER;
(vii) SELECT MAX(Price) FROM SUPPLIER WHERE City = ‘Kol kata’;



Answer:
(i) SELECT Pname FROM SUPPLIER WHERE Pname LIKE ‘B%’ ORDER BY Price;
(ii) SELECT Scode, Pname, City  FROM SUPPLIER WHERE Qty < 150;
(iii) SELECT COUNT(DISTINCT City) FROM SUPPLIER;
(iv) INSERT INTO SUPPLIER VALUES      (110,‘Bournvita’,‘ABC’,170,‘Delhi’,40.00);


Question 10.
Consider the table PERSONS given below. Write commands in SQL for (i) to (iv) and write output for (v) to (viii).

TABLE PERSONS

(i) Display the SurName, FirstName and City of people residing in Udhamwara city.
(ii) Display the Person IDs (PID), Cities and Pincode of persons in descending order of Pincode.
(iii) Display the FirstName and City of all the females getting Basic salaries above 40000.
(iv) Display FirstName and BasicSalaries of all the persons whose first name start with ‘G’. 

(v) SELECT SurName FROM PERSONS WHERE BasicSalary>= 50000;
(vi) SELECT SUM (BasicSalary) FROM PERSONS WHERE Gender = ‘F’;
(vii) SELECT Gender, MIN (BasicSalary) FROM PERSONS GROUP BY Gender;
(viii) SELECT Gender, COUNT (*) FROM PERSONS GROUP BY Gender;



Answer:
(i) SELECT SurName, FirstName, City FROM PERSONS WHERE City = ‘Udhamwara’;
(ii) SELECT PID, City. PinCode FROM PERSONS ORDER BY Pincode DESC;
(iii) SELECT FirstName, City FROM PERSONS WHERE Gender = ‘F’ AND
       BasicSalary > 40000;
(iv) SELECT FirstName, BasicSalary FROM PERSONS WHERE FirstName LIKE ‘G%’;