DBMS Lec 24 : Relational Algebra Practice Questions with answers | Dinesh Bhawnani | BIT DURG

#pyq #csvtu Consider following schemas and solve the following queries in relational algebra : emp (ename, street, city) works (ename, company_name, salary) company (company_name, city) 1. Display name of all employees who works for the company “Satyam”. 2. Display name, street, and city of employees those who working for company “Satyam”. 3. Find the name of person who live in the same city and same street as that of “Mr. Rahul”. 4. Find name of all employees in this database who lives in the same cities as the company for which they work. 5. Find all companies located in every city in which “Infosys” is located. (Infosys is a company). Given the following relational schemas : STUDENT (sid, sname, department, city) TEACHER (tid, tname, department, city, salary) TEACHES (sid, tid) Write relational algebra expression for the following queries : 1. Find all students who live in the same city as their teacher who teaches them. 2. Find all teacher names from department of Computer Sciences, giving them 10% increment on their salary amount. 3. Find total number of students from each department. 4. Find the average salary of each department. 5. Delete all the students who have come from Kanpur city. Write SQL query for each of the following : 1. Create table named as Std-Rec with columns – Rollno, Name, ContactNo, Dept. 2. Add one column (Address) to table Std-Rec. 3. Add primary key constraint to table Std-Rec. 4. Insert the following records into the table : Shankar 1234567 Religion Madurai Vivek 8910111 Mythodology Kolkata Arvind 2131415 Science Kolkata Given a schema Worker (name, address, id, total_sal, gross_sal), Find SQL queries for : 1. Find name of workers whose address contains character ‘s’ in their names third position. 2. Find average gross_sal of the workers from the above schema who belong to address ‘DELHI’. 3. Count all the records from the given schema. 4. Display different addresses and ids from the schema worker where name is in descending order. 5. Change the value of ids to another value whose address belong to ‘BILASPUR’. 6. Find the highest gross_sal of workers whose name is ‘Sunil’. 7. Find the names of workers whose address starts with character ‘S’ also id is 101.