Practice Lab Activity: Solve using SQL Queries: 1. Create a college database. 2. Create a student...

  

 

Transcribed Image Text:

Practice Lab Activity: Solve using SQL Queries: 1. Create a college database. 2. Create a student table with the following fields using SQ1 Query. StudentID, StudentName, Gender, Marks, CourseID. 3. Create a course table with the following fields using SQL Query. Courseld, CourseName, Specialization. 4. Insert the following values into the student table using SQL Query StudentID: S001, S002, S003, S004 StudentName; Ahmed, Hamed, Fatima, Soha Gender: M,M,F,F Marks: 65,83,80,63 CourseID: C101. C102, C102, C102 5. Insert the following values into the course table using SQL query CourseID: C101, C102, C103 CourseName: Ethics, Auditing, CRM Specialization: Common, Accounting, Marketing. 6. Add a new field coursetime to the course table. 7. Write a query to change the marks of student Ahmed to 68. 8. Write a query to remove duplicate values of course ID from the student table. 9. Write a query to display all details of the student table. 10. Write a query to display all details of female students whose marks are less than and equal to 65. 11. Write a query to count the number of students as 'scount' whose marks are greater than or equal to 80. 12. Write a query to display the maximum mark as 'maxmark' and miminum mark as 'minmark' from the student table. 13. Write a query to change the specialization of CRM course to common. 14. Write a query to display all details of course whose specialization begins with 'c'. 15. Write a query to display all the student details arranged in ascending order of marks. Solve using QBE: 1. List all details of the student table. 2. Count the number of courses from the course table. 3. Eliminate duplicate values from the course ID column of the student table. 4. List all the female students whose marks are less than 75. 5. Find the sum and average of marks from the student table. 6. Find the minimum mark and the maximum mark of the students. 7. List all student details who are taking ethics course. 8. List all student details whose marks are between 60 and 80. 9. List all student details who are in accounts specialization. Forms and Reports: 1. Create a form for the student table using form wizard and add the following record. i. ii. iii. StudentID: S006 2. Create a form using form wizard to display studentId, Name, Gender, Marks, CourseID StudentName; Ahmed Gender: M marks. Marks: 70 and Course Name. CourseID: C103 marks. Use tabular layout. Set the data view 'by course' Save as QBE form 3. Create a report using report wizard for the student table arranged course wise. 4. Create a report using report wizard for the student table arranged in decreasing order of 5. Create a report using report wizard to display total number of students and sum of their 6. Create a query using QBE to display the studentId, student name, gender, marks from the student table, course ID and course name from the course table for the students taking ethics course. Save as reportquery. Create a course wise report for the above query and give title as 'coursereport'. Practice Lab Activity: Solve using SQL Queries: 1. Create a college database. 2. Create a student table with the following fields using SQ1 Query. StudentID, StudentName, Gender, Marks, CourseID. 3. Create a course table with the following fields using SQL Query. Courseld, CourseName, Specialization. 4. Insert the following values into the student table using SQL Query StudentID: S001, S002, S003, S004 StudentName; Ahmed, Hamed, Fatima, Soha Gender: M,M,F,F Marks: 65,83,80,63 CourseID: C101. C102, C102, C102 5. Insert the following values into the course table using SQL query CourseID: C101, C102, C103 CourseName: Ethics, Auditing, CRM Specialization: Common, Accounting, Marketing. 6. Add a new field coursetime to the course table. 7. Write a query to change the marks of student Ahmed to 68. 8. Write a query to remove duplicate values of course ID from the student table. 9. Write a query to display all details of the student table. 10. Write a query to display all details of female students whose marks are less than and equal to 65. 11. Write a query to count the number of students as 'scount' whose marks are greater than or equal to 80. 12. Write a query to display the maximum mark as 'maxmark' and miminum mark as 'minmark' from the student table. 13. Write a query to change the specialization of CRM course to common. 14. Write a query to display all details of course whose specialization begins with 'c'. 15. Write a query to display all the student details arranged in ascending order of marks. Solve using QBE: 1. List all details of the student table. 2. Count the number of courses from the course table. 3. Eliminate duplicate values from the course ID column of the student table. 4. List all the female students whose marks are less than 75. 5. Find the sum and average of marks from the student table. 6. Find the minimum mark and the maximum mark of the students. 7. List all student details who are taking ethics course. 8. List all student details whose marks are between 60 and 80. 9. List all student details who are in accounts specialization. Forms and Reports: 1. Create a form for the student table using form wizard and add the following record. i. ii. iii. StudentID: S006 2. Create a form using form wizard to display studentId, Name, Gender, Marks, CourseID StudentName; Ahmed Gender: M marks. Marks: 70 and Course Name. CourseID: C103 marks. Use tabular layout. Set the data view 'by course' Save as QBE form 3. Create a report using report wizard for the student table arranged course wise. 4. Create a report using report wizard for the student table arranged in decreasing order of 5. Create a report using report wizard to display total number of students and sum of their 6. Create a query using QBE to display the studentId, student name, gender, marks from the student table, course ID and course name from the course table for the students taking ethics course. Save as reportquery. Create a course wise report for the above query and give title as 'coursereport'.

Answer rating: 100% (QA)

1 CREATE DATABASE databasename 2 CREATE TABLE student StudentID int StudentName varchar255 Gender va... View the full answer