Lab2 - DATA MANIPULATION LANGUAGE (DML) COMMANDS
Objectives
To study the various DML commands and implement them on the database.
Background
INSERT COMMAND
Inserting a single row into a table:
Syntax: insert into <table name> values (value list)
Example: insert into s values(„s3‟,‟sup3‟,‟blore‟,10)
Inserting more than one record using a single insert commands:
Syntax: insert into <table name> values (&col1, &col2, ….)
Example: Insert into stud values(®, „&name‟, &percentage);
Skipping the fields while inserting:
Insert into <tablename(coln names to which datas to b inserted)> values (list of
values);
Other way is to give null while passing the values.
SELECT COMMANDS
Selects all rows from the table
Syntax: Select * from tablename;
Example; Select * from IT;
The retrieval of specific columns from a table:
It retrieves the specified columns from the table
Syntax: Select column_name1, …..,column_namen from table name;
Example: Select empno, empname from emp;
Elimination of duplicates from the select clause:
It prevents retriving the duplicated values .Distinct keyword is to be used.
1|Page
Syntax: Select DISTINCT col1, col2 from table name;
Example: Select DISTINCT job from emp;
Select command with where clause:
To select specific rows from a table we include „where‟ clause in the select command.
It can appear only after the „from‟ clause.
Syntax: Select column_name1, …..,column_namen from table name where condition;
Example: Select empno, empname from emp where sal>4000;
Select command with order by clause:
Syntax: Select column_name1, …..,column_namen from table name where condition
order by colmnname;
Example: Select empno, empname from emp order by empno;
UPDATE COMMAND
Syntax:update tablename set field=values where condition;
Example:Update emp set sal = 10000 where empno=135;
DELETE COMMAND
Syntax: Delete from table where conditions;
Example:delete from emp where empno=135;
PROCEDURE
STEP1: Insert a single record into dept table.
Solution:
1.Decide the data to add in dept.
2.Add to dept one row at a time using the insert into syntax.
SQL> insert into dept values (1,'IT','Tholudur');
1 row created.
STEP2: Update the emp table to set the salary of all employees to Rs15000/- who are
working as ASP
SQL> select * from emp;
EMPNO ENAME JOB DEPTNO SAL
2|Page
---------- -------------------- ------------- ---------- ----------
1 Mathi AP 1 10000
2 Arjun ASP 2 12000
3 Gugan ASP 1 12000
SQL> update emp set sal=15000 where job='ASP';
2 rows updated.
SQL> select * from emp;
EMPNO ENAME JOB DEPTNO SAL
---------- -------------------- ------------- ---------- ----------
1 Mathi AP 1 10000
2 Arjun ASP 2 15000
3 Gugan ASP 1 15000
STEP3: Delete only those who are working as lecturer
SQL> select * from emp;
EMPNO ENAME JOB DEPTNO SAL
---------- -------------------- ------------- ---------- ----------
1 Mathi AP 1 10000
2 Arjun ASP 2 15000
3 Gugan ASP 1 15000
4 Karthik Prof 2 30000
5 Akalya AP 1 10000
6 suresh lect 1 8000
6 rows selected.
SQL> delete from emp where job='lect';
1 row deleted.
SQL> select * from emp;
EMPNO ENAME JOB DEPTNO SAL
---------- -------------------- ------------- ---------- ----------
1 Mathi AP 1 10000
2 Arjun ASP 2 15000
3|Page
3 Gugan ASP 1 15000
4 Karthik Prof 2 30000
5 Akalya AP 1 10000
STEP4: List the records in the emp table orderby salary in ascending order. Ans:
SQL> select * from emp order by sal;
EMPNO ENAME JOB DEPTNO SAL
---------- -------------------- ------------- ---------- ----------
1 Mathi AP 1 10000
5 Akalya AP 1 10000
2 Arjun ASP 2 15000
3 Gugan ASP 1 15000
4 Karthik Prof 2 30000
STEP5: List the records in the emp table orderby salary in descending order.
SQL> select * from emp order by sal desc;
EMPNO ENAME JOB DEPTNO SAL
---------- -------------------- ------------- ---------- ----------
STEP6: Display only those employees whose deptno is 30.
Solution:
1. Use SELECT FROM WHERE syntax.
SQL> select * from emp where deptno=1;
EMPNO ENAME JOB DEPTNO SAL
---------- -------------------- ------------- ---------- ----------
1 Mathi AP 1 10000
3 Gugan ASP 1 15000
5 Akalya AP 1 0000
STEP6: Display deptno from the table employee avoiding the duplicated values.
Solution:
1. Use SELECT FROM syntax.
2.Select should include distinct clause for the deptno.
SQL> select distinct deptno from emp;
DEPTNO
4|Page
----------
1
2
QUESTIONS AND ANSWERS
1. Add the following Employee to the employees table:
Juan Cruz 25 Iloilo
Grace Able 24 Bacolod
2. Add the following Student to the student table.
Jerome BSCompE 4
Rica BSHRM 3
3. Delete all faculty from Marine Department
4. Delete all guest.
5. Update all BSCompE to 2nd year.
5|Page
