JSPM’s

RAJARSHI SHAHU COLLEGE OF ENGINEERING,

POLYTECHNIC
Department of Computer Engineering
Academic Year: 2024-25

UNIT-3 ( 18 Marks)
MSBTE Question bank

Sr.No Question Year Marks

1 Enlist four aggregate functions. W-18 2
SUM() any 4
AVG() functions
MAX()
MIN()
COUNT()
2 List four DDL commands with syntax. W-18 2
DDL commands
1. 1.Create
Syntax : create table <table_name>(Column_name1 datatype1,
column_name2
Datatype2,…Column_nameN DatatypeN);
2. Drop
Syntax: drop table <table_name>;
3. Desc
Syntax: describe <table_name>;
OR
Desc <table_name>
4. Truncate
Syntax: truncate table <table_name>;
5. Alter
Syntax: Alter table <table_name> add Column_name Datatype
(size);
3 Explain any four string functions with example. W-18
S-22,
S-23

27
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 i) Lower(char)-
Returns the input string with all letters in lower case.
Example: SQL>Select lower (‘RAJESH’) from dual;
Output: rajesh
ii) Upper(char)-
Returns the input string with all letters in upper case.
Example: SQL>Select upper (‘rajesh’) from dual;
Output: RAJESH
iii) Ltrim(char,set)-
It removes or trims from left of character string
. Example: SQL>Select Ltrim(‘university’,’univ’) from dual;
Output: ersity
iv) Rtrim(char,set)-
It removes or trims from right of character string.
Example: SQL>Select Rtrim(‘university’,’sity’) from dual;
Output: univer
v) Length(char)-
It returns length of character string.
Example: SQL> Select length(‘University’) from dual;
Output:10
vi) Concat(str1,str2,...)-
Returns the string that result from concatenating the arguments.
Example: Select Concat(‘employee’, ‘name’) from dual;
Output: employee name
vii) Lpad(str, len, padstr)-
Returns the string str, left-padded with the string padstr to a
length of len characters.
Example: Select lpad(ename,10.’*’) from emp where
empno=7782;
viii) Rpad(str,len,padstr)-
Returns the string str, right-padded with the string padstr to a
length of len characters.
Example: Select rpad(ename,10.’*’) from emp where
empno=7782
viii) Substr(Char,m,n)-
It returns a portion of char, beginning at a character m, n
character long.
Example: Select substr(‘College’,3,4) from dual;
Output: lleg
4 Explain joins in SQL with examples. W-18, 4
S-24

28
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 JOIN:
A SQL join is an instruction to combine data from two sets of
data (i.e. two tables). A
JOIN clause is used to combine rows from two or more tables,
based on a related column
between them. SQL Join types are as follows:
1) INNER JOIN or EQUI JOIN:
A join which is based on equalities is called equi join. In equi
join comparison
operator “=” is used to perform a Join.
Syntax:
SELECT
tablename.column1_name,tablename.column1_name
FROM table_name1,table_name2
where
table_name1.column_name=table_name2.column_name;
Example:
Select stud_info.stud_name, stud_info.branch_code,
branch_details.location
From stud_info, branch_details
Where Stud_info.branch_code=branch_details.branch_code;
2) SELF JOIN:
The SQL SELF JOIN is used to join a table to itself, as if the
table were two
tables, temporarily renaming at least one table in the SQL
statement.
Syntax:
SELECT a.column_name, b.column_name
FROM table1 a, table1 b
WHERE a.common_filed = b.common_field;
Example:
Select x.stud_name, y.stud_name
from stud_info x, stud_info y
Where x.leader= y.stud_id;

29
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 3) LEFT OUTER JOIN:
A left outer join retains all of the rows of the “left” table,
regardless of whether there is a
row that matches on the “right” table.
Syntax:
Select column1name,column2name
from table1name any_alias1 ,table2name any_alias2
on any_alias1.columnname(+) = any_alias2.columnname;
OR
Select column1name,column2name
from table1name left outer join table2name
on table1name.columnname= table2name.columnname;
Example:
select last_name, department_name
from employees e, departments d
on e.department_id(+) = d.department_id;
OR
select last_name, department_name
from employees left outer join departments
on employees.department_id = departments.department_id;

4) RIGHT OUTER JOIN:

A right outer join retains all of the rows of the “right” table,
regardless of
whether there is a row that matches on the “left” table.
Syntax:
Select column1name, column2name
from table1name any_alias1, table2name any_alias2
on any_alias1.columnname =any_alias2.columnname (+);
OR
Select column1name, column2name
from table1name any_alias1 right outer join table2 name
any_alias2
on any_alias1.columnname =any_alias2.columnname;
Example:
Select last_name,department_name from employees e,
departments d on
e.department_id = d.department_id(+);
OR
Select last_name, department_name
from employees e right outer join departments d
on e.department_id = d.department_id;
5) NON EQUI JOIN:
Non equi joins is used to return result from two or more tables
where exact join is
not possible.
Syntax:
Select aliasname.column1name, aliasname.column2name
from tablename alias
where <condition using range>;

30
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 For example:
In emp table and salgrade table. The salgrade table contains
grade and their low
salary and high salary. Suppose you want to find the grade of
employees based on
their salaries then you can use NON EQUI join.
Select e.empno, e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.lowsal and s.hisal;
4 Explain set operators with example W-18 4
Set operators combine the results of two component queries
into a single result. Queries
containing set operators are called as compound queries. Set
operators in SQL are
represented with following special keywords as: Union, Union
all, intersection & minus.
Consider data from two tables emp and employee as

1) Union: The Union of two or more sets contains all elements,

which are present in
either or both. Union works as or.
E.g. select ename from emp union select ename from
employee;
The output considering above data is :
Output.

5 Describe commit & rollback with syntax example W-18 4

Commit: Description
and syntax –
31
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 The COMMIT command saves all transactions to the database 1 Mark
since the last COMMIT example 1
or ROLLBACK command Mark for
The syntax: SQL> COMMIT; each
Or
COMMIT WORK;
Example :
SQL>Commit;
Rollback:
The ROLLBACK command is used to undo transactions that
have not already been
saved to the database.
The ROLLBACK command can only be used to undo
transactions since the last
COMMIT or ROLLBACK command was issued.
The syntax for ROLLBACK is:
ROLLBACK TO SAVEPOINT_NAME;
OR
ROLLBACK;
OR
ROLLBACK WORK;
Example:
SQL>ROLLBACK;
6 Consider the following schema Depositor (ACC_no, Name, W-18 6
PAN, Balance) Create a view on Depositor having attributes
(ACC_no, PAN) where balance is greater than 100000.
create view v1 Correct logic
as 3M, Correct
select ACC_No,PAN syntax :3M
from Depositor
where balance > 100000;
7 Consider the following database Employee(emp_id, W-18 6
emp_name, emp_city, emp_addr, emp_dept, join_date) i)
Display the emp_id, of employee who live in city ‘Pune’ or
‘Nagpur’. ii) Change employee name, ‘Aayush’ to ‘Aayan’. iii)
Display the total number of employee whose dept is 50.
i) Display the emp_id of employee who live in city ‘Pune’ or Each query :
‘Nagpur’ 2M
select emp_id from Employee
where emp_city=’Pune’ or emp_city=’Nagpur’
ii) Change the employee name ‘Ayush’ to ‘Ayan’
update Employee set emp_name=’Ayan’
where emp_name=’Ayush’
iii) Display the total number of employee whose dept is 50
Select count(*)from Employee where emp_dept=50;

