How to select Courses and Course Categories with SQL

Task: select Courses and Course Categories used in them.

Tables used:
The Courses table contains information on courses (note: there is no CategoryId column in this table).
The CourseCategories table contains information on course categories.
The CourseCategoryMaps table is used to relate the Courses and CourseCategories tables, contains CourseId, CategoryId.

Sql query:

SELECT c.Title AS CourseTitle, c.Id AS CourseId, cc.Name AS CourseCategoryName, cc.Id AS CourseCategoryId
FROM CourseCategoryMaps AS ccm
JOIN Courses AS c on c.Id=ccm.CourseId
JOIN CourseCategories AS cc on cc.Id=ccm.CategoryId;Use SQL aliases to give a column or a table a temporary name. An alias only exists for the duration of the query. Aliases are often used to make column and table names more readable:

Use SQL aliases to give a column or a table a temporary name. An alias only exists for the duration of the query. Aliases are often used to make column and table names more readable:

 SELECT cc.Name AS CategoryName
FROM CourseCategoryMaps AS ccm

 A JOIN clause is used to combine rows from two or more tables based on a related column between them:

SELECT * FROM CourseCategoryMaps AS ccm 
JOIN Courses AS c on c.Id=ccm.CourseId
WHERE c.Title='Excel';

*use quotes around text values, numeric fields should not be in between quotes.

The WHERE clause is used to filter records and extract only those records that fulfill a specified condition:

SELECT * FROM Courses 
WHERE Courses.Title LIKE 'Excel';

*There are two wildcards often used with the LIKE operator:

  • % - The percent sign represents multiple characters:
    WHERE Courses.Title like '%Excel' - finds any values in course titles that ends with the word 'Excel'.
    WHERE Courses.Title like '%Excel%' - finds any values in course titles with the word 'Excel' in any position.
  • _ - The underscore represents a single character:
    WHERE Courses.Title LIKE 'E_' - finds any values that have 2 characters and "E" in the first position.

The SELECT DISTINCT statement is used to return only distinct values. When a column in a table contains many duplicate values, use SELECT DISTINCT to list the different values:

SELECT DISTINCT Title from Courses; 

The LIMIT keyword specifies a limited number of rows in the result to be returned. For instance, LIMIT 10 will return the first 10 rows matching the SELECT criteria:

SELECT * FROM CourseCategories
LIMIT 10;

The ORDER BY statement is used to sort the result in ascending (ASC) or descending (DESC) order:

SELECT * FROM Courses
ORDER BY Id ASC;

SQL comments:

  • single-line comment starts with --. The text between -- and the end of the line is ignored:
SELECT * FROM Courses -- WHERE Title='SQL Basics';
  • multi-line comment starts with /* and ends with */. The text between /* and */ is ignored:
SELECT c.Title AS CourseTitle /*, c.Id AS CourseId, cc.Name AS CourseCategoryName, cc.Id AS CourseCategoryId
FROM CourseCategoryMaps AS ccm
JOIN Courses AS c on c.Id = ccm.CourseId
JOIN CourseCategories AS cc on cc.Id = ccm.CategoryId */
LIMIT 5;
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Please sign in to leave a comment.