DEV Community: tom

Essential PSQL 50 Selected Practice Questions with Answers

tom — Wed, 07 Aug 2024 11:10:30 +0000

Environment Setup（PGsql）

t_course Table

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

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

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

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.

Practice Starts

### 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

sql
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

sql
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

sql
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

sql
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

sql
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)

sql
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

sql
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

sql
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.

sql
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

sql
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)

sql
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

sql
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"

sql
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

sql
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


12. 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

sql
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 ;

13. 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

sql
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)

);


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

sql
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

sql
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


17. 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

sql
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


18. Display all students' grades of all courses and average grades in descending order of average grades

sql
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


19. 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.

sql
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;


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

sql
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;


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

sql
select sid, sum(score), dense_rank() over(order by sum(score) desc) from

t_score group by sid;


22. 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

sql
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


23. Query for Students Who Have Not Taken All Courses

sql
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


24. Query the number of students selected for each course

sql
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 ;


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

sql
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


26. Query the number of male and female students

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


27. Query the student information whose name contains the character "Feng"

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


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

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


29. Query the list of students born in 1990

sql
select * from t_student where date_part('year', sage) = '1990'

select * from t_student where extract(year from sage) = '1990'


30. 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.

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


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

sql
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


32. Query the student name and score of students whose course name is "Mathematics" and whose score is lower than 60

sql
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')


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

sql
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


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

sql
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


35. Query courses with failed grades

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


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

sql
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


37. Query the number of students for each course

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


38. 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"

sql
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

sql
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


39. 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"

sql
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


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

sql
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


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

