Dec 152014
 

Solved 51 SQL Queries

25) SQL query to get the first day of a month

Select trunc(to_date('30-Nov-2014','DD-MON-YYYY'),'month')from dual;

Output: 01-Nov-2014

26) SQL query to get the first day of a Year

Select trunc(to_date('30-Nov-2014','DD-MON-YYYY'),'Year')from dual;

Output: 01-Jan-2014

27) SQL query to spell a day

Select To_char(to_date('20-Nov-2014','DD-MON-YYYY'),'ddspth')from dual;

Output: twentieth [day 20 is spelled as twentieth]

28) SQL query to replace null value with another value

Select nvl ('','Testing Null Values') from dual;

Output: Testing Null Values

29) SQL query using CASE and DECODE function 

[Create one test table “Suppliers” and add few rows into it as below]

Create table suppliers (supplier_id number, supplier_name varchar2(100));

Insert into suppliers values (10000, 'ABC');

Insert into suppliers values (10001, 'XYZ');

Insert into suppliers values (10002, 'LMN');

Commit;

CASE Statement: 

SELECT supplier_name,

CASE

when supplier_id=10000 THEN 'IBM'

when supplier_id=10001 THEN 'Microsoft'

when supplier_id=10002 THEN 'Hewlett Packard'

ELSE NULL end result

FROM suppliers;

(OR)

SELECT supplier_name,

CASE

supplier_id when 10000 THEN 'IBM'

when 10001 THEN 'Microsoft'

when 10002 THEN 'Hewlett Packard'

ELSE NULL end result

FROM suppliers;

Output:
SUPPLIER_NAME RESULT
ABC IBM
XYZ Microsoft
LMN Hewlett Packard
 

DECODE Function:

SELECT supplier_name,

DECODE (supplier_id, 10000, 'IBM',

10001, 'Microsoft',

10002, 'Hewlett Packard',

'Gateway') result

FROM suppliers;

Output:
SUPPLIER_NAME RESULT
ABC IBM
XYZ Microsoft
LMN Hewlett Packard
 

30) SQL query to create inline view

SELECT * FROM

(SELECT deptno, count (*) emp_count FROM emp GROUP BY deptno) emp,

//is a Inline View

dept

WHERE dept.deptno = emp.deptno

31) SQL query to find top three earnings employees

[Create one test table “emp” and add few rows into it as below]

create table emp (eno number, ename varchar2(30),esal number);

insert into emp values (101,'Sachin',50000);

insert into emp values (102,'Anil',20000);

insert into emp values (103,'Naveen',30000);

insert into emp values (104,'Naman',20000);

insert into emp values (105,'John',40000);

insert into emp values (106,'Peter',45000);

commit;

select ename,esal,rownum as "RANK" from (select ename,esal from emp order by esal desc) where rownum<=3;

Output:
ENAME ESAL RANK
Sachin 50000 1
Peter 45000 2
John 40000 3
 

32) SQL query to create sequence range from 100 to 10,000 and increment by 15

CREATE SEQUENCE SEQ_TEST

START WITH 100

INCREMENT BY 15

MAXVALUE 10000

MINVALUE 1

NOCYCLE

CACHE 20

NOORDER;

33) SQL query to get current and next available sequence number

Select SEQ_TEST.nextval from dual; //get next available number

Select SEQ_TEST.currval from dual; // get current available number

34) SQL query to create synonym for a table name.

Create synonym emp for employee;

[where “emp” is a synonym to “employee” table name]

35) SQL query to create and change password of a user

Create user <user_name> identified by <pass_word>; // Create User

Alter user <user_name> identified by <pass_word>; // Change Password

36) SQL query to grant system privileges to a user

Grant create session, create table, create sequence, create view to <user_name>;

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)