Lab Report

MD: Mie Mahmud Sifat

Lab 1
--------------------------------

Create Database Name : Miel368.

The Create Table : Customer

Input :
USE miel368;

CREATE TABLE Customer

(CustomerName Varchar(100),

CustomerID int ,

Purchase_Item Varchar(100),

Address Varchar(100),

PostalCode int ,

Phone_Number int

);

Output:

Then Insert Data ------------------

Input:
INSERT INTO Customer (

CustomerName,

CustomerID,

Purchase_Item,

Address,

PostalCode,

Phone_Number

VALUES

('Adiba Mehjabin', 1, 'Three Piece', 'Dhanmondi', 1205, '01317453678'),

('Rafiqul Islam', 2, 'Wallet', 'Mirpur', 1216, '01617453879'),

('Sanjila Islam', 3, 'Kurti', 'Uttarkhan', 1106, '01717483677'),

('Sharifa Begum', 4, 'Bed Sheet', 'Sector 10, Uttara', 1108, '01917453889'),

('Amina Begum', 5, 'Two Piece', 'Badda', 1300, '01317457680'),

('Monowara Akter', 6, 'Ear Ring', 'Mirpur 12', 1217, '01518453669');

Output:
 Lab 2
--------------------------------

1. Add a new column with name CustomerGrade

Input :
USE miel368;

ALTER TABLE Customer

ADD CustomerGrade VARCHAR(20);

Output:

2. Delete PostalCode column.

ALTER TABLE Customer DROP COLUMN PostalCode;

3. Rename address column to AddressDetails

USE miel368;
ALTER TABLE Customer

CHANGE COLUMN Address AddressDetails VARCHAR(255);

4 .Change the data type of phone number column.

USE miel368;

ALTER TABLE Customer

MODIFY COLUMN Phone_Number VARCHAR(15);

SELECT * FROM Customer;

5. Change the purchage iteam “kurta” to “shirt”

UPDATE Customer

SET Purchase_Item = 'Shirt'

WHERE Purchase_Item = 'Kurti';

USE miel368;

ALTER TABLE Customer

ADD Customer_ID INT AUTO_INCREMENT PRIMARY KEY;

SELECT * FROM Customer;

Select the information of customers whose grade is “gold”

SELECT * FROM Customer

WHERE CustomerGrade = 'Gold';

9. Truncate customer table.

TRUNCATE TABLE Customer;

USE miel368;

DROP TABLE Customer;

Lab 3
--------------------------------

The Table :

CREATE TABLE Customer (

CustomerID INT PRIMARY KEY,

CustomerName VARCHAR(100),

Purchase_Iteam VARCHAR(50),

Address VARCHAR(100),

PostalCode INT,

Phone_Number VARCHAR(15),

Grade VARCHAR(20)

);

INSERT INTO Customer (CustomerID, CustomerName, Purchase_Iteam, Address, PostalCode,

VALUES

(1, 'Adiba Mehjabin', 'Three Piece', 'Dhanmondi', 1205, '0131745367', 'Silver'),

(2, 'Rafiqul Islam', 'Wallet', 'Mirpur 1', 1216, '0161745387', 'Silver'),

(3, 'Sanjila Islam', 'Kurti', 'Uttarkhan', 1106, '0171748367', 'Gold'),

(4, 'Sharifa Begum', 'Bed Sheet', 'Sector 7', 1108, '0191745388', 'Silver'),

(5, 'Amina Begum', 'Two Piece', 'Badda', 1300, '0131745768', 'Platinum'),

(6, 'Monowara Akter', 'Ear Ring', 'Mirpur 12', 1217, '0151845366', 'Gold');
 1. Find out the “Gold” customer.

USE miel368;

SELECT * FROM Customer

WHERE Grade = 'Gold';

2. Find out the information of customer who purchase “Wallet”

3. Find the phone number of all customer.

5. Find out the grade of customer who purchage “bed sheet” and whose grade is “silver”.

USE miel368;
SELECT Grade
FROM Customer
WHERE Purchase_Iteam = 'Bed Sheet' AND Grade = 'Silver';

6. Find out the name of customer whose postal code is “1106” or whose id is “6”.

USE miel368;
SELECT CustomerName
FROM Customer
WHERE PostalCode = 1106 OR CustomerID = 6;

7. Find out the information of customer whose name start with “M”.
USE miel368;
SELECT *
FROM Customer
WHERE CustomerName LIKE 'M%';

8. Find out the information of customer whose name end with “M”.
USE miel368;
SELECT *
FROM Customer
WHERE CustomerName LIKE '%M';
 9. Find out the information of customer whose name contain “M” in second position.
USE miel368;

SELECT *
FROM Customer
WHERE CustomerName LIKE '_M%';

10. Find out the information of customer whose name is “Sanjila Islam”.
USE miel368;

SELECT *
FROM Customer
WHERE CustomerName = 'Sanjila Islam';

Lab 4
--------------------------------

Ans to the question no 1:

USE Student1;

CREATE TABLE Students

Student_ID int,

Name Varchar(100),

Age int ,

Major Varchar(100)
);

Then

USE Student1;

CREATE TABLE Courses

Course_ID int,

Courses_Nmae Varchar (100),

Creadits int,

Instructor_ID int

);

