SQL Basics
Create Database, Create Table, Insert Data, Update Data, and Alter Table
Introduction
SQL (Structured Query Language) is used to store and manage data in a database. It helps us create databases, create tables, insert data, update data, and change the table structure.
In this article, I will explain:
- How to create a database
- How to create a table
- How to insert data
- Difference between INSERT INTO and UPDATE
- How to use ALTER TABLE
- Different ways to alter a table
1. Create a Database
A database is a place where data is stored.
Syntax:
Create Database <db_name>;
Example:
Create database Pet_Store;
To use the database :
USE Pet_Store;
Now all the tables will be created inside this database.
2. Create a Table :
A table stores data in rows and columns.
Syntax:
create table <table_name>( column_name datatype );
Example :
CREATE TABLE pet_shop (id INT PRIMARY KEY, breed VARCHAR(50), AGE INT, Color VARCHAR(20));
Explanation
- id → Unique ID for each pet.
- breed → Breed of the pet.
- age → Age of the pet.
- color → Color of the pet.
2. INSERT INTO
The INSERT INTO statement is used to add new records to a table.
Example 1: Insert One Row
INSERT INTO pet_shop(breed,age,color) VALUES ( ‘Labrador’,2, ‘Brown’);
Example 2: Insert Multiple Rows
INSERT INTO pet_shop (breed, age, color) VALUES ( ‘german_shepherd’,1, ‘black’), ( ‘pug’,3, ‘white’), ( ‘pomerian’,2, ‘white’);
3. UPDATE
The UPDATE statement is used to change existing data in a table.
Get Surendarpoojala’s stories in your inbox
Join Medium for free to get updates from this writer.
Example 1: Update One Column
UPDATE pet_shop SET color = ‘Cream’ WHERE id = 3;
Example 2: Update Multiple Columns
UPDATE pet_shop SET age = 3, color = ‘Golden Brown’ WHERE id = 2;
This updates both the age and color.
Example 3: Update All Rows
UPDATE pet_shop SET age = age + 1;
This increases the age of every pet by one year.
4. ALTER TABLE
The ALTER TABLE statement is used to change the structure of a table.
Add a New Column :
ALTER TABLE pet_shop ADD price INT;
Adds a new price column.
Add Multiple Columns :
ALTER TABLE pet_shop
ADD owner_name VARCHAR(50),
ADD gender VARCHAR(10);
Adds owner_name and gender columns.
Modify a Column :
ALTER TABLE pet_shop MODIFY breed VARCHAR(100);
Changes the size of the breed column.
Rename a Column
ALTER TABLE pet_shop RENAME COLUMN color TO pet_color;
Changes the column name from color to pet_color.
Rename the Table:
ALTER TABLE pet_shop RENAME TO pet_store;
Changes the table name from pet_shop to pet_store.
Drop a Column:
ALTER TABLE pet_shop DROP COLUMN price;
Removes the price column
Add a Primary Key :
ALTER TABLE pet_shop ADD PRIMARY KEY(id);
Makes id the primary key
Adding a Unique Constraint
ALTER TABLE pet_shop ADD UNIQUE(breed);
Conclusion
In this article, we learned the basic SQL commands that are used to work with databases. We started by creating a database and a table, then learned how to insert new records using the INSERT INTO statement. We also saw how to update existing records using the UPDATE statement.
After that, we explored the ALTER TABLE command and learned different ways to modify a table, such as adding a new column, changing a column's data type, renaming a column, renaming a table, and deleting a column.
Thank you for reading, I hope this article helped you understand the basics of SQL in a simple and easy way.
