SQL basic knowledge sorting

• select query results, such as:   [student number, average score: group function AVG (score)]

• from which table to find data, such as: [related to grade: grade table score]

• where query criteria, such as: [b. course number = '0003' and b. score > 80]

• Group by grouping, such as: [average of each student: grouping by student number] (appears in select in oracle,SQL server)   The non grouping function after the group by clause must appear after the group by clause. It can not be used in MySQL

• having specifies conditions for grouping results, such as: [more than 60 points]

• order by sorts the query results, such as: [add order: score   ASC / descending: grade DESC];

• limit   Use the limt clause to return topN (corresponding to the top two scores returned by this question), such as: [limit]   2 = = > read 2 from 0 index]limit = = > start from 0 index   [0,N-1]

select * from table limit 2,1;                
 
-- It means to skip 2 and take out 1 data, limit After that, start reading from item 2 and read one piece of information, that is, read the third piece of data
 
select * from table limit 2 offset 1;     
 
-- It means to take out 2 pieces of data from the first (excluding) data, limit Followed by two pieces of data, offset The following is read from item 1, that is, read item 2,3 strip

Group function:   De duplication distinct()    Total statistics (sum)     count()   Average avg()   Max (min)

Multi table connection:   Inner join (omitting the default inner join)  ... on.. left join   tableName as b   on a.key ==b.key right join   Connect Union (no duplication (filter de duplication)) and union all (duplicate [no filter de duplication])

• union Union

• Union all (duplicate)

oracle(SQL server) database

• intersect intersection

• minus(except)

oracle

1, Database objects: tables   View   Sequence   Index   Synonym (synonym)

1. View: stored select statement

create view emp_vw
as
select employee_id, last_name, salary
from employees
where department_id = 90;

select * from emp_vw;

You can perform DML operations on simple views

update emp_vw
set last_name = 'HelloKitty'
where employee_id = 100;

select * from employees
where employee_id = 100;

1) 2. Complex view

create view emp_vw2
as
select department_id, avg(salary) avg_sal
from employees
group by department_id;

select * from emp_vw2;

Complex views cannot be DML operated

update emp_vw2
set avg_sal = 10000
where department_id = 100;

2. Sequence: used to generate a set of regular values. (usually used to set values for primary keys)

create sequence emp_seq1
start with 1
increment by 1
maxvalue 10000
minvalue 1
cycle
nocache;

select emp_seq1.currval from dual;

select emp_seq1.nextval from dual;

Problem: crack, cause:

• When multiple tables share the same sequence.

• rollback

• exception occurred

create table emp1(
       id number(10),
       name varchar2(30)
);

insert into emp1
values(emp_seq1.nextval, 'Zhang San');

select * from emp1;

3. Index: improve query efficiency

Auto create: Oracle will automatically create indexes for columns with unique constraints (unique constraints, primary key constraints)

create table emp2(
       id number(10) primary key,
       name varchar2(30)
)

Create manually

create index emp_idx
on emp2(name);

create index emp_idx2
on emp2(id, name);

4. Synonyms

create synonym d1 for departments;

select * from d1;

5. Table:

DDL: Data Definition Language   create table .../ drop table ... / rename ... to..../ truncate table.../alter table ...

DML: Data Manipulation Language

insert into ... values ...
update ... set ... where ...
delete from ... where ...

[important]

• select ...   Group function (MIN()/MAX()/SUM()/AVG()/COUNT())

• from ...join ... on ...   Left outer connection: left join... On... Right outer connection: right join... On

• where ...

• group by ...   (oracle,SQL server appears in select)   Non grouping function after clause must appear after group by clause)

• having ...   Functions for filtering groups

• order by ...   asc ascending, desc descending

• limit (0,4)   Limit N pieces of data, such as topN data

• union Union

• Union all (duplicate)

• intersect intersection

• Minus minus

DCL: data control language   Commit: commit / rollback: rollback / authorize grant...to  / revoke

Indexes

When to create an index:

I

