×
☰ See All Chapters

Basics of SQL

Create Table

CREATE STATEMENT FOR CREATING DATABASE TABLES

Syntax

CREATE TABLE <table-name>

(

<column-name1> <data-type(size)> <constraint>,

<column-name2> <data-type(size)> <constraint>,

<column-name3> <data-type(size)> <constraint>,

<column-name4> <data-type(size)> <constraint>,

<column-name5> <data-type(size)> <constraint>

);

Example

CREATE TABLE CUSTOMER

(

CUSTOMER_ID INT,

CUSTOMER_NAME VARCHAR(30) ,

EMAIL VARCHAR(30) ,

DOB DATE

);

 

CREATE TABLE EMP (

EMP_ID INT(5),

NAME VARCHAR(30),

SALARY INT(6));

ALTER STATEMENT FOR CHANGING THE STRUCTURE OF THE DATABASE TABLES

Syntax

ALTER TABLE <table-name>

ADD (<column-name> <data-type> <constraint>);

 

ALTER TABLE <table-name>

DROP COLUMN (<column-name>;

Example

ALTER TABLE CUSTOMER

ADD (PHONE NUMERIC (10));

 

ALTER TABLE CUSTOMER

DROP COLUMN PHONE;

 

INSERT Data

Syntax

For inserting values in all the columns

INSERT  INTO <table-name> VALUES (<value1>, <value2>, <value3>);

COMMIT;

For inserting values in particular columns

INSERT  INTO <table-name> (<column1>, <column2>, <column3>) VALUES  (<value1>, <value2>, <value3>);

COMMIT;

Example

INSERT INTO EMP VALUES (103,'Manu',3000);

INSERT INTO EMP VALUES(104,'Tyagraj',4000);

INSERT INTO EMP (EMP_ID,NAME,SALARY) VALUES(105,'Likitha',5000);

INSERT INTO EMP (EMP_ID,NAME,SALARY) VALUES(106,'Advith',6000);

INSERT INTO EMP (EMP_ID,NAME,SALARY) VALUES(106,'AdiTemp',6000);

COMMIT;

Updating and deleting existing data

Syntax

UPDATE <table-name>

 SET   <column1> = <value1>,

       <column2> = <value2>,

       <column3> = <value3>  

 [WHERE condition]

Anything specified in [ ] is optional. If WHERE condition is not specified then all the records will be updated.

DELETE FROM <table-name> [WHERE condition]

If WHERE condition is not specified then all the records of the table will be deleted.

Example

UPDATE EMP

SET NAME = 'CHINNU'

WHERE EMP_ID = 106;

COMMIT;

 

DELETE FROM EMP WHERE Name = 'AdiTemp';

COMMIT;

Querying Table

Syntax

SELECT * / <column1>, <column2>

FROM <table-name1>, <table-name2>

[WHERE  < condition1/expression1 >   and / OR  <condition2 / expression2 >]

[GROUP BY < column-name1 >.< column-name2 >  

[HAVING condition]

[ORDER BY < column-name1 >.< column-name2 >  [ ASC | DESC ] ]

Anything specified in [ ] is optional.

  • The SELECT statement is used in conjunction with the FROM clause to extract data from the database in an organized, readable format. 

  • The SELECT keyword in a query is followed by a list of columns that you want displayed as part of the query output. 

  • The asterisk (*) denotes that all columns in a table should be displayed as part of the output. The ALL option displays all values for a column, including duplicates. It is thought of as the default. 

  • Selecting distinct columns option suppresses duplicate rows from being displayed in the output. 

Example

SELECT * FROM EMP;

 

SELECT EMP_ID, NAME

FROM EMP

WHERE EMP_ID>105;

 

SELECT * FROM EMP

ORDER BY EMP_ID;

 

 

All Chapters
Author