UNIT-2- Introduction to SQL

9) What is SQL? Why it Is used?[3 MARKS]

Ans: SQL is Structured Query Language, which is a computer language for storing,
manipulating and retrieving data stored in relational database.

SQL is the standard language for Relation Database System. All relational database
management systems like MySQL, MS Access, Oracle, Sybase, Informix, postgres and
SQL Server use SQL asstandard database language.

Also, they are using different dialects, such as:

➢ Oracle using PL/SQL,
➢ My sql
➢ MS Access version of SQL is called JET SQL (native format) etc.
WhySQL?
➢ Allows users to access data in relational database management systems.

➢ Allows users to describe the data.

➢ Allows users to define the data in database and manipulate that data.

➢ Allows to embed within other languages using SQL modules, libraries & pre-
compilers.

➢ Allows users to create and drop databases and tables.

➢ Allows users to create view, stored procedure, functions in a database.

➢ Allows users to set permissions on tables, procedures, and views

10) Explain DDL Commands in brief.[3 MARKS PER EACH

STATEMENT]
Ans:

➢ DDL stand for Data Definition Language (DDL).

➢ DDL changes the structure of the table like creating a table, deleting a table, altering a
table, etc.
➢ All the command of DDL are auto-committed that means it permanently save all the
changes in the database.

Here are some commands that come under DDL:

➢ CREATE
➢ ALTER
➢ DROP
➢ TRUNCATE

❖ CREATE
It is used to create a new table in the database.

Syntax:
CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);
INTYRODUCTION TO DBMS (DBMS) (03606207)

Example:
CREATE TABLE EMPLOYEE
(Name VARCHAR2(20), Email VARCHAR2(100), DOB DATE);

❖ DROP TABLE
It is used to delete both the structure and record stored in the table.
Syntax:
DROP TABLE table_name;
Example:
DROP TABLE EMPLOYEE;

❖ ALTER TABLE
It is used to alter the structure of the database.
This change could be either to modify the characteristics of an existing
attribute or probably to add a new attribute.
Syntax:
To add a new column in the table
ALTER TABLE table_name ADD column_name COLUMN-definition;
To modify existing column in the table:
ALTER TABLE table_name MODIFY(column_definitions....);
Example:
ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20));
ALTER TABLE STU_DETAILS MODIFY (NAME VARCHAR2(20));

❖ TRUNCATE TABLE
It is used to delete all the rows from the table and free the space containing the table.
Syntax:
TRUNCATE TABLE table_name;
Example:
TRUNCATE TABLE EMPLOYEE

11) Explain DML commands with example.[2 MARKS PER EACH

COMMAND]

DML commands are used to modify the database. It is responsible for all form of
changes in the database.

The command of DML is not auto-committed that means it can't permanently save all
the changes in the database. They can be rollback.

❖ Here are some commands that come under DML:

• INSERT
• UPDATE
• DELETE

❖ INSERT TABLE
The INSERT statement is a SQL query. It is used to insert data into the row of a table.
Syntax:
INSERT INTO TABLE_NAME

PIET DS
INTYRODUCTION TO DBMS (DBMS) (03606207)

(col1, col2, col3,.... col N)

VALUES (value1, value2, value3, .... valueN);
OR
INSERT INTO TABLE_NAME
VALUES (value1, value2, value3, .... valueN);

Example:

INSERT INTO javatpoint (Author, Subject) VALUES ("Sonoo", "DBMS");

❖ UPDATE TABLE
This command is used to update or modify the value of a column in the table.
Syntax:
UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [
WHERE CONDITION]
Example:
UPDATE students
SET User_Name = 'Sonoo'
WHERE Student_Id = '3'

❖ DELETE TABLE
It is used to remove one or more row from a table.
Syntax:
DELETE FROM table_name [WHERE condition];
Example:
DELETE FROM javatpoint
WHERE Author="Sonoo";

12) Explain DCL commands with example.[2 MARKS PER EACH

COMMAND]
DCL commands are used to grant and take back authority from any database user.
❖ Here are some commands that come under DCL:
• Grant
• Revoke

❖ GRANT
It is used to give user access privileges to a database.
Example:

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_U

SER;

❖ REVOKE
It is used to take back permissions from the user.
Example:
REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

13) Explain TCL commands with example. .[2 MARKS PER EACH
COMMAND]

PIET DS
INTYRODUCTION TO DBMS (DBMS) (03606207)

TCL commands can only use with DML commands like INSERT, DELETE and
UPDATE only.
These operations are automatically committed in the database that's why they cannot
be used while creating tables or dropping them.

❖ Here are some commands that come under TCL:

• COMMIT
• ROLLBACK
• SAVEPOINT

❖ COMMIT
Commit command is used to save all the transactions to the database.
Syntax:
COMMIT;
Example:
DELETE FROM CUSTOMERS
WHERE AGE = 25;
COMMIT;

❖ ROLLBACK
Rollback command is used to undo transactions that have not already been saved to the
database.
Syntax:
ROLLBACK;
Example:
DELETE FROM CUSTOMERS
WHERE AGE = 25;
ROLLBACK;