Then

USE Student1;

CREATE TABLE Enrolments

Enrolment_ID int,

Student_ID int,

Course_ID int,

Semester Varchar (100),

Grade Varchar (100)

);

1. To Fetch The name and age of students who are majoring in cs.
USE Student1;

INSERT INTO Students

Student_ID ,

Name ,

Age ,

Major

) VALUE

(368,'Sifat',21,'Computer Science'),

(378,'Easin',70,'Operating System'),

(372,'Anas',23,'Networking'),

(388,'Ador',21,'Computer Science');

Then

Input: USE Student1;

SELECT Name,Age FROM Students WHERE Major='Computer Science';

Output:

2. To Retrieve Name of all Students who are enrolled in either Database System or ai ,
4. input:

USE Student1;

SELECT * FROM Students WHERE Name LIKE '__j%' and Name LIKE'%n';

Output : Null

5.Input :

USE Student1;

ALTER TABLE Students

ADD COLUMN Phone_number Varchar(15);

SELECT * FROM Students;

Outpur:

Lab 5
--------------------------------

Name : Sifat

Table Name: Sifat1;

USE Sifat;

CREATE TABLE Sifat1

(Customer_Name Varchar(100),

Customer_Id int,

Address Varchar(100),

Price int

);

Insert Data :

USE Sifat;

INSERT INTO Sifat1

(Customer_Name,

Customer_Id ,

Address ,

Price )

VALUES

('Miel',68,'Mirpur',250),

('Mahedi',96,'uttorA',1000),

('Sadia',70,'Lalkhuthi',5),

('Anas',34,'H Block',48),

('Easin',35,'C Block',45);

The Table:
Between:

USE Sifat;

SELECT * FROM Sifat1

WHERE Price BETWEEN 100 AND 500;

Output:

IN:

USE Sifat;

SELECT * FROM Sifat1

WHERE Address IN ('Mirpur', 'uttorA', 'Lalkhuthi');

Output:
NOT IN :

USE Sifat;

SELECT * FROM Sifat1

WHERE Address NOT IN ('H Block');

Output:

Group By:

USE Sifat;

SELECT Customer_Name,COUNT(Customer_Name) AS TOTAL_Customer

FROM Sifat1

GROUP BY Customer_Name;
ASE:

USE Sifat;

SELECT * From Sifat1

ORDER BY Price ASC;

Output :

DSEC:

USE Sifat;

SELECT * From Sifat1

ORDER BY Price DESC;

USE Sifat;

SELECT * From Sifat1

ORDER BY Price DESC

LIMIT 1 OFFSET 1 ;

USE Sifat;

SELECT * From Sifat1

ORDER BY Price DESC

LIMIT 1 OFFSET 2 ;
MIN;

USE Sifat;

SELECT MIN(Price) FROM Sifat1;

MAX:

USE Sifat;

SELECT MAX(Price) FROM Sifat1;

SELECT AVG(Price) FROM Sifat1;

Count: USE Sifat;

SELECT COUNT(*) FROM Sifat1;