DDL
QUESTIONS
1. Create a table student (roll no,name,address,phone no.,gender,branch,mark1,mark2).
2. Modify the table structure to include new column total marks
3. Modify the size of the address attribute to 20.
4. Delete the column gender and branch from the table.
5. Rename the column mark1 to m1.
6. Rename the table student to student1
7. Remove the table structure.
ANSWERS
1. create table student(rollno number(2),name varchar(20),address varchar(50),phoneno
number(10),gender varchar(10),branch varchar(10),mark1 number(20),mark2 number(20));
table created.
desc student;
2. alter table student add totalmarks number(20);
Table altered
desc student;
3. alter table student modify address varchar(20);
Table altered
desc student;
4. alter table student drop column gender;
alter table student drop column branch;
Table altered
desc student;
5. alter table student rename column mark1 to m1;
Table altered
desc student;
6. alter table student rename to student1;
Table altered
desc student;
7. drop table student1;
table dropped
DML
QUESTIONS
[Link] the table employees
Employee(empid,ename,eaddress,designation,department,salary,joindate)
2. Populate the employee table with 5 tuples.
3. Display the details of all employees.
4. Display name,designation and salary of all emloyees.
5. Find employee id of all employees whose salary is greater than 10,000.
6. Display the name and designation of all employees whose department is ‘cse’
7. Display the details of all employees whose name ends with ‘a’.
8. Display the average salary of employees of each department.
9. Find name and department of employees whose designation is ‘manager’.
10. Remove the details of employees whose salary is between 5000 and 7500.
11. Change the designation of all employees to asst manager whose current designation is executive
and who have joined before 1st Jan 2011
[Link] the name and increment of all employees .Assume 10% of increment.
QUERIES
1. create table employ(empid number(5),ename varchar(10),eaddr varchar(10),designation
varchar(20),dept varchar(10),salary number(10),joindate date);
table EMPLOY created.
2. insert into employ values(101,'anu','xyz','clerk','cse',10000,'08-apr-2012');
insert into employ values(102,'bijoy','abc','hr','me',6000,'05-dec-2007');
insert into employ values(103,'maya','pqr','manager','civil',25000,'10-feb-2010');
insert into employ values(104,'anish','cde','executive','ec',15000,'05-nov-2003');
insert into employ values(105,'jain','efg','manager','eee',7000,'01-jan-2010');
5 rows inserted.
3. select * from employ;
4. select ename,designation,salary from employ;
5. select empid from employ where salary>10000;
6. select ename,designation from employ where dept='cse';
7. select * from employ where ename like '%a';
8. select avg(salary),dept from employ group by dept;
9. select ename,dept from employ where designation='manager';
10. delete from employ where salary between 5000 and 7500;
2 rows deleted.
Select * from employ;
11.
update employ set designation='assistantmanager' where designation='executive' and joindate<'01-jan-
2011';
1 row updated
12. select ename,salary*0.10 from employ;
select ename,salary*0.10 "increment" from employ;
CONSTRAINS
QUESTIONS
1. Create a table department(dept no,dept name,staffno). Set underlined attribute as primary key
at column level.
2. Populate the table with following valueS
DEPTNO DEPTNAME STAFFNO
D1 S101 CS
D2 S110 EC
D1 S201 EEE
3. Create a table books (title,author). Set underlined attribute as primary key at table level.
4. Populate the table books with following values
Title Author
DBMS NAVATHE
C Dennis Richie
DBMS Korth
5. Create a table library(title,author,no of copies).set title as foriegn key at column level with
respect to table books.
6. Populate table library with following values
TITLE AUTHOR NO OF COPIES
C Dennis Richie 5
DBMS Navathe 10
OS Korth 20
7. Set title,author as primary key for the table library
8. Create table s1(rollno,name,dnum).set underlined attribute as primary key. Also set the
relation with table departmet(referencing clause should be included at table level)
9. List details of department
10. Populate the table s1 with following values
ROLLNO NAME PNO
10 Aswathy D1
20 Akash D3
30 Arun D2
11. Try to populate book table with values null,navathe
12. Create a table account(accno,amount). Set accno as primary key. Use check option to ensure
that amount does not fall below 250
13. Populate account table with following values
ACCNO AMT
147 1000
210 5000
777 100
623 5001
QUERIES
1. create table dept1(deptno varchar(5) primary key,deptname varchar(5),staffno varchar(10));
table DEPT1 created.
2. insert into dept1 values('D1','CS','s101');
insert into dept1 values('D2','EC','S110');
insert into dept1 values('D1','EE','S201');
1 rows inserted.
1 rows inserted.
Error report -
SQL Error: ORA-00001: unique constraint violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
3. create table books(title varchar(20),author varchar(20),primary key(title));
table BOOKS created.
4 . insert into books values('dbms','navathe');
insert into books values('c','dennis ritche');
insert into books values('dbms','korth');
1 rows inserted.
1 rows inserted.
Error report -
SQL Error: ORA-00001: unique constraint violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
5. create table library(title varchar(20) references books(title),author varchar(20),noofcopies
number(5));
table LIBRARY created.
6. insert into library values('c','dennis ritche',5);
insert into library values('dbms','navathe',10);
insert into library values('os','korth',20);
1 rows inserted.
1 rows inserted.
Error report -
SQL Error: ORA-02291: integrity constraint (MANEESHA.SYS_C008357) violated - parent key not
found
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
*Cause: A foreign key value has no matching primary key value.
*Action: Delete the foreign key or add a matching primary key.
7. alter table library modify (primary key(title,author));
table LIBRARY altered.
8. create table s1(rollno number(5) primary key,name varchar(10),dno varchar(5),foreign key(dno)
references dept1(deptno));
table S1 created.
9. select * from dept1;
10. insert into s1 values(10,'aswathy','D1');
insert into s1 values(20,'akash','D3');
insert into s1 values(30,'arun','D2');
1 rows inserted.
Error report -
SQL Error: ORA-02291: integrity constraint (MANEESHA.SYS_C008361) violated - parent key not
found
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
*Cause: A foreign key value has no matching primary key value.
*Action: Delete the foreign key or add a matching primary key.
1 rows inserted.
11. insert into books values(' ','navathe');
Error report -
SQL Error: ORA-01400: cannot insert NULL into ("MANEESHA"."BOOKS"."TITLE")
01400. 00000 - "cannot insert NULL into (%s)"
*Cause: An attempt was made to insert NULL into previously listed objects.
*Action: These objects cannot accept NULL values.
12. create table account(accno number(5) primary key,amt number(20) check (amt>=250));
table ACCOUNT created.
13. insert into account values(147,1000);
insert into account values(201,5000);
insert into account values(777,100);
insert into account values(623,5001);
1 rows inserted.
1 rows inserted.
Error starting at line : 40 in command -
insert into account values(777,100)
Error report -
SQL Error: ORA-02290: check constraint (MANEESHA.SYS_C008362) violated
02290. 00000 - "check constraint (%s.%s) violated"
*Cause: The values being inserted do not satisfy the named check
*Action: do not insert values that violate the constraint.
1 rows inserted.
select * from account;
AGGREGATE FUNCTIONS
QUESTIONS
1. Create a table ACCOUNTS with attributes ACCNO, CUSTOMERNAME, BRANCH, TYPE,
OPENINGDATE, CURRENTBALANCE
2. Retrieve average balance from the table
3. Find the total number of accounts
4. Find the name of the customer who have highest balance
QUERIES
1. create table accounts(accno varchar(5),name varchar(10),branchno varchar(5),type
varchar(5),opendate date,currbal number(10));
insert into accounts values('CA1','John','B1','CA','4-Feb-2016',10000);
insert into accounts values('CA3','Greeshma','B2','CA','3-May-2014',20000);
insert into accounts values('SB4','Liya','B3','SB','07-Apr-2014',40000);
insert into accounts values('SB1','Yadhu','B1','SB','08-Dec-2016',20000);
insert into accounts values('SB2','Rohit','B2','SB','07-Nov-2016',10000);
insert into accounts values('CA2','Rohit','B2','CA','03-Oct-2013',20000);
insert into accounts values('SB3','Linju','B3','SB','03-Sep-2012',20000);
7 rows inserted
select * from accounts;
2. select avg(currbal)from accounts;
3. select count(accno) from accounts;
4. select name from accounts where currbal=(select max(currbal) from accounts);
GROUPBY ORDERBY HAVING
Questions
1. Find the total number of accounts segregated on the basis of account type per branch
2. Find out the customer having more than one account type in the bank
3. Find out the number of accounts opened in each branch after 3/01/2015 only if the number of
accounts opened after 3/01/2015 exceed 1
4. Find out total number of accounts of each branch
5. Find out number of accounts at each branch in the order of branch number
Queries
1. select count(accno),branchno,type from accounts group by type,branchno;
2. select name from accounts group by branchno,name having (count(type)>1);
3. select count(accno) from accounts where opendate>'3-Jan-2015' group by branchno having
(count(accno)>1);
4. select branchno,count(accno) from accounts group by branchno;
[Link] branchno,count(accno) from accounts group by branchno order by branchno;
NESTED QUERIES
Questions
1. Find the total number of accounts segregated on the basis of account type per branch
2. Find out the customer having more than one account type in the bank
3. Find out the number of accounts opened in each branch after 3/01/2015 only if the number of
accounts opened after 3/01/2015 exceed 1
4. Find out total number of accounts of each branch
5. Find out number of accounts at each branch in the order of branch number
Queries
1. select count(accno),branchno,type from accounts group by type,branchno;
2. select name from accounts group by
branchno,name having (count(type)>1);
3. select count(accno) from accounts where opendate>'3-Jan-2015' group by branchno having
(count(accno)>1);
4. select branchno,count(accno) from accounts group by branchno;
[Link] branchno,count(accno) from accounts group by branchno order by branchno;
SET OPERATORS AND JOINS
QUESTIONS
1. Create the following table
a) book_details(ISBN, title, MRP, publisher name, author)
b) publisher (publisher_id, publisher name, city, state, country)
2. Populate the tables.
3. Display the details of all books.
4. Retrieve the details of all publishers.
5. List the name of books, price and city of publisher of all books.
6. List the details of books and their corresponding publisher details.
7. List all publishers, details of books published by each publisher.
8. List the name of publishers which have got entry either in book_details or publisher table.
9. List the name of publisher which have got entry in both tables.
10. List the name of publisher that have got entry in book_details but not in publisher table.
QUERIES
1. create table book_detail(ISBN number(5),TITLE
varchar(20),MRPnumber(5),PUBLISHER_NAME varchar(10),AUTHOR varchar(20));
table BOOK_DETAIL created.
create table publisher(publisher_id number(5),publisher_name varchar(20),city varchar(20),state
varchar(20),country varchar(20));
table PUBLISHER created.
2. insert into book_detail values(978,'DB',650,'PEARSON','NAVATHE');
insert into book_detail values(980,'OS',625,'WILEY','SILBERSCHATZ');
insert into book_detail values(920,'JAVA',649,'MCGRAWHILL','SCHILDT');
insert into book_detail values(989,'MICROPROCESSOR',720,'MCGRAWHILL','GAONKER');
insert into book_detail values(970,'ALGORITHMS',995,'MIT','COREMAN');
insert into publisher values(101,'PEARSON','LA','CALIFORNIA','USA');
insert into publisher values(102,'WILEY','SHEFFIELD','LONDON','UK');
insert into publisher values(103,'MCGRAWHILL','NYCITY','NY','USA');
insert into publisher values(104,'BAVARIA','BAVARIA','BERLIN','GERMANY');
3. select * from book_detail;
4. select * from publisher;
5. select [Link],[Link],[Link] from book_detail b left outer join publisher p on
b.publisher_name=p.publisher_name;
6. select * from book_detail b left outer join publisher p on b.publisher_name=p.publisher_name;
7. select * from publisher p right outer join book_detail b on b.publisher_name=p.publisher_name;
8. select
publisher_name from book_detail union select publisher_name from publisher;
9. select publisher_name from book_detail intersect select publisher_name from publisher;
10. select publisher_name from book_detail minus select publisher_name from publisher;
VIEWS
QUESTIONS
1. Create table product (pid,pname,unitprice,manufacturer,category,country).
2. Populate the table and display the details.
3. Create view v1 with pid, pname and category.
4. List the pname and country of all products whose category is ‘home appliances’ using views.
5. Populate v1 with values 501,XY505,mobilephone.
6. Modify pname as Xseries whose pid is 200.
7. Display the view to reflect the updation.
8. Modify the product table to set pid as primary key.
9. Create another view v2 with attributes pname,unit price and category.
10. Populate v2 with a row.
11. Delete the details of product whose pname is ‘Lenovo’.
12. Define another view v3 that contains employee id,ename,salary,dept id,and dept name.(Create
appropriate tables).
13. Modify the salary of all employees who belong to dept 10 by rupees 1000.
14. Modify the department table to set dept id as primary key.
15. Modify the table to set eid as primary and dept id as foreign key.
16. Insert a row to v3.
QUERIES
1. create table product(pid number(10),pname varchar(20),unitprice number(20),manufac
varchar(20),categ varchar(20),country varchar(20));
table PRODUCT created.
2. 5 rows inserted.
select * from product;
3. create view v1 as select pid,pname,categ from product;
view V1 created.
select * from v1;
4. select pname,country from v1 where categ='Home Appliances';
ORA-00904: "COUNTRY": invalid identifier
5. insert into v1 values(501,'XY505','Mobile Phone');
1 rows inserted.
select * from v1;
6. update v1 set pname='X Series' where pid=200;
1 rows updated.
7. select * from v1;
8. alter table product modify(primary key(pid));
table PRODUCT altered.
9. create view v2 as select pname,unitprice,categ from product;
view V2 created.
10. insert into v2 values('3D700',90001,'TV');
Error: These objects cannot accept NULL values
The previous row inserted to views made changes in parent table also. Therefore creating another
view causes fields with null values.
select * from v2;
11. delete from v2 where pname='Lenovo';
0 rows deleted
12. create table depart(dno number(10),dname varchar(20),mgrid number(10));
insert into depart values(10,'cse',2);
insert into depart values(20,'ece',11);
insert into depart values(30,'eee',3);
insert into depart values(40,'civil',7);
insert into depart values(50,'mech',6);
select * from depart;
select * from emplo:
create
view v3
as
select
[Link],[Link],[Link],[Link],[Link] from emplo e
left outer join depart d on [Link]=[Link];
view V3 created.
select * from v3;
13. update v3 set salary=salary+1000 where dno=10;
select * from v3;
Error: "cannot modify a column which maps to a non key-preserved table"
*Cause: An attempt was made to insert or update columns of a join view which
map to a non-key-preserved table.
*Action: Modify the underlying base tables directly.
14. alter table depart modify(primary key(dno));
table DEPART altered.
15. alter table emplo modify(primary key(eid),foreign key(dno) references depart(dno));
table EMPLO altered.
16. insert into v3 values(6,'Sabu',200,60,'manager');
SQL Error: ORA-01776: cannot modify more than one base table through a join view
01776. 00000 - "cannot modify more than one base table through a join view"
*Cause: Columns belonging to more than one underlying table were either
inserted into or updated.
*Action: Phrase the statement as two or more separate statements.