Summary SQL part 2

UNIT III – DATABASE MANAGEMENT
STRUCTURED QUERY LANGUAGE(SQL)
SQL : PART 2
DML COMMANDS

1 To enter records in an existing table Employee with the following scheme ( Ecode, Ename, Gender,
Grade, Gross)
Command : INSERT
Purpose : To insert records (tuples) in an existing table
Syntax : INSERT INTO <tablename>
VALUES (<value1>,<value2>,<value3>….);
Eg:
INSERT INTO Employee
VALUES(1001,’Ravi’,’M’,’E4’,4670.00);

Alternate Command
Syntax : INSERT INTO <tablename>
(columnname1,columnname2,columnname3,…)
VALUES (<value1>,<value2>,<value3>….);
Eg:
INSERT INTO Employee (Ecode, Ename, Gender, Grade, Gross)
VALUES (1001, ’Ravi’, ’M’, ’E4’, 4670.00);

Inserting NULL values
INSERT INTO Employee
VALUES(2014,’Alex’,’M’, NULL, NULL);

Table Salespeople with the following scheme
(Scode, Sname,City,Comm)
2 Command : UPDATE
Purpose : Modifies the data in existing tables

Updating all rows
Syntax : UPDATE <tablename>
SET columnname = expression;

Eg:Change the commission of all Salespeople to 1500
UPDATE Salespeople SET comm =1500;
Updating only certain rows
Syntax : UPDATE <tablename>
SET columnname=expression
WHERE searchcondition ;

Eg: Change the commission of Salesman no. 1001 to 2500
UPDATE Salespeople SET comm =2500 WHERE Scode =1001;

Updating multiple columns
Syntax : UPDATE <tablename>

, SET colname1=expr1,colname2=expr2
WHERE searchcondition ;

Eg:Change the commission and city as 5000 and ‘DAMMAM’ of Salesman no. 1005
UPDATE Salespeople SET comm =5000 ,City =’DAMMAM”
WHERE Scode =1005;

Using Expression in Update
Eg. Double the commission of all salespeople
UPDATE Salespeople SET comm = comm *2;

Updating to NULL
Eg. UPDATE Salespeople SET City = NULL
WHERE Scode =1007;

3 Command : DELETE
Purpose : Removes tuples or rows from a table

Removing all the rows from the table
Syntax : DELETE FROM <table name>;

Eg: To remove all the records of Salespeople table
DELETE FROM Salespeople;
Removing only particular rows
Syntax : DELETE FROM <table name>
WHERE <columnname> = value;
Eg: To remove the record of Salesman no. 1001
DELETE FROM Salespeople WHERE Scode =1001;

Table : EMP
Empno Empname Job Mgr Hiredate Sal Comm Deptno
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7902 FORD ANALYST 7566 03-DEC-81 3000 NULL
7369 SMITH CLERK 7902 17-DEC-80 800 NULL
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7934 MILLER CLERK 7782 23-JAN-82 1300 NULL

4 Command : SELECT
Purpose : To get information from a table

Selecting all data – Retrieves everything from table