select employee_id, last_name, salary, department_id
from employees
where department_id in (70, 80) --> 70:1  80:34

• union Union

• Union all (with duplicate parts)

• intersect intersection

• Minus minus

select employee_id, last_name, salary, department_id
from employees
where department_id in (80, 90)  --> 90:4  80:34

Question: query the information of employees whose salary is greater than the salary of employee No. 149

select * 
from employees
where salary > (
      select salary
      from employees
      where employee_id = 149
)

Question: query the manager of employee 141 or 174_ ID and department_ Employee of other employees with the same ID_ id, manager_id, department_id

select employee_id, manager_id, department_id
from employees
where manager_id in (
      select manager_id
      from employees
      where employee_id in(141, 174)
) and department_id in (
      select department_id
      from employees
      where employee_id in(141, 174)
) and employee_id not in (141, 174);

select employee_id, manager_id, department_id
from employees
where (manager_id, department_id) in (
      select manager_id, department_id
      from employees
      where employee_id in (141, 174)
) and employee_id not in(141, 174);

1. Use subqueries in the from clause

select max(avg(salary))
from employees
group by department_id;

select max(avg_sal)
from (
      select avg(salary) avg_sal
      from employees
      group by department_id
) e

• Question: return the last salary of employees whose salary is higher than the average salary of the Department_ name, department_ ID, salary and average salary

select last_name, department_id, salary, (select avg(salary) from employees where department_id = e1.department_id)
from employees e1
where salary > (
      select avg(salary)
      from employees e2
      where e1.department_id = e2.department_id
)

select last_name, e1.department_id, salary, avg_sal
from employees e1, (
     select department_id, avg(salary) avg_sal
     from employees
     group by department_id
) e2
where e1.department_id = e2.department_id
and e1.salary > e2.avg_sal;

case...when ... then... when ... then ... else ... end

• Query: if the Department is 10, check 1.1 times the salary, the department number is 1.2 times the salary of 20, and the rest is 1.3 times

SELECT
 employee_id,
 last_name,
 salary,
CASE
  department_id 
  WHEN 10 THEN
  salary * 1.1                                                           
  WHEN 20 THEN
  salary * 1.2  ELSE salary * 1.3                                                           
 END "new_salary" 
FROM
 employees;
SELECT
 employee_id,
 last_name,
 salary,
 decode( department_id, 10, salary * 1.1, 20, salary * 1.2,  salary * 1.3 ) "new_salary" 
FROM
 employees;

• Question: explicit employee_id,last_name and location. Where, if the employee department_id and location_ Department with ID 1800_ If the ID is the same, the location is' Canada ', and the rest is' USA'.

select employee_id, last_name, case department_id when (
                    select department_id
                    from departments
                    where location_id = 1800
) then 'Canada' else 'USA' end "location"
from employees;

• Question: query employees of employees_ id,last_ Name, according to the employee's department_name sort

select employee_id, last_name
from employees e1
order by (
      select department_name
      from departments d1
      where e1.department_id = d1.department_id
)

SQL optimization: do not use IN if you can use EXISTS

• Question: query the employee of the company manager_ id,last_ name,job_ id,department_ ID information

select employee_id, last_name, job_id, department_id
from employees
where employee_id in (
      select manager_id
      from employees
)


select employee_id, last_name, job_id, department_id
from employees e1
where exists (
      select 'x'
      from employees e2
      where e1.employee_id = e2.manager_id
) 

• Problem: query the departments of departments that do not exist in the employees table in the departments table_ ID and department_name

select department_id, department_name
from departments d1
where not exists (
      select 'x'
      from employees e1
      where e1.department_id = d1.department_id
)

• Change the information of 108 employees: change their salary to the highest salary in their department and the job to the job with the lowest average salary in the company

update employees e1
set salary = (
    select max(salary)
    from employees e2
    where e1.department_id = e2.department_id
), job_id = (
   select job_id
   from employees
   group by job_id
   having avg(salary) = (
         select min(avg(salary))
         from employees
         group by job_id
   )
)
where employee_id = 108;