8 Create a sequence i) Sequence name is Seq _ 1, Start with 1, W-18 6

increment by 1, minimum value 1, maximum value 20. ii) Use
a seq_1 to insert the values into table Student (ID Number (10),

32
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 Name char (20)); iii) Change the seq_ 1 max value 20 to 50. iv)
Drop the sequence.
i) create sequence Seq_1 start with 1 increment by 1 minvalue Query 1: 2M,
1 maxvalue Query 2: 2M,
20; Query 3 : 1M,
ii) insert into student values(Seq_1.nextval,’ABC’) Query 4 : 1M
iii) Alter sequence Seq_1 maxvalue 50;
iv) Drop sequence Seq_1

9 I)Create user ‘rahul’ W-18 6

ii) grant create ,select,insert,update,delete,drop priveileges to
‘rahul’
iii) removes the select privilege from user”Rahul”
(i) create user Rahul identified by rahul1234;
(ii)
1) assuming table Employee for granting permissions to user
‘Rahul’ for select,
insert, update and delete privilege)
Grant select, insert,update,delete on employee to Rahul;
2) for create and drop privilege which are system privileges not
specific to any
object such as table
Grant connect, resource, DBA to Rahul;
iii) (assuming table Employee for revoking permissions to user
‘Rahul’)
Revoke select on Employee from Rahul

1 List DCL commands. S-19 2

1. GRANT
2. REVOKE
2 Explain difference between delete and truncate command with S-19 4
example.

33
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 3 List the SQL ope S-19 4
rations and explain range searching operation ‘between’ and
pattern matching operator ‘like’ with example.
Types of SQL operators :
1) SQL Arithmetic Operators
2) SQL Comparison Operators
3) SQL Logical Operators
Arithmetic operators are used to perform arithmetic operations List of
on operators :
numbers. They are +,-,*, / and %. 2M,
Comparison operators are used in between two variables to between
compare operator :
their values. They are <,>,<=,>=,=,!=or <>,!< and !>.' 1M,
Logical operators are used for the Boolean results in sql queries Like
for operator :
comparison of values from the attributes of the tables. Eg: Any, 1M
Exists,
All, Like, Between, In etc.
Between operator: The BETWEEN operator is used to search
for
values that are within a set of values, given the minimum value
and the maximum value inclusive of both the limits.
Eg: select * from emp where salary between 40000 and 50000;

34
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 This will results in rows from emp table where salary falls in
the range of 40000 to 50000.
Like operator :
The LIKE operator is used to compare a value to similar values
using
wildcard operators. It uses two wild characters as ‘%’ and ‘_’
where ‘%’
represents all characters of the pattern and ‘_’ represents one
single character from pattern.
Eg :
Select ename from emp where ename like ‘S%’;
This will return all employee names starting with ‘S’.
Select ename from emp where ename like ‘_a%;
This will return all employee names whose second character is
‘a’
4 Write syntax for creating synonyms with example. S-19 2
Syntax to create synonym: 1 M for
CREATE SYNONYM SYNONYM_name correct
FOR Table_name; syntax, 1 M
Example to create synonym: for
CREATE SYNONYM offices correct
FOR locations example

5 Write and explain syntax for creating view with example. S-19 4
A view contains rows and columns, just like a real table. The
fields in a view are fields from one or more real tables in the
database.
View has two types:
1. Simple view: The fields in a view are fields from one table
in the
database.
2. Complex view: The fields in a view are fields from more
than one table in the database. You can add SQL functions,
WHERE, and JOIN statements to a view and present the data
as if the data were coming
from different table.
CREATE VIEW Syntax
Create view view_name As
Select column1, column2…
From table_name
Where condition ;
Example
Create view mumbai_customers AS
Select customer_name,contact_name
From customers
Where city=’Mumbai’;
6 Explain any four aggregate functions with example. S-19 4
W-23

35
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 An aggregate function is a function where the values of
multiple rows are grouped together as input on certain criteria
to form a single value of
more significant meaning.
Aggregate functions are :
1) Count()
2) Sum()
3) Avg()
4) Min()
5) Max()
1. Count () - 1) It returns number of rows from the given table
if no attribute is mentioned.
2) If some attribute is mentioned, it gives total number of not
null valfor that attribute.
Eg :Select count(*) from emp;
Returns total number of records from emp table.
1) Select count(telephone) from emp;
Returns total number of employees having telephone numbers.
2. Sum() - It give total of all values from a numeric attribute of
the given table,
Eg :Select sum(salary) from emp;
Returns total salary drawn of all employees from the emp table.
3. Avg () - It gives average of all the numeric values of the
given attribute from the table.
Eg :Select Avg(salary) from emp;
Returns average salary of employees from emp table.
4. Min () - It gives minimum of all the values of the numeric
given attribute from the table.
Eg :Select Min(salary) from emp;
Returns minimum salary value from emp table,
5. Max () - It gives maximum of all the values of the numeric
given attribute from the table.
Eg :Select Max(salary) from emp;
retunes maximum salary value from emp tableues

7 Create simple and composite index. Write command to drop S-19 6

above index.
Create simple index Simple index
Syntax: Create index index_name on <tablename><column 2M,
name>; Composite
(OR) index: 2M
E.g.: Create index idx_empno on employee (empno); Drop index
Create composite index: 2M
Syntax: Create index index_name on (Note: Either
<tablename><Column_name1, syntax or
Column_name2>; example can
(OR) be
E.g.: Create index idx_ename_eno on employee (ename, considered.
empno); Any other
Drop Index: example

36
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 Syntax: Drop index <index_name>; allowed.)
(OR)
E.g. (Assuming idx_empno created on employee table)
Drop index idx_empno;
8 Consider the table Student (name, marks, dept, age, place, S-19 6
phone, birthdate) Write SQL query for following : (i) To list
students having place as ‘Pune’ or ‘Jalgaon’. (ii) To list
students having same department (dept) as that of ‘Rachana’.
(iii) To change marks of ‘Rahul’ from 81 to 96. (iv) To list
student name and marks from ‘Computer’ dept. (v) To list
student name who have marks less than 40. (vi) To list students
who are not from ‘Mumbai’.
select name from Student where place= ‘Pune’ or Each Correct
place=’Jalgaon’; Query : 1M
(OR)
select name from Students where place in(‘Pune’,‘Jalgaon’);
ii) select name from Student where dept=(select dept from
student where
name=’Rachana’);
iii) update Student set marks=96 where name= ‘Rahul’;
v)select name,marks from Student where dept=‘Computer’;
iv) select name from Student where marks<40;
v) select * from Student where place != ‘Mumbai’;
9 i) Write a command to create table student (RNo., name, S-19 6
marks, dept.) with proper datatype and RNo as primary key.
(ii) Write a command to create and drop sequence.
i) create table student
(
RNO number(5) constraint student _RNOpk primary key,
name varchar2(20),
marks number(4),
dept varchar2(20)
);
(OR)
create table student
(
RNO number(5) ,
name varchar2(20),
marks number(4),
dept varchar2(20),
constraint student_RNO_pk primary key(RNO),
);
ii) Create Sequence:
Create sequence <seq_name>
Start with [initial value]
Increment by [value]
Minvalue [minimum value]
Maxvalue [maximum value]
[cycle/no cycle]
[{cache value / No cache}]

37
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 [{order / No order}];
(OR)
(Creating sequence for Employee number of emp table.)
Create sequence emp_eno_seq
start with 1
increment by 1
maxvalue 100
no cycle
no cache;
Drop sequence:
Drop sequence<Sequence Name>;
(OR)
Drop sequence emp_eno_seq

