SQL or Structured Query Language is a query language or special purpose programming language basically designed to manage the database or relational database management system (RDBMS). It can also be defined as the medium through which you can access and manipulate the database. SQL supports distributed databases (scattered databases over different computer systems), which is the main reason it is widely used in PC database systems. SQL is based on Tuple relations and consists of DML (Data Manipulation Language) and DDL (Data Definition Language). SQL is used for creating, inserting, retrieving and updating your database.
The most basic or important SQL commands are:
- CREATE DATABASE
- CREATE TABLE
- INSERT INTO
- AND & OR operators
- ALTER TABLE
Every table in a database has a specific name such as the table seen below. This table has the name “Workers” and every table has one or more rows that contain records.
The CREATE DATABASE command is a simple command used to create a new database. You can create other database objects after creating tables, procedures and views.
CREATE DATABASE database-name;
CREATE DATABASE first_database;
This will create a new database with the name “first_database”.
The CREATE TABLE command creates a new table into the existing database. A table is a combination of rows and columns. In addition, it can have different columns that can store different values of different data types.
CREATE TABLE table_name ( Column1 datatype(size), Column2 datatype(size), Column2 datatype(size), ….. );
The “datatype” specifies the type of data the column will hold and the size specifies the limit or maximum length of the value. For instance, data types can be “int” for integer values and “varchar” for alphanumeric values.
Now let us create a new table of name “Emp” that will contain the columns “EmpID”,”EmpName” and ”EmpCity”.
CREATE TABLE Emp (EmpID int, EmpName varchar(200), EmpCity varchar(200));
The above example will create a new table into the database with the name Emp, which will look like this:
INSERT INTO command
The INSERT INTO command inserts new data or record into the database table. The INSERT INTO command basically adds a new row into the database table.
INSERT INTO table_name VALUES (value1,value2,value3,…..);
INSERT INTO table_name(column1,column2,column3,…..) VALUES (value1,value2,value3,….);
This statement will insert data only into the selected columns.
Let us consider the above “Workers” table
INSERT INTO Workers (WorkerID, WorkerName, City, Country) VALUES (‘4’,’Shayne’,’Naples’,’Italy’);
The above example will insert the given values into the selected columns and the new table will now contain another row of given data.
The SELECT command is used to select records from the database. The SELECT command is used to retrieve data from the database table. It is used to retrieve a particular record or whole record from the column.
SELECT column name, column name FROM table name; OR SELECT * FROM table_name;
1) Let’s consider the above “Workers” table and apply the SELECT command
SELECT WorkerName, City FROM Workers;
The above example will select data only from column “WorkerName” and “City” from the table Workers.
2) Consider the above table and apply the select statement
SELECT * FROM Workers;
This example will select the whole table including all columns (indicated by the asterisk).
The WHERE clause is a conditional statement that helps filter values. The WHERE clause displays only records you want to see and filters unwanted records from the table.
SELECT column_name,column_name FROM table_name WHERE column_name operator value;
Now let us take our original “Workers” table for this example.
SELECT WorkerName, City FROM Workers WHERE City=’Berlin’;
This example will only select the “WorkerName” and “City” from the row that has the City “Berlin”.
AND & OR operators:
The AND operator is a logical operator that is used to compare two or more conditions and produces a result according to those conditions. The AND operator will only show data when both conditions are true.
Let us take the “Workers” table
SELECT * FROM Workers WHERE City=’Berlin’ AND Country=’Germany’;
This example will display records if both conditions are true i.e., City=’Berlin’ and Country=’Germany’;
The OR operator is a logical operator that is used to compare two or more conditions and produces a result according to those conditions. The OR operator will display records that match any condition that returns true.
Again let us take the “Workers” table
SELECT * FROM Workers WHERE City=’Berlin’ OR Country=’Germany’;
This example will display data if either city is Berlin or Country is Germany.
The DELETE command deletes rows from a database table. It can also be used to delete all records from a table without deleting the table object and even keeps the structure and attributes.
DELETE FROM table_name WHERE any column=any value; And the syntax for deleting whole data from the table is DELETE * FROM table name;
Let us take the above “Workers” table
DELETE FROM Workers WHERE WorkerName=’sam’ AND City=’London’;
Now the new table will look like this
The UPDATE command updates the present records in the database table. UPDATE command uses “WHERE” command to specify which row would be affected.
UPDATE table-name SET column_1=value1,column_2=value2,column_3=value3,……. WHERE any column=any value;
Let’s take our original “Workers” table and update its record.
UPDATE Workers SET WorkerName=’Dean’, City=’Munich’ WHERE WorkerName=’Michael’;
This example will update “Workers” and change “WorkerName” from Michael to Dean and City from Berlin to Munich. The new table will look like this.
ALTER TABLE command deletes and adds the columns in an existing database table. Basically it modifies the existing database table.
For adding column:
ALTER TABLE table_name ADD column _name datatype
Let us take our “Workers” table
ALTER TABLE Workers ADD Dob year
We need to specify a data type to add the column to an existing database table, because we have to specify the kind of value this new column will hold.
Now the table will look like this:
For deleting column:
ALTER TABLE table_name DROP COLUMN column_name
ALTER TABLE Workers DROP COLUMN Dob
Now the above table will look like this: