Skip to main content

Posts

Showing posts from March, 2019

How to insert multiple entry or row in SQL

insert multiple row in SQL is very easy. don't worry about it.

mysql> create table orders(orderid varchar(10), customerid varchar(10), orderdate varchar(10));
Query OK, 0 rows affected (0.02 sec)

mysql> desc orders;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| orderid    | varchar(10) | YES  |     | NULL    |       |
| customerid | varchar(10) | YES  |     | NULL    |       |
| orderdate  | varchar(10) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> insert into orders(orderid,customerid,orderdate) values ('10308', '2', '1996-09-18'), ('10309', '37', '1996-09-19'), ('10310', '77', '1996-09-20');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from o…

SQL INSERT INTO SELECT Statement with Example

SQL INSERT INTO SELECT StatementINSERT INTO SELECT copies data from one table to another table.INSERT INTO SELECT requires that data types in source and target tables match The SQL INSERT INTO SELECT syntax The general syntax is:
INSERTINTO table-name (column-names) SELECT column-names FROM table-name WHERE condition
SQL INSERT SELECT INTO Example

SQL SELECT INTO Statement With Exxample

SQL SELECT INTO StatementSELECT INTO copies data from one table into a new table.SELECT INTO creates a new table located in the default filegroup. The SQL SELECT INTO syntax The general syntax is:
SELECT column-namesINTO new-table-nameFROM table-nameWHEREEXISTS(SELECT column-nameFROM table-nameWHERE condition)

The new table will have column names as specified in the query.
SQL SELECT INTO Example

SQL WHERE EXISTS Statement with Example

SQL WHERE EXISTS StatementWHERE EXISTS tests for the existence of any records in a subquery.EXISTS returns true if the subquery returns one or more records.EXISTS is commonly used with correlated subqueries. The SQL EXISTS syntax The general syntax is:
SELECT column-namesFROM table-name WHEREEXISTS(SELECT column-nameFROM table-nameWHERE condition)
SQL EXISTS Example

SQL WHERE ANY, ALL Clause With Example

SQL WHERE ANY, ALL ClauseANY and ALL keywords are used with a WHERE or HAVING clause.ANY and ALL operate on sub-queries that return multiple values.ANY returns true if any of the sub-query values meet the condition.ALL returns true if all of the sub-query values meet the condition. The SQL WHERE ANY and ALL syntax The general ANY syntax is:
SELECT column-names FROM table-nameWHERE column-name operator ANY(SELECT column-nameFROM table-name WHERE condition)

The general ALL syntax is: SELECT column-namesFROM table-nameWHERE column-name operator ALL(SELECT column-nameFROM table-nameWHERE condition) SQL ANY Example

SQL Subqueries With Example

SQL Sub-queriesA sub-query is a SQL query within a query.Sub-queries are nested queries that provide data to the enclosing query.Sub-queries can return individual values or a list of recordsSub-queries m The SQL sub-query syntax There is no general syntax; sub-queries are regular queries placed inside parenthesis.
Sub-queries can be used in different ways and at different locations inside a query:
Here is an sub-query with the IN operator
SELECT column-namesFROM table-name1WHERE value IN(SELECT column-nameFROM table-name2 WHERE condition)
Sub-queries can also assign column values for each record:
SELECT column1 =(SELECT column-name FROM table-name WHERE condition),column-namesFROM table-name WHERE condition
SQL Sub-query Examples

SQL UNION Clause With Example

SQL UNION ClauseUNION combines the result sets of two queries.Column data types in the two queries must match.UNION combines by column position rather than column name

The SQL UNION syntax
The general syntax is:
SELECT column-namesFROM table-nameUNION SELECT column-namesFROM table-name
SQL UNION Examples

SQL Self JOIN or NATURAL JOIN With Example

SQL Self JOINA self JOIN occurs when a table takes a 'selfie'.A self JOIN is a regular join but the table is joined with itself.This can be useful when modeling hierarchies.They are also useful for comparisons within a table.
The SQL Self JOIN syntax
The general syntax is:
SELECT column-namesFROM table-name T1 JOIN table-name T2 WHERE condition


T1 and T2 are different table aliases for the same table  SQL Self JOIN Examples

SQL FULL JOIN Statement with Example

SQL FULL JOIN Statement FULL JOIN returns all matching records from both tables whether the other table matches or not. FULL JOIN can potentially return very large data-sets. FULL JOIN and FULL OUTER JOIN are the same.
The SQL FULL JOIN syntax
The general syntax is:
SELECT column-namesFROM table-name1 FULLJOIN table-name2 ON column-name1 = column-name2 WHERE condition

