Q1. What is the difference between DBMS and RDBMS.

DBMS RDBMS
• Data is stored as a file in • Data is stored in tabular format
hierarchical or navigational with columns named as headers
format. and data in rows .
• Only single user is allowed • Multiple users can work here
• Software and hardware cost is • Software and hardware cost is
low. higher compared to DBMS.
• Normalization is not supported in • A RDBMS can be normalised.
DBMS
• There’s less safety. • Multiple level of security available.
Such as OS and CMD.
• For complex and vast amount of • Because of it’s relational nature,
data, retrieval takes time. data retrieval is quick.

Q2. Explain Normalization with example.

Normalization is the process of organizing the database to reduce the redundancy and
dependency by making simple and manageable tables. It also helps in improving data
integrity.

Example

Unnormalized table:

StudentID Name CourseId Course

1 Rushi 101 Maths
2 Yaju 101 Maths
3 Dhruvi 105 Biology

Normalized Tables:

Student Table:

StudentID Name CourseId

1 Rushi 101
2 Yaju 101
3 Dhruvi 105
Course Table:

CourseId Course
101 Maths
101 Maths
105 Biology

Q3. What is the difference between Primary Key and Foreign Key.
Primary Key:
It is a unique identifier assigned to each record in database table. It uniquely identifies
row and columns in table. It make sures there’s no repetition of data in a particular row
or column.

Foreign Key:
It is a set of columns of one table that references the primary key column of another
table. Purpose of foreign key is to ensure that relation between tables are valid and not
invalid data enters the system.

Difference: Table which has primary key will be Parent Table and Foreign key will be
Child Table.

Q4. What is the concept of ACID property in DBMS.

ACID stands for:
Atomicity: The entire transaction takes place at once or doesn’t happen at all.
Consistency: The database must be consistent before and after the transaction.
Isolation: Multiple transaction occur independently without interference.
Durability: The change of a successful transaction occurs even if the system failure
occurs.

Q5. Explain the purpose of Table and Write a simple query of Insert, Update, Delete and
Select query with example.

Primary purpose of table is to store data in organizational and readable and concise
manner. They can used to highlight trend, patterns or users feedback report or other
information in it.

INSERT:
insert into Employees(EmpID, FirstName, LastName, HireDate, Salary, Department)
values(3,'Rushi','Patel', '2024-02-01', 85000, 101);

UPDATE:

update Employees
set FirstName = 'Jemsi'
where EmpID = 3;

DELETE:

delete from Employees where EmpID = 3;

SELECT:

select FirstName,LastName,Salary from Employees;

Q6.

/*

Write an SQL query to create a table Employees with columns EmpID (INT, Primary Key),

FirstName (VARCHAR 50), LastName (VARCHAR 50), HireDate (DATE), and Salary

(FLOAT). After creating the table, insert an employee record with EmpID = 1, FirstName
= 'John', LastName = 'Smith', HireDate = '2024-01-01', and Salary = 50000.

*/

create database assignment;

use assignment;

CREATE TABLE EMPLOYEES(

EmpID INT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50),

HireDate DATE,

Salary FLOAT

);

INSERT INTO EMPLOYEES(EmpID, FirstName, LastName, HireDate, Salary)

VALUES(

1,'John','Smith','2024-01-01', 50000

);

/*

Update the salary of the employee with EmpID = 1 to 55000. Then, write a query to

retrieve the FirstName, LastName, and Salary of the employee. Combine both the

update and select operations.

*/

UPDATE EMPLOYEES SET Salary = 55000 WHERE EmpID = 1;

SELECT FirstName, LastName, Salary FROM Employees;

Q7.

/*

Add a new column Department (VARCHAR 30) to the Employees table using a DDL

command. After that, retrieve all employees' FirstName, LastName, and Department.

You have two tables, Employees and Departments.

*/

ALTER TABLE Employees

ADD Department VARCHAR(50);

SELECT FirstName, LastName, Department from Employees;

/*

Insert a new department into the Departments table with DeptID = 101 and DeptName =
'Sales'.

*/

CREATE TABLE Departments(

DeptID INT PRIMARY KEY,

DeptName VARCHAR(50)

);

INSERT INTO Departments(DeptID, DeptName)

VALUEs(101,'Sales');

/*

Then, write a query to join Employees and Departments on DeptID to retrieve

FirstName, LastName, and DeptName.

*/

SELECT E.FirstName, E.LastName, D.DeptName FROM Employees E

JOIN Departments D ON E.Department = D.DeptID;

Q8.

/*Delete the employee with EmpID = 1 from the Employees table. After that, retrieve all

remaining employees' FirstName and LastName.

*/

DELETE FROM Employees

WHERE EmpID = 1;

SELECT FirstName, LastName FROM Employees;

/*

INSERT permission on the Employees table to a user named UserA. Then, insert a new

employee record with EmpID = 2, FirstName = 'Alice', LastName = 'Brown', HireDate =

'2024-02-01', and Salary = 60000.

*/

GRANT INSERT ON Employees TO UserA;

INSERT INTO Employees(EmpID, FirstName, LastName, HireDate, Salary)

VALUES(2, 'Alice', 'Brown', '2024-02-01', 60000);

Q9.

/*

SELECT permission from a user named UserA on the Employees table. Then, retrieve all

records from the Employees table to check the remaining permissions for the current

user.

*/

SHOW GRANTS FOR UserA;

/*

Add a column ManagerID (INT) to the Departments table. After that, join Employees

and Departments on DeptID and retrieve the FirstName, LastName, DeptName, and

ManagerID of all employees.

*/

ALTER TABLE Departments ADD ManagerID INT;

SELECT E.FirstName, E.LastName, D.DeptName, D.ManagerID FROM Employees E

JOIN Departments D ON E.Department = D.DeptID;

Q10.

/*

Retrieve all employees who earn a salary greater than 50,000. Then, update the salary

of all employees retrieved by this query by increasing their salary by 10%.

*/

SELECT * FROM Employees

WHERE Salary > 50000;

UPDATE Employees

SET Salary = Salary * 0.1 + Salary

WHERE Salary > 50000;

SELECT * FROM Employees;