❖ SAVEPOINT
It is used to roll the transaction back to a certain point without rolling back the entire
transaction.
Syntax:
SAVEPOINT SAVEPOINT_NAME;

14) Explain DQL Select Query with Where clause. .[3 MARKS]

DQL is used to fetch the data from the database.

It uses only one command:
❖ SELECT
• This is the same as the projection operation of relational algebra. It is used to select the
attribute based on the condition described by WHERE clause.
Syntax:
SELECT expressions
FROM TABLES
WHERE conditions;
Example:
SELECT emp_name
FROM employee
WHERE age > 20;

PIET DS
INTYRODUCTION TO DBMS (DBMS) (03606207)

PIET DS
 UNIT-3-WORKING WITH SQL FUNCTIONS AND OPERATOR

1. Explain SQL Operators with example. (3)

 Arithmetic operators:
We can use various arithmetic operators on the data stored in the tables.
Arithmetic Operators are:
Operator
1 + The addition is used to perform an addition operation on the data
values.
2 – This operator is used for the subtraction of the data values.
3 / This operator works with the ‘ALL’ keyword and it calculates division
operations.
4 * This operator is used for multiply data values.
5 % Modulus is used to get the remainder when data is divided by another.
 Comparison operators:
Another important operator in SQL is a comparison operator, which is used to
compare one expression’s value to other expressions. SQL supports different
types of the comparison operator, which is described below:
[Link]. Operator Description
1 = Equal to.
2 > Greater than.
3 < Less than.
4 >= Greater than equal to.
5 <= Less than equal to.
6 <> Not equal to.
 Logical operators:
The Logical operators are those that are true or false. They return true or false
values to combine one or more true or false values.
1. AND : Logical AND compares between two Booleans as expressions and
returns true when both expressions are true.
DATABASE MANAGEMENT SYSTEM (DBMS) (03606207)

2. OR : Logical OR compares between two Booleans as expressions and returns

true when one of the expressions is true.
3. NOT: Not takes a single Boolean as an argument and changes its value from
false to true or from true to false.
 SQL BETWEEN(RANGE SEARCHING OPERATOR)
The SQL BETWEEN operator tests an expression against a range. The range
consists of a beginning, followed by an AND keyword and an end expression.
The operator returns TRUE when the search value present within the range
otherwise returns FALSE.
The results are NULL if any of the range values are NULL.
Syntax:
SELECT [column_name... | expression1 ]
FROM [table_name]
WHERE expression2 [NOT] BETWEEN value_from AND value_to;
 SQL LIKE(CHARACTER OPERATOR)
The LIKE command is used in a WHERE clause to search for a specified pattern
in a column.
You can use two wildcards with LIKE:
% - Represents zero, one, or multiple characters
_ - Represents a single character (MS Access uses a question mark (?) instead)
The following SQL selects all customers with a CustomerName starting with
"a":
Syntax:
Columnname LIKE pattern;
Example:
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';
 SQL IN(SET SEARCHING OPERATOR)
The IN command allows you to specify multiple values in a WHERE clause.

PIET DS
DATABASE MANAGEMENT SYSTEM (DBMS) (03606207)

The IN operator is a shorthand for multiple OR conditions.

The following SQL selects all customers that are located in "Germany", "France"
and "UK":
Syntax:
Columnname IN (valueset1, valuesset2….valuesetN);
Example:
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
2. Explain Order by, Group by and having clause with example. (4)
 GROUP BY:
The GROUP BY command is used to group the result set (used with aggregate
functions: COUNT, MAX, MIN, SUM, AVG).
The following SQL lists the number of customers in each country:
Example
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
 HAVING:
The HAVING command is used instead of WHERE with aggregate functions.
The following SQL lists the number of customers in each country.
Only include countries with more than 5 customers:
Example
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
 ORDER BY
The ORDER BY command is used to sort the result set in ascending or
descending order.

PIET DS
DATABASE MANAGEMENT SYSTEM (DBMS) (03606207)

The ORDER BY command sorts the result set in ascending order by default. To
sort the records in descending order, use the DESC keyword.
The following SQL statement selects all the columns from the "Customers"
table, sorted by the "CustomerName" column:
Example:
SELECT * FROM Customers
ORDER BY CustomerName;
 ASC
The ASC command is used to sort the data returned in ascending order.
The following SQL statement selects all the columns from the "Customers"
table, sorted by the "CustomerName" column:
Example
SELECT * FROM Customers
ORDER BY CustomerName ASC;
 DESC
The DESC command is used to sort the data returned in descending order.
The following SQL statement selects all the columns from the "Customers"
table, sorted descending by the "CustomerName" column:
Example
SELECT * FROM Customers
ORDER BY CustomerName DESC;
3. Explain Character functions with example. (3)
 LOWER : This function converts alpha character values to lowercase.
LOWER will actually return a fixed-length string if the incoming string is fixed-
length.
LOWER will not change any characters in the string that are not letters, since
case is irrelevant for numbers and special characters, such as the dollar sign ( $ )
or modulus ( % ).

PIET DS
DATABASE MANAGEMENT SYSTEM (DBMS) (03606207)

Syntax:
LOWER(string)
Input1: SELECT LOWER('HELLO') FROM DUAL;
Output1: hello
 UPPER : This function converts alpha character values to uppercase.
