Crash Course in SQL : Important Functions in SQL

First we have to create a database.

create database phones;

Now we will create a table and with values in this:

DROP TABLE IF EXISTS `mobile`;

CREATE TABLE `mobile` (
  `company` varchar(10) DEFAULT NULL,
  `model` varchar(10) DEFAULT NULL,
  `os` varchar(10) DEFAULT NULL,
  `price` int(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `mobile` WRITE;
INSERT INTO `mobile` VALUES ('nokia','lumia710','wp8',15000),('nokia','lumia920','wp8',35000),('apple','iphone4','ios',40000),('samsung','s3','android',40000),('samsung','s4','android',40000);
UNLOCK TABLES;


Now just to be sure, we will see the data:

use phones;
select * from mobile ;













And now you can see the data. Now lets move to SQL functions:

SELECT AVG(price) FROM mobile;
Ans: '34000.0000'
Explanation: Calculates average of a coloum.

SELECT COUNT(os) FROM mobile;
Ans:5
Explanation: No of records in a coloum

SELECT COUNT(DISTINCT os) FROM mobile; 
Ans:3
Explanation: No of distinct column

SELECT model FROM mobile order by model LIMIT 1;
Ans:iphone4
Explanation: First Value

SELECT model FROM mobile ORDER BY model DESC LIMIT 1; 
Ans:s4
Explanation: Last Value

SELECT MAX(price) FROM mobile;
Ans:40000
Explanation: Maximum Value

SELECT MIN(price) FROM mobile;
Ans:15000
Explanation: Minimum Value

SELECT SUM(price) FROM mobile;
Ans:170000
Explanation: Sum of all values

SELECT LENGTH(os) FROM mobile where model='s3';
Ans:7
Explanation: Length of record

SELECT NOW() FROM mobile where model='s3';
Ans:'2013-09-16 00:39'
Explanation: Returns the current time

SELECT reverse(model) from mobile where company='apple';
Ans:enohpi 
Explanation: Reverses the string








 

Comments