Sql练习

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&#