Assignment – 6
Set A
1. Create Employee Table
CREATE TABLE emp (
empno INT PRIMARY KEY,
name VARCHAR(50),
address VARCHAR(50),
salary DECIMAL(10,2),
deptno INT
);
2. Insert Records (10 Records)
INSERT INTO emp VALUES (101,'Anand','Pune',50000,10);
INSERT INTO emp VALUES (102,'Rohit','Mumbai',45000,20);
INSERT INTO emp VALUES (103,'Sandeep','Pune',60000,10);
INSERT INTO emp VALUES (104,'Amit','Delhi',55000,30);
INSERT INTO emp VALUES (105,'Nandini','Pune',48000,20);
INSERT INTO emp VALUES (106,'Rajesh','Mumbai',NULL,30);
INSERT INTO emp VALUES (107,'Kiran','Pune',52000,10);
INSERT INTO emp VALUES (108,'Sandhya','Delhi',47000,40);
INSERT INTO emp VALUES (109,'Mohan','Mumbai',65000,20);
INSERT INTO emp VALUES (110,'Anandita','Pune',70000,40);
3. Select Queries
[Link] * FROM emp;
[Link] empno, name FROM emp;
[Link] DISTINCT deptno FROM emp;
[Link] * FROM emp WHERE deptno = 10;
[Link] * FROM emp WHERE address = 'Pune' AND salary > 50000;
[Link] * FROM emp WHERE address = 'Pune' AND salary BETWEEN 45000 AND 60000;
[Link] * FROM emp WHERE name LIKE 'An%';
[Link] * FROM emp WHERE name LIKE '%and%';
[Link] * FROM emp WHERE salary IS NULL;
[Link] * FROM emp ORDER BY empno;
[Link] * FROM emp ORDER BY deptno, empno DESC;
[Link] deptno AS department, SUM(salary) AS total FROM emp GROUP BY deptno ORDER
BY deptno;
[Link] deptno AS department, COUNT(empno) AS total_emp FROM emp
GROUP BY deptno HAVING COUNT(empno) > 2 ORDER BY deptno;
[Link] AVG(salary) FROM emp;
[Link] deptno, MAX(salary) FROM emp GROUP BY deptno HAVING MAX(salary) > 55000;
[Link] deptno, MIN(salary) FROM emp GROUP BY deptno ORDER BY deptno;
4. Department Table
CREATE TABLE department (
deptno INT PRIMARY KEY,
dname VARCHAR(50)
);
INSERT INTO department VALUES (10,'finance');
INSERT INTO department VALUES (20,'inventory');
INSERT INTO department VALUES (30,'production');
INSERT INTO department VALUES (40,'hr');
5. Update Queries
[Link] emp
SET salary = salary + 0.5 * salary
WHERE deptno = (SELECT deptno FROM department WHERE dname='finance');
[Link] emp
SET deptno = (SELECT deptno FROM department WHERE dname='finance')
WHERE deptno = (SELECT deptno FROM department WHERE dname='inventory');
6. Backup Table
[Link] TABLE emp_backup (
empno INT,
name VARCHAR(50)
);
7. Delete Query
20. DELETE FROM emp
WHERE deptno = (SELECT deptno FROM department WHERE dname='production');
Set – B
1. Create Tables
CREATE TABLE Area (
area_id INT PRIMARY KEY,
aname VARCHAR(30),
area_type VARCHAR(10) CHECK (area_type IN ('urban','rural'))
);
CREATE TABLE Person (
pnumber INT PRIMARY KEY,
pname VARCHAR(30),
birthdate DATE,
income DECIMAL(10,2),
area_id INT,
FOREIGN KEY (area_id) REFERENCES Area(area_id)
);
2. Insert Records
Area Table
INSERT INTO Area VALUES (1,'Pune','urban');
INSERT INTO Area VALUES (2,'Mumbai','urban');
INSERT INTO Area VALUES (3,'Nagpur','rural');
INSERT INTO Area VALUES (4,'Nashik','rural');
Person Table
INSERT INTO Person VALUES (101,'Anand','1995-03-15',50000,1);
INSERT INTO Person VALUES (102,'Amit','1994-03-15',45000,1);
INSERT INTO Person VALUES (103,'Rohit','1992-07-21',60000,2);
INSERT INTO Person VALUES (104,'Sandeep','1990-01-10',70000,2);
INSERT INTO Person VALUES (105,'Nandini','1995-12-05',48000,3);
INSERT INTO Person VALUES (106,'Kiran','1993-03-15',52000,3);
INSERT INTO Person VALUES (107,'Mohan','1991-06-18',65000,4);
INSERT INTO Person VALUES (108,'Sandhya','1994-07-21',40000,1);
3. SELECT Queries
1. Names of all people living in Pune
SELECT pname
FROM Person p, Area a
WHERE p.area_id = a.area_id
AND [Link] = 'Pune';
2. Names starting with ‘A’ and max 5 alphabets
SELECT pname
FROM Person
WHERE pname LIKE 'A%' AND LENGTH(pname) <= 5;
3. Names of people born in March
SELECT pname
FROM Person
WHERE MONTH(birthdate) = 3;
4. Count of people born on 1995-03-15
SELECT COUNT(*)
FROM Person
WHERE birthdate = '1995-03-15';
5. Count of people with income below 50000
SELECT COUNT(*)
FROM Person
WHERE income < 50000;
6. Names with income between 45000 and 60000
SELECT pname
FROM Person
WHERE income BETWEEN 45000 AND 60000;
7. Names of people with average income
SELECT pname
FROM Person
WHERE income = (SELECT AVG(income) FROM Person);
8. Sum of incomes of people living in Mumbai
SELECT SUM(income)
FROM Person p, Area a
WHERE p.area_id = a.area_id
AND [Link] = 'Mumbai';
9. Areas having people with maximum income
SELECT DISTINCT [Link]
FROM Person p, Area a
WHERE p.area_id = a.area_id
AND [Link] = (SELECT MAX(income) FROM Person);
10. Count of people in each area
SELECT [Link], COUNT([Link])
FROM Area a LEFT JOIN Person p
ON a.area_id = p.area_id
GROUP BY [Link];
11. People living in Pune and income > 50000
SELECT *
FROM Person p, Area a
WHERE p.area_id = a.area_id
AND [Link] = 'Pune'
AND income > 50000;
12. Details sorted by person number
SELECT * FROM Person ORDER BY pnumber;
13. Details sorted by area and person name
SELECT *
FROM Person
ORDER BY area_id, pname;
14. Minimum income of people
SELECT MIN(income) FROM Person;
15. Transfer all people from Pune to Mumbai
UPDATE Person
SET area_id = (SELECT area_id FROM Area WHERE aname='Mumbai')
WHERE area_id = (SELECT area_id FROM Area WHERE aname='Pune');
16. Delete people staying in urban area
DELETE FROM Person
WHERE area_id IN (
SELECT area_id FROM Area WHERE area_type='urban'
);