Also UPPER function too, will actually return a fixed-length string if the
incoming string is fixed-length. UPPER will not change any characters in the
string that are not letters, since case is irrelevant for numbers and special
characters, such as the dollar sign ( $ ) or modulus ( % ).
Syntax:
UPPER(string)
Input1: SELECT UPPER(' hello ') FROM DUAL;
Output1: HELLO
 INITCAP : This function converts alpha character values to uppercase for
the first letter of each word and all others in lowercase.
The words in the string is must be separated by either # or _ or space.
Syntax:
INITCAP (string)
Input1: SELECT INITCAP ('web port is a computer science portal') FROM
DUAL;
Output1: Web Port Is A Computer Science Portal

 CONCAT : This function always appends ( concatenates ) string2 to the end

of string1.
If either of the string is NULL, CONCAT function returns the non-NULL
argument. If both strings are NULL, CONCAT returns NULL.
Syntax:
CONCAT('String1', 'String2')
Input1: SELECT CONCAT('computer' ,'science') FROM DUAL;

PIET DS
DATABASE MANAGEMENT SYSTEM (DBMS) (03606207)

Output1: computerscience
 LENGTH : This function returns the length of the input string.
If the input string is NULL, then LENGTH function returns NULL and not
Zero. Also, if the input string contains extra spaces at the start, or in between or
at the end of the string, then the LENGTH function includes the extra spaces too
and returns the complete length of the string.
Syntax:
LENGTH(Column|Expression)
Input1: SELECT LENGTH('Learning Is Fun') FROM DUAL;
Output1: 15
 LPAD and RPAD : These functions return the strings padded to the left or
right ( as per the use ) ;
hence the “L” in “LPAD” and the “R” in “RPAD” ; to a specified length, and
with a specified pad string.
If the pad string is not specified, then the given string is padded on the left or
right ( as per the use ) with spaces.
Syntax:
LPAD(Column|Expression, n, 'String')
Syntax: RPAD(Column|Expression, n, 'String')
LPAD Input1: SELECT LPAD('100',5,'*') FROM DUAL;
LPAD Output1: **100
RPAD Input1: SELECT RPAD('5000',7,'*') FROM DUAL;
RPAD Output1: 5000***
 TRIM : This function trims the string input from the start or end (or both).
If no string or char is specified to be trimmed from the string and there exists
some extra space at start or end of the string, then those extra spaces are trimmed
off.
Syntax:
TRIM(Leading|Trailing|Both, trim_character FROM trim_source)

PIET DS
DATABASE MANAGEMENT SYSTEM (DBMS) (03606207)

Input1: SELECT TRIM('G' FROM 'GEEKS') FROM DUAL;

Output1: EEKS
4. Explain Numeric function with example. (4)
Numeric Functions are used to perform operations on numbers and return
numbers.
Following are the numeric functions defined in SQL:
 ABS(): It returns the absolute value of a number.
Syntax: SELECT ABS(-243.5);
Output: 243.5
 ACOS(): It returns the cosine of a number.
Syntax: SELECT ACOS(0.25);
Output: 1.318116071652818
 ASIN(): It returns the arc sine of a number.
Syntax: SELECT ASIN(0.25);
Output: 0.25268025514207865
 ATAN(): It returns the arc tangent of a number.
Syntax: SELECT ATAN(2.5);
Output: 1.1902899496825317
 CEIL(): It returns the smallest integer value that is greater than or equal to a
number.
Syntax: SELECT CEIL(25.75);
Output: 26
 DIV(): It is used for integer division.
Syntax: SELECT 10 DIV 5;
Output: 2
 EXP(): It returns e raised to the power of number.
Syntax: SELECT EXP(1);
Output: 2.718281828459045

PIET DS
DATABASE MANAGEMENT SYSTEM (DBMS) (03606207)

 FLOOR(): It returns the largest integer value that is less than or equal to a
number.
Syntax: SELECT FLOOR(25.75);
Output: 2
 GREATEST(): It returns the greatest value in a list of expressions.
Syntax: SELECT GREATEST(30, 2, 36, 81, 125);
Output: 125
 LEAST(): It returns the smallest value in a list of expressions.
Syntax: SELECT LEAST(30, 2, 36, 81, 125);
Output: 2
 LN(): It returns the natural logarithm of a number.
Syntax: SELECT LN(2);
Output: 0.6931471805599453
 MOD(): It returns the remainder of n divided by m.
Syntax: SELECT MOD(18, 4);
Output: 2
 PI(): It returns the value of PI displayed with 6 decimal places.
Syntax: SELECT PI();
Output: 3.141593
 POW(): It returns m raised to the nth power.
Syntax: SELECT POW(4, 2);
Output: 16
 ROUND(): It returns a number rounded to a certain number of decimal
places.
Syntax: SELECT ROUND(5.553);
Output: 6
 SIN(): It returns the sine of a number.
Syntax: SELECT SIN(2);
Output: 0.9092974268256817

PIET DS
DATABASE MANAGEMENT SYSTEM (DBMS) (03606207)

 SQRT(): It returns the square root of a number.

