[Solved] DBMS Lab Assignment # 1

Objective: To learn SQL for creating and retrieving information from databases

1. Create a database for University placement system with following relations:

Student(sroll, sname, gender, branch, programme, CGPA)

Company(company_id, name, specification)

Interview(sroll, company_id, idate)

Offer(sroll, company_id, osalary)

Create primary keys and foreign keys in each relation. Insert suitable records in all relations. A student can receive at most 3 offers.

Write SQL Queries for the following(s):

1. List all students name and their branches who have received at least one offer.
2. List all students name who have received offer with salary >100k.
3. List the students who has appeared in at least one interview but didnt receive any offer.
4. List the name of MTech students who have received at least one offer.
5. List the name of girl students who have received at least one offer.
6. List branch-wise number of offers received.
7. List the name of the students who have received maximum offered salary.
8. List the branchwise name of students who have received maximum offered salary.
9. List the name of the branch which has received maximum number of offers.
10. List the number of students in each branch who have not received offer.
11. List the student wise offer details with following fields <Student Name, Gender, Branch, Company Name, Specification, Osalary>

2. Design a university database with following details. Insert few more records in the database with the given relational schema. The Major and Department attributes can take values CS, ECE, EE, MATH. The Grade can take values A, B, C, D, P, F. Create primary keys and foreign keys for different relations as per the content of the database.

Write SQL queries for the following(s):

1. Create transcript for each student with <student name, course number, semester, year, section_id, grade> ii. Display name of the students who has received A grade in all the courses registered.

• List the number of students registered in each course.

1. List the name of students who have registered for all the courses instructed by Anderson.
2. Display the student wise total_credit taken in a given year. Year should be provided as input by user.
3. List the name of the students who has registered for at least one MATH course and 2 CS courses.

• Delete records for the course MATH2410 from course relation. The referenced records in section, grade_report and prerequisite relations must be automatically deleted.

vii. List the name of the students who has received more than one F in a given semester. Semester would be given as input. viii. Update Grade attribute values in Grade_Report relation with following rules: A 10; B 9; C 8; D 7; P 6; F 5