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 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 my

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