sql
(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

sql
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


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

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


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

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


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

sql
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


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

sql
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"


47. Query students whose birthday is this month

sql
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


48. Query students whose birthday is next month

sql
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


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

sql

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/](https://www.sqlynx.com/download/) Free download

Essential MtSQL 50 Selected Practice Questions with Answers

tom — Wed, 07 Aug 2024 11:09:46 +0000

Table Names and Fields（MySQL）

Student Table
Student(s_id, s_name, s_birth, s_sex)
Student ID, Student Name, Date of Birth, Student Gender
Course Table

Course(c_id, c_name, t_id)
Course ID, Course Name, Teacher ID
Teacher Table

Teacher(t_id, t_name)
Teacher ID, Teacher Name
Score Table

Score(s_id, c_id, s_score)
Student ID, Course ID, Score

Test Data - Creating Tables

Student Table

CREATE TABLE  `Student`(  
`s_id`  VARCHAR(20),  
`s_name`  VARCHAR(20) NOT NULL DEFAULT '',  
`s_birth`  VARCHAR(20) NOT NULL DEFAULT '',  
`s_sex`  VARCHAR(10) NOT NULL DEFAULT '',  
PRIMARY KEY(`s_id`)  
);

Course Table

CREATE TABLE  `Course`(  
`c_id`  VARCHAR(20),  
`c_name`  VARCHAR(20) NOT NULL DEFAULT '',  
`t_id`  VARCHAR(20) NOT NULL,  
PRIMARY KEY(`c_id`)  
);

Teacher Table

CREATE TABLE  `Teacher`(  
`t_id`  VARCHAR(20),  
`t_name`  VARCHAR(20) NOT NULL DEFAULT '',  
PRIMARY KEY(`t_id`)  
);

Score Table

CREATE TABLE  `Score`(  
`s_id`  VARCHAR(20),  
`c_id`  VARCHAR(20),  
`s_score`  INT(3),  
PRIMARY KEY(`s_id`,`c_id`)  
);

Inserting Test Data into Student Table

INSERT INTO Student VALUES('01', 'John Doe', '1990-01-01', 'Male');  
INSERT INTO Student VALUES('02', 'Jane Smith', '1990-12-21', 'Male');  
INSERT INTO Student VALUES('03', 'Michael Brown', '1990-05-20', 'Male');  
INSERT INTO Student VALUES('04', 'Emily Davis', '1990-08-06', 'Male');  
INSERT INTO Student VALUES('05', 'Lucy Johnson', '1991-12-01', 'Female');  
INSERT INTO Student VALUES('06', 'Sophia Williams', '1992-03-01', 'Female');  
INSERT INTO Student VALUES('07', 'Olivia Taylor', '1989-07-01', 'Female');  
INSERT INTO Student VALUES('08', 'Victoria King', '1990-01-20', 'Female');

Inserting Test Data into Course Table

INSERT INTO Course VALUES('01', 'Literature', '02');  
INSERT INTO Course VALUES('02', 'Mathematics', '01');  
INSERT INTO Course VALUES('03', 'English', '03');

Inserting Test Data into Teacher Table

INSERT INTO Teacher VALUES('01', 'Andrew');  
INSERT INTO Teacher VALUES('02', 'Bethany');  
INSERT INTO Teacher VALUES('03', 'Charlie');

Transcript Test Data

insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

Exercise questions and SQL statements

Retrieve the information and course scores of students who have a higher score in course '01' than in course '02'

SELECT a.*, b.s_score AS '01_score', c.s_score AS '02_score'  
FROM student a  
JOIN score b ON a.s_id = b.s_id AND b.c_id = '01'  
LEFT JOIN score c ON a.s_id = c.s_id AND c.c_id = '02'  
WHERE b.s_score > COALESCE(c.s_score, 0); -- Using COALESCE instead of OR c.c_id = NULL  

-- Alternatively  
SELECT a.*, b.s_score AS '01_score', c.s_score AS '02_score'  
FROM student a, score b, score c  
WHERE a.s_id = b.s_id  
AND a.s_id = c.s_id  
AND b.c_id = '01'  
AND c.c_id = '02'  
AND b.s_score > c.s_score;

Retrieve the information and course scores of students who have a lower score in course '01' than in course '02'

SELECT a.*, b.s_score AS '01_score', c.s_score AS '02_score'  
FROM student a  
LEFT JOIN score b ON a.s_id = b.s_id AND b.c_id = '01'  
JOIN score c ON a.s_id = c.s_id AND c.c_id = '02'  
WHERE COALESCE(b.s_score, 0) < c.s_score; -- Using COALESCE for clarity

Retrieve student IDs, names, and average scores for students with an average score of 60 or above

SELECT b.s_id, b.s_name, ROUND(AVG(a.s_score), 2) AS avg_score  
FROM student b  
JOIN score a ON b.s_id = a.s_id  
GROUP BY b.s_id, b.s_name  
HAVING AVG(a.s_score) >= 60;

Retrieve student IDs, names, and average scores for students with an average score below 60 (including those with no scores)

SELECT b.s_id, b.s_name, ROUND(AVG(a.s_score), 2) AS avg_score  
FROM student b  
LEFT JOIN score a ON b.s_id = a.s_id  
GROUP BY b.s_id, b.s_name  
HAVING AVG(a.s_score) < 60  
UNION  
SELECT a.s_id, a.s_name, 0 AS avg_score  
FROM student a  
WHERE a.s_id NOT IN (SELECT DISTINCT s_id FROM score);

Retrieve student IDs, names, total courses selected, and total scores across all courses

SELECT a.s_id, a.s_name, COUNT(b.c_id) AS sum_course, SUM(b.s_score) AS sum_score  
FROM student a  
LEFT JOIN score b ON a.s_id = b.s_id  
GROUP BY a.s_id, a.s_name;

Query the number of teachers with the surname "Smith"

SELECT  COUNT(t_id) FROM teacher WHERE t_name LIKE  'Smith%';

Query the information of students who have taken classes taught by Teacher "John Doe"

SELECT a.*  
FROM student a  
JOIN score b ON a.s_id = b.s_id  
WHERE b.c_id IN (  
    SELECT c_id FROM course  
    WHERE t_id = (  
        SELECT t_id FROM teacher  
        WHERE t_name = 'John Doe'  
    )  
);

Query the information of students who have not taken classes taught by Teacher "John Doe"

SELECT *  
FROM student c  
WHERE c.s_id NOT IN (  
    SELECT a.s_id  
    FROM student a  
    JOIN score b ON a.s_id = b.s_id  
    WHERE b.c_id IN (  
        SELECT a.c_id  
        FROM course a  
        JOIN teacher b ON a.t_id = b.t_id  
        WHERE t_name = 'John Doe'  
    )  
);

Query the information of students who have taken both courses with IDs "Math101" and "Science101"

SELECT a.*  
FROM student a, score b, score c  
WHERE a.s_id = b.s_id  
AND a.s_id = c.s_id  
AND b.c_id = 'Math101'  
AND c.c_id = 'Science101';

Query the information of students who have taken the course with ID "Math101" but have not taken the course with ID "Science101"

SELECT a.*
FROM student a
WHERE a.s_id IN (SELECT s_id FROM score WHERE c_id = 'Math101')
AND a.s_id NOT IN (SELECT s_id FROM score WHERE c_id = 'Science101');

Query information of students who have not taken all courses

-- @wendiepei's approach
SELECT s.*
FROM student s
LEFT JOIN Score s1 ON s1.s_id = s.s_id
GROUP BY s.s_id
HAVING COUNT(s1.c_id) < (SELECT COUNT(*) FROM course);
-- @k1051785839's approach
SELECT *  
FROM student  
WHERE s_id NOT IN (  
    SELECT s_id   
    FROM score t1    
    GROUP BY s_id   
    HAVING COUNT(*) = (SELECT COUNT(DISTINCT c_id) FROM course)  
);

Query information of students who have taken at least one course in common with student ID '01'

SELECT *   
FROM student   
WHERE s_id IN (  
    SELECT DISTINCT a.s_id   
    FROM score a   
    WHERE a.c_id IN (  
        SELECT c_id   
        FROM score   
        WHERE s_id = '01'  
    )  
);

Query information of students who have taken exactly the same courses as student ID '01'

SELECT
 t3.*
FROM
 (
  SELECT
   s_id,
   group_concat(c_id ORDER BY c_id) group1
  FROM
   score
  WHERE
   s_id &lt;> '01'
  GROUP BY
   s_id
 ) t1
INNER JOIN (
 SELECT
  group_concat(c_id ORDER BY c_id) group2
 FROM
  score
 WHERE
  s_id = '01'
 GROUP BY
  s_id
) t2 ON t1.group1 = t2.group2
INNER JOIN student t3 ON t1.s_id = t3.s_id

Query the names of students who have not taken any course taught by Teacher "Tom"

select a.s_name from student a where a.s_id not in (
    select s_id from score where c_id = 
                (select c_id from course where t_id =(
                    select t_id from teacher where t_name = 'Tom')));

Query student IDs, names, and average scores of students who have failed two or more courses

SELECT a.s_id, a.s_name, ROUND(AVG(b.s_score), 2) AS average_score  
FROM student a  
LEFT JOIN score b ON a.s_id = b.s_id  
WHERE a.s_id IN (  
    SELECT s_id  
    FROM score  
    WHERE s_score < 60  
    GROUP BY s_id  
    HAVING COUNT(*) >= 2  
)  
GROUP BY a.s_id, a.s_name;

Retrieve student information for students who scored less than 60 on course "01", ordered by score in descending order.

SELECT a.*, b.c_id, b.s_score  
FROM student a  
JOIN score b ON a.s_id = b.s_id  
WHERE b.c_id = '01' AND b.s_score < 60  
ORDER BY b.s_score DESC;

Display the scores of all courses and the average score for each student, ordered by their average score from highest to lowest.

SELECT   
    a.s_id,  
    MAX(CASE WHEN c_id = '01' THEN s_score END) AS Chinese,  
    MAX(CASE WHEN c_id = '02' THEN s_score END) AS Math,  
    MAX(CASE WHEN c_id = '03' THEN s_score END) AS English,  
    ROUND(AVG(s_score), 2) AS average_score  
FROM score a  
GROUP BY a.s_id  
ORDER BY average_score DESC;

Query the highest score, lowest score, average score, pass rate, medium rate, good rate, and excellent rate for each course. Display in the following format: Course ID, Course Name, Highest Score, Lowest Score, Average Score, Pass Rate, Medium Rate, Good Rate, Excellent Rate. -- Pass is >=60, Medium is 70-80, Good is 80-90, Excellent is >=90

SELECT   
    a.c_id,  
    b.c_name,  
    MAX(s_score) AS HighestScore,  
    MIN(s_score) AS LowestScore,  
    ROUND(AVG(s_score), 2) AS AverageScore,  
    ROUND(100 * (SUM(CASE WHEN s_score >= 60 THEN 1 ELSE 0 END) / COUNT(s_score)), 2) AS PassRate,  
    ROUND(100 * (SUM(CASE WHEN s_score BETWEEN 70 AND 80 THEN 1 ELSE 0 END) / COUNT(s_score)), 2) AS MediumRate,  
    ROUND(100 * (SUM(CASE WHEN s_score BETWEEN 80 AND 90 THEN 1 ELSE 0 END) / COUNT(s_score)), 2) AS GoodRate,  
    ROUND(100 * (SUM(CASE WHEN s_score >= 90 THEN 1 ELSE 0 END) / COUNT(s_score)), 2) AS ExcellentRate  
FROM   
    score a   
LEFT JOIN   
    course b ON a.c_id = b.c_id   
GROUP BY   
    a.c_id, b.c_name;

Sort scores by course and display rankings. MySQL does not have a built-in RANK() function, so we'll use variables to simulate it.

SELECT   
    a.s_id,  
    a.c_id,  
    @rank := IF(@prev_score = a.s_score, @rank, @rank + 1) AS rank_without_ties,  
    @prev_score := a.s_score AS score  
FROM   
    (SELECT s_id, c_id, s_score FROM score ORDER BY c_id, s_score DESC) a,  
    (SELECT @rank := 0, @prev_score := NULL) r  
ORDER BY   
    a.c_id, a.rank_without_ties;

Query the total score of each student and rank them

SELECT   
    a.s_id,  
    @rank := IF(@prev_score = a.sum_score, @rank, @rank + 1) AS rank,  
    @prev_score := a.sum_score AS total_score  
FROM   
    (SELECT s_id, SUM(s_score) AS sum_score FROM score GROUP BY s_id ORDER BY sum_score DESC) a,  
    (SELECT @rank := 0, @prev_score := NULL) r  
ORDER BY   
    total_score DESC;

Query the average score of different courses taught by different teachers, sorted from highest to lowest

SELECT   
    a.t_id,  
    c.t_name,  
    a.c_id,  
    ROUND(AVG(s_score), 2) AS avg_score   
FROM   
    course a  
LEFT JOIN   
    score b ON a.c_id = b.c_id   
LEFT JOIN   
    teacher c ON a.t_id = c.t_id  
GROUP BY   
    a.c_id, a.t_id, c.t_name   
ORDER BY   
    avg_score DESC;

Query the information of students who rank second and third in all courses along with their scores

(SELECT   
    d.*,  
    c.ranking,  
    c.s_score,  
    c.c_id  
FROM   
    (SELECT   
        s_id,   
        s_score,   
        c_id,   
        @rank := IF(@prev_cid = c_id, @rank + 1, 1) AS ranking,  
        @prev_cid := c_id  
    FROM   
        score,   
        (SELECT @rank := 0, @prev_cid := NULL) AS var_init  
    WHERE   
        c_id = '01'  
    ORDER BY   
        c_id, s_score DESC  
    ) c  
LEFT JOIN   
    student d ON c.s_id = d.s_id  
WHERE   
    c.ranking BETWEEN 2 AND 3  
)  
UNION  
(SELECT   
    d.*,  
    c.ranking,  
    c.s_score,  
    c.c_id  
FROM   
    (SELECT similar structure as above but with c_id = '02' in the WHERE clause) c  
LEFT JOIN   
    student d ON c.s_id = d.s_id  
WHERE   
    c.ranking BETWEEN 2 AND 3  
)  
UNION  
(SELECT similar structure as above but with c_id = '03' in the WHERE clause);

Count the number of students in each score range for each subject:

select distinct f.c_name, a.c_id,
       b.`85-100`, b.Percentage as `[85-100] Percentage`,
       c.`70-85`, c.Percentage as `[70-85] Percentage`,
       d.`60-70`, d.Percentage as `[60-70] Percentage`,
       e.`0-60`, e.Percentage as `[0-60] Percentage`
from score a
    left join (
        select c_id,
               SUM(case when s_score > 85 and s_score <= 100 then 1 else 0 end) as `85-100`,
               ROUND(100*(SUM(case when s_score > 85 and s_score <= 100 then 1 else 0 end)/count(*)),2) as Percentage
        from score GROUP BY c_id
    ) b on a.c_id = b.c_id
    left join (
        select c_id,
               SUM(case when s_score > 70 and s_score <= 85 then 1 else 0 end) as `70-85`,
               ROUND(100*(SUM(case when s_score > 70 and s_score <= 85 then 1 else 0 end)/count(*)),2) as Percentage
        from score GROUP BY c_id
    ) c on a.c_id = c.c_id
    left join (
        select c_id,
               SUM(case when s_score > 60 and s_score <= 70 then 1 else 0 end) as `60-70`,
               ROUND(100*(SUM(case when s_score > 60 and s_score <= 70 then 1 else 0 end)/count(*)),2) as Percentage
        from score GROUP BY c_id
    ) d on a.c_id = d.c_id
    left join (
        select c_id,
               SUM(case when s_score >= 0 and s_score <= 60 then 1 else 0 end) as `0-60`,
               ROUND(100*(SUM(case when s_score >= 0 and s_score <= 60 then 1 else 0 end)/count(*)),2) as Percentage
        from score GROUP BY c_id
    ) e on a.c_id = e.c_id
    left join course f on a.c_id = f.c_id;

Query average scores and their ranks for students:

select a.s_id,
       @i:=@i+1 as 'No Gaps in Ranking',
       @k:=(case when @avg_score=a.avg_s then @k else @i end) as 'With Gaps in Ranking',
       @avg_score:=avg_s as 'Average Score'
from (select s_id, ROUND(AVG(s_score),2) as avg_s from score GROUP BY s_id ORDER BY avg_s DESC) a,
     (select @avg_score:=0, @i:=0, @k:=0) b;

Query records of the top three students in each subject:

select a.s_id, a.c_id, a.s_score from score a 
    left join score b on a.c_id = b.c_id and a.s_score < b.s_score
    group by a.s_id, a.c_id, a.s_score 
    having count(b.s_id) < 3
    order by a.c_id, a.s_score desc;

Query the number of students enrolled in each course:

select c_id, count(s_id) from score group by c_id;

Query the student ID and name of students who have taken exactly two courses:

select s_id, s_name from student 
    where s_id in (select s_id from score group by s_id having count(c_id) = 2);

Query the number of male and female students:

select s_sex, count(s_sex) as Count from student group by s_sex;

Query student information whose name contains the character "Tom":

select * from student where s_name like '%Tom%';

Query list of students with the same name and gender, and count of such names:

select a.s_name, a.s_sex, count(*) as Count from student a  
    join student b on a.s_id != b.s_id and a.s_name = b.s_name and a.s_sex = b.s_sex
    group by a.s_name, a.s_sex;

Query list of students born in 1990:

select s_name from student where s_birth like '1990%';

Query average scores for each course, ordered by average score descending, and course ID ascending if average scores are the same:

select c_id, round(avg(s_score), 2) as avg_score from score group by c_id order by avg_score desc, c_id asc;

Query student ID, name, and average score of students with average score >= 85:

select a.s_id, b.s_name, round(avg(a.s_score), 2) as avg_score from score a
    left join student b on a.s_id = b.s_id group by s_id having avg_score >= 85;

Query names and scores of students who scored less than 60 in the course "mathematics":

select a.s_name, b.s_score from student a 
    join score b on a.s_id = b.s_id 
    where b.c_id = (select c_id from course where c_name = 'mathematics') 
    and b.s_score < 60;

Query course-wise scores and total scores of all students:

select a.s_id, a.s_name,
    sum(case c.c_name when 'history' then b.s_score else 0 end) as 'history',
    sum(case c.c_name when 'mathematics' then b.s_score else 0 end) as 'mathematics',
    sum(case c.c_name when 'Politics' then b.s_score else 0 end) as 'Politics',
    sum(b.s_score) as 'Total score'
from student a 
left join score b on a.s_id = b.s_id 
left join course c on b.c_id = c.c_id 
group by a.s_id, a.s_name;

Query names, course names, and scores of students scoring above 70 in any course:

select a.s_name, b.c_name, c.s_score from student a 
    left join score c on a.s_id = c.s_id 
    left join course b on c.c_id = b.c_id 
    where c.s_score >= 70;

Query courses where students failed:

select a.s_id, a.c_id, b.c_name, a.s_score from score a 
    left join course b on a.c_id = b.c_id 
    where a.s_score < 60;

Query student ID and name of students who scored above 80 in course '01':

select a.s_id, b.s_name from score a 
    left join student b on a.s_id = b.s_id 
    where a.c_id = '01' and a.s_score > 80;

Count number of students in each course:

select count(*) from score group by c_id;

Query information of the highest scoring student in courses taught by teacher "Tom": -- Get teacher ID

select c_id from course c, teacher d where c.t_id = d.t_id and d.t_name = 'Tom';

-- Get maximum score (could have ties)

select max(s_score) from score where c_id = '02';

-- Get information

select a.*, b.s_score, b.c_id, c.c_name from student a 
    left join score b on a.s_id = b.s_id 
    left join course c on b.c_id = c.c_id 
    where b.c_id = (select c_id from course c, teacher d where c.t_id = d.t_id and d.t_name = 'Tom')
    and b.s_score in (select max(s_score) from score where c_id = '02');

Query student ID, course ID, and score where different courses have the same score:

select distinct b.s_id, b.c_id, b.s_score from score a, score b 
    where a.c_id != b.c_id and a.s_score = b.s_score;

Query top two scores for each course:

select a.s_id, a.c_id, a.s_score from score a 
    where (select count(1) from score b where b.c_id = a.c_id and b.s_score >= a.s_score) <= 2 order by a.c_id;

Count number of students enrolled in each course (courses with more than 5 students):

select c_id, count(*) as total from score group by c_id having total > 5 order by total, c_id asc;

Query student IDs who have enrolled in at least two courses:

select s_id, count(*) as sel from score group by s_id having sel >= 2;

Query information of students who have enrolled in all courses:

select * from student where s_id in (select s_id from score group by s_id having count(*) = (select count(*) from course));

Query age of each student: -- Calculate age based on birthdate; subtract one if current month/day is before birthdate's month/day

select s_birth, (date_format(now(), '%Y') - date_format(s_birth, '%Y') - 
    (case when date_format(now(), '%m%d') > date_format(s_birth, '%m%d') then 0 else 1 end)) as age
    from student;

Query students whose birthday is this week:

select * from student where week(date_format(now(), '%Y%m%d')) = week(s_birth);

Query students whose birthday is next week:

select * from student where week(date_format(now(), '%Y%m%d')) + 1 = week(s_birth);

Query students whose birthday is this month:

select * from student where month(date_format(now(), '%Y%m%d')) = month(s_birth);

Query students whose birthday is next month:

select * from student where month(date_format(now(), '%Y%m%d')) + 1 = month(s_birth);

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

How to check the MySQL version on Windows, so easy that even a five-year-old can learn it

tom — Mon, 05 Aug 2024 01:43:35 +0000

Why do you need to know your MySQL version?
Check the MySQL Version
- From the Command Line
- From the MySQL Client
- SELECT VERSION Statement
- STATUS Command
- SHOW VARIABLES LIKE Statement

Why do you need to know your MySQL version?

Knowing which version of MySQL you’re using is crucial because there can be some significant differences between versions. A newer version of MySQL means that your system will have new features and be more capable. Your MySQL version number can be essential in figuring out potential issues when interacting with external tools and plugins since some features might not work with your system.

Outdated databases are vulnerable to security breaches, duplicate data, and inaccurate reporting, among other errors. It’s critical to upgrade your database software to meet your organization’s needs and ensure you’re utilizing the most recent software and technology. Additionally, installing the best MySQL version lowers the possibility of your system failing.

This article will teach you how to check the MySQL version on your server.

Check the MySQL Version

These guidelines are for confirming the MySQL version on our dedicated server and VPS. Remember, some operations need root privileges. And because MariaDB is a fork of MySQL, the commands work interchangeably with each database software.

From the Command Line

Use the following command to check the version of your local MySQL server. This command is not specific to any operating system. This command is compatible with all Linux, Windows, and macOS versions running MySQL.

mysql -V

Here is the output.

# mysql -V
mysql  Ver 15.1 Distrib 10.3.37-MariaDB, for Linux (x86_64) using readline 5.1

Database administration tasks can also be carried out using the client-side utility mysqladmin. It allows you to do many things, including creating and deleting databases, tables, and users and checking the configuration and current status of the server. To find out which version of MySQL is installed, use the mysqladmin command.

mysqladmin -V

Here is the output.

# mysqladmin -V
mysqladmin  Ver 9.1 Distrib 10.3.37-MariaDB, for Linux on x86_64

From the MySQL Client

The MySQL command line client is a simple and direct SQL shell that supports input editing. You can use the following command as root to connect to your MySQL client.

mysql

Here is the output.

# mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 586239
Server version: 10.3.37-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

According to the output, the server is running MariaDB version 10.3.37. There are many commands to get detailed information about the installed version in the MySQL or MariaDB client shell.

SELECT VERSION Statement

Use the SELECT VERSION() command in the MySQL database client to check the MySQL version.

SELECT version();

Here is the output.

MariaDB [***]> SELECT version();
+-----------------+
| version()       |
+-----------------+
| 10.3.37-MariaDB |
+-----------------+
1 row in set (0.000 sec)

You can also use the following query to check the MySQL version.

SELECT @@version;

Here is the output.

MariaDB [***]> SELECT @@version;
+-----------------+
| @@version       |
+-----------------+
| 10.3.37-MariaDB |
+-----------------+
1 row in set (0.000 sec)

MariaDB [***]>

STATUS Command

Another option to check the MySQL version is the STATUS command. Run it in the MySQL client tool.

STATUS;

Here is the output. It contains uptime information, including how long the MySQL server has been running, the threads information, and other valuable details.

MariaDB [***]> STATUS; 
--------------
mysql  Ver 15.1 Distrib 10.3.37-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:      123456
Current database:   testdb_1
Current user:       testdb_user@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server:         MariaDB
Server version:     10.3.37-MariaDB MariaDB Server
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    utf8mb4
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /var/lib/mysql/mysql.sock
Uptime:         18 days 12 hours 40 min 9 sec

Threads: 21  Questions: 12345678  Slow queries: 2  Opens: 12345  Flush tables: 1  Open tables: 2048  Queries per second avg: 18.405
--------------

MariaDB [***]>

SHOW VARIABLES LIKE Statement

A SHOW VARIABLES LIKE statement is another method of displaying the MySQL version information. Enter the following command in MySQL Client.

SHOW VARIABLES LIKE "%version%";

Here is the output.

MariaDB [***]> SHOW VARIABLES LIKE "%version%";
+-----------------------------------+------------------------------------------+
| Variable_name                | Value                                    |
+-----------------------------------+------------------------------------------+
| in_predicate_conversion_threshold | 1000                                |
| innodb_version               | 10.3.37                                  |
| protocol_version             | 10                                       |
| slave_type_conversions       |                                          |
| system_versioning_alter_history | ERROR                                 |
| system_versioning_asof       | DEFAULT                                  |
| version                      | 10.3.37-MariaDB                          |
| version_comment             | MariaDB Server                           |
| version_compile_machine     | x86_64                                   |
| version_compile_os          | Linux                                    |
| version_malloc_library      | system                                   |
| version_source_revision     | 3303748fd133drface4d64ert56c5a09445 |
| version_ssl_library         | OpenSSL 1.0.2k-fips  26 Jan 2017         |
| wsrep_patch_version         | wsrep_25.24                              |
+-----------------------------------+------------------------------------------+
14 rows in set (0.020 sec)
MariaDB [***]>

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

Essential Oracle 50 Selected Practice Questions with Answers

tom — Fri, 02 Aug 2024 10:39:01 +0000

Attention: All the questions are based on the tables of the scott account to write SQL.

Query the name and department number of the employee whose employee number is 7499
```
select ename, deptno from emp where empno = 7499;
```

Select the names and salaries of employees whose salary is not between 5000 and 12000

select ename, sal from emp where sal < 5000

 sal > 12000; or  
select ename, sal from emp where sal not between 5000 and 12000;

Select the names, job_ids and hire dates of employees whose hire date is between 1998-02-01 and 1998-05-01

select ename, job, hiredate from emp where hiredate > to_date('1998-02-01', 'yyyy-mm-dd')  
and hiredate < to_date('1998-05-01', 'yyyy-mm-dd');

Select the names and department numbers of employees who work in department 20 or 50

select ename, deptno from emp where deptno in(20, 50);

select ename, deptno from emp where deptno = 20 or deptno = 50

Select the names and hire dates of employees who were hired in 1992

select ename, hiredate from emp  
where extract(year from hiredate) = '1992';

Select the names and jobs of employees who have no manager in the company
```
select ename, job from emp where mgr is null;
```

Select the names of employees whose third letter of the name is 'a'

select ename from emp where instr(ename, 'A') = 3;

select ename from emp where ename like '__A%';

Select the names of employees whose names contain both the letters 'a' and 'e'

select ename as Name from emp where instr(ename, 'A') > 0 and instr(ename, 'E') > 0; or  
select ename from emp  
where ename like '%A%' and ename like '%E%';

Query the employee number, name, salary, and the result after increasing the salary by 20%
```
select empno, ename, sal, (sal + sal * 0.2) from emp;*
```

Sort the employees' names by the first letter and write the length of the name (length)

select ename, length(ename) from emp  
order by ascii(substr(ename, 1, 1));

Query the names of each employee and display the number of months each employee has worked in the company
```
select ename, trunc(months_between(sysdate, hiredate)) from emp;
```
Query the names of employees and the number of months they have worked (worked_month), and sort them in descending order of the number of months
```
select ename, trunc(months_between(sysdate, hiredate)) worked_month from emp order by worked_month desc;
```
Query the maximum, minimum, average and total salary of the employees in the company
```
select max(sal), min(sal), avg(sal), sum(sal) from emp;
```

Query the maximum, minimum, average and total salary of employees of each job

select max(sal), min(sal), avg(sal), sum(sal) from emp group by job;

Select the number of employees of each job

select job, count(1) from emp group by job

Query the difference between the maximum and minimum salary of employees
```
select max(sal) - min(sal) difference from emp
```
Query the minimum salary of each manager's subordinates, where the minimum salary cannot be lower than 6000, and employees without managers are not counted
```
select mgr, min(sal) from emp where mgr is not null  
group by mgr having min(sal) > 4000;
```

Query the names of all departments, work locations, number of employees and average salary.

select d.dname, d.loc, count(1), avg(sal) from dept d inner join emp e on e.deptno = d.deptno group by d.dname, d.loc;

Query the names and hire dates of employees who are in the same department as Scott

select ename, hiredate, deptno from emp  
where deptno = (select deptno from emp where ename = 'SCOTT');

Query the employee number, name and salary of employees whose salary is higher than the average salary of the company
```
select empno, ename, sal from emp where sal > (select avg(sal) from emp);
```

Query the employee number, name and salary of employees whose salary is higher than the average salary of their department

select empno, ename, sal from emp e  
where sal > (select avg(sal) from emp where deptno = e.deptno);

Query the employee numbers and names of employees who are in the same department as the employees whose names contain the letter 'u'

select empno, ename  
from emp where deptno in (select deptno from emp  
where instr(ename, 'U') > 0);

select e1.empno, e1.ename from emp e1, emp e2  
where instr(e2.ename, 'U', 1, 1) > 0 and e1.deptno = e2.deptno;

Query the names and salaries of employees whose manager is King

select ename, sal from emp  
where mgr = (select empno from emp where ename = 'KING');

Use PL/SQL to implement the 9*9 multiplication table

declare  
v_result number;  
v_i number;  
v_j number;  
begin  
for v_i in 1..9  
loop  
for v_j in 1..v_i  
loop  
v_result := v_i * v_j;  
dbms_output.put(v_i || '' || v_j || '=' || v_result ||' ');  
end loop;  
dbms_output.put_line('');  
end loop;  
end;

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

Essential MySQL 50 Selected Practice Questions with Answers

tom — Fri, 02 Aug 2024 10:37:43 +0000

Table Names and Fields（MySQL）

Student Table
Student(s_id, s_name, s_birth, s_sex)
Student ID, Student Name, Date of Birth, Student Gender
Course Table

Course(c_id, c_name, t_id)
Course ID, Course Name, Teacher ID
Teacher Table

Teacher(t_id, t_name)
Teacher ID, Teacher Name
Score Table

Score(s_id, c_id, s_score)
Student ID, Course ID, Score

Test Data - Creating Tables

Student Table

CREATE TABLE  `Student`(  
`s_id`  VARCHAR(20),  
`s_name`  VARCHAR(20) NOT NULL DEFAULT '',  
`s_birth`  VARCHAR(20) NOT NULL DEFAULT '',  
`s_sex`  VARCHAR(10) NOT NULL DEFAULT '',  
PRIMARY KEY(`s_id`)  
);

Course Table

CREATE TABLE  `Course`(  
`c_id`  VARCHAR(20),  
`c_name`  VARCHAR(20) NOT NULL DEFAULT '',  
`t_id`  VARCHAR(20) NOT NULL,  
PRIMARY KEY(`c_id`)  
);

Teacher Table

CREATE TABLE  `Teacher`(  
`t_id`  VARCHAR(20),  
`t_name`  VARCHAR(20) NOT NULL DEFAULT '',  
PRIMARY KEY(`t_id`)  
);

Score Table

CREATE TABLE  `Score`(  
`s_id`  VARCHAR(20),  
`c_id`  VARCHAR(20),  
`s_score`  INT(3),  
PRIMARY KEY(`s_id`,`c_id`)  
);

Inserting Test Data into Student Table

INSERT INTO Student VALUES('01', 'John Doe', '1990-01-01', 'Male');  
INSERT INTO Student VALUES('02', 'Jane Smith', '1990-12-21', 'Male');  
INSERT INTO Student VALUES('03', 'Michael Brown', '1990-05-20', 'Male');  
INSERT INTO Student VALUES('04', 'Emily Davis', '1990-08-06', 'Male');  
INSERT INTO Student VALUES('05', 'Lucy Johnson', '1991-12-01', 'Female');  
INSERT INTO Student VALUES('06', 'Sophia Williams', '1992-03-01', 'Female');  
INSERT INTO Student VALUES('07', 'Olivia Taylor', '1989-07-01', 'Female');  
INSERT INTO Student VALUES('08', 'Victoria King', '1990-01-20', 'Female');

Inserting Test Data into Course Table

INSERT INTO Course VALUES('01', 'Literature', '02');  
INSERT INTO Course VALUES('02', 'Mathematics', '01');  
INSERT INTO Course VALUES('03', 'English', '03');

Inserting Test Data into Teacher Table

INSERT INTO Teacher VALUES('01', 'Andrew');  
INSERT INTO Teacher VALUES('02', 'Bethany');  
INSERT INTO Teacher VALUES('03', 'Charlie');

Transcript Test Data

insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

Exercise questions and SQL statements

Retrieve the information and course scores of students who have a higher score in course '01' than in course '02'

SELECT a.*, b.s_score AS '01_score', c.s_score AS '02_score'  
FROM student a  
JOIN score b ON a.s_id = b.s_id AND b.c_id = '01'  
LEFT JOIN score c ON a.s_id = c.s_id AND c.c_id = '02'  
WHERE b.s_score > COALESCE(c.s_score, 0); -- Using COALESCE instead of OR c.c_id = NULL  

-- Alternatively  
SELECT a.*, b.s_score AS '01_score', c.s_score AS '02_score'  
FROM student a, score b, score c  
WHERE a.s_id = b.s_id  
AND a.s_id = c.s_id  
AND b.c_id = '01'  
AND c.c_id = '02'  
AND b.s_score > c.s_score;

Retrieve the information and course scores of students who have a lower score in course '01' than in course '02'

SELECT a.*, b.s_score AS '01_score', c.s_score AS '02_score'  
FROM student a  
LEFT JOIN score b ON a.s_id = b.s_id AND b.c_id = '01'  
JOIN score c ON a.s_id = c.s_id AND c.c_id = '02'  
WHERE COALESCE(b.s_score, 0) < c.s_score; -- Using COALESCE for clarity

Retrieve student IDs, names, and average scores for students with an average score of 60 or above

SELECT b.s_id, b.s_name, ROUND(AVG(a.s_score), 2) AS avg_score  
FROM student b  
JOIN score a ON b.s_id = a.s_id  
GROUP BY b.s_id, b.s_name  
HAVING AVG(a.s_score) >= 60;

Retrieve student IDs, names, and average scores for students with an average score below 60 (including those with no scores)

SELECT b.s_id, b.s_name, ROUND(AVG(a.s_score), 2) AS avg_score  
FROM student b  
LEFT JOIN score a ON b.s_id = a.s_id  
GROUP BY b.s_id, b.s_name  
HAVING AVG(a.s_score) < 60  
UNION  
SELECT a.s_id, a.s_name, 0 AS avg_score  
FROM student a  
WHERE a.s_id NOT IN (SELECT DISTINCT s_id FROM score);

Retrieve student IDs, names, total courses selected, and total scores across all courses

SELECT a.s_id, a.s_name, COUNT(b.c_id) AS sum_course, SUM(b.s_score) AS sum_score  
FROM student a  
LEFT JOIN score b ON a.s_id = b.s_id  
GROUP BY a.s_id, a.s_name;

Query the number of teachers with the surname "Smith"

SELECT  COUNT(t_id) FROM teacher WHERE t_name LIKE  'Smith%';

Query the information of students who have taken classes taught by Teacher "John Doe"

SELECT a.*  
FROM student a  
JOIN score b ON a.s_id = b.s_id  
WHERE b.c_id IN (  
    SELECT c_id FROM course  
    WHERE t_id = (  
        SELECT t_id FROM teacher  
        WHERE t_name = 'John Doe'  
    )  
);

Query the information of students who have not taken classes taught by Teacher "John Doe"

SELECT *  
FROM student c  
WHERE c.s_id NOT IN (  
    SELECT a.s_id  
    FROM student a  
    JOIN score b ON a.s_id = b.s_id  
    WHERE b.c_id IN (  
        SELECT a.c_id  
        FROM course a  
        JOIN teacher b ON a.t_id = b.t_id  
        WHERE t_name = 'John Doe'  
    )  
);

Query the information of students who have taken both courses with IDs "Math101" and "Science101"

SELECT a.*  
FROM student a, score b, score c  
WHERE a.s_id = b.s_id  
AND a.s_id = c.s_id  
AND b.c_id = 'Math101'  
AND c.c_id = 'Science101';

Query the information of students who have taken the course with ID "Math101" but have not taken the course with ID "Science101"

SELECT a.*
FROM student a
WHERE a.s_id IN (SELECT s_id FROM score WHERE c_id = 'Math101')
AND a.s_id NOT IN (SELECT s_id FROM score WHERE c_id = 'Science101');

Query information of students who have not taken all courses

-- @wendiepei's approach
SELECT s.*
FROM student s
LEFT JOIN Score s1 ON s1.s_id = s.s_id
GROUP BY s.s_id
HAVING COUNT(s1.c_id) < (SELECT COUNT(*) FROM course);
-- @k1051785839's approach
SELECT *  
FROM student  
WHERE s_id NOT IN (  
    SELECT s_id   
    FROM score t1    
    GROUP BY s_id   
    HAVING COUNT(*) = (SELECT COUNT(DISTINCT c_id) FROM course)  
);

Query information of students who have taken at least one course in common with student ID '01'

SELECT *   
FROM student   
WHERE s_id IN (  
    SELECT DISTINCT a.s_id   
    FROM score a   
    WHERE a.c_id IN (  
        SELECT c_id   
        FROM score   
        WHERE s_id = '01'  
    )  
);

Query information of students who have taken exactly the same courses as student ID '01'

SELECT
 t3.*
FROM
 (
  SELECT
   s_id,
   group_concat(c_id ORDER BY c_id) group1
  FROM
   score
  WHERE
   s_id &lt;> '01'
  GROUP BY
   s_id
 ) t1
INNER JOIN (
 SELECT
  group_concat(c_id ORDER BY c_id) group2
 FROM
  score
 WHERE
  s_id = '01'
 GROUP BY
  s_id
) t2 ON t1.group1 = t2.group2
INNER JOIN student t3 ON t1.s_id = t3.s_id

Query the names of students who have not taken any course taught by Teacher "Tom"

select a.s_name from student a where a.s_id not in (
    select s_id from score where c_id = 
                (select c_id from course where t_id =(
                    select t_id from teacher where t_name = 'Tom')));

Query student IDs, names, and average scores of students who have failed two or more courses

SELECT a.s_id, a.s_name, ROUND(AVG(b.s_score), 2) AS average_score  
FROM student a  
LEFT JOIN score b ON a.s_id = b.s_id  
WHERE a.s_id IN (  
    SELECT s_id  
    FROM score  
    WHERE s_score < 60  
    GROUP BY s_id  
    HAVING COUNT(*) >= 2  
)  
GROUP BY a.s_id, a.s_name;

Retrieve student information for students who scored less than 60 on course "01", ordered by score in descending order.

SELECT a.*, b.c_id, b.s_score  
FROM student a  
JOIN score b ON a.s_id = b.s_id  
WHERE b.c_id = '01' AND b.s_score < 60  
ORDER BY b.s_score DESC;

Display the scores of all courses and the average score for each student, ordered by their average score from highest to lowest.

SELECT   
    a.s_id,  
    MAX(CASE WHEN c_id = '01' THEN s_score END) AS Chinese,  
    MAX(CASE WHEN c_id = '02' THEN s_score END) AS Math,  
    MAX(CASE WHEN c_id = '03' THEN s_score END) AS English,  
    ROUND(AVG(s_score), 2) AS average_score  
FROM score a  
GROUP BY a.s_id  
ORDER BY average_score DESC;

Query the highest score, lowest score, average score, pass rate, medium rate, good rate, and excellent rate for each course. Display in the following format: Course ID, Course Name, Highest Score, Lowest Score, Average Score, Pass Rate, Medium Rate, Good Rate, Excellent Rate. -- Pass is >=60, Medium is 70-80, Good is 80-90, Excellent is >=90

SELECT   
    a.c_id,  
    b.c_name,  
    MAX(s_score) AS HighestScore,  
    MIN(s_score) AS LowestScore,  
    ROUND(AVG(s_score), 2) AS AverageScore,  
    ROUND(100 * (SUM(CASE WHEN s_score >= 60 THEN 1 ELSE 0 END) / COUNT(s_score)), 2) AS PassRate,  
    ROUND(100 * (SUM(CASE WHEN s_score BETWEEN 70 AND 80 THEN 1 ELSE 0 END) / COUNT(s_score)), 2) AS MediumRate,  
    ROUND(100 * (SUM(CASE WHEN s_score BETWEEN 80 AND 90 THEN 1 ELSE 0 END) / COUNT(s_score)), 2) AS GoodRate,  
    ROUND(100 * (SUM(CASE WHEN s_score >= 90 THEN 1 ELSE 0 END) / COUNT(s_score)), 2) AS ExcellentRate  
FROM   
    score a   
LEFT JOIN   
    course b ON a.c_id = b.c_id   
GROUP BY   
    a.c_id, b.c_name;

Sort scores by course and display rankings. MySQL does not have a built-in RANK() function, so we'll use variables to simulate it.

SELECT   
    a.s_id,  
    a.c_id,  
    @rank := IF(@prev_score = a.s_score, @rank, @rank + 1) AS rank_without_ties,  
    @prev_score := a.s_score AS score  
FROM   
    (SELECT s_id, c_id, s_score FROM score ORDER BY c_id, s_score DESC) a,  
    (SELECT @rank := 0, @prev_score := NULL) r  
ORDER BY   
    a.c_id, a.rank_without_ties;

Query the total score of each student and rank them

SELECT   
    a.s_id,  
    @rank := IF(@prev_score = a.sum_score, @rank, @rank + 1) AS rank,  
    @prev_score := a.sum_score AS total_score  
FROM   
    (SELECT s_id, SUM(s_score) AS sum_score FROM score GROUP BY s_id ORDER BY sum_score DESC) a,  
    (SELECT @rank := 0, @prev_score := NULL) r  
ORDER BY   
    total_score DESC;

Query the average score of different courses taught by different teachers, sorted from highest to lowest

SELECT   
    a.t_id,  
    c.t_name,  
    a.c_id,  
    ROUND(AVG(s_score), 2) AS avg_score   
FROM   
    course a  
LEFT JOIN   
    score b ON a.c_id = b.c_id   
LEFT JOIN   
    teacher c ON a.t_id = c.t_id  
GROUP BY   
    a.c_id, a.t_id, c.t_name   
ORDER BY   
    avg_score DESC;

Query the information of students who rank second and third in all courses along with their scores

(SELECT   
    d.*,  
    c.ranking,  
    c.s_score,  
    c.c_id  
FROM   
    (SELECT   
        s_id,   
        s_score,   
        c_id,   
        @rank := IF(@prev_cid = c_id, @rank + 1, 1) AS ranking,  
        @prev_cid := c_id  
    FROM   
        score,   
        (SELECT @rank := 0, @prev_cid := NULL) AS var_init  
    WHERE   
        c_id = '01'  
    ORDER BY   
        c_id, s_score DESC  
    ) c  
LEFT JOIN   
    student d ON c.s_id = d.s_id  
WHERE   
    c.ranking BETWEEN 2 AND 3  
)  
UNION  
(SELECT   
    d.*,  
    c.ranking,  
    c.s_score,  
    c.c_id  
FROM   
    (SELECT similar structure as above but with c_id = '02' in the WHERE clause) c  
LEFT JOIN   
    student d ON c.s_id = d.s_id  
WHERE   
    c.ranking BETWEEN 2 AND 3  
)  
UNION  
(SELECT similar structure as above but with c_id = '03' in the WHERE clause);

Count the number of students in each score range for each subject:

select distinct f.c_name, a.c_id,
       b.`85-100`, b.Percentage as `[85-100] Percentage`,
       c.`70-85`, c.Percentage as `[70-85] Percentage`,
       d.`60-70`, d.Percentage as `[60-70] Percentage`,
       e.`0-60`, e.Percentage as `[0-60] Percentage`
from score a
    left join (
        select c_id,
               SUM(case when s_score > 85 and s_score <= 100 then 1 else 0 end) as `85-100`,
               ROUND(100*(SUM(case when s_score > 85 and s_score <= 100 then 1 else 0 end)/count(*)),2) as Percentage
        from score GROUP BY c_id
    ) b on a.c_id = b.c_id
    left join (
        select c_id,
               SUM(case when s_score > 70 and s_score <= 85 then 1 else 0 end) as `70-85`,
               ROUND(100*(SUM(case when s_score > 70 and s_score <= 85 then 1 else 0 end)/count(*)),2) as Percentage
        from score GROUP BY c_id
    ) c on a.c_id = c.c_id
    left join (
        select c_id,
               SUM(case when s_score > 60 and s_score <= 70 then 1 else 0 end) as `60-70`,
               ROUND(100*(SUM(case when s_score > 60 and s_score <= 70 then 1 else 0 end)/count(*)),2) as Percentage
        from score GROUP BY c_id
    ) d on a.c_id = d.c_id
    left join (
        select c_id,
               SUM(case when s_score >= 0 and s_score <= 60 then 1 else 0 end) as `0-60`,
               ROUND(100*(SUM(case when s_score >= 0 and s_score <= 60 then 1 else 0 end)/count(*)),2) as Percentage
        from score GROUP BY c_id
    ) e on a.c_id = e.c_id
    left join course f on a.c_id = f.c_id;

Query average scores and their ranks for students:

select a.s_id,
       @i:=@i+1 as 'No Gaps in Ranking',
       @k:=(case when @avg_score=a.avg_s then @k else @i end) as 'With Gaps in Ranking',
       @avg_score:=avg_s as 'Average Score'
from (select s_id, ROUND(AVG(s_score),2) as avg_s from score GROUP BY s_id ORDER BY avg_s DESC) a,
     (select @avg_score:=0, @i:=0, @k:=0) b;

Query records of the top three students in each subject:

select a.s_id, a.c_id, a.s_score from score a 
    left join score b on a.c_id = b.c_id and a.s_score < b.s_score
    group by a.s_id, a.c_id, a.s_score 
    having count(b.s_id) < 3
    order by a.c_id, a.s_score desc;

Query the number of students enrolled in each course:

select c_id, count(s_id) from score group by c_id;

Query the student ID and name of students who have taken exactly two courses:

select s_id, s_name from student 
    where s_id in (select s_id from score group by s_id having count(c_id) = 2);

Query the number of male and female students:

select s_sex, count(s_sex) as Count from student group by s_sex;

Query student information whose name contains the character "Tom":

select * from student where s_name like '%Tom%';

Query list of students with the same name and gender, and count of such names:

select a.s_name, a.s_sex, count(*) as Count from student a  
    join student b on a.s_id != b.s_id and a.s_name = b.s_name and a.s_sex = b.s_sex
    group by a.s_name, a.s_sex;

Query list of students born in 1990:

select s_name from student where s_birth like '1990%';

Query average scores for each course, ordered by average score descending, and course ID ascending if average scores are the same:

select c_id, round(avg(s_score), 2) as avg_score from score group by c_id order by avg_score desc, c_id asc;

Query student ID, name, and average score of students with average score >= 85:

select a.s_id, b.s_name, round(avg(a.s_score), 2) as avg_score from score a
    left join student b on a.s_id = b.s_id group by s_id having avg_score >= 85;

Query names and scores of students who scored less than 60 in the course "mathematics":

select a.s_name, b.s_score from student a 
    join score b on a.s_id = b.s_id 
    where b.c_id = (select c_id from course where c_name = 'mathematics') 
    and b.s_score < 60;

Query course-wise scores and total scores of all students:

select a.s_id, a.s_name,
    sum(case c.c_name when 'history' then b.s_score else 0 end) as 'history',
    sum(case c.c_name when 'mathematics' then b.s_score else 0 end) as 'mathematics',
    sum(case c.c_name when 'Politics' then b.s_score else 0 end) as 'Politics',
    sum(b.s_score) as 'Total score'
from student a 
left join score b on a.s_id = b.s_id 
left join course c on b.c_id = c.c_id 
group by a.s_id, a.s_name;

Query names, course names, and scores of students scoring above 70 in any course:

select a.s_name, b.c_name, c.s_score from student a 
    left join score c on a.s_id = c.s_id 
    left join course b on c.c_id = b.c_id 
    where c.s_score >= 70;

Query courses where students failed:

select a.s_id, a.c_id, b.c_name, a.s_score from score a 
    left join course b on a.c_id = b.c_id 
    where a.s_score < 60;

Query student ID and name of students who scored above 80 in course '01':

select a.s_id, b.s_name from score a 
    left join student b on a.s_id = b.s_id 
    where a.c_id = '01' and a.s_score > 80;

Count number of students in each course:

select count(*) from score group by c_id;

Query information of the highest scoring student in courses taught by teacher "Tom": -- Get teacher ID

select c_id from course c, teacher d where c.t_id = d.t_id and d.t_name = 'Tom';

-- Get maximum score (could have ties)

select max(s_score) from score where c_id = '02';

-- Get information

select a.*, b.s_score, b.c_id, c.c_name from student a 
    left join score b on a.s_id = b.s_id 
    left join course c on b.c_id = c.c_id 
    where b.c_id = (select c_id from course c, teacher d where c.t_id = d.t_id and d.t_name = 'Tom')
    and b.s_score in (select max(s_score) from score where c_id = '02');

Query student ID, course ID, and score where different courses have the same score:

select distinct b.s_id, b.c_id, b.s_score from score a, score b 
    where a.c_id != b.c_id and a.s_score = b.s_score;

Query top two scores for each course:

select a.s_id, a.c_id, a.s_score from score a 
    where (select count(1) from score b where b.c_id = a.c_id and b.s_score >= a.s_score) <= 2 order by a.c_id;

Count number of students enrolled in each course (courses with more than 5 students):

select c_id, count(*) as total from score group by c_id having total > 5 order by total, c_id asc;

Query student IDs who have enrolled in at least two courses:

select s_id, count(*) as sel from score group by s_id having sel >= 2;

Query information of students who have enrolled in all courses:

select * from student where s_id in (select s_id from score group by s_id having count(*) = (select count(*) from course));

Query age of each student: -- Calculate age based on birthdate; subtract one if current month/day is before birthdate's month/day

select s_birth, (date_format(now(), '%Y') - date_format(s_birth, '%Y') - 
    (case when date_format(now(), '%m%d') > date_format(s_birth, '%m%d') then 0 else 1 end)) as age
    from student;

Query students whose birthday is this week:

select * from student where week(date_format(now(), '%Y%m%d')) = week(s_birth);

Query students whose birthday is next week:

select * from student where week(date_format(now(), '%Y%m%d')) + 1 = week(s_birth);

Query students whose birthday is this month:

select * from student where month(date_format(now(), '%Y%m%d')) = month(s_birth);

Query students whose birthday is next month:

select * from student where month(date_format(now(), '%Y%m%d')) + 1 = month(s_birth);

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

Table Names and Fields（MySQL）

tom — Tue, 30 Jul 2024 02:29:49 +0000

Student Table
Student(s_id, s_name, s_birth, s_sex)
Student ID, Student Name, Date of Birth, Student Gender
Course Table

Course(c_id, c_name, t_id)
Course ID, Course Name, Teacher ID
Teacher Table

Teacher(t_id, t_name)
Teacher ID, Teacher Name
Score Table

Score(s_id, c_id, s_score)
Student ID, Course ID, Score

Test Data - Creating Tables

Student Table

CREATE TABLE  `Student`(  
`s_id`  VARCHAR(20),  
`s_name`  VARCHAR(20) NOT NULL DEFAULT '',  
`s_birth`  VARCHAR(20) NOT NULL DEFAULT '',  
`s_sex`  VARCHAR(10) NOT NULL DEFAULT '',  
PRIMARY KEY(`s_id`)  
);

Course Table

CREATE TABLE  `Course`(  
`c_id`  VARCHAR(20),  
`c_name`  VARCHAR(20) NOT NULL DEFAULT '',  
`t_id`  VARCHAR(20) NOT NULL,  
PRIMARY KEY(`c_id`)  
);

Teacher Table

CREATE TABLE  `Teacher`(  
`t_id`  VARCHAR(20),  
`t_name`  VARCHAR(20) NOT NULL DEFAULT '',  
PRIMARY KEY(`t_id`)  
);

Score Table

CREATE TABLE  `Score`(  
`s_id`  VARCHAR(20),  
`c_id`  VARCHAR(20),  
`s_score`  INT(3),  
PRIMARY KEY(`s_id`,`c_id`)  
);

Inserting Test Data into Student Table

INSERT INTO Student VALUES('01', 'John Doe', '1990-01-01', 'Male');  
INSERT INTO Student VALUES('02', 'Jane Smith', '1990-12-21', 'Male');  
INSERT INTO Student VALUES('03', 'Michael Brown', '1990-05-20', 'Male');  
INSERT INTO Student VALUES('04', 'Emily Davis', '1990-08-06', 'Male');  
INSERT INTO Student VALUES('05', 'Lucy Johnson', '1991-12-01', 'Female');  
INSERT INTO Student VALUES('06', 'Sophia Williams', '1992-03-01', 'Female');  
INSERT INTO Student VALUES('07', 'Olivia Taylor', '1989-07-01', 'Female');  
INSERT INTO Student VALUES('08', 'Victoria King', '1990-01-20', 'Female');

Inserting Test Data into Course Table

INSERT INTO Course VALUES('01', 'Literature', '02');  
INSERT INTO Course VALUES('02', 'Mathematics', '01');  
INSERT INTO Course VALUES('03', 'English', '03');

Inserting Test Data into Teacher Table

INSERT INTO Teacher VALUES('01', 'Andrew');  
INSERT INTO Teacher VALUES('02', 'Bethany');  
INSERT INTO Teacher VALUES('03', 'Charlie');

Transcript Test Data

insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

Exercise questions and SQL statements

Retrieve the information and course scores of students who have a higher score in course '01' than in course '02'

SELECT a.*, b.s_score AS '01_score', c.s_score AS '02_score'  
FROM student a  
JOIN score b ON a.s_id = b.s_id AND b.c_id = '01'  
LEFT JOIN score c ON a.s_id = c.s_id AND c.c_id = '02'  
WHERE b.s_score > COALESCE(c.s_score, 0); -- Using COALESCE instead of OR c.c_id = NULL  

-- Alternatively  
SELECT a.*, b.s_score AS '01_score', c.s_score AS '02_score'  
FROM student a, score b, score c  
WHERE a.s_id = b.s_id  
AND a.s_id = c.s_id  
AND b.c_id = '01'  
AND c.c_id = '02'  
AND b.s_score > c.s_score;

Retrieve the information and course scores of students who have a lower score in course '01' than in course '02'

SELECT a.*, b.s_score AS '01_score', c.s_score AS '02_score'  
FROM student a  
LEFT JOIN score b ON a.s_id = b.s_id AND b.c_id = '01'  
JOIN score c ON a.s_id = c.s_id AND c.c_id = '02'  
WHERE COALESCE(b.s_score, 0) < c.s_score; -- Using COALESCE for clarity

Retrieve student IDs, names, and average scores for students with an average score of 60 or above

SELECT b.s_id, b.s_name, ROUND(AVG(a.s_score), 2) AS avg_score  
FROM student b  
JOIN score a ON b.s_id = a.s_id  
GROUP BY b.s_id, b.s_name  
HAVING AVG(a.s_score) >= 60;

Retrieve student IDs, names, and average scores for students with an average score below 60 (including those with no scores)

SELECT b.s_id, b.s_name, ROUND(AVG(a.s_score), 2) AS avg_score  
FROM student b  
LEFT JOIN score a ON b.s_id = a.s_id  
GROUP BY b.s_id, b.s_name  
HAVING AVG(a.s_score) < 60  
UNION  
SELECT a.s_id, a.s_name, 0 AS avg_score  
FROM student a  
WHERE a.s_id NOT IN (SELECT DISTINCT s_id FROM score);

Retrieve student IDs, names, total courses selected, and total scores across all courses

SELECT a.s_id, a.s_name, COUNT(b.c_id) AS sum_course, SUM(b.s_score) AS sum_score  
FROM student a  
LEFT JOIN score b ON a.s_id = b.s_id  
GROUP BY a.s_id, a.s_name;

Query the number of teachers with the surname "Smith"

SELECT  COUNT(t_id) FROM teacher WHERE t_name LIKE  'Smith%';

Query the information of students who have taken classes taught by Teacher "John Doe"

SELECT a.*  
FROM student a  
JOIN score b ON a.s_id = b.s_id  
WHERE b.c_id IN (  
    SELECT c_id FROM course  
    WHERE t_id = (  
        SELECT t_id FROM teacher  
        WHERE t_name = 'John Doe'  
    )  
);

Query the information of students who have not taken classes taught by Teacher "John Doe"

SELECT *  
FROM student c  
WHERE c.s_id NOT IN (  
    SELECT a.s_id  
    FROM student a  
    JOIN score b ON a.s_id = b.s_id  
    WHERE b.c_id IN (  
        SELECT a.c_id  
        FROM course a  
        JOIN teacher b ON a.t_id = b.t_id  
        WHERE t_name = 'John Doe'  
    )  
);

Query the information of students who have taken both courses with IDs "Math101" and "Science101"

SELECT a.*  
FROM student a, score b, score c  
WHERE a.s_id = b.s_id  
AND a.s_id = c.s_id  
AND b.c_id = 'Math101'  
AND c.c_id = 'Science101';

Query the information of students who have taken the course with ID "Math101" but have not taken the course with ID "Science101"

SELECT a.*
FROM student a
WHERE a.s_id IN (SELECT s_id FROM score WHERE c_id = 'Math101')
AND a.s_id NOT IN (SELECT s_id FROM score WHERE c_id = 'Science101');

Query information of students who have not taken all courses

-- @wendiepei's approach
SELECT s.*
FROM student s
LEFT JOIN Score s1 ON s1.s_id = s.s_id
GROUP BY s.s_id
HAVING COUNT(s1.c_id) < (SELECT COUNT(*) FROM course);
-- @k1051785839's approach
SELECT *  
FROM student  
WHERE s_id NOT IN (  
    SELECT s_id   
    FROM score t1    
    GROUP BY s_id   
    HAVING COUNT(*) = (SELECT COUNT(DISTINCT c_id) FROM course)  
);

Query information of students who have taken at least one course in common with student ID '01'

SELECT *   
FROM student   
WHERE s_id IN (  
    SELECT DISTINCT a.s_id   
    FROM score a   
    WHERE a.c_id IN (  
        SELECT c_id   
        FROM score   
        WHERE s_id = '01'  
    )  
);

Query information of students who have taken exactly the same courses as student ID '01'

SELECT
 t3.*
FROM
 (
  SELECT
   s_id,
   group_concat(c_id ORDER BY c_id) group1
  FROM
   score
  WHERE
   s_id &lt;> '01'
  GROUP BY
   s_id
 ) t1
INNER JOIN (
 SELECT
  group_concat(c_id ORDER BY c_id) group2
 FROM
  score
 WHERE
  s_id = '01'
 GROUP BY
  s_id
) t2 ON t1.group1 = t2.group2
INNER JOIN student t3 ON t1.s_id = t3.s_id

Query the names of students who have not taken any course taught by Teacher "Tom"

select a.s_name from student a where a.s_id not in (
    select s_id from score where c_id = 
                (select c_id from course where t_id =(
                    select t_id from teacher where t_name = 'Tom')));

Query student IDs, names, and average scores of students who have failed two or more courses

SELECT a.s_id, a.s_name, ROUND(AVG(b.s_score), 2) AS average_score  
FROM student a  
LEFT JOIN score b ON a.s_id = b.s_id  
WHERE a.s_id IN (  
    SELECT s_id  
    FROM score  
    WHERE s_score < 60  
    GROUP BY s_id  
    HAVING COUNT(*) >= 2  
)  
GROUP BY a.s_id, a.s_name;

Retrieve student information for students who scored less than 60 on course "01", ordered by score in descending order.

SELECT a.*, b.c_id, b.s_score  
FROM student a  
JOIN score b ON a.s_id = b.s_id  
WHERE b.c_id = '01' AND b.s_score < 60  
ORDER BY b.s_score DESC;

Display the scores of all courses and the average score for each student, ordered by their average score from highest to lowest.

SELECT   
    a.s_id,  
    MAX(CASE WHEN c_id = '01' THEN s_score END) AS Chinese,  
    MAX(CASE WHEN c_id = '02' THEN s_score END) AS Math,  
    MAX(CASE WHEN c_id = '03' THEN s_score END) AS English,  
    ROUND(AVG(s_score), 2) AS average_score  
FROM score a  
GROUP BY a.s_id  
ORDER BY average_score DESC;

Query the highest score, lowest score, average score, pass rate, medium rate, good rate, and excellent rate for each course. Display in the following format: Course ID, Course Name, Highest Score, Lowest Score, Average Score, Pass Rate, Medium Rate, Good Rate, Excellent Rate. -- Pass is >=60, Medium is 70-80, Good is 80-90, Excellent is >=90

SELECT   
    a.c_id,  
    b.c_name,  
    MAX(s_score) AS HighestScore,  
    MIN(s_score) AS LowestScore,  
    ROUND(AVG(s_score), 2) AS AverageScore,  
    ROUND(100 * (SUM(CASE WHEN s_score >= 60 THEN 1 ELSE 0 END) / COUNT(s_score)), 2) AS PassRate,  
    ROUND(100 * (SUM(CASE WHEN s_score BETWEEN 70 AND 80 THEN 1 ELSE 0 END) / COUNT(s_score)), 2) AS MediumRate,  
    ROUND(100 * (SUM(CASE WHEN s_score BETWEEN 80 AND 90 THEN 1 ELSE 0 END) / COUNT(s_score)), 2) AS GoodRate,  
    ROUND(100 * (SUM(CASE WHEN s_score >= 90 THEN 1 ELSE 0 END) / COUNT(s_score)), 2) AS ExcellentRate  
FROM   
    score a   
LEFT JOIN   
    course b ON a.c_id = b.c_id   
GROUP BY   
    a.c_id, b.c_name;

Sort scores by course and display rankings. MySQL does not have a built-in RANK() function, so we'll use variables to simulate it.

SELECT   
    a.s_id,  
    a.c_id,  
    @rank := IF(@prev_score = a.s_score, @rank, @rank + 1) AS rank_without_ties,  
    @prev_score := a.s_score AS score  
FROM   
    (SELECT s_id, c_id, s_score FROM score ORDER BY c_id, s_score DESC) a,  
    (SELECT @rank := 0, @prev_score := NULL) r  
ORDER BY   
    a.c_id, a.rank_without_ties;

Query the total score of each student and rank them

SELECT   
    a.s_id,  
    @rank := IF(@prev_score = a.sum_score, @rank, @rank + 1) AS rank,  
    @prev_score := a.sum_score AS total_score  
FROM   
    (SELECT s_id, SUM(s_score) AS sum_score FROM score GROUP BY s_id ORDER BY sum_score DESC) a,  
    (SELECT @rank := 0, @prev_score := NULL) r  
ORDER BY   
    total_score DESC;

Query the average score of different courses taught by different teachers, sorted from highest to lowest

SELECT   
    a.t_id,  
    c.t_name,  
    a.c_id,  
    ROUND(AVG(s_score), 2) AS avg_score   
FROM   
    course a  
LEFT JOIN   
    score b ON a.c_id = b.c_id   
LEFT JOIN   
    teacher c ON a.t_id = c.t_id  
GROUP BY   
    a.c_id, a.t_id, c.t_name   
ORDER BY   
    avg_score DESC;

Query the information of students who rank second and third in all courses along with their scores

(SELECT   
    d.*,  
    c.ranking,  
    c.s_score,  
    c.c_id  
FROM   
    (SELECT   
        s_id,   
        s_score,   
        c_id,   
        @rank := IF(@prev_cid = c_id, @rank + 1, 1) AS ranking,  
        @prev_cid := c_id  
    FROM   
        score,   
        (SELECT @rank := 0, @prev_cid := NULL) AS var_init  
    WHERE   
        c_id = '01'  
    ORDER BY   
        c_id, s_score DESC  
    ) c  
LEFT JOIN   
    student d ON c.s_id = d.s_id  
WHERE   
    c.ranking BETWEEN 2 AND 3  
)  
UNION  
(SELECT   
    d.*,  
    c.ranking,  
    c.s_score,  
    c.c_id  
FROM   
    (SELECT similar structure as above but with c_id = '02' in the WHERE clause) c  
LEFT JOIN   
    student d ON c.s_id = d.s_id  
WHERE   
    c.ranking BETWEEN 2 AND 3  
)  
UNION  
(SELECT similar structure as above but with c_id = '03' in the WHERE clause);

Count the number of students in each score range for each subject:

select distinct f.c_name, a.c_id,
       b.`85-100`, b.Percentage as `[85-100] Percentage`,
       c.`70-85`, c.Percentage as `[70-85] Percentage`,
       d.`60-70`, d.Percentage as `[60-70] Percentage`,
       e.`0-60`, e.Percentage as `[0-60] Percentage`
from score a
    left join (
        select c_id,
               SUM(case when s_score > 85 and s_score <= 100 then 1 else 0 end) as `85-100`,
               ROUND(100*(SUM(case when s_score > 85 and s_score <= 100 then 1 else 0 end)/count(*)),2) as Percentage
        from score GROUP BY c_id
    ) b on a.c_id = b.c_id
    left join (
        select c_id,
               SUM(case when s_score > 70 and s_score <= 85 then 1 else 0 end) as `70-85`,
               ROUND(100*(SUM(case when s_score > 70 and s_score <= 85 then 1 else 0 end)/count(*)),2) as Percentage
        from score GROUP BY c_id
    ) c on a.c_id = c.c_id
    left join (
        select c_id,
               SUM(case when s_score > 60 and s_score <= 70 then 1 else 0 end) as `60-70`,
               ROUND(100*(SUM(case when s_score > 60 and s_score <= 70 then 1 else 0 end)/count(*)),2) as Percentage
        from score GROUP BY c_id
    ) d on a.c_id = d.c_id
    left join (
        select c_id,
               SUM(case when s_score >= 0 and s_score <= 60 then 1 else 0 end) as `0-60`,
               ROUND(100*(SUM(case when s_score >= 0 and s_score <= 60 then 1 else 0 end)/count(*)),2) as Percentage
        from score GROUP BY c_id
    ) e on a.c_id = e.c_id
    left join course f on a.c_id = f.c_id;

Query average scores and their ranks for students:

select a.s_id,
       @i:=@i+1 as 'No Gaps in Ranking',
       @k:=(case when @avg_score=a.avg_s then @k else @i end) as 'With Gaps in Ranking',
       @avg_score:=avg_s as 'Average Score'
from (select s_id, ROUND(AVG(s_score),2) as avg_s from score GROUP BY s_id ORDER BY avg_s DESC) a,
     (select @avg_score:=0, @i:=0, @k:=0) b;

Query records of the top three students in each subject:

select a.s_id, a.c_id, a.s_score from score a 
    left join score b on a.c_id = b.c_id and a.s_score < b.s_score
    group by a.s_id, a.c_id, a.s_score 
    having count(b.s_id) < 3
    order by a.c_id, a.s_score desc;

Query the number of students enrolled in each course:

select c_id, count(s_id) from score group by c_id;

Query the student ID and name of students who have taken exactly two courses:

select s_id, s_name from student 
    where s_id in (select s_id from score group by s_id having count(c_id) = 2);

Query the number of male and female students:

select s_sex, count(s_sex) as Count from student group by s_sex;

Query student information whose name contains the character "Tom":

select * from student where s_name like '%Tom%';

Query list of students with the same name and gender, and count of such names:

select a.s_name, a.s_sex, count(*) as Count from student a  
    join student b on a.s_id != b.s_id and a.s_name = b.s_name and a.s_sex = b.s_sex
    group by a.s_name, a.s_sex;

Query list of students born in 1990:

select s_name from student where s_birth like '1990%';

Query average scores for each course, ordered by average score descending, and course ID ascending if average scores are the same:

select c_id, round(avg(s_score), 2) as avg_score from score group by c_id order by avg_score desc, c_id asc;

Query student ID, name, and average score of students with average score >= 85:

select a.s_id, b.s_name, round(avg(a.s_score), 2) as avg_score from score a
    left join student b on a.s_id = b.s_id group by s_id having avg_score >= 85;

Query names and scores of students who scored less than 60 in the course "mathematics":

select a.s_name, b.s_score from student a 
    join score b on a.s_id = b.s_id 
    where b.c_id = (select c_id from course where c_name = 'mathematics') 
    and b.s_score < 60;

Query course-wise scores and total scores of all students:

select a.s_id, a.s_name,
    sum(case c.c_name when 'history' then b.s_score else 0 end) as 'history',
    sum(case c.c_name when 'mathematics' then b.s_score else 0 end) as 'mathematics',
    sum(case c.c_name when 'Politics' then b.s_score else 0 end) as 'Politics',
    sum(b.s_score) as 'Total score'
from student a 
left join score b on a.s_id = b.s_id 
left join course c on b.c_id = c.c_id 
group by a.s_id, a.s_name;

Query names, course names, and scores of students scoring above 70 in any course:

select a.s_name, b.c_name, c.s_score from student a 
    left join score c on a.s_id = c.s_id 
    left join course b on c.c_id = b.c_id 
    where c.s_score >= 70;

Query courses where students failed:

select a.s_id, a.c_id, b.c_name, a.s_score from score a 
    left join course b on a.c_id = b.c_id 
    where a.s_score < 60;

Query student ID and name of students who scored above 80 in course '01':

select a.s_id, b.s_name from score a 
    left join student b on a.s_id = b.s_id 
    where a.c_id = '01' and a.s_score > 80;

Count number of students in each course:

select count(*) from score group by c_id;

Query information of the highest scoring student in courses taught by teacher "Tom": -- Get teacher ID

select c_id from course c, teacher d where c.t_id = d.t_id and d.t_name = 'Tom';

-- Get maximum score (could have ties)

select max(s_score) from score where c_id = '02';

-- Get information

select a.*, b.s_score, b.c_id, c.c_name from student a 
    left join score b on a.s_id = b.s_id 
    left join course c on b.c_id = c.c_id 
    where b.c_id = (select c_id from course c, teacher d where c.t_id = d.t_id and d.t_name = 'Tom')
    and b.s_score in (select max(s_score) from score where c_id = '02');

Query student ID, course ID, and score where different courses have the same score:

select distinct b.s_id, b.c_id, b.s_score from score a, score b 
    where a.c_id != b.c_id and a.s_score = b.s_score;

Query top two scores for each course:

select a.s_id, a.c_id, a.s_score from score a 
    where (select count(1) from score b where b.c_id = a.c_id and b.s_score >= a.s_score) <= 2 order by a.c_id;

Count number of students enrolled in each course (courses with more than 5 students):

select c_id, count(*) as total from score group by c_id having total > 5 order by total, c_id asc;

Query student IDs who have enrolled in at least two courses:

select s_id, count(*) as sel from score group by s_id having sel >= 2;

Query information of students who have enrolled in all courses:

select * from student where s_id in (select s_id from score group by s_id having count(*) = (select count(*) from course));

Query age of each student: -- Calculate age based on birthdate; subtract one if current month/day is before birthdate's month/day

select s_birth, (date_format(now(), '%Y') - date_format(s_birth, '%Y') - 
    (case when date_format(now(), '%m%d') > date_format(s_birth, '%m%d') then 0 else 1 end)) as age
    from student;

Query students whose birthday is this week:

select * from student where week(date_format(now(), '%Y%m%d')) = week(s_birth);

Query students whose birthday is next week:

select * from student where week(date_format(now(), '%Y%m%d')) + 1 = week(s_birth);

Query students whose birthday is this month:

select * from student where month(date_format(now(), '%Y%m%d')) = month(s_birth);

Query students whose birthday is next month:

select * from student where month(date_format(now(), '%Y%m%d')) + 1 = month(s_birth);

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

The Most Comprehensive Summary of SQL Fundamentals Ever (Theory + Examples)

tom — Thu, 25 Jul 2024 08:55:58 +0000

SQL Overview
- What is SQL
- Syntax Requirements
SQL Classification
DDL（Data Definition Language）
- Basic Operations
  - Operating Databases
  - Data Types
  - Operating Tables
DML（Data Manipulation Language）
- Inserting Data
- Modifying Data
- Deleting Data
DCL（Data Control Language）
- Creating Users
- Granting Permissions
- Revoking Permissions
- Viewing User Permissions
- Deleting Users
- Modifying User Passwords
DQL（Data Query Language）
- Creating Database: Info
  - Basic Queries
  - Conditional Queries
  - Fuzzy Queries
  - Field Control Queries
  - Sorting
  - Aggregate Functions
  - Group By Queries
  - LIMIT
  - Joining Multiple Tables

1. SQL Overview

1.1 What is SQL

SQL (Structured Query Language) is a language used for managing relational databases. It can be applied to all relational databases, such as MySQL, Oracle, SQL Server, etc. The SQL standard (ANSI/ISO) includes:

SQL-92: SQL language standard released in 1992.
SQL:1999: SQL language standard released in 1999.
SQL:2003: SQL language standard released in 2003.

These standards, like versions of the JDK, always have syntax changes in new versions. Databases at different times have implemented different standards.

Although SQL can be used in all relational databases, many databases have some syntax after the standard, which we can call "dialects." For example, the LIMIT statement in MySQL is unique to MySQL, and other databases do not support it! Of course, Oracle or SQL Server also have their own dialects.

1.2 Syntax Requirements

SQL statements can be written on one or more lines, ending with a semicolon.
Spaces and indentation can enhance the readability of statements.
Keywords are case-insensitive, but uppercase is recommended.

2.SQL Classification

DDL (Data Definition Language): Used to define database objects: databases, tables, columns, etc.
DML (Data Manipulation Language): Used to define database records (data).
DCL (Data Control Language): Used to define access permissions and security levels.
DQL (Data Query Language): Used to query records (data).

3. DDL（Data Definition Language）

3.1 Basic Operations

Show all databases: SHOW DATABASES;
Switch databases: USE mydb1;
3.1.1 Operating Databases

Create a database: CREATE DATABASE [IF NOT EXISTS] mydb1;
Example: CREATE DATABASE mydb1;creates a database named mydb1. If this database already exists, an error will occur.

- Example: `CREATE DATABASE IF NOT EXISTS mydb1;` creates the database mydb1 if it does not exist, avoiding errors.

Delete a database: DROP DATABASE [IF EXISTS] mydb1;
Example: DROP DATABASE mydb1; deletes the database named mydb1. If this database does not exist, an error will occur.
Example: DROP DATABASE IF EXISTS mydb1; does not report errors even if mydb1 does not exist.
Modify database encoding: ALTER DATABASE mydb1 CHARACTER SET utf8;
Modify the encoding of the mydb1 database to utf8. Note that in MySQL, all UTF-8 encodings cannot use the middle "-", so UTF-8 should be written as UTF8.

3.1.2 Data Types

MySQL, like Java and C, also has data types mainly applied to columns. Common types:

int: integer
double: floating point type, for example, double(5,2) indicates up to 5 digits, of which 2 must be decimals, i.e., the maximum value is 999.99;
decimal: generic type, used in form wire aspects, because there is no problem of accuracy missing;
char: fixed-length string type; (When the input character is not enough in length, it will be padded with spaces)
varchar: fixed-length string type;
text: string type;
blob: byte type;
date: date type, format: yyyy-MM-dd;
time: time type, format: hh:mm:ss;
timestamp: timestamp type;

3.1.3 Operating Tables

Create a table

CREATE TABLE table_name( 
column_name column_type, 
column_name column_type,
 ... );

Create stu table

 CREATE TABLE stu(
    sid CHAR(6), 
    sname VARCHAR(20), 
    age INT, 
    gender VARCHAR(10)
);

View the structure of the table
DESC table_name;

Delete table
DROP TABLE table_name;

Modify the table

Add a column: Add the classname column to the stu table
ALTER TABLE stu ADD (classname varchar(100));
Modify the data type of a column: Change the gender column type of the stu table to CHAR(2)
ALTER TABLE stu MODIFY gender CHAR(2);
Rename a column: Change the gender column name of the stu table to sex
ALTER TABLE stu change gender sex CHAR(2);
Delete a column: Delete the classname column from the stu table
ALTER TABLE stu DROP classname;
Modify the table name: Change the stu table name to student
ALTER TABLE stu RENAME TO student;

4. DML（Data Manipulation Language）

4.1 Inserting Data

Syntax 1：INSERT INTO table_name(column_name1, column_name2, ...) VALUES(value1, value2, ...);

INSERT INTO stu(sid, sname,age,gender) VALUES('s_1001', 'Tom', 23, 'male');

INSERT INTO stu(sid, sname) VALUES('s_1001', 'Tom');

Syntax 2: INSERT INTO table_name VALUES(value1, value2, ...);
Since no specific column is specified to insert, all column values in the order they were created in the table are used:

INSERT INTO stu VALUES('s_1002', 'liSi', 32, 'female');

4.2 Modifying Data

Syntax:UPDATE 表名 SET 列名 1=值 1, … 列名 n=值 n [WHERE 条件];

UPDATE stu SET sname=’zhangSanSan’, age=’32’, gender=’female’ WHERE sid=’s_1001’;
UPDATE stu SET sname=’liSi’, age=’20’WHERE age>50 AND gender=’male’;
UPDATE stu SET sname=’wangWu’, age=’30’WHERE age>60 OR gender=’female’;
UPDATE stu SET gender=’female’WHERE gender IS NULL
UPDATE stu SET age=age+1 WHERE sname=’zhaoLiu’;

4.3 Deleting Data

Syntax 1: DELETE FROM table_name [WHERE condition];

DELETE FROM stu WHERE sid=’s_1001’003B;
DELETE FROM stu WHERE sname=’chenQi’ OR age > 30;
DELETE FROM stu;

Syntax 2: TRUNCATE TABLE table_name;

TRUNCATE TABLE stu;

The difference between the two:
Although TRUNCATE and DELETE can delete all records in a table, they have different principles. The efficiency of DELETE is not as high as TRUNCATE!

TRUNCATE is actually a DDL statement because it first drops the table and then recreates it. Moreover, records deleted by TRUNCATE cannot be rolled back, but records deleted by DELETE can be rolled back (rollback is knowledge of transactions!).

5. DCL（Data Control Language）

5.1 Creating Users

Syntax: CREATE USER 'username'@host IDENTIFIED BY 'password';

CREATE USER ‘user1’@localhost IDENTIFIED BY ‘123’;
CREATE USER ‘user2’@’%’ IDENTIFIED BY ‘123’;

5.2 Granting Permissions

Syntax: GRANT permission1, ..., permissionN ON database.* TO 'username'@host;

GRANT  CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT  ON mydb1.*  TO  'user1'@localhost;  
GRANT  ALL  ON mydb1.*  TO user2@localhost;

5.3 Revoking Permissions

Syntax: REVOKE permission1, ..., permissionN ON database.* FROM 'username'@host;

REVOKE CREATE,ALTER,DROP ON mydb1.* FROM 'user1'@localhost;

5.4 Viewing User Permissions

Syntax: SHOW GRANTS FOR 'username'@host;

SHOW GRANTS FOR 'user1'@localhost;

5.5 Deleting Users

Syntax: DROP USER 'username'@host;

DROP USER ‘user1’@localhost;

5.6 Modifying User Passwords

Syntax:
SET PASSWORD FOR 'username'@host = PASSWORD('new_password');

6. DQL（Data Query Language）

Executing DQL statements in a database does not change data, but instead sends the result set to the client.

select column_name ----> specifies the columns to retrieve
from table_name ----> specifies the table from which to retrieve data
where condition ----> specifies rows to retrieve based on a condition
group by group_column ----> groups the results based on a column
having group_condition ----> specifies which grouped rows to include
order by sort_column ----> sorts the results
limit start_row, row_count ----> limits the number of rows returned

Creating Database

Student Table: stu

Column Name	Data Type	Description
sid	char(6)	Student ID
sname	varchar(50)	Student name
age	int	Student age
gender	varchar(50)	Student gender

CREATE TABLE stu ( 
sid CHAR(6), 
sname VARCHAR(50),
 age INT, 
 gender VARCHAR(50) 
 ); 
 INSERT INTO stu VALUES('S_1001', 'Ethan Brown', 35, 'male'); 
 INSERT INTO stu VALUES('S_1002', 'Olivia Taylor', 15, 'female'); 
 INSERT INTO stu VALUES('S_1003', 'Liam Johnson', 95, 'male'); 
 INSERT INTO stu VALUES('S_1004', 'Emma Martinez', 65, 'female'); 
 INSERT INTO stu VALUES('S_1005', 'Noah Anderson', 55, 'male'); 
 INSERT INTO stu VALUES('S_1006', 'Ava Garcia', 75, 'female'); 
 INSERT INTO stu VALUES('S_1007', 'William Davis', 25, 'male'); 
 INSERT INTO stu VALUES('S_1008', 'Sophia Wilson', 45, 'female'); 
 INSERT INTO stu VALUES('S_1009', 'James Clark', 85, 'male'); 
 INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female'); 
 INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);