Syntax: SELECT SQRT(25);
Output: 5
 TAN(): It returns the tangent of a number.
Syntax: SELECT TAN(1.75);
Output: -5.52037992250933
 TRUNCATE(): This doesn’t work for SQL Server. It returns 7.53635
truncated to 2 places right of the decimal point.
Syntax: SELECT TRUNCATE(7.53635, 2);
Output: 7.53
5. Explain Conversion function with example. (3)
 TO_CHAR Function :
TO_CHAR function is used to typecast a numeric or date input to character type
with a format model (optional).
SYNTAX :
TO_CHAR(date, ’format_model’)
example:
SELECT employee_id, TO_CHAR(hire_date, ’MM/YY’) Month_Hired
FROM employees
WHERE last_name = ’Higgins’;
OUTPUT :
EMPLOYEE_ID MONTH_HIRED
205 06/94
 TO_NUMBER: (char[, ’format_model’])
Convert a character string to a date format using the TO_DATE function:
 TO_DATE: (char[, ’format_model’])
These functions have an fx modifier. This modifier specifies the exact matching
for the character argument and date format model of a TO_DATE function.

PIET DS
DATABASE MANAGEMENT SYSTEM (DBMS) (03606207)

EXAMPLE :
SELECT last_name, hire_date
FROM employees
WHERE hire_date = TO_DATE(’May 24, 1999’, ’fxMonth DD, YYYY’);
OUTPUT :
LASTNAME HIREDATE
Kumar 24-MAY-99
6. List Aggregate function and explain with example. (4)
 Count():
Count(*): Returns total number of records .i.e 6.
Count(salary): Return number of Non Null values over the column salary. i.e 5.
Count(Distinct Salary): Return number of distinct Non Null values over the
column salary .i.e 4
Example:
Select count(total_sal) from employee;
Output: 5
 Sum():
sum(salary): Sum all Non Null values of Column salary i.e., 310
sum(Distinct salary): Sum of all distinct Non-Null values i.e., 250.
Example:
Select sum(total_sal) from employee;
Output: 456000
 Avg():
Avg(salary) = Sum(salary) / count(salary) = 310/5
Avg(Distinct salary) = sum(Distinct salary) / Count(Distinct Salary) = 250/4
Example:
Select avg(total_sal) from employee;
Output: 50.56
 Min():

PIET DS
DATABASE MANAGEMENT SYSTEM (DBMS) (03606207)

Min(salary): Minimum value in the salary column except NULL i.e., 40.
Max(salary): Maximum value in the salary i.e., 80.
Example:
Select max(total_sal) from employee;
Output: 50000
Select min(total_sal) from employee;
Output: 20000
7. Explain types of joins with example. (4)
 INNER JOIN
The INNER JOIN keyword selects all rows from both the tables as long as the
condition is satisfied.
This keyword will create the result-set by combining all rows from both the
tables where the condition satisfies i.e value of the common field will be the
same.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column;
Example Queries(INNER JOIN)
SELECT StudentCourse.COURSE_ID, [Link], [Link] FROM
Student
INNER JOIN StudentCourse
ON Student.ROLL_NO = StudentCourse.ROLL_NO;

PIET DS
DATABASE MANAGEMENT SYSTEM (DBMS) (03606207)

Output:

 LEFT JOIN
This join returns all the rows of the table on the left side of the join and matches
rows for the table on the right side of the join.
For the rows for which there is no matching row on the right side, the result-set
will contain null. LEFT JOIN is also known as LEFT OUTER JOIN.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
Example Queries(LEFT JOIN):
SELECT [Link],StudentCourse.COURSE_ID
FROM Student
LEFT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output:

PIET DS
DATABASE MANAGEMENT SYSTEM (DBMS) (03606207)

 RIGHT JOIN
RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table
on the right side of the join and matching rows for the table on the left side of the
join.
For the rows for which there is no matching row on the left side, the result-set
will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;
Example Queries(RIGHT JOIN):
SELECT [Link],StudentCourse.COURSE_ID
FROM Student
RIGHT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output:

 FULL JOIN
FULL JOIN creates the result-set by combining results of both LEFT JOIN and
RIGHT JOIN.
The result-set will contain all the rows from both tables. For the rows for which
there is no matching, the result-set will contain NULL values.

PIET DS
DATABASE MANAGEMENT SYSTEM (DBMS) (03606207)

Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;
Example Queries(FULL JOIN):
SELECT [Link],StudentCourse.COURSE_ID
FROM Student
FULL JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output:

8. Explain SQL constraints in brief. (4)

 NOT NULL
If we specify a field in a table to be NOT NULL. Then the field will never
accept null value.
That is, you will be not allowed to insert a new row in the table without
specifying any value to this field.

PIET DS
DATABASE MANAGEMENT SYSTEM (DBMS) (03606207)

For example, the below query creates a table Student with the fields ID and
NAME as NOT NULL.
That is, we are bound to specify values for these two fields every time we wish
to insert a new row.
EXAMPLE:
CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
ADDRESS varchar(20)
);
 UNIQUE
