-- Creating the emp table
CREATE TABLE emp (
    eno INT PRIMARY KEY,
    ename VARCHAR(100),
    bdate DATE,
    title VARCHAR(50),
    salary DECIMAL(10, 2),
    dno VARCHAR(10)
);

-- Creating the proj table
CREATE TABLE proj (
    pno INT PRIMARY KEY,
    pname VARCHAR(100),
    budget DECIMAL(15, 2),
    dno INT
);

-- Creating the dept table
CREATE TABLE dept (
    dno VARCHAR(10) PRIMARY KEY,
    dname VARCHAR(100),
    mgreno INT
);

-- Creating the workson table
CREATE TABLE workson (
    eno INT,
    pno INT,
    resp VARCHAR(100),
    hours INT
    );

-- Inserting sample data into emp table
INSERT INTO emp (eno, ename, bdate, title, salary, dno) VALUES
(101, 'Ved', '1980-01-01', 'Manager', 60000, 'D1'),
(102, 'Om', '1985-02-01', 'Analyst', 50000, 'D2'),
(103, 'Harsh', '1990-03-01', 'EE', 40000, 'D3'),
(104, 'Santosh', '1975-04-01', 'Designer', 55000, 'D4'),
(105, 'Akash', '1982-05-01', 'SA', 36000, 'D5');


-- Inserting sample data into proj table
INSERT INTO proj (pno, pname, budget, dno) VALUES
(1, 'Project A', 120000, 'D1'),
(2, 'Project B', 95000, 'D2'),
(3, 'Project C', 200000, 'D3'),
(4, 'Project D', 50000, 'D4'),
(5, 'Project E', 150000, 'D5');


-- Inserting sample data into dept table
INSERT INTO dept (dno, dname, mgreno) VALUES
('D1', 'HR', 6801),
('D2', 'Finance', 6802),
('D3', 'IT', 6803),
('D4', 'Marketing', 6804),
('D5', 'Consulting', 6805);

-- Inserting sample data into workson table
INSERT INTO workson (eno, pno, resp, hours) VALUES
(101, 1, 'Manager', 20),
(102, 2, 'Analyst', 15),
(103, 3, 'EE', 25),
(104, 4, 'Designer', 30),
(105, 5, 'Manager', 8);

-- 1) Write an SQL query that returns the project number and name for projects with a 
-- budget greater than $100,000.
SELECT pno, pname 
FROM proj 
WHERE budget > 100000;   

Output-
1|Project A
3|Project C
5|Project E

--2) Write an SQL query that returns all works on records where hours worked is less than 
--10 and the responsibility is 'Manager'.
SELECT * 
FROM workson 
WHERE hours < 10 AND resp = 'Manager';

Output-


-- 3) Write an SQL query that returns the employees (number and name only) who have a 
-- title of 'EE' or 'SA' and make more than $35,000.
SELECT eno, ename 
FROM emp 
WHERE title in ('EE' , 'SA') AND salary > 35000;

-- 4) Write an SQL query that returns the employees (name only) in department 'D1' 
-- ordered by decreasing salary.
SELECT ename FROM emp 
WHERE dno = 'D1' 
ORDER BY salary DESC;


-- 5) Write an SQL query that returns the departments (all fields) ordered by ascending 
-- department name.
SELECT * FROM dept 
ORDER BY dname ASC;

--6) Write an SQL query that returns the employee name, department name, and employee 
--title.
SELECT ename, dname, title 
FROM emp, dept 
WHERE emp.dno = dept.dno;

-- 7) Write an SQL query that returns the project name, hours worked, and project number 
-- for all works on records where hours > 10.
SELECT pname, hours, proj.pno
FROM workson, proj 
WHERE hours > 10 AND proj.pno = workson.pno;

--8) Write an SQL query that returns the project name, department name, and budget for 
--all projects with a budget < $50,000.
SELECT pname, dname, budget 
FROM proj, dept 
WHERE budget < 50000 AND proj.dno = dept.dno;




--9) Write an SQL query that returns the employee numbers and salaries of all employees 
-- in the 'Consulting' department ordered by descending salary.
SELECT eno, salary 
FROM emp, dept 
WHERE dname = 'Consulting' 
ORDER BY salary DESC;

Output-
101|60000
104|55000
102|50000
103|40000
105|36000

--10) Write an SQL query that returns the employee name, project name, employee title, 
--and hours for all works on records.
 SELECT ename, pname, title, hours
FROM emp, proj, workson 
WHERE emp.eno = workson.eno and proj.pno = workson.pno;

Output-
Ved|Project A|Manager|20
Om|Project B|Analyst|15
Harsh|Project C|EE|25
Santosh|Project D|Designer|30
Akash|Project E|SA|8