Dec 162014
 

Solved 51 SQL Queries

37) SQL query to create a role and grant it to a user

Create role <role_name>; //Create Role

Grant create table, create view to <role_name>; // Grant privileges to role

Grant <role_name> to <user_name1>, <user_name2>; // Grant Role to Users

38) SQL query to revoke select and insert privileges from a user

Revoke select, insert on <table_name> from <user_name>;

39) SQL query to display all data from Table 1 that is missing in Table 2

[Where, Table 1 and Table 2 are having same column name]

Select * from <Table1>

Minus

Select * from <Table2>;

40) SQL query to get all distinct data from table 1 and table 2

[Where, Table 1 and Table 2 are having same column name]

Select * from <Table1>

Union

Select * from <Table2>;

41) SQL query to display all data from table 1 and table 2

[Where, Table 1 and Table 2 are having same column name]

Select * from <Table1>

Union All

Select * from <Table2>;

42) SQL query to get number of records in a table

Select count (*) from employee;

(OR)

Select count (1) from employee;

43) SQL query to get sub-total values from a table

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

Create table emp (eno number,deptid number,jobtype varchar2(30),salary number)

Insert into emp values (101,10,'Assistant', 10000);

Insert into emp values (102,20,'Analyst', 50000);

Insert into emp values (103,20,'Clerk', 12000);

Insert into emp values (104,30,'Assistant', 20000);

Insert into emp values (105,30,'Manager', 60000);

Insert into emp values (106,30,'Sr analyst', 55000);

Insert into emp values (107,40,'Team Lead', 44000);

Commit;

select deptid,jobtype,sum(salary) from emp group by rollup (deptid,jobtype) order by deptid;

Output:

JOBTYPE
SUM(SALARY)

Assistant
10000

10000

Analyst
50000

Clerk
12000

62000

Assistant
20000

Manager
60000

Sr analyst
55000

135000

Team Lead
44000

44000

251000
DEPTID
10 10 20 20 20 30 30 30 30 40 40
 

44) SQL query to get data in cross-tabulation form from a table

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

Create table emp (eno number,deptid number,jobtype varchar2(30),salary number)

Insert into emp values (101,10,'Assistant', 10000);

Insert into emp values (102,20,'Analyst', 50000);

Insert into emp values (103,20,'Clerk', 12000);

Insert into emp values (104,30,'Assistant', 20000);

Insert into emp values (105,30,'Manager', 60000);

Insert into emp values (106,30,'Sr analyst', 55000);

Insert into emp values (107,40,'Team Lead', 44000);

Commit;

select deptid,jobtype,sum(salary) from emp group by cube (deptid,jobtype) order by deptid;

Output:

JOBTYPE
SUM(SALARY)

Assistant
10000

10000

Analyst
50000

Clerk
12000

62000

Assistant
20000

Manager
60000

Sr analyst
55000

135000

Team Lead
44000

44000

Analyst
50000

Assistant
30000

Clerk
12000

Manager
60000

Sr analyst
55000

Team Lead
44000

251000
DEPTID
10 10 20 20 20 30 30 30 30 40 40
 

45) SQL query to display detail of all employees who earn more than the average salary in their department

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

Create table emp (eno number,ename varchar2(30),deptid number,jobtype varchar2(30),salary number)

Insert into emp values (101,'Anil',10,'Assistant', 10000);

Insert into emp values (102,'Sunny',20,'Analyst', 50000);

Insert into emp values (103,'Karan',20,'Clerk', 12000);

Insert into emp values (104,'Sam',30,'Assistant', 20000);

Insert into emp values (105,'Peter',30,'Manager', 60000);

Insert into emp values (106,'Lucky',30,'Sr analyst', 55000);

Insert into emp values (107,'Sandy',40,'Team Lead', 44000);

Commit;

select ename,salary,deptid from emp outer where salary > (Select AVG (salary) from emp where deptid=outer.deptid);

Output:

SALARY
DEPTID

50000
20

60000
30

55000
30
ENAME
Sunny Peter Lucky
 

46) SQL query to find employees who have atleast one person reporting to them

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

Create table emp (eno number,ename varchar2(30),deptid number,jobtype varchar2(30),salary number,manager_id number)

Insert into emp values (101,'Anil',10,'Assistant', 10000,105);

Insert into emp values (102,'Sunny',20,'Analyst', 50000,106);

Insert into emp values (103,'Karan',20,'Clerk', 12000,104);

Insert into emp values (104,'Sam',30,'Assistant', 20000,106);

Insert into emp values (105,'Peter',30,'Manager', 60000,null);

Insert into emp values (106,'Lucky',30,'Sr analyst', 55000,105);

Insert into emp values (107,'Sandy',40,'Team Lead', 44000,106);

Commit;

select eno,ename,jobtype,deptid from emp outer

where exists ( Select 'X' from emp where manager_id=outer.eno);

Output:

ENAME
JOBTYPE
DEPTID

Peter
Manager
30

Lucky
Sr analyst
30

Sam
Assistant
30
ENO
105 106 104
 

47) SQL query to display data in a hierarchical form

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

Create table emp (eno number,ename varchar2(30),deptid number,jobtype varchar2(30),salary number,manager_id number)

Insert into emp values (101,'Anil',10,'Assistant', 10000,105);

Insert into emp values (102,'Sunny',20,'Analyst', 50000,106);

Insert into emp values (103,'Karan',20,'Clerk', 12000,104);

Insert into emp values (104,'Sam',30,'Assistant', 20000,106);

Insert into emp values (105,'Peter',30,'Manager', 60000,null);

Insert into emp values (106,'Lucky',30,'Sr analyst', 55000,105);

Insert into emp values (107,'Sandy',40,'Team Lead', 44000,106);

Commit;

Select ename || ' reports to ' || prior ename from emp

Start with eno=105

Connect by prior eno=manager_id;

Output:

HIERARCHICAL_FORM
Peter reports to Anil reports to Peter Lucky reports to Peter Sunny reports to Lucky Sam reports to Lucky Karan reports to Sam Sandy reports to Peter
 

48) SQL query to create user defined index when creating a table

Create table emp

(eno number primary key using index (Create INDEX idx_empid on emp (eno)),

ename varchar2(30),

deptid number,

jobtype varchar2(30),

salary number,

manager_id number);

[Where, “idx_empid” is a user-defined Index name]

49) SQL query to create a temporary table 

Create GLOBAL TEMPORARY TABLE TEMP_EMP (eno number, ename varchar2(30),esal number)ON COMMIT DELETE ROWS;

(OR)

Create GLOBAL TEMPORARY TABLE TEMP_EMP (eno number, ename varchar2(30),esal number)ON COMMIT PRESERVE ROWS;

50) SQL query to create a materialized view

Create materialized view MV1

Refresh fast with rowid

Enable query rewrite as

Select sales.channel_id C1,

Sum (sales.amount_sold) M1,

Count (*) M2

From sales group by sales.channel_id;

51) SQL query to add a default value to a column

create table emp

(eno number,

ename varchar2(30),

deptid number,

jobtype varchar2(30),

salary number,

manager_id number,

Hire_date date DEFAULT SYSDATE);

[Set System Date as Default date for “Hire_Date” column]

 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)