Identify the primary key in the table. Write query for the following:
ii. Find average salary in the table.
iii. Display number of records for each individual designation.
iv. Display number of records along with sum of salaries for each individual designation where number of records are more than 1.
v. What is the degree and cardinality of the relation Employee?
Answer (1)
To address the questions regarding the table provided, let's go through each step:
Identify the Primary Key in the Table:
A primary key is a unique identifier for each record in a database table. In the given table, the id column serves as the primary key since each entry is unique, thereby uniquely identifying each record.
Find Average Salary in the Table:
To find the average salary, we can use the SQL query:
SELECT AVG(Sal) AS AverageSalary FROM Employee;
This query calculates the average salary from the Sal column of the Employee table.
Display Number of Records for Each Individual Designation:
To count the records for each designation, the SQL query is:
SELECT Designation, COUNT(*) AS NumberOfRecords FROM Employee GROUP BY Designation;
This query groups records by the Designation field and counts the number of records in each group.
Display Number of Records along with Sum of Salaries for Each Individual Designation Where Number of Records are More Than 1:
The query to perform this task is:
SELECT Designation, COUNT( ) AS NumberOfRecords, SUM(Sal) AS TotalSalary FROM Employee GROUP BY Designation HAVING COUNT( ) > 1;
This SQL query calculates the number of records and total salary for each designation but only includes those designations that have more than one record.
What is the Degree and Cardinality of the Relation Employee?
Degree: Refers to the number of attributes (columns) in the table. The Employee table has four columns: id, Name, Designation, and Sal. Hence, the degree of this table is 4.
Cardinality: Refers to the number of rows (records) in the table. The Employee table has 6 records. So, the cardinality is 6.
Overall, these queries and definitions help in understanding the structure of the table and analyzing the data within it. These exercises are important for managing and querying databases effectively.
