Sure, here are the SQL statements for the given queries:

1) Create tables with Primary Key, foreign key constraints in given schemas.

```sql
CREATE TABLE Faculty (
f_id INT PRIMARY KEY,
f_name VARCHAR(100),
address VARCHAR(100),
city VARCHAR(50),
state VARCHAR(50),
age INT,
salary DECIMAL(10,2),
d_id INT
);

CREATE TABLE Department (
dep_id INT PRIMARY KEY,
d_name VARCHAR(100),
course VARCHAR(100),
location VARCHAR(100),
sub_code VARCHAR(50),
subject VARCHAR(100)
);

CREATE TABLE Student (
enrl_no INT PRIMARY KEY,
s_name VARCHAR(100),
s_address VARCHAR(100),
s_city VARCHAR(50),
s_state VARCHAR(50),
s_age INT,
course VARCHAR(100),
f_id INT,
hobby VARCHAR(100),
FOREIGN KEY (f_id) REFERENCES Faculty(f_id)
);
```

2) Display name of faculties and students who are not living in the same city.

```sql
SELECT f_name, s_name
FROM Faculty, Student
WHERE Faculty.city != Student.s_city;
```

3) Find out how much amount is spent on faculties as their salary who are working in 'IT Engineering' Department.

```sql
SELECT SUM(salary)
FROM Faculty
WHERE d_id IN (SELECT dep_id FROM Department WHERE d_name = 'IT Engineering');
```

4) Display department name, course, subject code and subject of person who have enrolled as a student.

```sql
SELECT d_name, course, sub_code, subject
FROM Department
WHERE dep_id IN (SELECT d_id FROM Faculty WHERE f_id IN (SELECT f_id FROM Student));
```

5) Display id and name of faculty who are working in 'IT Engineering' department and have a salary more than Rs. 60000

```sql
SELECT f_id, f_name
FROM Faculty
WHERE salary 60000 AND d_id IN (SELECT dep_id FROM Department WHERE d_name = 'IT Engineering');
```

Question

Sure, here are the SQL statements for the given queries:

1) Create tables with Primary Key, foreign key constraints in given schemas.

```sql
CREATE TABLE Faculty (
    f_id INT PRIMARY KEY,
    f_name VARCHAR(100),
    address VARCHAR(100),
    city VARCHAR(50),
    state VARCHAR(50),
    age INT,
    salary DECIMAL(10,2),
    d_id INT
);

CREATE TABLE Department (
    dep_id INT PRIMARY KEY,
    d_name VARCHAR(100),
    course VARCHAR(100),
    location VARCHAR(100),
    sub_code VARCHAR(50),
    subject VARCHAR(100)
);

CREATE TABLE Student (
    enrl_no INT PRIMARY KEY,
    s_name VARCHAR(100),
    s_address VARCHAR(100),
    s_city VARCHAR(50),
    s_state VARCHAR(50),
    s_age INT,
    course VARCHAR(100),
    f_id INT,
    hobby VARCHAR(100),
    FOREIGN KEY (f_id) REFERENCES Faculty(f_id)
);
```

2) Display name of faculties and students who are not living in the same city.

```sql
SELECT f_name, s_name 
FROM Faculty, Student 
WHERE Faculty.city != Student.s_city;
```

3) Find out how much amount is spent on faculties as their salary who are working in 'IT Engineering' Department.

```sql
SELECT SUM(salary) 
FROM Faculty 
WHERE d_id IN (SELECT dep_id FROM Department WHERE d_name = 'IT Engineering');
```

4) Display department name, course, subject code and subject of person who have enrolled as a student.

```sql
SELECT d_name, course, sub_code, subject 
FROM Department 
WHERE dep_id IN (SELECT d_id FROM Faculty WHERE f_id IN (SELECT f_id FROM Student));
```

5) Display id and name of faculty who are working in 'IT Engineering' department and have a salary more than Rs. 60000

```sql
SELECT f_id, f_name 
FROM Faculty 
WHERE salary > 60000 AND d_id IN (SELECT dep_id FROM Department WHERE d_name = 'IT Engineering');
```

Knowee AI · Accepted Answer