To display 3 digits after decimal, use TRUNCATE(). Let us first create a table −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Value DECIMAL(10, 5) ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Value) values(109.4567); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable(Value) values(15.9875); Query OK, 1 row affected (0.28 sec) mysql> insert into DemoTable(Value) values(1234.2346789); Query OK, 1 row affected, 1 warning (0.14 sec)Following is the query to display all records from the table using select statement −mysql> select ... Read More

You need to use SUBSTRING_INDEX() function from MySQL to extract part of a URL. Let us first create a table −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    URL text ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(URL) values('https:\www.example.com\homepage'); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable(URL) values('https:\www.onlinetest.com\welcome\indexpage'); Query OK, 1 row affected (0.12 sec)Following is the query to display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output. Here, we can ... Read More

You can use subquery with aggregate COUNT(*) to get the total row count. Let us first create a table −mysql> create table DemoTable (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentFirstName varchar(20),    StudentAge int ); Query OK, 0 rows affected (0.48 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentFirstName, StudentAge) values('John', 23); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(StudentFirstName, StudentAge) values('Larry', 21); Query OK, 1 row affected (0.40 sec) mysql> insert into DemoTable(StudentFirstName, StudentAge) values('Johnny', 23); Query OK, 1 row affected (0.43 sec) mysql> insert into DemoTable(StudentFirstName, StudentAge) ... Read More

You can use CURRENT_TIMESTAMP to set system date time. Let us first create a table −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    ClientFirstName varchar(20),    ClientLastName varchar(20),    ClientAge int ); Query OK, 0 rows affected (0.66 sec)Following is the query to set default datetime as system date time in MySQL −mysql> alter table DemoTable add column ClientProjectDeadline timestamp default current_timestamp; Query OK, 0 rows affected (0.46 sec) Records: 0 Duplicates: 0 Warnings: 0Let us check the description of table once again −mysql> desc DemoTable;This will produce the following output −+-----------------------+-------------+------+-----+-------------------+----------------+ | Field   ... Read More

To count number of positive and negative votes, you can use CASE statement along with aggregate function SUM().Let us first create a table −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Vote int ); Query OK, 0 rows affected (1.70 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Vote) values(-10); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(Vote) values(100); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(Vote) values(45); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(Vote) values(-6); Query OK, 1 row ... Read More

You need to create event to drop table after x hours. The syntax is as follows −CREATE EVENT yourEventName ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL x HOUR DO DROP TABLE IF EXISTS yourTableName;Let us first create a table −mysql> create table DemoTable (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudnetFirstName varchar(20),    StudentLastName varchar(20),    StudnetAge int ); Query OK, 0 rows affected (0.52 sec)Now implement the above event in order to drop table after 2 hours −mysql> CREATE EVENT drop_table_event_after2HoursDemo ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 HOUR DO DROP TABLE IF EXISTS DemoTable; Query OK, ... Read More

Yes, you can perform this in MySQL by first getting the middle value. Let us first create a table:mysql> create table DemoTable (    UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY ); Query OK, 0 rows affected (0.65 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable values(); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.16 sec) mysql> ... Read More

Let us first create a table. After that we will create a new random value column and order the record randomly:mysql> create table DemoTable (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentName varchar(20) ); Query OK, 0 rows affected (0.57 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable(StudentName) values('Larry'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(StudentName) values('Sam'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(StudentName) values('Mike'); Query OK, 1 row affected (0.34 sec) mysql> insert into DemoTable(StudentName) values('Carol'); Query OK, 1 ... Read More

If you try to create a table and the table name already exist then MySQL will give a warning message. Let us verify the concept.Here, we are creating a table that already exist −mysql> CREATE TABLE IF NOT EXISTS DemoTable    (    CustomerId int,    CustomerName varchar(30),    CustomerAge int    ); Query OK, 0 rows affected, 1 warning (0.05 sec)The table name DemoTable is already present. Let us check the warning message.Following is the query −mysql> show warnings;This will produce the following output i.e. the warning message −+-------+------+------------------------------------+ | Level | Code | Message ... Read More

You can use INFORMATION_SCHEMA.COLUMNS for this. Following is the syntax −SELECT COLUMN_NAME, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='yourTableName';Let us first create a table −mysql> create table DemoTable    (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    ClientName varchar(60),    ClientAge int,    ClientSalary DECIMAL(10, 4),    isRegularClient bool    ); Query OK, 0 rows affected (0.44 sec)Following is the query to get field name types from a SQL database −mysql> SELECT COLUMN_NAME, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='DemoTable';This will produce the following output −+-----------------+---------------+ | COLUMN_NAME | COLUMN_TYPE | +-----------------+---------------+ | Id ... Read More