练习1 各部门工资最高的员工
CREATETABLEEmployee(
IdINTEGERNOT NULL,
NameVARCHAR(50),
SalaryINTEGERNOT NULL,
DepartmentIdINTEGERNOT NULL,
PRIMARY KEY(Id)
);
START TRANSACTION;
INSERT INTOEmployeeVALUES(1,'Joe',70000,1);
INSERT INTOEmployeeVALUES(2,'Henry',80000,2);
INSERT INTOEmployeeVALUES(3,'Sam',60000,2);
INSERT INTOEmployeeVALUES(4,'Max',90000,1);
COMMIT;
CREATETABLEDepartment(
IdINTEGERNOT NULL,
NameVARCHAR(50)NOT NULL,
PRIMARY KEY(Id)
);
START TRANSACTION;
INSERT INTODepartmentVALUES(1,'IT');
INSERT INTODepartmentVALUES(2,'SALES');
COMMIT;
SELECTF.NameASDepartment,E.NameASEmployee,F.MaxSalaryASSalary
FROMEmployeeASE
INNER JOIN(
SELECTD.Id,D.Name,X.MaxSalary
FROMDepartmentASD
INNER JOIN(
SELECTDepartmentId,MAX(Salary)ASMaxSalary
FROMEmployee
GROUP BYDepartmentId
)ASXonD.Id=X.DepartmentId
)ASFonE.DepartmentId=F.Id
WHEREE.Salary=F.MaxSalary;
练习2 换座位
CREATETABLEseat(
idINTEGERNOT NULL,
studentVARCHAR(50),
PRIMARY KEY(id)
);
START TRANSACTION;
INSERT INTOseatVALUES(1,'Abbot');
INSERT INTOseatVALUES(2,'Doirs');
INSERT INTOseatVALUES(3,'Emerson');
INSERT INTOseatVALUES(4,'Green');
INSERT INTOseatVALUES(5,'Jeames');
COMMIT;
练习3 分数排名
CREATETABLEScores(
IdINTEGERNOT NULL,
Score FLOATNOT NULL,
PRIMARY KEY(Id)
);
START TRANSACTION;
INSERT INTOScoresVALUES(1,3.50);
INSERT INTOScoresVALUES(2,3.65);
INSERT INTOScoresVALUES(3,4.00);
INSERT INTOScoresVALUES(4,3.85);
INSERT INTOScoresVALUES(5,4.00);
INSERT INTOScoresVALUES(6,3.65);
COMMIT;
SELECTScore,
DENSE_RANK() OVER (ORDER BYScoreDESC)AS'Rank'
FROMScores;
练习4 连续出现的数字
CREATETABLELogs(
IdINTEGERNOT NULL,
NumINTEGERNOT NULL,
PRIMARY KEY(Id)
);
START TRANSACTION;
INSERT INTOLogsVALUES(1,1);
INSERT INTOLogsVALUES(2,1);
INSERT INTOLogsVALUES(3,1);
INSERT INTOLogsVALUES(4,2);
INSERT INTOLogsVALUES(5,1);
INSERT INTOLogsVALUES(6,2);
INSERT INTOLogsVALUES(7,2);
COMMIT;
练习5 树节点
CREATETABLEtree(
idINTEGERNOT NULL,
p_idINTEGER,
PRIMARY KEY(id)
);
START TRANSACTION;
INSERT INTOtreeVALUES(1,NULL);
INSERT INTOtreeVALUES(2,1);
INSERT INTOtreeVALUES(3,1);
INSERT INTOtreeVALUES(4,2);
INSERT INTOtreeVALUES(5,2);
COMMIT;
练习6 至少有五名直接下属的经理
CREATETABLEEmployee6(
IdINTEGERNOT NULL,
NameVARCHAR(50)NOT NULL,
DepartmentCHAR(1),
ManagerIdINTEGER,
PRIMARY KEY(Id)
);
START TRANSACTION;
INSERT INTOEmployee6VALUES(101,'John','A',NULL);
INSERT INTOEmployee6VALUES(102,'Dan','A',101);
INSERT INTOEmployee6VALUES(103,'James','A',101);
INSERT INTOEmployee6VALUES(104,'Amy','A',101);
INSERT INTOEmployee6VALUES(105,'Anne','A',101);
INSERT INTOEmployee6VALUES(106,'Ron','B',101);
COMMIT;
SELECTE.Name
FROMEmployee6ASE
INNER JOIN(
SELECTManagerId,COUNT(ManagerId)ASCount
FROMEmployee6
GROUP BYManagerId
)ASGonE.Id=G.ManagerId
WHEREG.Count>=5;
练习7 分数排名
SELECTScore,
RANK() OVER (ORDER BYScoreDESC)AS'Rank'
FROMScores;
练习8 查询回答率最高的问题
CREATETABLE`question`(
`uid`INTEGER,
`action`varchar(10),
`question_id`INTEGER,
`answer_id`INTEGER,
`q_num`INTEGER,
`timestamp`INTEGER);
INSERT INTO`question`
(`uid`,`action`,`question_id`,`answer_id`,`q_num`,`timestamp`)
VALUES
(5,'show',285,null,1,123),
(5,'answer',285,124124,1,124),
(5,'show',369,null,2,125),
(5,'skip',369,null,2,126);
SELECT*FROMQUESTION;
练习9 各部门前3高工资的员工
INSERT INTOEmployeeVALUES(5,'Janet',69000,1);
INSERT INTOEmployeeVALUES(6,'Randy',85000,1);
SELECTT.Department,T.Employee,T.Salary
FROM(SELECTD.Name AS 'Department',E.Name AS 'Employee',E.Salary AS 'Salary',
RANK() OVER (PARTITION BYE.DepartmentId ORDER BYE.SalaryDESC) AS ranking FROM Employee AS EINNER JOIN Department AS D ON E.DepartmentId=D.Id) AS T
WHERET.ranking<=3;
