RDBMS UNIT 3 Notes

MODEL QUESTION AND ANSWERS UNITWISE AND MARKWISE UNIT –3 (2 & 4 MARKS QUESTIONS)
1) Explain Datatypes in SQL. (Each: ½ Mark, Considered any 4 Data Types)
Ans: Data types in SQL:
1. Char(size): This data type is used to store strings values of fixed length.
The size in brackets determines the number of characters the cell can hold.
It can hold maximum 255 characters.
2. Varchar2(size): This data type is used to store variable length alphanumeric data.
The maximum this data type can hold up to 4000 characters.
3. Date: This data type is used to represent date and time.
4. Number(p,s): This data type is used to store fixed or floating point numbers.
P is the precision & S specifies scale.
The maximum precision is 38 digits.
5. Long: This data type is used to store variable length character strings containing up to 2 GB.
6. Raw/Long Raw: The RAW/LONG RAW data types are used to store binary data, such as digitized picture or image.
RAW data type can have a maximum length of 255 bytes.
LONG RAW data type can contain up to 2 GB.

2) List any four DDL commands. (Consider any four DDL commands – 1/2 Mark each)
Ans: DDL Commands:
1. Create
2. Alter
3. Drop
4. Rename
5. Desc
6. Truncate
3) List DCL commands any four. (Each Commands: ½ Mark)
Ans: DCL Commands:
1. GRANT
2. REVOKE
3. COMMIT
4. ROLLBACK
5. SAVE POINT
Note: Any four commands can be considered

4) List any four DML commands.(For each command – ½ Mark)
Ans:
List of DML commands:
1. Select
2. Insert
3. Update
4. Delete
5) List different string functions in SQL. [Any 4 string functions. ½ mark each]
Ans:- String functions: inticap, lower, upper, ltrim, rtrim, translate, replace, substr, lpad, rpad, length, concatenation.
6) Define the term “Transaction” in SQL. [2 Marks for correct definition]
Ans: A transaction can be defined as a unit of program execution that access and updates data
items.
OR
A sequence of operations with the database which represents one meaningful activity is called “Transaction”.
OR
A single logical unit of work which is the collection of several operations is called a Transaction.

7) State use of ‘%’ character in string operations.(Explanation – 2 Marks)
Ans: ‘%’ is used in select command with ‘like’ operator .
‘%’ matches all characters.
e.g. Select ename from emp where ename like ‘A%’;
In this it will give all ename’s having first character as ‘A’.

8) Explain database languages with its two types. (For each Database Language: 2 Marks)
Ans: Database Languages:
a) Data Definition Language(DDL):
A database scheme is specified by a set of definitions which are expressed by a special language called a data definition language (DDL). The result of compilation of DDL statements is a set of tables which are stored in a special file called data dictionary  (or directory). A data directory is a file that contains metadata. DDL commands are create, rename, alter, drop, truncate, desc etc.
b) Data Manipulation Language(DML):
A data manipulation language (DML) is a language that enables users to access or manipulates data as organized by the appropriate data model. Using DML data can be accessed in the following ways:
• The retrieval of information stored in the database.
• The insertion of new information into database.
• The deletion of information from the database.
• The modification of data stored in the database.
There are basically two types.
 Procedural DMLs require a user to specify what data is needed and how to get it.
 Nonprocedural DMLs require a user to specify what data is needed without specifying how to get it.
DML commands are insert, update, delete & call.
c) Data Control Language(DCL):
It is the component of SQL statement that control access to data and to database.

DCL commands are used to enforce database security in a multiple user database
environment.Two types of DCL commands are GRANT and REVOKE, also
COMMIT,
ROLLBACK and SAVEPOINT used to provide the security. Only database administrator’s or owners of the database object can provide/remove privileges on a database object.
OR
9) Explain
(i) Data definition language (ii) Data manipulation language
(iii) Data Control language (iv) Transaction control language
[DDL-2 Marks,DML-2 Marks,DCL-2 Marks, TCL-2 Marks ]
Ans:-
i) Data Definition Language (DDL) commands provide means for defining relation schema,
deleting relations, creating indices, views and modifying relation schemas.
E.g. CREATE TABLE/VIEW/INDEX to create a table/view/index in the database
DROP TABLE to remove a table from the database
ALTER TABLE to add, modify or remove columns from a table in the database
RENAME to change the name of table.
ii) Data Manipulation Language (DML) commands manipulates the data i.e. they perform
the operations of insertion, deletion, updating and selection of data.
E.g. SELECT to select rows of data from a table
INSERT to insert rows of data into a table
UPDATE to change rows of data in a table
DELETE to remove rows of data from a table
iii) Data Control language (DCL) :-This component of the SQL language is used to create privileges to allow users access to, and manipulation of, the database.
There are two main commands:
GRANT to grant a privilege to a user
REVOKE to revoke (remove) a privilege from a user
iv) Transaction control language (TCL):- Transaction Control (TCL) statements are used to
manage the changes made by DML statements.
COMMIT – To make the changes permanent in the database
SAVEPOINT – saving point in a transaction to which you can later roll back
ROLLBACK – Restore database to original since the last COMMIT

