mysql> select * from emp001;
+----+------+----------+--------+
| id | name | dept     | salary |
+----+------+----------+--------+
|  1 | A    | Math     |    100 |
|  2 | B    | Science  |    200 |
|  3 | C    | Computer |    300 |
|  4 | C    | Math     |    100 |
|  5 | D    | Science  |    200 |
|  6 | E    | Computer |    300 |
+----+------+----------+--------+
6 rows in set (0.00 sec)

mysql> select dept, salary from emp001 where salary >(select AVG(salary) from emp001) group by dept;
+----------+--------+
| dept     | salary |
+----------+--------+
| Computer |    300 |
+----------+--------+
1 row in set (0.00 sec)




mysql> create table emp(id int, name varchar(255), state varchar(255));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into emp(id, name, state)values(1, 'a', 'UP');
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp(id, name, state)values(2, 'b', 'MP');
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp(id, name, state)values(3, 'c', 'UP');
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp(id, name, state)values(4, 'd', 'MP');
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp(id, name, state)values(5, 'e', 'KA');
Query OK, 1 row affected (0.00 sec)

mysql> select * from emp;
+------+------+-------+
| id   | name | state |
+------+------+-------+
|    1 | a    | UP    |
|    2 | b    | MP    |
|    3 | c    | UP    |
|    4 | d    | MP    |
|    5 | e    | KA    |
+------+------+-------+
5 rows in set (0.01 sec)

mysql> select * from emp group by state;
+------+------+-------+
| id   | name | state |
+------+------+-------+
|    5 | e    | KA    |
|    2 | b    | MP    |
|    1 | a    | UP    |
+------+------+-------+
3 rows in set (0.00 sec)

mysql> select name, count(*) from emp group by state;
+------+----------+
| name | count(*) |
+------+----------+
| e    |        1 |
| b    |        2 |
| a    |        2 |
+------+----------+
3 rows in set (0.00 sec)

mysql> select name, state ,count(*) from emp group by state;
+------+-------+----------+
| name | state | count(*) |
+------+-------+----------+
| e    | KA    |        1 |
| b    | MP    |        2 |
| a    | UP    |        2 |
+------+-------+----------+
3 rows in set (0.00 sec)


mysql> create table trans(id int, emp_id int, amount int);
Query OK, 0 rows affected (0.03 sec)

mysql> select * from emp;
+------+------+-------+
| id   | name | state |
+------+------+-------+
|    1 | a    | UP    |
|    2 | b    | MP    |
|    3 | c    | UP    |
|    4 | d    | MP    |
|    5 | e    | KA    |
+------+------+-------+
5 rows in set (0.00 sec)

mysql> insert into trans(id, emp_id, amount)values(1, 1, 100)
    -> ;
Query OK, 1 row affected (0.01 sec)

mysql> insert into trans(id, emp_id, amount)values(2, 1, 200);
Query OK, 1 row affected (0.00 sec)

mysql> insert into trans(id, emp_id, amount)values(3, 1, 300);
Query OK, 1 row affected (0.00 sec)

mysql> insert into trans(id, emp_id, amount)values(4, 5, 500);
Query OK, 1 row affected (0.00 sec)

mysql> select * from trans;
+------+--------+--------+
| id   | emp_id | amount |
+------+--------+--------+
|    1 |      1 |    100 |
|    2 |      1 |    200 |
|    3 |      1 |    300 |
|    4 |      5 |    500 |
+------+--------+--------+
4 rows in set (0.00 sec)

mysql> select emp_id, count(*) from trans group by emp_id;
+--------+----------+
| emp_id | count(*) |
+--------+----------+
|      1 |        3 |
|      5 |        1 |
+--------+----------+
2 rows in set (0.00 sec)

mysql> select emp_id, name, count(*) from trans, emp where emp.id=trans.emp_id group by emp_id;
+--------+------+----------+
| emp_id | name | count(*) |
+--------+------+----------+
|      1 | a    |        3 |
|      5 | e    |        1 |
+--------+------+----------+
2 rows in set (0.00 sec)