Employee Table: emp

Column Name	Data Type	Description
empno	int	Employee number
ename	varchar(50)	Employee name
job	varchar(50)	Employee job
mgr	int	Manager's number
hiredate	date	Hire date
sal	decimal(7,2)	Monthly salary
comm	decimal(7,2)	Commission
deptno	int	Department number

CREATE TABLE emp(
    empno INT, 
    ename VARCHAR(50), 
    job VARCHAR(50), 
    mgr INT, 
    hiredate DATE,
    sal DECIMAL(7,2), 
    comm decimal(7,2), 
    deptno INT
) ;
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

Department Table: dept

Column Name	Data Type	Description
deptno	int	Department code
dname	varchar(50)	Department name
loc	varchar(50)	Department location

CREATE TABLE dept(
    deptno INT, 
    dname varchar(14),
    loc varchar(13)
);
INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS',

6.1 Basic Queries

Query all columns: SELECT * FROM table_name;
(*: wildcard, represents all columns)

SELECT * FROM stu;

Query specific columns: SELECT column_name1, column_name2, ... column_nameN FROM table_name;

SELECT sid, sname, age FROM stu;

6.2 Conditional Queries

Conditional queries involve using a WHERE clause with the following operators and keywords:

=、!=、<>、<、<=、>、>=；
BETWEEN…AND；
IN(set)；
IS NULL；
AND；
OR；
NOT；

