How to Create table in Oracle

How to Create Table in SQL

Create a table employee put the attributes are employee_id, employee_name, department, designation, salary where employee_id is primary key. Then insert these values.

Create table employe(emp_id int primary key, emp_name char(20), dept char(15), designation varchar(25), salary int)

 How to Insert Values in Table

Insert into employe values(11, 'Ravi', 'Marketing', 'lecturer', 25000)

Insert into employe values(12, 'Raj', 'Finance', 'Teacher', 30000)

EMP_ID	EMP_NAME	DEPT	DESIGNATION	SALARY
11	Ravi	Marketing	lecturer	25000
12	Raj	Finance	Teacher	30000

How to View the Table

Select * from employe

1. Show the name of all employee where salary is more than 26000.

select emp_name

from employe

where salary>26000

EMP_NAME
Raj

2. Show the employee_id of all employee who are working in finance department.

select emp_id

from employe

where dept='Marketing'

EMP_ID
11

3. Change the department of employee_id 12 to marketing.

Update employe

Set dept='marketing'

Where emp_id=12

EMP_ID	EMP_NAME	DEPT	DESIGNATION	SALARY
11	Ravi	Marketing	lecturer	25000
12	Raj	marketing	Teacher	30000

Create table order112(order_id int primary key, customer_id char(15), bill_date date, bill_amount int)

Select * from order112

Insert into order112 values(13, 'B', '24-jan-14', 90000)

Insert into order112 values(14, 'A', '23-jan-14', 50000)

Insert into order112 values(15, 'C', '26-jan-14', 100000)

1. Show order_id of all the order where bill amount is more than Rs.20000.

Select order_id

From order112

Where bill_amount>20000

2. Display all order detail table after arranging them date wise.

Select * from order112

Order by bill_date

3. Change the order_id of customer with customer_id 234.

Update order112

Set COLUMN NAME = VALUES

Where CONDITION

4. At add new attribute customer _name in order detail table.

Alter table TABLE NAME

Modify COLUMN NAME     DATA TYPE(SIZE)

OR,

Alter table TABLE NAME

Add COLUMN NAME      DATA TYPE(SIZE)

Alter table order112

add customer_name char(25)

5. Display customer_id of those customers who spent Rs.10000 to Rs.20000.

Select customer_id

From order112

Where bill_amount between 10000 and 20000

6. Delete all the record of order_id is 13.

Delete from order112

Where order_id=13

 7. Delete the table order detail.

Delete order112

Create table product12(product_id int primary key, product_name char(20), supplier_id int, unit int, price int)

select * from product12

insert into product12 values(1, 'Apple', 21, 10, 20000)

insert into product12 values(2, 'Orange', 22, 11, 600000)

1. Show the largest value of price column from product table.

Select max(price)

From product12

2. Find the average value of the price from product table.

Select avg(price)

From product12

3. Display the product_name and price record that have an above average price.

Select product_name, price

From product12

Where price>(select avg(price) from product12)

4. Find the total number of record in product table.

Select count(*)

From product12

5. Display the number of distinct values of product_id.

Select count(distinct product_id)

From product12

6. Find the sum of all price field price columns for the price product table.

Select sum(price)

From product12

7. Display the smallest values of the unit column.

Select min(unit)

From product12