• Delete the employee with the lowest salary in the Department of employee 108

delete from employees e1
where salary = (
      select min(salary)
      from employees
      where department_id = (
            select department_id
            from employees
            where employee_id = 108
      )
)

select * from employees where employee_id = 108;
select * from employees where department_id = 100
order by salary;

rollback;

Common SQL interview questions: Classic 50 questions

Four tables are known as follows:

• Student form: student (student number, student name, date of birth, gender)

• Transcript: score (student number, course number, grade)

• Course schedule: Course (course number, course name, teacher number)

• Teacher form: teacher (teacher number, teacher name)

Write the corresponding SQL statement according to the above information and the following requirements.

ps: these questions examine the ability to write SQL. For this type of questions, you need to clarify the association relationship between the four tables. The best way is to draw the association diagram on the draft paper, and then write the corresponding SQL statement. The following figure is the relationship diagram of the four tables I drew. You can see which foreign keys are associated between them:

1, Create databases and tables

In order to demonstrate the running process of the topic, we first create the database and table in the client navicat according to the following statement.

If you don't know what a database is and what a client navicat is, you can learn this first:

1. Create table

1) Create student table

Create a student table in navicat as shown below.

The student number column of the student table is set as the primary key constraint. The following figure shows the data types and constraints set for each column

Click "save" after creating the table

2) Create score

In the same step, create a "grade sheet". The "student number" and "course number" of the "course sheet" are set as the primary key constraint (joint primary key), and the "grade" column is set as the numerical type (float, floating-point numerical value)

3) Create a course

The course number of the curriculum is set as the primary key constraint

4) teacher table

The teacher No. column of the teacher table is set as the primary key constraint, and the teacher name column is set as null (unchecked in the red box), indicating that this column can contain null values.

Adding data to a table

1) Add data to student table

sql to add data

insert into student(Student number,full name,date of birth,Gender) 
values('0001' , 'monkey' , '1989-01-01' , 'male');
 
insert into student(Student number,full name,date of birth,Gender) 
values('0002' , 'monkey' , '1990-12-21' , 'female');
 
insert into student(Student number,full name,date of birth,Gender) 
values('0003' , 'Jack Ma' , '1991-12-21' , 'male');
 
insert into student(Student number,full name,date of birth,Gender) 
values('0004' , 'Sephirex Wang' , '1990-05-20' , 'male');

Operation in navicat client

2) score

sql to add data

insert into score(Student number,Course number,achievement) 
values('0001' , '0001' , 80);
 
insert into score(Student number,Course number,achievement) 
values('0001' , '0002' , 90);
 
insert into score(Student number,Course number,achievement) 
values('0001' , '0003' , 99);
 
insert into score(Student number,Course number,achievement) 
values('0002' , '0002' , 60);
 
insert into score(Student number,Course number,achievement) 
values('0002' , '0003' , 80);
 
insert into score(Student number,Course number,achievement) 
values('0003' , '0001' , 80);
 
insert into score(Student number,Course number,achievement) 
values('0003' , '0002' , 80);
 
insert into score(Student number,Course number,achievement) 
values('0003' , '0003' , 80);

Operation in client navicat

3) Curriculum

sql to add data

insert into course(Course number,Course name,Teacher number)
values('0001' , 'language' , '0002');
 
insert into course(Course number,Course name,Teacher number)
values('0002' , 'mathematics' , '0001');
 
insert into course(Course number,Course name,Teacher number)
values('0003' , 'English' , '0003');

Operation in client navicat

4) Add data to the teacher table

sql to add data

-- Teacher tables: adding data
insert into teacher(Teacher number,Teacher name) 
values('0001' , 'Menzaza');
 
insert into teacher(Teacher number,Teacher name) 
values('0002' , 'pony ');
 
-- The teacher name here is null( null)
insert into teacher(Teacher number,Teacher name) 
values('0003' , null);
 