10) Give the syntax of create & RENAME command with example.
(Each Command Syntax with example: 2 Marks)
Ans: Syntax of create command:
Create table <tableName>
(
ColumnName1 datatype(size),
ColumnName2 datatype(size),
……….
ColumnNameN datatype(size)
);
Example:
Create table employee
(
empno number(4),
ename varchar2(25),
deptno number(5)
);
Syntax of Rename command:
RENAME <oldtable_name> TO <newtable_name>;
Example:
RENAME employee TO employee_details;

11) Explain following commands with syntax and example (i)ALTER (ii)UPDATE
[Alter command 2 Marks, Update command 2 Marks]
Ans:-
i)Alter table command is used to add new column to the table or to modify the column
width and data type of the column or to drop any particular column of the table.
 Syntax: alter table <table name> add(column _name datatype(size));
 E.g. alter table emp add (dob date);
 Syntax: alter table <table _name>modify (column_name datatype(size));
 E.g. alter table emp modify (ename varchar2(30));
ii) Update: Update command is used to modify the existing data of any relation.
 Syntax: Update table_name set column_name1=expression1>;
 E.g. : update emp set bonus=2000;
 Syntax: Update table_name set column_name1=expression1> where <search
condition>;
 E.g. : update emp set bonus=1000 where designation= ‘Programmer’;

12) How to apply NOT NULL constraint at the time of table creation? Give syntax.
(For correct syntax – 2 Marks)
Ans:
Syntax:
create table<table name>
(
column_name1 datatype(size),
column_name2 datatype(size) not null,
……………….,
column_name_n datatype(size)
);
Example:
create table emp
(
eno number(10),
ename varchar2(20) not null
);
13) Attempt any four of the following: Marks 16 a) Explain domain integrity constraint
with example. (Explanation -2 Marks, Example -1 Mark each)
Ans:
It is a type of integrity constraint.
Domain integrity constraint is used to maintain the value according to user specification.
It has two types:
a) Not null constraint:
When not null constraint is assigned on a column or set of column, then it will not allow null values.
Syntax: Create table <tablename>(column1 datatype(size),column2
datatype(size) not null);
Example: Create table Emp (EmpId number(4),Empname varchar(20) not null);
b) Check constraint:
It defines condition that each row must satisfy.
Single column can have multiple check conditions.
Syntax: Create table <tablename>(column1 datatype(size),column2
datatype(size) check (logical expression));
Example: Create table Emp (EmpId number(4), Empname varchar(20),sal
number(20) check (sal>4000));

14) Write syntax of insert command. Demonstrate with suitable example.
(Any one Syntax -2 Marks, Any one example-2 Marks)
Ans: It is DML statement/command. Used to insert or add data into table.
Syntax1: syntax for insert command without defining attributes
Insert into <Table_name> values (value1,value2,…..);
NIT POLYTECHNIC, NAGPUR UNIT-3 RDM NOTES
NOTES BY:- VIKAS DUDHE(9561277162) Page 6
Syntax2: syntax for insert command with defining attributes
Insert into <Table_name>(col1,col2,…) values (value1,value2,…..);
Syntax3: To insert more than one row.
Insert into <Table_name> values(&attribute1, ‘&attribute2’,…..&attribute n);
Example: Consider table “persons”

 

Last_Name First_name  Address  City
 Varma  Ram  Airoli  Navi mumbai

Example for both syntax and its output
Example1: Insert into persons values (‘Bhosale’, ‘Ajit’, ‘Bapat Road’, ‘pune’);
Example2: Insert into persons (Last_Name, First_name, Address, City)
values (‘Suryawanshi’, ‘Savita’, ‘Meera road’, ‘Raigad’);
Example3: Insert into persons values (‘&Last_Name’, ‘&First_name’, ‘&Address’,
‘&City’);
Output:

 Last_Name First_name Address City
 Bhosale  Ajit  Bapat Road  pune
 Suryawanshi  Savita  Meera road  Raigad
 Varma  Ram  Airoli  Navi mumbai

 

