Advanced Database Topics - Queries for assignment 1.

employee_db_queries.sql

-- Professor: Juan Sebastian Toquica
-- Student: Nádio Dib (201918579)
-- Gist: https://gist.github.com/Devwarlt/8f52d04496efb841ad183129eda3b838

SELECT 
    `Fname`, `Lname`
FROM
    `employee`
WHERE
    `Super_ssn` IS NULL;

-- Query 2:
SELECT 
    `Fname`, `Lname`
FROM
    `employee`
WHERE
    `Super_ssn` IS NOT NULL;
    
-- Query 3:
SELECT DISTINCT
    `Pnumber`
FROM
    `project`
WHERE
    `Pnumber` IN (SELECT 
            `Pnumber`
        FROM
            `project`,
            `department`,
            `employee`
        WHERE
            `Dnum` = `Dnumber` AND `Mgr_ssn`
                AND `Lname` = 'Smith')
        OR `Pnumber` IN (SELECT 
            `Pno`
        FROM
            `works_on`,
            `employee`
        WHERE
            `Essn` = `Ssn` AND `Lname` = 'Smith');
            
-- Query 4:
SELECT DISTINCT
    `Essn`
FROM
    `works_on`
WHERE
    (`Pno` , `Hours`) IN (SELECT 
            `Pno`, `Hours`
        FROM
            `works_on`
        WHERE
            `Essn` = '123456789');
            
-- Query 5:
SELECT 
    `Lname`, `Fname`
FROM
    `employee`
WHERE
    `Salary` > ALL (SELECT 
            `Salary`
        FROM
            `employee`
        WHERE
            `Dno` = 5);
            
-- Query 6:
SELECT 
    `e`.`Fname`, `e`.`Lname`
FROM
    `employee` AS `e`
WHERE
    `e`.`Ssn` IN (SELECT 
            `d`.`Essn`
        FROM
            `dependent` AS `d`
        WHERE
            `e`.`Fname` = `d`.`Dependent_name`
                AND `e`.`Sex` = `d`.`Sex`);
            
-- Query 7:
SELECT 
    `e`.`Fname`, `e`.`Lname`
FROM
    `employee` AS `e`,
    `dependent` AS `d`
WHERE
    `e`.`Ssn` = `d`.`Essn`
        AND `e`.`Sex` = `d`.`Sex`
        AND `e`.`Fname` = `d`.`Dependent_name`;
            
-- Query 8:
SELECT 
    `e`.`Fname`, `e`.`Lname`
FROM
    `employee` AS `e`
WHERE
    EXISTS( SELECT 
            *
        FROM
            `dependent` AS `d`
        WHERE
            `e`.`Ssn` = `d`.`Essn`
                AND `e`.`Sex` = `d`.`Sex`
                AND `e`.`Fname` = `d`.`Dependent_name`);

SELECT 
    `Fname`, `Lname`
FROM
    `employee`
WHERE
    NOT EXISTS( SELECT 
            *
        FROM
            `dependent`
        WHERE
            `Ssn` = `Essn`);

-- Query 9:
SELECT 
    `Fname`, `Lname`
FROM
    `employee`
WHERE
    EXISTS( SELECT 
            *
        FROM
            `dependent`
        WHERE
            `Ssn` = `Essn`
                AND EXISTS( SELECT 
                    *
                FROM
                    `department`
                WHERE
                    `Ssn` = `Mgr_ssn`));

-- Query 10:
SELECT 
    SUM(`Salary`), MAX(`Salary`), MIN(`Salary`), AVG(`Salary`)
FROM
    `employee`;

-- Query 11:
SELECT 
    SUM(`Salary`) AS `Total Salary`,
    MAX(`Salary`) AS `Highest Salary`,
    MIN(`Salary`) AS `Lowest Salary`,
    AVG(`Salary`) AS `Average Salary`
FROM
    `employee`;
    
-- Query 12:
SELECT 
    SUM(`Salary`), MAX(`Salary`), MIN(`Salary`), AVG(`Salary`)
FROM
    (`employee`
    JOIN `department` ON `Dno` = `Dnumber`)
WHERE
    `Dname` = 'Research';
    
-- Query 13:
SELECT 
    COUNT(*)
FROM
    `employee`;
    
-- Query 14:
SELECT 
    COUNT(*)
FROM
    `employee`,
    `department`
WHERE
    `Dno` = `Dnumber`
        AND `Dname` = 'Research';
    
-- Query 15:
SELECT 
    COUNT(DISTINCT `Salary`)
FROM
    `employee`;
    
-- Query 16:
SELECT 
    `Lname`, `Fname`
FROM
    `employee`
WHERE
    (SELECT 
            COUNT(*)
        FROM
            `dependent`
        WHERE
            `Ssn` = `Essn`) >= 2;
    
