Menu iconMenu iconPython & SQL Bible
Python & SQL Bible

Chapter 12: Introduction to SQL

12.6 Practical Exercises of Chapter 12: Introduction to SQL

These exercises are designed to reinforce your understanding of SQL syntax and concepts. It is highly recommended that you use an SQL database software or an online SQL platform to perform these exercises.

Exercise 1

Create a table called "Students" with the following columns: StudentID (integer, primary key), FirstName (varchar), LastName (varchar), Age (integer), Major (varchar).

CREATE TABLE Students (
    StudentID int PRIMARY KEY,
    FirstName varchar(255),
    LastName varchar(255),
    Age int,
    Major varchar(255)
);

Exercise 2

Insert 5 records into the "Students" table with values of your choice.

INSERT INTO Students (StudentID, FirstName, LastName, Age, Major)
VALUES (1, 'John', 'Doe', 20, 'Computer Science');

-- Repeat for other 4 records with different values

Exercise 3

Write a query to select all students who are majoring in "Computer Science".

SELECT * FROM Students WHERE Major = 'Computer Science';

Exercise 4

Update the major of the student with StudentID = 1 to "Data Science".

UPDATE Students SET Major = 'Data Science' WHERE StudentID = 1;

Exercise 5

Delete the record of the student with StudentID = 1.

DELETE FROM Students WHERE StudentID = 1;

Exercise 6

Write a query to select all students, ordered by their age in descending order.

SELECT * FROM Students ORDER BY Age DESC;

Exercise 7

Write a query to count the number of students for each major.

SELECT Major, COUNT(*) FROM Students GROUP BY Major;

Take your time with these exercises and experiment with different commands and queries to fully understand how SQL works. The more you practice, the more comfortable you will become with SQL syntax and operations.

Chapter 12 Conclusion

In this chapter, we embarked on a journey through SQL, a declarative language specifically designed to manage data stored in relational databases. We started with a brief historical review, tracing its origins back to IBM labs in the 1970s, to better understand the motivations behind SQL's creation and its enduring relevance.

Then, we ventured into the practical elements of SQL. We explored the syntax of SQL, which is notably different from Python and other popular programming languages, but it has its own clarity and logic. We studied the structure of SQL statements, learned about keywords, identifiers, operators, and expressions. We examined the basic but powerful operations that SQL allows us to perform on data: SELECT for data retrieval, INSERT for adding new data, UPDATE for modifying existing data, and DELETE for removing data.

We then delved into more complex queries involving sorting (ORDER BY), filtering (WHERE), and aggregation (GROUP BY). These operations enhance the power of SQL by enabling data analysis directly on the database. Understanding these concepts opens the door to more advanced SQL features, such as subqueries, joins, and set operations.

Throughout the chapter, we emphasized the importance of practicing SQL through hands-on exercises. SQL is a skill that is best learned through doing, and the more you interact with databases, write queries, and manipulate data, the more comfortable you will become with SQL.

As we close this chapter, keep in mind that while SQL might seem different and challenging at first, especially if you're more familiar with procedural languages like Python, it is a tool of immense power and versatility in the realm of data management. The ability to directly interact with and analyze data in databases is a skill in high demand in many fields. So keep practicing, keep exploring, and keep expanding your SQL knowledge. In the next chapter, we'll look at how SQL can be used in concert with Python, merging the capabilities of both into a formidable data analysis duo. Stay tuned!

12.6 Practical Exercises of Chapter 12: Introduction to SQL

These exercises are designed to reinforce your understanding of SQL syntax and concepts. It is highly recommended that you use an SQL database software or an online SQL platform to perform these exercises.

Exercise 1

Create a table called "Students" with the following columns: StudentID (integer, primary key), FirstName (varchar), LastName (varchar), Age (integer), Major (varchar).

CREATE TABLE Students (
    StudentID int PRIMARY KEY,
    FirstName varchar(255),
    LastName varchar(255),
    Age int,
    Major varchar(255)
);

Exercise 2

Insert 5 records into the "Students" table with values of your choice.

INSERT INTO Students (StudentID, FirstName, LastName, Age, Major)
VALUES (1, 'John', 'Doe', 20, 'Computer Science');

-- Repeat for other 4 records with different values

Exercise 3

Write a query to select all students who are majoring in "Computer Science".

SELECT * FROM Students WHERE Major = 'Computer Science';

Exercise 4

