LAB ASSIGNMENT
Create database and tables as mentioned in questions below in mysql.
Screenshot and print the entire task and the query should be written below
the task with BLUE INKED pen. (screen shot is not require for question
1a,1b,2a, 2b,3a and 3b)
1. Consider the relational schema and write down the relational algebra for the following
Students(Student_ID, Student_Name, Class, Major)
Course( CourseNo, CourseName, Cr_Hr, Dept)
Section(Sect_ID, CourseNo, Semester, Year, Instructor_Name)
GradeReport(Student_ID, Sect_ID, Grade)
Prequisite( CourseNo, Prequisite_No)
a) Create a database named “ labworkquestion1” and create tables mentioned above.
b) Insert 5 rows each in every table.
c)Modify the database so that credit hour of course no 5 is changed to 4
d) Display name and major of all student whose grade is “A”
e) Delete all tuples in the section relation whose instructor is Mr. Bean.
f) Display the number of courses taught by Mr. Bill.
g)Find the number of students in each class.
h) Delete tuples of student table whose course no is 15.
i) Display the name of the student of 4th semester in computer department.
j) Modify the grade of student Raju from ‘B’ to ‘A’.
k)Insert 2 new students in student table.
2. Consider the following relational schema of library where primary keys are underlined.
employee (person_name, street, city)
works (person_name, company-name, salary)
company (company_name, city)
manages (person_name, manager_name)
Write down the SQL for the following
a) Create a database named “ labworkquestion2” and create tables mentioned above.
b) Insert 5 rows each in every table.
c) Display employees name and city whose name contains “Ram”
d) Find all employees in order of city in the database who works for Janta Bank Corporation.
e) Find the names, street address, and cities of residence of all employees who work for NMB
Bank and earn more than Rs.10,000.
f) Display the name of all employees whose salary is more than 25000 and is managed by “Mr
Bill”
g) Find out the name of employee who works in company that is located in “Kathmandu” and
whose name does not contain alphabet “b”.
h) Find names and cities of employee who works for the “CISCO Company”.
i) Find name of the manager of Microsoft Company.
j) Delete record of employees whose salary is less than 10000”.
k) Increase salary of employees by 5% on employee relation whose salary is less than 5000 and
by 10% whose salary is 5000-10000 and by 15% whose salary is more that 15000.
l) Find minimum salary of the employee of each company.
3. Write SQL queries for the following based on the schema given
Employee (empid, gender, name, dob, salary, address, dept_no, start_date)
Department (deptno, name)
Students (std_id,gender, name, dob, projectno,deptno)
Project (projectno, name, location)
a) Create a database named “ labworkquestion3” and create tables mentioned above.
b) Insert 5 rows each in every table.
c) Display the name of employees working in ‘BBA’ Department.
d) List details of employees earning salary above average salary
e) List details of all students along with the name of project they are involved in. Also display
the name of the department.
f) Decrease salary by 10% of all employees whose salary is more than 10,000
