SQL Basics Tutorial: Creating, Selecting, Updating, and Deleting Data
Welcome to our SQL Basics Tutorial. In this blog post, we'll cover essential SQL operations: creating tables, inserting data, selecting data, updating records, and deleting data. These operations are fundamental for managing databases effectively.
Creating a Table
Let's start by creating a table named MARVEL. This table will store information about various Marvel characters.
CREATE TABLE marvel (
id INT PRIMARY KEY,
name VARCHAR(50),
alias VARCHAR(50),
superpower VARCHAR(100),
team VARCHAR(50),
birth_date DATE,
networth DECIMAL(15, 2)
);
Each column has a specific data type. The PRIMARY KEY ensures each ID is unique.
Inserting Data into a Table
Next, we'll insert data into our MARVEL table. We'll begin by inserting a single entry and then multiple entries at once.
Inserting a single entry:
INSERT INTO marvel (id, name, alias, superpower, team, birth_date, networth)
VALUES (1, 'Tony Stark', 'Iron Man', 'Genius-level intellect', 'Avengers', '1970-05-29', 12000000000.00);
Inserting multiple entries:
INSERT INTO marvel (id, name, alias, superpower, team, birth_date, networth)
VALUES
(2, 'Steve Rogers', 'Captain America', 'Superhuman strength', 'Avengers', '1918-07-04', 1000000.00),
(3, 'Peter Parker', 'Spider-Man', 'Spider-like abilities', 'Avengers', '2001-08-10', 50000.00),
(4, 'Natasha Romanoff', 'Black Widow', 'Expert spy and combatant', 'Avengers', '1984-12-03', 60000.00);
We've added four characters: Iron Man, Captain America, Spider-Man, and Black Widow.
Selecting Data from a Table
To retrieve data, we use the SELECT statement.
Selecting all columns:
SELECT * FROM marvel;
Selecting specific columns:
SELECT name, alias, team FROM marvel;
Updating Data in a Table
To update existing records, we use the UPDATE statement. We will update a single column and then multiple columns.
Updating a single column:
UPDATE marvel
SET networth = 1200000.00
WHERE name = 'Peter Parker';
Updating multiple columns:
UPDATE marvel
SET networth = 1500000.00, team = 'New Avengers'
WHERE name = 'Peter Parker';
The SET clause specifies the new values, and the WHERE clause identifies the record to update.
Deleting Data from a Table
To delete records, we use the DELETE statement. Additionally, we'll show how to drop a column and the entire table.
Deleting a record:
DELETE FROM marvel
WHERE id = 3;
Dropping a column:
ALTER TABLE marvel
DROP COLUMN networth;
Dropping the entire table:
DROP TABLE marvel;
Conclusion
In this tutorial, we've covered the basics of SQL: creating tables, inserting data, selecting data, updating records, and deleting data. These foundational operations are crucial for effective database management.