There is a table employee with the following columns
fname : employee first name
minit : employee middle initial
lname : Employee last name
ssn : social security number
bdate : date of birth of the employee
address : address of the employee
sex : gender of the employee
salary : annual salary of the employee
super_ssn : social security number of the supervisor of the employee
dno : department number of the employee
Write a query to find the social security numbers of all employees who are either male or have salary less than 30000. Order the results on the basis of social security number in ascending order. Please note that the gender is denoted by either F or M.
where sex='M' or salary<30000 order by ssn asc
from dependent d, employee e
where d.essn=e.ssn and e.dno=5 order by d.dependent_name
Consider two tables employee and dependent containing the columns as given below.
ssn: social security number
dno: departmnt number of employee
essn: ssn for employee
dependent_name: dependent name
perform a query to determine the name of all dependents of the employees of department number 10? Order the results by the name of dependents.
from dependent d, employee e
where d.essn=e.ssn and e.dno=10 order by d.dependent_name
Method 2 Using join operation
from dependent d join employee e
on d.essn = e.ssn
where e.dno = 10
order by d.dependent_name ;
employee having the following columns
employee_id : unique id of the employee
employee_name :name of the employee
designation : designation of the employee
joining_date : date the employee joined the organisation in YYYY-MM-DD format
Write a query to print the names of the employees in the order they joined the organisation. In case two people have joined the organisation in the same year, the person higher in the organisational hierarchy should appear first.
CEO - First level
Department Head - Second level
Regional Manager - Third level
alter table employee
add desv int
set desv = 2
where designation = 'Department Head' ;
set desv = 3
where designation = 'Regional Manager' ;
order by extract(year from joining_date), desv;
There is a table named salary containing the details of salaries of employees in an organisation along with their department names.
emp_id : Storing the id of the employee
dep_name : Storing the name of the department
salary : Storing the salary of the employee
Find the name of the department having the maximum average salary.
where avg(salary)=(select avg(salary) from salary order by desc limit 1)
order by avg(salary) desc
Suppose you have a table marks following columns
Student_id : id of the student
Course : name of the course
Marks : marks obtained by the student in a particular course
Write a query to determine the average marks obtained by students. Order the results in the descending order of average marks. In case the average marks are same for two students, student with a lower student_id should appear first.
select student_id, avg(marks)
group by student_id
order by avg(marks) desc, student_id;
Write a query to determine the spread of the marks of the student having average marks greater than the overall average. Alias the column as Spread. Spread is defined as the difference between the highest and lowest marks obtained by the student. Order the output in order of student id.
select student_id, (max(marks)-min(marks) ) as Spread
from student group by student_id order by student_id
select Student_id , (Max(marks) - min(marks)) as Spread
group by Student_id
having avg(marks) > (select avg(marks) from marks)
order by Student_id