-- The teacher name here is an empty string('')
insert into teacher(Teacher number,Teacher name) 
values('0004' , '');

Operation in client navicat

Add results

3, 50 interview questions

In order to facilitate learning, I classified 50 interview questions

Query the list of students surnamed "monkey"

Query the number of teachers surnamed "Meng"

select count(Teacher number)
from teacher
where Teacher name like 'Meng%';

2. Summary statistics and grouping analysis

Interview question: query the total score of the course number "0002"

--Analysis ideas
--select Query results [Total score:Summary Function  sum]
--from Which table to find data from[Transcript score]
--where query criteria [The course number is 0002]
select sum(achievement)
from score
where Course number = '0002';

Query the number of students who have selected the course

--This topic is translated into Vernacular: query how many candidates are there for the course
--select Student number. The student number in the grade sheet has duplicate values and needs to be removed
--from Find from schedule score;
select count(distinct Student number) as Number of students 
from score;

Query the highest and lowest scores of each subject and display them in the following form: course number, highest score and lowest score

/*
Analysis ideas
select Query results   [Course ID: is the alias of the course number, the highest score: Max (score), the lowest score: min (score)]
from Which table to find data from   [transcript score]
where query criteria   [no]
group by grouping   [grade of each subject: that is, the grade of each course, which needs to be grouped according to the course number];
*/
select Course number,max(achievement) as Highest score,min(achievement) as Lowest score
from score
group by Course number;

Query the number of students selected for each course

/*
Analysis ideas
select Query results   [course number, number of students taking the course: summary function count]
from Which table to find data from   [transcript score]
where query criteria   [no]
group by grouping   [each course: grouped by course number];
*/
select Course number, count(Student number)
from score
group by Course number;

Query the number of boys and girls

/*
Analysis ideas
select Query results   [gender, number of persons corresponding to gender: summary function count]
from Which table to find data from   [gender is in the student table, so the student table is searched]
where query criteria   [no]
group by grouping   [number of boys and girls: grouped by gender]
having Specify criteria for grouping results   [no]
order by Sort query results [none];
*/
select Gender,count(*)
from student
group by Gender;

Query the student number and average score of students whose average score is greater than 60

/* 
The title is translated into Vernacular:
Average score: to expand, it is to calculate the average score of each student
 When it comes to "each", we need to group
 If the average score is greater than 60 points, the grouping results shall be subject to the specified conditions
 Analysis ideas
select Query results   [student number, average score: summary function AVG (score)]
from Which table to find data from   [the score is in the score sheet, so the score sheet is searched]
where query criteria   [no]
group by grouping   [average score: first group by student number, and then calculate the average score]
having Specify criteria for grouping results   [average score greater than 60 points]
*/
select Student number, avg(achievement)
from score
group by Student number
having avg(achievement)>60;

Query the student number of students taking at least two courses

/* 
Translated into Vernacular:
In step 1, you need to calculate the course data of each student's elective course, which needs to be grouped by student number
 Step 2, take at least two courses: that is, the number of elective courses for each student > = 2, and specify the conditions for the grouping results
 Analysis ideas
select Query results   [student number, number of elective courses per student: summary function count]
from Which table to find data from   [student number of the course: curriculum score]
where query criteria   [take at least two courses: you need to calculate how many courses each student has taken and use grouping, so there is no where clause here]
group by grouping   [number of elective courses for each student: group according to the course number, and then calculate the number of elective courses with the summary function count]
having Specify criteria for grouping results   [at least two elective courses: number of elective courses per student > = 2]
*/
select Student number, count(Course number) as Number of elective courses
from score
group by Student number
having count(Course number)>=2;

Query the list of same-sex students with the same name and count the number of students with the same name

