【WEEK6】 【DAY2】DQL Data Querying - Part Two 【English Version】

2024-04-03 14:36

本文主要是介绍【WEEK6】 【DAY2】DQL Data Querying - Part Two 【English Version】,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

2024.4.2 Tuesday
Following the previous article 【WEEK6】 【DAY1】DQL Data Query - Part One【English Version】

Contents

  • 4.4. Join Queries
    • 4.4.1. JOIN Comparison
    • 4.4.2. Seven Types of JOINs
    • 4.4.3. Examples
      • 4.4.3.1. In this example, the results of INNER JOIN and RIGHT JOIN are the same
      • 4.4.3.2.LEFT JOIN
      • 4.4.3.3. querying students who missed exams
      • 4.4.3.4. Exercise: Query Information of Students Who Took Exams (Student ID, Student Name, Subject Name, Score)
      • 4.4.3.5. Self-Join (For Understanding)
        • A table joins itself, essentially splitting into two identical tables
        • Query the Grade of Students (Student ID, Name, Grade Name)
        • Query the Grade of Subjects (Subject Name, Grade Name)
        • Query Information of Students Who Took 'Advanced Mathematics-1' Exam (Student ID, Student Name, Subject Name, Score)
  • 4.5. Sorting and Pagination
    • 4.5.1. Sorting
      • 4.5.1.1. ORDER BY: Which field to sort by and how
    • 4.5.2. Pagination
      • 4.5.2.1. Displaying data skipping the top five scores in descending order
      • 4.5.2.2. Query the top 10 students by score in 'Advanced Mathematics-3', with scores no less than 80 (Student ID, Name, Course Name, Score)
  • 4.6. Subqueries
    • 4.6.1. Query all exam results for 'Advanced Mathematics-3' (Student ID, Course Number, Score) in descending order
      • 4.6.2.1. Method 1: Using join queries + subqueries
      • 4.6.1.2. Method 2: Using subqueries (Inside-out approach)
    • 4.6.2. Query student ID and name for students scoring above 80 in 'Advanced Mathematics-4'
      • 4.6.2.1. Method 1: Using join queries + subqueries
      • 4.6.2.2. Method 2: Using Join Queries
      • 4.6.2.3. Method 3: Using Subqueries
    • 4.6.3. Exercises
      • 4.6.3.1. Query the top 5 students' scores in C Language-1 (Student ID, Name, Score)
      • 4.6.3.2. Using a subquery, find the grade name where student Liu Fu is enrolled

4.4. Join Queries

4.4.1. JOIN Comparison

Operator NameDescription
INNER JOINReturns rows if there is at least one match in the tables
LEFT JOINReturns all rows from the left table, even if there are no matches in the right table
RIGHT JOINReturns all rows from the right table, even if there are no matches in the left table

4.4.2. Seven Types of JOINs

Insert image description here

4.4.3. Examples

/*
Join QueriesTo query data from multiple tables, connection operators can be used to perform multiple queries.
Inner joinQueries the intersection of the result sets of two tables.
Outer joinLeft outer join(Using the left table as the base, the right table is matched one by one. If there is no match, the records from the left table are returned, and the right table is filled with NULL.)Right outer join(Using the right table as the base, the left table is matched one by one. If there is no match, the records from the right table are returned, and the left table is filled with NULL.)Equi-join and Non-equi-joinSelf-join
*/
-- Joined table query join --
-- Query the student number, name, score, and subject number of students who took the exam.
/*Approach
1. Analyze the approach, determining which tables each required field comes from,
2. Determine which type of join query to use (the result of querying individual tables should form a complete table for the requirement): 7 types
-> Determine the intersection point (which data is the same in these two tables)
The condition: based on the same field name in both tables, such as: studentNo in the student table = studentNo in the result table
*/
-- JOIN + the table to connect + ON + the condition to judge  (A specific syntax for join query)
-- WHERE		Equi-join

4.4.3.1. In this example, the results of INNER JOIN and RIGHT JOIN are the same

-- INNER JOIN
SELECT s.studentNo, studentName, SubjectNo, StudentResult	-- The field names at the intersection must declare which table they come from
FROM student AS s
INNER JOIN result AS r
WHERE s.studentNo = r.studentNo	-- on is the condition before joining the tables, where is the filter after joining the tables
-- RIGHT JOIN, with the fields from the right table as the basis
SELECT s.studentNo, studentName, SubjectNo, StudentResult
FROM student AS s	-- Left table
RIGHT JOIN result AS r	-- Right table
ON s.studentNo = r.studentNo

Insert image description here

4.4.3.2.LEFT JOIN

