Learn SQL: Fundamentals of SQL for beginners

sqlSQL 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.

Learn more about SQL  through this tutorial at Udemy.com

The most basic or important SQL commands are:

  • CREATE DATABASE
  • CREATE TABLE
  • INSERT INTO
  • SELECT
  • WHERE
  • AND & OR operators
  • DELETE
  • UPDATE
  • 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.

Workers

S1

 

CREATE DATABASE

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.

General Syntax:

CREATE DATABASE database-name;

Example:

CREATE DATABASE first_database;

This will create a new database with the name “first_database”.

CREATE TABLE

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.

Learn SQL from scratch through a tutorial at Udemy.com

General Syntax:

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.

Example:

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:

 

S2


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.

Get to know more about SQL through a tutorial at Udemy.com

General Syntax:

INSERT INTO table_name VALUES (value1,value2,value3,…..);

Or

 INSERT INTO table_name(column1,column2,column3,…..) VALUES (value1,value2,value3,….);

This statement will insert data only into the selected columns.

Example:

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.

S3


SELECT command:

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.

General Syntax:

SELECT column name, column name FROM table name;

OR

SELECT * FROM table_name;

Example:

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.

S4


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).

S5


WHERE:

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.

General Syntax:

SELECT column_name,column_name FROM table_name WHERE column_name operator value;

Example:

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.

Example:

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.

Example:

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.

DELETE command:

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.

General Syntax:

DELETE FROM table_name WHERE any column=any value;

And the syntax for deleting whole data from the table is

DELETE * FROM table name;

Example:

Let us take the above “Workers” table

DELETE FROM Workers WHERE WorkerName=’sam’ AND City=’London’;

Now the new table will look like this

S6

 

UPDATE command:

The UPDATE command updates the present records in the database table. UPDATE command uses “WHERE” command to specify which row would be affected.

General Syntax:

UPDATE table-name

SET column_1=value1,column_2=value2,column_3=value3,…….  WHERE any column=any value;

Example:

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.

S7


ALTER TABLE:

ALTER TABLE command deletes and adds the columns in an existing database table. Basically it modifies the existing database table.

General Syntax:

For adding column:

ALTER TABLE table_name  ADD column _name datatype

Example:

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:

 

S8

For deleting column:

ALTER TABLE table_name DROP COLUMN column_name

Example:

ALTER TABLE Workers DROP COLUMN Dob

Now the above table will look like this:

S9