-- Query 17:
SELECT 
    `Dno`, COUNT(*), AVG(`Salary`)
FROM
    `employee`
GROUP BY `Dno`;
    
-- Query 18:
SELECT 
    `Pnumber`, `Pname`, COUNT(*)
FROM
    `project`,
    `works_on`
WHERE
    `Pnumber` = `Pno`
GROUP BY `Pnumber` , `Pname`;
    
-- Query 19:
SELECT 
    `Pnumber`, `Pname`, COUNT(*)
FROM
    `project`,
    `works_on`
WHERE
    `Pnumber` = `Pno`
GROUP BY `Pnumber` , `Pname`
HAVING COUNT(*) > 2;
    
-- Query 20:
SELECT 
    `Pnumber`, `Pname`, COUNT(*)
FROM
    `project`,
    `works_on`,
    `employee`
WHERE
    `Pnumber` = `Pno` AND `Ssn` = `Essn`
        AND `Dno` = 5
GROUP BY `Pnumber` , `Pname`;

-- Query 21:
SELECT 
    `Dno`, COUNT(*)
FROM
    `employee`
WHERE
    `Salary` > 4000
GROUP BY `Dno`
HAVING COUNT(*) > 3;

-- Query 22:
SELECT 
    `Dno`, COUNT(*)
FROM
    `employee`
WHERE
    `Salary` > 10000
        AND `Dno` IN (SELECT 
            `Dno`
        FROM
            `employee`
        GROUP BY `Dno`
        HAVING COUNT(*) > 2)
GROUP BY `Dno`;

-- Query 23:
-- Alias for 'INNER JOIN'
SELECT 
    `Fname`, `Lname`, `Address`
FROM
    (`employee`
    JOIN `department` ON `Dno` = `Dnumber`)
WHERE
    `Dname` = 'Research';

-- Query 24:
SELECT 
    `Fname`, `Lname`, `Address`
FROM
    (`employee`
    INNER JOIN `department` ON `Dno` = `Dnumber`)
WHERE
    `Dname` = 'Research';

-- Query 25:
-- Alias for 'LEFT JOIN'
SELECT 
    `e`.`Lname` AS `Employee Name`,
    `s`.`Lname` AS `Supervisor Name`
FROM
    (`employee` AS `e`
    LEFT OUTER JOIN `employee` AS `s` ON `e`.`Super_ssn` = `s`.`ssn`);

-- Query 26:
-- Alias for 'RIGHT JOIN'
SELECT 
    `e`.`Lname` AS `Employee Name`,
    `s`.`Lname` AS `Supervisor Name`
FROM
    (`employee` AS `e`
    RIGHT OUTER JOIN `employee` AS `s` ON `e`.`Super_ssn` = `s`.`Ssn`);
    
-- Create View 1:
CREATE VIEW `works_on1` AS
    SELECT 
        `Fname`, `Lname`, `Pname`, `Hours`
    FROM
        `employee`,
        `project`,
        `works_on`
    WHERE
        `Ssn` = `Essn` AND `Pno` = `Pnumber`;

-- Create View 2:
CREATE VIEW `dept_info` (`Dept_name` , `No_of_emps` , `Total_sal`) AS
    SELECT 
        `Dname`, COUNT(*), SUM(`Salary`)
    FROM
        `department`,
        `employee`
    WHERE
        `Dnumber` = `Dno`
    GROUP BY `Dname`;

-- Query 27:
SHOW FULL TABLES WHERE `table_type`='VIEW';

employee_db_schema.sql

CREATE DATABASE IF NOT EXISTS `employee_db`
DEFAULT CHARACTER SET latin1
COLLATE latin1_swedish_ci;

USE `employee_db`;

CREATE TABLE EMPLOYEE (
    Fname VARCHAR(10) NOT NULL,
    Minit CHAR,
    Lname VARCHAR(20) NOT NULL,
    Ssn CHAR(9) NOT NULL,
    Bdate DATE,
    Address VARCHAR(30),
    Sex CHAR(1),
    Salary DECIMAL(5),
    Super_ssn CHAR(9),
    Dno INT NOT NULL,
    PRIMARY KEY (Ssn)
);

CREATE TABLE DEPARTMENT (
    Dname VARCHAR(15) NOT NULL,
    Dnumber INT NOT NULL,
    Mgr_ssn CHAR(9) NOT NULL,
    Mgr_start_date DATE,
    PRIMARY KEY (Dnumber),
    UNIQUE (Dname),
    FOREIGN KEY (Mgr_ssn)
        REFERENCES EMPLOYEE (Ssn)
);

CREATE TABLE DEPT_LOCATIONS (
    Dnumber INT NOT NULL,
    Dlocation VARCHAR(15) NOT NULL,
    PRIMARY KEY (Dnumber , Dlocation),
    FOREIGN KEY (Dnumber)
        REFERENCES DEPARTMENT (Dnumber)
);

