Getting Started with SQL: Creating a Table, Inserting Values and Retrieving Data
SQL is a powerful query language which is used to access relational databases. It can retrieve large amounts of data quickly and efficiently. SQL is easy to learn, understand and use, and this is because it has well-defined standards.
SQL is portable and can run on a variety of platforms easily, and the language is used to interact with complex databases. SQL also has an advantage of providing different views of structure and content of a database. It has client-server architecture.
To better understand the functionality of SQL, the following is an example of how to create a table.
For instance, a car dealer wants to store the database of their customers with the information about the customer and the car that has been purchased
Syntax
CREATE TABLE table_name ( column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size), .... );
Example
CREATE TABLE Customers
(
CustomerId int NOT NULL,
LastName varchar(255),
FirstName varchar(255),
CarModel varchar(255), Address varchar(255), City varchar(255) );
Here we are creating a table named customer, where there are various columns like `CustomerId`, `LastName`, `FirstName`, etc. The data type is `int` for `CustomerId`, which means it can hold an integer value. For other columns the datatype is `varchar`, which is used to provide character value. The following table would be created from the commands above.
CustomerId LastName FirstName CarModel Address CityA table with name Customers and six columns has been created from the above command. Now we will add data into the table. The data inserted into table will be a record. Following are the statement to insert new records.
Syntax
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
Example
INSERT INTO Customers(CustomerID,LastName,FirstName,CarModel,Address,City)
VALUES(98563,Harper,Charles, Ford Mustang, 104 Howard St, San Francisco);
The above statements will result in following
CustomerId LastName FirstName CarModel Address City 98563 Harper Charles Ford Mustang 104 Howard St San FranciscoWe can use the similar statement of `INSERT INTO....VALUES` to add more records to the table.
CustomerId LastName FirstName CarModel Address City 98563 Harper Charles Mustang 104 Howard St San Francisco 98426 Carson David Fusion 23 St Hillburn Apartments San Jose 98367 Owen Tim Raider 12 Smith St San Francisco 98634 Simpson Julia Endeavour 12, Granada Hills Los AngelesNow we have successfully create a table Customers and added details of the customers, along with the details of car model they have purchased.
But what if the car dealer wants to know about the car models which the customers that reside in San Francisco, for example? They can use the following command
Syntax
SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;
Example
SELECT CarModel FROM Customers
WHERE City = 'San Francisco';
CarModel
City
Mustang
San Francisco
Raider
San Francisco
The following example will yield the above results. Thus now the car dealer knows that which cars the buyers in San Francisco prefer. The next time the car dealer has to promote his cars in San Francisco he will be well aware of which cars to promote in that area.
About the Author:
Vaishnavi Agrawal loves pursuing excellence through writing and has a passion for technology. She has successfully managed and run personal technology magazines and websites. She currently writes for intellipaat.com, a global training company that provides e-learning and professional certification training.