10 Write SQL command for following : (i) Create user (ii) Grant S-19 6
privileges to user (iii) Remove privileges from user
i) Create user Each correct
CREATE USER <username> IDENTIFIED BY <password>; command:
(OR) 2M
CREATE USER RAJ IDENTIFIED BY RAJ123; (Note: Either
ii) Grant privileges to user. syntax or
GRANT <privilege list> ON <relation name or view name> example can
TO<user list>; be
(OR) considered.
(assuming table Employee for granting permissions to user Any other
‘RAJ’ example
for select, insert, update and delete privilege) allowed
GRANT SELECT, INSERT,UPDATE,DELETE ON
EMPLOYEE TO RAJ;
Iii) Remove privileges from user.
REVOKE <privilege list> ON <relation name or view name >
FROM <user list>;
(OR)
(assuming table Employee for revoking permissions to user
‘RAJ)
REVOKE SELECT, INSERT,UPDATE,DELETE ON
EMPLOYEE FROM RAJ;

1 List any four string functions in SQL. W-19 2

Initcap(String) – converts first character of string to upper case four
Upper(String) – converts the string to upper case string
Lower(String) – converts string to lower case function
Length(String) – returns the number of characters in the string s ½M
Instr(String, sub) – returns the location of the substring each
Lpad(String,char,number) – returns the string left padded with
the
character specified to a total of length specified.
Rpad(String,char,number) – returns the string right padded
with the

38
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 character specified to a total of length specified. Ltrim(String)
-removes white space or other specified characters from the left
end of the string Rtrim(String)--removes white space or other
specified characters from the right end of the string
Replace(String, char,char) – replace all occurrence of a
substring by another substring Substring(String,number) –
extracts substring from the string Translate(String,char,char) –
replace all occurrence of characters by other characters
2 Enlist DML commands. W-19 2
- used to insert new row into table Any 2
- used to delete a row from the table comman ds
– used to modify data in the table. 1M each
Select – used to view data from a table.
3 Explain any four set operators in SQL with example. W-19 4
Set operators combine the results of two component queries 1M each
into a for
single result. Queries containing set operators are called as explanat
compound queries. Set operators in SQL are represented with ion of
following special operator
keywords as: Union, Union all, intersection & minus. s with
1) Union: The Union of two or more sets contains all elements, example
which are present in either or both. Union works as or. The
duplicates of both the tables will appear only once.
E.g. select ename from emp1 union select ename from emp2;
2) Union all: The Union of 2 or more sets contains all elements,
which are present in both, including duplicates.
E.g. select ename from emp1 union all select ename from
emp2;
3) Intersection: The intersection of two sets includes elements
which are present in both.
E.g. select ename from emp1 intersect select ename from
emp2;
4) Minus: The minus of two sets includes elements from set1
minus
elements of set2.
E.g. select ename from emp1 minus select ename from emp2;

4 Describe create & alter command with syntax & example. W-19 4
1) The SQL CREATE TABLE statement is used to create a Each
new comman
table. d 2M
Syntax
The basic syntax of the CREATE TABLE statement is as
follows –
CREATE TABLE table_name
(
column1 datatype (size),
column2 datatype(size),
column3 datatype(size),
....
39
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 );
Example:
CREATE TABLE Persons
(
PersonIDnumber(10),
LastNamevarchar2(20),
FirstNamevarchar2(20),
Address varchar2(20),
City varchar2(20)
);
2)The ALTER TABLE statement is used to add, delete, or
modify columns in an existing table.
The ALTER TABLE statement is also used to add and drop
various constraints on an existing table.
i) To add Columns in a table
Syntax:ALTER TABLE table_name
ADD column_name datatype;
Example
ALTER TABLE Customers
ADD Email varchar2(20);
ii) To delete a column in a table
ALTER TABLE table_name
DROP COLUMN column_name;
Example
ALTER TABLE Customers
DROP COLUMN Email;
iii) To modify a column in a table
Syntax:
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
Example
ALTER TABLE Customers
MODIFY COLUMN customeridnumeric(10);
iv) To add Constraints in A table
Syntax:
ALTER TABLE table_name
ADD constraint constraintname (column_name);
Example:
ALTER TABLE Customers
ADD constraint primary key(CustomerID);
5 Describe Views and write a command to create view. W-19 4
A view is a virtual table based on the result set of the SQL Explana
statement. tion 3M
The fields in a view are fields from one or more than one table General
in the database. SQL functions, where, join statements can be syntax/
added to a view and the data in it can be presented as if it were example
from one table. The database engine recreates the data, using 1M
the view’s SQL statement, every time a user queries a view. A
view can be updated using the create or replace view command.
For deleting a view, drop query can be used.

40
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 General syntax to create a view:
create view viewname as select query.
Eg:
create view vw_student as select stud_id, stud_name,ssc_per
from
student;
6 Define index. Explain it’s types. W-19 4
An Index is a schema object. It is used by the oracle server to Definitio
improve the speed of retrieval of the rows from a table .Indexes n 1M
are of two types based on number of columns included in the Each
index. type
The types of index are: 1½M
1) Simple index: An index created on a single column of table
is
called as simple index
Syntax:
SQL>Create Index index_name on tablename(attribute);
Example:Create index emp_index on emp(empno);
2) Composite Index: An index created on more than one
column is called composite index.
Syntax:
SQL>Create Index index_name on
tablename(attribute1,attribute2);
Example: Create index emp_index on emp(empno,ename);