CREATE TABLE PROJECT (
    Pname VARCHAR(15) NOT NULL,
    Pnumber INT NOT NULL,
    Plocation VARCHAR(15),
    Dnum INT NOT NULL,
    PRIMARY KEY (Pnumber),
    UNIQUE (Pname),
    FOREIGN KEY (Dnum)
        REFERENCES DEPARTMENT (Dnumber)
);

CREATE TABLE WORKS_ON( Essn            CHAR(9)           NOT NULL,
  Pno             INT               NOT NULL,
  Hours           DECIMAL(3,1)      NOT NULL,
PRIMARY KEY (Essn, Pno),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn),
FOREIGN KEY (Pno) REFERENCES PROJECT(Pnumber) );

CREATE TABLE DEPENDENT( Essn            CHAR(9)           NOT NULL,
  Dependent_name  VARCHAR(15)       NOT NULL,
  Sex             CHAR,
  Bdate           DATE,
  Relationship    VARCHAR(8),
PRIMARY KEY (Essn, Dependent_name),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn) );

INSERT INTO EMPLOYEE
VALUES      ('John','B','Smith',123456789,'1965-01-09','731 Fondren, Houston TX','M',30000,333445555,5),
            ('Franklin','T','Wong',333445555,'1965-12-08','638 Voss, Houston TX','M',40000,888665555,5),
            ('Alicia','J','Zelaya',999887777,'1968-01-19','3321 Castle, Spring TX','F',25000,987654321,4),
            ('Jennifer','S','Wallace',987654321,'1941-06-20','291 Berry, Bellaire TX','F',43000,888665555,4),
            ('Ramesh','K','Narayan',666884444,'1962-09-15','975 Fire Oak, Humble TX','M',38000,333445555,5),
            ('Joyce','A','English',453453453,'1972-07-31','5631 Rice, Houston TX','F',25000,333445555,5),
            ('Ahmad','V','Jabbar',987987987,'1969-03-29','980 Dallas, Houston TX','M',25000,987654321,4),
            ('James','E','Borg',888665555,'1937-11-10','450 Stone, Houston TX','M',55000,null,1);

INSERT INTO DEPARTMENT
VALUES      ('Research',5,333445555,'1988-05-22'),
            ('Administration',4,987654321,'1995-01-01'),
            ('Headquarters',1,888665555,'1981-06-19');

INSERT INTO PROJECT
VALUES      ('ProductX',1,'Bellaire',5),
            ('ProductY',2,'Sugarland',5),
            ('ProductZ',3,'Houston',5),
            ('Computerization',10,'Stafford',4),
            ('Reorganization',20,'Houston',1),
            ('Newbenefits',30,'Stafford',4);

INSERT INTO WORKS_ON
VALUES     (123456789,1,32.5),
           (123456789,2,7.5),
           (666884444,3,40.0),
           (453453453,1,20.0),
           (453453453,2,20.0),
           (333445555,2,10.0),
           (333445555,3,10.0),
           (333445555,10,10.0),
           (333445555,20,10.0),
           (999887777,30,30.0),
           (999887777,10,10.0),
           (987987987,10,35.0),
           (987987987,30,5.0),
           (987654321,30,20.0),
           (987654321,20,15.0),
           (888665555,20,16.0);

INSERT INTO DEPENDENT
VALUES      (333445555,'Alice','F','1986-04-04','Daughter'),
            (333445555,'Theodore','M','1983-10-25','Son'),
            (333445555,'Joy','F','1958-05-03','Spouse'),
            (987654321,'Abner','M','1942-02-28','Spouse'),
            (123456789,'Michael','M','1988-01-04','Son'),
            (123456789,'Alice','F','1988-12-30','Daughter'),
            (123456789,'Elizabeth','F','1967-05-05','Spouse');

INSERT INTO DEPT_LOCATIONS
VALUES      (1,'Houston'),
            (4,'Stafford'),
            (5,'Bellaire'),
            (5,'Sugarland'),
            (5,'Houston');

ALTER TABLE DEPARTMENT
 ADD CONSTRAINT Dep_emp
 FOREIGN KEY (Mgr_ssn)
 REFERENCES EMPLOYEE(Ssn);

ALTER TABLE EMPLOYEE
 ADD CONSTRAINT Emp_emp
 FOREIGN KEY (Super_ssn)
 REFERENCES EMPLOYEE(Ssn);
 
ALTER TABLE EMPLOYEE
 ADD CONSTRAINT Emp_dno
 FOREIGN KEY (Dno)
 REFERENCES DEPARTMENT(Dnumber);
 
ALTER TABLE EMPLOYEE
 ADD CONSTRAINT Emp_super
 FOREIGN KEY (Super_ssn)
 REFERENCES EMPLOYEE(Ssn);