Examples:
Query records where gender is female and age is less than 50:
SELECT * FROM stu WHERE gender='female' AND age<50;

Query records where student ID is S_1001 or name is liSi:
SELECT * FROM stu WHERE sid ='S_1001' OR sname='liSi';

Query records where student ID is S_1001, S_1002, or S_1003:
SELECT * FROM stu WHERE sid IN ('S_1001','S_1002','S_1003')

Query records where student ID is not S_1001, S_1002, or S_1003:
SELECT * FROM stu WHERE sid NOT IN ('S_1001','S_1002','S_1003');

Query records where age is null:
SELECT * FROM stu WHERE age IS NULL;

Query student records with age between 20 and 40:
SELECT * FROM stu WHERE age>=20 AND age<=40;
or
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;

Query student records where gender is not male:
SELECT * FROM stu WHERE gender!='male';
or
SELECT * FROM stu WHERE gender<>'male';
or
SELECT * FROM stu WHERE NOT gender='male';

Query student records where name is not null:
SELECT * FROM stu WHERE NOT sname IS NULL;
or
SELECT * FROM stu WHERE sname IS NOT NULL;

6.3 Fuzzy Queries

SELECT fields FROM table WHERE some_field LIKE condition
SQL provides two matching patterns for conditions:
%: Represents any 0 or more characters. It can match any type and length of characters. In some cases, for Chinese characters, please use double percent signs (%%).
_ : Represents any single character. It matches a single arbitrary character and is often used to limit the length of expression statements.