/* 
Translated into vernacular, problem analysis:
1)Find out who the students with the same name are and the number of students with the same name
 Query result: name, number of people
 Condition: how to calculate the same name? The number of people grouped by name is greater than or equal to 2, because the number of people with the same name is greater than or equal to 2
 Analysis ideas
select Query results   [name, number of people: summary function count(*)]
from Which table to find data from   [student form]
where query criteria   [no]
group by grouping   [same name: group by name]
having Specify criteria for grouping results   [same name: count (*) > = 2]
order by Sort query results [none];
*/
 
select full name,count(*) as Number of people
from student
group by full name
having count(*)>=2;

Query the failed courses and arrange them according to the course number from large to small

/* 
Analysis ideas
select Query results   [Course No.]
from Which table to find data from   [transcript score]
where query criteria   [fail: Grade  < 60]
group by grouping   [no]
having Specify criteria for grouping results   [no]
order by Sort the query results [course numbers from large to small: descending desc];
*/
select Course number
from score 
where achievement<60
order by Course number desc;

Query the average score of each course. The results are sorted in ascending order according to the average score. If the average score is the same, it is sorted in descending order according to the course number

/* 
Analysis ideas
select Query results   [course number, average grade: summary function AVG (grade)]
from Which table to find data from   [transcript score]
where query criteria   [no]
group by grouping   [each course: grouped by course number]
having Specify criteria for grouping results   [no]
order by Sort the query results [sort by average score in ascending order: asc, if the average score is the same, sort by course number in descending order: desc];
*/
select Course number, avg(achievement) as Average score
from score
group by Course number
order by Average score asc,Course number desc;

Search the student numbers of students with course number "0004" and score less than 60, and the results are sorted in descending order by score

/* 
Analysis ideas
select Query results   []
from Which table to find data from   [transcript score]
where query criteria   [course number is "04" and score is less than 60]
group by grouping   [no]
having Specify criteria for grouping results   []
order by Sort query results [query results are sorted in descending order by score];
*/
select Student number
from score
where Course number='04' and achievement <60
order by achievement desc;

Count the number of students in each course (only those with more than 2 students)

It is required to output the course number and the number of electives. The query results are sorted in descending order by the number of people. If the number of people is the same, they are sorted in ascending order by the course number

/* 
Analysis ideas
select Query results   [required to output course number and number of electives]
from Which table to find data from   []
where query criteria   []
group by grouping   [each course: grouped by course number]
having Specify criteria for grouping results   [number of students taking elective courses (only for courses with more than 2 students): number of students per course > 2]
order by Sort the query results [sort the query results in descending order by the number of people, or in ascending order by the course number if the number of people is the same];
*/
select Course number, count(Student number) as 'Number of electives'
from score
group by Course number
having count(Student number)>2
order by count(Student number) desc,Course number asc;

Query the student number and average score of students who have failed more than two courses

/*
Analysis ideas
 First break down the topic:
1)[Restrictions on more than two] [failed courses]
2)[Student number and average score], that is, the average score of each student, displays the student number and average score
 Analysis process:
Step 1: get the average score of each student, display the student number and average score
 Step 2: add restrictions:
1)Failed course
2)More than two [failed courses]: number of courses > 2
 
 
/* 
Step 1: get the average score of each student, display the student number and average score
select Query results   [student number, average score: summary function AVG (score)]
from Which table to find data from   [related to grades: score of score sheet]
where query criteria   [no]
group by grouping   [average per student: grouped by student number]
having Specify criteria for grouping results   [no]
order by Sort query results [none];
*/
select Student number, avg(achievement) as Average score
from score
group by Student number;
 
 
/* 
Step 2: add restrictions:
1)Failed course
2)More than two [failed courses]
select Query results   [student number, average score: summary function AVG (score)]
from Which table to find data from   [related to grades: score of score sheet]
where query criteria   [restrictions: failed courses, average score < 60]
group by grouping   [average per student: grouped by student number]
having Specify criteria for grouping results   [restrictions: number of courses > 2, summary function count > 2]
order by Sort query results [none];
*/
select Student number, avg(achievement) as Average score
from score
where achievement <60
group by Student number
having count(Course number)>=2;

If you can't do the above questions, you can review the sql knowledge involved in this part:

