MySQL Practical Assignments

Q1. Student Database Operations (SchoolDB)

Database Name: SchoolDB
Table Name: Students

StudentID (INT, Primary Key, Auto Increment)
FullName (VARCHAR(100), NOT NULL)
DOB (DATE)
Gender (ENUM('Male', 'Female'))
Email (VARCHAR(100), UNIQUE)
Phone (VARCHAR(15))

  1. Insert at least 5 records into the Students table.
  2. Insert at least 2 records with only StudentID, FullName, and DOB.
  3. Write a query to add 2 records in a single query.
  4. Display all records.
  5. Display only name, dob, gender of all students.
  6. Show record with StudentID = 4.
  7. Display only Female students.
  8. Display Male students whose name starts with M.
  9. Show students born after 2005.

Q2. Student Course Management (YCTC)

Database Name: YCTC
Table Name: Students

REGNO VARCHAR(30) PRIMARY KEY
Name VARCHAR(100) NOT NULL
COURSE VARCHAR(50) NOT NULL
BATCH INT NOT NULL
FEES INT NOT NULL
DURATION VARCHAR(20)

  1. Insert 10 records in the table.
  2. Count total number of students.
  3. Update BATCH = 24005 where Course = 'CDTA'.
  4. Update Fees = 5000 where Batch = 34002.
  5. Update Duration = '7 month' where COURSE = 'DDTA' and batch is 34006 or 34007.
  6. Add Fees 500 to all students.
  7. Reduce Fees by 400 for students not in CFAS.
  8. Update COURSE of student SUMIT SEN to ADFAS.
  9. Delete students whose COURSE = 'DFAS' and FEES = 4000.

Q3. Table Modification (YOUTH)

Database Name: YOUTH
Table Name: Student

REGNO VARCHAR(30)
Name VARCHAR(100)
COURSE VARCHAR(50)
BATCH INT

  1. Add field MARKS (INT) at the end.
  2. Change field name REGNO to Registration.
  3. Add field ADDRESS (VARCHAR(30)) after Name.
  4. Make COURSE and BATCH not null.
  5. Delete the field BATCH.

Q4. Course Table Design (AcademyDB)

DATABASE: AcademyDB
TABLE: Courses

CourseID :INT PRIMARY KEY AUTO_INCREMENT
CourseName: VARCHAR(100) NOT NULL
Duration :INT
Fee :INT NOT NULL

  1. Insert 5 courses.
  2. Update fee of 'DTP' course to 4000.
  3. Display courses with duration more than 6 months.
  4. Delete course 'FA' if exists.
  5. Add column Instructor (VARCHAR(100)) after CourseName.

Q5. Marks Table Operations (ResultsDB)

Database Name: ResultsDB
Table Name: Marks

StudentID INT
Subject VARCHAR(50)
MarksObtained INT

  1. Insert at least 6 records for 3 different students.
  2. Display all marks for student ID 101.
  3. Update marks in "Maths" to 95 for student ID 102.
  4. Delete records for student ID 103.
  5. Add column Grade (VARCHAR(2)) at the end of the table.

Q6. Relationships Between Tables (CollegeDB)

Database Name: CollegeDB
Tables:
Students (StudentID INT, FullName VARCHAR(100))
Courses (CourseID INT, CourseName VARCHAR(100))
Enrollments (EnrollmentID INT, StudentID INT, CourseID INT, EnrollDate DATE)

Relationships:
- Students to Enrollments (1 to Many)
- Courses to Enrollments (1 to Many)

  1. Insert 3 students and 3 courses.
  2. Insert enrollment records mapping students to courses.
  3. Use JOIN to display Student Name, Course Name, and Enroll Date.
  4. Display students not enrolled in any course (LEFT JOIN).
  5. Delete an enrollment record by EnrollmentID.