Scribd
Upload
9 views28 pages

Dbms Sheet

The document contains a series of SQL case studies involving the creation and manipulation of database tables for Players, Countries, Colleges, Departments, and Customers. It includes commands for creating tables, inserting values, updating attributes, and performing various queries to extract specific information. Each case study demonstrates different SQL functionalities such as joins, aggregations, and conditional selections.

Uploaded by

tanvi.digikraf
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
9 views28 pages

Dbms Sheet

The document contains a series of SQL case studies involving the creation and manipulation of database tables for Players, Countries, Colleges, Departments, and Customers. It includes commands for creating tables, inserting values, updating attributes, and performing various queries to extract specific information. Each case study demonstrates different SQL functionalities such as joins, aggregations, and conditional selections.

Uploaded by

tanvi.digikraf
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

CASE STUDY 1

-- 1. Create Player and Country Tables (MySQL)


CREATE TABLE Country (
Tid INT PRIMARY KEY,
Cname VARCHAR(15)
);

CREATE TABLE Player (


Pid INT PRIMARY KEY,
Pname VARCHAR(20),
Role VARCHAR(20),
Age INT,
Runs INT,
Wicket INT,
Tid INT,
FOREIGN KEY (Tid) REFERENCES Country(Tid)
);

-- 2. Insert Values into Tables

INSERT INTO Country VALUES


(101,'India'),
(102,'Srilanka'),
(103,'England'),
(104,'Australia'),
(105,'South Africa');

INSERT INTO Player VALUES


(1001,'Sachin','Batsman',25,10000,45,101),
(1002,'Jaywardhne','Captain',29,5000,NULL,102),
(1003,'Kapil','Bowler',30,2000,470,103),
(1004,'Clark','Batsman',27,3000,120,104),
(1005,'Gautam','Batsman',28,2000,NULL,101),
(1006,'Rahul','Batsman',22,15000,NULL,101),
(1007,'Vas','Bowler',28,3000,480,102),
(1008,'Flintop','Bowler',24,6200,190,103),
(1009,'Hussy','Batsman',29,2900,NULL,104),
(1010,'Murlidharan','Bowler',31,1800,500,102),
(1011,'Laxman','Batsman',21,5200,NULL,101),
(1112,'Dhoni','Wicketkeeper',24,4500,NULL,101);

-- 3. Add attribute NOM


ALTER TABLE Player ADD NOM INT;

-- 4. Update NOM = 75
UPDATE Player SET NOM = 75;

-- 5. Players not taking wickets


SELECT Pname
FROM Player
WHERE Wicket IS NULL OR Wicket = 0;

-- 6. Lower & Upper case


SELECT LOWER(Pname), UPPER(Pname)
FROM Player;

-- 7. Strike Rate
SELECT Pname, Runs, (Runs/NOM) AS Strike_Rate
FROM Player;

-- 8. Different roles
SELECT DISTINCT Role FROM Player;

-- 9. Not in teams 101,102,104


SELECT *
FROM Player
WHERE Tid NOT IN (101,102,104);

-- 10. Runs between 10000 and 20000


SELECT Pname, Runs
FROM Player
WHERE Runs BETWEEN 10000 AND 20000;
-- 11. Team-wise stats (Tid = 101)
SELECT Tid,
SUM(Runs) AS Total_Runs,
MAX(Runs) AS Highest_Runs,
AVG(Runs) AS Avg_Runs
FROM Player
WHERE Tid = 101
GROUP BY Tid
HAVING AVG(Runs) > 1500
ORDER BY Total_Runs DESC;

-- 12. Players in each country (include empty)


SELECT [Link], [Link]
FROM Country C
LEFT JOIN Player P
ON [Link] = [Link];

-- 13. Same runs as VAS


SELECT Pname
FROM Player
WHERE Runs = (
SELECT Runs FROM Player WHERE Pname = 'Vas'
);

-- 14. Total players (MySQL version)


SELECT COUNT(*) AS Total_Players FROM Player;

-- 15. Roles in Tid 101 & 102


SELECT DISTINCT Role
FROM Player
WHERE Tid IN (101,102);
CASE STUDY 2
-- 1. Create Player and Country Tables
CREATE TABLE Country (
Tid INT PRIMARY KEY,
Cname VARCHAR(15)
);

