Title: Comprehensive Guide on Database Operations
Description Paragraph:
In this article, you will be taken through the basics of Database Operations through SQL. By the end of this article, you will have a clear understanding of creating tables, inserting data, updating and deleting rows, and validating the outcomes.
Explanation for the video:
The embedded YouTube video provides visual aid to complement the text in the article. Watch the video to gain a better understanding of the concepts discussed in the article.
Key Concepts Explanation
Description of the key concepts. Provide key concepts as subheadings and explain them with inline code examples. Multi-line code examples should be highlighted using code highlighter.
Key Concept 1
In this section, you will learn about creating a table in SQL with various column types.
CREATE TABLE courses (
course_id SERIAL PRIMARY KEY,
course_name VARCHAR(60),
course_author VARCHAR(40),
course_status VARCHAR(15),
course_published_dt DATE
);
Key Concept 2
Description of the key concept 2 with inline code examples
Multi line code examples (if any) should be highlighted using code highlighter
Hands-On Tasks
Description of the hands-on tasks. Provide a list of tasks that the reader can perform to apply the concepts discussed in the article.
- Task 1: Create a table named ‘courses’ with the specified column names and types.
- Task 2: Insert sample data into the ‘courses’ table according to the provided data.
Conclusion
Summary of the main points discussed in the article. Encourage the reader to practice or engage with the community for further learning.
Exercises - Database Operations
Let’s create a table and perform database operations using direct SQL.
Exercise 1 - Create Table
Create table - courses
Provide the CREATE TABLE script using the provided column names and data types.
Exercise 2 - Inserting Data
Provide the INSERT INTO statements for inserting data into the 'courses' table using the provided data.
Exercise 3 - Updating Data
Provide the UPDATE statement to change the status of specific courses along with the published date.
Exercise 4 - Deleting Data
Provide the DELETE statement to remove courses that are not in 'draft' or 'published' status.
Validation:
SELECT course_author, COUNT(1) AS course_count
FROM courses
WHERE course_status = 'published'
GROUP BY course_author
ORDER BY course_count DESC;
| Course Author | Course Count |
|---|---|
| Bob Dillon | 5 |
| Elvis Presley | 2 |
| Uncle Sam | 1 |