DBMS Assignment 4

Nemish Mogra
SY 11
Roll. No 25

Aim: Demonstrate implementation of DML commands of SQL with suitable examples

1)Insert
2)Update
3)Delete
4)Select
Software Required: MySQL

Exercises:

I. Design an SQL command to insert a new product record into the "Products" table
of the online store database, including details such as product name, price,
quantity available, and category.

mysql> CREATE DATABASE OnlineStoreDB;

Query OK, 1 row affected (0.06 sec)

mysql> USE OnlineStoreDB;

Database changed
mysql>
mysql> CREATE TABLE Products (
-> ProductID INT AUTO_INCREMENT PRIMARY KEY,
-> ProductName VARCHAR(100) NOT NULL,
-> Price DECIMAL(10,2) NOT NULL,
-> QuantityAvailable INT NOT NULL,
-> Category VARCHAR(50)
-> );
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql> INSERT INTO Products (ProductName, Price, QuantityAvailable, Category)
-> VALUES ('Wireless Mouse', 799.99, 50, 'Electronics');
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> SELECT * FROM Products;
+-----------+----------------+--------+-------------------+-------------+
| ProductID | ProductName | Price | QuantityAvailable | Category |
+-----------+----------------+--------+-------------------+-------------+
| 1 | Wireless Mouse | 799.99 | 50 | Electronics |
+-----------+----------------+--------+-------------------+-------------+
1 row in set (0.00 sec)

mysql>

II. Create a set of DML commands to update the "Employee" table in the HR
database, modifying the salary of an employee based on their performance rating and
position.

mysql> CREATE DATABASE HRDB;

Query OK, 1 row affected (0.01 sec)
mysql> USE HRDB;
Database changed
mysql>
mysql> CREATE TABLE Employee (
-> EmployeeID INT AUTO_INCREMENT PRIMARY KEY,
-> Name VARCHAR(100),
-> Position VARCHAR(50),
-> Salary DECIMAL(10,2),
-> PerformanceRating VARCHAR(20)
-> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO Employee (Name, Position, Salary, PerformanceRating)
-> VALUES ('John Doe', 'Manager', 65000, 'Excellent'),
-> ('Jane Smith', 'Developer', 50000, 'Good');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql>
mysql> UPDATE Employee
-> SET Salary =
-> CASE
-> WHEN Position = 'Manager' AND PerformanceRating = 'Excellent' THEN
Salary * 1.10
-> WHEN Position = 'Developer' AND PerformanceRating = 'Good' THEN
Salary * 1.05
-> ELSE Salary
-> END;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql>
mysql> SELECT * FROM Employee;
+------------+------------+-----------+----------+-------------------+
| EmployeeID | Name | Position | Salary | PerformanceRating |
+------------+------------+-----------+----------+-------------------+
| 1 | John Doe | Manager | 71500.00 | Excellent |
| 2 | Jane Smith | Developer | 52500.00 | Good |
+------------+------------+-----------+----------+-------------------+
2 rows in set (0.00 sec)

mysql>

III. Develop an SQL script to delete all inactive user accounts from the "Users"
table of the social networking database, where the last login date is older than
six months.

mysql> CREATE DATABASE SocialNetworkDB;

Query OK, 1 row affected (0.01 sec)

mysql> USE SocialNetworkDB;

Database changed
mysql>
mysql> CREATE TABLE Users (
 -> UserID INT AUTO_INCREMENT PRIMARY KEY,
-> Username VARCHAR(50),
-> LastLogin DATE,
-> Status VARCHAR(20)
-> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO Users (Username, LastLogin, Status)
-> VALUES ('Alice', '2024-01-01', 'Inactive'),
-> ('Bob', '2025-03-01', 'Active'),
-> ('Charlie', '2024-12-01', 'Inactive');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql>
mysql> DELETE FROM Users
-> WHERE LastLogin < DATE_SUB(CURDATE(), INTERVAL 6 MONTH);
Query OK, 3 rows affected (0.01 sec)

mysql>
mysql> SELECT * FROM Users;
Empty set (0.00 sec)

mysql>

IV. Design a series of DML commands to insert a new patient's medical record into
the "Patients" table of the hospital database, capturing information like patient
ID, name, date of birth, admission date, and medical condition.

mysql> CREATE DATABASE HospitalDB;

Query OK, 1 row affected (0.01 sec)

mysql> USE HospitalDB;

Database changed
mysql>
mysql> CREATE TABLE Patients (
-> PatientID INT PRIMARY KEY,
-> Name VARCHAR(100),
-> DateOfBirth DATE,
-> AdmissionDate DATE,
-> MedicalCondition VARCHAR(255)
-> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO Patients (PatientID, Name, DateOfBirth, AdmissionDate,
MedicalCondition)
-> VALUES (101, 'Ravi Kumar', '1990-08-15', '2025-09-05', 'Diabetes');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT * FROM Patients;
+-----------+------------+-------------+---------------+------------------+
| PatientID | Name | DateOfBirth | AdmissionDate | MedicalCondition |
+-----------+------------+-------------+---------------+------------------+
| 101 | Ravi Kumar | 1990-08-15 | 2025-09-05 | Diabetes |
+-----------+------------+-------------+---------------+------------------+
1 row in set (0.00 sec)

mysql>

V. Create an SQL command to update the "Inventory" table in the retail store
database, increasing the quantity of a specific product that has been restocked.

mysql> CREATE DATABASE RetailStoreDB;

Query OK, 1 row affected (0.00 sec)

mysql> USE RetailStoreDB;

Database changed
mysql>
mysql> CREATE TABLE Inventory (
-> ProductID INT AUTO_INCREMENT PRIMARY KEY,
-> ProductName VARCHAR(100),
-> Quantity INT
-> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO Inventory (ProductName, Quantity)
-> VALUES ('Laptop', 10),
-> ('Headphones', 25);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql>
mysql> UPDATE Inventory
-> SET Quantity = Quantity + 20
-> WHERE ProductName = 'Laptop';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql>
mysql> SELECT * FROM Inventory;
+-----------+-------------+----------+
| ProductID | ProductName | Quantity |
+-----------+-------------+----------+
| 1 | Laptop | 30 |
| 2 | Headphones | 25 |
+-----------+-------------+----------+
2 rows in set (0.00 sec)

mysql>

FAQS

I. What is the purpose of the WHERE clause in SQL DML commands?

The WHERE clause is used to filter records in SELECT, UPDATE, and DELETE
statements. It ensures that only the rows meeting the specified condition are
affected. Without WHERE, the command applies to all rows in the table.
II. How can I update multiple columns in a table using UPDATE?
You can update multiple columns by separating each column assignment with a comma.
Example:

UPDATE Employee
SET Salary = 60000,
Position = 'Senior Developer'
WHERE EmployeeID = 2;

III. Can I insert data into multiple tables at once?

Standard SQL does not allow inserting into multiple tables with a single INSERT
statement. You must use separate INSERT statements for each table. However, some
databases support triggers or stored procedures to insert into multiple tables
automatically.

IV. What is the difference between the INSERT and UPDATE commands?

INSERT is used to add new records into a table.

UPDATE is used to modify existing records in a table.

INSERT adds data, while UPDATE changes existing data based on a condition.