DEV Community

Abdul Haseeb
Abdul Haseeb

Posted on

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
);
Enter fullscreen mode Exit fullscreen mode

2. Display the list of tables in BankXXX database

SHOW TABLES IN BankXXX;
Enter fullscreen mode Exit fullscreen mode

3. Display the structure of the Deposit table

DESCRIBE Deposit;
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

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)
);
Enter fullscreen mode Exit fullscreen mode

6. Display the list of tables in MSEBXXX database

SHOW TABLES IN MSEBXXX;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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
);
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

10. Rename the STOCK table to Item

RENAME TABLE STOCK TO Item;
Enter fullscreen mode Exit fullscreen mode

11. Display all the databases

SHOW DATABASES;
Enter fullscreen mode Exit fullscreen mode

12. Display those databases whose name starts with letter B

SHOW DATABASES LIKE 'B%';
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Top comments (0)