Examples:
Query student records with names consisting of 5 letters:
SELECT * FROM stu WHERE sname LIKE '_ _ _ _ _';

Query student records with names consisting of 5 letters and the fifth letter is "i":
SELECT * FROM stu WHERE sname LIKE '_ _ _ _i';

Query student records where names start with "z":
SELECT * FROM stu WHERE sname LIKE 'z%';
Here, "%" matches 0 to n characters.

Query student records where the second letter in the name is "i":
SELECT * FROM stu WHERE sname LIKE '_i%';

Query student records where the name contains the letter "a":
SELECT * FROM stu WHERE sname LIKE '%a%';

6.4 Field Control Queries

Remove duplicate records
To eliminate duplicate records based on the sal field in the emp table, you can use the DISTINCT keyword as follows:

SELECT DISTINCT sal FROM emp;

This query will return only unique values from the sal column of the emp table, removing any duplicates.

View the sum of employees' monthly salary and commission.
Because both sal and comm columns are numeric types, they can be added together. If either sal or comm contains a non-numeric value, an error will occur.
SELECT *,sal + comm FROM emp;
The comm column has many records with NULL values. Because adding anything to NULL results in NULL, the calculation result may be NULL. Below, the IFNULL function is used to convert NULL to the numeric value 0:
SELECT *, sal+IFNULL(comm,0) FROM emp;

