Assignment No-2-Database & Table Operations
● CREATE DATABASE
● SHOW DATABASES
● USE
● CREATE TABLE
● SHOW TABLES
● DESC
● ALTER TABLE
● INSERT
● SELECT
● UPDATE
● DELETE
● DROP TABLE
● DROP DATABASE
Problem Statement:
Create and manage a Student Management Database using MySQL. Perform basic database and
table operations such as creating a database, viewing databases and tables, creating and
describing tables, modifying table structure, inserting and retrieving records, updating and
deleting records, and finally deleting the table and database.
MySql Commands and Syntax:
1. CREATE DATABASE
CREATE DATABASE database_name;
CREATE DATABASE StudentDB;
2. SHOW DATABASES
Display all databases available on the MySQL server and verify that StudentDB has been
created.
SHOW DATABASES;
3. USE
Select StudentDB as the current database so that all subsequent table operations are performed
within this database.
USE database_name;
USE StudentDB;
4. CREATE TABLE
Create a table named Student to store the following information:
● Student ID
● Student Name
● Course
● Age
● City
● Marks
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype
);
CREATE TABLE Student (
Student_ID INT,
Student_Name VARCHAR(50),
Course VARCHAR(30),
Age INT,
City VARCHAR(30),
Marks INT
);
5. SHOW TABLES
Display all tables available in the currently selected StudentDB database and verify that the
Student table has been created.
SHOW TABLES;
6. DESC
Display the structure of the Student table, including column names, data types, and other
properties.
DESC table_name;
DESC Student;
7. ALTER TABLE
The ALTER TABLE command is used to modify the structure of an existing table.
The college has decided to add a new column named Email to the Student table to store students'
email addresses.
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE Student ADD Email VARCHAR(100);
Another Example – Modify a Column
Suppose the college wants the Marks column to store decimal values.
ALTER TABLE table_name MODIFY column_name new_datatype;
ALTER TABLE Student MODIFY Marks DECIMAL(5,2);
Another Example – Rename a Column
ALTER TABLE Student RENAME COLUMN Student_Name TO Name;
8. INSERT
Insert the following student records into the Student table.
● INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2,
value3, ...);
● INSERT INTO Student (Student_ID, Student_Name, Course, Age, City, Marks, Email)
VALUES (101, 'Rahul', 'BCA', 20, 'Pune', 85, 'rahul@[Link]');
● INSERT INTO Student (Student_ID, Student_Name, Course, Age, City, Marks, Email)
VALUES
(102, 'Priya', 'BCA', 21, 'Mumbai', 91, 'priya@[Link]'),
(103, 'Amit', 'BSc CS', 20, 'Nashik', 78, 'amit@[Link]'),
(104, 'Sneha', 'BCA', 19, 'Pune', 88, 'sneha@[Link]'),
(105, 'Rohan', 'BSc CS', 21, 'Nagpur', 76, 'rohan@[Link]');
9. SELECT
Display all records stored in the Student table.
SELECT * FROM table_name;
SELECT * FROM Student;
Display Specific Columns
SELECT Student_ID, Student_Name, Course, Marks FROM Student;
Display the details of students who have scored more than 80 marks.
SELECT * FROM Student WHERE Marks > 80;
10. UPDATE
Student 103 has received updated marks. Change Amit's marks from 78 to 82.
UPDATE table_name SET column_name = new_value WHERE condition;
UPDATE Student SET Marks = 82 WHERE Student_ID = 103;
UPDATE Student SET Marks = 82;
11. DELETE
Student 105 has left the college. Delete the record of student 105 from the Student table.
DELETE FROM table_name WHERE condition;
DELETE FROM Student WHERE Student_ID = 105;
DELETE FROM Student;
12. DROP TABLE/Column/Database
The college no longer requires the Student table. Delete the complete Student table from the
StudentDB database.
DROP TABLE table_name;
DROP TABLE Student;
DROP COLUMN using ALTER TABLE
The college has decided that the Email information is no longer required. Remove the Email
column from the Student table.
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE Student DROP COLUMN Email;
DROP DATABASE
The Student Management project has been completed, and the entire StudentDB database is no
longer required. Delete the database from the MySQL server.
DROP DATABASE database_name;
DROP DATABASE StudentDB;