15) Explain following commands with syntax and example
(i) CREATE TABLE (ii) INSERT. [For each 2 Marks]
Ans:-
(i) Create table: It’s a DDL statement of SQL and is used to create a table in thedatabase.
It creates an empty structure of the table.
Syntax: Create table < table name> ( column1 datatype (size), column2 datatype (size), column3 datatype (size),……);
Example: Create table employee ( empno number(5), empname varchar2(20),Salary number(8,2));
This statement will create a table ‘employee’ with the structure containing 3 fields as empno with size for accepting integer data as 5, empname with maximum 20 characters and salary containing float values upto 2 decimal places.

(ii) Insert : It’s a DML statement of SQL and is used to insert data into columns of the
table.
Syntax : Insert into <tablename> [column1,column2, column3,….] values (value1,value2, value3,…);
Column names in the syntax are compulsory in case where there is a
variation in number of column names and/or sequence of values.
Example : Consider employee schema as {empno, empname, salary} then
1) Insert into employee values(101, ’abc’,12000.50);
2) Insert into employee (empno,empname) values(102,’xyz’);

16) What are the four ways to insert a record in a table?(Each Syntax or example- 1 M)
Ans:
Insert is a DML statement used to insert or add data into table.
1. Syntax1: syntax for insert command without defining attributes
Insert into <Table_name> values (value1,value2,…..);
2. Syntax2: syntax for insert command with defining attributes
Insert into <Table_name> (col1,col2,.…) values (value1,value2,…..);
3. Syntax3: To insert values for selected attributes in a table.
Insert into <Table_name> (col1,col2) values (value1,value2);
4. Syntax4: To insert more than one row.
Insert into <Table_name>values(&attribute1, &attribute2,…,&attribute_n);
OR
Example:
Consider table “persons”

Last_Name First_name Address  City
 Varma  Ram  Airoli  Navimumbai

Example for both syntax and its output
Example1:
Insert into persons values (‘Bhosale’, ‘Ajit’, ‘Bapat Road’, ‘pune’);
Example2:
Insert into persons (Last_Name, First_name, Address, City) values
(‘Suryawanshi’, ‘Savita’, ‘Meera road’, ‘Raigad’);
Example3:
Insert into persons (Last_Name, First_name) values (‘Suryawanshi’, ‘Savita’);
Example4:
Insert into persons values (‘&Last_Name’, ‘&First_name’, ‘&Address’, ‘&City’);
Output:

Untitled

17) What is the use of GRANT and REVOKE?(GRANT-2 Marks, REVOKE -2Marks)
Ans:
Grant: This command is used to give permission to user to do operations on the other user’s object.
Syntax: Grant<object privileges>on<object name>to<username>[with grant option] ;
Example: Grant select,update on emp to user1;
Revoke: This command is used to withdraw the privileges that has been granted to a user.
Syntax: Revoke <object privileges>on<object name>from <username> ;
Example: Revoke select, update on emp from user1;

18) Explain DROP & DELETE commands with syntax. State the difference between them. (Explanation with Syntax: 3 Marks, Difference: 1 Marks)
Ans: DROP AND DELETE COMMANDS:
DROP Command:
The DROP command removes a table from the database.
All the tables’ rows, indexes and privileges will also be removed.
No DML triggers will be fired.
The operation cannot be rolled back.
Syntax: DROP TABLE <table name>;
DELETE Command:
The DELETE command is used to remove rows from a table.
A WHERE clause can be used to only remove some rows.
If no WHERE condition is specified, all rows will be removed.
After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.
Syntax: DELETE from <table name>;
Difference:
DROP is a DDL commands, whereas DELETE is a DML command.
As such, DELETE operations can be rolled back (undone),
while DROP operation cannot be rolled back.

19) Explain the difference between DROP and TRUNCATE with example.
(Any 4 valid differences – 1 Mark each OR Explanation of DROP and TRUNCATE – 2
Marks each)
Ans:

Untitled

 