CREATE TABLE Player (


Pid INT PRIMARY KEY,
Pname VARCHAR(20),
Role VARCHAR(20),
Age INT,
Runs INT,
Wicket INT,
Tid INT,
NOM INT,
FOREIGN KEY (Tid) REFERENCES Country(Tid)
);

-- 2. Insert Values
INSERT INTO Country VALUES
(101,'India'),
(102,'Srilanka'),
(103,'England'),
(104,'Australia'),
(105,'South Africa');

INSERT INTO Player VALUES


(1001,'Sachin','Batsman',25,10000,45,101,NULL),
(1002,'Jaywardhne','Captain',29,5000,NULL,102,NULL),
(1003,'Kapil','Bowler',30,2000,470,103,NULL),
(1004,'Clark','Batsman',27,3000,120,104,NULL),
(1005,'Gautam','Batsman',28,20000,NULL,101,NULL),
(1006,'Rahul','Batsman',22,15000,NULL,101,NULL),
(1007,'Vas','Bowler',28,3000,480,102,NULL),
(1008,'Flintop','Bowler',24,6200,190,103,NULL),
(1009,'Hussy','Batsman',29,2900,NULL,104,NULL),
(1010,'Murlidharan','Bowler',31,1800,500,102,NULL),
(1011,'Laxman','Batsman',21,5200,NULL,101,NULL),
(1112,'Dhoni','Wicketkeeper',24,4500,NULL,101,NULL);

-- 3. Modify width of NOM to number(4)


ALTER TABLE Player MODIFY NOM INT(4);

-- 4. Update NOM = 75
UPDATE Player SET NOM = 75;

-- 5. List batsman for Tid = 101


SELECT Pname
FROM Player
WHERE Role = 'Batsman' AND Tid = 101;

-- 6. Country names containing 'N'


SELECT Cname
FROM Country
WHERE Cname LIKE '%n%' OR Cname LIKE '%N%';

-- 7. Name, Runs, Strike Rate


SELECT Pname, Runs, (Runs*1.0/NOM) AS Strike_Rate
FROM Player;

-- 8. Different Tid’s present


SELECT DISTINCT Tid FROM Player;

-- 9. Names ending with 'N'


SELECT Pname
FROM Player
WHERE Pname LIKE '%n' OR Pname LIKE '%N';
-- 10. Runs between 2000 and 8000
SELECT Pname, Runs
FROM Player
WHERE Runs BETWEEN 2000 AND 8000;

-- 11. Team stats (Tid = 101)


SELECT Tid,
SUM(Runs) AS Total_Runs,
MAX(Runs) AS Highest_Runs,
AVG(Runs) AS Avg_Runs
FROM Player
WHERE Tid = 101
GROUP BY Tid
HAVING AVG(Runs) > 1000
ORDER BY Total_Runs ASC;

-- 12. Display pid, pname, tid, country name


SELECT [Link], [Link], [Link], [Link]
FROM Player P
JOIN Country C
ON [Link] = [Link];

-- 13. Players belonging to South Africa


SELECT Pname
FROM Player
WHERE Tid = (
SELECT Tid FROM Country WHERE Cname = 'South Africa'
);

-- 14. Create View (pname, age, avg runs)


CREATE VIEW Player_View AS
SELECT Pname, Age, AVG(Runs) AS Avg_Runs
FROM Player
GROUP BY Pname, Age;
-- 15. Common roles in Tid 102 and 103
SELECT Role
FROM Player
WHERE Tid = 102
INTERSECT
SELECT Role
FROM Player
WHERE Tid = 103;
CASE STUDY 3
-- 1. Create College and Department Tables
CREATE TABLE Department (
DEPTNO INT PRIMARY KEY,
DNAME VARCHAR(20)
);

CREATE TABLE College (


SNO INT PRIMARY KEY,
SNAME VARCHAR(20),
Year VARCHAR(15),
DOA DATE,
PCM INT,
DEPTNO INT,
FOREIGN KEY (DEPTNO) REFERENCES Department(DEPTNO)
);

-- 2. Insert Values
INSERT INTO Department VALUES
(10,'Computer'),
(20,'Electronics'),
(30,'Civil'),
(40,'Chemical');

INSERT INTO College VALUES


