For this, use AVG() for average and GROUP BY to group records of duplicate column (Product Id). Let us first create a table −mysql> create table DemoTable1490    -> (    -> ProductId varchar(20),    -> ProductPrice int    -> ); Query OK, 0 rows affected (0.43 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1490 values('PRODUCT_100', 700); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1490 values('PRODUCT_200', 500); Query OK, 1 row affected (0.31 sec) mysql> insert into DemoTable1490 values('PRODUCT_200', 1000); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1490 values('PRODUCT_100', ... Read More

Let us first create a table −mysql> create table DemoTable1489    -> (    -> ProductId int,    -> ProductPrice int    -> ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1489 values(100, 900); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1489 values(115, 1000); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1489 values(119, 2100); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1489 values(125, 2100); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1489 values(128, 2900); Query OK, 1 ... Read More

MySQL EXPLAIN gives a query execution plan. EXPLAIN can be used in the beginning with SELECT, INSERT, DELETE, REPLACE, and UPDATE.To avoid the complete table scan in database, you need to use index. Let us first create a table −mysql> create table DemoTable1488    -> (    -> StudentId int,    -> StudentName varchar(20),    -> StudentAge int    -> ); Query OK, 0 rows affected (2.18 sec)Here is the query to create index −mysql> create index student_id_index on DemoTable1488(StudentId); Query OK, 0 rows affected (0.90 sec) Records: 0  Duplicates: 0  Warnings: 0insert into DemoTable1488 valueInsert some records in the ... Read More

For this, you can use ORDER BY along with some aggregate function right(). Let us first create a table −mysql> create table DemoTable1487    -> (    -> StudentCode text    -> ); Query OK, 0 rows affected (0.91 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1487 values('560'); Query OK, 1 row affected (0.36 sec) mysql> insert into DemoTable1487 values('789'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1487 values('STUDENT78'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1487 values('John89'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1487 ... Read More

For this, you can use ORDER BY with LIMIT. Here, LIMIT is used to set the limit (count) of records you want to fetch. Let us first create a table −mysql> create table DemoTable1486    -> (    -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> StudentName varchar(20)    -> ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1486(StudentName) values('Chris Brown'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1486(StudentName) values('David Miller'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1486(StudentName) values('John Doe'); ... Read More

To count, you can use SUM() along with CASE statement for conditions. Let us first create a table −mysql> create table DemoTable1485    -> (    -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> StudentName varchar(20),    -> StudentSubject varchar(20)    -> ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1485(StudentName, StudentSubject) values('Chris', 'MySQL'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1485(StudentName, StudentSubject) values('Robert', 'MongoDB'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1485(StudentName, StudentSubject) values('Robert', 'MongoDB'); Query OK, 1 row affected ... Read More

Let us first create a table −mysql> create table DemoTable1484    -> (    -> Id int    -> ); Query OK, 0 rows affected (0.46 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1484 values(100); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable1484 values(175); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1484 values(165); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1484 values(145); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1484 values(170); Query OK, 1 row affected (0.10 sec)Display all records from the table ... Read More

Let us first create a table −mysql> create table DemoTable1483    -> (    -> Salary int    -> ); Query OK, 0 rows affected (0.41 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1483 values(100); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1483 values(500); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1483 values(400); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −mysql> select * from DemoTable1483;This will produce the following output −+--------+ | Salary | +--------+ |    100 | |   ... Read More

For this, use \G as in the below syntax −select * from yourTableName\GLet us first create a table −mysql> create table DemoTable1482    -> (    -> Title varchar(255)    -> ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1482 values('Deep Dive using Java with Data Structure And Algorithm'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1482 values('Introduction To MySQL and MongoDB'); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −mysql> select * from DemoTable1482;This will produce the following ... Read More

To set conditions and replace rows, use MySQL CASE statement. Let us first create a table −mysql> create table DemoTable1481    -> (    -> PlayerScore int    -> ); Query OK, 0 rows affected (0.42 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1481 values(454); Query OK, 1 row affected (0.41 sec) mysql> insert into DemoTable1481 values(765); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1481 values(890); Query OK, 1 row affected (0.09 sec)Display all records from the table using select statement −mysql> select * from DemoTable1481;This will produce the following output −+-------------+ ... Read More