Techumber
Home Blog Work

The Quick SQL Tutorial For Absolute Beginners

Published on April 29, 2013

Three years ago i prepared a notes on Structured Query Language(SQL) for my studies. I used to read it just 10 minutes before go to an exam or an interview. Today i am sharing that same note with you. Hoping this will help you as quick reference guide. Total SQL commands are 3 types.

A Quick SQL Tutorial For Absolute Beginners

1)Data Definition Language(DDL)    a)Create    b)Alter    c)Drop    d)Rename 2)Data Manipulation Language (DML)    a)Select    b)Insert    c)Update    d)Delete    e)Truncate 3)Data Control Language(DCL)    a)Grant    b)Revoke

1.a)Create

This command used to create table. Syntax:

CREATE TABLE <table-name>
(
<attribute-name1>(<data-type>),
<attribute-name2>(<data-type>),
<attribute-name3>(<data-type>),
.
.
.
.
<attribute-name4> <data-type>
)

Example:

CREATE TABLE Student
(
stuid INT,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
DOB DATE NOT NULL
);

1b)Alter

This command used to modified the table structure. Syntax:

--Adding New column to table
ALTER TABLE <table-name>
ADD <column-name> <data-type>(length)

--Modifying column
ALTER TABLE <table-name>
MODIFY <column-name> <data-type>(lenght)

--Removing column from table
ALTER TABLE <table-name>
DROP COLUMN <column-table>

Example:

--Adding New join data column to student table
ALTER TABLE Student
ADD JoinDate DATE

--Modifying lastname colument datatype and length
ALTER TABLE Student
MODIFY lastname nvarchar(40)

--Removing column from table
ALTER TABLE Student
DROP COLUMN firstname

1c)Drop

Drop command used to delete table from database. Syntax:

DROP TABLE <table-name>

Example:

DROP TABLE Student

1d)Rename

This command used to rename the existing table. Syntax:

RENAME <old-table-name> to <new-table-name>

Example:

RENAME Student to StudentIndex

2a)Select

This command is used to retrieve data from database. Syntax:

SELECT <column-name> FROM <table-name>

Example:

SELECT firstname, lastname FROM Student

Read more SQL SELECT statement from here.

2b)Insert

This command is used to add new row to the table. Syntax:

INSERT INTO <table-name>(col1,col2,....coln) VALUES (val1,val2,...valn)

Example:

INSERT INTO Student(firstname,lastname,DOB)VALUES ('aravind','buddha','6/8/1990")

2c)Update

This command is used to update data inside the table. Syntax:

UPDATE <table-name>
SET <column1> = <value1>, <column2> = <value2>...., <column n> = <value n>
WHERE <condition>;

Example:

UPDATE Student
SET DOB = "12/8/1990"
WHERE firstname='aravind'

2d)Delete

This command is used to delete data from table. Syntex:

DELETE FROM <table-name>
WHERE <condition>

Example:

DELETE FROM Student
WHERE firstname="aravind"

2e)Truncate

This command is used to delete complete data from table not structure. Syntax:

TRUNCATE TABLE <table-name>

Example

TRUNCATE TABLE Student

3a)Grant

In general database admins uses this commands. Grant command used to give permission to user for certain operations. Syntax:

GRANT <privilege>
ON <object>
TO <user>

Example:

GRANT CREATE TABLE
TO user1;

3b)Revoke

This command is used to take back the previously allocated permissions from user. Syntax:

REVOKE <privilege>
ON <object>
FROM <username>

Example:

REVOKE CREATE TABLE
FROM user1

Conclusion

This are very sql commands we use frequently in our projects. But these are not complete list of commands. There are still some advanced SQL Commands. But these commands are enough for beginners.