SQL – Check, Default and Index Constraints
It use as a condition and check the value of columns.
CREATE TABLE employee ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CONSTRAINT CHK_Employee CHECK (Age>=18) );
It used to provide default value for a column.
Running on My SQL / SQL Server / Oracle / MS Access:
CREATE TABLE employee ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, Address varchar(100) DEFAULT ‘Etah’ );
Used to create indexes in tables
It used for speed up data.
CREATE INDEX index_name ON table_name;
SQL – Aggregate function
In this section we will learn about aggregate function.
There are different types of aggregate function which is used in SQL
AVG, COUNT, MAX, MIN, SUM, LTRIM, RTRIM, POWER, ROUND, etc.
Returns the average value of ‘n’
Table structure with data
CREATE TABLE `employee` ( `id` int(11) NOT NULL AUTO_INCREMENT, `firstname` varchar(50) DEFAULT NULL, `lastname` varchar(50) DEFAULT NULL, `salary` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
/*Data for the table `employee` */ insert into `employee`(`id`,` firstname `, ‘lastname’, `salary`) values (1,'Naveen', ‘kumar’,15000),(2,’jitendra’,’singh’,16000),(3,'sachin',‘pandit’,15000),(4,'adarsh',’kumar’,18000); SELECT AVG(salary) FROM employee; SELECT count(salary) FROM employee; SELECT max(salary) FROM employee; SELECT min(salary) FROM employee; SELECT sum(salary) FROM employee;
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards often used in conjunction with the LIKE operator:
% - The percent sign represents zero, one, or multiple characters
_ - The underscore represents a single character
· LIKE 'a% - Finds any values that start with "a"
· LIKE '%a' - Finds any values that start with "a"
· LIKE '%or%' - Finds any values that have "or" in any position
· Finds any values that have "r" in the second position
· Finds any values that start with "a" and are at least 3 characters in length
· Finds any values that start with "a" and ends with "o"
WHERE SALARY LIKE '50%' WHERE SALARY LIKE '%100%' WHERE SALARY LIKE '_00%' WHERE SALARY LIKE '5_%_%' : Finds any values that start with 2 and are at least 3 characters in length. WHERE SALARY LIKE '%3' WHERE SALARY LIKE '_1%2' - Finds any values that have a 2 in the second position and end with a 3. WHERE SALARY LIKE '1___3' : Finds any values in a five-digit number that start with 2 and end with 3.
SQL Logic Operator
Logic Operator that can be used in SQL –
· AND Operator
· OR Operator
· NOT Operator
Select columnname1, columnname2…column3 from tablename Where columnname1..columnnameN > 50 AND columnname1..columnnameN < 100 ;
Select salary from employee where salary >25000 AND salary<30000;
As you can use any remaining OR and NOT operator.
SQL Range Serching – Between, IN and NOT IN
In order to select data within the range of values, the BETWEEN opera