数据库习题

最新推荐文章于 2022-08-25 10:00:00 发布

黑化后的亚瑟

最新推荐文章于 2022-08-25 10:00:00 发布

阅读量818

点赞数

分类专栏：数据库总结文章标签：数据库

本文链接：https://blog.csdn.net/weixin_44025076/article/details/114749357

版权

数据库总结专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1.计算周内各日期的次数

CREATE TABLE week (
  dateno      INT PRIMARY KEY AUTO_INCREMENT,
  date_inyear DATETIME,
  weekDay     VARCHAR(30)
);


DROP PROCEDURE IF EXISTS insert_canlender;
DELIMITER $$
CREATE PROCEDURE insert_canlender(st DATETIME, ed DATETIME)
  BEGIN

    DECLARE dayofweek INT;
    DECLARE str VARCHAR(20);
    SET dayofweek = weekday(st);
    WHILE st <= ed DO
      CASE WHEN dayofweek = 0
        THEN SET str = 'MONDAY';
        WHEN dayofweek = 1
        THEN SET str = 'TUSEDAY';
        WHEN dayofweek = 2
        THEN SET str = 'WENSDAY';
        WHEN dayofweek = 3
        THEN SET str = 'THURSDAY';
        WHEN dayofweek = 4
        THEN SET str = 'FRIDAY';
        WHEN dayofweek = 5
        THEN SET str = 'SATURDAY';
        WHEN dayofweek = 6
        THEN SET str = 'SUNDAY';
      END CASE;
      INSERT INTO week (date_inyear, weekDay) VALUES (st, str);
      SET st = (st + INTERVAL 1 DAY);
      SET dayofweek = (dayofweek + 1) MOD 7;

    END WHILE;
  END $$

CALL insert_canlender('2021-01-01', now());

select weekDay,count(1) cnt
  from week
GROUP BY weekDay;

2.所有被A和B讨论但不被A和C讨论的话题

DROP TABLE IF EXISTS discuss;
CREATE TABLE  discuss(u1 VARCHAR(3),u2 VARCHAR(3),topic int);
INSERT INTO discuss VALUES('A','B',1);
INSERT INTO discuss VALUES('A','B',2);
INSERT INTO discuss VALUES('A','B',3);
INSERT INTO discuss VALUES('A','C',3);

SELECT topic
  FROM discuss
where topic in
(SELECT topic
  FROM discuss
WHERE u1='A' and u2='B'  
UNION
SELECT topic
FROM discuss
WHERE u1='B' and u2='A')
and topic not in
(
  SELECT topic
  FROM discuss
  WHERE u1='A' and u2='C'
  UNION
  SELECT topic
  FROM discuss
  WHERE u1='C' and u2='A'
)

3.EMP,DEPT

职员表EMP（eno，ename，job，hire，salary，dno）eno是员工编号，dno是部门编号，hire是雇佣日期就是进公司的日期,部门表DEPT（dno，dname，loc）

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
CREATE TABLE EMP (
  eno    INT,
  ename  VARCHAR(20),
  salary INT,
  dno    INT
);
CREATE TABLE DEPT (
  dno   INT,
  dname VARCHAR(20)
);
TRUNCATE EMP;
TRUNCATE DEPT;
INSERT INTO EMP (ename, salary, dno) VALUES ('a', 1, 1);
INSERT INTO EMP (ename, salary, dno) VALUES ('b', 4, 1);
INSERT INTO EMP (ename, salary, dno) VALUES ('c', 6, 1);
INSERT INTO EMP (ename, salary, dno) VALUES ('d', 12, 1);
INSERT INTO EMP (ename, salary, dno) VALUES ('e', 3, 1);
INSERT INTO EMP (ename, salary, dno) VALUES ('e', 22, 1);
INSERT INTO EMP (ename, salary, dno) VALUES ('gg', 25, 2);
INSERT INTO DEPT (dno, dname) VALUES (1, 'RI');
INSERT INTO DEPT (dno, dname) VALUES (2, 'RB');

dname=RI的员工工资的中间数。

中间数就是若有九个员工，将员工工资从大到小排列，中间数就是第五个，若有十个就是从大到小排列的第五和第六个数的平均数。

SELECT avg(salary)
FROM
  ( #对该部门的工资排序，加入rk字段
     SELECT
     salary,
     @rank := @rank + 1 rk
   FROM
     (SELECT salary
      FROM EMP
      WHERE dno = (SELECT dno
                   FROM DEPT
                   WHERE dname = 'RI')
      ORDER BY salary) t, (SELECT @rank := 0) init
  ) l1,
  (
     #该部门的人数
    SELECT count(*) cnt
    FROM EMP
    WHERE dno = (SELECT dno
                 FROM DEPT
                 WHERE dname = 'RI')) l2
