Essential PostgreSQL 50 Selected Practice Questions with Answers

Review all blog information to know more about databases and our products.

Essential PostgreSQL 50 Selected Practice Questions with Answers

Environment Setup（PGsql）

t_course Table

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE public.t_course (
    cid varchar(10) NOT NULL,
    cname varchar(10) NOT NULL,
    tid varchar(10) NOT NULL,
    CONSTRAINT t_course_pkey PRIMARY KEY (cid)
);

INSERT INTO public.t_course (cid, cname, tid) VALUES
('01', 'Chinese', '02'),
('02', 'Math', '01'),
('03', 'English', '03');

t_score Table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE TABLE public.t_score (
    sid varchar(10) NOT NULL,
    cid varchar(10) NOT NULL,
    score numeric(18, 1) NOT NULL,
    CONSTRAINT t_score_pkey PRIMARY KEY (sid, cid)
);

INSERT INTO public.t_score (sid, cid, score) VALUES
('01', '02', 90.0),
('01', '01', 80.0),
('01', '03', 99.0),
('02', '02', 60.0),
('02', '01', 70.0),
('02', '03', 80.0),
('03', '01', 80.0),
('03', '02', 80.0),
('03', '03', 80.0),
('04', '01', 50.0),
('04', '02', 30.0),
('04', '03', 20.0),
('05', '01', 76.0),
('05', '02', 87.0),
('06', '01', 31.0),
('06', '03', 34.0),
('07', '02', 90.0),
('07', '03', 98.0);

t_student Table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE public.t_student (
    sid varchar(6) NOT NULL,
    sname varchar(10) NOT NULL,
    sage timestamp NULL,
    ssex varchar(10) NOT NULL,
    CONSTRAINT t_student_pkey PRIMARY KEY (sid),
    CONSTRAINT t_student_ssex_check CHECK (((ssex)::text = 'Male'::text) OR ((ssex)::text = 'Female'::text))
);

INSERT INTO public.t_student (sid, sname, sage, ssex) VALUES
('01', 'Zhao Lei', '1990-05-18 00:00:00', 'Male'),
('02', 'Qian Dian', '1990-05-24 00:00:00', 'Male'),
('03', 'Sun Feng', '1990-05-20 00:00:00', 'Male'),
('04', 'Li Yun', '1990-05-25 00:00:00', 'Male'),
('05', 'Zhou Mei', '1991-12-01 00:00:00', 'Female'),
('06', 'Wu Lan', '1992-01-01 00:00:00', 'Female'),
('07', 'Zheng Zhu', '1989-10-31 00:00:00', 'Female'),
('08', 'Zhang San', '2017-12-20 00:00:00', 'Female'),
('09', 'Li Si', '2017-12-25 00:00:00', 'Female'),
('10', 'Wang Wu', '2021-09-14 00:00:00', 'Female'),
('11', 'Zhao Liu', '2013-09-13 00:00:00', 'Female'),
('12', 'Sun Qi', '2014-10-01 00:00:00', 'Female');

t_teacher Table

1
2
3
4
5
6
7
8
9
10
CREATE TABLE public.t_teacher (
    tid varchar(10) NOT NULL,
    tname varchar(10) NOT NULL,
    CONSTRAINT t_teacher_pkey PRIMARY KEY (tid)
);

INSERT INTO public.t_teacher (tid, tname) VALUES
('01', 'Zhang San'),
('02', 'Li Si'),
('03', 'Wang Wu');

be careful

MySQL can use single quotes (‘) or double quotes (“) to represent values, but PG can only use single quotes (‘) to represent values.

PG’s double quotes (“) are used to represent system identifiers, such as table names or field names. MySQL can use backticks (`) to represent system identifiers, such as table names and field names, which are not supported in PG.

MySQL can use single quotes (‘) or double quotes (“) to represent values, but PG can only use single quotes (‘) to represent values.

PG’s double quotes (“) are used to represent system identifiers, such as table names or field names. MySQL can use backticks (`) to represent system identifiers, such as table names and field names, which are not supported in PG.

Practice Starts

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
### Environment Setup