The general FULL OUTER JOIN syntax is:
SELECT column-namesFROM table-name1 FULLOUTERJOIN table-name2 ON column-name1 = column-name2WHERE condition SQL FULL JOIN Examples

SQL RIGHT JOIN with Example

SQL RIGHT JOIN RIGHT JOIN performs a join starting with the second (right-most) table and then any matching first (left-most) table records. RIGHT JOIN and RIGHT OUTER JOIN are the same.

The SQL RIGHT JOIN syntax The general syntax is:
SELECT column-names FROM table-name1 RIGHTJOIN table-name2 ON column-name1 = column-name2 WHERE condition The general RIGHT OUTER JOIN syntax is: SELECT column-namesFROM table-name1 RIGHTOUTERJOIN table-name2 ON column-name1 = column-name2WHERE condition
SQL RIGHT JOIN Example 1) Example of Right outer join 
mysql> select customers.customername, orders.orderid from customers right join orders on customers.customerid = orders.customerid;
+---------------+---------+
| customername  | orderid |
+---------------+---------+
| Mike Wooksahi | 10308   |
| NULL          | 10309   |
| NULL          | 10310   |
+---------------+---------+
3 rows in set (0.00 sec)

2) Example of Right outer join with order by Clause


mysql>  select customers.customern…

SQL LEFT JOIN with Example

SQL LEFT JOIN LEFT JOIN performs a join starting with the first (left-most) table and then any matching second (right-most) table records. LEFT JOIN and LEFT OUTER JOIN are the same.

The SQL LEFT JOIN syntax
The general syntax is:
SELECT column-namesFROM table-name1 LEFTJOIN table-name2ON column-name1 = column-name2WHERE condition
SQL LEFT JOIN Examples1) Example of Left Outer join

mysql> select customers.customername, orders.orderid from customers left join orders on customers.customerid = orders.customerid;
+---------------+---------+
| customername  | orderid |
+---------------+---------+
| Mike Wooksahi | 10308   |
| Subham Ball   | NULL    |
| jon Wooksahi  | NULL    |
+---------------+---------+
3 rows in set (0.00 sec)

2) Example of Left Outer join with order by Clause


mysql> select customers.customername, orders.orderid from customers left join orders on customers.customerid = orders.customerid order by customers.customername;
+---------------+---------+
| customern…

SQL JOIN with Examples

SQL JOIN A SQL JOIN combines records from two tables.A JOIN locates related column values in the two tables.A query can contain zero, one, or multiple JOIN operations.INNER JOIN is the same as JOIN; the keyword INNER is optional.
Different types of JOINs(INNER) JOIN: Select records that have matching values in both tables.LEFT (OUTER) JOIN: Select records from the first (left-most) table with matching right table records.RIGHT (OUTER) JOIN: Select records from the second (right-most) table with matching left table records.FULL (OUTER) JOIN: Selects all records that match either left or right table records.

The SQL JOIN syntax

The general syntax is: SELECT column-namesFROM table-name1 JOIN table-name2 ON column-name1 = column-name2WHERE condition

The general syntax with INNER is: SELECT column-namesFROM table-name1 INNERJOIN table-name2 ON column-name1 = column-name2 WHERE condition
SQL JOIN ExamplesTable1
mysql> select *from customers; select *from orders;
+------------+-…

SQL Alias With Example

SQL AliasAn Alias is a shorthand for a table or column name.Aliases reduce the amount of typing required to enter a query.Complex queries with aliases are generally easier to read.Aliases are useful with JOINs and aggregates: SUM, COUNT, etc.An Alias only exists for the duration of the query. Alias Column SyntaxSELECT column_name AS alias_name FROM table_name; Alias Table SyntaxSELECT column_name(s) FROM table_name AS alias_name;
SQL Alias Examples1) Example of Alias column  mysql> select name as alias_name from person;
+-----------------+
| alias_name      |
+-----------------+
| subham ball     |
| jit             |
| sajal           |
| sourav biswas   |
| sajal           |
| chayan das      |
| shantanu biswas |
| sudip saha      |
| debo            |
| NULL            |
| NULL            |
| mono            |
| monojit         |
+-----------------+
13 rows in set (0.00 sec)
2) Example of Alias Table 
mysql> select name,age,sex from person as alias_name;
+-----------------…

SQL HAVING Clause with example

SQL HAVING ClauseHAVING filters records that work on summarized GROUP BY results.HAVING applies to summarized group records, whereas WHERE applies to individual records.Only the groups that meet the HAVING criteria will be returned.HAVING requires that a GROUP BY clause is present.WHERE and HAVING can be in the same query. The SQL HAVING syntax
The general syntax is:
SELECT column-namesFROM table-name WHERE conditionGROUPBY column-names HAVING condition


