SQL Server Database | SQL Query and Commands in Practice | Database and SQL tables

Database is a collection of data that is organized in a manner that facilitates ease of access, as well as efficient management of updating. A database is made up of tables that stores relevant information. For example, you would use a database, if you were to create a website like Youtube, which contains a lot of information like videos, user names, passwords, comments. A database comprises of different tables which are made using any programming language such as SQL. SQL table stores and displays information in a structured format consisting of columns and rows that are similar to those seen in Microsoft Excel spreadsheets.

Databases often contain multiple tables, each designed for a specific purpose. For example, imagine creating a SQL table of names and telephone numbers. First, we could set up columns with the titles FirstName, LastName, and TelephoneNumber. Each Sql table includes it's own set of fields, based on the data it will store.

PRIMARY KEY

Primary key is a field in the table that uniquely identifies the table records. The main features of a primary key are:

- It must contain a unique value for each row in SQL table

- It can not contain NULL values.

SQL TABLE

For instance, our SQL table contains a record for each name in a phone book. The unique ID number would be a good choice for a primary key in the SQL table, as there is always the chance for more than one person to have the same name. It is clearly shown in the SQL table that the name "Fakhar" is repeated but because of primary key, it uniquely identifies both of the persons. So what is SQL?

What is SQL?

It stands for Structured Query Language. It is used to access and manipulate a database, While, Mysql is a program that understands Structured Query Language. SQL query is the command which allows us to work with SQL database server.

It can:

- Insert, update, and delete records from a database.

- Create new databases, table, stored procedures, views.

- Retrieve data from a database, etc.

SQL Query and Commands

It have some very useful and powerful commands for the manipulation of data, efficiently. We will be discussing the following SQL query and commands:

1. Select query
2. Create table query
3. Insert query
4. Update query
5. Delete query
6. Alter query
7. Truncate query

Select Query

The select query or statement is used to select data from a database. The result is stored in a result table, which is called result-set. A select query retrieves information from the selected columns or from all columns in the table.

To create a simple SELECT query, specify the name of the column you need from the table. The syntax for the select query is:

SELECT column_List FROM table_name

- Column_list includes one or more columns from which data is retrieved.
- table_name is the name of the table.

As we have our SQL table, we can apply following SQL query to select the FirstName from the Authors table:

SELECT FirstName FROM Authors; 

Result:

SQL TABLE

However, it is also possible for SQL query to select multiple SQL table columns at once, as shown below:

SELECT FirstName, LastName, TelephoneNumber
FROM Authors;

You can also retrieve all the information contained in your table by this SQL query. Rather than typing each column names separately, you can put an asterisk(*) sign after the SELECT query.

SELECT * FROM Authors; 

Create Table Query

A single database can house hundreds of tables, each playing its own unique role in the database schema. SQL tables are comprised of table rows and columns. SQL table columns are responsible for storing many different types of data, including numbers, texts, dates, and even files. The CREATE table query is used to create a new SQL table. While SQL query and commands manipulate the SQL database.

The basic syntax for the CREATE TABLE query is as follows:

CREATE TABLE table_name
(
  column_name1  data_type(size),  column_name2  datat_ype(size),  column_name3  data_type(size),                       . . . .  columnN data_type(size));

- The column_names specify the names of the columns we want to create.

- The data_type parameter tells the type of data the column can hold. For example, use int for whole numbers.

- The size parameter specifies the maximum length of the table's column.


Now, assume that you want to create table called "User" that consists of four columns:

• UserID
• LastName
• FirstName
• City

Use the following SQL query to build this SQL table:

CREATE TABLE Users
(
  UserID int;
  FirstName varchar(100),
  LastName varchar(100),
  City varchar(100)
);

Here UserID is the best choice for your SQL table primary key. Define it as a primary key during SQL table creation, using PRIMARY KEY keyword.

CREATE TABLE Users
(
  UserID int;
  FirstName varchar(100),
  LastName varchar(100),
  City varchar(100),
  PRIMARY KEY(UserID)
);

Now, when we run the query, our table will created in the database:

SQL TABLE

Insert Query

SQL table stores data in rows, one row after another. The INSERT INTO SQL query is used to add new rows of data to an SQL table. The SQL query syntax for inserting data is as follows:

INSERT INTO table_name


  VALUES (Value1, Value2, Value3, . . . .);

Consider the following SQL table, Users:

SQL TABLE

Use the following SQL query to insert a new row:

INSERT INTO Users


  VALUES (6, 'intasar', 'Ata', 90033254);

The values are comma-separated and their order corresponds to the columns in the table:

Result:

SQL TABLE

You can also use the following method to insert data. This will insert the data into the corresponding columns:

INSERT INTO Users (ID, FirstName, LastName, TelephoneNumeber)


  VALUES (6, 'intasar', 'Ata', 90033254);

In addition, you can also insert data into specific columns only, as shown below:

INSERT INTO Users (ID, FirstName, LastName)


  VALUES (6, 'intasar', 'Ata');

Update Query

The update query allows us to alter data in the table. The basic syntax for an UPDATE query with a WHERE clause is as follows:

UPDATE table_name

SET column1 = value1, column2 = value2,  . . . .

  
WHERE condition;

Now, consider the following table called "_table":

SQL TABLE

To update the First name of Fakhar, simply we can use the following query:

UPDATE _table

SET FirstName= 'Robert'
  
WHERE ID = 1;

Result:

SQL TABLE

It is also possible to update multiple columns by separating them by comma, as shown below:

UPDATE _table

SET FirstName= 'Robert', TelephoneNumber= 11111111
  
WHERE ID = 1;

Result:

SQL TABLE

Delete Query

The delete SQL query is used to remove data from your table. DELETE SQL query works much like UPDATE SQL query.

DELETE FROM table_name
  
WHERE condition;

For instance, you can delete a specific person from the table:

DELETE FROM _table
  
WHERE ID = 1;

Results:

SQL TABLE

Alter Query

Alter SQL query is used to modify or alter the columns of SQL table, it uses following syntax for Alter query:

ALTER TABLE table_name
  
ADD column_name  data_type;

Now, if you want to alter your SQL table such that, add a column in the table then you will use the following SQL query:

ALTER TABLE _table
  
ADD address  varchar(100);

Now, when we run the query, you will get the address column added to your SQL table as shown:

SQL TABLE

Truncate Query

Truncate SQL query works same as DELETE SQL query does, but without WHERE keyword. It uses following syntax for Truncate query:

TRUNCATE TABLE table_name

Now, if you want to truncate your SQL table such that, delete all column from the table then you will use the following SQL query:

TRUNCATE TABLE _table

All the elements will got deleted after executing the above SQL query.