Reg.
No:24KB1A05AJ
Name of the Students: M. VISHNU VARDHAN
Year & Section: II B. Tech II Sem & C
Assignment No: 3
Staff Name: V. SURENDRA REDDY SIR
DATABASE MANAGEMENT SYSTEM
SQL – 2 Marks Questions Answers
1. Write Aggregate Functions?
Aggregate functions perform calculations on a set of values and return a single value.
Common aggregate functions:
COUNT() – Returns the number of rows.
SUM() – Returns the total sum of a numeric column.
AVG() – Returns the average value.
MIN() – Returns the smallest value.
MAX() – Returns the largest value.
Example:
SELECT COUNT(*) FROM Students;
2. Demonstrate Subquery?
A subquery is a query inside another SQL query. It is used to retrieve data that will be used in the main
query.
Example:
SELECT name
FROM Students
WHERE marks > (SELECT AVG(marks) FROM Students);
Here the inner query calculates the average marks and the outer query selects students scoring above
average.
3. Demonstrate group by Clause?
GROUP BY is used to arrange identical data into groups. It is often used with aggregate functions.
Example:
SELECT department, COUNT(*)
N.B.K.R. Institute of Science & Technology-Vidyanagar Computer Science & Engineering
FROM Employees
GROUP BY department;
This groups employees based on department and counts the number of employees in each department.
4. Explain Order by Clause in SQL?
ORDER BY is used to sort the result set in ascending or descending order.
Example:
SELECT name, marks
FROM Students
ORDER BY marks DESC;
ASC – Ascending order (default)
DESC – Descending order
5. Explain View?
A view is a virtual table based on the result of an SQL query. It does not store data itself but shows data
from one or more tables.
Example:
CREATE VIEW Student view AS
SELECT name, marks FROM Students;
Views help in security, simplify complex queries, and present data in a structured format.
N.B.K.R. Institute of Science & Technology-Vidyanagar Computer Science & Engineering
PART B – Large Questions with Answers
[Link] and Logical Operations in SQL
SQL provides different types of operators to perform calculations, comparisons, and logical decision-making
on data stored in tables.
1. Arithmetic Operators
Arithmetic operators are used to perform mathematical calculations on numeric values.
Operator Description Example
+ Addition salary + bonus - Subtraction marks - deduction
* Multiplication quantity * price
/ Division total / subjects
% or MOD Remainder MOD(10,3)
Example Queries
-- Calculate total marks
SELECT 60 + 55 + 45 + 70 AS total_marks FROM dual;
-- Calculate percentage
SELECT (230 / 400) * 100 AS percentage FROM dual;
-- Find remainder
SELECT MOD(10,3) AS remainder FROM dual;
Applications
Student mark calculations
Employee salary computation
Profit and loss analysis
Financial reports
2. Comparison (Relational) Operators
Comparison operators are used to compare two values and return TRUE or FALSE.
Operator Meaning
Example Queries
-- Find students with marks greater than 60
SELECT * FROM student WHERE marks > 60;
N.B.K.R. Institute of Science & Technology-Vidyanagar Computer Science & Engineering
-- Find employees earning salary >= 30000
SELECT * FROM employee WHERE salary >= 30000;
-- Find students whose department is not CSE
SELECT * FROM student WHERE dept <> 'CSE';
Importance
Used for record filtering
Helps in decision making
Essential in data retrieval queries
3. Logical Operators
Logical operators are used to combine multiple conditions in SQL queries.
Operator Description
AND Both conditions must be true
OR At least one condition must be true
NOT Reverses the condition
Example Queries
-- Find CSE students who scored more than 60 marks
SELECT * FROM student
WHERE department = 'CSE'
AND marks > 60;
-- Find employees who earn more than 30000 or are managers
SELECT * FROM employee
WHERE salary > 30000
OR designation = 'Manager';
-- Using NOT operator
SELECT * FROM student
WHERE NOT dept = 'ECE';
Advantages
Combine multiple conditions
Create complex queries
Improve filtering accuracy
N.B.K.R. Institute of Science & Technology-Vidyanagar Computer Science & Engineering
2. SQL Functions with Examples
SQL functions are built-in operations used to perform calculations, manipulate data, and transform values.
1. Date and Time Functions
Function Description
SYSDATE Returns current system date
CURRENT_DATE Returns current session date
ADD_MONTHS Adds months to a date
LAST_DAY Returns last day of the month
Example Queries
-- Display current system date
SELECT SYSDATE FROM dual;
-- Add two months to current date
SELECT ADD_MONTHS(SYSDATE, 2) FROM dual;
-- Find last day of current month
SELECT LAST_DAY(SYSDATE) FROM dual;
2. Numeric Functions
Function Description
ABS() Returns absolute value
CEIL() Returns smallest integer greater than value
Function Description
FLOOR() Returns largest integer less than value
ROUND() Rounds number to specified decimals
SQRT() Returns square root
Example Queries
SELECT ABS(-10) FROM dual;
SELECT CEIL(12.3) FROM dual;
SELECT FLOOR(12.3) FROM dual;
SELECT ROUND(123.456, 2) FROM dual; SELECT SQRT(25) FROM dual;
N.B.K.R. Institute of Science & Technology-Vidyanagar Computer Science & Engineering
3. String Functions
Function Description
UPPER() Converts string to uppercase
LOWER() Converts string to lowercase
LENGTH() Returns string length
SUBSTR() Extracts substring
Example Queries
-- Convert text to uppercase
SELECT UPPER('oracle') FROM dual;
-- Find length of string
SELECT LENGTH('DATABASE') FROM dual;
-- Extract substring
SELECT SUBSTR('ORACLE', 1, 3) FROM dual;
4. Conversion Functions
Function Conversion
TO_CHAR Number/Date to Character
TO_NUMBER Character to Number
TO_DATE Character to Date
Example Queries
-- Convert date to character
SELECT TO_CHAR(SYSDATE, 'DD-MM-YYYY') FROM dual;
-- Convert string to number
SELECT TO_NUMBER('100') + 50 FROM dual;
3. Demonstrate Key and Integrity Constraints
Constraints are rules applied to database tables to maintain data accuracy, reliability, and consistency. Key
Constraints
1. Primary Key
A Primary Key uniquely identifies each record in a table. It cannot contain NULL values or duplicates.
N.B.K.R. Institute of Science & Technology-Vidyanagar Computer Science & Engineering
CREATE TABLE STUDENT (
SID INT PRIMARY KEY,
SNAME VARCHAR(30)
);
2. Candidate Key
A candidate key is a column or group of columns that can uniquely identify records.
Example:
SID
EMAIL
3. Alternate Key
A candidate key that is not chosen as the primary key is called an alternate key.
Example:
Email ID
Aadhar Number
4. Unique Key
Ensures that all values in a column are unique.
CREATE TABLE STUDENT (
SID INT PRIMARY KEY,
EMAIL VARCHAR(50) UNIQUE
);
5. Foreign Key
A Foreign Key creates a relationship between two tables by referencing the primary key of another table.
CREATE TABLE DEPARTMENT (
DID INT PRIMARY KEY,
DNAME VARCHAR(30)
);
CREATE TABLE EMPLOYEE (
EID INT PRIMARY KEY,
N.B.K.R. Institute of Science & Technology-Vidyanagar Computer Science & Engineering
ENAME VARCHAR(30),
DID INT,
FOREIGN KEY (DID) REFERENCES DEPARTMENT(DID)
);
Integrity Constraints
Constraint Type Description Example
Entity Integrity Primary key cannot be NULL PRIMARY KEY
Referential Integrity Foreign key must match primary key values FOREIGN KEY
Domain Integrity Restricts column values within range CHECK
Example with CHECK Constraint
CREATE TABLE STUDENT (
SID INT PRIMARY KEY,
SNAME VARCHAR(30),
AGE INT CHECK (AGE >= 18)
);
Importance of Constraints
Maintains data accuracy
Prevents invalid data entry
Establishes relationships between tables
Improves database reliability
4. Different Types of Joins in SQL
A JOIN combines data from two or more tables based on a related column.
Sample Tables EMPLOYEE
Table
emp_id emp_name dept_id
Ravi 10
Sita 20
Arjun NULL
N.B.K.R. Institute of Science & Technology-Vidyanagar Computer Science & Engineering
DEPARTMENT Table
dept_id dept_name
10 CSE
20 ECE
30 MECH
1. INNER JOIN
Returns only records that have matching values in both tables.
SELECT emp.emp_id, emp.emp_name, dept.dept_name
FROM employee emp
INNER JOIN department dept
ON emp.dept_id = dept.dept_id;
Result: Ravi, Sita
2. LEFT JOIN
Returns all records from the left table and matching records from the right table.
SELECT emp.emp_name, dept.dept_name
FROM employee emp
LEFT JOIN department dept
ON emp.dept_id = dept.dept_id;
Result: Arjun appears with NULL department.
3. RIGHT JOIN
Returns all records from the right table and matching records from the left table.
SELECT emp.emp_name, dept.dept_name
FROM employee emp
RIGHT JOIN department dept
ON emp.dept_id = dept.dept_id;
Result: MECH appears with NULL employee.
4. FULL JOIN
Returns all records from both tables.
N.B.K.R. Institute of Science & Technology-Vidyanagar Computer Science & Engineering
SELECT emp.emp_name, dept.dept_name
FROM employee emp
FULL JOIN department dept
ON emp.dept_id = dept.dept_id;
Result: All employees and all departments with NULL where no match exists.
Advantages of Joins
Combine data from multiple tables
Reduce redundancy
Improve data retrieval efficiency
Maintain relational structure
5. Types of Views with Examples
A View is a virtual table created from a SQL query result. It does not store data physically.
1. Simple View
Created from a single table without complex operations.
CREATE VIEW student_view AS SELECT
name, dept FROM student;
Retrieve data:
SELECT * FROM student_view;
2. Complex View
Created using multiple tables, joins, or aggregate functions.
CREATE VIEW dept_salary AS
SELECT dept, SUM(salary) AS total_salary
FROM employee
GROUP BY dept; Retrieve data:
SELECT * FROM dept_salary;
3. Read-Only View
Does not allow INSERT, UPDATE, or DELETE operations.
CREATE VIEW emp_view AS
N.B.K.R. Institute of Science & Technology-Vidyanagar Computer Science & Engineering
SELECT * FROM employee
WITH READ ONLY;
-- This will fail
INSERT INTO emp_view VALUES (4, 'John', 10);
Advantages of Views
Advantage Description
Data Security Hide sensitive columns
Query Simplification Complex queries become simple
Data Abstraction Users see only required data
Logical Independence Table changes may not affect view
Query Reusability Write once, use many times
Disadvantages of Views
Disadvantage Description
Performance Complex views may execute slowly
Update Restrictions Some views cannot be updated
Dependency Depends on base tables Storage
DBMS stores view definitions
N.B.K.R. Institute of Science & Technology-Vidyanagar Computer Science & Engineering