- Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.
describe
Create a SQL statement to query the course information in the curriculum whose name is System Design.
Table definition: courses
The column name | type | annotation |
---|---|---|
id | int | A primary key |
name | varchar | Course name |
student_count | int | Total number of students |
created_at | date | Start time |
teacher_id | int | The lecturer id |
**
If there is no result, nothing is returned.
The sample
A sample:
Table content: courses
id | name | student_count | created_at | teacher_id |
---|---|---|---|---|
1 | Advanced Algorithms | 880 | The 2020-6-1 | 4 |
2 | System Design | 1350 | The 2020-7-18 | 3 |
3 | Django | 780 | The 2020-2-29 | 3 |
4 | Web | 340 | The 2020-4-22 | 4 |
5 | Big Data | 700 | The 2020-9-11 | 1 |
6 | Artificial Intelligence | 1660 | The 2018-5-13 | 3 |
7 | Java P6+ | 780 | The 2019-1-19 | 3 |
8 | Data Analysis | 500 | The 2019-7-12 | 1 |
10 | Object Oriented Design | 300 | The 2020-8-8 | 4 |
12 | Dynamic Programming | 2000 | The 2018-8-18 | 1 |
After running your SQL statement, the table should return:
id | name | student_count | created_at | teacher_id |
---|---|---|---|---|
2 | System Design | 1350 | The 2020-7-18 | 3 |
Example 2:
Table content: courses
id | name | student_count | created_at | teacher_id |
---|---|---|---|---|
1 | Advanced Algorithms | 880 | The 2020-6-1 | 4 |
2 | Java P6+ | 1350 | The 2020-7-18 | 3 |
3 | Django | 780 | The 2020-2-29 | 3 |
4 | Web | 340 | The 2020-4-22 | 4 |
5 | Big Data | 700 | The 2020-9-11 | 1 |
After running your SQL statement, the table should return:
id | name | student_count | created_at | teacher_id |
---|
There is no eligible data in sample 2, so the output contains only the table header and no data.
Answer key
This question is an entry level database query, create a single select can be successful. Postgresql WHERE name =’System Design’; Single quotation marks are used here, and error will be reported if double quotation marks are used.
unterminated quoted identifier at or near “”System Design’;”
LINE 3: WHERE name =”System Design’;
MYSQL
SELECT id.name, student_count, created_at, teacher_id
FROM courses
WHERE name ="System Design";
Copy the code
Postgresql
SELECT id, name, student_count, created_at, teacher_id
FROM courses
WHERE name ='System Design';
Copy the code