Informatics Practices
Consider the following tables:
Table 1:
EMPLOYEE which stores Employee ID (EMP_ID), Employee Name (EMP_NAME), Employee City (EMP_CITY)
Table 2:
PAYROLL which stores Employee ID (EMP_ID), Department (DEPARTMENT), Designation (DESIGNATION), and Salary (SALARY) for various employees.
Note: Attribute names are written within brackets.
Table: EMPLOYEE
| EMP_ID | EMP_NAME | EMP_CITY |
|---|---|---|
| 1 | ABHINAV | AGRA |
| 2 | KABIR | FARIDABAD |
| 3 | ESHA | NOIDA |
| 4 | PAUL | SEOUL |
| 5 | VICTORIA | LONDON |
Table: PAYROLL
| EMP_ID | DEPARTMENT | DESIGNATION | SALARY |
|---|---|---|---|
| 1 | SALES | MANAGER | 75000 |
| 2 | SALES | ASSOCIATE | 50000 |
| 3 | ENGINEERING | MANAGER | 95000 |
| 4 | ENGINEERING | ENGINEER | 70000 |
| 5 | MARKETING | MANAGER | 65000 |
Write appropriate SQL queries for the following:
I. Display department-wise average Salary.
II. List all designations in the decreasing order of Salary.
III. Display employee name along with their corresponding departments.
SQL Joins & Grouping
1 Like
Answer
I.
SELECT DEPARTMENT, AVG(SALARY)
FROM PAYROLL
GROUP BY DEPARTMENT;
II.
SELECT DESIGNATION
FROM PAYROLL
ORDER BY SALARY DESC;
III.
SELECT EMP_NAME, DEPARTMENT
FROM EMPLOYEE E, PAYROLL P
WHERE E.EMP_ID = P.EMP_ID;
Answered By
2 Likes
Related Questions
Write a Python Program to create a Pandas Series as shown below using a dictionary. Note that the left column indicates the indices and the right column displays the data.
Russia Moscow Hungary Budapest Switzerland Bern I. Write an SQL statement to create a table named STUDENTS, with the following specifications:
Column Name Data Type Key StudentID Numeric Primary Key FirstName Varchar(20) LastName Varchar(10) DateOfBirth Date Percentage Float(10, 2) II. Write SQL Query to insert the following data in the Students Table
1, Supriya, Singh, 2010-08-18, 75.5
Consider the following tables:
Table 1:
ATHLETE, which stores AthleteID, Name, Country. The table displays basic information of the athletes.
Table 2:
MEDALS, which stores AthleteID, Sport, and Medals. The table displays the number of medals won by each athlete in their respective sports.
Table: ATHLETE
AthleteID Name COUNTRY 101 Arjun INDIA 102 Priya INDIA 103 Asif UAE 104 Rozy USA 105 David DENMARK Table: MEDALS
AthleteID Sport Medals 101 Swimming 8 102 Track 3 103 Gymnastics 5 104 Swimming 2 105 Track 6 Write appropriate SQL queries for the following:
I. Display the sports-wise total number of medals won.
II. Display the names of all the Indian athletes in uppercase.
III. Display the athlete name along with their corresponding sports
During a practical exam, a student Ankita has to fill in the blanks in a Python program that generates a bar chart. This bar chart represents the number of books read by four students in one month.
Student Name Books Read Karan 12 Lina 9 Raj 5 Simran 3 Help Ankita to complete the code.
import _____ as plt #Statement-1 students = ['Karan', 'Lina', 'Raj', 'Simran'] books_read = [12, 9, 5, 3] plt.bar( students, _____, label='Books Read') #Statement-2 plt.xlabel('Student Name') plt._____('Books Read') #Statement-3 plt.legend() plt.title('_____') #Statement-4 plt.show()I. Write the suitable code for the import statement in the blank space in the line marked as Statement-1.
II. Refer to the graph shown above and fill in the blank in Statement-2 with suitable Python code.
III. Fill in the blank in Statement-3 with the name of the function to set the label on the y-axis.
IV. Refer the graph shown above and fill the blank in Statement-4 with suitable Chart Title.