20) How to use COMMIT, SAVE POINT, ROLLBACK commands.
( Explanation – 3Marks, Example – 1Mark)
Ans:
Commit:
The COMMIT command is used to save changes invoked by a transaction to the database.
The COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command.
The syntax for COMMIT command is as follows: COMMIT;
Savepoint:
A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.
The syntax for SAVEPOINT command is as follows:
SAVEPOINT SAVEPOINT_NAME;
e.g. SAVEPOINT SV1;
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 rolling back to a SAVEPOINT is as follows:
ROLLBACK TO SAVEPOINT_NAME;
e.g. ROLLBACK TO sv1;

21) Describe savepoint and rollback commands with example.
(Explanation of Commands -2 Marks, Example – 2 Marks )
Ans:
Savepoint: Savepoints define breakpoints for the transaction to unable partial rollback.
Savepoints are treated as marker to divide lengthy transaction to smaller one.
Syntax: savepoint <savepoint_name>;
Rollback: The rollback command can be used to undo the changes done in current transaction.
We can either rollback the entire transaction or till a particular savepoint transaction can be rolled back.
If savepoint is given then rollback will be done till the savepoint.
Syntax: rollback; or rollback [to < savepoint>];
Example: Update emp set empid=124; Savepoint ss1;
Delete from emp where job=’çlerk’;
Savepoint ss2;
Rollback to ss1;
22) With example explain „select‟ clause and „where‟ clause.
[Select: example 1 Mark and explanation 1 Mark, where: example 1 Mark and
explanation 1 Mark]
Ans:
1) The SELECT statement is used to select data from a database.
The result is stored in a result table.
SELECT Syntax:
i)Select columnname(s) from tablename;
This syntax will display only specified columns in the list.
ii) select * from tablename;
This syntax will display all the columns in the table.
SELECT Example:
i)Select stud_id,stud_name from stud_info;
ii) select * from stud_info;
2) The WHERE Clause is used to extract only those records that fulfill a specified condition.
WHERE Syntax:
SELECT column_name(s) FROM table_name WHERE column_name=rowvalue;
Example: SELECT * FROM stud_info WHERE stud_name=’Ameya';

23) Consider the following schema.
STUDENT (Name, Mark ,Age, Place, Phone, Birth date)
Write SQL queries for following: (For each query: 1 Mark)
i) To list name of student who do not have phone number.
ii) To list student from Nashik & Pune.
iii) To change mark of Monika to 88 instead of 80.
iv) To list the student from Amit’s age group.
Ans:
i) Select Name from Student Where Phone IS NULL;
ii) Select Name from Student Where Place= ‘Nashik’ or Place= ‘Pune’;
OR
Select Name from Student Where Place in(‘Nashik’, ‘Pune’);
iii) Update Student Set Mark=88 Where Name= ‘Monika’;
iv) Select Name from Student Where Age=(Select Age from Student Where Name= ‘Amit’);
24) Given-
Employee (EMP_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE,
DEPARTMENT)Write SQL queries for
i) Get FIRST_NAME, LAST_NAME from employee table.
ii) Get unique DEPARTMENT from employee table.
iii) Get FIRST_NAME form employee table using alias name “Employee Name”
iv) Get FIRST_NAME from employee table after removing white spaces from left
side.( Each query – 1 Mark)
Ans :
i) select FIRST_NAME,LAST_NAME from employee;
ii) select distinct( DEPARTMENT) from employee;
iii) select FIRST_NAME “Employee Name” from employee;
iv) select ltrim(FIRST_NAME) from employee;

25) Consider following database and solve queries(For each command – 2 Marks)
emp (empno, ename, ph, sal, dept_no, comm)
(i) Change employee name ‘Rahul’ to ‘Ramesh’.
(ii) Give increment of 20% in salary to all employees.
Ans:
(i) Update emp set ename= „Ramesh‟ where ename= „Rahul‟;
(ii) Update emp set salary=salary+(salary*20)/100;
NIT POLYTECHNIC, NAGPUR UNIT-3 RDM NOTES
NOTES BY:- VIKAS DUDHE(9561277162) Page 12

26) Consider following schema:
Employee (emp_no, emp_name, dept, designation, salary, Dept_location)
Solve following queries: (For each query – 1 Mark)
(i) List all Managers in Mumbai location.
(ii) Set salary of all ‘project leaders’ to 70000/-.
(iii) List employees with having alphabet ‘A’ as second letter in their name.
(iv) Display details of those employees who work in Mumbai or Chennai.
Ans:
i) Select * from Employee where designation= ‘Manager’ AND Dept_location= ‘Mumbai’;
ii) Update Employee set salary=70000 where designation= ‘project leader’;
iii) Select * from Employee where emp_name like ‘_A%’;
iv) Select * from Employee where Dept_location= ‘Mumbai’ or Dept_location= ‘Chennai’;