7 Write a command to create table student (rollno, Stud_name, W-19 6

branch, class, DOB, City, Contact_no) and write down queries
for following : (i) Insert one row into the table (ii) Save the data
(iii) Insert second row into the table (iv) Undo the insertion of
second row (v) Create save point S1 . (vi) Insert one row into
the table.
SQL>Create table student( Each
Rollno number(5), correct
Stud_name char(10, Query
branch varchar(10), ½M
class varchar(10), each
DOB date,
city varchar(15),
Contact_no number(12)
);
(i) Insert one row into the table:
SQL>Insert into student
values(1,‟Ram‟,‟CO‟,‟FirstYear‟,‟12-
jun-2001‟,‟Pune‟,98576867)
(ii) Save the data:
SQL> commit;
( OR )
SQL> commit work;
(iii) Insert second row into the table:

41
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 SQL>Insert into student
values(2,‟Raj‟,‟CO‟,‟FirstYear‟,‟22-
Sep2002‟,‟Mumbai‟,98896863)
(iv)Undo the insertion of second row:
SQL> rollback;
( OR)
SQL> rollback work;
(v)Create savepoint s1:
SQL>Savepoint s1;
(vi) insert one row into the table:
SQL>Insert into student
values(3,‟Beena‟,‟CO‟,‟FirstYear‟,‟30-
Dec-2002‟,‟Mumbai‟,97846455)

8 Consider following schema : EMP (empno, deptno, ename, W-19 6

salary, designation, join_date, DOB, dept_location). Write
down SQL queries for following : (i) Display employees name
& number in decreasing order of salary. (ii) Display employee
name & employee number whose designation is Manager. (iii)
Display age of employees with ename. (iv) Display total salary
of all employees. (v) Display employee names having deptno
as 20 and dept_location is Mumbai. (vi) Display name of
employee who earned lowest salary.
(i) Display employees name &number in descending order of
salary:
SQL> select ename,empno from EMP order by salary desc;
(ii) Display employee name & employee number whose
designation
is Manager.
SQL> select ename,empno from EMP where
designation=‟Manager‟;
(iii) Display age of employees with ename
SQL>select round ( (sysdate - DOB ) /365, 0 ) as “age”,ename
from EMP;
OR
select months_between(TRUNC(sysdate),DOB)/12 as “age”
,ename from EMP ;
(**Note consider any other logic also)
(iv)Display total salary of all employees.
SQL> select sum(salary) from EMP;
(v) Display employee names having deptno as 20 and
dept_location is
Mumbai.
SQL> select enamefrom EMP where deptno=20 and
dept_location=‟Mumbai‟;
(vi) Display name of employee who earned lowest salary
SQL> select ename from EMP where salary=(select
min(salary)
from EMP);

42
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 9 Consider the structure for book table as Book-Master (bookid, W-19 6
bookname, author, no_of copies, price) Write down SQL
queries for following (i) Write a command to create
Book_master table. (ii) Get authorwise list of all books. (iii)
Display all books whose price is between ` 500 & ` 800. (iv)
Display all books with details whose name start with ‘D’. (v)
Display all books whose price is above ` 700. (vi) Display all
books whose number of copies are less than 10.
(i) Write a command to create Book_Master table table. Each
SQL>Create table Book-Master( correct
bookid number(5), Query
bookname char(10), 1M
authorvarchar(20),
no_of_copiesnumber(10),
price number(10,2)
);
(ii) Get authorwise list of all books.
SQL>Select sum(no_of copies) from Book_Master group by
author;
(iii) Display all books whose price is between Rs.500 & Rs. 800
SQL> Select * from Book_Master where price between 500
and
800;
OR
SQL> Select * from Book_Master where price >=500 and
price<=800;
(iv) Display all books with details whose name start with ‘D’
SQL> Select bookname from Book_Master where bookname
like
„D%‟;
(v) Display all books whose price is above Rs. 700
SQL>Select * from Book_Master where price >700;
(vi) Display all books whose number of copies are less than 10
SQL>Select * from Book_Master where no_of_copies<10;

1 State the use of Avg function with example S-22 2

The AVG() function returns the average value of a numeric
column
Ex: Find the average price of all products:

SELECT AVG(Price)
FROM Products;
2 List any four data types in sql S-22 2
Numeric data types such as: INT , TINYINT , BIGINT ,
FLOAT , REAL , etc.
Date and Time data types such as: DATE , TIME , DATETIME
, etc.
Character and String data types such as: CHAR , VARCHAR
, TEXT , etc.

43
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 Unicode character string data types such as: NCHAR ,
NVARCHAR , NTEXT , etc
3 Explain any two DDL commands along with example. S-22 4

Data Definition Language(DDL) is a subset of SQL and a part

of DBMS(Database Management System). DDL consist of
Commands to commands like CREATE, ALTER,
TRUNCATE and DROP. These commands are used to create
or modify the tables in SQL.
DDL Commands :
In this section, We will cover the following DDL commands as
follows.
Create
Alter
truncate
drop
Rename
Let’s discuss it one by one.
Command-1 :
CREATE :
This command is used to create a new table in SQL. The user
has to give information like table name, column names, and
their datatypes.
Syntax –
CREATE TABLE table_name
(
column_1 datatype,
column_2 datatype,
column_3 datatype,
....
);
Example –
We need to create a table for storing Student information of a
particular College. Create syntax would be as below.
CREATE TABLE Student_info
(
College_Id number(2),
College_name varchar(30),
Branch varchar(10)
);
Command-2 :
ALTER :
This command is used to add, delete or change columns in the
existing table. The user needs to know the existing table name
and can do add, delete or modify tasks easily.

Syntax –
Syntax to add a column to an existing table.

ALTER TABLE table_name

44
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 ADD column_name datatype;
Example –
In our Student_info table, we want to add a new column for
CGPA. The syntax would be as below as follows.

ALTER TABLE Student_info

ADD CGPA number;
Command-3 :
TRUNCATE :
This command is used to remove all rows from the table, but
the structure of the table still exists.

Syntax –
Syntax to remove an existing table.

TRUNCATE TABLE table_name;

Example –
The College Authority wants to remove the details of all
students for new batches but wants to keep the table structure.
The command they can use is as follows.

TRUNCATE TABLE Student_info;

Command-4 :
DROP :
This command is used to remove an existing table along with
its structure from the Database.

Syntax –
Syntax to drop an existing table.

DROP TABLE table_name;

Example –
If the College Authority wants to change their Database by
deleting the Student_info Table.
DROP TABLE Student_info;
Command -5
RENAME:
It is possible to change name of table with or without data in it
using simple RENAME command.
We can rename any table object at any point of time.

Syntax –
RENAME TABLE <Table Name> To <New_Table_Name>;
Example:
If you want to change the name of the table from Employee to
Emp we can use rename command as
RENAME TABLE Employee To EMP; syntax for i) Create
Index ii) Drop Index
4 Describe the concept of view with example. State its purpose. S-22 4

45
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 A view is a virtual table based on the result set of the SQL
statement.
The fields in a view are fields from one or more than one table
in the
database. SQL functions, where, join statements can be added
to a
view and the data in it can be presented as if it were from one
table.
The database engine recreates the data, using the view’s SQL
statement, every time a user queries a view. A view can be
updated
using the create or replace view command. For deleting a view,
drop
query can be used.
General syntax to create a view:
create view viewname as select query.
Eg:
create view vw_student as select stud_id, stud_name,ssc_per
from
student;
5 i) Create user ‘RAM’. ii) Grant create, select, insert, update, S-22 6
delete privileges to user ‘RAM’. iii) Remove update privilege
from user ‘RAM’
I)CREATE USER RAM IDENTIFIED BY RAM123;
ii) CREATE USER RAJ IDENTIFIED BY RAJ123;
ii) Grant privileges to user.
GRANT <privilege list> ON <relation name or view name>
TO<user list>;
(OR)
(assuming table Employee for granting permissions to user
‘RAM’
for select, insert, update and delete privilege)
GRANT SELECT, INSERT,UPDATE,DELETE ON
EMPLOYEE TO RAM;
Iii) Remove privileges from user.
REVOKE <privilege list> ON <relation name or view name >
FROM <user list>;
(OR)
(assuming table Employee for revoking permissions to user
‘RAM)
REVOKE SELECT, INSERT,UPDATE,DELETE ON
EMPLOYEE FROM RAM;

6 Consider the schema Customer (Cust-id, Cust_name, S-22 6

Cust_addr, Cust_city) i) Create a view on Customer (Cust_id,
Cust_name) where Cust_city is ‘Pune’ ii) Create a sequence on
Cust_id
CREATE VIEW Syntax:
create view view_name As
select column1, column2…
46
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 from table_name
where condition ;
DROP VIEW Syntax:
DROP VIEW view_name;
7 i) Create table Student (S_id, S_name, S_addr, S_marks) with S-22 6
proper data type and size. ii) Insert row (5, ‘ABC’, ‘RRRRR’,
79) into student table. iii) Update marks of student 85 where
S_id is 5.

8 Consider the following table employee (Emp_id, Emp_name, S-22 6

Emp_age) i) Display details of employees whose age is less
than 30. ii) Display details of employees whose age is in
between the range 30 to 60. iii) Display total number of
employee whose age is 60. iv) Display names of employees
whose name starts with 'S'. v) Display details of employees
whose name end with 'd'. vi) Display details of employees
whose age is greater than 50 and whose name contain 'e'.

1 Write syntax for creating and dropping views. W-22 2

CREATE VIEW Syntax:
create view view_name As
select column1, column2…
from table_name
where condition ;
DROP VIEW Syntax:
DROP VIEW view_name;
2 Write syntax for creating and Renaming a table. W-22 2
Syntax of the CREATE TABLE statement is as follows: Correct
CREATE TABLE table_name Syntax
( of Create
column1 datatype (size), Table:
column2 datatype(size), 1M,
column3 datatype(size), Correct
.... Syntax
); of Rename
Syntax of RENAME TABLE statement is as follows: Table: 1M
RENAME old_table _name To new_table_name ;

3 Enlist arithmetic and logical SQL operators. W-22 2

SQL Arithmetic Operators: Addition Operator (+) SQL
Subtraction Operator (-) arithmetic
Multiplication Operator (+) operators:
Division Operator (-) 1M, SQL
Modulus Operator (+) logical
SQL Logical Operators: operators:
ALL operator 1M
AND operator
OR operator
BETWEEN operator
47
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 IN operator
NOT operator
ANY operator
LIKE operator
4 Write SQL queries for following: i) Create table student with W-22 4
following attributes using suitable data types. Roll no., as
primary key, name, marks as not null and city. ii) Add column
Date of Birth in above student table. iii) Increase the size of
attribute name by 10 in above student table. iv) Change name
of Student table to stud.
(
CREATE TABLE Student
( Rollno int PRIMARY KEY,
name varchar(30) NOT NULL,
marks int NOT NULL,
city varchar(20)
);
ii)
ALTER TABLE student
ADD DateofBirth varchar(20);
iii)
ALTER TABLE student
Modify name varchar(40);
iv)
RENAME Student to Stud;

5 State the use of group by and order by clauses. W-22 4

Group by Clause: 2 M=group
Group by clause is used to collect the data as multiple records by
and group them to 2 M=order by
produce the result.
Syntax:
1. SELECT column_name, function(column_name)
2. FROM table_name
3. WHERE condition
4. GROUP BY column_name;
Ex: select avg(sal) from emp_details group by deptno;
Order by Clause:
To view the data in sorted order, the order by clause is used.
By default, the data is sorted in ascending order.
Syntax:
5. SELECT expressions
6. FROM tables
7. [WHERE conditions]
8. ORDER BY expression [ ASC | DESC ];
Ex: select deptno from emp_details order by deptno;
Ex: select deptno from emp_details order by deptno desc; (for
descending order)
6 Write and Explain the syntax for creating, Altering and W-22 6
dropping the sequence.
48
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 Syntax for creating sequence: 2M
CREATE SEQUENCE sequence_name
START WITH initial_value
INCREMENT BY increment_value
MINVALUE minimum value
MAXVALUE maximum value
CYCLE|NOCYCLE ;
.where as:sequence_name: Name of the sequence.
initial_value: starting value from where the sequence starts.
Initial_value should be greater than or equal to minimum value
and less than equal to maximum value.increment_value: Value
by which sequence will increment itself. Increment_value can
be positive or negative.minimum_value: Minimum value of the 2M
sequence.maximum_value: Maximum value of the sequence.
cycle: When sequence reaches its set_limit
it starts from beginning.nocycle: An exception will be thrown
if sequence exceeds its max_value.
Example:
CREATE SEQUENCE sequence_1
start with 1
increment by 1
minvalue 0
maxvalue 100
cycle;
Alter sequence:
Syntax:
alter sequence <sequence_name> maxvalue <number>;
Alter sequence can change the maxvalue in the sequence 2M
created.
Dropping sequence:
Syntax:
drop sequence <sequence_name>;
To drop the sequence the DROP command is used.
7 Write and Explain the syntax for creating and dropping indexes W-22 4
with an example.
CREATE INDEX Correct
The CREATE INDEX command is used to create indexes in explanation
tables. It allows duplicate values. Indexes are used to retrieve Of
data from the database very fast. The DROP
users cannot see the indexes; they are just used to speed up INDEX
searches/queries. Syntax with
Syntax: example: 2M
CREATE INDEX index_name
ON table_name (column1, column2, ...);
Example:
The following SQL creates an index named id_firstname on the
FirstName column in
the Student table:
CREATE INDEX id_firstname
ON Student (FirstName);

49
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 DROP INDEX
The DROP INDEX statement is used to delete an index in a
table.
Syntax:
DROP INDEX index_name ON table_name;
Example:
DROP INDEX id_firstname ON Student;
8 State the use of group by and order by clauses. W-22 4
Group by Clause: 2 M=
Group by clause is used to collect the data as multiple records group by
and group them to produce the result. 2 M=
Syntax: order by
1. SELECT column_name, function(column_name)
2. FROM table_name
3. WHERE condition
4. GROUP BY column_name;
Ex: select avg(sal) from emp_details group by deptno;
Order by Clause:
To view the data in sorted order, the order by clause is used.
By default, the data is sorted in ascending order.
Syntax:
5. SELECT expressions
6. FROM tables
7. [WHERE conditions]
8. ORDER BY expression [ ASC | DESC ];
Ex: select deptno from emp_details order by deptno;
Ex: select deptno from emp_details order by deptno desc; (for
descending order
9 Write the SQL queries for following EMP table. Emp (empno, W-22 6
deptno, ename, salary, designation, city.) i) Display average
salary of all employees. ii) Display names of employees who
stay in Mumbai or Pune. iii) Set the salary of employee
'Ramesh' to 50000. iv)Display names of employees whose
salaries are less than 50000. v) Remove the Record of
employees whose deptno is 10. vi) Remove the column deptno
from EMP table.
i. select avg(salary) from emp; 1 M each
ii. select ename from emp where city=’Mumbai’ or
city=’Pune’;
iii. update emp set salary=50000 where ename=’Ramesh’;
iv. select ename from emp where salary<50000;
v. delete from emp where deptno=10;
vi. alter table emp drop column deptno;
10 Write SQL queries for following. Consider table stud (roll no, W-22 6
name, subl, sub2, sub3) i) Display names of student who got
minimum mark in subl. ii) Display names of students who got
above 40 marks in sub2. iii) Display count of Students failed in
sub2. iv) Display average marks of subl of all students. v)
Display names of students whose name start with 'A' by

50
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 arranging them in ascending order of subl marks. vi) Display
student name whose name ends with h' and subject 2 marks ar6
i. select name from stud where sub1= (select min(sub1) from 1 M each
stud);
ii. select name from stud where sub2>40;
iii. select count(*) from stud where sub2<40;
iv. select avg(sub1) from stud;
v. select name from stud where name like 'A%' order by sub1;
vi. select name from stud where name like '%h' and sub2
between 60 and 75;
11 Write SQL queries for following. 1) Create user named 'user1' W-22 6
having Password '1234 ii) Assign 'insert' and update' Privilege
to 'userl". ii) Remove update Privilege assigned to the userl. iv)
Assign the resource Pemission to userl.
i. create user user1 identified by 1234; 1½M
ii. grant insert, update on system.emp to user1; each
iii. revoke update on system.emp from user1;
iv. grant create session, unlimited tablespace, create table to
user1;

1 List any two DDL Commands With syntax S-23 2

DDL commands Any two DDL
1.Create command s
Syntax : with syntax
create table (Column_name1 datatype1(size1), column_name2 1M each
Datatype 2(size2),
Column_nameN DatatypeN(size n));
2. Drop
Syntax:
drop table < table_name>;
3. Desc
Syntax: describe < table_name> ;
OR
Desc < table_name> ;
4. Truncate
Syntax: truncate table < table_name> ;
5. Alter
Syntax: i) Alter table < table_name> add (Column_name
Datatype (size));
ii) Alter table <table_name> drop(Column_name);
iii) Alter table <table_name> rename column(old
column_name to new
column_name);

2 Describe grant and Revoke commands with example S-23 4

Grant: This command is used to give permission to user to do Each
operations on the command
other user‟s object. with
example
51
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 Syntax: Grant <object privileges> on <object name> to 2M
<User/Public/Role>[with
grant option];
Example: Grant select, update on emp to user1;
Revoke: This command is used to withdraw the privilege that
has been granted to
a user.
Syntax: Revoke <object privileges> on <object name> from
<<User/Public/Role>;
Example: Revoke select, update on emp from user1;

3 Explain savcpoint and rollback command with example S-23 4

Savepoint : This command is used to temporarily save a Explanati
transaction so that you on of
can rollback to that point whenever required. savepoint
Syntax : with
Savepointsavepoint_name; example
Eg :savepoint s1; 2M
Rollback : Explanati
This command restores the database to last committed state. It on of
is also used with rollback
SAVEPOINT command to jump to a savepoint in an ongoing with
transaction. example
If we have used the UPDATE command to make some changes 2M
into the database,
and realise that those changes were not required, then we can
use the
ROLLBACK command to rollback those changes, if they were
not committed
using the COMMIT command.
Syntax :
Rollback [tosavepoint_name];
Eg Rollback to s1;
Example :
INSERT INTO class VALUES(5, 'Rahul');
COMMIT;
UPDATE class SET name = 'Abhijit' WHERE id = '5';
SAVEPOINT s1;
INSERT INTO class VALUES(6, 'Chris');
Rollback to s1;
This will undone the transaction of last insert, i.e. till savepoint
s1;

4 Explain any DML Commands with syntax and example S-23 4

DML commands:
- used to insert new row into table
- used to delete a row from the table
– used to modify data in the table.
– used to view data from a table.

52
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 1) Insert : For adding records in table:
We use Insert into command for adding/inserting data into
table.
Syntax:
Insert into <table name> values (value1, value2, value3…);
Ex:
insert into Student values(1,‟xyz‟,‟12-Jan-1990‟,88,10);
OR
Ex:Insert into
Studentvalues(&R_No,‟&Name‟,‟&DOB‟,&Percentage,&D
_No);
2) Update : For modifying records in table
We use update command for modifying data of a table.
Syntax:
Update <table name>set columnname1=expression where
<condition>;
Ex:
update Student set DOB=‟22-feb-1995‟ where R_No=3;
3) Delete : For deleting records from table:
We use delete command for deleting data of a table.
Syntax:
Delete from <table name>[where <condition>];
Ex:
delete from Student where R_No=2;
4) Select : For viewing the records from table
We use select command for displaying or viewing the records
from a table or
tables.
Syntax:
Select <*/ list of columns> from <table name> [where
<condition> ] ;
Ex :
Select * from Student;
Select R_N0,Name,DOB from Student where Percentage > 80;
5 Explain any two types of join with example. S-23 6
JOIN: Explanati
A SQL join is an instruction to combine data from two sets of on of any
data (i.e. two two types
tables). A with
JOIN clause is used to combine rows from two or more tables, example
based on a 3M each
related column between them. SQL Join types are as follows:
1) EQUI JOIN:
A join which is based on equalities is called equi join. In equi
join comparison
operator “=” is used to perform a Join.
Syntax:
SELECT
tablename.column1_name,tablename.column2_name

53
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 FROM table_name1,table_name2
where
table_name1.column_name=table_name2.column_name;
Example:
Select stud_info.stud_name, stud_info.branch_code,
branch_details.location
From stud_info, branch_details
Where Stud_info.branch_code=branch_details.branch_code;
2) SELF JOIN:
The SQL SELF JOIN is used to join a table to itself, as if the
table were two
tables,temporarily renaming at least one table in the SQL
statement.
Syntax:
SELECT a.column_name, b.column_name
FROM table1 a, table1 b
WHERE a.common_filed = b.common_field;
Example:
Select x.stud_name, y.stud_name
from stud_info x, stud_info y
Where x.leader= y.stud_id;
3) INNER JOIN:The INNER JOIN command returns rows that
have
matching values in both tables.
Syntax:
Select column1_name,column2_name
from table1name INNER JOIN table2name
on table1name.columnname= table2name.columnname;
Example:
Select last_name, department_name
from employees inner join departments
on employees.department_id = departments.department_id;
4) LEFT OUTER JOIN:
A left outer join retains all of the rows of the “left” table,
regardless of whether there is a row that matches on the “right”
table.
Syntax:
Select column1_name,column2_name
from table1name any_alias1 ,table2name any_alias2
on any_alias1.columnname(+) = any_alias2.columnname;
OR
Select column1_name,column2_name from table1name left
outer join table2name
on table1name.columnname= table2name.columnname;
Example:
select last_name, department_name
from employees e, departments d
on e.department_id(+) = d.department_id;
OR
select last_name, department_name

54
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 from employees left outer join departments
on employees.department_id = departments.department_id;
5) RIGHT OUTER JOIN:
A right outer join retains all of the rows of the “right” table,
regardless of whether there is a row that matches on the “left”
table.
Syntax:
Select column1name, column2name from table1name
any_alias1, table2name any_alias2
on any_alias1.columnname =any_alias2.columnname (+);
OR
Select column1name, column2name from table1name
any_alias1 right outer join table2 name any_alias2
on any_alias1.columnname =any_alias2.columnname;
6 Explain view with example S-23 4
A view is a logical table based on another table.View logically Explanati
represents subsets of data from one or more tables .A view on
contains no data of it‟s own but is like a window through which 2M
data from tables can be viewed or changed .The tables on which Example
the view is based are called base tables.SQL functions, where, 2M
join statements can be added to a view and the data in it
can be presented as if it were from one table.The database
engine recreates the data, using the view‟s SQL statement,
every time a user queries a view. A view can be updated using
the create or replace view command. For deleting a view,
dropquery can be used.General syntax to create a view:
create view viewname as select query.
Example:
create view vw_student as select stud_id, stud_name,ssc_per
from
student;

1 List any four DML Commands With syntax W-23 2

1) Insert - used to insert new row into table
INSERT INTO table_name (column1, column2, ...) VALUES
(value1, value2,...);
OR
INSERT INTO table_name VALUES (value1, value2, value3,
...);
2) Delete- used to delete a row from the table
DELETE FROM table_name WHERE condition;
3) Update – used to modify data in the table.
UPDATE table_name SET column1 = value1, column2 =
value2, ...
WHERE condition;
4) Select – used to view data from a table.
SELECT column1, column2, ...FROM table_name
2 Enlist types of SQL Joins W-23 2
1) Inner Join Any 4 join
55
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 2) Left Outer Join 1/2 M each
3) Right Outer Join
4) Full Outer Join
5) Cross Join
6) Self Join
3 Explain pattern matching operators like; and range searching W-23 4
operator ‘between’ with example
Like operator: Proper
The LIKE operator is used to compare a value to similar values Explanation
using wildcard operators. 1 M each,
It uses two wild characters as ‘%’ and ‘_’ where ‘%’ represents Example 1
all characters of the M each
pattern and ‘_’ represents one single character from pattern.
Eg :
Select ename from emp where ename like ‘S%’;
This will return all employee names starting with ‘S’.
Select ename from emp where ename like ‘_a%;
This will return all employee names whose second character is
‘a’.
Between operator: The BETWEEN operator is used to search
for values that are within a
set of values, given the minimum value and the maximum
value inclusive of both the
limits.
Eg: select * from emp where salary between 40000 and 50000;
This will results in rows from emp table where salary falls in
the range
of 40000 to 50000.
4 Explain commit and rollback with syntax and example W-23 4
Commit: Commit:
The COMMIT command saves all transactions to the database Description
since the last COMMIT or and syntax –
ROLLBACK command. 1M
If you want to save all the commands which are executed in a example
transaction, then just after 1M
completing the transaction, you have to execute the commit
command. This command will
save all the commands which are executed on a table.
Furthermore, once the commit command is executed in the
database, we cannot regain its
previous states in which it was earlier before the execution of
the first statement. Rollback:
The syntax: SQL> COMMIT; Description
Or and syntax –
COMMIT WORK; 1M
Example: example
SQL>Commit; 1M
Rollback
The ROLLBACK command is used to undo transactions that
have not already been saved

56
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 to the database.
The ROLLBACK command can only be used to undo
transactions since the last
COMMIT or ROLLBACK command was issued.
The syntax for ROLLBACK is:
ROLLBACK TO SAVEPOINT_NAME;
OR
ROLLBACK;
OR
ROLLBACK WORK;
Example:
SQL>ROLLBACK;
5 Write and explain create, update and drop view commands W-23 4
A view is a Virtual Table that is based on the result set of an Create
SQL query. command
Create View: 2M (syntax
A view is created with the CREATE VIEW statement. 1M,
Syntax: explanation
Create view view_name As ½ M,
Select column1, column2… example ½
From table_name M)
Where condition; Update
Example command
Create view mumbai_customers AS 1M (syntax
Select customer_name, contact_name ½ M,
From customers example ½
Where city=’Mumbai’; M)
Update View: Drop1M
A view can be updated with the CREATE OR REPLACE (syntax
VIEW statement. ½ M,
Syntax: example ½
CREATE OR REPLACE VIEW view_name AS M)
SELECT column1, column2, ...
FROM table_name WHERE condition;
Example:
The following SQL adds the "City" column to the "USA
Customers" view:
CREATE VIEW [USA Customers] AS
SELECT CustomerID, CustomerName, City
FROM Customers WHERE Country = ‘USA’;
Drop view:
A view can be deleted with the DROP VIEW statement.
Syntax:
DROP VIEW view_name;
Example:
DROP VIEW [USA Customers];
6 Explain cursor with syntax for declaring ,opening and closing W-23 4
a cursor

57
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 A cursor is a temporary work area created in system memory Cursor
when an SQL statement is executed. A cursor is a set of rows Explanation
together with a pointer that identifies a current row. It 1M
is a database object to retrieve data from result set on row at a Declaration
time. Implicit cursor: these types of cursors are generated and of cursor
used by the system during the manipulation of a DML query. 1M
An implicit cursor is also generated by the system when a Open cursor
single row is selected by a SELECT command. Programmers 1M
cannot control the implicit cursors. Closing
Explicit cursor: this type of cursor is created by the user when cursor 1M
the select command returns more than one row, and only one
row is to be processed at a time. An explicit
cursor can move from one row to another in a result set. An
explicit cursor uses a pointer that holds the record of a row.
1) Declaring cursor:
It defines the cursor with a name and the associated SELECT
statement.
Syntax
CURSOR name IS SELECT statement;
2) Open the cursor: It is used to allocate memory for the cursor
and make it easy to fetchthe rows returned by the SQL
statements into it.
Syntax
OPEN cursor_name;
3) Close the cursor:
It is used to release the allocated memory. The following
syntax is used to close the
above-opened cursors.
Syntax:
Close cursor_name;
7 Write the SQL queries for following EMP table. Emp (empno, W-23 6
deptno, ename, salary, designation, city.) i)list employees with
having alphabate ‘A’ as second letter in their name
II) set salary of all project leader to 50,000
III) display average salary of all employess
Iv) display employee-id of employees who live in city ‘pune’
or nagpur
V) Display employee name ,salary and city from ‘hr’
department who having salary greater than 70,000
Vi)display details of all employee who’s salary same as that of
salary of shashank
1. select * from Emp where emp_name like ‘_A%’; 1M each
2. update Emp set salary=50000 where designation=’project
leader’;
3. select avg(salary) from Emp;
4. select emp_id from Emp where city= ‘Pune’ or
city=’Nagpur’;
5. select emp_name,salary,city from Emp where dept=’HR’
and salary > 70000;

58
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 6. select * from Emp where salary in (select salary from Emp
where
emp_name=’Shashank’);
8 Consider following schema: W-23 6
Bookmaster (bookid,bookname,author,no _of copies,price)
write down sql queries for following :
I) Write a query to create table Book _master table
II) Write a command to create composite Index on book master
table
III) Write a command to drop above index
IV) Display all books whose number of copies are less than 10
V) Display all books whose price is above rs 700
VI) Create synoym for relation book master as book
information
1. create table Book_Master(book_id number(4,0), book_name 1M each
varchar(50), author
varchar(50), no_of_copies(2,0), price number(6,2));
2. create index composite_book_master on
Book_Master(book_id, book_name);
3. drop index composite_book_master;
4. select book_name,author from Book_Master order by price
desc;
5. select * from Book_Master where no_of_copies >50;
6. create or replace public synonym Book_Information for
Book_Master;
9 Write a command to create table Customer (Custo_id, W-23 6
Cust_name, address, Contact_no, City). And write down the
queries for the following: i) Insert one row into table. ii) Save
the data. iii) Insert Second row into the table. iv) Create save
point S1. v) Insert one row into the table
1. create table Customer(Custo_id number(2,0), Cust_name 1 M each
varchar(30), address
varchar(50), Contact_no varchar(10), city varchar(30));
2. insert into Customer values(1,’Ramesh’,’Ganesh Peth’,
‘9123456780’,’Pune’);
3. commit;
4. insert into Customer values (2,’Suresh’,’Sadashiv Peth’,
‘8123456790’,’Pune’);
5. savepoint s1;
6. insert into Customer values (3,’Rajesh’,’Shukrawar Peth’,
‘8912345670’,’Pune’);

1 List date and time function S-24 2

ADDTIME(exp1,exp2) This function adds the two expressions
exp1 and exp2 and displays the result. ...
CURTIME() This returns the current time of the system from
the current time zone in the format HH:MM:SS. ...
DAYOFMONTH(date) ...
MONTH(date)
2 Explain any set operators in SQL with example. S-24 4
59
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 Set operators combine the results of two component queries
into a
single result. Queries containing set operators are called as
compound
queries. Set operators in SQL are represented with following
special
keywords as: Union, Union all, intersection & minus.
1) Union: The Union of two or more sets contains all elements,
which are present in either or both. Union works as or. The
duplicates
of both the tables will appear only once.
E.g. select ename from emp1 union select ename from emp2;
2) Union all: The Union of 2 or more sets contains all elements,
which are present in both, including duplicates.
E.g. select ename from emp1 union all select ename from
emp2;
3) Intersection: The intersection of two sets includes elements
which
are present in both.
E.g. select ename from emp1 intersect select ename from
emp2;
4) Minus: The minus of two sets includes elements from set1
minus
elements of set2.
E.g. select ename from emp1 minus select ename from emp2
3 Write a query using group by clause(use student database) S-24 4
The GROUP BY Statement in SQL is used to arrange identical
data into groups with the help of some functions. i.e. if a
particular column has the same values in different rows then it
will arrange these rows in a group.

Features
GROUP BY clause is used with the SELECT statement.
In the query, the GROUP BY clause is placed after the
WHERE clause.
In the query, the GROUP BY clause is placed before the
ORDER BY clause if used.
In the query, the Group BY clause is placed before the Having
clause.
Place condition in the having clause.
Syntax:

SELECT column1, function_name(column2)

FROM table_name
WHERE condition

GROUP BY column1, column2

ORDER BY column1, column2;

60
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 CREATE TABLE student (
name VARCHAR(50),
year INT,
subject VARCHAR(50)
);
INSERT INTO student (name, year, subject) VALUES
('Alice', 1, 'Mathematics'),
('Bob', 2, 'English'),
('Charlie', 3, 'Science'),
('David', 1, 'Mathematics'),
('Emily', 2, 'English'),
('Frank', 3, 'Science')

4 Write and explain syntax for creating and dropping synonyms S-24 4
with example
A synonym is an alternative name for objects such as tables,
views, sequences, stored procedures, and other database
objects.
You generally use synonyms when you are granting access to
an object from another schema and you don't want the users to
have to worry about knowing which schema owns the object.
Create Synonym (or Replace)
You may wish to create a synonym so that users do not have to
prefix the table name with the schema name when using the
table in a query.

Syntax
The syntax to create a synonym in Oracle is:
CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema .]
synonym_name
FOR [schema .] object_name [@ dblink];
OR REPLACE
Allows you to recreate the synonym (if it already exists)
without having to issue a DROP synonym command.
PUBLIC
It means that the synonym is a public synonym and is
accessible to all users. Remember though that the user must
first have the appropriate privileges to the object to use the
synonym.
schema
The appropriate schema. If this phrase is omitted, Oracle
assumes that you are referring to your own schema.
object_name
The name of the object for which you are creating the synonym.
It can be one of the following:
table
view
sequence
stored procedure
function

61
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 package
materialized view
java class schema object
user-defined object
synonym
Example
Let's look at an example of how to create a synonym in Oracle.

For example:

CREATE PUBLIC SYNONYM suppliers

FOR app.suppliers;
This first CREATE SYNONYM example demonstrates how to
create a synonym called suppliers. Now, users of other schemas
can reference the table called suppliers without having to prefix
the table name with the schema named app. For example:

SELECT *
FROM suppliers;
If this synonym already existed and you wanted to redefine it,
you could always use the OR REPLACE phrase as follows:

CREATE OR REPLACE PUBLIC SYNONYM suppliers

FOR app.suppliers;
Drop synonym
Once a synonym has been created in Oracle, you might at some
point need to drop the synonym.

Syntax
The syntax to drop a synonym in Oracle is:

DROP [PUBLIC] SYNONYM [schema .] synonym_name

[force];

5 Difference between delete and truncate command with S-24 4

example

62
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 6 Explain any four joins if SQL Example S-24 4
JOIN:
A SQL join is an instruction to combine data from two sets of
data (i.e. two
tables). A JOIN clause is used to combine rows from two or
more tables, based on a related column between them. SQL
Join types are as follows:
1) EQUI JOIN:
A join which is based on equalities is called equi join. In equi
join comparison
operator “=” is used to perform a Join.
Syntax:
SELECTtablename.column1_name,tablename.column2_name
FROM table_name1,table_name2
Where
table_name1.column_name=table_name2.column_name;
Example:
Select stud_info.stud_name, stud_info.branch_code,
branch_details.location
From stud_info, branch_details
Where Stud_info.branch_code=branch_details.branch_code;
2) SELF JOIN:
The SQL SELF JOIN is used to join a table to itself, as if the
table were two

63
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 tables,temporarily renaming at least one table in the SQL
statement.
Syntax:
SELECT a.column_name, b.column_name
FROM table1 a, table1 b
WHERE a.common_filed = b.common_field;
Example:
Select x.stud_name, y.stud_name
from stud_info x, stud_info y
Where x.leader= y.stud_id;
3) INNER JOIN:The INNER JOIN command returns rows that
have
matching values in both tables.
Syntax:
Select column1_name,column2_name
from table1name INNER JOIN table2name
on table1name.columnname= table2name.columnname;
Example:
Select last_name, department_name
from employees inner join departments
on employees.department_id = departments.department_id;
4) LEFT OUTER JOIN:
A left outer join retains all of the rows of the “left” table,
regardless of whether
there is a row that matches on the “right” table.
Syntax:
Select column1_name,column2_name
from table1name any_alias1 ,table2name any_alias2
on any_alias1.columnname(+) = any_alias2.columnname;
OR
Select column1_name,column2_name
from table1name left outer join table2name
on table1name.columnname= table2name.columnname;
Example:
select last_name, department_name
from employees e, departments d
on e.department_id(+) = d.department_id;
OR
select last_name, department_name
from employees left outer join departments
on employees.department_id = departments.department_id;
5) RIGHT OUTER JOIN:
A right outer join retains all of the rows of the “right” table,
regardless of
whether there is a row that matches on the “left” table.
Syntax:
Select column1name, column2name
from table1name any_alias1, table2name any_alias2
on any_alias1.columnname =any_alias2.columnname (+);
OR

64
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 Select column1name, column2name
from table1name any_alias1 right outer join table2 name
any_alias2
on any_alias1.columnname =any_alias2.columnname;
7 I) Write a query to create table emp- S-24 6
details(EMP_ID,name,dept) with proper data types and
Emp_ID as primary key and name as NOT NULL constrain
II) Perform all DML command on above created table

8 Create simple and composite index write command to drop S-24 6

index
CREATE INDEX
The CREATE INDEX command is used to create indexes in
tables. It allows
duplicate values. Indexes are used to retrieve data from the
database very fast. The
users cannot see the indexes; they are just used to speed up
searches/queries.
Syntax:
CREATE INDEX index_name
ON table_name (column1, column2, ...);
Example:
The following SQL creates an index named id_firstname on the
FirstName column in
the Student table:
CREATE INDEX id_firstname
ON Student (FirstName);
DROP INDEX
The DROP INDEX statement is used to delete an index in a
table.
Syntax:
DROP INDEX index_name ON table_name;
Example:
DROP INDEX id_firstname ON Student;
9 Consider following schema: S-24 6
Bookmaster (bookid,bookname,author,no _of copies,price)
write down sql queries for following :
VII) Write a command to create Boo _master table
65
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE
 VIII) Increase the price of all books by 20%
IX) Display all books whose price ranges from rs 500 to rs 800.
X) Display all books whose number of copies are less than 10
XI) Display all books whose price is above rs 700
XII) Display all books with details whose name start with ‘D’.

10 S-24 6
WRITE SQL COMMAND FOR Following
I) Create user
II) Grant privileges to user
III) Remove privileges to user
i) Create user
CREATE USER <username> IDENTIFIED BY <password>;
(OR)
CREATE USER RAJ IDENTIFIED BY RAJ123;
ii) Grant privileges to user.
GRANT <privilege list> ON <relation name or view name>
TO<user list>;
(OR)
(assuming table Employee for granting permissions to user
‘RAJ’
for select, insert, update and delete privilege)
GRANT SELECT, INSERT,UPDATE,DELETE ON
EMPLOYEE TO RAJ;
Iii) Remove privileges from user.
REVOKE <privilege list> ON <relation name or view name >
FROM <user list>;
(OR)
66
RSCOEP COMPUTER ENGINEERING DEPARTMENT MRS.S.S.GHULE