(7369,'Adams','First','2008-06-01',275,20),
(7499,'Brooks','Second','2007-06-01',265,10),
(7521,'Curry','Second','2007-06-01',255,30),
(7566,'Glenn','Third','2006-06-01',215,30),
(7654,'Green','Second','2006-06-01',220,30),
(7698,'Hayes','Third','2006-06-01',235,30),
(7782,'Johnson','Third','2006-06-01',290,10),
(7783,'Jones','Third','2006-06-01',189,20),
(7839,'Lindsay','Final','2005-06-01',175,10),
(7844,'Smith','Second','2007-06-01',263,30),
(7876,'Turner','First','2008-06-01',285,20),
(7900,'Williams','First','2008-06-01',275,30),
(7902,'Adams','Third','2006-06-01',274,20),
(7934,'Brooks','First','2008-06-01',167,10);

-- 3. Add Sports Marks (SM)


ALTER TABLE College ADD SM INT(2);

-- 4. Update SM = 15 for DEPTNO = 10


UPDATE College
SET SM = 15
WHERE DEPTNO = 10;

-- 5. Students eligible for Sports Marks


SELECT SNAME
FROM College
WHERE SM IS NOT NULL;

-- 6. Names with first letter capital only


SELECT CONCAT(UPPER(LEFT(SNAME,1)),
LOWER(SUBSTRING(SNAME,2)))
FROM College;

-- 7. Name, PCM and Percentage


SELECT SNAME, PCM, (PCM/300)*100 AS Percentage
FROM College;

-- 8. Different Years
SELECT DISTINCT Year FROM College;

-- 9. Students not in Dept 20,30,40


SELECT *
FROM College
WHERE DEPTNO NOT IN (20,30,40);
-- 10. PCM between 250 and 300
SELECT SNAME, PCM
FROM College
WHERE PCM BETWEEN 250 AND 300;

-- 11. Dept-wise stats (Dept 30)


SELECT DEPTNO,
SUM(PCM) AS Total_PCM,
MAX(PCM) AS Highest_PCM,
AVG(PCM) AS Avg_PCM
FROM College
WHERE DEPTNO = 30
GROUP BY DEPTNO
HAVING AVG(PCM) > 250
ORDER BY Total_PCM DESC;

-- 12. Students in each department (include empty)


SELECT [Link], [Link]
FROM Department D
LEFT JOIN College C
ON [Link] = [Link];

-- 13. Students with same PCM as Williams


SELECT SNAME
FROM College
WHERE PCM = (
SELECT PCM FROM College WHERE SNAME = 'Williams'
);

-- 14. Total number of students


SELECT COUNT(*) AS Total_Students FROM College;

-- 15. Different years in Dept 10 and 20


SELECT DISTINCT Year
FROM College
WHERE DEPTNO IN (10,20);
CASE STUDY 4
-- 1. Create College and Department Tables
CREATE TABLE Department (
DEPTNO INT PRIMARY KEY,
DNAME VARCHAR(20)
);

CREATE TABLE College (


SNO INT PRIMARY KEY,
SNAME VARCHAR(20),
Year VARCHAR(15),
DOA DATE,
PCM INT,
SM INT,
DEPTNO INT,
FOREIGN KEY (DEPTNO) REFERENCES Department(DEPTNO)
);

-- 2. Insert Values
INSERT INTO Department VALUES
(10,'Computer'),
(20,'Electronics'),
(30,'Civil'),
(40,'Chemical');

INSERT INTO College VALUES


(7369,'adams','First','2008-06-01',275,NULL,20),
(7499,'brooks','Second','2007-06-01',265,NULL,30),
(7521,'curry','Second','2007-06-01',255,NULL,30),
(7566,'glenn','Third','2006-06-01',215,NULL,20),
(7654,'green','Second','2007-06-01',220,NULL,30),
(7698,'hayes','Third','2006-06-01',235,NULL,30),
(7782,'johnson','Third','2006-06-01',290,NULL,10),
(7783,'jones','Third','2006-06-01',189,NULL,20),
(7839,'lindsay','Final','2005-06-01',175,NULL,10),
(7844,'smith','Second','2007-06-01',263,NULL,30),
(7876,'turner','First','2008-06-01',285,NULL,20),
(7900,'williams','First','2008-06-01',253,NULL,30),
(7902,'adams','Third','2006-06-01',274,NULL,20),
(7934,'brooks','First','2008-06-01',167,NULL,10);

-- 3. Modify width of SM to number(4)


ALTER TABLE College MODIFY SM INT(4);

-- 4. Update SM = 10 for department 20


UPDATE College
SET SM = 10
WHERE DEPTNO = 20;