27) Consider following schema:
depositor (cust_name, acc_no)
Borrower (cust_name, loan_no)
Solve following queries: (Each query – 2 Marks)
(i) Find customer name having savings account as well as loan account.
(ii) Find customer names having loan account but not the saving account.
Ans:
(i) select cust_name from depositor intersect select cust_name from borrower;
(ii) Select cust_name from borrower minus select cust_name from depositor;

28) List the SQL operators & explain rang searching operator- “BETWEEN” & pattern
matching operator – “LIKE”. (List at least four: 1 Mark, for Explanation- 1 Mark each,
For Syntax or example: ½ Mark each)
Ans:
1. Arithmetic operators
2. Comparison operators/relational operators
a) symbolic comparison operator
b) word comparison operator
3. Logical operators
4. Range searching operators
5. pattern matching operators
6. set operators
BETWEEN Operator:
Between is a range searching operator.
It is used to select data within a range of value.
Syntax:
SELECT colume_name(s) FROM table_name WHERE column_name
BETWEEN value1 AND value2;
Example: SELECT * FROM Emp WHERE salary BETWEEN 10000 AND 20000;
NIT POLYTECHNIC, NAGPUR UNIT-3 RDM NOTES
NOTES BY:- VIKAS DUDHE(9561277162) Page 13
LIKE Operator:
By using like operator, we can compare one value exactly to other value.
OR
It is used to compares data with an expression using wild card operators.
1) percent sign(%):matches all string.
2) underscore sign(_):matches any single character.
Syntax:
Select column_name from table_name where column_name like patteren;
Example:
Select * from person where firstname like ‘A%’;
Select * from person where firstname like ‘A_ _’;

29) Explain word comparison operators: (Each operator – 1 Mark)
(i) IN and NOT IN
(ii) BETWEEN and NOT BETWEEN.
Ans:
(i) IN and NOT IN
IN: a in(b,c)
This operator tests that operand a matches at least one element
of the list provided.
Example: select * from emp where sal IN(500,600,700);
NOT IN: a not in (b,c)
This operator tests that operand a does not match any elements of list provided
Example: select * from emp where sal NOT IN(500,600,700);
(ii) BETWEEN and NOT BETWEEN
BETWEEN: a between b and c
Tests that operand a is greater than or equal to operand b and less
than or equal to operand c.
The range which is specified will contain min value and max value. It is used numeric and date datatype.
Example: Select * from emp where sal BETWEEN 400 AND 600;
NOT BETWEEN: a not between b and c
Testes that operand a is less than operand b or greater than
operand c.
It test the Range other than b to c.
Example: Select * from emp where sal NOT BETWEEN 400 AND 600;

30) Explain the set operators of SQL. (Set Operators of SQL: 1 Marks for each)
Ans:
1. Union: UNION is used to combine the results of two or more Select statements.
However it will eliminate duplicate rows from its result set.
In case of union, number of columns and data type must be same in both the
tables.
Example: Select empno from employee Union Select empno from emp;
2. Union All: This operation is similar to Union. But it also shows the duplicate rows.
Example: Select empno from employee Union all Select empno from emp;
3. Intersect: Intersect operation is used to combine two SELECT statements,but it
only retunes the records which are common from both SELECT statements.
In case of Intersect the number of columns and data type must be same.
My SQL does not support INTERSECT operator.
Example: Select empno from employee Intersect Select empno from emp;
4. Minus: Minus operation combines result of two Select statements and return only that
result which belongs to first set of result.
Example: Select empno from employee Minus Select empno from emp;

31) List and explain set of operators in SQL with example. (Each operator 1 Mark)
{Note: Any other operators like Arithmetic, Comparison, Logical operators should be
consider}
Ans: 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 emp1 and emp2 as

emp1 emp2
ename ename
abc pqr
xyz xyz
lmn

 

