Sem V MYSQL Practical 1
Abdul Haseeb
Posted on September 4, 2024
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;
💖 💪 🙅 🚩
Abdul Haseeb
Posted on September 4, 2024
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.
Related
