Scribd
Upload
10 views

SQL Commands - 3

SQL commands

Uploaded by

jagshaan Singh777
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
10 views

SQL Commands - 3

SQL commands

Uploaded by

jagshaan Singh777
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

SQL, Resource-3

UPDATE Command
This command is used to implement modification of the data values.

UPDATE <TableName> SET <col name>=<new valus>


WHERE <col>=val;

Increase fees value by 500.

➢ UPDATE student SET fees = fees + 500;

Increase the fees value by 100 for adno 222.

➢ UPDATE student SET fees = fees+100 WHERE adno = 222;

Table: Employee
Eno Ename Esal Edesig Egender
1 aaa 10000 Clerk M
2 aaa 15000 Manager F
3 bbb 8000 null M
4 ccc 22000 Analyst M
:
50 zzz 15000 Clerk F

➢ UPDATE Employee SET Esal=Esal+Esal*0.05 WHERE Edesig=’Clerk’;

DELETE Command
This command is used to remove information from a particular row or rows. Please
remember that this command will delete only row information but not the structure
of the table.

Remove adno 444 information.


➢ DELETE FROM student;
➢ DELETE FROM employee WHERE Esal<30000;
➢ DELETE FROM student WHERE stream= ‘Non=Med’;
Remove all records from a table.
➢ DELETE FROM student;
DDL commands
ALTER TABLE command
This command is used to implement modification of the structure of the table. This
is a DDL command.
Using this command, we can add a new column, remove the existing column and
modify data type of existing column.
Syntax:
ALTER TABLE <table name>
[ADD/CHANGE/MODIFY/DROP] <column name>;

For example:
1. Add one new column Totalfees with number (10, 2).
➢ ALTER TABLE student ADD Totalfees number(10,2);

2. Change Totalfees column to Total_fee.


➢ ALTER TABLE student CHANGE Totalfees Total_fee number(12,2);

3. Change Totalfees datatype as number(12,2).


➢ ALTER TABLE student MODIFY Total_fee integer;

4. Remove Totalfees column.


➢ ALTER TABLE student DROP Total_fee;

DROP TABLE Command

This command is used to remove the entire structure of the table and information.
This is also from the DDL command.

Syntax:
DROP TABLE<table name>;

For example:
Remove the whole structure of student table.
➢ DROP TABLE student;

Equi Join

Equi Joins are used to give information in different tables. It is a special type of join
in which we use only equality operator.
For example
➢ SELECT * FROM product, customer
WHERE product.product_no = customer. procuct_no;
(or)
➢ SELECT * FROM product p, customer c
WHERE p.product_no=c.procuct_no;

SQL Non-equi joins

The non-equi join is a type of join in which, we use only relational operators except
equal operator. These include >, <, >=, >= and !=.
For example
➢ SELECT * FROM product,customer
WHERE product.product_no!=customer.procuct_no;

LET'S REVISE

CREATE TABLE: Used to create structure of the table.


ALTER TABLE: Used to implement structure modification.
DROP TABLE: To remove full structure.
INSERT INTO: To add row values.
SELECT: To display row or column information.
DISTINCT: To select different information.
MAX(): To select maximum value of a particular column.
MIN(): To select minimum value of a particular column.
SUM(): To find total value of a particular column.
AVG(): To find average value of a particular column.
COUNT(): Number of records in the table.

You might also like