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.
Employee:
ssn: social security number
dno: departmnt number of employee
Dependent:
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