SQL Queries for Employee and Student Data
Uploaded by
Aquatic ZoneSQL Queries for Employee and Student Data
Uploaded by
Aquatic ZoneCommon questions
Powered by AIA primary key is a unique identifier for a table, typically a single column like a student ID. A composite key, however, uses multiple columns to define uniqueness when a single column does not suffice as a unique identifier. In a student-course-registration context, (Student_Ref, Course_Ref, Semester) can serve as a composite key if Reg_ID is not available . This ensures each record is unique across the combination of these three attributes, particularly in tables lacking a singular unique field.
To find employees hired in the last two years, use the query: SELECT emp_id, emp_name, hire_date FROM Employee WHERE hire_date >= DATE_SUB(CURDATE(), INTERVAL 2 YEAR). This query uses the DATE_SUB function to subtract two years from the current date, thereby determining a date range to compare against the employees' hire dates.
To find employees with duplicate names and the number of occurrences, use: SELECT emp_name, COUNT(*) AS occurrences FROM Employee GROUP BY emp_name HAVING COUNT(*) > 1 . This query not only identifies duplicate employee names but also counts and displays how many times each duplicate occurs, providing both identification and frequency information in the dataset.
Maintaining unique student-course relations requires careful consideration of candidate keys. If each student-course instance is unique, a composite key like (name, course_id) can be suitable, provided there's no case for a student enrolling multiple times in the same course . However, uniqueness may often require a dedicated identifier such as course registration IDs. A composite key is ideal when no singular unique identifier suffices or is available to ensure data integrity effectively.
The query SELECT name, AVG(marks) FROM Student GROUP BY course_id will error because 'name' is not aggregated or grouped; SQL requires each selected field to either be included in the GROUP BY clause or wrapped in an aggregate function. To correct it, include 'name' in the GROUP BY clause if suitable or wrap the non-grouped field in an appropriate function . This illustrates the necessity of adhering to SQL's grouping rules to prevent execution errors.
To handle NULL department values in a department-wise employee query, use COALESCE to substitute 'No Department' for NULLs: SELECT emp_id, emp_name, COALESCE(dept_id, 'No Department') AS Department FROM Employee . COALESCE returns the first non-NULL value among its arguments, allowing alternative text representation for NULL department IDs.
Executing a DELETE statement followed by a ROLLBACK will have different results depending on the autocommit setting. If autocommit is ON, each statement is committed automatically, making the deletion permanent. If autocommit is OFF, the DELETE is only tentative until a COMMIT is issued, allowing ROLLBACK to restore the deleted data . This highlights the importance of understanding transaction management and autocommit settings in SQL operations.
In SQL, comparisons involving NULL require special handling because NULL represents an unknown value. Using SELECT NULL = NULL will return NULL because two unknowns cannot be determined as equal by standard comparison. However, SELECT NULL IS NULL correctly returns TRUE because the IS operator is used specifically for NULL checks, recognizing both sides as NULL . This highlights the importance of using IS NULL for accurate NULL value comparisons.
To display the third highest salary without using LIMIT or TOP, you can use a correlated subquery that counts distinct salaries greater than the current salary in the outer query. The query is: SELECT salary FROM Employee e1 WHERE 2 = ( SELECT COUNT(DISTINCT salary) FROM Employee e2 WHERE e2.salary > e1.salary ). This illustrates the SQL concept of correlated subqueries, where the inner query depends on the outer query for its evaluation.
To identify employees with non-unique names but different email addresses, use: SELECT name, COUNT(DISTINCT email) FROM Student GROUP BY name HAVING COUNT(DISTINCT email) > 1 . This query groups the data by name and counts distinct email addresses, displaying names where more than one unique email exists, highlighting the non-unique name condition with different emails.