The general syntax with ORDER BY is:
SELECT column-namesFROM table-name WHERE conditionGROUPBY column-names HAVING conditionORDERBY column-names
SQL GROUP BY Examples
1) Example of having Clause  without order by
mysql> select name,count(age) from person group by name having count(age>5);
+-----------------+------------+
| name            | count(age) |
+-----------------+------------+
| chayan das      |          1 |
| jit             |          1 |
| mono            |          1 |
| monojit         |          1 |
| sajal           …

SQL GROUP BY Clause with Example

SQL GROUP BY ClauseThe GROUP BY clause groups records into summary rows.GROUP BY returns one records for each group.GROUP BY typically also involves aggregates: COUNT, MAX, SUM, AVG, etc.GROUP BY can group by one or more columns. The SQL GROUP BY syntax
The general syntax is: SELECT column-name FROM table-name WHERE conditionGROUPBY column-names

The general syntax with ORDER BY is: SELECT column-namesFROM table-nameWHERE condition GROUPBY column-name ORDERBY column-names
SQL GROUP BY Examples 1) Example of Group By Clause
mysql> select name from person where age>20 group by name;
+-----------------+
| name            |
+-----------------+
| chayan das      |
| monojit         |
| sajal           |
| shantanu biswas |
| sourav biswas   |
| subham ball     |
| sudip saha      |
+-----------------+
7 rows in set (0.00 sec)
2) Example of Group By Clause with  ORDER BY
mysql> select name from person where age>20 group by name order by name;
+-----------------+
| name     …

SQL IS NULL Clause with example

SQL IS NULL ClauseNULL is a special value that signifies 'no value'.Comparing a column to NULL using the = operator is undefined.Instead, use WHERE IS NULL or WHERE IS NOT NULL. The SQL WHERE IS NULL syntax The general syntax is: SELECT column-names FROM table-name WHERE column-name ISNULL

The general not null syntax is: SELECT column-namesFROM table-name WHERE column-name ISNOTNULL
SQL WHERE IS NULL Examples 1) Example of is null Clause 
mysql> select name from person where age is null;
+------+
| name |
+------+
| debo |
| NULL |
| NULL |
+------+
3 rows in set (0.00 sec)
2) Example of Is not null
 mysql> select name from person where age is not null;
+-----------------+
| name            |
+-----------------+
| subham ball     |
| jit             |
| sajal           |
| sourav biswas   |
| sajal           |
| chayan das      |
| shantanu biswas |
| sudip saha      |
| mono            |
| monojit         |
+-----------------+
10 rows in set (0.00 sec)

SQL WHERE LIKE Statement with example

SQL WHERE LIKE StatementWHERE LIKE determines if a character string matches a pattern.Use WHERE LIKE when only a fragment of a text value is known.WHERE LIKE supports two wildcard match options: % and  _ . The SQL WHERE LIKE syntax
The general syntax is:
SELECT column-namesFROM table-name WHERE column-name LIKE value

Optional Wildcard characters allowed in 'value' are % (percent) and _ (underscore).
  A % matches any string with zero or more characters.
  An _ matches any single character.
SQL WHERE LIKE Examples 1) any string with zero or more characters(%)
 mysql> select name from person where name like 's%';
+-----------------+
| name            |
+-----------------+
| subham ball     |
| sajal           |
| sourav biswas   |
| sajal           |
| shantanu biswas |
| sudip saha      |
+-----------------+
6 rows in set (0.00 sec)
2) matches any single character(_)
mysql> select name from person where name like 'saja_';
+-------+
| name  |
+-----…

SQL WHERE IN Clause with Example

SQL WHERE IN ClauseWHERE IN returns values that matches values in a list or sub-query.WHERE IN is a shorthand for multiple OR conditions The SQL WHERE IN syntax The general syntax is: SELECT column-namesFROM table-nameWHERE column-name IN(values)
SQL WHERE IN Examples mysql> select name from person where age in(26);
+-----------------+
| name            |
+-----------------+
| shantanu biswas |
| sudip saha      |
+-----------------+
2 rows in set (0.00 sec)

SQL WHERE BETWEEN Clause with Example