-- 5. Names of second year students in dept 10


SELECT SNAME
FROM College
WHERE Year = 'Second' AND DEPTNO = 10;

-- 6. Names ending with 's'


SELECT SNAME
FROM College
WHERE SNAME LIKE '%s';

-- 7. Name, PCM and Percentage


SELECT SNAME, PCM, (PCM*100.0/300) AS Percentage
FROM College;

-- 8. Different department numbers


SELECT DISTINCT DEPTNO FROM College;

-- 9. Names ending with 'N'


SELECT SNAME
FROM College
WHERE SNAME LIKE '%n' OR SNAME LIKE '%N';
-- 10. Students admitted between 01-Jun-07 and 01-Jun-08
SELECT SNAME, DOA
FROM College
WHERE DOA > '2007-06-01' AND DOA < '2008-06-01';

-- 11. Dept-wise stats (Dept 20)


SELECT DEPTNO,
SUM(PCM) AS Total_PCM,
MAX(PCM) AS Highest_PCM,
AVG(PCM) AS Avg_PCM
FROM College
WHERE DEPTNO = 20
GROUP BY DEPTNO
HAVING AVG(PCM) > 250
ORDER BY Total_PCM ASC;

-- 12. Student name, year, deptno, department name


SELECT [Link], [Link], [Link], [Link]
FROM College C
JOIN Department D
ON [Link] = [Link];

-- 13. Students in department of 'Smith'


SELECT SNAME
FROM College
WHERE DEPTNO = (
SELECT DEPTNO FROM College WHERE SNAME = 'smith'
);

-- 14. Create View (sno, sname, pcm)


CREATE VIEW Student_View AS
SELECT SNO, SNAME, PCM FROM College;
-- 15. Common years in dept 10 and 20
SELECT DISTINCT [Link]
FROM College c1
JOIN College c2
ON [Link] = [Link]
WHERE [Link] = 10 AND [Link] = 20;
CASE STUDY 5
-- 1. Create Customer and Branch Tables
CREATE TABLE Branch (
Branchcode INT PRIMARY KEY,
BranchName VARCHAR(20)
);

CREATE TABLE Customer (


Acno VARCHAR(6) PRIMARY KEY,
cname VARCHAR(20),
cstreet VARCHAR(20),
ccity VARCHAR(20),
Balamt DECIMAL(9,2),
Loanno VARCHAR(4),
Loanamt DECIMAL(9,2),
Branchcode INT,
FOREIGN KEY (Branchcode) REFERENCES Branch(Branchcode)
);

-- 2. Insert Values
INSERT INTO Branch VALUES
(10,'Brighton'),
(20,'Downtown'),
(30,'Mianus'),
(40,'North Town');

INSERT INTO Customer VALUES


('A-101','Adams','Spring','Pittsfield',9000,'L-1',5000,10),
('A-201','Brooks','Senator','Brooklyn',8000,NULL,NULL,20),
('A-301','Curry','North','Rye',4500,'L-2',2000,10),
('A-401','Glenn','Sand Hill','Woodside',8900,NULL,NULL,20),
('A-501','Jones','Main','Harrison',3784,NULL,NULL,10),
('A-601','Lindsay','Park','Pittsfield',7893,'L-3',5700,30),
('A-701','Smith','North','Rye',4532,NULL,NULL,10),
('A-801','Turner','Putnam','Stanford',4981,NULL,NULL,30);
-- 3. Add Assets column
ALTER TABLE Customer ADD Assets INT(4);

-- 4. Update Assets = 25% of Balamt


UPDATE Customer
SET Assets = Balamt * 0.25;

-- 5. Customers who have not taken loan


SELECT cname
FROM Customer
WHERE Loanno IS NULL;

-- 6. Names in lower and upper case


SELECT LOWER(cname), UPPER(cname)
FROM Customer;

-- 7. Acno, cname, balamt and interest (7%)


SELECT Acno, cname, Balamt, (Balamt*0.07) AS Interest
FROM Customer;

-- 8. Different cities
SELECT DISTINCT ccity FROM Customer;

-- 9. Customers not in branch 10 and 20


SELECT *
FROM Customer
WHERE Branchcode NOT IN (10,20);

-- 10. Customers with balamt between 2000 and 5000


