Institute Academy

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 o...

SQL INSERT INTO SELECT Statement INSERT 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: INSERT INTO table-name ( column-names ) SELECT column-names FROM table-name WHERE condition SQL INSERT SELECT INTO Example

SQL SELECT INTO Statement SELECT 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-names INTO new-table-name FROM table-name WHERE EXISTS ( SELECT column-name FROM table-name WHERE condition ) The new table will have column names as specified in the query. SQL SELECT INTO Example

SQL WHERE EXISTS Statement WHERE 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-names FROM table-name WHERE EXISTS ( SELECT column-name FROM table-name WHERE condition ) SQL EXISTS Example

SQL WHERE ANY, ALL Clause ANY 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-name WHERE column-name operator ANY ( SELECT column-name FROM table-name WHERE condition ) The general ALL syntax is: SELECT column-names FROM table-name WHERE column-name operator ALL ( SELECT column-name FROM table-name WHERE condition ) SQL ANY Example

SQL Sub-queries A 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 records Sub-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-names FROM table-name1 WHERE value IN ( SELECT column-name FROM 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-names FROM table-name WHERE condition SQL Sub-query Examples

SQL UNION Clause UNION 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-names FROM table-name UNION SELECT column-names FROM table-name SQL UNION Examples

SQL Self JOIN A 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-names FROM 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 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-names FROM table-name1 FULL JOIN table-name2 ON column-name1 = column-name2 WHERE condition The general FULL OUTER JOIN syntax is: SELECT column-names FROM table-name1 FULL OUTER JOIN table-name2 ON column-name1 = column-name2 WHERE condition SQL FULL JOIN Examples

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 RIGHT JOIN table-name2 ON column-name1 = column-name2 WHERE condition The general RIGHT OUTER JOIN syntax is: SELECT column-names FROM table-name1 RIGHT OUTER JOIN table-name2 ON column-name1 = column-name2 WHERE 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 ...

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-names FROM table-name1 LEFT JOIN table-name2 ON column-name1 = column-name2 WHERE condition SQL LEFT JOIN Examples 1) 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 jo...

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-names FROM table-name1 JOIN table-name2 ON column-name1 = column-name2 WHERE condition The general syntax with INNER is: SELECT column-names FROM table-name1 INNER JOIN table-name2 ON column-name1 = column-name2 WHERE condition...

SQL Alias An 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 Syntax SELECT column_name AS alias_name FROM table_name; Alias Table Syntax   SELECT column_name(s) FROM table_name AS alias_name; SQL Alias Examples 1) Example of Alias column    mysql> select name as alias_name from person; +-----------------+ | alias_name      | +-----------------+ | subham ball     | | jit             | | sajal           | | sourav biswas   | | sajal           | | chayan das...

SQL HAVING Clause HAVING 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-names FROM table-name WHERE condition GROUP BY column-names HAVING condition The general syntax with ORDER BY is: SELECT column-names FROM table-name WHERE condition GROUP BY column-names HAVING condition ORDER BY 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) | +-----------------+------------+ | c...

SQL GROUP BY Clause The 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 condition GROUP BY column-names The general syntax with ORDER BY is: SELECT column-names FROM table-name WHERE condition GROUP BY column-name ORDER BY 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 b...

SQL IS NULL Clause NULL 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 IS NULL The general not null syntax is: SELECT column-names FROM table-name WHERE column-name IS NOT NULL 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             | | ...

SQL WHERE LIKE Statement WHERE 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-names FROM 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  ...

SQL WHERE IN Clause WHERE 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-names FROM table-name WHERE 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 WHERE 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-name WHERE 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 mys...

SQL WHERE AND, OR, NOT Clause WHERE 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-names FROM table-name WHERE 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-name WHERE NOT 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           |...