Update the major of the student with StudentID = 1 to "Data Science".

UPDATE Students SET Major = 'Data Science' WHERE StudentID = 1;

Exercise 5

Delete the record of the student with StudentID = 1.

DELETE FROM Students WHERE StudentID = 1;

Exercise 6

Write a query to select all students, ordered by their age in descending order.

SELECT * FROM Students ORDER BY Age DESC;

Exercise 7

Write a query to count the number of students for each major.

SELECT Major, COUNT(*) FROM Students GROUP BY Major;

Take your time with these exercises and experiment with different commands and queries to fully understand how SQL works. The more you practice, the more comfortable you will become with SQL syntax and operations.

Chapter 12 Conclusion

In this chapter, we embarked on a journey through SQL, a declarative language specifically designed to manage data stored in relational databases. We started with a brief historical review, tracing its origins back to IBM labs in the 1970s, to better understand the motivations behind SQL's creation and its enduring relevance.

Then, we ventured into the practical elements of SQL. We explored the syntax of SQL, which is notably different from Python and other popular programming languages, but it has its own clarity and logic. We studied the structure of SQL statements, learned about keywords, identifiers, operators, and expressions. We examined the basic but powerful operations that SQL allows us to perform on data: SELECT for data retrieval, INSERT for adding new data, UPDATE for modifying existing data, and DELETE for removing data.

We then delved into more complex queries involving sorting (ORDER BY), filtering (WHERE), and aggregation (GROUP BY). These operations enhance the power of SQL by enabling data analysis directly on the database. Understanding these concepts opens the door to more advanced SQL features, such as subqueries, joins, and set operations.

Throughout the chapter, we emphasized the importance of practicing SQL through hands-on exercises. SQL is a skill that is best learned through doing, and the more you interact with databases, write queries, and manipulate data, the more comfortable you will become with SQL.

As we close this chapter, keep in mind that while SQL might seem different and challenging at first, especially if you're more familiar with procedural languages like Python, it is a tool of immense power and versatility in the realm of data management. The ability to directly interact with and analyze data in databases is a skill in high demand in many fields. So keep practicing, keep exploring, and keep expanding your SQL knowledge. In the next chapter, we'll look at how SQL can be used in concert with Python, merging the capabilities of both into a formidable data analysis duo. Stay tuned!

12.6 Practical Exercises of Chapter 12: Introduction to SQL

These exercises are designed to reinforce your understanding of SQL syntax and concepts. It is highly recommended that you use an SQL database software or an online SQL platform to perform these exercises.

Exercise 1

Create a table called "Students" with the following columns: StudentID (integer, primary key), FirstName (varchar), LastName (varchar), Age (integer), Major (varchar).

CREATE TABLE Students (
    StudentID int PRIMARY KEY,
    FirstName varchar(255),
    LastName varchar(255),
    Age int,
    Major varchar(255)
);

Exercise 2

Insert 5 records into the "Students" table with values of your choice.

INSERT INTO Students (StudentID, FirstName, LastName, Age, Major)
VALUES (1, 'John', 'Doe', 20, 'Computer Science');

-- Repeat for other 4 records with different values

Exercise 3

Write a query to select all students who are majoring in "Computer Science".

SELECT * FROM Students WHERE Major = 'Computer Science';

Exercise 4

Update the major of the student with StudentID = 1 to "Data Science".

UPDATE Students SET Major = 'Data Science' WHERE StudentID = 1;

Exercise 5

Delete the record of the student with StudentID = 1.

DELETE FROM Students WHERE StudentID = 1;

Exercise 6

Write a query to select all students, ordered by their age in descending order.

SELECT * FROM Students ORDER BY Age DESC;

Exercise 7

Write a query to count the number of students for each major.

SELECT Major, COUNT(*) FROM Students GROUP BY Major;

Take your time with these exercises and experiment with different commands and queries to fully understand how SQL works. The more you practice, the more comfortable you will become with SQL syntax and operations.

Chapter 12 Conclusion

In this chapter, we embarked on a journey through SQL, a declarative language specifically designed to manage data stored in relational databases. We started with a brief historical review, tracing its origins back to IBM labs in the 1970s, to better understand the motivations behind SQL's creation and its enduring relevance.

Then, we ventured into the practical elements of SQL. We explored the syntax of SQL, which is notably different from Python and other popular programming languages, but it has its own clarity and logic. We studied the structure of SQL statements, learned about keywords, identifiers, operators, and expressions. We examined the basic but powerful operations that SQL allows us to perform on data: SELECT for data retrieval, INSERT for adding new data, UPDATE for modifying existing data, and DELETE for removing data.

