Display Coming Sunday’s Date for All Date Records in MySQL

AmitDiwan
Updated on 03-Sep-2019 13:13:44

261 Views

Let us first create a table −mysql> create table DemoTable(GetSundayDate date); Query OK, 0 rows affected (0.44 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-08-07'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('2018-09-05'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values('2019-09-12'); Query OK, 1 row affected (0.17 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+---------------+ | GetSundayDate | +---------------+ | 2019-08-07    | | 2018-09-05    | | 2019-09-12    | +---------------+ 3 rows in ... Read More

Convert Varchar to Unsigned Integer in MySQL

AmitDiwan
Updated on 03-Sep-2019 13:11:43

799 Views

To convert varchar to unsigned integer, use CAST() function. Let us first create a table −mysql> create table DemoTable868(Amount varchar(100)); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable868 values('781'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable868 values('990'); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable868 values('1002'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable868 values('560'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable868 values('890'); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable868 values('9890'); ... Read More

Get Top 3 Salaries from MySQL Table

AmitDiwan
Updated on 03-Sep-2019 13:09:05

1K+ Views

For this, use LIMIT and OFFSET. Let us first create a table −mysql> create table DemoTable867(EmployeeSalary int); Query OK, 0 rows affected (0.64 sec)Insert some records in the table using insert command −mysql> insert into DemoTable867 values(63737); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable867 values(899833); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable867 values(23644); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable867 values(89393); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable867 values(534333); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable867 values(889322); Query OK, 1 ... Read More

Perform Sum and Subtraction in MySQL Query Based on Condition

AmitDiwan
Updated on 03-Sep-2019 13:05:37

2K+ Views

For this, use CASE statement and set for both SUM and SUBTRACTION. Let us first create a table −mysql> create table DemoTable866(    Status varchar(100),    Amount int ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command −mysql> insert into DemoTable866 values('ACTIVE', 50); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable866 values('INACTIVE', 70); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable866 values('INACTIVE', 20); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable866 values('ACTIVE', 100); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable866 ... Read More

Understanding Parentheses in MySQL SELECT Column Names

AmitDiwan
Updated on 03-Sep-2019 13:02:08

441 Views

The SELECT(COLNAME) means, we are creating an alias for that column. Let us see an example and create a table −mysql> create table DemoTable865(    FirstName varchar(100),    LastName varchar(100) ); Query OK, 0 rows affected (0.77 sec)Insert some records in the table using insert command −mysql> insert into DemoTable865 values('Chris', 'Brown'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable865 values('Adam', 'Smith'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable865 values('David', 'Miller'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable865 values('Carol', 'Taylor'); Query OK, 1 row affected (0.15 sec)Display all records ... Read More

Return Non-Empty and Non-Null Values in MySQL

AmitDiwan
Updated on 03-Sep-2019 13:01:17

234 Views

Let us first create a table −mysql> create table DemoTable839(    StudentFirstName varchar(100),    StudentLastName varchar(100) ); Query OK, 0 rows affected (0.69 sec)Insert some records in the table using insert command −mysql> insert into DemoTable839 values('Chris', 'Brown'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable839 values('', 'Taylor'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable839 values(NULL, 'Taylor'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable839 values('Adam', 'Smith'); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −mysql> select *from DemoTable839;This will produce the following ... Read More

Create MySQL Table with Column Having 3 Possible Values

AmitDiwan
Updated on 03-Sep-2019 12:59:00

276 Views

For this, use the ENUM data type. Let us first create a table −mysql> create table DemoTable838(Color ENUM('RED','GREEN','BLUE')); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command −mysql> insert into DemoTable838 values('RED'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable838 values('Green'); Query OK, 1 row affected (0.64 sec) mysql> insert into DemoTable838 values('Blue'); Query OK, 1 row affected (0.88 sec)Display all records from the table using select statement −mysql> select *from DemoTable838;This will produce the following output −+-------+ | Color | +-------+ | RED | | GREEN | | BLUE | +-------+ 3 rows in set (0.00 sec)

Compare Year, Month, and Day in a MySQL Query

AmitDiwan
Updated on 03-Sep-2019 12:58:07

515 Views

For this, you can use DATE(). Let us first create a table −mysql> create table DemoTable864(DueDateTime timestamp); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable864 values('2019-01-10 12 −34 −55'); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable864 values('2016-12-11 11 −12 −00'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable864 values('2015-04-01 10 −00 −00'); Query OK, 1 row affected (0.28 sec) mysql> insert into DemoTable864 values('2017-05-20 04 −40 −10'); Query OK, 1 row affected (0.17 sec)Display all records from the table using select statement ... Read More

Replace Empty Values in MySQL Table with a Specific Value

AmitDiwan
Updated on 03-Sep-2019 12:57:02

444 Views

Let us first create a table −mysql> create table DemoTable837(Name varchar(100)); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable837 values('Chris'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable837 values(''); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable837 values('Robert'); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable837 values(''); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable837 values('David'); Query OK, 1 row affected (1.13 sec)Display all records from the table using select statement −mysql> select *from DemoTable837;This will produce ... Read More

MySQL Query to Get Dates Between Range of Records

AmitDiwan
Updated on 03-Sep-2019 12:55:31

387 Views

For fetching records between dates, use BETWEEN. Let us first create a table −mysql> create table DemoTable863(StudentDateOfBirth date); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable863 values('1998-01-10'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable863 values('2000-10-15'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable863 values('2003-04-20'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable863 values('2005-12-31'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable863 values('1999-07-01'); Query OK, 1 row affected (0.27 sec)Display all records from the table using select ... Read More

Advertisements