#### t_course Table
```sql
CREATE TABLE public.t_course (
    cid varchar(10) NOT NULL,
    cname varchar(10) NOT NULL,
    tid varchar(10) NOT NULL,
    CONSTRAINT t_course_pkey PRIMARY KEY (cid)
);

INSERT INTO public.t_course (cid, cname, tid) VALUES
('01', 'Chinese', '02'),
('02', 'Math', '01'),
('03', 'English', '03');

t_score Table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE TABLE public.t_score (
    sid varchar(10) NOT NULL,
    cid varchar(10) NOT NULL,
    score numeric(18, 1) NOT NULL,
    CONSTRAINT t_score_pkey PRIMARY KEY (sid, cid)
);

INSERT INTO public.t_score (sid, cid, score) VALUES
('01', '02', 90.0),
('01', '01', 80.0),
('01', '03', 99.0),
('02', '02', 60.0),
('02', '01', 70.0),
('02', '03', 80.0),
('03', '01', 80.0),
('03', '02', 80.0),
('03', '03', 80.0),
('04', '01', 50.0),
('04', '02', 30.0),
('04', '03', 20.0),
('05', '01', 76.0),
('05', '02', 87.0),
('06', '01', 31.0),
('06', '03', 34.0),
('07', '02', 90.0),
('07', '03', 98.0);

t_student Table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE public.t_student (
    sid varchar(6) NOT NULL,
    sname varchar(10) NOT NULL,
    sage timestamp NULL,
    ssex varchar(10) NOT NULL,
    CONSTRAINT t_student_pkey PRIMARY KEY (sid),
    CONSTRAINT t_student_ssex_check CHECK (((ssex)::text = 'Male'::text) OR ((ssex)::text = 'Female'::text))
);

INSERT INTO public.t_student (sid, sname, sage, ssex) VALUES
('01', 'Zhao Lei', '1990-05-18 00:00:00', 'Male'),
('02', 'Qian Dian', '1990-05-24 00:00:00', 'Male'),
('03', 'Sun Feng', '1990-05-20 00:00:00', 'Male'),
('04', 'Li Yun', '1990-05-25 00:00:00', 'Male'),
('05', 'Zhou Mei', '1991-12-01 00:00:00', 'Female'),
('06', 'Wu Lan', '1992-01-01 00:00:00', 'Female'),
('07', 'Zheng Zhu', '1989-10-31 00:00:00', 'Female'),
('08', 'Zhang San', '2017-12-20 00:00:00', 'Female'),
('09', 'Li Si', '2017-12-25 00:00:00', 'Female'),
('10', 'Wang Wu', '2021-09-14 00:00:00', 'Female'),
('11', 'Zhao Liu', '2013-09-13 00:00:00', 'Female'),
('12', 'Sun Qi', '2014-10-01 00:00:00', 'Female');

t_teacher Table

1
2
3
4
5
6
7
8
9
10
CREATE TABLE public.t_teacher (
    tid varchar(10) NOT NULL,
    tname varchar(10) NOT NULL,
    CONSTRAINT t_teacher_pkey PRIMARY KEY (tid)
);

INSERT INTO public.t_teacher (tid, tname) VALUES
('01', 'Zhang San'),
('02', 'Li Si'),
('03', 'Wang Wu');

Practice Starts

  1. Query the information of students whose score of the course “01” is higher than that of the course “02” and the course scores 

    1
    2
    3
    4
    5
    6
    7
    with data_01 as (select sid, score from t_score ts1 where cid = '01'),  
    data_02 as (select sid, score from t_score ts2 where cid = '02')  
    select ts.*, d1.score from t_student ts, data_01 d1, data_02 d2  
    where  
    d1.sid = d2.sid  
    and d1.score > d2.score  
    and ts.sid = d1.sid;

  2. Query the situation where both the course “01” and the course “02” exist 

    1
    2
    3
    4
    5
    6
    with data_01 as (select sid, score from t_score ts1 where cid = '01'),  
    data_02 as (select sid, score from t_score ts2 where cid = '02')  
    select ts.* from t_student ts, data_01 d1, data_02 d2  
    where  
    d1.sid = d2.sid  
    and ts.sid = d1.sid;

  3. Query the situation where the course “01” exists, but the course “02” may not exist (display as null if it does not exist) 

    1
    2
    3
    4
    5
    6
    7
    8
    with data_01 as (select sid, score from t_score ts1 where cid = '01'),  
    data_02 as (select sid, score from t_score ts2 where cid = '02')  
    select  
    d1.sid as sid,  
    d1.score as "01",  
    d2.score as "02"  
    from  
    data_01 as d1 left join data_02 as d2 on d1.sid = d2.sid;

  4. Query the situation where the course “01” does not exist but the course “02” exists 

    1
    2
    3
    4
    select ts.sid, ts.cid, ts.score from t_score ts  
    where  
    cid = '02'  
    and sid not in (select sid from t_score ts1 where cid = '01')

  5. Query the student ID, student name and average score of students whose average score is greater than or equal to 60 points 

    1
    2
    3
    4
    5
    6
    select st.*, sid_avg.avger as "avg" from  
    t_student st,  
    (select ts.sid, round(sum(score)/3,1) as avger from t_score ts group by ts.sid) sid_avg  
    where  
    st.sid = sid_avg.sid and sid_avg.avger >= 60  
    order by "avg" asc;

  6. Query the student information where there are grades in the SC table. 

    1
    2
    select * from t_student ts  
    where sid in (select distinct sid from t_score ts)

  7. Query the student ID, student name, total number of selected courses and the sum of grades of all courses of all students 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    select  
    ts2.*, lo.num, lo.avger  
    from  
    t_student ts2  
    left join  
    (select ts.sid, count(1) as num, round(sum(score)/3,1) as avger  
    from t_score ts  
    group by ts.sid  
    ) lo  
    on  
    ts2.sid = lo.sid;

  8. Display students who have not selected courses (display as NULL) 

    1
    select * from t_student ts where ts.sid not in (select sid from t_score ts group by sid order by sid)

  9. Query the student information with grades 

    1
    2
    3
    4
    select *  
    from t_student  
    where  
    sid not in(select distinct sid from t_score)

    Refer to the table (small table in large table exists)

  10. Query the number of teachers whose surname is “Li” 

    1
    select count(*) from (select tid from t_teacher where tname like 'Li%') as tech;

  11. Query the information of students who have taken the courses taught by the teacher “Zhang San”
    – Get the teacher tid
    – Get the relevant cid through tid
    – Traverse t_score to get sid through cid
    – Get all information through sid

1
2
3
4
5
with cid_col as (select cid from t_course tc where tc.tid in  
(select tid from t_teacher where tname = 'Zhang San'))  
select * from t_student ts  
inner join (select sid from t_score where t_score.cid in (select cid from cid_col)) as sid_col  
on sid_col.sid = ts.sid

  1. Query the information of students who have not taken all courses
    – Get the number of courses
    – Get the course selection table and select the list of those who have not selected all
    – Query student information through the list

    1
    2
    3
    4
    5
    6
    select ts2.* from t_student ts2  
    inner join  
    ( select sid from t_score ts group by sid  
    having count(cid) < (select count(*) from t_course)  
    ) res_col  
    on res_col.sid = ts2.sid ;

  2. Query the information of students who have at least one course in common with the student whose student ID is “01” ()
    – First check the course set cid_col_01 of student 01
    – Check for other students to see if they are in this set

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    select distinct t_student.sid, t_student.sname, t_student.sage, t_student.ssex from t_student inner join  
    (  
    select t_score.sid from t_score  
    where t_score.cid in (select cid from t_score where sid = '01')  
    ) sid_col  
    on sid_col.sid = t_student.sid;  
    ```  

    15.  Query the student names of students who have not taken any courses taught by the teacher "Zhang San"  
        – First check all courses of Teacher Zhang San  
        – First check the teacher tid  
        – Check the cid through tid  
        – Find all those who have taken courses through the grade table  
        – Exclude those who have taken courses
      
    ```sql
    with cid_col as  
    (select cid from t_course where tid in (select tid from t_teacher where tname = 'Zhang San'))  
    select * from t_student  
    where sid not in(  
    select sid from t_score where cid in (select cid from cid_col)  
    );

  3. Query the student ID, student name and average score of students who have two or more failed courses

1
2
3
4
select t_student.sid, t_student.sname, avg(score) from t_student  
inner join t_score  
on t_student.sid = t_score.sid  
group by t_student.sid having count(score < 60 or score = null) >= 2  
1
2
3
4
select t_student.sid, t_student.sname, avg(score) from t_student  
inner join t_score  
on t_student.sid = t_score.sid where t_score.score < 60  
group by t_student.sid having count(*) >= 2
  1. Retrieve the student information whose score of the course “01” is less than 60 and sorted in descending order of the score
    – Find the cid of the course “01” in t_course
    – Find the sid through the cid in t_score
1
2
3
4
5
6
7
8
9
10
select ts.*, cid_score_lo.score as score from t_student ts  
inner join  
(select sid, score from t_score ts2 where cid  
in (select cid from t_course where cid = '01')  
)cid_score_lo  
on  
cid_score_lo.sid = ts.sid  
and  
cid_score_lo.score < 60  
order by score desc

  1. Display all students’ grades of all courses and average grades in descending order of average grades 

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT sid,  
    MAX(CASE WHEN cid='01' THEN score ELSE 0 END) "Chinese",  
    MAX(CASE WHEN cid='02' THEN score ELSE 0 END) "Mathematics",  
    MAX(CASE WHEN cid='03' THEN score ELSE 0 END) "English",  
    round(sum(score)/3,1) "Average Grade"  
    FROM t_score  
    GROUP BY sid  
    ORDER BY "Average Grade" DESC

  2. Query the highest score, lowest score, average score of each course and the pass rate, medium rate, good rate, excellent rate of each course.
    The pass is >= 60, the medium is: 70 - 80, the good is: 80 - 90, the excellent is: >= 90.
    The output is required to be the course ID and the number of students taking the course. The query results are sorted in descending order of the number of students. If the number of students is the same, they are sorted in ascending order of the course ID.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select  
ts.cid,  
max(tc.cname) "Name",  
max(ts.score) "Highest Score",  
min(ts.score) "Lowest Score",  
round(sum(ts.score) / count(ts.sid),1) "Average Score",  
sum(case when ts.score >= 60 then 1 else 0 end) "Pass Rate",  
sum(case when ts.score >= 70 and ts.score <80 then 1 else 0 end) "Medium Rate",  
sum(case when ts.score >= 80 and ts.score <90 then 1 else 0 end) "Good Rate",  
sum(case when ts.score >= 90 then 1 else 0 end) "Excellent Rate",  
count(sid) "Number of Students"  
from t_score ts  
left join t_course tc  
on ts.cid = tc.cid  
group by ts.cid  
order by "Number of Students", cid;

  1. Sort by each course grade and display the ranking. When the Score is repeated, keep the ranking blank 

    1
    2
    select cid, sid, score, (select count(*) from t_score where cid = ts.cid and score > ts.score) + 1 rank  
    from t_score ts order by cid, score desc;

  2. Query the total score of students and rank them. When the total score is repeated, do not keep the ranking blank 

    1
    2
    select sid, sum(score), dense_rank() over(order by sum(score) desc) from  
    t_score group by sid;

  3. Count the number of people in each score range of each course: course ID, course name, [100 - 85], (85 - 70], (70 - 60], (60 - 0] and the percentage 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    select 
    	ts.cid,
    	max(tc.cname),
    	sum(case when score >= 85 then 1 else 0 end) "[100-85]",
    	sum(case when score >= 70 and score < 85 then 1 else 0 end) "(85-70]",
    	sum(case when score >= 60 and score < 70 then 1 else 0 end) "(70-60]",
    	sum(case when score < 60 then 1 else 0 end) "(60-0]"
    from t_score ts 
    left join t_course tc 
    on ts.cid = tc.cid
    group by ts.cid

  4. Query for Students Who Have Not Taken All Courses 

    1
    2
    3
    4
    5
    6
    with course_num as(select count(*) from t_course)
    	,sid_col as (select distinct sid from t_score)
    	,extra_col as (select distinct sid from t_score group by sid having count(sid) = (select * from course_num) )
    select * from t_student ts where sid not in (select * from sid_col)
    or sid not in (select * from extra_col)
    order by sid

  5. Query the number of students selected for each course 

    1
    select ts.cid, max(tc.cname), count(ts.sid) from t_score ts left join t_course tc on ts.cid = tc.cid group by ts.cid ;

  6. Query the student ID and name of students who have selected only two courses 

    1
    2
    3
    4
    5
    6
    7
    8
    select  
    ts2.sid,  
    ts2.sname,  
    ri.num  
    from t_student ts2  
    right join  
    (select ts.sid, count(_) as num from t_score ts group by ts.sid having count(_) = 2) ri  
    on ri.sid = ts2.sid

  7. Query the number of male and female students 

    1
    select ssex, count(_) as num from t_student group by ssex ;

  8. Query the student information whose name contains the character “Feng” 

    1
    select * from t_student where sname like '%Feng%';

  9. Query the list of students with the same name and count the number of people with the same name 

    1
    select sname, count(_) from t_student group by sname having count(_) > 1;

  10. Query the list of students born in 1990 

    1
    2
    select * from t_student where date_part('year', sage) = '1990'  
    select * from t_student where extract(year from sage) = '1990'

  11. Query the average score of each course. The results are sorted in descending order of the average score. When the average score is the same, they are sorted in ascending order of the course ID. 

    1
    select cid, avg(score) av_s from t_score ts group by cid order by av_s desc,cid asc;

  12. Query the student ID, student name and average score of all students whose average score is greater than or equal to 85 

    1
    2
    3
    4
    5
    6
    7
    select  
    ts.sid,  
    ts.sname,  
    ri.avs  
    from t_student ts  
    right join (select sid, avg(score) avs from t_score ts group by sid having avg(score) >=85 ) ri  
    on ri.sid = ts.sid

  13. Query the student name and score of students whose course name is “Mathematics” and whose score is lower than 60 

    1
    2
    3
    4
    5
    select  
    ts.sid, ts2.sname, ts.score  
    from t_score ts  
    inner join t_student ts2 on ts2.sid = ts.sid  
    and ts.score < 60 and ts.cid in (select cid from t_course where cname ='Mathematics')

  14. Query the courses and scores of all students (there are situations where students have no grades or have not selected courses 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    select  
    ts.*,  
    ri."Chinese",  
    ri."Mathematics",  
    ri."English"  
    from t_student ts  
    left join  
    (select ts2.sid,  
    max(case when ts2.cid = '01' then score else null end) "Chinese",  
    max(case when ts2.cid = '02' then score else null end) "Mathematics",  
    max(case when ts2.cid = '03' then score else null end) "English"  
    from t_score ts2 group by ts2.sid) ri  
    on ts.sid = ri.sid

  15. Query the name, course name and score of students whose score of any course is above 70 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    select  
    ts.sid,  
    ts.sname,  
    ri."Chinese",  
    ri."Mathematics",  
    ri."English"  
    from t_student ts  
    inner join  
    (select  
    ts2.sid,  
    max(case when ts2.cid = '01' then score else null end) "Chinese",  
    max(case when ts2.cid = '02' then score else null end) "Mathematics",  
    max(case when ts2.cid = '03' then score else null end) "English"  
    from t_score ts2 group by ts2.sid) ri  
    on ri.sid = ts.sid and ri."Chinese" >=70 and ri."Mathematics" >=70 and ri."English" >=70

  16. Query courses with failed grades 

    1
    select cid from t_score ts group by cid having min(score) < 60

  17. Query the student ID and name of students whose course ID is 01 and whose course score is 80 or above 

    1
    2
    3
    4
    5
    6
    7
    select  
    ts2.sid, ts2.sname  
    from  
    t_student ts2  
    right join  
    (select sid from t_score ts where ts.cid = '01' and ts.score >=80) ri  
    on ri.sid = ts2.sid

  18. Query the number of students for each course 

    1
    select cid, count(_) from t_score group by cid;

  19. When the grades are not repeated, query the student information and grade of the student with the highest grade among the students who have taken the courses taught by the teacher “Zhang San” 

    1
    2
    3
    4
    5
    6
    with cid_col as (select cid from t_course where tid in  
    (select tid from t_teacher where tname = 'Zhang San'))  
    select ts._, ri.score from t_student ts  
    inner join (select sid, score from t_score where cid in (select * from cid_col))ri  
    on ri.sid = ts.sid  
    order by ri.score desc limit 1  
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    select  
    t_student._,  
    t_score.score  
    from t_course,t_score,t_student,t_teacher  
    where  
    t_teacher.tname = 'Zhang San'  
    and t_teacher.tid = t_course.tid  
    and t_course.cid = t_score.cid  
    and t_score.sid = t_student.sid  
    order by t_score.score desc limit 1

  20. When the grades are repeated, query the student information and grade of the student with the highest grade among the students who have taken the courses taught by the teacher “Zhang San” 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    select lef.sid, lef.sname, lef.score from  
    (  
    select  
    t_student._,  
    t_score.score,  
    t_score.cid  
    from t_course,t_score,t_student,t_teacher  
    where  
    t_teacher.tname = 'Zhang San'  
    and t_teacher.tid = t_course.tid  
    and t_course.cid = t_score.cid  
    and t_score.sid = t_student.sid  
    ) lef  
    inner join  
    (select cid,max(score) max_sc from t_score group by cid) rig  
    on  
    lef.cid = rig.cid  
    and  
    lef.score = rig.max_sc

  21. Query the student ID, course ID and student score of students with the same score in different courses 

    1
    2
    3
    4
    5
    select ts1.sid, ts1.cid, ts1.score from t_score ts1  
    inner join t_score ts2  
    on ts2.cid = ts1.cid and ts1.sid<> ts2.sid and ts1.score = ts2.score  
    group by ts1.cid, ts1.sid  
    order by cid

  22. Query the top two students with the best grades in each course 

    1
    2
    3
    4
    5
    6
    (select * from t_score where cid = '01' order by score desc limit 2)  
    union  
    (select * from t_score where cid = '02' order by score desc limit 2)  
    union  
    (select * from t_score where cid = '03' order by score desc limit 2)  
    order by cid  
    1
    2
    3
    4
    5
    6
    select ts1.sid, ts1.cid, ts1.score from t_score ts1  
    left join t_score ts2  
    on ts2.cid = ts1.cid and ts1.score < ts2.score  
    group by ts1.sid, ts1.cid  
    having count(ts1.sid) < 2  
    order by cid

  23. Count the number of students selected for each course (only count courses with more than 5 students) 

    1
    select cid, count(*) as num from t_score group by cid having count(*) > 5 order by cid

  24. Retrieve the student ID of students who have selected at least two courses 

    1
    select sid, count(_) as num from t_score group by sid having count(_) >= 2 order by sid

  25. Query the information of students who have selected all courses 

    1
    2
    with nu as (select count(_) from t_course tc)  
    select sid, count(*) as num from t_score group by sid having count(*) >= (select * from nu) order by sid

  26. Query the age of each student, calculated only by year 

    1
    2
    3
    4
    5
    6
    select sname, to_number( to_char(current_date,'yyyy'), '9999') - to_number(to_char(sage,'yyyy'), '9999') as "Year" from t_student order by "Year"  
    ```  
    46. Calculate the age based on the date of birth. If the current month and day are less than the month and day of the birth date, subtract one from the age  
    ```sql
    select sname, extract ("year" from age(sage)) as "Age" from t_student order by "Age"  
    select sname, date_part ('year', age(sage)) as "Age" from t_student order by "Age"

  27. Query students whose birthday is this month 

    1
    select sname, to_char(sage,'yyyy-mm-dd') from t_student where to_number( to_char(current_date,'mm'), '99') - to_number(to_char(sage,'mm'), '99') = 0

  28. Query students whose birthday is next month 

    1
    select sname, to_char(sage,'yyyy-mm-dd') from t_student where to_number( to_char(current_date,'mm'), '99') - to_number(to_char(sage,'mm'), '99') = -1

  29. Query students whose date of birth is from May 18th to 25th, 1990
    General do not use between and statement

    1
    2
    3
    4
    5

    select * from t_student ts where  
    ts.sage >= cast(concat('1990-05-18', '0:00:00') as timestamp)  
    and  
    ts.sage <= cast(concat('1990-05-25', '3:59:59') as timestamp)

If it is xml, modification is required.

OK,If you find this article helpful, feel free to share it with more people.

If you want to find a SQL tool to practice, you can try our sqlynx, which has a simple interface and is easy to use. https://www.sqlynx.com/download/ Free download

Share

Copyright © 2024 SQLynx Co., Ltd.