Showing posts with label Structure Query Language (SQL). Show all posts
Showing posts with label Structure Query Language (SQL). Show all posts

Sql Concept using Oracle

Structure Query Language (SQL) ,
Sql Concept using Oracle
CONTENTS DESCRIPTION
  • SELECT
 DATA RETRIEVAL
  • INSERT
  • UPDATE
  • DELETE
 DATA MANIPULATION LANGUAGE (DML)
  • CREATE
  • ALTER
  • DROP
  • RENAME
  • TRUNCATE
 DATA DEFINATION LANGUAGE (DDL)
  • COMMIT
  • ROLLBACK
  • SAVEPOINT
 TRANSACTION CONTROL LANGUAGE (TCL)
  • GRANT
  • REVOKE
 DATA CONTROL LANGUAGE (DCL)

SQL DESCRIPTION
  • */{DISTINCT} COLUMN
 For retrive data All/Not repeted
  • ARITHMETIC OPERATIONS(* , / ,+, -)
 Mathematical Operations
  • COLUMN ALLIASES (BY USING AS / ””)
 Define another name of column
  • CONCATENATION (||)
 Concate more than one string or columns
  • DESC / DESCRIBE
 Show the schema of any table or view
WHERE CLAUSE
  • COMPARISION OPERATORS
    (= , <, <=, >, >=, <>)
 Use of comparision operators
  • BETWEEN ….. AND Condition
 Use of between and condition
  • IN CONDITION
 Use of IN Condition
  • LIKE CONDITION
 Use of LIKE Condition
  • NULL CONDITION
 Use of NULL Condition
  • LOGICAL CONDITION (AND, OR, NOT)
 Use of logical Operators
  • ORDER BY CLAUSE (ASC / DESC)
 Show data in ASC/DESC Order
Read more »
- No comments:
Tags

Find highest salary for each job.

Structure Query Language (SQL) ,
Find highest salary for each job.

For solving the above question we need to create two tables (EMPLOYEE , DEPARTMENT)


create table employee
(
empid number(5) primary key,
empname varchar2(50),
salary number(10,2),
Grade number(1),
deptno number(3) references department(deptno),
location varchar2(50),
desig varchar(20)
);

create table department
(
deptno number(3) primary key,
deptname varchar(50)
);

Then Insert few records in Employee and Department table.

EMPLOYEE

EMPID EMPNAME SALARY GRADE DEPTNO LOCATION DESIG
1 AMIT SEN 11000 1 1 BHOPAL MANAGER
2 RAMESH THAKUR 11000 2 1 BHOPAL CLERK
3 RAKESH 12000 1 1 INDORE CLERK
4 SUMIT SAKET 9000 3 2 BHOPAL CLERK
5 AMIT SEN 11000 2 2 BHOPAL MANAGER
6 AMIT SEN 12000 2 5 INDORE CLERK
7 AMIT SEN 15000 1 6 INDORE MANAGER
8 ANKIT CHOUHAN 17000 1 6 INDORE CLERK
9 RAMAKANT RAJPOOT 12000 1 6 BHOPAL CLERK

DEPARTMENT

DEPTNO DEPTNAME
1 SALES
2 PRODUCTION
3 PURCHASE
4 MARKETING
5 ACCOUNTS
6 HR
7 ADMIN

SQL :

select deptno,MAX(salary) from employee GROUP BY deptno;


RESULT :

DEPTNO MAX(salary)
1 12000
2 11000
5 12000
6 17000
Read more »
- No comments:
Tags

Find the employee having minimum salary in each department.

Structure Query Language (SQL) ,
Find the employee having minimum salary in each department.

For solving the above question we need to create two tables (EMPLOYEE , DEPARTMENT)


create table employee
(
empid number(5) primary key,
empname varchar2(50),
salary number(10,2),
Grade number(1),
deptno number(3) references department(deptno),
location varchar2(50),
desig varchar(20)
);

create table department
(
deptno number(3) primary key,
deptname varchar(50)
);

Then Insert few records in Employee and Department table.

EMPLOYEE

