Dml & Ddl
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Dml & Ddl as PDF for free.
More details
- Words: 687
- Pages: 7
CREATE: SQL> create table student_db(name char(10),age number(10),rollno number(10),mark1 number(10),mark2 number(10),total number(10),result char(10)); Table created.. RENAME: SQL> rename student_db to student ; Table renamed. SQL> select * from student; INSERT: SQL> insert into student values('&name','&age','&rollno','&mark1','&mark2','&total','&result'); Enter value for name: priya Enter value for age: 19 Enter value for rollno: 01 Enter value for mark1: 99 Enter value for mark2: 99 Enter value for total: 198 Enter value for result: pass old 1: insert into student values('&name','&age','&rollno','&mark1','&mark2','&total','&result') new 1: insert into student values('priya','19','01','99','99','198','pass') 1 row created.
NAME
AGE
ROLLNO
MARK1
MARK2
TOTAL RESULT
---------- ---------- ---------- ---------- ---------- ---------- ---------priya
19
1
99
99
198
pa ss
prema
18
2
98
99
197
pas s
sumathi
20
3
96
96
192
pa ss
murugan
21
4
96
95
191
pas s
latha
18
5
98
97
195
pass
sindhu
20
6
98
95
193
pas s
saran
22
7
96
98
194
pa ss
madhu
22
9
99
99
198
pas s
amutha
26
10
99
99
198
pass
hema
12
12
99
99
198
pas s
SELECT: SQL> select * from student where name='priya';
NAME
AGE
ROLLNO
MARK1
MARK2
TOTAL RESULT
---------- ---------- ---------- ---------- ---------- ---------- ---------priya
19
1
99
99
DISTINCT: SQL> SELECT DISTINCT ROLLNO FROM STUDENT; ROLLNO ---------1 2 3 4 5 6
198
pa ss
7 9 10 12
10 rows selected. SQL> select * from student where rollno='01'; NAME
AGE
ROLLNO
MARK1
MARK2
TOTAL RESULT
---------- ---------- ---------- ---------- ---------- ---------- ---------priya
19
1
99
99
198 pass
SQL> update student set age='20' where name='priya'; 1 row updated.
ALTER: SQL> ALTER TABLE STUDENT ADD DOB DATE; Table altered. NAME
AGE
ROLLNO
MARK1
MARK2
TOTAL R ESULT
---------- ---------- ---------- ---------- ---------- ---------- ------DOB ---------hema pass
12
12
99
99
198
DROPPING A COLUMN: SQL> ALTER TABLE STUDENT DROP COLUMN DOB; Table altered. SQL> SELECT * FROM STUDENT; NAME
AGE
ROLLNO
MARK1
MARK2
TOTAL RESULT
---------- ---------- ---------- ---------- ---------- ---------- ----------
priya
20
1
99
99
prema
18
2
98
99
198
pass
197
pass
sumathi
20
3
96
96
192
pass
murugan
21
4
96
95
191
pass
98
97
195
pass
latha
18
5
sindhu
20
6
98
95
193
pass
saran
22
7
96
98
194
pass
madhu
22
9
99
amutha
26
10
99
hema
12
12
99
198
99
99
198
99
198
pass pass pass
10 rows selected.
MODIFY: SQL> ALTER TABLE STUDENT MODIFY RESULT CHAR(11); Table altered.
DELETE: SQL> DELETE FROM STUDENT WHERE AGE='12'; 1 row deleted. SQL> SELECT*FROM STUDENT; NAME
AGE
ROLLNO
MARK1
MARK2
TOTAL RESULT
---------- ---------- ---------- ---------- ---------- ---------- ----------priya
20
1
99
99
198
pass
prema
18
2
98
99
197
pass
sumathi
20
3
96
96
192
pass
murugan
21
4
96
95
191
pass
latha
18
5
98
97
195
pass
sindhu
20
6
98
95
193
pass
saran
22
7
96
98
194
pass
99
99
198
madhu
22
9
amutha
26
10
9 rows selected.
TRUNCATE: SQL> TRUNCATE TABLE EMPLOYEE; Table truncated. SQL> SELECT * FROM EMPLOYEE; no rows selected
DROP:
SQL> DROP TABLE EMPLOYEE; Table dropped. SQL> SELECT * FROM EMPLOYEE; SELECT * FROM EMPLOYEE *ERROR at line 1: ORA-00942: table or view does not exist
USAGE OF RELATIONAL OPERATORS: SQL> select * from employee_db;
NAME
SALARY
DA
99
99
198
pass pass
---------- ---------- ---------priya prema murugan latha
50000
12000
25000
10000
10100
1000
25006
1230
SQL> select * from employee_db where da>10000; NAME
SALARY
DA
---------- ---------- ---------priya
50000
12000
SQL> select * from employee_db where da>=10000;
NAME
SALARY
DA
---------- ---------- ---------priya prema
50000
12000
25000
10000
SQL> select * from employee_db where da<10000;
NAME
SALARY
DA
---------- ---------- ---------murugan latha
10100 25006
1000 1230
SQL> select * from employee_db where da>=10000 or name='latha';
NAME
SALARY
DA
---------- ---------- ---------priya prema latha
50000
12000
25000 25006
10000 1230
SQL> select * from employee_db where da>=10000 and name!='priya';
NAME
SALARY
DA
---------- ---------- ---------prema
25000
10000
SQL> select * from employee_db where salary between 10000 and 50000;
NAME
SALARY
DA
---------- ---------- ---------priya prema murugan latha
50000 25000 10100 25006
12000 10000 1000 1230
NAME
AGE
ROLLNO
MARK1
MARK2
TOTAL RESULT
---------- ---------- ---------- ---------- ---------- ---------- ---------priya
19
1
99
99
198
pa ss
prema
18
2
98
99
197
pas s
sumathi
20
3
96
96
192
pa ss
murugan
21
4
96
95
191
pas s
latha
18
5
98
97
195
pass
sindhu
20
6
98
95
193
pas s
saran
22
7
96
98
194
pa ss
madhu
22
9
99
99
198
pas s
amutha
26
10
99
99
198
pass
hema
12
12
99
99
198
pas s
SELECT: SQL> select * from student where name='priya';
NAME
AGE
ROLLNO
MARK1
MARK2
TOTAL RESULT
---------- ---------- ---------- ---------- ---------- ---------- ---------priya
19
1
99
99
DISTINCT: SQL> SELECT DISTINCT ROLLNO FROM STUDENT; ROLLNO ---------1 2 3 4 5 6
198
pa ss
7 9 10 12
10 rows selected. SQL> select * from student where rollno='01'; NAME
AGE
ROLLNO
MARK1
MARK2
TOTAL RESULT
---------- ---------- ---------- ---------- ---------- ---------- ---------priya
19
1
99
99
198 pass
SQL> update student set age='20' where name='priya'; 1 row updated.
ALTER: SQL> ALTER TABLE STUDENT ADD DOB DATE; Table altered. NAME
AGE
ROLLNO
MARK1
MARK2
TOTAL R ESULT
---------- ---------- ---------- ---------- ---------- ---------- ------DOB ---------hema pass
12
12
99
99
198
DROPPING A COLUMN: SQL> ALTER TABLE STUDENT DROP COLUMN DOB; Table altered. SQL> SELECT * FROM STUDENT; NAME
AGE
ROLLNO
MARK1
MARK2
TOTAL RESULT
---------- ---------- ---------- ---------- ---------- ---------- ----------
priya
20
1
99
99
prema
18
2
98
99
198
pass
197
pass
sumathi
20
3
96
96
192
pass
murugan
21
4
96
95
191
pass
98
97
195
pass
latha
18
5
sindhu
20
6
98
95
193
pass
saran
22
7
96
98
194
pass
madhu
22
9
99
amutha
26
10
99
hema
12
12
99
198
99
99
198
99
198
pass pass pass
10 rows selected.
MODIFY: SQL> ALTER TABLE STUDENT MODIFY RESULT CHAR(11); Table altered.
DELETE: SQL> DELETE FROM STUDENT WHERE AGE='12'; 1 row deleted. SQL> SELECT*FROM STUDENT; NAME
AGE
ROLLNO
MARK1
MARK2
TOTAL RESULT
---------- ---------- ---------- ---------- ---------- ---------- ----------priya
20
1
99
99
198
pass
prema
18
2
98
99
197
pass
sumathi
20
3
96
96
192
pass
murugan
21
4
96
95
191
pass
latha
18
5
98
97
195
pass
sindhu
20
6
98
95
193
pass
saran
22
7
96
98
194
pass
99
99
198
madhu
22
9
amutha
26
10
9 rows selected.
TRUNCATE: SQL> TRUNCATE TABLE EMPLOYEE; Table truncated. SQL> SELECT * FROM EMPLOYEE; no rows selected
DROP:
SQL> DROP TABLE EMPLOYEE; Table dropped. SQL> SELECT * FROM EMPLOYEE; SELECT * FROM EMPLOYEE *ERROR at line 1: ORA-00942: table or view does not exist
USAGE OF RELATIONAL OPERATORS: SQL> select * from employee_db;
NAME
SALARY
DA
99
99
198
pass pass
---------- ---------- ---------priya prema murugan latha
50000
12000
25000
10000
10100
1000
25006
1230
SQL> select * from employee_db where da>10000; NAME
SALARY
DA
---------- ---------- ---------priya
50000
12000
SQL> select * from employee_db where da>=10000;
NAME
SALARY
DA
---------- ---------- ---------priya prema
50000
12000
25000
10000
SQL> select * from employee_db where da<10000;
NAME
SALARY
DA
---------- ---------- ---------murugan latha
10100 25006
1000 1230
SQL> select * from employee_db where da>=10000 or name='latha';
NAME
SALARY
DA
---------- ---------- ---------priya prema latha
50000
12000
25000 25006
10000 1230
SQL> select * from employee_db where da>=10000 and name!='priya';
NAME
SALARY
DA
---------- ---------- ---------prema
25000
10000
SQL> select * from employee_db where salary between 10000 and 50000;
NAME
SALARY
DA
---------- ---------- ---------priya prema murugan latha
50000 25000 10100 25006
12000 10000 1000 1230