SQL WHERE BETWEEN ClauseWHERE BETWEEN returns values that fall within a given range.WHERE BETWEEN is a shorthand for >= AND <=. BETWEEN operator is inclusive: begin and end values are included. The SQL WHERE BETWEEN syntax
The general syntax is:
SELECT column-names FROM table-nameWHERE column-name BETWEEN value1 AND value2
SQL WHERE BETWEEN Examples 1) Example of  Where Between Clause
mysql> select name from person where age between 21 and 25; +---------------+ | name          | +---------------+ | subham ball   | | sajal         | | sourav biswas | | sajal         | | chayan das    | | monojit       | +---------------+ 6 rows in set (0.01 sec)
2) Example of  Where Between Clause with Distinct Clause
mysql> select distinct(name) from person where age between 21 and 25;
+---------------+
| name          |
+---------------+
| subham ball   |
| sajal         |
| sourav biswas |
| chayan das    |
| monojit       |
+---------------+
5 rows in set (0.00 sec)

SQL WHERE AND, OR, NOT Clause with Example

SQL WHERE AND, OR, NOT ClauseWHERE conditions can be combined with AND, OR, and NOT.A WHERE clause with AND requires that two conditions are true.A WHERE clause with OR requires that one of two conditions is true.A WHERE clause with NOT negates the specified condition. The WHERE with AND, OR, NOT syntax
A WHERE clause with AND:
SELECT column-namesFROM table-nameWHERE condition1 AND condition2

A WHERE clause with OR:
UPDATE table-name SET column-name = value WHERE condition1 OR condition2

A WHERE clause with NOT:
DELETE table-nameWHERENOT condition
SQL WHERE with AND, OR, and NOT Examples 1) Example of AND Clause
mysql> select name from person where age>20 and Sex='M';
+-----------------+
| name            |
+-----------------+
| subham ball     |
| sajal           |
| sourav biswas   |
| sajal           |
| chayan das      |
| shantanu biswas |
+-----------------+
6 rows in set (0.00 sec)
2) Example of OR Clause
 mysql> select name from person where age>20…

SQL SELECT COUNT, SUM, AVG Statement with Example

SQL SELECT COUNT, SUM, AVGSELECT COUNT returns a count of the number of data values.SELECT SUM returns the sum of the data values.SELECT AVG returns the average of the data values. The SQL SELECT COUNT, SUM, and AVG syntax
The general COUNT syntax is:
SELECT COUNT(column-name)FROM table-name

The general SUM syntax is:
SELECT SUM(column-name)FROM table-name

The general AVG syntax is:
SELECT AVG(column-name)FROM table-name

SQL SELECT COUNT, SUM, and AVG Examples

1) example of Count Statement
mysql> select count(name) from person;
+-------------+
| count(name) |
+-------------+
|          11 |
+-------------+
1 row in set (0.00 sec)
2) Example of Sum Statement
mysql> select sum(age) from person;
+----------+
| sum(age) |
+----------+
|      226 |
+----------+
1 row in set (0.00 sec)
5) Example of Sum Statement with Distinct Clause
mysql> select sum(distinct age) from person;
+-------------------+
| sum(distinct age) |
+-------------------+
|               179 |
+------------…

SQL SELECT MIN, MAX Statement with Example

SQL SELECT MIN, MAX StatementSELECT MIN returns the minimum value for a column.SELECT MAX returns the maximum value for a column. The SQL SELECT MIN and MAX syntax
The general MIN syntax is:
SELECT MIN(column-name)FROM table-name

The general MAX syntax is:
SELECT MAX(column-name)FROM table-name
SQL SELECT MAX and MIN Examples1) Example of MIN statement
mysql> select min(age) from person;
+----------+
| min(age) |
+----------+
|       18 |
+----------+
1 row in set (0.00 sec)
2) Example of MAX Statement

mysql> select max(age) from person;
+----------+
| max(age) |
+----------+
|       26 |
+----------+
1 row in set (0.00 sec)

SQL SELECT DISTINCT Statement with Example

SQL SELECT DISTINCT StatementSELECT DISTINCT returns only distinct (different) values.SELECT DISTINCT eliminates duplicate records from the results.DISTINCT can be used with aggregates: COUNT, AVG, MAX, etc.DISTINCT operates on a single column. DISTINCT for multiple columns is not supported.
The SQL SELECT DISTINCT syntax
The general syntax is:
SELECTDISTINCT column-nam FROM table-name

Can be used with COUNT and other aggregates
SELECT COUNT (DISTINCT column-name)FROM table-name
SQL SELECT Examples 1) The general syntax
mysql> select distinct name from person;
+-----------------+
| name            |
+-----------------+
| subham ball     |
| jit             |
| sajal           |
| sourav biswas   |
| chayan das      |
| shantanu biswas |
| sudip saha      |
| debo            |
| NULL            |
| mono            |
| monojit         |
+-----------------+
11 rows in set (0.00 sec)
2) with COUNT and other aggregates 
mysql> select count(distinct name) from person;
+---------…

