DBMS & VISUAL BASICS
PRACTICAL FILE
SUBMITTED BY: AYUSH BHARDWAJ
ROLLNO: 21002570023
SECTION: B
DEPARTMENT OF OPERATIONAL RESEARCH
SUBMITTED TO: Dr. JAGVINDER SINGH
ASSISTANT PROFESSOR
DEPARTMENT OF OPERATIONAL RESEARCH
UNIVESITY OF DELHI
Ques 1: Create a table STUDENT and EXAM with the following fields:
STUDENT TABLE: {STUDENT_ID, Name, Gender, Age, Class (‘10th Grade’, ‘11th Grade’,
‘12th Grade’)}
EXAM: {STUDENT_ID, MATH_SCORE, SCIENCE_SCORE, ENGLISH_SCORE}
Perform the following queries in SQL:
i. Insert at least 10 records in the tables. (2)
ii. Display the details of male students who are in ‘12th Grade’. (1)
iii. Display the details of the student who secured the highest total score. (2)
iv. Add a new Column HISTORY_SCORE in EXAM table and modify the table by inserting
values to HISTORY_SCORE for the records. (1)
v. List Top 3 students of ‘11th Grade’ based on total score. (1)
vi. Display the Average Age of students in ‘10th Grade’.
vii. Display the list of students who scored more than the average marks in
MATH_SCORE
I)
ii)
CODE:
SELECT * FROM STUDENT
WHERE CLASS = '12TH GRADE'
ORDER BY 1;
iii)
CODE:
IV) Add a new Column HISTORY_SCORE in EXAM table and modify the table by inserting
values to HISTORY_SCORE for the records. (1)
CODE:
v) List Top 3 students of ‘11th Grade’ based on total score. (1)
CODE:
OUTPUT:
VI) Display the Average Age of students in ‘10th Grade’. (1)
CODE:
OUTPUT:
VII) Display the list of students who scored more than the average marks in
MATH_SCORE. (2)
CODE:
OUTPUT:
QUES 2) Create a table EMPLOYEES and DEPARTMENTS with the following fields:
EMPLOYEES: {EMPLOYEE_ID, NAME, DEPARTMENT_ID, SALARY, JOIN_DATE}
DEPARTMENTS: {DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID}
Perform the following queries in SQL:
i) Insert at least 5 records into the EMPLOYEES and DEPARTMENTS tables. (1
mark)
CODE:
ii) Display the names of all employees. (1 mark)
CODE:
iii) Display the department names and their corresponding manager IDs. (1 mark)
CODE:
iv) Find the employee with the highest salary. (1 mark)
CODE:
v) Display the details of employees who joined in the year 2024. (1 mark)
CODE:
vi) Ensure that the SALARY column in the EMPLOYEES table does not accept
negative values. (1 mark)
CODE:
vii) Calculate the total number of employees. (1 mark)
CODE:
viii) List the names of employees along with their department names. (2 marks)
CODE:
ix) Display the details of departments that have no employees. (1 mark)
CODE:
x) Update the salary of an employee with EMPLOYEE_ID 1 to 60000. (1 mark)
CODE:
QUES 3) 6. Create a table PATIENTS and APPOINTMENTS with the following fields:
PATIENTS: {PATIENT_ID, NAME, AGE, GENDER}
APPOINTMENTS: {APPOINTMENT_ID, PATIENT_ID, DOCTOR, APPOINTMENT_DATE,
FEES}
Perform the following queries in SQL:
I) Insert at least 5 records into the PATIENTS and APPOINTMENTS tables. (1
mark)
CODE:
II) Display the names of all patients. (1 mark)
CODE:
III) Display the details of appointments for a particular doctor (e.g., 'Dr. Smith'). (1
mark)
CODE:
IV) Find the patient with the highest number of appointments. (1 mark)
CODE:
V) Display the details of appointments that occurred in the last month. (1 mark)
CODE:
VI) Ensure that the FEES column in the APPOINTMENTS table does not accept
negative values. (1 mark)
Already ensured in table definition using CHECK (FEES >= 0).
VII) Calculate the total number of appointments. (1 mark)
CODE:
VIII) List the names of patients along with their appointment details. (2 marks)
CODE:
IX) Display the details of patients who have not had any appointments. (1 mark)
CODE:
X) Update the appointment date for a specific appointment (e.g.,
APPOINTMENT_ID 1) to a new date. (1 mark)
CODE:
Ques 4) Create a table PROFESSOR and PUBLICATION with the following fields:
PROFESSOR TABLE: {PROFESSOR_ID, NAME, DEPARTMENT, AGE, SALARY}
PUBLICATION TABLE: {PUBLICATION_ID, PROFESSOR_ID, TITLE, JOURNAL, YEAR}
Perform the following queries in SQL:
i. Insert at least 10 records into PROFESSOR and PUBLICATION tables. (2 marks)
ii. Display the details of professors from the 'OPERATIONS RESEARCH' department. (1
mark)
iii. Display the details of the professor with the highest number of publications. (2
marks)
iv. Add a new column EMAIL in the PROFESSOR table and update the table by inserting
values for EMAIL for the records. (1 mark)
v. List the top 3 journals with the highest number of publications. (1 mark)
vi. Display the average salary of professors in the 'MATHEMATICS' department. (1 mark)
viii. Display the list of professors who have published in more than 2 journals. (2
marks)
SELECT P.NAME, COUNT(DISTINCT PUB.JOURNAL) AS JOURNAL_COUNT
FROM PROFESSOR P
JOIN PUBLICATION PUB ON P.PROFESSOR_ID = PUB.PROFESSOR_ID
GROUP BY P.NAME
HAVING COUNT(DISTINCT PUB.JOURNAL) > 2;
Visual Basic Programming
Ques 1) CGPA and Percentage
CODE
OUTPUT:
Ques 2) Simple Calculator
CODE:
OUTPUT:
Ques 3) Simple and Compound Interest
CODE:
Output:
Ques 4) Economic Order Quantity
Code:
Output:
Ques 5) VB & MS-Access connectivity:
Private Sub Command1_Click()
Data1.Recordset.AddNew
End Sub
Private Sub Command2_Click()
Data1.Recordset.Update
End Sub
Private Sub Command3_Click()
Data1.Recordset.Delete
End Sub
Private Sub Command4_Click()
Data1.Recordset.MovePrevious
End Sub
Private Sub Command5_Click()
Data1.Recordset.MoveNext
End Sub