# Cartision product
mysql> select * from emp;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from trans;
+----+--------+--------+
| id | emp_id | amount |
+----+--------+--------+
|  1 |      1 |   10.5 |
|  2 |      2 |     16 |
+----+--------+--------+
2 rows in set (0.00 sec)

mysql> select * from emp, trans;
+----+------+----+--------+--------+
| id | name | id | emp_id | amount |
+----+------+----+--------+--------+
|  1 | a    |  1 |      1 |   10.5 |
|  2 | b    |  1 |      1 |   10.5 |
|  1 | a    |  2 |      2 |     16 |
|  2 | b    |  2 |      2 |     16 |
+----+------+----+--------+--------+
4 rows in set (0.00 sec)


mysql> select * from trans, emp;
+----+--------+--------+----+------+
| id | emp_id | amount | id | name |
+----+--------+--------+----+------+
|  1 |      1 |   10.5 |  1 | a    |
|  2 |      2 |     16 |  1 | a    |
|  1 |      1 |   10.5 |  2 | b    |
|  2 |      2 |     16 |  2 | b    |
+----+--------+--------+----+------+
4 rows in set (0.00 sec)


# Simple/natural join
mysql> select * from emp, trans where emp.id=trans.emp_id;
+----+------+----+--------+--------+
| id | name | id | emp_id | amount |
+----+------+----+--------+--------+
|  1 | a    |  1 |      1 |   10.5 |
|  2 | b    |  2 |      2 |     16 |
+----+------+----+--------+--------+
2 rows in set (0.00 sec)



mysql> update  trans set amount=30.5 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from trans;
+----+--------+--------+
| id | emp_id | amount |
+----+--------+--------+
|  1 |      1 |   30.5 |
|  2 |      2 |     16 |
+----+--------+--------+
2 rows in set (0.00 sec)

mysql> delete from trans where id=1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from trans;
+----+--------+--------+
| id | emp_id | amount |
+----+--------+--------+
|  2 |      2 |     16 |
+----+--------+--------+
1 row in set (0.00 sec)



mysql> select * from trans order by amount desc;
+----+--------+--------+
| id | emp_id | amount |
+----+--------+--------+
|  5 |      1 |     80 |
|  3 |      2 |     20 |
|  4 |      1 |     20 |
|  1 |      2 |     16 |
|  2 |      2 |     16 |
+----+--------+--------+
5 rows in set (0.00 sec)

mysql> select * from trans order by amount desc limit 1;
+----+--------+--------+
| id | emp_id | amount |
+----+--------+--------+
|  5 |      1 |     80 |
+----+--------+--------+
1 row in set (0.00 sec)


mysql> select * from trans where amount < (select amount from trans order by amount desc limit 1) limit 1;
+----+--------+--------+
| id | emp_id | amount |
+----+--------+--------+
|  1 |      2 |     16 |
+----+--------+--------+
1 row in set (0.00 sec)

LEFT JOIN: get all the records that match in the same way in natural join and IN ADDITION
get an extra record for each unmatched record in the left table
of the join

mysql> select e.id, name, amount from emp e left join trans t on e.id=t.emp_id;
+----+------+--------+
| id | name | amount |
+----+------+--------+
|  2 | b    |     16 |
|  2 | b    |     16 |
|  2 | b    |     20 |
|  1 | a    |     20 |
|  1 | a    |     80 |
|  3 | c    |   NULL |
|  4 | c    |   NULL |
+----+------+--------+
7 rows in set (0.00 sec)

mysql> select e.id, name, amount from emp e left outer join trans t on e.id=t.emp_id;
+----+------+--------+
| id | name | amount |
+----+------+--------+
|  2 | b    |     16 |
|  2 | b    |     16 |
|  2 | b    |     20 |
|  1 | a    |     20 |
|  1 | a    |     80 |
|  3 | c    |   NULL |
|  4 | c    |   NULL |
+----+------+--------+
7 rows in set (0.00 sec)

mysql> select e.id, name, amount from emp e right join trans t on e.id=t.emp_id;
+------+------+--------+
| id   | name | amount |
+------+------+--------+
|    1 | a    |     20 |
|    1 | a    |     80 |
|    2 | b    |     16 |
|    2 | b    |     16 |
|    2 | b    |     20 |
+------+------+--------+
5 rows in set (0.00 sec)