SQL OFFSET-FETCH Clause With Example

SQL OFFSET-FETCH ClauseOFFSET excludes the first set of records.OFFSET can only be used with an ORDER BY clause.OFFSET with FETCH NEXT returns a defined window of records.OFFSET with FETCH NEXT is great for building pagination support.
The SQL ORDER BY OFFSET syntax
The general syntax to exclude first n records is:
SELECT column-namesFROM table-name ORDERBY column-names OFFSET n ROWS

To exclude first n records and return only the next m records:
SELECT column-namesFROM table-nameORDERBY column-names OFFSET n ROWSFETCHNEXT m ROWSONLY

This will return only record (n + 1) to (n + 1 + m). See example below.


The SQL ORDER BY OFFSET Example

SQL SELECT TOP Statement with example

SQL SELECT TOP StatementThe SELECT TOP statement returns a specified number of records.SELECT TOP is useful when working with very large datasets. Non SQL Server databases use keywords like LIMIT, OFFSET, and ROWNUM. The SQL SELECT TOP syntax
The general syntax is: SELECTTOP n column-names FROM table-name
SQL SELECT TOP Example SELECTTOP3 Id, ProductName, UnitPrice, PackageFROM Product ORDERBY UnitPrice DESC

IdProductNameUnitPricePackage38subham Ball263.5012 - 75 cl bottles29Chayan das123.7950 bags x 30 sausgs.9Shantanu Biswas97.0018 - 500 g pkgs.



























SQL ORDER BY Clause With Example

SQL ORDER BY ClauseSELECT returns records in no particular order.To ensure a specific order use the ORDER BY clause.ORDER BY allows sorting by one or more columns.Records can be returned in ascending or descending order.
The SQL ORDER BY syntax The general syntax is: SELECT column-names FROM table-name WHERE conditionORDERBY column-names or
SELECT column-names1, column-name2, ......... FROM table-name WHERE conditionORDERBY column-names
SQL ORDER BY Examples 1) for single column mysql> select  name from person where age>20 order by name;
+-----------------+
| name            |
+-----------------+
| chayan das      |
| monojit         |
| sajal           |
| sajal           |
| shantanu biswas |
| sourav biswas   |
| subham ball     |
| sudip saha      |
+-----------------+
8 rows in set (0.00 sec)
2) for multiple Column
mysql> select  name,age from person where age>20 order by age;
+-----------------+------+
| name            | age  |
+-----------------+------+
| sajal           |   21 |
|…

SQL DELETE Statement with Example

SQL DELETE StatementDELETE permanently removes records from a table.DELETE can delete one or more records in a table.Use the WHERE clause to DELETE only specific records. The SQL DELETE syntax
The general syntax is:


DELETE table-name

To delete specific records append a WHERE clause:
DELETE table-name WHERE condition
SQL DELETE Examples1) Normal Delete or without Where Clause delete mysql> delete from person; Query OK, 4 rows affected (0.01 sec)
mysql> select *from person; Empty set (0.00 sec)
2) Delete With Where Clause
mysql> delete from person where sno='4'; Query OK, 1 row affected (0.01 sec)
mysql> select *from person;
+-----+--------+------+----------------------+------+------------+ | sno | name | age | email | sex | phone | +-----+--------+------+----------------------+------+------------+ | 1 | subham | 25 | NULL | M | NULL | | 2 | nunu | 25 | NULL | M | NULL | | 3 | …

SQL Update statement With Example

SQL UPDATE StatementThe UPDATE statement updates data values in a database.UPDATE can update one or more records in a table.Use the WHERE clause to UPDATE only specific records. The SQL UPDATE syntax The general syntax is: UPDATE table-name SET column-name = value, column-name = value,...


To limit the number of records to UPDATE append a WHERE clause: UPDATE table-name SET column-name = value, column-name = value,...WHERE condition
SQL UPDATE Examples

1) Normal update or without where condition update mysql> update person set age=25; Query OK, 4 rows affected (0.02 sec) Rows matched: 5 Changed: 4 Warnings: 0
mysql> update person set sex='M'; Query OK, 1 row affected (0.02 sec) Rows matched: 5 Changed: 1 Warnings: 0
mysql> select *from person; +-----+--------+------+----------------------+------+------------+ | sno | name | age | email | sex | phone | +-----+--------+------+----------------------+------+------------+ | 1 | subham | 2…