To get all rows apart from first and last, use subquery along with MIN() and MAX(). Let us first create a table −mysql> create table DemoTable1917    (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentCode int,    StudentMarks int    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1917(StudentCode, StudentMarks) values(78, 95); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1917(StudentCode, StudentMarks) values(78, 96); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1917(StudentCode, StudentMarks) values(78, 97); Query OK, 1 row affected (0.00 ... Read More

For this, set conditions using MySQL CASE statement −mysql> create table DemoTable1916    (    StudentName varchar(20),    StudentMarks int    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1916 values('Chris', 59); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1916 values('David', 89); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1916 values('Sam', 94); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1916 values('Mike', 75); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1916 values('Carol', 69); Query OK, 1 row affected ... Read More

To set NOT NULL, use IS NOT NULL and find the value. The syntax is as follows −select if('' is not NULL, 1, 0) as anyAliasName;Here is the working query −mysql> select if('' is not NULL, 1, 0);This will produce the following output −+------------------------+ | if('' is not NULL, 1, 0) | +------------------------+ |                      1 | +------------------------+ 1 row in set (0.00 sec)To understand the above syntax, let us create a table −mysql> create table DemoTable1915    (    Name varchar(20)    ); Query OK, 0 rows affected (0.00 sec)Insert ... Read More

For this, use SET yourColumnName = NULL as in the below syntax −update yourTableName set yourColumnName=NULL where yourColumnName=yourValue;Let us first create a table −mysql> create table DemoTable1914    (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Code varchar(20)    )AUTO_INCREMENT=1001; Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1914(Code) values('John101'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1914(Code) values('234David'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1914(Code) values('100_Mike'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select ... Read More

To append 000, use the concept of ZEROFILL. Let us first create a table −mysql> create table DemoTable1913    (    Code int(4) ZEROFILL AUTO_INCREMENT NOT NULL,    PRIMARY KEY(Code)    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1913 values(1); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1913 values(2); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1913 values(3); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1913 values(4); Query OK, 1 row affected (0.00 sec)Display all records from the table ... Read More

For this, use ORDER BY along with LIMIT. Let us first create a table wherein we have a column with User id, logged in time, and name −mysql> create table DemoTable1911    (    UserId int,    UserLoggedInTime time,    UserName varchar(20)    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1911 values(100, '7:32:00', 'Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1911 values(101, '5:00:00', 'David'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1911 values(102, '6:10:20', 'Mike'); Query OK, 1 row affected (0.00 ... Read More

For this, you can use CASE WHEN statement. Let us first create a table −mysql> create table DemoTable1910    (    FirstName varchar(20),    Marks int    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1910 values('Chris', 45); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1910 values('David', 85); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1910 values('Chris', 55); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1910 values('Chris', 98); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1910 values('David', ... Read More

To return list of databases, the syntax is as follows −select schema_name as anyAliasName from information_schema.schemata;Here is the query to return list of databases in MySQL −mysql> select schema_name as DatabaseName from information_schema.schemata;This will produce the following output −+---------------------------+ | DatabaseName              | +---------------------------+ | mysql                     | | information_schema        | | performance_schema        | | sys                       | | business                  | | sample                    | | hello                     | | test                      | | mybusiness                | | databasesample            | | schemasample              | | universitydatabase        | | education                 | | mydatabase                | | database1                 | | sampledatabase            | | test3                     | | javadatabase2             | | javasampledatabase        | | rdb                       | | onetomanyrelationship     | | webtracker                | | web                       | | commandline               | | hb_student_tracker        | | bothinnodbandmyisam       | | customertracker           | | tracker                   | | demo                      | | customer_tracker_database | | login                     | | onlinebookstore           | | customer-tracker          | | web_tracker               | | instant_app               | | 1233                      | +---------------------------+ 36 rows in set (0.00 sec)

For this, you need to use SET command only once. Let us first create a table −mysql> create table DemoTable1909    (    Id int NOT NULL,    FirstName varchar(20),    LastName varchar(20)    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1909 values(101, 'John', 'Smith'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1909 values(102, 'John', 'Doe'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1909 values(103, 'Adam', 'Smith'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1909 values(104, 'David', 'Miller'); Query ... Read More

Let us first create a table −mysql> create table DemoTable1908    (    Code text    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1908 values('MySQL(1)Database'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1908 values('MongoDB 2 Database'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1908 values('MySQL(3)Database'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1908 values('SQL Server(10)Database'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1908 values('MySQL 8 Database'); Query OK, 1 row affected (0.00 sec)Display all records from ... Read More