mysql> select e.id, name, amount from emp e right outer join trans t on e.id=t.emp_id;
+------+------+--------+
| id   | name | amount |
+------+------+--------+
|    1 | a    |     20 |
|    1 | a    |     80 |
|    2 | b    |     16 |
|    2 | b    |     16 |
|    2 | b    |     20 |
+------+------+--------+
5 rows in set (0.00 sec)


mysql> select * from trans where amount regexp '^2';
+----+--------+--------+
| id | emp_id | amount |
+----+--------+--------+
|  3 |      2 |     20 |
|  4 |      1 |     20 |
+----+--------+--------+
2 rows in set (0.00 sec)

mysql> select * from trans where amount regexp '6$';
+----+--------+--------+
| id | emp_id | amount |
+----+--------+--------+
|  1 |      2 |     16 |
|  2 |      2 |     16 |
+----+--------+--------+
2 rows in set (0.00 sec)

A database index is a data structure that improves the speed of operations in a table.
 Indexes can be created using one or more columns, providing the
basis for both rapid random lookups and efficient ordering of access to records.

Practically, indexes are also a type of tables, which keep primary key or index field and a
 pointer to each record into the actual table.

The users cannot see the indexes, they are just used to speed up queries
and will be used by the Database Search Engine to locate records very fast.
The INSERT and UPDATE statements take more time on tables having indexes,
whereas the SELECT statements become fast on those tables.
The reason is that while doing insert or update, a database needs to insert or update the index values as well.

While creating index, it should be taken into consideration which all columns will be used to make SQL queries
and create one or more indexes on those columns.

A Simple index allows duplicate values in a table.
mysql> create index emp_id_index on trans(emp_id);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Unique Index: Field value cannot be duplicate in table
e.g
mysql> select * from trans where amount regexp '6$';
+----+--------+--------+
| id | emp_id | amount |
+----+--------+--------+
|  1 |      2 |     16 |
|  2 |      2 |     16 |
+----+--------+--------+

mysql> create unique index emp_id_index on trans(emp_id);
ERROR 1062 (23000): Duplicate entry '2' for key 'emp_id_index'
mysql>

mysql> show index from trans;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| trans |          0 | PRIMARY      |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| trans |          1 | emp_id_index |            1 | emp_id      | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> show index from emp;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| emp   |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)


Temprary Table: To keep temp data and deleted when the client session terminates
mysql> create temporary table temp1(id int, name char(5));
Query OK, 0 rows affected (0.03 sec)


mysql> insert into temp1(id, name)values(1, 'a');
Query OK, 1 row affected (0.03 sec)

mysql> select * from temp1 ;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

After close the client console and reopen:
mysql> select  * from temp1;
ERROR 1146 (42S02): Table 'test.temp1' doesn't exist
mysql>


Clone Table: Create a table with same coloum type, index and copy all records