EMPID EMPNAME SALARY GRADE DEPTNO LOCATION DESIG
1 AMIT SEN 11000 1 1 BHOPAL MANAGER
2 RAMESH THAKUR 11000 2 1 BHOPAL CLERK
3 RAKESH 12000 1 1 INDORE CLERK
4 SUMIT SAKET 9000 3 2 BHOPAL CLERK
5 AMIT SEN 11000 2 2 BHOPAL MANAGER
6 AMIT SEN 12000 2 5 INDORE CLERK
7 AMIT SEN 15000 1 6 INDORE MANAGER
8 ANKIT CHOUHAN 17000 1 6 INDORE CLERK
9 RAMAKANT RAJPOOT 12000 1 6 BHOPAL CLERK

DEPARTMENT

DEPTNO DEPTNAME
1 SALES
2 PRODUCTION
3 PURCHASE
4 MARKETING
5 ACCOUNTS
6 HR
7 ADMIN

SQL :

select deptno,MIN(salary) from employee GROUP BY deptno;


RESULT :

DEPTNO MIN(salary)
1 11000
2 9000
5 12000
6 12000
Read more »
- No comments:
Tags

Find Employee name who earn minimum salary.

Structure Query Language (SQL) ,
Find Employee name who earn minimum salary.

For solving the above question we need to create two tables (EMPLOYEE , DEPARTMENT)


create table employee
(
empid number(5) primary key,
empname varchar2(50),
salary number(10,2),
Grade number(1),
deptno number(3) references department(deptno),
location varchar2(50),
desig varchar(20)
);

create table department
(
deptno number(3) primary key,
deptname varchar(50)
);

Then Insert few records in Employee and Department table.

EMPLOYEE

EMPID EMPNAME SALARY GRADE DEPTNO LOCATION DESIG
1 AMIT SEN 11000 1 1 BHOPAL MANAGER
2 RAMESH THAKUR 11000 2 1 BHOPAL CLERK
3 RAKESH 12000 1 1 INDORE CLERK
4 SUMIT SAKET 9000 3 2 BHOPAL CLERK
5 AMIT SEN 11000 2 2 BHOPAL MANAGER
6 AMIT SEN 12000 2 5 INDORE CLERK
7 AMIT SEN 15000 1 6 INDORE MANAGER
8 ANKIT CHOUHAN 17000 1 6 INDORE CLERK
9 RAMAKANT RAJPOOT 12000 1 6 BHOPAL CLERK

DEPARTMENT

DEPTNO DEPTNAME
1 SALES
2 PRODUCTION
3 PURCHASE
4 MARKETING
5 ACCOUNTS
6 HR
7 ADMIN

SQL :

select empname,salary from employee where salary=(select min(salary) from employee);


RESULT :

EMPNAME SALARY
SUMIT SAKET 9000
Read more »
- No comments:
Tags

Find Average Salary of employee working in each department.

Structure Query Language (SQL) ,
Find Average Salary of employee working in each department.

For solving the above question we need to create two tables (EMPLOYEE , DEPARTMENT)


create table employee
(
empid number(5) primary key,
empname varchar2(50),
salary number(10,2),
Grade number(1),
deptno number(3) references department(deptno),
location varchar2(50),
desig varchar(20)
);

create table department
(
deptno number(3) primary key,
deptname varchar(50)
);

Then Insert few records in Employee and Department table.

EMPLOYEE

EMPID EMPNAME SALARY GRADE DEPTNO LOCATION DESIG
1 AMIT SEN 11000 1 1 BHOPAL MANAGER
2 RAMESH THAKUR 11000 2 1 BHOPAL CLERK
3 RAKESH 12000 1 1 INDORE CLERK
4 SUMIT SAKET 9000 3 2 BHOPAL CLERK
5 AMIT SEN 11000 2 2 BHOPAL MANAGER
6 AMIT SEN 12000 2 5 INDORE CLERK
7 AMIT SEN 15000 1 6 INDORE MANAGER
8 ANKIT CHOUHAN 17000 1 6 INDORE CLERK
9 RAMAKANT RAJPOOT 12000 1 6 BHOPAL CLERK

DEPARTMENT

DEPTNO DEPTNAME
1 SALES
2 PRODUCTION
3 PURCHASE
4 MARKETING
5 ACCOUNTS
6 HR
7 ADMIN

SQL :

select Avg(salary),deptno from employee Group By deptno;


RESULT :

SALARY DEPTNO
11333.3333 1
10000 2
12000 5
14666.6667 6
Read more »
- No comments:
Tags