Chapter 13: SQL Basics

13.8 Practical Exercises of Chapter 13: SQL Basics

Exercise 1: Creating Databases and Tables

Create a new database named ExerciseDB.
In this database, create a table called Customers with the following fields:
- CustomerID (int, primary key)
- FirstName (varchar(255))
- LastName (varchar(255))
- City (varchar(255))

The SQL commands for these tasks would look something like this:

CREATE DATABASE ExerciseDB;
USE ExerciseDB;
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(255),
LastName VARCHAR(255),
City VARCHAR(255)
);

Exercise 2: Inserting Data

Insert the following records into the Customers table:
- CustomerID = 1, FirstName = 'John', LastName = 'Doe', City = 'New York'
- CustomerID = 2, FirstName = 'Jane', LastName = 'Smith', City = 'London'

Here's what your SQL might look like:

INSERT INTO Customers (CustomerID, FirstName, LastName, City)
VALUES
(1, 'John', 'Doe', 'New York'),
(2, 'Jane', 'Smith', 'London');

Exercise 3: Updating and Deleting Data

Update the City of CustomerID = 1 to 'Los Angeles'.
Delete the record where CustomerID = 2.

Your SQL might look like this:

UPDATE Customers
SET City = 'Los Angeles'
WHERE CustomerID = 1;

DELETE FROM Customers WHERE CustomerID = 2;

Exercise 4: Querying Data

Select all records from the Customers table.
Select only the FirstName and City for each record.

Your SQL might look like this:

SELECT * FROM Customers;

SELECT FirstName, City FROM Customers;

Exercise 5: Working with NULL

Insert a new record where CustomerID = 3, FirstName = 'Jim', LastName = 'Brown', but leave City as NULL.
Select all records where City IS NULL.

Your SQL might look like this:

INSERT INTO Customers (CustomerID, FirstName, LastName, City)
VALUES
(3, 'Jim', 'Brown', NULL);

SELECT * FROM Customers WHERE City IS NULL;

Try these exercises out and see how you do! These should give you a well-rounded practice of all the key topics covered in Chapter 13.

Chapter 13 Conclusion

In this chapter, we have delved into the fundamentals of SQL, building upon the foundational understanding laid in the previous chapter. The SQL language, with its powerful yet straightforward syntax, provides us with tools to manipulate and query databases.

We started our journey by understanding how to create databases and tables in SQL, where we learned the importance of defining the structure of our data with appropriate data types. Our exploration of the SELECT command allowed us to retrieve data and understand how a simple query can yield powerful insights.

We then learned about SQL's INSERT, UPDATE, and DELETE commands, which provide us with the ability to manipulate our data at will. These commands are the bedrock of data manipulation, and understanding them is crucial for any SQL user.

We also discussed SQL's WHERE clause, which enables us to filter and refine our queries to our needs. This command represents the power of SQL, the ability to distill vast amounts of data into concise, insightful information.

Finally, we moved into more advanced territory, discussing SQL's ORDER BY, GROUP BY, and JOIN commands. These commands allow us to interact with our data at a higher level, structuring and combining our data in more complex ways.

The NULL value, often overlooked, represents the absence of data. Understanding how SQL handles NULL values in its commands is crucial for preventing unexpected results and errors.

We concluded our exploration with some practical exercises. These exercises provided hands-on experience with the concepts we learned, reinforcing our understanding.

Despite the breadth of this chapter, we've only just scratched the surface of what's possible with SQL. The subsequent chapters will delve deeper into the advanced functionalities of SQL and their applications in various scenarios. As we advance further into this SQL journey, the power and flexibility of this language will continue to unfold.

Thus, as we close this chapter, we should feel confident with the basics of SQL. It's important to note, though, that mastery comes with practice. So, always keep experimenting, exploring, and challenging yourself with more complex queries.

13.8 Practical Exercises of Chapter 13: SQL Basics

Exercise 1: Creating Databases and Tables

Create a new database named ExerciseDB.
In this database, create a table called Customers with the following fields:
- CustomerID (int, primary key)
- FirstName (varchar(255))
- LastName (varchar(255))
- City (varchar(255))

The SQL commands for these tasks would look something like this:

CREATE DATABASE ExerciseDB;
USE ExerciseDB;
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(255),
LastName VARCHAR(255),
City VARCHAR(255)
);

Exercise 2: Inserting Data

Insert the following records into the Customers table:
- CustomerID = 1, FirstName = 'John', LastName = 'Doe', City = 'New York'
- CustomerID = 2, FirstName = 'Jane', LastName = 'Smith', City = 'London'

Here's what your SQL might look like:

INSERT INTO Customers (CustomerID, FirstName, LastName, City)
VALUES
(1, 'John', 'Doe', 'New York'),
(2, 'Jane', 'Smith', 'London');

Exercise 3: Updating and Deleting Data

Update the City of CustomerID = 1 to 'Los Angeles'.
Delete the record where CustomerID = 2.

Your SQL might look like this:

UPDATE Customers
SET City = 'Los Angeles'
WHERE CustomerID = 1;

DELETE FROM Customers WHERE CustomerID = 2;

Exercise 4: Querying Data

Select all records from the Customers table.
Select only the FirstName and City for each record.

Your SQL might look like this:

SELECT * FROM Customers;

SELECT FirstName, City FROM Customers;

Exercise 5: Working with NULL

Insert a new record where CustomerID = 3, FirstName = 'Jim', LastName = 'Brown', but leave City as NULL.
Select all records where City IS NULL.

Your SQL might look like this:

INSERT INTO Customers (CustomerID, FirstName, LastName, City)
VALUES
(3, 'Jim', 'Brown', NULL);

SELECT * FROM Customers WHERE City IS NULL;