Give alias to column names:
In the above query, the column name sal+IFNULL(comm,0) appears, which is not aesthetically pleasing. Now, let's give an alias to this column, named total:
SELECT *, sal+IFNULL(comm,0) AS total FROM emp;
When assigning aliases to columns, you can omit the AS keyword:
SELECT *, sal+IFNULL(comm,0) total FROM emp;

6.5 Sorting

Query all student records, sorted in ascending order by age:
SELECT * FROM stu ORDER BY sage ASC;
or
SELECT * FROM stu ORDER BY sage;

Query all student records, sorted in descending order by age:
SELECT * FROM stu ORDER BY age DESC;

Query all employees, sorted in descending order by salary. If salaries are the same, then sort by employee number in ascending order:
SELECT * FROM emp ORDER BY sal DESC ,empno ASC;

6.6 Aggregate Functions

Aggregate functions are used for vertical operations:

COUNT(): Counts the number of rows where the specified column is not NULL.
MAX(): Computes the maximum value of the specified column. If the column is of string type, it uses string sorting.
MIN(): Computes the minimum value of the specified column. If the column is of string type, it uses string sorting.
SUM(): Computes the sum of numeric values in the specified column. If the column type is not numeric, the result is 0.
AVG(): Computes the average value of the specified column. If the column type is not numeric, the result is 0.

