Mastering SQL Fundamentals: Exploring Data Definition and Manipulation

As a data science student, diving into SQL has been an exciting and eye-opening experience. SQL, short for Structured Query Language, is essential for anyone working with data. It allows us to interact with databases, retrieve information, and make meaningful changes. In this article, I'll break down the foundational concepts I’ve learned, focusing on Data Definition Language (DDL) and Data Manipulation Language (DML), with a few practical examples that anyone can try.

Understanding Data Definition Language (DDL)

DDL is all about defining and modifying the structure of your database. It helps us create tables, alter them, and even delete them. Let's look at some examples.

Creating a Table

We use the CREATE TABLE command to define a new table. In our example, I created a table amazon_orders to store order details like the order ID, date, product name, total price, and payment method.

CREATE TABLE amazon_orders(
    order_id INT,
    order_date DATE,
    product_name VARCHAR(100),
    total_price DECIMAL(5,3),
    payment_method VARCHAR(25)
);        

Here, each column has a specific data type. For instance, total_price is a DECIMAL(5,3) which means there are 2 digits before the decimal and 3 after it.

Altering a Table

Over time, you might need to modify your table's structure. The ALTER TABLE command is useful for such tasks. Here’s an example of changing the data type of a column:

ALTER TABLE amazon_orders 
ALTER COLUMN order_date TYPE TIMESTAMP WITH TIME ZONE;        

We’ve converted the order_date column from DATE to TIMESTAMP WITH TIME ZONE, allowing us to track orders with time and time zone details.

Need to revert the change? No problem:

ALTER TABLE amazon_orders
ALTER COLUMN order_date TYPE DATE;        

We can also add or drop columns easily:

ALTER TABLE amazon_orders
ALTER COLUMN order_date TYPE DATE;        

We can also add or drop columns easily:

ALTER TABLE amazon_orders ADD COLUMN referral VARCHAR(10);
ALTER TABLE amazon_orders DROP COLUMN referral;        

Dropping a Table

If you no longer need a table, you can delete it using DROP TABLE:

DROP TABLE amazon_orders;        

But, if you want to clear data and keep the table structure, use DELETE FROM instead, which we’ll discuss next.

Introducing Data Manipulation Language (DML)

DML focuses on modifying data within your tables. This includes inserting, updating, deleting, and querying data.

Inserting Data

Inserting data into a table is straightforward. For example, here's how we added some orders to our amazon_orders table:

INSERT INTO amazon_orders VALUES(1, '2022-11-25', 'Macbook Pro 2015', 12.514, 'Easypesa');        

We can even include optional columns:

INSERT INTO amazon_orders VALUES(4, '2022-11-25', 'Macbook Pro 2017', 72.512, 'Easypesa', 'Yes');        

Selecting Data

To retrieve data, we use the SELECT statement. You can retrieve all the data from a table like this:

SELECT * FROM amazon_orders;        

Or, if you only need specific columns:

SELECT order_id, total_price FROM amazon_orders;        

Do you want to limit the number of rows you see? Try this:

SELECT product_name, total_price FROM amazon_orders LIMIT 2;        

Sorting Data

Sorting is also a key part of querying. Here's how to sort data by date in descending order:

SELECT * FROM amazon_orders ORDER BY order_date DESC;        

You can even sort by multiple columns:

SELECT * FROM amazon_orders ORDER BY order_date DESC, total_price ASC;        

Deleting Data

If you need to remove specific rows, you can delete them using the DELETE statement. Here’s an example:

DELETE FROM amazon_orders WHERE product_name = 'Macbook Pro 2017';        

This removes only the row where the product name is 'Macbook Pro 2017'.

Adding Constraints for Data Integrity

In SQL, constraints help ensure the accuracy and reliability of the data in your tables. While creating another table orders, I used constraints like NOT NULL, CHECK, and DEFAULT.

CREATE TABLE orders(
    order_id INTEGER NOT NULL,
    product_name VARCHAR(100),
    payment_method VARCHAR(25) CHECK (payment_method IN ('Easypesa', 'JazzCash')) DEFAULT 'UBL',
    category VARCHAR(25) DEFAULT 'Mens Wear',
    PRIMARY KEY (order_id, product_name)
);        

In this case:

• NOT NULL ensures that the order_id can’t be empty.
• CHECK enforces that the payment method can only be ‘Easypesa’ or ‘JazzCash’.
• DEFAULT provides a default value for the category column if no value is entered.

Updating Data

Sometimes we need to update existing records. Here's how to change the product name for a specific order:

UPDATE orders SET product_name = 'Shirt' WHERE order_id = 3;        

What’s Next?

This foundation in SQL has provided a solid understanding of how to structure and manipulate data efficiently. Moving forward, I’m eager to explore advanced queries, joins, subqueries, and how SQL integrates with other tools and technologies to unlock deeper insights from data.

SQL’s versatility and power make it an essential skill, and learning it step by step makes the process both rewarding and enjoyable. Stay tuned for more insights as I continue this exciting journey into SQL!

Memory updated

My Journey into SQL: A Beginner's Experience with Data Definition and Manipulation

