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.

select ssn

from employee

where sex='M' or salary<30000 order by ssn asc

select d.dependent_name

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.

Method 1:

select d.dependent_name

from dependent d, employee e

where d.essn=e.ssn and e.dno=10 order by d.dependent_name

Method 2 Using join operation

select d.dependent_name

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

default 1;

update employee

set desv = 2

where designation = 'Department Head' ;

update employee

set desv = 3

where designation = 'Regional Manager' ;

select employee_name

from employee

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.

select dept_name

from salary

where avg(salary)=(select avg(salary) from salary order by desc limit 1)

select dep_name

from salary

group by(dep_name)

order by avg(salary) desc

limit 1;

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)

from 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

from marks

group by Student_id

having avg(marks) > (select avg(marks) from marks)

order by Student_id