This constraint helps to uniquely identify each row in the table. i.e. for a
particular column, all the rows should have unique values.
We can have more than one UNIQUE columns in a table.
For example, the below query creates a table Student where the field ID is
specified as UNIQUE. i.e, no two students can have the same ID. Unique
constraint in detail.
Example:
CREATE TABLE Student
(ID int(6) NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20)
);
 PRIMARY KEY
Primary Key is a field which uniquely identifies each row in the table.
If a field in a table as primary key, then the field will not be able to contain
NULL values as well as all the rows should have unique values for this field.

PIET DS
DATABASE MANAGEMENT SYSTEM (DBMS) (03606207)

So, in other words we can say that this is combination of NOT NULL and
UNIQUE constraints.
A table can have only one field as primary key. Below query will create a table
named Student and specifies the field ID as primary key.
Example:
CREATE TABLE Student
(ID int(6) NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20),
PRIMARY KEY(ID)
);
 FOREIGN KEY
Foreign Key is a field in a table which uniquely identifies each row of a another
table.
That is, this field points to primary key of another table. This usually creates a
kind of link between the tables.
Consider the two tables as shown below:
C_ID NAME ADDRESS
1 RAMESH DELHI
2 SURESH NOIDA
3 DHARMESH GURGAON
As we can see clearly that the field C_ID in Orders table is the primary key in
Customers table, i.e. it uniquely identifies each row in the Customers table.
Therefore, it is a Foreign Key in Orders table.
Query:
CREATE TABLE Orders
(O_ID int NOT NULL,
ORDER_NO int NOT NULL,
C_ID int,

PIET DS
DATABASE MANAGEMENT SYSTEM (DBMS) (03606207)

PRIMARY KEY (O_ID),

FOREIGN KEY (C_ID) REFERENCES Customers(C_ID));
 CHECK
Using the CHECK constraint we can specify a condition for a field, which
should be satisfied at the time of entering values for this field.
For example, the below query creates a table Student and specifies the condition
for the field AGE as (AGE >= 18 ).
That is, the user will not be allowed to enter any record in the table with AGE <
18. Check constraint in detail
Example:
CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
AGE int NOT NULL CHECK (AGE >= 18)
);
9. Explain sub query in brief. (4)
In SQL a Subquery can be simply defined as a query within another query. In
other words we can say that a Subquery is a query that is embedded in WHERE
clause of another SQL query.
Important rules for Subqueries:
 You can place the Subquery in a number of SQL clauses: WHERE clause,
HAVING clause, FROM clause.
 Subqueries can be used with SELECT, UPDATE, INSERT, DELETE
statements along with expression operator. It could be equality operator or
comparison operator such as =, >, =, <= and Like operator.
 A subquery is a query within another query. The outer query is called as main
query and inner query is called as subquery.

PIET DS
DATABASE MANAGEMENT SYSTEM (DBMS) (03606207)

 ORDER BY command cannot be used in a Subquery. GROUPBY command

can be used to perform same function as ORDER BY command.
Syntax:
There is not any general syntax for Subqueries.
However, Subqueries are seen to be used most frequently with SELECT
statement as shown below:
SELECT column_name
FROM table_name
WHERE column_name expression operator
( SELECT COLUMN_NAME from TABLE_NAME WHERE ... );
To display NAME, LOCATION, PHONE_NUMBER of the students from
DATABASE table whose section is A:
Example:
Select NAME, LOCATION, PHONE_NUMBER from DATABASE
WHERE ROLL_NO IN
(SELECT ROLL_NO from STUDENT where SECTION=’A’);
Output:
NAME ROLL_NO LOCATION PHONE_NUMBER
Ravi 104 delhi 8989898989
Raj 102 Coimbatore 8877665544

PIET DS
 UNIT-4- Introduction to various Data Model

24) Explain types of attributes of Entity relationship model.[4 MARKS\3

MARKS]
➢ An attribute is a property or characteristic of an entity.
➢ An entity may contain any number of attributes.
➢ One of the attributes is considered as the primary key. In an Entity-Relation model,
attributes are represented in an elliptical shape.

Example:
➢ Student has attributes like name, age, roll number, and many more. To uniquely identify
the student, we use the primary key as a roll number as it is not repeated. Attributes can
also be subdivided into another set of attributes.

➢ There are five such types of attributes: Simple, Composite, Single-valued, Multi-valued,
and Derived attribute. One more attribute is their, i.e. Complex Attribute, this is the
rarely used attribute.

Simple attribute :
➢ An attribute that cannot be further subdivided into components is a simple attribute.
Example:
➢ The roll number of a student, the id number of an employee.

Composite attribute :
➢ An attribute that can be split into components is a composite attribute.

Example:
➢ The address can be further split into house number, street number, city, state, country,
and pin code, the name can also be split into first name middle name, and last name.

Single-valued attribute :
➢ The attribute which takes up only a single value for each entity instance is a single-
valued attribute.

Example:
➢ The age of a student.

Multi-valued attribute :
➢ The attribute which takes up more than a single value for each entity instance is a multi-
valued attribute.
INTYRODUCTION TO DBMS (DBMS) (03606207)

Example:
➢ Phone number of a student: Landline and mobile.

Derived attribute :
➢ An attribute that can be derived from other attributes is derived attributes.

