SQL
Group by date from a datetime or timestamp field in Mysql
This is a simple but greatly useful mysql query. You can use it to find all the dates even with time included in datetime or timestamp field.
No CommentsMySQL enum equvalent alternate in oracle
One of Mysqls great features in enum data type. An ENUM is a string object with a value chosen from a list of allowed values that are enumerated explicitly in the column specification at table creation time.
But in oracle there is no such data type. So if you want to restrict column value input like mysql enum datatype in oracle you can make use of an check constraint.
-- Student Table
CREATE TABLE student
(
reg number(10) NOT NULL,
name varchar2(50) NOT NULL,
dept varchar2(5) NOT NULL,
ses number(4) NOT NULL,
fname varchar2(50),
mname varchar2(50),
bday date,
gender varchar2(6),
cgpa number(4,2),
grade_letter varchar2(2),
constraint student_pk_1 PRIMARY KEY(reg), constraint student_en_1 CHECK(gender IN('Male', 'Female')),
constraint student_fk_1 FOREIGN KEY(dept) REFERENCES dept(code) ON DELETE cascade,
constraint student_fk_2 FOREIGN KEY(ses) REFERENCES ses(ses) ON DELETE cascade
);
Here in line 14 check function is working as like enum data type in mysql
No CommentsHow to implement query limit or pagination in oracle
There is no limit command like mysql in oracle. If you need to limit your query in oracle or want to make a pagination in oracle what you need to do is really rubbish!
You need to make a nested select query like below… uff
SELECT url, title, metadesc FROM (
SELECT url,title, metadesc, rownum rn FROM (
SELECT url, title, metadesc FROM web
)
WHERE rownum <= 60
)
WHERE rn > 50
This query is as same as mysql query like below
SELECT url,title,metaDesc FROM web LIMIT 50,10No Comments
Case insensitive like query in sql
Of course like query is case sensitive. If want to make it case insensitive simply make the column lower with lower() function and make the like query.
Here is a example
SELECT * FROM web WHERE lower(metaDesc) LIKE '%dscript%tutorial%'No Comments
