KEMBAR78
DBMS Assignment 2 | PDF
Scribd
Upload
44 views2 pages

DBMS Assignment 2

The document outlines a database schema for students and instructors, detailing operations such as adding primary and foreign keys, finding instructors for students, and creating views based on specific criteria. It also includes a second schema for departments and employees, with queries to retrieve employee information based on location, salary comparisons, and department statistics. The document emphasizes the use of various types of joins and subqueries in executing the specified queries.

Uploaded by

try.shreyas25
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
44 views2 pages

DBMS Assignment 2

The document outlines a database schema for students and instructors, detailing operations such as adding primary and foreign keys, finding instructors for students, and creating views based on specific criteria. It also includes a second schema for departments and employees, with queries to retrieve employee information based on location, salary comparisons, and department statistics. The document emphasizes the use of various types of joins and subqueries in executing the specified queries.

Uploaded by

try.shreyas25
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

I.

Consider the given database schema:


Student (studentid , studentname,instructorid,studentcity)
Instructor (instructorid,Instructorname,instructorcity,specialization)
Use all types of Joins and set opoeration
1. Add primary and foreign keys
2. Find the instructor of each student.
2. Find the student who is not having any instructor.
3. Find the student who is not having any instructor as well as instructor who is not having student.
4. Find the students whose instructor’s specialization is computer.
5. Create a view containing total number of students whose instructor belongs to “Pune”.

II. Consider following database. Execute each query given using join and subqueries.

CREATE TABLE departments (


department_id INT (11) AUTO_INCREMENT PRIMARY KEY,
department_name VARCHAR (30) NOT NULL,
location_id INT (11) DEFAULT NULL,
);

CREATE TABLE employees (


employee_id INT (11) AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR (20) DEFAULT NULL,
last_name VARCHAR (25) NOT NULL,
email VARCHAR (100) NOT NULL,
phone_number VARCHAR (20) DEFAULT NULL,
hire_date DATE NOT NULL,
job_id INT (11) NOT NULL,
salary DECIMAL (8, 2) NOT NULL,
manager_id INT (11) DEFAULT NULL,
department_id INT (11) DEFAULT NULL,
FOREIGN KEY (department_id) REFERENCES departments (department_id) ON
DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (manager_id) REFERENCES employees (employee_id)
);

1. Find all employees who locate in the location with the id 1700
2. Find all employees who do not locate at the location 1700
3. Finds the employees who have the highest salary
4. Finds all employees who salaries are greater than the average salary of all employees
5. Finds all departments which have at least one employee with the salary is greater than
10,000
6. Finds all departments that do not have any employee with the salary greater than 10,000
7. Finds all employees whose salaries are greater than the lowest salary of every
department
8. Finds all employees whose salaries are greater than or equal to the highest salary of
every department
9. Finds the salaries of all employees, their average salary, and the difference between the
salary of each employee and the average salary

You might also like