Example:
➢ Total and average marks of a student.

Complex attribute :
➢ Those attributes, which can be formed by the nesting of composite and multi-valued
attributes, are called “Complex Attributes”. These attributes are rarely used in
DBMS(DataBase Management System). That’s why they are not so popular.
Example:
➢ Let us consider a person having multiple phone numbers, emails, and an address.

➢ Here, phone number and email are examples of multi-valued attributes and address is an
example of the composite attribute, because it can be divided into house number, street,
city, and state.

25) Explain Entity and Entity set in brief.[MARK-4]

Entity :
➢ An entity is a thing in a real-world with independent existence. An entity can exist
independently and is distinguishable from other objects. It can be identified uniquely.
Example :
➢ A student with a particular roll number is an entity.
➢ A company with a particular registration number is an entity.
Entity Set :

PIET DS
INTYRODUCTION TO DBMS (DBMS) (03606207)

➢ An entity set is a collection or set of all entities of a particular entity type at any point in
time. The type of all the entities should be the same.

Example :
➢ The collection of all the students from the student table at a particular instant of time is
an example of an entity set.
➢ The collection of all the employees from the employee table at a particular instant of time
is an example of an entity set.
26) What is relationship? Explain degree of relationship.[MARK-4]
Degree of Relationship
➢ In DBMS, a degree of relationship represents the number of entity types that associate in
a relationship.
For example:
➢ We have two entities, one is a student and the other is a bag and they are connected with
the primary key and foreign key. So, here we can see that the degree of relationship is 2
as 2 entities are associating in a relationship.
Types of degree
➢ Now, based on the number of linked entity types, we have 4 types of degrees of
relationships.
1) Unary
2) Binary
3) Ternary
4) N-ARY
Unary
➢ In this type of relationship, both the associating entity type are the same.
➢ So, we can say that unary relationships exist when both entity types are the same and we
call them the degree of relationship is 1. Or in other words, in a relation only one entity
set is participating then such type of relationship is known as a unary relationship.
Example:
➢ In a particular class, we have many students, there are monitors too. So, here class
monitors are also students. Thus, we can say that only students are participating here.
➢ So the degree of such type of relationship is 1.

PIET DS
INTYRODUCTION TO DBMS (DBMS) (03606207)

Binary (degree 2)
➢ In a Binary relationship, there are two types of entity associates. So, we can say that a
Binary relationship exists when there are two types of entity and we call them a degree of
relationship is 2.
➢ Or in other words, in a relation when two entity sets are participating then such type of
relationship is known as a binary relationship.
➢ This is the most used relationship and one can easily be converted into a relational table.

Example:
➢ We have two entity types ‘Student’ and ‘ID’ where each ‘Student’ has his ‘ID’. So, here
two entity types are associating we can say it is a binary relationship.
➢ Also, one ‘Student’ can have many ‘daughters’ but each ‘daughter’ should belong to only
one ‘father. We can say that it is a one-to-many binary relationship.

Ternary(degree 3)

➢ In the Ternary relationship, there are three types of entity associates. So, we can say that
a Ternary relationship exists when there are three types of entity and we call them a
degree of relationship is 3.

➢ Since the number of entities increases due to this, it becomes very complex to turn E-R
into a relational table. Now let’s understand with the examples.

Example:
➢ We have three entity types ‘Teacher’, ‘Course’, and ‘Class’. The relationship between
these entities is defined as the teacher teaching a particular course, also the teacher
teaches a particular class.

PIET DS
INTYRODUCTION TO DBMS (DBMS) (03606207)

➢ So, here three entity types are associating we can say it is a ternary relationship.

N-ARY (n degree)
➢ In the N-ary relationship, there are n types of entity that associates. So, we can say that
an N-ary relationship exists when there are n types of entities.
➢ There is one limitation of the N-ary relationship, as there are many entities so it is very
hard to convert into an entity, rational table.
➢ So, this is very uncommon, unlike binary which is very much popular.

Example:
➢ We have 5 entities Teacher, Class, Location, Salary, Course. So, here five entity types
are associating we can say an n-ary relationship is 5.

27) Explain mapping cardinality with example.[MARK-4]

Ans:
➢ A mapping cardinality is a data constraint that specifies how many entities an entity can
be related to in a relationship set.

PIET DS
INTYRODUCTION TO DBMS (DBMS) (03606207)

Example:
➢ Each customer can purchase as many kinds of flyrods as they want (a constraint on
Purchased), but can have only one favorite flyrod/fly combination (a constraint on Fave-
Combo).
➢ A binary relationship set is a relationship set on two entity sets. Mapping cardinalities on
binary relationship sets are simplest.
➢ Consider a binary relationship set R on entity sets A and B. There are four possible
mapping cardinalities in this case:
One-to-one:
➢ An entity in A is related to at most one entity in B, and an entity in B is related to at
most one entity in A.

One-to-many:
➢ an entity in A is related to any number of entities in B, but an entity in B is related to at
most one entity in A.

Many-to-one:
➢ An entity in A is related to at most one entity in B, but an entity in B is related to any
number of entities in A

PIET DS
INTYRODUCTION TO DBMS (DBMS) (03606207)

