Dec 112014

Solved 51 SQL Queries

13) SQL query to copy structure of a table

Create table employee_new as select * from employee where 1>2;

[Where”employee_new” is a copied table to “employee” table without any data]

14) SQL query using MERGE statement

MERGE INTO bonuses D

USING (SELECT employee_id, salary, department_id FROM employees

WHERE department_id = 80) S

ON (D.employee_id = S.employee_id)

WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01

DELETE WHERE (S.salary > 8000)

WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)

VALUES (S.employee_id, S.salary*.01)

WHERE (S.salary <= 8000);

15) SQL query using LIKE condition

Select * from employee where emp_name like 'Sh%';

[Above query will display all employees whose name started with letter “Sh”]

16) SQL query to make a string case-sensitive.

Select upper ('john') from dual // All characters in upper case

Output: JOHN

Select lower ('JOHN') from dual // All characters in lower case

Output: john

Select initcap ('joHn') from dual //first character in upper case

Output: John

17) SQL query to calculate the length of a string value

Select length ('john') from dual;

Length of “john” is: 4

18) SQL query to pad blank space up to a maximum length

select lpad ('TESTING',10,'*') from dual; //Left Padding

Output: ***TESTING

select rpad ('TESTING',10,'*') from dual; // Right Padding

Output: TESTING***

[Here 10 is the maximum length for padding]

19) SQL query to return remainder of a division

Select mod (1500, 200) from dual;

Output: 100   [Remainder is 1500/200 = 100]

20) SQL query to find number of weeks fall between two dates

[Suppose Start Date is ’01-Nov-2014’ and End Date is “30-Nov-2014”]

Select (to_date ('30-Nov-2014','DD-MON-YYYY') - to_date('01-Nov-2014','DD-MON-YYYY'))/7 from dual;

Output: 4.14 weeks

21) SQL query to find last day of a month

[Suppose Start Date is ’01-Nov-2014’]

Select last_day (to_date('01-Nov-2014','DD-MON-YYYY')) from dual;

Output: 30-Nov-2014

22) SQL query to find next day of a month

[Suppose Start Date is ’01-Nov-2014’ and is a Saturday]

Select next_day (to_date('01-Nov-2014','DD-MON-YYYY'),'friday') from dual;

Output: 07-Nov-2014 [Next Friday will be on 07-Nov-2014]

23) SQL query to find number of months between two dates

[Suppose Start Date is ’01-Jul-2014’ and End Date is “30-Nov-2014”]

Select months_between(to_date ('30-Nov-2014','DD-MON-YYYY') , to_date('01-Jul-2014','DD-MON-YYYY'))from dual;

Output: 4.94

24) SQL query to add number of months to a date

Select add_months (to_date('01-Nov-2014','DD-MON-YYYY'),2) from dual;

Output: 01-Jan-2015

 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>