COUNT: COUNT() is used for vertical counting operations.

Query the number of records in the emp table:
SELECT COUNT(*) AS cnt FROM emp;

Query the number of employees in the emp table who have commission:
SELECT COUNT(comm) cnt FROM emp;
Note: COUNT(comm) counts the number of non-NULL values in the comm column.

Query the number of employees in the emp table whose monthly salary is greater than 2500:
SELECT COUNT(*) FROM emp WHERE sal > 2500;

Count the number of employees whose total monthly salary and commission sum is greater than 2500:
SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;

Query the count of employees with commission and the count of employees with a manager:
SELECT COUNT(comm), COUNT(mgr) FROM emp;

SUM and AVG: SUM() and AVG() are used for vertical sum and average operations.

Query the total salary of all employees:
SELECT SUM(sal) FROM emp;

Query the total salary and total commission of all employees:
SELECT SUM(sal), SUM(comm) FROM emp;

Query the total of monthly salary + commission of all employees:
SELECT SUM(sal+IFNULL(comm,0)) FROM emp;

Calculate the average salary of all employees:
SELECT SUM(sal), COUNT(sal) FROM emp;
or
SELECT AVG(sal) FROM emp;

MAX and MIN

Query the highest and lowest salary:

SELECT MAX(sal), MIN(sal) FROM emp;