-- LEFT JOIN, with the fields of the left table prevailing 
-- Difference from a right join: shows information about people without test scores
SELECT s.studentNo, studentName, SubjectNo, StudentResult
FROM student AS s -- right table
LEFT JOIN result AS r -- left table
ON s.studentNo = r.studentNo

在这里插入图片描述

4.4.3.3. querying students who missed exams

-- Query the students who missed the exam
SELECT s.studentNo, studentName, SubjectNo, StudentResult
FROM student s -- right table
LEFT JOIN result r -- left table
ON s.studentNo = r.studentNo
WHERE StudentResult IS NULL

在这里插入图片描述

4.4.3.4. Exercise: Query Information of Students Who Took Exams (Student ID, Student Name, Subject Name, Score)

-- Exercise: Query information of students who took exams (student ID, student name, subject name, score)
/*Approach
1. Analyze the approach, determining which tables each required field comes from: student, result, subject join query.
2. Determine which type of join query to use (the result of querying individual tables should form a complete table for the requirement): 7 types
-> Determine the intersection point (which data is the same in these two tables)
The condition: based on the same field name in both tables, such as: studentNo in the student table = studentNo in the result table
*/
-- Lines 10~11 (first connection point) from here is the left table join here is the right table choose which table to base on decides whether to use left or right
SELECT s.studentNo, studentName, subjectName, `StudentResult`
FROM student s
RIGHT JOIN result r
ON r.studentNo = s.studentNo
INNER JOIN `subject` sub
ON r.subjectNo = sub.subjectNo

Insert image description here

#Standard Thinking Steps
-- What data do I want to query -> SELECT ...
-- From which tables -> FROM some table JOIN the table to connect ON the intersection condition of these two tables
-- If querying from multiple tables, repeat the previous step, starting with connecting two tables

4.4.3.5. Self-Join (For Understanding)

A table joins itself, essentially splitting into two identical tables
  1. Parent category
    Insert image description here

  2. Subcategory
    Insert image description here

  3. Desired query result
    Insert image description here

#Self-Join
-- Write SQL statement to present the parent-child relationship of categories (Parent Category Name, Subcategory Name)
-- Query parent-child information: Split one table into two identical tables
SELECT a.`categoryName` AS 'Parent Category', b.`categoryName` AS 'Subcategory'
FROM `category` AS a, `category` AS b
WHERE a.`categoryid` = b.`pid`

Insert image description here

Query the Grade of Students (Student ID, Name, Grade Name)
-- Query the grade of students (student ID, name, grade name)
SELECT studentNo, studentName, `GradeName`
FROM student s
INNER JOIN `grade` g
ON s.`GradeID` = g.`GradeID`

Insert image description here

Query the Grade of Subjects (Subject Name, Grade Name)
-- Query the grade of subjects (subject name, grade name)
SELECT `SubjectName`,`GradeName`
FROM `subject` sub
INNER JOIN `grade` g
ON sub.`GradeID` = g.`GradeID`

Insert image description here

Query Information of Students Who Took ‘Advanced Mathematics-1’ Exam (Student ID, Student Name, Subject Name, Score)
-- Query information of students who took the 'Advanced Mathematics-1' exam (student ID, student name, subject name, score)
SELECT s.`StudentNo`, `StudentName`, `SubjectName`, `StudentResult`
FROM student s
INNER JOIN `result` r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE subjectName = 'Advanced Mathematics-1'

Insert image description here

4.5. Sorting and Pagination

4.5.1. Sorting

4.5.1.1. ORDER BY: Which field to sort by and how

Syntax: ORDER BYThe ORDER BY statement is used to sort the result set by a specified column.The ORDER BY statement defaults to sorting records in ascending order (ASC).To sort the records in descending order, you can use the DESC keyword.
-- Pagination with limit and sorting with order by --
#Sorting: ASC for ascending, DESC for descending
#Syntax: ORDER BY which field to sort by and how
SELECT s.`StudentNo`, `StudentName`, `SubjectName`, `StudentResult`
FROM student s
INNER JOIN `result` r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE subjectName = 'Advanced Mathematics-1'
ORDER BY StudentResult DESC	-- The results are sorted in 'descending' order by score
-- To sort the results in 'ascending' order by score, simply change DESC to ASC

Insert image description here
Insert image description here

4.5.2. Pagination

Syntax: SELECT * FROM table LIMIT [offset (number of pages to skip),] rows (how many rows per page) | rows OFFSET offset
Benefits: (User experience, Network transmission, Query pressure)

4.5.2.1. Displaying data skipping the top five scores in descending order

