SkillsDrome: All labs

Lab categories:

5. For each department that has more than five employees, retrieve the department number and the number of its employees who are making more than Rs. 6,00,000.

4. Retrieve the name of each employee who works on all the projects controlledby department number 5 (use NOT EXISTS operator).

3. Find the sum of the salaries of all employees of the ‘Accounts’ department, as well as the maximum salary, the minimum salary, and the average salary in this department

2. Show the resulting salaries if every employee working on the ‘IoT’ project is given a 10 percent raise.

1. Make a list of all project numbers for projects that involve an employee whose last name is ‘Scott’, either as a worker or as a manager of the department that controls the project.

5 Consider the schema for Company Database: EMPLOYEE(SSN, Name, Address, Sex, Salary, SuperSSN, DNo) DEPARTMENT(DNo, DName, MgrSSN, MgrStartDate) DLOCATION(DNo,DLoc) PROJECT(PNo, PName, PLocation, DNo) WORKS_ON(SSN, PNo, Hours) Write SQL queries to

5. Categorize students based on the following criterion: If FinalIA = 17 to 20 then CAT = ‘Outstanding’ If FinalIA = 12 to 16 then CAT = ‘Average’ If FinalIA< 12 then CAT = ‘Weak’ Give these details only for 8th semester A, B, and C section students.

4. Calculate the FinalIA (average of best two test marks) and update the corresponding table for all students.

3. Create a view of Test1 marks of student USN ‘1BI17CS101’ in all subjects.

2. Compute the total number of male and female students in each semester and in each section.

1. List all the student details studying in fourth semester ‘C’ section.

4 Consider the schema for College Database: STUDENT(USN, SName, Address, Phone, Gender) SEMSEC(SSID, Sem, Sec) CLASS(USN, SSID) SUBJECT(Subcode, Title, Sem, Credits) IAMARKS(USN, Subcode, SSID, Test1, Test2, Test3, FinalIA) Write SQL queries to

5. Update rating of all movies directed by ‘Steven Spielberg’ to 5.

4. Find the title of movies and number of stars for each movie that has at least one rating and find the highest number of stars that movie received. Sort the result by movie title.

3. List all actors who acted in a movie before 2000 and also in a movie after 2015 (use JOIN operation).

2. Find the movie names where one or more actors acted in two or more movies.

1. List the titles of all movies directed by ‘Hitchcock’.

3 Consider the schema for Movie Database: ACTOR(Act_id, Act_Name, Act_Gender) DIRECTOR(Dir_id, Dir_Name, Dir_Phone) MOVIES(Mov_id, Mov_Title, Mov_Year, Mov_Lang, Dir_id) MOVIE_CAST(Act_id, Mov_id, Role) RATING(Mov_id, Rev_Stars) Write SQL queries to

5. Demonstrate the DELETE operation by removing salesman with id 1000. All his orders must also be deleted.

4. Create a view that finds the salesman who has the customer with the highest order of a day.