1) Union : The Union of 2 or more sets contains all elements, which are present in either or both. Union works as OR.
Example:- select ename from emp1 union select ename from emp2;
The output considering above data is :
ename
abc
xyz
lmn
pqr
2) Union all : The Union of 2 or more sets contains all elements, which are present in both, including duplicates.
Example:- select ename from emp1 union all select ename from emp2;
The output considering above data is :
ename
abc
xyz
lmn
ename
pqr
xyz
3) Intersection: The intersection of 2 sets includes elements which are present in both.
Example:- select ename from emp1 intersection select ename from emp2;
The output considering above data is:
ename
xyz
4) Minus: The minus of 2 sets includes elements from set1 minus elements of set2.
Example:- select ename from emp1 minus select ename from emp2;
The output considering above data is:
Ename
abc
lmn

32) Explain any four string functions. (Each: 1 Mark)
Ans: Lower, upper, initcap, ltrim, Rtrim, Lpad, Rpad, Length, substr
1)Lower: Lower converts all letters of string in lowercase.
It is used in select statement.
Syntax: Select Lower( column_name ) from table_name;
Example: Select Lower( Ename ) from Emp;
2)Upper: Upper converts all letters of string in Uppercase.
It is used in select statement.
Syntax: Select Upper( column_name ) from table_name;
Example: consider table Emp( EmpId,Ename,city,sal ):-
Select upper( Ename ) from Emp;
3)Ltrim: It trims or cuts the character from left .
Syntax: Select Ltrim(string/column_name, text) from table_name;
Example: Select Ltrim(‘Rajesh’, ‘Raj’) from Emp; OR
Select Ltrim( empname , ‘Raj ’) from emp where empno=2;
4)Rtrim: It trims or cuts the character from right.
Syntax: Select Rtrim(string/column_name, text) from table_name;
Example: Select Rtrim(‘Rajesh’, ‘sh’) from Emp; OR
Select Rtrim( empname , ‘ sh ’) from emp where empno=2;
5)Initcap: Returns the input string with initial letter capitalized and all other character in lower case
Syntax: Select initcap(column_name) from table_name;
Example: Select initcap(ename) from Emp;
6)Length: It returns the length of character string.
Syntax: Select length(char/string/column_name) from <table_name>;
Example: Select length(empname) from Emp;
7)Lpad and Rpad: It is used for formatting from left and right side by using any character.
Syntax: Select lpad/rpad(string, length, symbol to padded) from table_name;
Example: Select Lpad(rekha,7, ‘*’) from emp;
Select Rpad(rekha,7, ‘*’) from emp;
8)Translate: This function is used to translate the given character from the input string.
Syntax: Select translate(string,string to be replaced,stringto be replaced by) from table_name;
Example: Select translate(‘banglore ’, ‘b’, ‘m’) from emp;
9)Concatenation: used to combine two string,
Syntax: Select (string|| column_name) from <table_name>;
Example: Select (‘the name is’|| name) from table_name;
10)Substr: it returns the substring from the specified position.
Syntax: Select substr(string,position,char to be replaced)from emp;
Example: Select substr(ename,1,3 )from emp;

33) Explain date and time functions of SQL.
(Any 4 functions Date and time syntax /example 2 Marks)
Ans:- Date function:
1) Months_between(d1,d2) : Used to find number of months between d1 and d2.
E.g.:- Select months_between(‘05-MAY-1996’, ‘05-JAN-1996’) “Months” from dual;
2) Add_months(d,n) : returns date after adding the number of months specified with the function.
E.g.:- Select add_months(sysdate,2) from dual;
3) Next_day(d,char) : Returns the date of the first weekday named ‘char’ that is after the date named by date.
E.g.:- Select next_day(‘01-FEB-2006’, ‘Wednesday’) “next_day” from dual;
4) Last_day(d) : Returns the last day of the month that contains date ‘d’.
E.g.:- Select last_day(sysdate) “last” from dual;
5) Round(date,[fmt]) : Returns date rounded to the unit specified by the
format model ‘fmt’.
E.g.:- Select round(sysdate, ‘day’) “round_day” from dual;
6) Trunc(date([fmt]): Returns date with the time portion of the day truncated to the unit
specified by the format model fmt.
E.g.:- Select trunc(sysdate, ‘day’) “trunc_day” from dual;
7) New_time(date, ‘d’, ‘n’) : Returns the time and date of a date column or literal in other given time zones.
E.g.:- Select new_time(sysdate, ‘est’, ‘pst’) from dual;
Where est is eastern standard time & pst is pacific standard time