We then delved into more complex queries involving sorting (ORDER BY), filtering (WHERE), and aggregation (GROUP BY). These operations enhance the power of SQL by enabling data analysis directly on the database. Understanding these concepts opens the door to more advanced SQL features, such as subqueries, joins, and set operations.

Throughout the chapter, we emphasized the importance of practicing SQL through hands-on exercises. SQL is a skill that is best learned through doing, and the more you interact with databases, write queries, and manipulate data, the more comfortable you will become with SQL.

As we close this chapter, keep in mind that while SQL might seem different and challenging at first, especially if you're more familiar with procedural languages like Python, it is a tool of immense power and versatility in the realm of data management. The ability to directly interact with and analyze data in databases is a skill in high demand in many fields. So keep practicing, keep exploring, and keep expanding your SQL knowledge. In the next chapter, we'll look at how SQL can be used in concert with Python, merging the capabilities of both into a formidable data analysis duo. Stay tuned!

12.6 Practical Exercises of Chapter 12: Introduction to SQL

These exercises are designed to reinforce your understanding of SQL syntax and concepts. It is highly recommended that you use an SQL database software or an online SQL platform to perform these exercises.

Exercise 1

Create a table called "Students" with the following columns: StudentID (integer, primary key), FirstName (varchar), LastName (varchar), Age (integer), Major (varchar).

CREATE TABLE Students (
    StudentID int PRIMARY KEY,
    FirstName varchar(255),
    LastName varchar(255),
    Age int,
    Major varchar(255)
);

Exercise 2

Insert 5 records into the "Students" table with values of your choice.

INSERT INTO Students (StudentID, FirstName, LastName, Age, Major)
VALUES (1, 'John', 'Doe', 20, 'Computer Science');

-- Repeat for other 4 records with different values

Exercise 3

Write a query to select all students who are majoring in "Computer Science".

SELECT * FROM Students WHERE Major = 'Computer Science';

Exercise 4

Update the major of the student with StudentID = 1 to "Data Science".

UPDATE Students SET Major = 'Data Science' WHERE StudentID = 1;

Exercise 5

Delete the record of the student with StudentID = 1.

DELETE FROM Students WHERE StudentID = 1;

Exercise 6

Write a query to select all students, ordered by their age in descending order.

SELECT * FROM Students ORDER BY Age DESC;

Exercise 7

Write a query to count the number of students for each major.

SELECT Major, COUNT(*) FROM Students GROUP BY Major;

Take your time with these exercises and experiment with different commands and queries to fully understand how SQL works. The more you practice, the more comfortable you will become with SQL syntax and operations.

Chapter 12 Conclusion

In this chapter, we embarked on a journey through SQL, a declarative language specifically designed to manage data stored in relational databases. We started with a brief historical review, tracing its origins back to IBM labs in the 1970s, to better understand the motivations behind SQL's creation and its enduring relevance.

Then, we ventured into the practical elements of SQL. We explored the syntax of SQL, which is notably different from Python and other popular programming languages, but it has its own clarity and logic. We studied the structure of SQL statements, learned about keywords, identifiers, operators, and expressions. We examined the basic but powerful operations that SQL allows us to perform on data: SELECT for data retrieval, INSERT for adding new data, UPDATE for modifying existing data, and DELETE for removing data.

We then delved into more complex queries involving sorting (ORDER BY), filtering (WHERE), and aggregation (GROUP BY). These operations enhance the power of SQL by enabling data analysis directly on the database. Understanding these concepts opens the door to more advanced SQL features, such as subqueries, joins, and set operations.

Throughout the chapter, we emphasized the importance of practicing SQL through hands-on exercises. SQL is a skill that is best learned through doing, and the more you interact with databases, write queries, and manipulate data, the more comfortable you will become with SQL.

As we close this chapter, keep in mind that while SQL might seem different and challenging at first, especially if you're more familiar with procedural languages like Python, it is a tool of immense power and versatility in the realm of data management. The ability to directly interact with and analyze data in databases is a skill in high demand in many fields. So keep practicing, keep exploring, and keep expanding your SQL knowledge. In the next chapter, we'll look at how SQL can be used in concert with Python, merging the capabilities of both into a formidable data analysis duo. Stay tuned!