Scribd
Upload
4 views3 pages

SQL Practical

The document outlines a practical SQL assignment for M.Sc. Part II students at the University of Mumbai, requiring them to create and manipulate a database named CollegeDB. It includes tasks such as creating tables for Students, Courses, and Employees, performing various SQL operations like adding and modifying columns, inserting records, and writing queries for data retrieval and calculations. The assignment emphasizes practical skills in SQL language and database management.

Uploaded by

rangipayal4
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views3 pages

SQL Practical

The document outlines a practical SQL assignment for M.Sc. Part II students at the University of Mumbai, requiring them to create and manipulate a database named CollegeDB. It includes tasks such as creating tables for Students, Courses, and Employees, performing various SQL operations like adding and modifying columns, inserting records, and writing queries for data retrieval and calculations. The assignment emphasizes practical skills in SQL language and database management.

Uploaded by

rangipayal4
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Department of Statistics

University of Mumbai, Mumbai


[Link]. Part II (Semester IV)
Practical on SQL Language
[Link]: SK/I/IV Roll No:
Date:

1.​ Create a database named CollegeDB.


2.​ Create a table named Students with the following columns:
●​ StudentID (Integer, Primary Key)
●​ Name (VARCHAR 50)
●​ Age (Integer)
●​ Course (VARCHAR 50)
●​ AdmissionDate (DATE)
3.​ Perform the following operations:
●​ Add a new column Email (VARCHAR 100) to the Students
table.
●​ Modify the column Name to increase its size to
VARCHAR(100).
●​ Delete the column Age from the table.
4.​ Insert the following records into the Students table:
●​ (1, 'Rahul Sharma', 'BCA', '2023-07-01', 'rahul@[Link]'),
●​ (2, 'Anita Verma', 'BBA', '2023-07-02', 'anita@[Link]'),
●​ (3, 'Karan Mehta', 'BSc', '2023-07-03', 'karan@[Link]')
5.​ Write SQL queries for the following:
●​ Update the course of student with StudentID = 2 to MBA.
●​ Update the Email of all students whose name starts with K to
updated_k@[Link].
●​ Increase AdmissionDate of all students by 1 day.
6.​ Create another table named Courses with:
●​ CourseID (Integer, Primary Key)
●​ CourseName (VARCHAR 50)
●​ Duration (Integer)
7.​ Insert at least 3 records into the Courses table.
●​ 101, BCA, 3
●​ 102, BBA, 3
●​ 103, MBA, 2
8.​ Add a new column Fees (Decimal(10,2)) to the Courses table.
9.​ Update the Fees of all courses to 50000 where Duration is greater
than 2 years.
10.​ Create a table Employees with:
●​ EmpID (Integer, Primary Key)
●​ Name (VARCHAR 50)
●​ Department (VARCHAR 50)
●​ Salary (Integer)
●​ Age(Integer)
11.​ Insert values

EmpID Name Department Salary Age

1 Amit HR 30000 25

2 Neha IT 50000 28

3 Rahul IT 55000 30

4 Sneha Finance 40000 27

5 Karan HR 32000 26

12.​ Write SQL Queries to answer the following questions:


●​ Find total salary of all employees and give column name as
Total Salary.
●​ Find average salary and give column name as Average
Salary.
●​ Find maximum and minimum salary and give names as
Highest Salary and Lowest Salary.
●​ Count Employees and give column name as Total Employees.
●​ Show employees with salary greater than 40000
●​ Show employees from IT department
●​ Show employees with salary between 30000 and 50000
●​ Show employees whose age is 25 OR 30
●​ Find total salary of IT employees and give name as Total IT
Salary.
●​ Departments with more than 1 employee
●​ Max Salary per Department and give column name as
Maximum Salary.

You might also like