#Pagination
/*
First page: LIMIT 0,5 (skip 0 rows, 5 rows on this page)
Second page: LIMIT 5,5 (skip 5 rows, 5 rows on this page)
Third page: LIMIT 10,5 (skip 10 rows, 5 rows on this page)
......
Nth page: LIMIT (pageNo - 1) * pageSize, pageSizewhere pageNo is the page number, pageSize is the number of items per page, total pages = |_Total Items / Items per Page_|
*/
-- Displaying data skipping the top five scores in descending order
SELECT s.`StudentNo`, `StudentName`, `SubjectName`, `StudentResult`
FROM student s
INNER JOIN `result` r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE subjectName = 'Advanced Mathematics-1'
ORDER BY StudentResult DESC, StudentNo
LIMIT 1,5

Insert image description here

4.5.2.2. Query the top 10 students by score in ‘Advanced Mathematics-3’, with scores no less than 80 (Student ID, Name, Course Name, Score)

-- Query the top 10 students by score in 'Advanced Mathematics-3', with scores no less than 80 (Student ID, Name, Course Name, Score)
SELECT s.`StudentNo`, `StudentName`, `SubjectName`, `StudentResult`
FROM `student` s
INNER JOIN `result` r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE subjectName = 'Advanced Mathematics-3' AND StudentResult >= 80
ORDER BY StudentResult DESC, StudentNo
LIMIT 0,10

Insert image description here

4.6. Subqueries

4.6.1. Query all exam results for ‘Advanced Mathematics-3’ (Student ID, Course Number, Score) in descending order

4.6.2.1. Method 1: Using join queries + subqueries

-- Subqueries --
-- 1. Query all exam results for 'Advanced Mathematics-3' (Student ID, Course Number, Score) in descending order
#Method 1: Using join queries
SELECT `StudentNo`, sub.`SubjectNo`, `StudentResult`	-- or r.SubjectNo is also possible
FROM `result` r
INNER JOIN `subject` sub
ON r.SubjectNo = sub.SubjectNo
WHERE SubjectName = 'Advanced Mathematics-3'
ORDER BY StudentResult DESC

4.6.1.2. Method 2: Using subqueries (Inside-out approach)

#Method 2: Using subqueries (Inside-out approach)
SELECT `StudentNo`, `SubjectNo`, `StudentResult`
FROM `result`
WHERE SubjectNo = (SELECT SubjectNo FROM `subject`WHERE SubjectName = 'Advanced Mathematics-3'
)
ORDER BY StudentResult DESC

Insert image description here

4.6.2. Query student ID and name for students scoring above 80 in ‘Advanced Mathematics-4’

4.6.2.1. Method 1: Using join queries + subqueries

