047、学习打卡230409

---

完全背包

• 518.零钱兑换II；

class Solution {
    public int change(int amount, int[] coins) {
        int[] dp = new int[amount + 1];
        dp[0] = 1;

        for (int i = 0; i < coins.length; i++) {
            for (int j = coins[i]; j <= amount; j++) {
                dp[j] += dp[j - coins[i]];
            }
        }

        return dp[amount];
    }
}

• 377. 组合总和 Ⅳ；

class Solution {
    public int combinationSum4(int[] nums, int target) {
        int[] dp = new int[target + 1];
        dp[0] = 1;

        for (int i = 0; i <= target; i++) {
            for (int j = 0; j < nums.length; j++) {
                if (i >= nums[j]) {
                    dp[i] += dp[i - nums[j]];
                }
            }
        }


        return dp[target];
    }
}

• 70. 爬楼梯；

class Solution {
    public int climbStairs(int n) {
        int[] dp = new int[n + 1];
        dp[0] = 1;
        dp[1] = 1;

        for (int i = 2; i <= n; i++) {
            for (int j = 1; j <= 2; j++) {
                dp[i] += dp[i - j];
            }
        }

        return dp[n];
    }
}

• 322. 零钱兑换；

class Solution {
    public int coinChange(int[] coins, int amount) {
        int[] dp = new int[amount + 1];
        Arrays.fill(dp, Integer.MAX_VALUE);
        dp[0] = 0;

        for (int i = 0; i < coins.length; i++) {
            for (int j = coins[i]; j <= amount; j++) {
                if (dp[j - coins[i]] != Integer.MAX_VALUE) {
                    dp[j] = Math.min(dp[j], dp[j - coins[i]] + 1);
                }
            }
        }

        return dp[amount] == Integer.MAX_VALUE ? -1 : dp[amount];
    }
}

SQL题目

---

• 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号

-- 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号
select s_id, s_score from score where c_id = '01';
select s_id, s_score from score where c_id = '02';

select a.s_id from 
                  (select s_id, s_score from score where c_id = '01') a
         inner join 
                      (select s_id, s_score from score where c_id = '02') b 
         on a.s_id = b.s_id
where a.s_score > b.s_score;

• 查询平均成绩大于60分的学生的学号和平均成绩

-- 查询平均成绩大于60分的学生的学号和平均成绩
select s_id,avg(s_score) from score group by s_id having avg(s_score) > 60;

• 查询所有学生的学号、姓名、选课数、总成绩

-- 查询所有学生的学号、姓名、选课数、总成绩
select student.s_id, s_name, count(c_id), sum(case when s_score is null then 0 else s_score end)
from student
         left join score s on student.s_id = s.s_id
group by student.s_id, s_name;

• 查询没学过张三老师课程的学生的学号和姓名;

-- 查询没学过张三老师课程的学生的学号和姓名;

select student.s_id
from student
         inner join score s on student.s_id = s.s_id
         inner join course c on s.c_id = c.c_id
         inner join teacher t on c.t_id = t.t_id
where t_name = '张三';

select s_id, s_name
from student
where s_id not in (select student.s_id
                   from student
                            inner join score s on student.s_id = s.s_id
                            inner join course c on s.c_id = c.c_id
                            inner join teacher t on c.t_id = t.t_id
                   where t_name = '张三');

---