select distinct deptname
from Dept d,(select deptno from proj
where prstdate< CURDATE())p
where d.deptno=p.deptno;
----------------------
Find the name, start date and duration in
months of projects that have the earliest end
date

select projname,prstdate,
TIMESTAMPDIFF(MONTH,prstdate,prenddate) AS duration
FROM Proj
Where prendate=(SELECT MIN(prendate) FROM Proj);
----------------------------------------------
Produce a list of all female employees whose education level is higher than the
average education level of all employees in their departments. Display employee
number, first name and last name

select empno,firstname,lastname
FROM Emp e1
where SEX= 'F' AND edlevel >
(SELECT AVG(edlevel) from EMP where workdept=e1.workdept);
---------------------------------------------------------
Find the lastname, job and salary of employees who do not work on any project

select lastname,job,salary
FROM EMP
where empno <> ALL(SELECT empno from Pworks);
---------------------------------------------------------
List all employees who have a higher education level than all designers. Display the
first name, last name and edlevel of the employee. Assume that designers are
indicated by job = ‘Designer’.

select fristname,lastname,edlevel
FROM Emp
Where edlevel > ALL (SELECT edlevel FROM emp where JOB='Designer');
---------------------------------------------------------
Get the names of departments that are currently responsible for only one project each.

select deptname
from Proj,Dept
where Proj.deptno=Dept.deptno
GROUP BY deptname
HAVING COUNT(deptname)=1;

select deptname
from Proj JOIN dept ON Proj.deptno=Dept.deptno
GROUP BY deptname
HAVING COUNT(deptname)=1;
-------------------------------------------
In the PROJ table, a row with a null value in the MAJPROJ column indicates that the
project represented by that row is not a sub-project of any other. Display the project
number, name and end date of all projects along with a remark of ‘Sub-project’ if the
MAJPROJ value is not null and ‘Not a sub-project’ otherwise. Order the result by
project end date
---------------------------------
(select projno,projname,prendate, 'Sub-Project' AS remark
from proj
where majproj is NOT NULL)
UNION
(select projno,projname,prendate, 'Not a sub-Project' AS remark
from proj
where majproj is  NULL)
Order by prendate;
The select_expression should select the appropriate table, and the results are stored in
Emp2, instead of being displayed and discarded.
The employee name in Emp2 should be in the format <lastname>,<first initial>. For
example, Eileen Henderson becomes Henderson,E. Note also that the table contains
the age of the employee, not the birthdate, so you will need to calculate that.

----------------------------------------------

CREATE TABLE Emp2
SELECT empno AS eid,
 CONCAT(lastname,',',substr(firstname,1,1)) AS Ename,
 TIMESTAMPDIFF(year,birthdate,CURDATE()) AS age,sex,salary FROM emp;
------------------------------------------------
ALTER TABLE Emp2 ADD PRIMARY KEY(eid);
-----------------------------------------------
Insert data into Dept2 from the Emp and Dept tables of Practical 2. Take the budget
of each department as 20% more than the total salary of all employees in the
department. Use an insert statement of the same form as above. Note the renaming of
some attributes.
--------------------------------------------------
INSERT INTO Dept2
SELECT deptno AS did,deptname AS dname, SUM(salary)*1.2 AS budget,mgrno AS managerid FROM Dept,EMP
WHERE deptno=workdept
GROUP BY did,dname,managerid;
------------------------------------------------
Use a Create table statement with a subquery to create the table Works2, assuming
that the percentage of time each employee works in his/her workdept is 100.
works table eid,did,pct_time
------------------------------------
CREATE TABLE Works2
  select empno AS eid, workdept AS did, 100 as pct_time
  FROM emp;
-----------------------------------------------------

Add an attribute called since of TIMESTAMP type to Works2, using an ALTER
TABLE statement. Give CURRENT_TIMESTAMP as the default value for this
attribute. Display the rows of the table to check the changes.
Hint: Look up the TIMESTAMPE type and default values.

ALTER TABLE works2 ADD since TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
-------------------------------------------------
Delete all managers of departments from the Works2 table


select * FROM works2
where eid IN (select managerid from dept2)
----------------------------------------------
Update the since column of Works2 with the hiredate of each employee from the
EMP table. In the update statement of the form: UPDATE tablename AS corr_var
SET columnname = expression, use a correlated subquery as the expression to assign
the corresponding hiredate of Emp table to each Works2 tuple.
-------------------------------------------

UPDATE works2 AS w
set since =(
select hiredate from emp
where w.eid=empno);

-----------------------------------------
ALTER TABLE WORKs2 ADD PRIMARY KEY (eid,did);