-- 2. Query student ID and name for students scoring above 80 in 'Advanced Mathematics-4'
#Method 1: Using join queries + subqueries
-- First part: Getting student IDs and names for students scoring above 80 in any subject
SELECT DISTINCT s.`StudentNo`, `StudentName`	-- Without DISTINCT, each matching result appears (the same person's name and ID may appear multiple times)
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo	-- Up to this point, it includes students with scores in any subject
WHERE `StudentResult` > 80
-- Second part: Adding 'Advanced Mathematics-4' on this basis, avoiding another join query -> switching to querying the course number
SELECT DISTINCT s.`StudentNo`, `StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE `StudentResult` > 80 AND `SubjectNo` = (SELECT SubjectNoFROM `subject`WHERE `SubjectName` = 'Advanced Mathematics-4'
)

4.6.2.2. Method 2: Using Join Queries

#Method 2: Using Join Queries
SELECT DISTINCT s.`StudentNo`, `StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
INNER JOIN `subject` sub
ON r.SubjectNo = sub.SubjectNo
WHERE `StudentResult` > 80 AND SubjectName = 'Advanced Mathematics-4'

4.6.2.3. Method 3: Using Subqueries

#Method 3: Using Subqueries
SELECT StudentNo, StudentName FROM student WHERE StudentNo IN (		-- It's better to use equals (=) instead of IN for a single condition, as = is more efficient in queries.SELECT StudentNo FROM result WHERE StudentResult > 80 AND SubjectNo = (SELECT SubjectNo FROM `subject` WHERE `SubjectName` = 'Advanced Mathematics-4')
)

Insert image description here

4.6.3. Exercises

4.6.3.1. Query the top 5 students’ scores in C Language-1 (Student ID, Name, Score)

/*
Exercise:Query the top 5 students' scores in C Language-1 (Student ID, Name, Score).Use a subquery to find the grade name where student Liu Fu is enrolled.
*/
-- 1
SELECT s.StudentNo, StudentName, StudentResult 
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
WHERE SubjectNo = (SELECT SubjectNo FROM `subject` WHERE SubjectName = 'C Language-1'
)
ORDER BY StudentResult DESC
LIMIT 0,5

Insert image description here

4.6.3.2. Using a subquery, find the grade name where student Liu Fu is enrolled

-- 2
SELECT GradeName FROM grade WHERE GradeID = (SELECT GradeID FROM student WHERE StudentName = 'Liu Fu'
)

Insert image description here

这篇关于【WEEK6】 【DAY2】DQL Data Querying - Part Two 【English Version】的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/873181

相关文章

论文翻译:arxiv-2024 Benchmark Data Contamination of Large Language Models: A Survey

Benchmark Data Contamination of Large Language Models: A Survey https://arxiv.org/abs/2406.04244 大规模语言模型的基准数据污染:一项综述 文章目录 大规模语言模型的基准数据污染:一项综述摘要1 引言 摘要 大规模语言模型(LLMs),如GPT-4、Claude-3和Gemini的快

Maven创建项目中的groupId, artifactId, 和 version的意思

文章目录 groupIdartifactIdversionname groupId 定义:groupId 是 Maven 项目坐标的第一个部分,它通常表示项目的组织或公司的域名反转写法。例如,如果你为公司 example.com 开发软件,groupId 可能是 com.example。作用:groupId 被用来组织和分组相关的 Maven artifacts,这样可以避免

CentOS下mysql数据库data目录迁移

https://my.oschina.net/u/873762/blog/180388        公司新上线一个资讯网站,独立主机,raid5,lamp架构。由于资讯网是面向小行业,初步估计一两年内访问量压力不大,故,在做服务器系统搭建的时候,只是简单分出一个独立的data区作为数据库和网站程序的专区,其他按照linux的默认分区。apache,mysql,php均使用yum安装(也尝试

使用Spring Boot集成Spring Data JPA和单例模式构建库存管理系统

引言 在企业级应用开发中,数据库操作是非常重要的一环。Spring Data JPA提供了一种简化的方式来进行数据库交互,它使得开发者无需编写复杂的JPA代码就可以完成常见的CRUD操作。此外,设计模式如单例模式可以帮助我们更好地管理和控制对象的创建过程,从而提高系统的性能和可维护性。本文将展示如何结合Spring Boot、Spring Data JPA以及单例模式来构建一个基本的库存管理系统

Level3 — PART 3 — 自然语言处理与文本分析

目录 自然语言处理概要 分词与词性标注 N-Gram 分词 分词及词性标注的难点 法则式分词法 全切分 FMM和BMM Bi-direction MM 优缺点 统计式分词法 N-Gram概率模型 HMM概率模型 词性标注(Part-of-Speech Tagging) HMM 文本挖掘概要 信息检索(Information Retrieval) 全文扫描 关键词

MySQL record 02 part

查看已建数据库的基本信息: show CREATE DATABASE mydb; 注意,是DATABASE 不是 DATABASEs, 命令成功执行后,回显的信息有: CREATE DATABASE mydb /*!40100 DEFAULT CHARACTER SET utf8mb3 / /!80016 DEFAULT ENCRYPTION=‘N’ / CREATE DATABASE myd

15 组件的切换和对组件的data的使用

划重点 a 标签的使用事件修饰符组件的定义组件的切换:登录 / 注册 泡椒鱼头 :微辣 <!DOCTYPE html><html lang="en"><head><meta charset="UTF-8"><meta name="viewport" content="width=device-width, initial-scale=1.0"><meta http-equiv="X-UA-

12C 新特性,MOVE DATAFILE 在线移动 包括system, 附带改名 NID ,cdb_data_files视图坏了

ALTER DATABASE MOVE DATAFILE  可以改名 可以move file,全部一个命令。 resue 可以重用,keep好像不生效!!! system照移动不误-------- SQL> select file_name, status, online_status from dba_data_files where tablespace_name='SYSTEM'

Jenkins 通过 Version Number Plugin 自动生成和管理构建的版本号

步骤 1:安装 Version Number Plugin 登录 Jenkins 的管理界面。进入 “Manage Jenkins” -> “Manage Plugins”。在 “Available” 选项卡中搜索 “Version Number Plugin”。选中并安装插件,完成后可能需要重启 Jenkins。 步骤 2:配置版本号生成 打开项目配置页面。在下方找到 “Build Env

Learn ComputeShader 09 Night version lenses

这次将要制作一个类似夜视仪的效果 第一步就是要降低图像的分辨率, 这只需要将id.xy除上一个数字然后再乘上这个数字 可以根据下图理解,很明显通过这个操作在多个像素显示了相同的颜色,并且很多像素颜色被丢失了,自然就会有降低分辨率的效果 效果: 但是这样图像太锐利了,我们加入噪声去解决这个问题 [numthreads(8, 8, 1)]void CSMain(uint3 id