Lab 1: Create a database name “College” and within it define a table name “students” with
the following attributes:
Student id
First name
Last name
Date of birth
GPA
Write the SQL statement to complete this task, including the appropriate data types for each
attribute.
1
Taaya Rai
BCA 4th Semester
Lab 2: Create a collage database, consisting student table with following information.
• Roll no(int)
• Name (First name and last name) varchar (50)
• Address varchar (50)
• Contact varchar (10)
1.Create a student table.
2.Insert any 5 records to the student table.
2
Taaya Rai
BCA 4th Semester
3.Display all records of student table.
4.Display only roll number and student’s first name and last name from student table.
3
Taaya Rai
BCA 4th Semester
5.Display only those address whose address is Pokhara.
6.Modify address of those students whose roll no 1 to KTM
4
Taaya Rai
BCA 4th Semester
7.Altering table by adding new column email varchar (50).
8.Deletes a row from table where student roll is equal to 5.
5
Taaya Rai
BCA 4th Semester
9.Drop a column contact from student table.
10.Delete those students whose roll number is less than 2.
11.Alter table by changing the data type of first name to varchar (20).
6
Taaya Rai
BCA 4th Semester
12.Delete all the data from the table student.
13.Delete entire table.
7
Taaya Rai
BCA 4th Semester
Lab 3: Create a Company database, and create two tables such that:
Department Table:
Department ID (int)
Department Name (varchar)
Employee Table:
Employee ID (int)
Employee Name (varchar)
Department ID (int)
Hire Date (Date)
Salary (decimal)
Now write queries for following operation:
1. Create both Table and while creating use NOT NULL, CHECK, UNIQUE, PRIMARY
KEY and FOREIGN KEY constraints wherever necessary.
8
Taaya Rai
BCA 4th Semester
2. Insert any 5 records to both tables.
9
Taaya Rai
BCA 4th Semester
3. Display all record of both tables
4.
D
isplay the minimum salary among the employees.
10
Taaya Rai
BCA 4th Semester
5. Display the total count of employees working.
6.
D
isplay the name and salary of all employees in decreasing order.
7. Display the name of all employees who work in ‘HR’ Department.
8. Apply inner join between Department Table and Employee Table.
11
Taaya Rai
BCA 4th Semester
9. Apply left outer join, right outer join and full outer join between Department Table and
Employee Table.
12
Taaya Rai
BCA 4th Semester
10. Create a view from Employee Table selecting Employee ID, Employee Name and Salary
and use the created view to display the records.
13
Taaya Rai
BCA 4th Semester
Lab 4: Create an Employee Database and create Record table such that:
Record Table:
Employee Id (int)
Employee Name (varchar)
Employee Department (varchar)
Address (varchar)
Salary (decimal)
Now write queries for following operations:
1. Create a Record Table
14
Taaya Rai
BCA 4th Semester
2. Insert any 5 records to the Record table.
3. Display all the records from Record table.
4. Display Id and Employee name of those employee whose Salary is more than 20,000 and
less than 80,000.
15
Taaya Rai
BCA 4th Semester
5. Display Id and Employee name of those employee whose Department is Sales and
Address is Kathmandu.
16
Taaya Rai
BCA 4th Semester
6. Display Id and Employee name of those employee whose name starts from letter ‘A’.
7. Display Id and Employee name of those employee whose name starts from letter ‘A’ and
ends from ‘N’.
17
Taaya Rai
BCA 4th Semester
Lab 5: Create a Student Database and create BCAForth Table such that:
BCAForth:
Student Id (int)
First name (varchar)
Last name (varchar)
Location (varchar)
GPA (Decimal)
Rank (int)
Now write queries for following operations:
1. Create BCAForth Table
2. Insert multiple record to BCAForth Table.
18
Taaya Rai
BCA 4th Semester
3. Display all the data from BCAForth Table.
4. Create a Stored Procedure that selects Student Id, first name and Last name and call it.
19
Taaya Rai
BCA 4th Semester
20
Taaya Rai
BCA 4th Semester
5. Alter the created Stored Procedure to select the Location also.
6. Create Stored Procedure that selects Student Id, First name and Last name, pass Student
Id as parameter.
21
Taaya Rai
BCA 4th Semester
7. Create a Trigger such that if the GPA is 4 then the rank is 1.
22
Taaya Rai
BCA 4th Semester
