Tag Archives: sql tutorials for beginners

SQL – WHERE Clause

SQL WHERE Clause

SQL WHERE Clause is used to filter the recoreds on the basis of some condition.
This will fetch the results which will fullfill the condition specified.

SQL WHERE Syntax :

SELECT ColumnName1,ColumnName2,ColumnName3 FROM TableName WHERE Condition

Suppose The “Users” Table has following records :

SQL WHERE Clause tableFull

Suppose we have to find those records which have ‘city’ value as “DELHI”

Example 1 – : The Following Example will fetch the results as

Example

SELECT * FROM Users WHERE city = “DELHI”;

The Above Query Will Fetch the results :

resultDelhi

Example 2 – : Fetch The Results where user’s ID is ‘5’

Example

SELECT * FROM Users WHERE ID = “5”;

The Above Query Will Produce the result as :

idResult

SQL – SELECT

SQL – SELECT Statement :

Sql Select Statement is used to fetch records from database table.

SQL – SELECT Syntax 1 (Select All Columns) :

SELECT *FROM TableName;

SQL – SELECT Syntax 2 (Select few Columns) :

SELECT ColumnName1, ColumnName2, ColumnName3,…. FROM TableName;

SQL Select All Columns Example :

Example

SELECT * FROM Users;

The above Query will display the results as :

 sql select all-c…sql-query

Select-all-c…ql-query

SQL Select Few Columns Example :

Example

SELECT ID, Name, Email FROM Users;

The above Query will display the results as :

Sql-INsert-in-Selected-columns

SQL – INSERT

SQL – INSERT Statement:

SQL INSERT INTO Statement is used to insert a new row(ie. record) in a table.

SQL INSERT INTO Syntax:

INSERT INTO TableName VALUES(Value1,Value2,….);

Note: The Above Syntax inserts rows in all columns of the table.

If You want to insert values in selected columns the following syntax is used :

INSERT INTO TableName (ColumnName1,ColumnName2,….)VALUES(Value1,Value2,….);

EXAMPLE 1 (Insert in all column) :

Example

INSERT INTO Users VALUES(‘1′,’John’,’john@yahoo.com’,’USA’,’NewYork’);

1 New row will be inserted.

Now the table has its first record after the selection it will show:

MysqlInsertStatement

EXAMPLE 2 (Insert in few columns only):

Example

INSERT INTO Users (ID,Name,Email)VALUES(‘2′,’Devid’,’david@yahoo.com’);

Note: We need to specify the column name in which we want to insert values Like If want to insert values in ID, Name and Email Columns only…

Now the table will look like:

SQL - INSERT-in-Selected-columns

Sql-Insert-in-Selected-columns

SQL – CREATE TABLE

SQL Create Table Syntax is used to create a table in database .

Tables are collection of rows & columns.

SQL – CREATE TABLE Syntax

CREATE TABLE Table_Name
(
ColumnName1 datatype,
ColumnName2 datatype,
ColumnName3 datatype,
….
….
….

);

ColumnName1, ColumnName2, ColumnName3, .. are name of columns.
datatype specifies the type of data to be stored in that column example : integer, variable , text or decimal etc..

SQL – CREATE TABLE Example

Example

CREATE TABLE Users
(
ID int,
Name varchar(100),
Email varchar(100),
Address varchar(100),
City varchar(100),
PRIMARY KEY (ID)
);

* PRIMARY KEY : Primary creates constraint on the column ID which will contain unique & not null values. We will Explain it in later topics.

Users Table will be created in CustomersDb.

To Verify the table Creation Use The following Command .

DESC TableName;

Example

DESC Users;

Which will Display Created table AS :

SQL create table

Mysql Table

SQL – Select Database

To Select database for work use the following syntax :

Syntax for SQL – Select Database

USE Keyword is used to select the database.

USE DatabaseName;

Example

USE CustomersDb;

You are now switched to database cusetomersdb.
Now you can perform actions like select , create table etc.

SQL – CREATE Database

SQL – CREATE Database Syntax is used to create a database .

Syntax for SQL – Create Database

The Sytax for CREATE Database is

CREATE Database Database_name;

Example

CREATE Database CustomersDB;

To See the Databases following command is Used.

SHOW DATABASES;

Which will Show the result as :

SQL - CREATE Database Show

My sql Show Databases

SQL – Data Types

Most Common used SQL Data types

char(n) : Fixed n numbers of characters accepted(Memory will be Allocated for n character at the time of creation). Max 8,000 characters allowed.

varchar(n) : Variable n numbers of characters accepted(Memory will be Allocated for the character at run time). Max 8,000 characters allowed.

int : Range – whole numbers from 2,147,483,648 to 2,147,483,647
bigint : Range – whole numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
smallint : Range – whole numbers from -32,768 32,767
tinyint : Range – whole numbers from 0 to 255
decimal : Range Allows decimal numbers from -10^38 +1 to 10^38 -1
numeric : Allows numbers from -10^38 +1 to 10^38 –1.

text : Variable character string. Maximum 2GB of text data can be accepted.
nchar : Fixed size Unicode string. Maximum 4,000 characters allowed.
nvarchar :Variable size Unicode string. Maximum 4,000 characters allowed.

datetime : Range – From 31 Jan, 1753 to Dec 31, 9999
date : Stores date a date From January 1, 0001 to December 31, 9999
time : Store a time example : 1:00 PM

SQL Syntax

Most Commonly used SQL Keywords are

SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, JOIN, USE, SHOW

Note :

  1. Sql is case insensitive means you can use both upper case & lower case
  2. My Sql: If you are using MySql, Table Name are case sensitive means use the table names in MySql as they are
    in MySql Database.

Semicolon :

Semicolon after syntax is optional after the each statement but always use semicolon after the statement.

Example

select *from users;

 

Some Common Sql Commands

1. SELECT
2. INSERT
3. UPDATE
4. JOIN
5. DELETE
6. ALTER
7. DROP

SQL Tutorial

SQL (pronounced “ess-que-el”) stands for Structured Query Language.
SQL is standard language to communicate with a database.
According to ANSI (American National Standards Institute), Sql is the standard language for relational database management systems.

For Selecting all data from Users Table

Following Query is used :

Example

select *from users

Try it »

Get the best out of yourself with our SQL Tutorial online