warehouse_db=# CREATE table student (number int primary key,name varchar (), age int);
CREATE TABLE
warehouse_db=# INSERT into student values (1, ' David ', 20);
INSERT 0 1
warehouse_db=# INSERT into student values (2, ' Luna ', 21);
INSERT 0 1
warehouse_db=# INSERT into student values (3, ' Lisa ', 21);
INSERT 0 1
warehouse_db=# INSERT into student values (4, ' Susan ', 22);
INSERT 0 1
warehouse_db=# INSERT into student values (5, ' Jack ', 22);
INSERT 0 1
warehouse_db=# INSERT into student values (6, ' Peter ', 23);
INSERT 0 1
warehouse_db=# INSERT into student values (7, ' Andrew ', 23);
INSERT 0 1
warehouse_db=# INSERT into student values (8, ' Stallman ', 23);
INSERT 0 1
warehouse_db=# select * from student;
number | name | Age
--------+----------+-----
1 | David | 20
2 | Luna | 21st
3 | Lisa | 21st
4 | Susan | 22
5 | Jack | 22
6 | Peter | 23
7 | Andrew | 23
8 | Stallman | 23
(8 rows)
Group queries
warehouse_db=# Select Age,count (*) from student group by age;
Age | Count
-----+-------
23 | 3
20 | 1
22 | 2
21 | 2
(4 rows)
warehouse_db=# SELECT * FROM student order by age;
number | name | Age
--------+----------+-----
1 | David | 20
2 | Luna | 21st
3 | Lisa | 21st
4 | Susan | 22
5 | Jack | 22
6 | Peter | 23
7 | Andrew | 23
8 | Stallman | 23
(8 rows)
warehouse_db=# SELECT * FROM student order BY age Desc;
number | name | Age
--------+----------+-----
8 | Stallman | 23
6 | Peter | 23
7 | Andrew | 23
5 | Jack | 22
4 | Susan | 22
3 | Lisa | 21st
2 | Luna | 21st
1 | David | 20
(8 rows)
warehouse_db=# SELECT * FROM student where age <= the order by age Desc;
number | name | Age
--------+-------+-----
4 | Susan | 22
5 | Jack | 22
2 | Luna | 21st
3 | Lisa | 21st
1 | David | 20
(5 rows)
warehouse_db=# CREATE TABLE Student_bak (number int primary key,name varchar (), age int);
CREATE TABLE
warehouse_db=# INSERT INTO Student_bak select * from student;
INSERT 0 8
warehouse_db=# select * from Student_bak;
number | name | Age
--------+----------+-----
1 | David | 20
2 | Luna | 21st
3 | Lisa | 21st
4 | Susan | 22
5 | Jack | 22
6 | Peter | 23
7 | Andrew | 23
8 | Stallman | 23
(8 rows)
PostgreSQL Simple Select (Group BY and insert INTO ... select)