WHERE rk = FLOOR((cnt + 1) / 2) OR rk = FLOOR((cnt + 2) / 2);

日期差

每个部门第一个进部门和最后一个进部门的员工的时间上的时间差换算成年是差几年，换算成月又是差几个月。

SELECT dno,timestampdiff(MONTH,m2,m1)
 from
(SELECT date_format(max(hire),'%Y-%m-%d') m1,date_format(min(hire),'%Y-%m-%d') m2,dno
FROM EMP
group by dno) t;
/*
%Y	年，4 位		
%y	年，2 位
%M	月名
%m	月，数值(00-12)
*/

4 同时工作于“硬件”和“软件”两个部门的每个雇员的名字和年龄

Emp(eid, ename, age, salary)

Works(eid, did, pct_time)

Dept(did,dname , budget, managerid)

drop table if exists Emp;
drop table if exists Works;
drop table if exists Dept;
create table Emp(eid int, ename  varchar(20), age int, salary int);
create table Works(eid int, pct_time  datetime, did int);
create table Dept(did int,budget int,managerid int,dname varchar(20));

insert into Dept(did,dname) values(1,'ying');
insert into Dept(did,dname) values(2,'ruan');
insert into Dept(did,dname) values(3,'a');
insert into Emp(eid,ename,age) values(1,'ea',11);
insert into Emp(eid,ename,age) values(2,'bb',12);
insert into Emp(eid,ename,age) values(3,'cc',13);
insert into Works(eid,did) values(1,1);
insert into Works(eid,did) values(1,2);
insert into Works(eid,did) values(1,3);
insert into Works(eid,did) values(2,1);
insert into Works(eid,did) values(2,2);
insert into Works(eid,did) values(3,1);

select ename,age
from Emp where eid in(
    select w1.eid
    from Works w1,Works w2
    where w1.eid=w2.eid and w1.did=(select did from Dept where dname='ying')
    and w2.did=(select did from Dept where dname='ruan')
    );

5.

某商场部分关系模式如下

店铺表store(store_id, store_name, store_ floor, room_no)

商品表commodity(com_id, com_name, com_price, store_id)

销售员表saler(saler_id, saler_name, saler__gender, saler__age, store_id)

流水账单表 bill(bill_id, saler_id, com_id, purchase_quantity, total_price, bill_time)

其中,店铺表记录了该商场入驻的店铺信息,商品为每个店铺中销售的商品信息,销售员为店铺中的工作人员,流水账单表为为商场收银台交易的纪录, bill time交易发生的时间,为 datetime类型,每个流水账单只包含了一种商品,不同类商品为不同的账单,purchase_ quantity数量为正整数

drop table if exists store;
drop table if exists commodit;
drop table if exists saler;
drop table if exists bill;

create table store(store_id int, store_name varchar(20), store_floor int, room_no int);
create table commodit(com_id int, com_name varchar(20), com_price int, store_id int);
create table saler(saler_id int, saler_name varchar(20), saler_gender int, saler_age int,
 store_id int);
create table bill(bill_id int, saler_id int, com_id int, purchase_quantity int, total_price int, bill_time datetime);

(1)在生成账单记录时, total_price是由传入的 com_ id和 purchase_quantity查询到相关的单价以及数量计算出来的,请完成这样一条插入

drop procedure if exists m_insert;
DELIMITER $$
create PROCEDURE m_insert(in cd int,in q int)
begin
    insert into bill(com_id,purchase_quantity,total_price,bill_time)
    select cd,q,com_price*q,now()
    from commodit
    where com_id=cd;
end $$

call m_insert(1,20);

(2)A店铺的老板想要了解一下店铺员工每天的销售业绩,请查询 store_id为"MCSA"的店铺里的所有员工在2021年3月4日的完成的交易笔数,以及每个员工完成的总交易额

select count(total_price) as dealCount, sum(total_price) as sum
from saler s
left join bill b on s.saler_id = b.saler_id
where store_id = 'MCSA' 
and 
date_format(b.bill_time,'%Y-%m-%d')='2021-03-14'

商场的管理人员想了解2021年3月14日哪个店铺的交易额最大,查询其店铺名称，店铺所在楼层,店铺编号以及当天的交易额。

select  @max_value:=max(ss)
from
    (select store.store_name,store_floor,store.store_id, sum(ifnull(total_price, 0)) ss
     from store
              left join saler on store.store_id = saler.store_id
              left join (
         select *
         from bill
         where date_format(bill.bill_time, '%Y-%m-%d') = '2021-03-14'
     ) mbill on saler.saler_id = mbill.saler_id
     group by store.store_id
    ) tmp;


