Create table command defines each column of the table uniquely. Each column has a minimum three attribute, a name, datatype and size(i.e. column width)
Create table employeeCREATE TABLE employee ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255) NOT NULL, Age int Address varchar(100), City varchar(50), State varchar(50) );
SQL- Inserting data into table
The insert statement is used to insert or add a row of data into the table.
Once Table is created the most natural thing to do is load this table with data to be manipulated later.
To insert records into a table, enter the key words insert into followed by the table name, followed by an open parenthesis, followed by a list of column names separated by commas, followed by a closing parenthesis, followed by the keyword values, followed by the list of values enclosed in parenthesis.
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);ORINSERT INTO table_name VALUES (value1, value2, value3, ...);
INSERT INTO employee (ID, lastName, FirstName, Age, Address, City, State) VALUES (101570, ‘Kumar’, ‘Naveen’, 28, ‘XYZ’, ‘Etah’, ‘UP’);
Note: Character statement placed within the insert into statement must be enclosed in single quotes(‘).
It have a one to one relationship, means we insert one value in each column at a time.
SQL - Viewing data in the Table
The select statement is used to query the database and retrieve selected data that match the criteria that you specify.
SELECT column1, column2, ... FROM table_name;Or SELECT * FROM table_name;
SELECT FirstName, LastName FROM employee;orSELECT *FROM employee;
Note: In this we have use meta character asterisk(*), which used to expand all columns of the table.
SQL- Filtering table data
SQL must give us a method of filtering out data that is not required.
Three way of filtering table data will be:
1-Selected columns and all rows
2-Selected rows and all columns
3-Selected columns and selected rows
Selected columns and all rows
Retrieval of specific column from a table.
SELECT column1, column2, ... FROM table_name;
SELECT FirstName, LastName FROM employee;
Selected rows and all columns:
SELECT FROM table_name WHERE <condition>;
SELECT *FROM employee WHERE FirstName = “naveen”;
Selected columns and selected rows
To view the specific set of rows and columns.
SELECT column1, column2, ... FROM table_name WHERE <condition>;
SELECT lastName, FirstName, ... FROM employee WHERE City = “etah”;
SQL- Eliminating duplicate row using select statement
If table could hold duplicate rows, in this case it necessary to remove delicacy using this
SELECT DISTINCT lastName, FirstName, ... FROM employee; Or SELECT DISTINCT *FROM employee;