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)