3. Complex query

Query the student number and name of all students whose course scores are less than 60

[knowledge point] sub query

1. Translate into vernacular

1) Query results: Student ID, name 2) query criteria: all students with course scores < 60 need to find them in the score table and use sub query

Step 1, write sub query (all students with course scores < 60)

• select query result [student number]

• from which table to find data [score]

• where query criteria [score < 60]

• group by [no]

• having specifies criteria for grouping results [no]

• order by sorts query results [no]

• limit retrieves the specified row from the query result [no];

select Student number 
from score
where achievement < 60;

Step 2, query result: student number, name, condition is the student number found in the previous step

• select query result [student number, name]

• from which table to find data [student table: student]

• where query condition [using operator in]

• group by [no]

• having specifies criteria for grouping results [no]

• order by sorts query results [no]

• limit retrieves the specified row from the query result [no];

select Student number,full name
from student
where  Student number in (
select Student number 
from score
where achievement < 60
);

Query the student number and name of students who have not learned all courses

/*
Find the student number. Condition: not learning all the courses, that is, the number of courses the student elects  <  Total number of courses
[Investigation knowledge point] in, sub query
*/
select Student number,full name
from student
where Student number in(
select Student number 
from score
group by Student number
having count(Course number) < (select count(Course number) from course)
);

Find out the student numbers and names of all students who have only taken two courses

select Student number,full name
from student
where Student number in(
select Student number
from score
group by Student number
having count(Course number)=2
);

List of students born in 1990

/*
Find a list of students born in 1990
 The type of birth date column in the student table is datetime
*/
select Student number,full name 
from student 
where year(date of birth)=1990; 

Query the top two records of each subject

In fact, this kind of problem is common: grouping takes the maximum and minimum values of each group, and the maximum N (top N) records of each group.

sql interview question: topN question

We often encounter such business problems at work:

• How to find out which products users like best under each category?

• If you find the five most clicked products in each category, what are they?

In fact, this kind of problem is common: grouping takes the maximum and minimum values of each group, and the maximum N (top N) records of each group.

How to solve such problems?

Let's give the answer through the example of the grade sheet.

The score sheet is the student's score, including student number (student's student number), course number (student's elective course number) and score (student's score obtained by elective course)

Group and take the maximum value of each group

Case: get the data of the line with the maximum score by grouping by course number

We can use the group by and summary functions to get a value (maximum, minimum, average, etc.) in each group. However, the data of the row where the maximum score is located cannot be obtained.

select Course number,max(achievement) as Maximum achievement
from score 
group by Course number;

We can use the associated sub query to implement:

select * 
from score as a 
where achievement = (
select max(achievement) 
from score as b 
where b.Course number = a.Course number);

There are two rows of data in the above query result course No. "0001", because there are two maximum scores of 80

Take the minimum value of each group in groups

Case: get the data of the row with the lowest score by grouping according to the course number

The same is achieved using associated subqueries

select * 
from score as a 
where achievement = (
select min(achievement) 
from score as b 
where b.Course number = a.Course number);

Maximum N records per group

Case: query the records of the top two in each subject

Step 1, find out which groups are there

We can group by course number to find out which groups there are and which course numbers correspond to this question

select Course number,max(achievement) as Maximum achievement
from score 
group by Course number;

Step 2: first use the order by clause to sort the scores in descending order (desc), and then use the limt clause to return topN (corresponding to the top two scores returned by this question)

-- Course number'0001' Top 2 in this group
select * 
from score 
where Course number = '0001' 
order by achievement  desc 
limit 2;

Similarly, you can write the of other groups (other course numbers) and take out the sql of the top two grades. Step 3, use union all to combine the selected data of each group

-- Slide left and right to get all sql
(select * from score where Course number = '0001' order by achievement  desc limit 2)
union all
(select * from score where Course number = '0002' order by achievement  desc limit 2)
union all
(select * from score where Course number = '0003' order by achievement  desc limit 2);