mysql> show create table trans;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| trans | CREATE TABLE `trans` (
  `id` int(11) NOT NULL DEFAULT '0',
  `emp_id` int(11) DEFAULT NULL,
  `amount` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `emp_id_index` (`emp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table trans \G;
*************************** 1. row ***************************
       Table: trans
Create Table: CREATE TABLE `trans` (
  `id` int(11) NOT NULL DEFAULT '0',
  `emp_id` int(11) DEFAULT NULL,
  `amount` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `emp_id_index` (`emp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>
mysql> CREATE TABLE `trans_clone` (
    ->   `id` int(11) NOT NULL DEFAULT '0',
    ->   `emp_id` int(11) DEFAULT NULL,
    ->   `amount` float DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `emp_id_index` (`emp_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into trans_clone(id, emp_id, amount)select * from trans;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from trans_clone;
+----+--------+--------+
| id | emp_id | amount |
+----+--------+--------+
|  1 |      2 |     16 |
|  2 |      2 |     16 |
|  3 |      2 |     20 |
|  4 |      1 |     20 |
|  5 |      1 |     80 |
+----+--------+--------+
5 rows in set (0.00 sec)

Sequence:
A sequence is a set of integers 1, 2, 3, ... that are generated in order on a specific demand.
Sequences are frequently used to have a unique value in each row in a table
and sequences provide an easy way to generate them
in MySQL to use Sequences is to define a column as AUTO_INCREMENT

mysql> create table emp2(id int UNSIGNED auto_increment, name varchar(25), primary key (id));
mysql> insert into emp2(name)values('a');
Query OK, 1 row affected (0.01 sec)

mysql> insert into emp2(name)values('b');
Query OK, 1 row affected (0.00 sec)

mysql> select * from emp2;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)
mysql> show create table emp2\G;
*************************** 1. row ***************************
       Table: emp2
Create Table: CREATE TABLE `emp2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
mysql> insert into emp2(name)values('c');
Query OK, 1 row affected (0.00 sec)

mysql> show create table emp2\G;
*************************** 1. row ***************************
       Table: emp2
Create Table: CREATE TABLE `emp2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR:
No query specifi

re-sequencing of an AUTO_INCREMENT: drop the column from the table, then add it again. but make sure no Foreign key refrence to it

Starting a Sequence at a Particular Value:
create table emp2(id int UNSIGNED auto_increment=10, name varchar(25), primary key (id));


Preventing duplicates: Use primary key or UNIQUE Index
1. Define PRIMARY KEY: does not allow duplicate and NULL values
   create table person(id int unsigned, first_name varchar(10) not null, last_name varchar(10) not null, primary key(first_name,last_name));

2. INSERT IGNORE command:
 If a record doesn't duplicate an existing record, then MySQL inserts it as usual.
 If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error.
3. Use the REPLACE:
     If the record is new, it is inserted just as with INSERT. If it is a duplicate, the new record replaces the old one.
     mysql> select * from person;
Empty set (0.01 sec)

mysql> replace into person(first_name, last_name)values('aafak', 'moh');
Query OK, 1 row affected (0.00 sec)

mysql> replace into person(first_name, last_name)values('aafak', 'moh');
Query OK, 1 row affected (0.00 sec)

mysql> select * from person;
+------+------------+-----------+
| id   | first_name | last_name |
+------+------------+-----------+
| NULL | aafak      | moh       |
+------+------------+-----------+
1 row in set (0.00 sec)

4. add a UNIQUE index rather than a PRIMARY KEY to a table.

mysql> create table person2(id int unsigned auto_increment, first_name varchar(10) not null, last_name varchar(10) not null, unique(first_name,last_name));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql>
mysql> create table person2(id int unsigned, first_name varchar(10) not null, last_name varchar(10) not null, unique(first_name,last_name));
Query OK, 0 rows affected (0.14 sec)
mysql> show create table person2 \G;
*************************** 1. row ***************************
       Table: person2
Create Table: CREATE TABLE `person2` (
  `id` int(10) unsigned DEFAULT NULL,
  `first_name` varchar(10) NOT NULL,
  `last_name` varchar(10) NOT NULL,
  UNIQUE KEY `first_name` (`first_name`,`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

1. Behavior: Primary Key is used to identify a row (record) in a table,
 whereas Unique-key is to prevent duplicate values in a column (with the exception of a null entry).
2. Indexing: By default SQL-engine creates Clustered Index on primary-key if not exists and Non-Clustered Index on Unique-key

mysql> create table person3(id int unsigned, first_name varchar(10), last_name varchar(10), unique(first_name,last_name));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into person3(id, first_name, last_name)values(1, NULL, NULL); - Allows null
Query OK, 1 row affected (0.01 sec)
mysql> insert into person3(id, first_name, last_name)values(1, NULL, NULL);
Query OK, 1 row affected (0.01 sec)
mysql> select * from person3;
+------+------------+-----------+
| id   | first_name | last_name |
+------+------------+-----------+
|    1 | NULL       | NULL      |
|    1 | NULL       | NULL      |


mysql> create table person4(id int unsigned, first_name varchar(10), last_name varchar(10), primary key(first_name,last_name));
Query OK, 0 rows affected (0.14 sec)
mysql> insert into person4(id, first_name, last_name)values(1, NULL, NULL);
ERROR 1048 (23000): Column 'first_name' cannot be null

mysql> insert into person3(id)values(2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into person3(id)values(3);
Query OK, 1 row affected (0.00 sec)
mysql> select * from person3
    -> ;
+------+------------+-----------+
| id   | first_name | last_name |
+------+------------+-----------+
|    1 | NULL       | NULL      |
|    1 | NULL       | NULL      |
|    2 | NULL       | NULL      |
|    3 | NULL       | NULL      |
+------+------------+-----------+
6 rows in set (0.00 sec)


mysql> insert into person4(id)values(3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from person4
    -> ;
+------+------------+-----------+
| id   | first_name | last_name |
+------+------------+-----------+
|    3 |            |           |
+------+------------+-----------+
1 row in set (0.00 sec)

mysql> insert into person4(id)values(3);
ERROR 1062 (23000): Duplicate entry '' for key 'PRIMARY'
mysql>


Find Duplicates:
mysql> create table person5(id int auto_increment, fname varchar(10), lname varchar(20), primary key (id));
Query OK, 0 rows affected (0.03 sec)

mysql> select * from person5;
+----+--------+-------+
| id | fname  | lname |
+----+--------+-------+
|  1 | aafak  | moh   |
|  2 | aman   | moh   |
|  3 | aafak  | moh   |
|  4 | aakash | gupta |
|  5 | aman   | moh   |
+----+--------+-------+
5 rows in set (0.00 sec)

mysql> select fname, lname, count(*) as rep from person5 group by fname, lname having rep>1;
+-------+-------+-----+
| fname | lname | rep |
+-------+-------+-----+
| aafak | moh   |   2 |
| aman  | moh   |   2 |
+-------+-------+-----+
2 rows in set (0.00 sec)

mysql> select id, fname, lname, count(*) as rep from person5 group by fname, lname having rep>1 order by id desc;
+----+-------+-------+-----+
| id | fname | lname | rep |
+----+-------+-------+-----+
|  2 | aman  | moh   |   2 |
|  1 | aafak | moh   |   2 |
+----+-------+-------+-----+
2 rows in set (0.00 sec)


Eliminating Duplicates from a Query Result:
1. Using Distinct:
mysql> select  distinct fname, lname from person5;
+--------+-------+
| fname  | lname |
+--------+-------+
| aafak  | moh   |
| aman   | moh   |
| aakash | gupta |
+--------+-------+
3 rows in set (0.00 sec)

2. Using group by
mysql> select  id, fname, lname from person5 group by fname, lname;
+----+--------+-------+
| id | fname  | lname |
+----+--------+-------+
|  1 | aafak  | moh   |
|  4 | aakash | gupta |
|  2 | aman   | moh   |
+----+--------+-------+
3 rows in set (0.00 sec)

Removing Duplicates from table:
mysql> select * from person5 ;
+----+--------+-------+
| id | fname  | lname |
+----+--------+-------+
|  1 | aafak  | moh   |
|  2 | aman   | moh   |
|  3 | aafak  | moh   |
|  4 | aakash | gupta |
|  5 | aman   | moh   |
+----+--------+-------+
5 rows in set (0.00 sec)

mysql> create table tmmp select id, fname, lname from person5 group by fname, lname;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tmp
    -> ;
ERROR 1146 (42S02): Table 'test.tmp' doesn't exist
mysql> select * from tmmp;
+----+--------+-------+
| id | fname  | lname |
+----+--------+-------+
|  1 | aafak  | moh   |
|  4 | aakash | gupta |
|  2 | aman   | moh   |
+----+--------+-------+
3 rows in set (0.00 sec)

mysql> drop table person5;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table tmmp rename to person5 ;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from person5;
+----+--------+-------+
| id | fname  | lname |
+----+--------+-------+
|  1 | aafak  | moh   |
|  4 | aakash | gupta |
|  2 | aman   | moh   |
+----+--------+-------+
3 rows in set (0.00 sec)