34) Explain any four aggregate functions with example.
(Each function: 1 Mark- meaning 1/2 with syntax or example: 1/2 mark)
Ans: Avg, sum, min, max, count
1) Avg(column_name):
It is used to calculate average of values in column
OR
Returns average value of column.
Example: Select avg(salary) from Emp;
2) Sum(column):
It is used to calculate sum/addition of values of column.
OR
Returns addition of column of number data type.
Example: select sum(salary) from Emp;
3) Min(column):
Used to find lowest value in column
OR
Return lowest value from column.
Example: select min(salary) from Emp;
4) Max(column):
Used to find highest value in column.
OR
Returns heighest value in column.
Example: select max(salary) from Emp;
5) Count(column_name):
count the number of values in a column.
Example: select count(name) from Emp;
6) Count(*):
count the total number of rows.
Example: select count(*) from Emp;
7) Count(Distinct column_name):
count the total number of values without duplication.
Example: select count(distinct name) from Emp;

35) Explain group by having & order by clause in SQL.
(Explanation: 3 Marks, Syntax: 1Mark)
(Note: Separate syntax for each clause can be considered)
Ans: Group by clause:
This clause allows multiple columns to be grouped so aggregate functions may be performed on multiple columns with one command.
It is used to divide the rows in a table into groups.
It can also be used to return summary information for each group.
Having Clause:
It can be used to check condition on particular group.
This is like a where clause except that it involves summary value
rather than column value.
Order by clause:
It is used to arrange the record in ascending or descending order.
The order by clause should be placed last in query.
The default sort is ascending.
Syntax:
SELECT <ColumnName1>, <ColumnName2>, <ColumnName3>,
AGGREGATE_FUNCTION (<Expression>)
FROM TableName WHERE <Condition>
GROUP BY <Column-list>
Having <condition1>…….<condition n>
Order by <column-list> [ASC|DESC];
NIT POLYTECHNIC, NAGPUR UNIT-3 RDM NOTES
NOTES BY:- VIKAS DUDHE(9561277162) Page 19

36) Explain ‘Group by’ in SQL with suitable example.
(Explanation of Group by- 2 Marks, Example – 2 Marks)
Ans: The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups.
The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
Example :- SELECT dept, SUM(salary) from emp group by dept;

dept SUM(salary)
10 25000
20 30000

 

37) Explain GROUP BY, ORDER BY clause of SQL with example.
(Explanation with syntax / example – 2Marks each)
Ans:
Group by clause
The group by clause can be used in a select statement to collect data across multiple records and group by one or more columns.
The group by field contains the repetitive records or values like department number in employee.
The syntax is:
Select col1,col2,….,col n aggregate_function (expression)
From table_name
Where condition
Group by column_name;
Example: Display average salary of each department
Select avg(sal)
From emp
group by deptno;
Order by clause:
The ORDER BY keyword is used to sort the result-set by one or more columns.
The ORDER BY keyword sorts the records in ascending order by default.
To sort the records in a descending order, you can use the DESC keyword.
Syntax:
SELECT column_name, column_name
FROM table_name
ORDER BY column_name, column_name ASC|DESC;
Example:
1. SELECT * FROM Customers ORDER BY Country;
2. SELECT * FROM Customers ORDER BY Country DESC;

38) Explain Join concept of SQL. State types of join [Join concept 1 Mark, Types 3 Mark]
Ans:
1) JOIN: SQL Joins are used to relate information in different tables.
A Join condition is a part of the sql query that retrieves rows from two or more tables.
A SQL Join condition is used in the SQL WHERE Clause of select.
OR
Join is used to combine the data spread across tables.
A join is performed by the where clause which combines the specified rows of the tables.
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;
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 table2name 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>;
Example: we have 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;

39) Explain ACID properties. (Each property – 1 Mark)
OR
39) What is transaction? List its properties and explain in brief any two properties.
[Transaction definition 2 Marks, Listing of properties 2 Marks and Explanation of any 2
properties 2 Marks]
Ans: A transaction is a logical unit of work of database processing that includes one or more
database access operations.
OR
A single logical unit of a work which is the collection of several operations is called as transaction.
Transaction should possess several properties.
They are often called as ACID properties & they should be enforced by the concurrency
control & recovery methods of the DBMS.
ACID Properties:
1) Atomicity: A transactions is an atomic unit of processing; it is either performed
entirely or not performed at all
2)Consistency :The consistency property of a transaction implies that if the database was in consistent state before the start of a transaction then on termination of the transaction the database will also be in consistent state.
OR
2) Consistency: Consistency keeps the database consistent. Execution of a transaction needs to take place in isolation.
It helps in reducing complications of executing multiple transactions at a time and preserves the consistency of the database.
3) Isolation: Even though multiple transactions may execute concurrently, the system guarantees that, for every pair of transactions Ti and Tj, it appears to Ti that either Tj finished execution before Ti started, or Tj started execution after
Ti finished. Thus,each transaction is unaware of other transactions
executing concurrently in the system.
OR
3) Isolation: It is necessary to maintain isolation for the transactions. This means one transaction should not be aware of another transaction getting executed.
Also their intermediate result should be kept hidden.
4) Durability: It states that changes applied to the database by committed transaction must persist in the database. The durability property of a transaction ensures that once the transaction completes successfully the changes it has made to the database remains even if there is system failure.