SELECT cname, Balamt
FROM Customer
WHERE Balamt BETWEEN 2000 AND 5000;
-- 11. Branch-wise stats (Branch 30)
SELECT Branchcode,
SUM(Balamt) AS Total_Balance,
MAX(Balamt) AS Highest_Balance,
AVG(Balamt) AS Avg_Balance
FROM Customer
WHERE Branchcode = 30
GROUP BY Branchcode
HAVING AVG(Balamt) > 1500
ORDER BY Total_Balance DESC;

-- 12. Customers in each branch (include empty)


SELECT [Link], [Link]
FROM Branch B
LEFT JOIN Customer C
ON [Link] = [Link];

-- 13. Customers with same street and city as Smith


SELECT cname
FROM Customer
WHERE (cstreet, ccity) = (
SELECT cstreet, ccity FROM Customer WHERE cname = 'Smith'
);

-- 14. PL/SQL factorial (MySQL alternative)


SELECT 7*6*5*4*3*2*1 AS Factorial;

-- 15. Different cities in branch 10 and 30


SELECT DISTINCT [Link]
FROM Customer c1
JOIN Customer c2
ON [Link] = [Link]
WHERE [Link] = 10 AND [Link] = 30;
CASE STUDY 6
-- 1. Create Customer and Branch Tables
CREATE TABLE Branch (
Branchcode INT PRIMARY KEY,
BranchName VARCHAR(20)
);

CREATE TABLE Customer (


Acno VARCHAR(6) PRIMARY KEY,
cname VARCHAR(20),
cstreet VARCHAR(20),
ccity VARCHAR(20),
Balamt DECIMAL(9,2),
Loanno VARCHAR(4),
Loanamt DECIMAL(9,2),
Branchcode INT,
Assets INT,
FOREIGN KEY (Branchcode) REFERENCES Branch(Branchcode)
);

-- 2. Insert Values
INSERT INTO Branch VALUES
(10,'Brighton'),
(20,'Downtown'),
(30,'Mianus'),
(40,'North Town');

INSERT INTO Customer VALUES


('A-101','Adams','Spring','Pittsfield',9000,'L-1',5000,10,NULL),
('A-201','Brooks','Senator','Brooklyn',8000,NULL,NULL,20,NULL),
('A-301','Curry','North','Rye',4500,'L-2',2000,10,NULL),
('A-401','Glenn','Sand Hill','Woodside',8900,NULL,NULL,20,NULL),
('A-501','Jones','Main','Harrison',3784,NULL,NULL,10,NULL),
('A-601','Lindsay','Park','Pittsfield',7893,'L-3',5700,30,NULL),
('A-701','Smith','North','Rye',4532,NULL,NULL,10,NULL),
('A-801','Turner','Putnam','Stanford',4981,NULL,NULL,30,NULL);
-- 3. Modify width of Assets to number(6)
ALTER TABLE Customer MODIFY Assets INT(6);

-- 4. Update Assets = 25% of Balamt


UPDATE Customer
SET Assets = Balamt * 0.25;

-- 5. Customers of branch 10 staying in Rye


SELECT cname
FROM Customer
WHERE Branchcode = 10 AND ccity = 'Rye';

-- 6. Names containing 'N'


SELECT cname
FROM Customer
WHERE cname LIKE '%n%' OR cname LIKE '%N%';

-- 7. Acno, cname, balamt and interest


SELECT Acno, cname, Balamt, (Balamt*0.07) AS Interest
FROM Customer;

-- 8. Different streets
SELECT DISTINCT cstreet FROM Customer;

-- 9. Names ending with 'S'


SELECT cname
FROM Customer
WHERE cname LIKE '%s' OR cname LIKE '%S';

-- 10. Customers with balamt between 3000 and 5000


SELECT cname, Balamt
FROM Customer
WHERE Balamt BETWEEN 3000 AND 5000;
-- 11. Branch-wise stats (Branch 20)
SELECT Branchcode,
SUM(Balamt) AS Total_Balance,
MAX(Balamt) AS Highest_Balance,
AVG(Balamt) AS Avg_Balance
FROM Customer
WHERE Branchcode = 20
GROUP BY Branchcode
HAVING AVG(Balamt) > 1500
ORDER BY Total_Balance DESC;

-- 12. Acno, cname, branchcode, branch name


SELECT [Link], [Link], [Link], [Link]
FROM Customer C
JOIN Branch B
ON [Link] = [Link];

-- 13. Customers in same branch as Adams


