Database Articles - Page 300 of 671

Here we will see how to check given year is leap year or not, using PL/SQL. In PL/SQL code, some group of commands are arranged within a block of related declaration of statements.The leap year checking algorithm is like below.AlgorithmisLeapYear(year): begin    if year is divisible by 4 and not divisible by 100, then       it is leap year    else if the number is divisible by 400, then       it is leap year    else       it is not leap year endExampleDECLARE    year NUMBER := 2012; BEGIN    IF MOD(year, 4)=0   ... Read More

For this, you need to use IN() and after that FIELD() method. Let us first create a table −mysql> create table DemoTable(    StudentId varchar(10),    StudentName varchar(20) ) ; Query OK, 0 rows affected (4.11 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('10001', 'Adam'); Query OK, 1 row affected (0.52 sec) mysql> insert into DemoTable values('1010', 'Chris'); Query OK, 1 row affected (0.72 sec) mysql> insert into DemoTable values('1020', 'Bob'); Query OK, 1 row affected (0.34 sec) mysql> insert into DemoTable values('1030', 'Carol'); Query OK, 1 row affected (0.47 sec) mysql> insert into ... Read More

For exact string, you need to use wildcard ‘%’ with LIKE operator −select *from yourTableName where binary yourColumnName LIKE '%yourStringValue%';Let us first create a table −mysql> create table DemoTable(    Name varchar(20) ); Query OK, 0 rows affected (1.93 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (1.11 sec) mysql> insert into DemoTable values('John'); Query OK, 1 row affected (0.38 sec) mysql> insert into DemoTable values('Adam'); Query OK, 1 row affected (1.19 sec) mysql> insert into DemoTable values('JOHN'); Query OK, 1 row affected (0.60 sec) mysql> insert into DemoTable ... Read More

Let us first create a table −mysql> create table DemoTable(    Value int ); Query OK, 0 rows affected (1.35 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.30 sec) mysql> insert into DemoTable values(NULL); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.59 sec) mysql> insert into DemoTable values(0); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.70 ... Read More

The SELECT * is slower than 40 columns listing. It’s a better choice to list the column names while using the SELECT query. Let us see a simple example and create a table −mysql> create table DemoTable(    Id int,    Name varchar(20),    Age int,    ZipCode varchar(20),    CountryName varchar(20) ); Query OK, 0 rows affected (0.82 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(101, 'Chris', 23, '0909332', 'US'); Query OK, 1 row affected (0.48 sec) mysql> insert into DemoTable values(102, 'Bob', 24, '8747443', 'AUS'); Query OK, 1 row affected (0.50 sec) ... Read More

Let us first create a table −mysql> create table DemoTable(    Marks int ); Query OK, 0 rows affected (1.34 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(78); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(88); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(67); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(76); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable values(98); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(86); Query OK, 1 row affected (0.11 ... Read More

Let us first create a table −mysql> create table DemoTable(    Data int ); Query OK, 0 rows affected (0.98 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(60); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values(40); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(50); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(60); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(40); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(80); Query OK, 1 row affected (0.15 ... Read More

Let us first create a table −mysql> create table DemoTable1(    Value int ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(67); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1 values(46); Query OK, 1 row affected (0.11 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;This will produce the following output −+-------+ | Value | +-------+ | 67 | | 46 | +-------+ 2 rows in set (0.00 sec)Following is the query to create the ... Read More

Let us first create a table with ENUM type column −mysql> create table DemoTable(    isMarried ENUM('1', '0') ); Query OK, 0 rows affected (0.46 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('0'); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable values('1'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values('1'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('0'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values('0'); Query OK, 1 row affected (1.00 sec) mysql> insert into DemoTable values('1'); Query ... Read More

For this, use ORDER BY FIELD(). Let us first create a table −mysql> create table DemoTable(    ClientId varchar(40),    ClientName varchar(40) ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('987_John', 'John'); Query OK, 1 row affected (0.33 sec) mysql> insert into DemoTable values('1000_Sam', 'Sam'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values('777_Carol', 'Carol'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('2000_Bob', 'Bob'); Query OK, 1 row affected (0.20 sec)Display all records from the table using select statement −mysql> select ... Read More