1,每日经典sql:
1)根据三张关系表查询雇员中工资最高的雇员的员工姓名、工资和部门号。
salary(工资表) employee(员工表) department(部门表)
SELECT
emp.ename AS '姓名',
sal.sal AS '薪资',
dep.depname AS '部门'
FROM
employee AS emp
INNER JOIN salary AS sal
INNER JOIN department dep ON dep.salid = sal.salid
AND dep.empid = emp.empid
AND sal.sal = ( SELECT max( sal ) FROM salary )
2)找出各月倒数第3天受雇的所有员工:
SELECT
*
FROM
emp
WHERE --关键点:last_day()函数返回日期的最后一天
hiredate = last_day( hiredate ) -2
3)显示所有员工的姓名,用a替换所有"A"
SELECT REPLACE --replace函数 替换一些字符
( ename, 'A', 'a' ) name,
job,
hiredate
FROM
emp;
4)显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面:
SELECT
ename,
--t o_char函数是用来截取时间的 to_char ( hiredate, 'yyyy' ) YEAR,
to_char ( hiredate, 'mm' ) months
FROM
emp
ORDER BY
months,
YEAR ASC;
5)显示姓名字段的任何位置包含"A"的所有员工的姓名.
SELECT -- racle instr函数返回要截取的字符串在源字符串中的位置。只检索一次,也就是说从字符的开始到字符的结尾就结束。
ename
FROM
emp
WHERE
instr( ename, 'A', 1 ) > 0;
6)列出薪金比“SMITH”多的所有员工. --upper函数 小写字符转化成大写的函数:
SELECT
ename,
sal
FROM
emp
WHERE
sal > ( SELECT sal FROM emp WHERE upper( ename ) = 'SMITH' );
7)列出受雇日期早于其直接上级的所有员工:
SELECT
e.ename,
m.ename
FROM
emp e,
emp m
WHERE
e.mgr = m.empno
AND ( e.hiredate < m.hiredate );
2,50道经典的sql题:
建表sql语句:https://pan.baidu.com/s/1BTHg7at7eZbDpU4McrrFvQ 提取码:v2hz
1)查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
SELECT
sc.s_id AS '学生id',
sc.s_score AS '成绩'
FROM
( SELECT * FROM score WHERE c_id = "01" ) AS sc
INNER JOIN ( SELECT * FROM score WHERE c_id = "02" ) AS sco ON sc.s_id = sco.s_id
WHERE
sc.s_score > sco.s_score
2)查询同时存在" 01 “课程和” 02 "课程的情况
SELECT s.s_id AS '学生id' FROM score s
WHERE s.c_id="01" AND s.s_id IN(SELECT s1.s_id FROM score s1 WHERE s1.c_id ="02")
3)查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
SELECT
s.c_id AS '01课程id',
s.s_id AS '01课程的学生id',
sc.c_id AS '02课程id',
sc.s_id AS '02课程的学生id'
FROM
(SELECT * FROM score WHERE c_id = "01")AS s
LEFT JOIN
(SELECT * FROM score WHERE c_id = "02")AS sc ON s.s_id = sc.s_id
4)查询不存在" 01 “课程但存在” 02 "课程的情况
SELECT * FROM score s WHERE s.c_id = "02" AND s.s_id
NOT IN (SELECT s1.s_id FROM score s1 WHERE s1.c_id = "01&#