40) Explain states of transaction with neat diagram.
states- 2 Marks)
Ans:

Untitled

 

OR
40) Draw transaction state diagram.
(Correct diagram- 2 Marks)

Untitled

 

• Active, the initial state; the transaction stays in this state while it is executing
• Partially committed, after the final statement has been executed
• Failed, after the discovery that normal execution can no longer proceed
• Aborted or terminated after the transaction has been rolled back and the database has been restored to its state prior to the start of the transaction
• Committed, after successful completion
41) What is concurrent schedule? Explain with example.
(Explanation – 2 Marks, Any valid Example
Ans:
It is mechanism to achieve isolation i.e. to control the interaction among the concurrent transactions in order to prevent them from destroying the consistency of the database.
A schedule S is serial if, for every transaction T participating in the schedule, all the operations of T are executed consecutively in the schedule; otherw called concurrent.
UntitledImportant Topic —- Sub query——
 When we write a query inside another query, the inner query is called sub query.
 Outer query is called parent query.
 Sub query is executed first and the parent query will be executed by using the result Of the sub query.
 Sub queries are used to get the results based on unknown values.
Single row sub query: When sub query returns one row (one value), it is called single-row sub query.
Example1: I want to display all the employees who are having Sal greater than BHANU
Sal.

Step 1: we need to find BHANU Sal,
Query to get BHANU Sal
Select Sal from EMP Where ename=’BHANU’;
Output: 1600
Step 2: Now, we want all the rows that are having Sal > 1600
Query: Select * from EMP Where Sal > (Select Sal from EMP Where ename=’BHANU’);
Always sub query should be used in parenthesis.
As we know, sub query is executed first, it returns 1600.
And then parent query will display all the rows who are having Sal > BHANU SAL
T1 T2
read(A)
A := A –50
write(A)
read(A)
temp := A * 0.1
A := A temp
write(A )
read(B)
B := B + 50
write(B)
read(B)
B := B + temp
write(B)
Example 2: I want to display the rows who are having Sal > BHANU Sal and job same as JONES job.
Query:Select * from EMP Where Sal >(select Sal from EMP Where ename=’BHANU’)
And job = (select job from EMP Where ename=’JONES’);
One parent query can have any multiple sub queries.
Example 3: Write a query to display details of an employee who is having highest Sal.
Step 1: Find the highest Sal.
Select max (Sal) from EMP; — output 5000
Step 2:
Select * from EMP Where sal = (select max (Sal) from EMP);
In all the above examples, sub query is returning only one row (one value).
Hence they are called single-row sub query.
Exercise:-
1)consider the structure for book table as
Book_master = { bookid, bookname, subcode, author, no_of_copies, price}
write sql queries for the following:
i)Display total number of books for subject ‘DBM’.

ii) Get authorwise list of all books.

iii)Display all books whose prices between Rs.200 and Rs. 500.

iv)Display all books with details whose name start with ‘s’

2) Give SQL DDL definition of the database schema given below and solve the following
EMP(empno, ename, job, mgr, joindate, salary, comm, deptno, address)
i) Change the joining date of employee having empno=1000 to 12/11/2009

ii) write a query to display the employee details whose empno=500.

iii) Write a query to display the name and salary of employee who earn more than Rs.5000 and are in
department 10 or 30.

iv) Find the list of employee whose salary is between 5000 to 20,000.

3) Consider the following database
Employee(emp_id,emp_name,emp_city,emp_addr,emp_dept,join_date)
i) Display the names of employees in capital letters

ii) Display the emp_id of employee who live in city Pune and Mumbai.

iii) Display the details of employees whose joining date is after ’01-apr-1997’.

iv) Display the total number of employees whose dept no is ‘10’.

Leave a Reply

Your email address will not be published. Required fields are marked *