Sem V MYSQL Practical 1

1. Create a database BankXXX and a table Deposit

CREATE DATABASE BankXXX;

USE BankXXX;

CREATE TABLE Deposit (
    DepositCode INT(10) PRIMARY KEY,
    AmountDeposited DECIMAL(8, 2) CHECK (AmountDeposited > 0),
    DateOfRegistration DATE NOT NULL
);

2. Display the list of tables in BankXXX database

SHOW TABLES IN BankXXX;

3. Display the structure of the Deposit table

DESCRIBE Deposit;

4. Insert records into the Deposit table

INSERT INTO Deposit (DepositCode, AmountDeposited, DateOfRegistration) VALUES 
(1256, 10000.00, '2023-12-25'),
(1363, 80000.00, '2024-01-31'),
(5623, 50000.00, '2024-04-25');

5. Create a database MSEBXXX and a table Electric

CREATE DATABASE MSEBXXX;

USE MSEBXXX;

CREATE TABLE Electric (
    ConsumerNumber INT,
    ConsumerName VARCHAR(15),
    MeterNumber CHAR(10),
    TypeOfConnection CHAR(15) DEFAULT 'Residential',
    BillAmount DECIMAL(8, 2)
);

6. Display the list of tables in MSEBXXX database

SHOW TABLES IN MSEBXXX;

7. Insert records into the Electric table

INSERT INTO Electric (ConsumerNumber, ConsumerName, MeterNumber, TypeOfConnection, BillAmount) VALUES
(101, 'John Doe', 'MTR1234567', 'Commercial', 1500.50),
(102, 'Jane Smith', 'MTR7654321', 'Residential', 1200.00);

8. Create a database PayXXX and a table STOCK

CREATE DATABASE PayXXX;

USE PayXXX;

CREATE TABLE STOCK (
    ItemNo INT PRIMARY KEY,
    Name CHAR(15),
    OpeningStock INT DEFAULT 0,
    Purchase INT CHECK (Purchase >= 0),
    ClosingStock INT,
    Price DECIMAL(9, 2),
    DateOfSale DATE
);

9. Insert records into the STOCK table

INSERT INTO STOCK (ItemNo, Name, OpeningStock, Purchase, ClosingStock, Price, DateOfSale) VALUES
(1, 'Item A', 100, 50, 150, 200.00, '2023-12-01'),
(2, 'Item B', 200, 75, 275, 150.00, '2023-12-15');

10. Rename the STOCK table to Item

RENAME TABLE STOCK TO Item;

11. Display all the databases

SHOW DATABASES;

12. Display those databases whose name starts with letter B

SHOW DATABASES LIKE 'B%';

13. Delete all the tables and databases created

-- Delete tables
DROP TABLE IF EXISTS BankXXX.Deposit;
DROP TABLE IF EXISTS MSEBXXX.Electric;
DROP TABLE IF EXISTS PayXXX.Item;

-- Drop databases
DROP DATABASE IF EXISTS BankXXX;
DROP DATABASE IF EXISTS MSEBXXX;
DROP DATABASE IF EXISTS PayXXX;