SELECT cname
FROM Customer
WHERE Branchcode = (
SELECT Branchcode FROM Customer WHERE cname = 'Adams'
);

-- 14. Area of circle (radius = 5)


SELECT PI()*5*5 AS Area;

-- 15. Different cities in branch 20 and 30


SELECT DISTINCT [Link]
FROM Customer c1
JOIN Customer c2
ON [Link] = [Link]
WHERE [Link] = 20 AND [Link] = 30;
CASE STUDY 7
-- 1. Create Employee and Department Tables
CREATE TABLE Department (
DEPTNO INT PRIMARY KEY,
DNAME VARCHAR(20),
LOC VARCHAR(20)
);

CREATE TABLE Employee (


EMPNO INT PRIMARY KEY,
ENAME VARCHAR(20),
JOB VARCHAR(20),
MGR INT,
HIREDATE DATE,
SAL DECIMAL(9,2),
COMM DECIMAL(9,2),
DEPTNO INT,
FOREIGN KEY (DEPTNO) REFERENCES Department(DEPTNO)
);

-- 2. Insert Values
INSERT INTO Department VALUES
(10,'Accounting','New York'),
(20,'Research','Dallas'),
(30,'Sales','Chicago'),
(40,'Operations','Boston');

INSERT INTO Employee VALUES


(7369,'Smith','Clerk',7902,'1980-12-17',800,NULL,20),
(7499,'Allen','Salesman',7698,'1981-02-20',1600,NULL,30),
(7521,'Ward','Salesman',7698,'1981-02-22',1250,300,30),
(7566,'Jones','Manager',7839,'1981-04-02',2975,500,20),
(7654,'Martin','Salesman',7698,'1981-09-28',1250,NULL,30),
(7698,'Blake','Manager',7839,'1981-05-01',2850,1400,30),
(7782,'Clark','Manager',7839,'1981-06-09',2450,NULL,10),
(7783,'Scott','Analyst',7566,'1982-12-09',3000,NULL,20),
(7839,'King','President',NULL,'1981-11-17',5000,0,10),
(7844,'Turner','Salesman',7698,'1981-09-08',1500,0,30),
(7876,'Adams','Clerk',7788,'1983-01-12',1100,NULL,20),
(7900,'James','Clerk',7698,'1981-12-03',950,NULL,30),
(7902,'Ford','Analyst',7566,'1981-12-04',3000,NULL,20),
(7934,'Miller','Clerk',7782,'1982-01-23',1300,NULL,10);

-- 3. Add company column


ALTER TABLE Employee ADD company VARCHAR(4);

-- 4. Employees not eligible for commission


SELECT ENAME
FROM Employee
WHERE COMM IS NULL OR COMM = 0;

-- 5. Names in lower and upper case


SELECT LOWER(ENAME), UPPER(ENAME)
FROM Employee;

-- 6. Name, salary and PF (10%)


SELECT ENAME, SAL, (SAL*0.10) AS PF
FROM Employee;

-- 7. Different designations
SELECT DISTINCT JOB FROM Employee;

-- 8. Employees not in dept 10,30,40


SELECT *
FROM Employee
WHERE DEPTNO NOT IN (10,30,40);

-- 9. Salary between 1000 and 2000


SELECT ENAME, SAL
FROM Employee
WHERE SAL BETWEEN 1000 AND 2000;
-- 10. Dept-wise stats (Dept 20)
SELECT DEPTNO,
SUM(SAL) AS Total_Salary,
MAX(SAL) AS Highest_Salary,
AVG(SAL) AS Avg_Salary
FROM Employee
WHERE DEPTNO = 20
GROUP BY DEPTNO
HAVING AVG(SAL) > 1500
ORDER BY Total_Salary DESC;

-- 11. Employees in each department (include empty)


SELECT [Link], [Link]
FROM Department D
LEFT JOIN Employee E
ON [Link] = [Link];

-- 12. Employee with same salary as FORD


SELECT ENAME
FROM Employee
WHERE SAL = (
SELECT SAL FROM Employee WHERE ENAME = 'Ford'
);

-- 13. Employees who joined before their manager


SELECT [Link]
FROM Employee E
JOIN Employee M
ON [Link] = [Link]
WHERE [Link] < [Link];

