SQL Short Introduction - Learning the Basics of SQL
SQL Basics
SQL stands for Structured Query Language. It is used primarily with create and edit databases. Using SQL queries you can store, view, edit and delete data in a database. SQL is most often used along with a programming language like PHP or ASP. Every popular language provides SQL extensions in order to run SQL queries from that language.
What is a Database?
A database is like a virtual storage area which allows you to store structured data in it for use with queries. A database consists of various tables which have data stored in them in the form of tuples.
To create a database, you can use this SQL command:
CREATE DATABASE DBNAME;
This will create a database with the name DBNAME.
To create a table inside the database, use the command:
CREATE TABLE TABLENAME {DATA1 DATATYPE,DATA2 DATATYPE}
This will create a table with two columns - DATA1 and DATA2. There are various datatypes like number, varchar, varchar2, etc.
For example, to create a table NAME with columns FIRSTNAME, LASTNAME and ID, you can run the following command.
CREATE TABLE NAME {FIRSTNAME varchar(10),LASTNAME varchar(10),ID number PRIMARY KEY}
This creates the following column variables:
- FIRSTNAME - variable string of length 10
- LASTNAME - variable string of length 10
- ID - number - the primary key of the table
Once we have created the table, we can populate it with data or we can change the structure of the table by adding or deleting columns from it. We can also delete the entire table. These queries are DDL queries - Data Definition Language queries.
To delete a table, use this SQL command:
DROP TABLE TABLENAME;
This will delete the table with the name TABLENAME alongwith all the data contained in it.
To delete only all the data contained in a table, use this SQL command:
TRUNCATE TABLE TABLENAME;
This will only delete the tuples in the table, not the table itself.
Altering a Table in SQL
Now that we have learned how to create and delete a table, we will learn how to alter an existing table.
To alter a table by adding a column to an existing table, use this SQL command:
ALTER TABLE TABLENAME
ADD COLUMNNAME DATATYPE
This will add a column to the table with the type DATATYPE.
For example,
ALTER TABLE NAME
ADD MIDDLENAME varchar(10)
To remove a column from an existing table:
ALTER TABLE TABLENAME
DROP COLUMNNAME DATATYPE
For example, this will drop the column added earlier:
ALTER TABLE NAME
DROP MIDDLENAME varchar(10)
To change the type of a column already in the table:
ALTER TABLE TABLENAME
ALTER COLUMN COLUMNNAME DATATYPE
For example, this command will change the datatype of the column MIDDLENAME to varchar2:
ALTER TABLE NAME
ALTER COLUMN MIDDLENAME varchar2(10)
With this, we have completed studying the Data Definition Language (DDL) commands. In the next article, we will see how to view, insert, edit and delete data from a database table.
This post is part of the series: Introduction to SQL
This series is an introduction to SQL. It includes details on tables, DDL and DML queries, joins in SQL, and more.