6.7 Grouping Queries

Grouping Queries

When conducting grouping queries, you need to use the GROUP BY clause. For example, to query the total salary for each department, you need to group by department.

Query the department number and the total salary for each department:

SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno;

Query the department number and the count of employees in each department:

SELECT deptno,COUNT(*)
FROM emp
GROUP BY deptno;

Query the department number and the count of employees whose salary is greater than 1500 in each department:

SELECT deptno ,COUNT(*)
FROM emp
WHERE sal>1500`
GROUP BY deptno;

HAVING Clause:

Query the department number and the total salary for departments where the total salary is greater than 9000:

SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal) > 9000;

Note: WHERE clause filters records before grouping, so rows that do not meet the WHERE conditions are excluded from grouping. HAVING clause filters data after grouping.

6.8 LIMIT

To query 5 rows, starting from row 0:
SELECT * FROM emp LIMIT 0, 5;
Note: Starting from row 0 means starting from the first row!

To query 10 rows, starting from row 3:
SELECT * FROM emp LIMIT 3, 10;

If there are 10 records per page, and you want to view the records on the 3rd page:

The starting row for the first page is 0, querying a total of 10 rows.
The starting row for the second page is 10, querying a total of 10 rows.
The starting row for the third page is 20, querying a total of 10 rows.

6.9 Joining Multiple Tables: Inner and Outer Joins

Table joins are divided into inner joins and outer joins. The main difference between them is that an inner join selects only the records that have matching values in both tables, whereas an outer join includes unmatched records from one or both tables.

For example, consider the staff table and the department table:
|id|name|deptname|
|----|----|----|
|1|Steve|tech|
|2|Jobs|seal|
|3|Drake|tech|
|4|Tom|seal|
|5|Jay|tech|
|6|Ll|hr|
|7|Chuan|ceo|
|8|Anna|seal|
|9|Satan|driver|

Inner Join

select staff.name,deptname from staff,deptno where staff.name=deptno.name;

id	name	deptname
1	Steve	tech
2	Jobs	seal
3	Drake	tech
4	Tom	seal
5	Jay	tech
6	Ll	hr
7	Chuan	ceo
8	Anna	seal
9	Satan	driver

Outer Joins: Left Join and Right Join

Left Join: Includes all records from the left table, and unmatched records from the right table appear as NULL.

Right Join: Includes all records from the right table, and unmatched records from the left table appear as NULL.

Outer Join (Left Join)：

select staff.name,deptname from staff left  join deptno onstaff.name=deptno.name;

id	name	deptname
1	Steve	tech
2	Jobs	seal
3	Drake	tech
4	Tom	seal
5	Jay	tech
6	Ll	hr
7	Chuan	ceo
8	Anna	seal
9	Satan

Outer Join (Right Join):

select deptname,deptno.name from staff right join deptno ondeptno.name=staff.name;

id	name	deptname
1	Steve	tech
2	Jobs	seal
3	Drake	tech
4	Tom	seal
5	Jay	tech
6	Ll	hr
7	Chuan	ceo
8	Anna	seal
9	Satan

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

Tell me, like I'm 5, why SQLynx is better than Data Grip?

tom — Mon, 22 Jul 2024 03:43:08 +0000

SQLynx has a simple and clear interface that helps users quickly understand its functions. It's easier to learn, and it's permanently free for personal users and educators.

SQLynx supports multiple operating systems, including Windows, Linux, and Mac OS, making it highly compatible across platforms.

It features a powerful query editor with SQL syntax highlighting, auto-complete, and code folding.

Additionally, SQLynx offers functionalities such as data editing, import/export, test data generation, database backup and restore, and one-click SQL statement generation, catering to various user needs.

SQLynx demonstrates stability and high performance when handling large volumes of SQL queries, which is crucial for users dealing with extensive data. For instance, it exports large datasets (like 13 million records) in just 74 seconds, showcasing its excellent performance.

If you're interested, you can download and use SQLynx for free at www.sqlynx.com.

Why is it recommended that beginners learn SQL rather than Python?

tom — Thu, 04 Jul 2024 08:01:50 +0000

SQLStructured Query Language
Purpose: SQL is a standard programming language used to manage and operate relational database systems. It allows users to create, modify, retrieve, and delete data stored in the database.
Importance: SQL is crucial for any system or application that needs to handle large amounts of data. Whether it's web applications, data analysis, data science, or database management, mastering SQL is essential. It provides the ability to directly access and manipulate data in a database, and is the cornerstone of data management and analysis.
Career path: For database administrators (DBAs), data analysts, data scientists, and other roles, SQL is one of the core skills.
Python
Purpose: Python is a high-level programming language known for its concise syntax, rich libraries, and strong community support. It is widely used in many fields such as web development, data analysis, artificial intelligence, machine learning, automated scripting, and more.
Importance: Python has become the preferred language in many fields due to its ease of use and powerful ecosystem. In the field of data science, Python is particularly popular due to its powerful data processing libraries such as Pandas and NumPy, as well as machine learning libraries such as Scikit-learn, TensorFlow, and PyTorch. In addition, Python is widely used for automating tasks and web development through frameworks such as Django and Flask.
Career Path: Python provides developers with a wide range of career options, including but not limited to data scientists, machine learning engineers, web developers, automated test engineers, and more.
conclusion
Therefore, it is impossible to simply say which is more important, SQL or Python. They each play a key role in different fields and scenarios. For most technical professionals, mastering both is highly valuable. If you are looking for a comprehensive skill set, it would be beneficial to learn SQL to handle databases and data, while learning Python to leverage its rich libraries and powerful ecosystem for programming and data analysis.

In general, choosing which technology to learn should be based on your interests, career goals, and project needs. In many cases, mastering both SQL and Python will open up more opportunities and possibilities for you.

A must-have for database entry, a complete collection of SQL statements

tom — Thu, 04 Jul 2024 06:23:33 +0000

Data Definition Language (DDL)
DDL is used to define and modify database structures.

CREATE DATABASE: Create a new database.
DROP DATABASE: Delete a database.
CREATE TABLE: Create a new table.
ALTER TABLE: Modify the table structure, such as adding, deleting, or modifying columns.
DROP TABLE: Delete a table.
CREATE INDEX: Create an index to improve query efficiency.
DROP INDEX: Deletes an index.
Data Manipulation Language DML
DML is used to add, delete, and modify data in the database.

INSERT INTO: Inserts new data into a table.
UPDATE: Update the data in the table.
DELETE: Delete data from the table.
Data Query Language DQL
DQL is mainly used to query data in the database.

SELECT: Select data from the database. It can be used with various clauses, such as WHERE condition filtering, ORDER BY sorting, GROUP BY grouping, and so on.
JOIN: Combines rows from two or more tables based on common fields between these tables.
Data Control Language DCL
DCL is used to define the access permissions and security levels for databases, tables, and fields.

GRANT: Grant user permissions.
REVOKE: Recover user permissions.
transaction control statement
BEGIN TRANSACTION or START TRANSACTION: Start a transaction.
COMMIT: Submits the current transaction, making all changes since BEGIN TRANSACTION permanent.
ROLLBACK: Roll back the current transaction, canceling all changes since BEGIN TRANSACTION.

Recommended 5 SQL tools most suitable for beginners to get started

tom — Mon, 01 Jul 2024 08:06:32 +0000

SQLynx

Reason for recommendation: SQLynx is a powerful and user-friendly web-based database management tool. It features an intuitive web interface and supports multiple database types, making it suitable for both beginners and professionals. SQLynx offers real-time collaboration, an intelligent SQL editor, and security features, providing users with a comprehensive database management experience.

MySQL Workbench

Reason for recommendation: MySQL Workbench is a database design and management tool officially released by MySQL. It supports database modeling, SQL development, management, and maintenance, offering an intuitive interface and rich functionality ideal for learning and practicing MySQL database skills.

Oracle SQL Developer

Reason for recommendation: Oracle SQL Developer is a free database integrated development environment (IDE) from Oracle. It supports Oracle databases and others, providing powerful features for SQL development, debugging, performance tuning, and version control. It is suitable for learning and practicing SQL language and database management skills.

This SQL tool has finally been updated!

tom — Fri, 28 Jun 2024 09:03:23 +0000

Before July 30th, sqlynx will add connections to 4 databases. Please stay tuned

DEV Community: tom

Essential PSQL 50 Selected Practice Questions with Answers

Environment Setup（PGsql）

t_course Table

t_score Table

be careful

Practice Starts

Essential MtSQL 50 Selected Practice Questions with Answers

Table Names and Fields（MySQL）

Test Data - Creating Tables

Exercise questions and SQL statements

How to check the MySQL version on Windows, so easy that even a five-year-old can learn it

Contents

Why do you need to know your MySQL version?

Check the MySQL Version

From the Command Line

From the MySQL Client

SELECT VERSION Statement

STATUS Command

SHOW VARIABLES LIKE Statement

Essential Oracle 50 Selected Practice Questions with Answers

Essential MySQL 50 Selected Practice Questions with Answers

Table Names and Fields（MySQL）

Test Data - Creating Tables

Exercise questions and SQL statements

Table Names and Fields（MySQL）

Test Data - Creating Tables

Exercise questions and SQL statements

The Most Comprehensive Summary of SQL Fundamentals Ever (Theory + Examples)

Contents

1. SQL Overview

1.1 What is SQL

1.2 Syntax Requirements

2.SQL Classification

3. DDL（Data Definition Language）

3.1 Basic Operations

3.1.1 Operating Databases

- Example: CREATE DATABASE IF NOT EXISTS mydb1; creates the database mydb1 if it does not exist, avoiding errors.

3.1.2 Data Types

3.1.3 Operating Tables

4. DML（Data Manipulation Language）

4.1 Inserting Data

4.2 Modifying Data

4.3 Deleting Data

5. DCL（Data Control Language）

5.1 Creating Users

5.2 Granting Permissions

5.3 Revoking Permissions

5.4 Viewing User Permissions

5.5 Deleting Users

5.6 Modifying User Passwords

6. DQL（Data Query Language）

Creating Database

6.1 Basic Queries

6.2 Conditional Queries

6.3 Fuzzy Queries

6.4 Field Control Queries

6.5 Sorting

6.6 Aggregate Functions

6.7 Grouping Queries

6.8 LIMIT

6.9 Joining Multiple Tables: Inner and Outer Joins

Tell me, like I'm 5, why SQLynx is better than Data Grip?

Why is it recommended that beginners learn SQL rather than Python?

A must-have for database entry, a complete collection of SQL statements

Recommended 5 SQL tools most suitable for beginners to get started

This SQL tool has finally been updated!

- Example: `CREATE DATABASE IF NOT EXISTS mydb1;` creates the database mydb1 if it does not exist, avoiding errors.