Previously, we used the order by clause to sort a column in descending order (desc) to get the largest N records in each group. If you want to reach the minimum N records in each group, you can sort the order by clause in ascending order (asc) of a column.

The problem of seeking topN can also be realized by using user-defined variables, which will be introduced later.

If you don't know about multi table merging, you can take a look at the "multi table query" in learning SQL from scratch.

summary

Common interview questions: take the maximum and minimum values of each group in groups, and the maximum N (top N) records of each group.

4. Multi table query

Query the student number, name, number of courses selected and total score of all students

select a.Student number,a.full name,count(b.Course number) as Number of courses selected,sum(b.achievement) as Total score
from student as a left join score as b
on a.Student number = b.Student number
group by a.Student number;

Query the student number, name and average score of all students with an average score greater than 85

select a.Student number,a.full name, avg(b.achievement) as Average score
from student as a left join score as b
on a.Student number = b.Student number
group by a.Student number
having avg(b.achievement)>85;

Query students' course selection: student number, name, course number and course name

select a.Student number, a.full name, c.Course number,c.Course name
from student a inner join score b on a.Student number=b.Student number
inner join course c on b.Course number=c.Course number;

Find out the number of pass and fail of each course

-- investigate case expression
select Course number,
sum(case when achievement>=60 then 1 
  else 0 
    end) as Number of pass,
sum(case when achievement <  60 then 1 
  else 0 
    end) as Number of failed
from score
group by Course number;

Use segments [100-85], [85-70], [70-60], [< 60] to count the scores of each subject, including the number of people in each score segment, course number and course name

-- investigate case expression
select a.Course number,b.Course name,
sum(case when achievement between 85 and 100 
  then 1 else 0 end) as '[100-85]',
sum(case when achievement >=70 and achievement<85 
  then 1 else 0 end) as '[85-70]',
sum(case when achievement>=60 and achievement<70  
  then 1 else 0 end) as '[70-60]',
sum(case when achievement<60 then 1 else 0 end) as '[<60]'
from score as a right join course as b 
on a.Course number=b.Course number
group by a.Course number,b.Course name;

Query the student number and name of students with course number 0003 and course score above 80|

select a.Student number,a.full name
from student  as a inner join score as b on a.Student number=b.Student number
where b.Course number='0003' and b.achievement>80;

The following is the student's transcript (table name score, column name: student number, course number, grade)

Use sql implementation to convert the table row into the following table structure

[summary of interview question types] this kind of questions belong to how to exchange rows and columns. The solution ideas are as follows:

[interview questions] the following is the student's transcript (table name score, column name: student number, course number, score)

Use sql implementation to convert the table row into the following table structure

[answer]

Step 1, output the structure of the target table using constant columns

You can see that the query result is very close to the target table

select Student number,'Course No. 0001','Course No.: 0002','Course No.: 0003'
from score;

Step 2, use the case expression to replace the constant column as the corresponding score

select Student number,
(case Course number when '0001' then achievement else 0 end) as 'Course No. 0001',
(case Course number when '0002' then achievement else 0 end) as  'Course No.: 0002',
(case Course number when '0003' then achievement else 0 end) as 'Course No.: 0003'
from score;

In this query result, each row represents the score of a student in a course. For example, the first row is the score of 'student No. 0001' elective 'course No. 00001', while the scores of 'course No. 0002' and 'course No. 0003' in the other two columns are 0.

The results of each student's elective course are in each box in the figure below. We can take out the results of each course by grouping.

Level 3, grouping

Group and use the maximum function max to get the maximum value in each box in the figure above

select Student number,
max(case Course number when '0001' then achievement else 0 end) as 'Course No. 0001',
max(case Course number when '0002' then achievement else 0 end) as 'Course No.: 0002',
max(case Course number when '0003' then achievement else 0 end) as 'Course No.: 0003'
from score
group by Student number;

In this way, we get the target table (row column exchange)

Author: sh_c_2450957609

blog.csdn.net/u010565545/article/details/100785261