深圳大学数据库系统实验一 PostgreSQL数据库SQL语句练习

一、创建表：

1、员工表：

create table EMP
(
    EMPNO    int unique primary key,
    ENAME    varchar(15) not null,
    JOB        varchar(15),
    MGR       int,
    HIREDATE  date,
    SAL        int check (SAL >= 0),
    COMM     int check (COMM > 0),
    DEPTNO    int
);

2、部门表：

create table DEPT
(
    deptno  int         primary key,
    dname  varchar(15)  not null,
    loc     varchar(15)  not null,
    primary key (deptno)
);

3、设置外键：为EMP表的deptno字段添加外键约束，关联DEPT表的主键deptno。

alter table emp add constraint fk_dept_no foreign key (deptno) references dept(deptno);

二、SQL练习题

1、?List all information about the departments

select * from dept;

2、List details of employees in departments 10 and 30.

select * from emp where deptno in(10, 30);

3、3.What are the names of the employees who earn less than ￡20,000?

select ename, sal from emp where sal + coalesce(comm, 0) < 20000;

4、Find all employees whose job is either Clerk or Salesman.

select * from emp where job in ('CLERK', 'SALESMAN');

5、Find everyone whose job is Salesman and all the Analysts in department 20.

select * from emp where job = 'SALESMAN' or (job = 'ANALYST' and deptno = 20);

6、Find the name of the President.

select ename from emp where job = 'PRESIDENT';

7、List the employees whose names have TH or LL in them

select * from emp where ename like '%TH%' or ename like '%LL%';

8、Find the name, job, salary, hiredate, and department number of all employees by alphabetical order of name.

select ename, job, sal, hiredate, deptno from emp order by ename;

9、List all salesmen in descending order by commission divided by their salary.

select *, coalesce(comm, 0)::float / sal::float from emp where job = 'SALESMAN' order by coalesce(comm, 0)::float / sal::float desc;

10、Find the names, jobs, salaries and commissions of all employees who do not have managers.

select ename, job, sal, comm from emp where mgr is null;