Many-to-many:
➢ An entity in A is related to any number of entities in B, but an entity in B is related to any
number of entities in A.

28) Explain ER Diagram representation of Entity and Attributes.[4

MARKS]
➢ An Entity–relationship model (ER model) describes the structure of a database with the
help of a diagram, which is known as Entity Relationship Diagram (ER Diagram).

➢ An ER model is a design or blueprint of a database that can later be implemented as a

database. The main components of E-R model are: entity set and relationship set.

Attribute
➢ An attribute describes the property of an entity. An attribute is represented as Oval
in an ER diagram. There are four types of attributes:

[Link] attribute
2. Composite attribute
3. Multivalued attribute
4. Derived attribute

1. Key attribute:

PIET DS
INTYRODUCTION TO DBMS (DBMS) (03606207)

➢ A key attribute can uniquely identify an entity from an entity set.

➢ For example, student roll number can uniquely identify a student from a set of
students. Key attribute is represented by oval same as other attributes however
the text of key attribute is underlined.

2. Composite attribute:

➢ An attribute that is a combination of other attributes is known as composite

attribute.

➢ For example, In student entity, the student address is a composite attribute as an

address is composed of other attributes such as pin code, state, country.

3. Multivalued attribute:

➢ An attribute that can hold multiple values is known as multivalued attribute. It is

represented with double ovals in an ER Diagram.

PIET DS
INTYRODUCTION TO DBMS (DBMS) (03606207)

➢ For example – A person can have more than one phone numbers so the phone
number attribute is multivalued.

4. Derived attribute:

➢ A derived attribute is one whose value is dynamic and derived from another
attribute.

➢ It is represented by dashed oval in an ER Diagram. For example – Person age is a

derived attribute as it changes over time and can be derived from another attribute
(Date of birth).

E-R diagram with multivalued and derived attributes:

PIET DS
INTYRODUCTION TO DBMS (DBMS) (03606207)

29) Draw ER diagram for Hospital Management[4 MARKS]

30) Draw an ER Diagram for Library Management System.[4 MARKS]

PIET DS
 UNIT-2- Functional Dependency and Normalization)

31) Explain various normal forms of DBMS.[3 MARKS PER EACH NF]

Ans: Normalization

o Normalization is the process of organizing the data in the database.

o Normalization divides the larger table into the smaller table and links them using
relationship.
o The normal form is used to reduce redundancy from the database table.
If a database design is not perfect, it may contain anomalies, which are like a bad dream
for any database administrator. Managing a database with anomalies is next to impossible.
o Update anomalies − If data items are scattered and are not linked to each other properly,
then it could lead to strange situations. For example, when we try to update one data item
having its copies scattered over several places, a few instances get updated properly while
a few others are left with old values. Such instances leave the database in an inconsistent
state.
o Deletion anomalies − we tried to delete a record, but parts of it was left undeleted because
of unawareness, the data is also saved somewhere else.
o Insert anomalies − we tried to insert data in a record that does not exist at all.

Types of Normal Forms

There are the four types of normal forms:

NormalFor Description
m

1NF A relation is in 1NF if it contains an atomic value.

A relation will be in 2NF if it is in 1NF and all non-key attributes are

2NF
fullyfunctional dependent on the primary key.

3NF A relation will be in 3NF if it is in 2NF and no transition dependency exists.

A relation will be in 4NF if it is in Boyce Codd normal form and has no multi-
4NF
valued dependency.
 INTYRODUCTION TO DBMS (DBMS) (03606207)

❖ First Normal Form

First Normal Form is defined in the definition of relations (tables) itself. This rule defines that all
the attributes in a relation must have atomic domains. The values in an atomic domain are
indivisible units.

Re-arrange the relation (table) as below, to convert it to First Normal Form.

Each attribute must contain only a single value from its pre-defined domain.
❖ Second Normal Form
Before we learn about the second normal form, we need to understand the following −
• Prime attribute − An attribute, which is a part of the candidate-key, is known as a prime
attribute.
• Non-prime attribute − An attribute, which is not a part of the prime-key, is said to be a non-
prime attribute.
If we follow second normal form, then every non-prime attribute should be fully functionally
dependent on prime key attribute. That is, if X → A holds, then there should not be any proper
subset Y of X, for which Y → A also holds true.

PIET DS
INTYRODUCTION TO DBMS (DBMS) (03606207)

In Student_Project relation that the prime key attributes are Stu_ID and Proj_ID. According to the
rule, non-key attributes, i.e. Stu_Name and Proj_Name must be dependent upon both and not on
any of the prime key attribute individually. But we find that Stu_Name can be identified by Stu_ID
and Proj_Name can be identified by Proj_ID independently. This is called partial dependency,
which is not allowed in Second Normal Form.

broke the relation in two as depicted in the above picture. So there exists no partial
dependency.
❖ Third Normal Form
For a relation to be in Third Normal Form, it must be in Second Normal form and the following
must satisfy −

No non-prime attribute is transitively dependent on prime key attribute.

For any non-trivial functional dependency, X → A, then either −

o X is a superkey or,

o A is prime attribute.