-- 14. Factorial of 5
SELECT 5*4*3*2*1 AS Factorial;
-- 15. Increase salary of Managers by 5%
UPDATE Employee
SET SAL = SAL * 1.05
WHERE JOB = 'Manager';
CASE STUDY 8
-- 1. Create Employee and Department Tables
CREATE TABLE Department (
DEPTNO INT PRIMARY KEY,
DNAME VARCHAR(20),
LOC VARCHAR(20)
);

CREATE TABLE Employee (


EMPNO INT PRIMARY KEY,
ENAME VARCHAR(20),
JOB VARCHAR(20),
MGR INT,
HIREDATE DATE,
SAL DECIMAL(9,2),
COMM DECIMAL(9,2),
DEPTNO INT,
FOREIGN KEY (DEPTNO) REFERENCES Department(DEPTNO)
);

-- 2. Insert Values
INSERT INTO Department VALUES
(10,'Accounting','New York'),
(20,'Research','Dallas'),
(30,'Sales','Chicago'),
(40,'Operations','Boston');

INSERT INTO Employee VALUES


(7369,'Smith','Clerk',7902,'1980-12-17',800,NULL,20),
(7499,'Allen','Salesman',7698,'1981-02-20',1600,NULL,30),
(7521,'Ward','Salesman',7698,'1981-02-22',1250,300,30),
(7566,'Jones','Manager',7839,'1981-04-02',2975,500,20),
(7654,'Martin','Salesman',7698,'1981-09-28',1250,NULL,30),
(7698,'Blake','Manager',7839,'1981-05-01',2850,1400,30),
(7782,'Clark','Manager',7839,'1981-06-09',2450,NULL,10),
(7783,'Scott','Analyst',7566,'1982-12-09',3000,NULL,20),
(7839,'King','President',NULL,'1981-11-17',5000,NULL,10),
(7844,'Turner','Salesman',7698,'1981-09-08',1500,0,30),
(7876,'Adams','Clerk',7788,'1983-01-12',1100,NULL,20),
(7900,'James','Clerk',7698,'1981-12-03',950,NULL,30),
(7902,'Ford','Analyst',7566,'1981-12-04',3000,NULL,20),
(7934,'Miller','Clerk',7782,'1982-01-23',1300,NULL,10),
(7777,'Xyz','Clerk',7782,'1983-01-23',578,NULL,50);

-- 3. Add Grade column


ALTER TABLE Employee ADD Grade INT;

-- 4. Clerk employees in dept 20


SELECT ENAME
FROM Employee
WHERE JOB = 'Clerk' AND DEPTNO = 20;

-- 5. Department name and length


SELECT DNAME, LENGTH(DNAME) AS Length_Name
FROM Department;

-- 6. Name, salary and HRA (20%)


SELECT ENAME, SAL, (SAL*0.20) AS HRA
FROM Employee;

-- 7. Different department numbers


SELECT DISTINCT DEPTNO FROM Employee;

-- 8. Names starting with 'M'


SELECT ENAME
FROM Employee
WHERE ENAME LIKE 'M%';

-- 9. Employees hired between 30-Jun-81 and 31-Dec-81


SELECT ENAME, HIREDATE
FROM Employee
WHERE HIREDATE BETWEEN '1981-06-30' AND '1981-12-31';
-- 10. Dept-wise stats (Dept 30)
SELECT DEPTNO,
SUM(SAL) AS Total_Salary,
MAX(SAL) AS Highest_Salary,
AVG(SAL) AS Avg_Salary
FROM Employee
WHERE DEPTNO = 30
GROUP BY DEPTNO
HAVING AVG(SAL) > 1000
ORDER BY Total_Salary ASC;

-- 11. Employees with no department match


SELECT [Link]
FROM Employee E
LEFT JOIN Department D
ON [Link] = [Link]
WHERE [Link] IS NULL;

-- 12. Employees in department of JONES


SELECT ENAME
FROM Employee
WHERE DEPTNO = (
SELECT DEPTNO FROM Employee WHERE ENAME = 'Jones'
);

-- 13. Employee and Manager name


SELECT [Link] AS Employee, [Link] AS Manager
FROM Employee E
LEFT JOIN Employee M
ON [Link] = [Link];
-- 14. Table of 5
SELECT 5 AS Num, 5*1 AS Val UNION
SELECT 5, 5*2 UNION
SELECT 5, 5*3 UNION
SELECT 5, 5*4 UNION
SELECT 5, 5*5;

-- 15. Add Rs 500 commission to all employees


UPDATE Employee
SET COMM = IFNULL(COMM,0) + 500;

You might also like