DBMS2.sql

SELECT NAME, ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE EMPLOYEE.DNO=DEPARTMENT.DNO AND DNAME='ADMIN';

SELECT NAME FROM EMPLOYEE e, WORKS_ON w, DEPARTMENT d WHERE d.DNUMBER=5 AND e.DNO=d.DNO AND w.ESSN=e.SSN;

UPDATE EMPLOYEE SET SALARY=SALARY*1.1 WHERE DEPARTMENT.DNUMBER=EMPLOYEE.DNO AND DEPARTMENT.NAME='Research';

UPDATE EMPLOYEE SET SALARY=SALARY*1.1 WHERE DNO=(SELECT DNUMBER FRON DEPARTMENT WHERE DNAME='Research');


SELECT NAME FROM EMPLOYEE WHERE SSN NOT IN (SELECT ESSN FROM DEPENDENT);

SELECT NAME FROM EMPLOYEE WHERE NOT EXISTS (SELECT ESSN FROM DEPENDENT WHERE ESSN=EMPLOYEE.SSN);

EMPLOYEE

1 saif
2 rohan
3 sagar

DEPENDENT
1 bj
1 rohit
2 xyz


SELECT * FROM DEPENDENT WHERE ESSN=2


CREATE VIEW DEPT_SUMMATION AS 
SELECT DNO, COUNT(*), SUM(SALARY), AVG(SALARY) FROM EMPLOYEE WHERE AVG(SALARY)>10000 GROUP BY DNO;

SELECT dno, COUNT(*), SUM(sal), AVG(sal) FROM emp WHERE AVG(sal)>10000 GROUP BY dno;

SELECT dno, count(*), sum(sal), avg(sal) FROM emp GROUP BY dno HAVING avg(sal)>10000;

SELECT name, price, year FROM AUTHOR a, CATALOG c WHERE EXISTS(SELECT * FROM CATALOG GROUP BY author_id HAVING COUNT(*)>=2) AND year>2015;

SELECT author_id, SUM(price) as x FROM CATALOG GROUP BY author_id DESC LIMIT 1;


SELECT aid, SUM(price) AS x FROM emp x GROUP BY aid ORDER BY DESC LIMIT 1;


ALTER TABLE stud ADD CONSTRAINT fk FOREIGN KEY(d_num) REFERENCES dept(d_id);

NOT NULL
CHECK
FOREIGN KEY
PRIMARY KEY
UNIQUE

SELECT member_id FROM Reserves WHERE bid IN (SELECT Bid FROM Books WHERE Bprice <= 500);

SELECT member_id FROM Reserves, Books WHERE Books.bid = reserves.bid GROUP BY member_id HAVING SUM(Bprice) > 500;