Find that in the above Student_detail relation, Stu_ID is the key and only prime key attribute.
We find that City can be identified by Stu_ID as well as Zip itself. Neither Zip is a superkey
nor is City a prime attribute. Additionally, Stu_ID → Zip → City, so there exists transitive
dependency.
To bring this relation into third normal form, we break the relation into two relations as
follows −

PIET DS
INTYRODUCTION TO DBMS (DBMS) (03606207)

❖ Boyce-Codd Normal Form

Boyce-Codd Normal Form (BCNF) is an extension of Third Normal Form on strict terms.
BCNF states that −

• For any non-trivial functional dependency, X → A, X must be a super-key.

In the above image, Stu_ID is the super-key in the relation Student_Detail and Zip is the
super-key in the relation ZipCodes. So,
Stu_ID → Stu_Name, Zip
and
Zip → City
Which confirms that both the relations are in BCNF.

32) Explain Functional Dependency in DBMS.[3 MARKS]

Ans: A functional dependency is a constraint that specifies the relationship between
two sets ofattributes where one set can accurately determine the value of other sets.
It is denoted as X →
Y,[Link]
ntheleft side of the arrow, X is called Determinant, while on the right side, Y is called
the
[Link]
databaseentitiesandareveryimportanttounderstandadvancedconceptsinRelationalDat
abaseSystemandunderstanding problems in competitive exams like Gate.

Example:
roll_no name dept_name dept_building

42 abc CO A4

43 pqr IT A3

44 xyz CO A4

PIET DS
INTYRODUCTION TO DBMS (DBMS) (03606207)

45 xyz IT A3

46 mno EC B2

47 jkl ME B2

From the above table we can conclude some valid functional dependencies:

➢ roll_no→{name,dept_name,dept_building},→Here,roll_nocandeterminevaluesoff
ields name, dept_name and dept_building, hence a valid Functional dependency
➢ roll_no→dept_name,Since,roll_nocandeterminewholesetof{name,dept_name,dep
t_building}, it can determine its subset dept_name also.
➢ dept_name→dept_building,Dept_namecanidentifythedept_buildingaccurately,sin
cedepartments with different dept_name will also have a different dept_building
➢ Morevalidfunctionaldependencies:roll_no→name,{roll_no,name}⇢{dept_name,
dept_building}, etc.

Some invalid functional dependencies:

➢ name → dept_name Students with the same name can have different dept_name,
hence thisis not a valid functional dependency.
➢ dept_building → dept_nameThere can be multiple departments in the same
building, Forexample, in the above table departments ME and EC are in the same
building B2, hencedept_building → dept_name is an invalid functional
dependency.
➢ More invalid functional dependencies: name → roll_no, {name, dept_name} →
roll_no,dept_building → roll_no, etc.

33) List and explain various Inference Rules. [3 MARKS PER EACH IR]

Ans:

Inference Rule (IR):

➢ The Armstrong's axioms are the basic inference rule.

➢ Armstrong's axioms are used to conclude functional dependencies on a relational database.

➢ The inference rule is a type of assertion. It can apply to a set of FD(functional

dependency) toderive other FD.

➢ Using the inference rule, we can derive additional functional dependency from the initial
set.

PIET DS
INTYRODUCTION TO DBMS (DBMS) (03606207)

The Functional dependency has 6 types of inference rule:

❖ Reflexive Rule (IR1)

In the reflexive rule, if Y is a subset of X, then X determines Y.

IfX⊇Ythen X→Y

Example:

1.X = {a, b, c, d, e}
2.Y = {a, b, c}

❖ Augmentation Rule (IR2)

The augmentation is also called as a partial dependency. In augmentation, if Xdetermines

Y, then XZdetermines YZ for any Z.

If X →Y then XZ→ YZ

Example:

1. For R(ABCD),ifA→ B then AC→ BC

❖ Transitive Rule (IR3)

In the transitive rule, if X determines Y and Y determine Z, then X must also determine Z.

o If X→ Y and Y→Z then X→ Z

❖ Union Rule (IR4)

Union rule says, if X determines Y and X determines Z, then X must also determine Y and Z.

If X →Y and X →Z then X→ YZ

Proof:

X → Y (given)
X → Z (given)

PIET DS
INTYRODUCTION TO DBMS (DBMS) (03606207)

X → XY (using IR2on 1 by augmentation with X. Where XX = X)

XY → YZ (using IR2on 2 by augmentation with Y)
X → YZ (using IR3on 3 and 4)

❖ Decomposition Rule (IR5)

Decomposition rule is also known as project rule. It is the reverse of union rule.

This Rule says, if X determines Y and Z, then X determines Y and X determines Z separately.

If X → YZ then X → Y and X→ Z

Proof:

X → YZ (given)
YZ → Y (using IR1Rule)
X → Y (using IR3on 1 and 2)

❖ Pseudo transitive Rule (IR6)

In Pseudo transitive Rule, if X determines Y and YZ determines W, then XZ determines W.

If X→ Y and YZ → W then XZ → W

Proof:

X → Y (given)
WY → Z (given)
WX → WY (using IR2on 1 by augmenting with W)
WX → Z (using IR3on 3 and 2)

PIET DS