As a data science student, diving into SQL has been an exciting and eye-opening experience. After completing my first two lectures on SQL, I want to share what I’ve learned so far. SQL, short for Structured Query Language, is essential for anyone working with data. It allows us to interact with databases, retrieve information, and make meaningful changes. In this article, I'll break down the foundational concepts I’ve learned, focusing on Data Definition Language (DDL) and Data Manipulation Language (DML), with a few practical examples that anyone can try.

Understanding Data Definition Language (DDL)

DDL is all about defining and modifying the structure of your database. It helps us create tables, alter them, and even delete them. Let's look at some examples.

Creating a Table

We use the CREATE TABLE command to define a new table. In our example, I created a table called amazon_orders to store order details like the order ID, date, product name, total price, and payment method.

sql        

Copy code

CREATE TABLE amazon_orders( order_id INT, order_date DATE, product_name VARCHAR(100), total_price DECIMAL(5,3), payment_method VARCHAR(25) );

Here, each column has a specific data type. For instance, total_price is a DECIMAL(5,3) which means there are 2 digits before the decimal and 3 after it.

Altering a Table

Over time, you might need to modify your table's structure. The ALTER TABLE command is useful for such tasks. Here’s an example of changing the data type of a column:

sql        

Copy code

ALTER TABLE amazon_orders ALTER COLUMN order_date TYPE TIMESTAMP WITH TIME ZONE;

We’ve converted the order_date column from DATE to TIMESTAMP WITH TIME ZONE, allowing us to track orders with time and time zone details.

Need to revert the change? No problem:

sql        

Copy code

ALTER TABLE amazon_orders ALTER COLUMN order_date TYPE DATE;

We can also add or drop columns easily:

sql        

Copy code

ALTER TABLE amazon_orders ADD COLUMN referral VARCHAR(10); ALTER TABLE amazon_orders DROP COLUMN referral;

Dropping a Table

If you no longer need a table, you can delete it using DROP TABLE:

sql        

Copy code

DROP TABLE amazon_orders;

But, if you just want to clear data and keep the table structure, use DELETE FROM instead, which we’ll discuss next.

Introducing Data Manipulation Language (DML)

DML focuses on modifying data within your tables. This includes inserting, updating, deleting, and querying data.

Inserting Data

Inserting data into a table is straightforward. For example, here's how we added some orders into our amazon_orders table:

sql        

Copy code

INSERT INTO amazon_orders VALUES(1, '2022-11-25', 'Macbook Pro 2015', 12.514, 'Easypesa');

We can even include optional columns:

sql        

Copy code

INSERT INTO amazon_orders VALUES(4, '2022-11-25', 'Macbook Pro 2017', 72.512, 'Easypesa', 'Yes');

Selecting Data

To retrieve data, we use the SELECT statement. You can retrieve all the data from a table like this:

sql        

Copy code

SELECT * FROM amazon_orders;

Or, if you only need specific columns:

sql        

Copy code

SELECT order_id, total_price FROM amazon_orders;

Want to limit the number of rows you see? Try this:

sql        

Copy code

SELECT product_name, total_price FROM amazon_orders LIMIT 2;

Sorting Data

Sorting is also a key part of querying. Here's how to sort data by date in descending order:

sql        

Copy code

SELECT * FROM amazon_orders ORDER BY order_date DESC;

You can even sort by multiple columns:

sql        

Copy code

SELECT * FROM amazon_orders ORDER BY order_date DESC, total_price ASC;

Deleting Data

If you need to remove specific rows, you can delete them using the DELETE statement. Here’s an example:

sql        

Copy code

DELETE FROM amazon_orders WHERE product_name = 'Macbook Pro 2017';

This removes only the row where the product name is 'Macbook Pro 2017'.

Adding Constraints for Data Integrity

In SQL, constraints help ensure the accuracy and reliability of the data in your tables. While creating another table orders, I used constraints like NOT NULL, CHECK, and DEFAULT.

sql        

Copy code

CREATE TABLE orders( order_id INTEGER NOT NULL, product_name VARCHAR(100), payment_method VARCHAR(25) CHECK (payment_method IN ('Easypesa', 'JazzCash')) DEFAULT 'UBL', category VARCHAR(25) DEFAULT 'Mens Wear', PRIMARY KEY (order_id, product_name) );

In this case:

• NOT NULL ensures that the order_id can’t be empty.
• CHECK enforces that the payment method can only be ‘Easypesa’ or ‘JazzCash’.
• DEFAULT provides a default value for the category column if no value is entered.

Updating Data

Sometimes we need to update existing records. Here's how to change the product name for a specific order:

sql        

Copy code

UPDATE orders SET product_name = 'Shirt' WHERE order_id = 3;

What’s Next?

These first two lectures have laid the foundation for understanding SQL, covering both the structure and manipulation of data. As I progress, I’m excited to dive deeper into more advanced queries, joins, subqueries, and eventually integrating SQL with other tools and technologies.

SQL is powerful, and learning it step by step makes it manageable and fun. Stay tuned as I continue to explore SQL and share my insights!

Thank you for reading! If you found this article helpful, don’t forget to like, share, and leave your thoughts in the comments. Let’s learn SQL together!