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&#
  • 1
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值