select  store_id,store_name,store_floor,ss
from
(select store.store_name,store_floor,store.store_id, sum(ifnull(total_price, 0)) ss
 from store
          left join saler on store.store_id = saler.store_id
          left join (
     select *
     from bill
     where date_format(bill.bill_time, '%Y-%m-%d') = '2021-03-14'
 ) mbill on saler.saler_id = mbill.saler_id
 group by store.store_id
) tmp
where ss=@max_value;

6.

教师表teacher(teacher_id, teacher_name, teacher_email, teacher_gender, teacher_title)

课程表course(course_id, course_name, teacher_id, term, year)

学生表student(student_id, student_name, student_grade, student_gender)

选课情况course_election(ce_id, course_id, student_id, marks)

为了方便查询,希望创建一个课程视图,包括2015年第一学期的所有课程,包含课程表的所有字段,以及每门课的总选修人数。若没人选修,则选修人数为0

create view  class_choose as
select course.*,count(course_election.student_id) cnt
from course left join  course_election
on course.course_id=course_election.course_id
group by course.course_id;

(3)教务处现在进行了一些修改,当有不及格的成绩录入时,需要对成绩进行规范处理。对于低于60分的成绩按照60分算,使用触发器完成相应的要求

create trigger insert_grade_trigger
    before insert on course_election
    for each row
    begin
        if new.marks<60 then
           set new.marks=60;
        end if;
    end;

在每一个部门中查询其年薪收入最高的职工,结果返回部门编号和该部门中年薪收入最高职工的工号。

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	)
;

7. 船员租赁船只的系统

Sailors(sid, sname, rating, age);

Boats(bid, bname, color);

Reaserves(sid, bid, day)

create table 
Sailors(sid int, sname varchar(20), rating double, age int);

create table 
Boats(bid int, bname varchar(20), color varchar(10));

create table
Reaserves(sid int, bid int, day datetime);

找出年龄在35岁以上，rating>5的，一个月内，同时预定了红色船和绿色船的水手姓名

select sname
from Sailors
where age>35 and sid in(
          select r1.sid
          from Reaserves r1 join Reaserves r2
          on r1.sid=r2.sid
          where r1.bid=(select bid from  Boats where color='red')
            and r2.bid=(select bid from Boats where color='green')
      );

3)找出预定了所有船的水手的名字

select s1.sname
from Sailors s1
where not exists(
        select *
        from Boats b
        where not exists(
                select*
                from Reaserves r
                where r.sid = s1.sid
                  and r.bid = b.bid
            )
)

8.社交应用

假设: Circo是一款面向于大学生的群组类的社交应用,致力于让同学们能够体验到校园生活更加有趣有活力的一面,营造和谐自然的校园氛围。在这里,你可以关注与自己志同道合的小伙伴(User),发布实时动态( Moment),创建或者加入各种各样有趣的兴趣圈子( Group),在圈子内Po上自己的美丽心情(red),参与各种话题( Topic)的讨论

其中涉及到一些实体数据表和属性,包括

create TABLE Topic(id int,name varchar(20),heat int,createdAt datetime,updated datetime );

create TABLE  TopicRelation(topica int,momentId int);

create TABLE Moment(id int,authorId int,content vasqlrchar(20),createAt datetime);

用户User 编号_id,昵称 nickname,头像 avatar,学校编号 schooled,创建时间 createdAt

学校 School 编号_id,学校名name

新鲜事 Moment 编号_id,发布者编号 author,文字内容 content,图片 Image,创建时间createdAt

新鲜事的点赞LikingMoment 用户编号 userid,新鲜事编号 momentId,创建时间 createdAt

话题 Topic 编号id,话题名称name,热度heat,创建时间 createdAt,更新时间 updated

话题关系表TopicRelation 话题编号 topica, moment编号 momentId

1.查询热度前十的话题（话题id，名称，热度，moment数量，时间）

#用变量标识排名，防止出现热度相同的情况
select tid,name,heat,mcnt
from
(select l1.*, if(@pre != -1 and heat < @pre, @rk := @rk + 1, @rk) id, @pre := heat
 from (select t.id tid, count(momentId) mcnt, heat,name
       from Topic t
                left join TopicRelation tr on t.id = tr.topica
       group by t.id
       order by heat desc) l1,
      (select @rk := 1, @pre := 0
      ) init
) list
where id<=10;

2.查询该话题下的moment详情，以及我是否点赞

#输入用户的id和话题的id

delimiter $$
create PROCEDURE  find(uid_in int,topic_in int)
begin
    select m1.id,m1.createAt,if(pp is not null,1,0) zan
    from moment m1 left join (
        select likeMoment.mid,likeMoment.uid pp
        from likeMoment
        where uid=uid_in
    )sub_list on sub_list.mid=m1.id
    where m1.id in
          (select m.id
           from topicrelation t1 join moment m on t1.momentId=m.id
           where t1.topica= topic_in
           order by  m.createAt desc)
    limit 20;
end$$