SQL Create Table

SQL CREATE TABLE

The CREATE TABLE statement is used to create a table in a database to stored data.

Tables are organized into rows and columns; and each table must have a name.

SQL CREATE TABLE Syntax

CREATE TABLE tablename
(
columnname1 datatype(size) [null | Not Null],
columnname2 datatype(size) [null | Not Null],
columnname3 datatype(size) [null | Not Null],
....
);

Parameters

tablename

The name of the table that you wish to create.

columnname1, columnname2

The columns that you wish to create in the table. Each column must have a datatype. The column should either be defined as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.

CREATE TABLE employees

( employeeid INT NOT NULL,

  lastname VARCHAR(50) NOT NULL,

  firstname VARCHAR(50), 

  Address varchar(200)

  areaPin bigint

  );

1.     Column name employeeid is numeric column and can’t be null
2.        Lastname, firstName,Address is varchar column.
3.      AreaPin is numeric column.

SQL Basics for Beginner

SQL Basics

An instruction to a database to combine data from more than one table.

A SQL join combines records from two or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining fields from two tables (or more) by using values common to each.

DATABASE

A database is a collection of information that is organized so that it can easily be accessed, managed, and updated. In one view, databases can be classified according to types of content: bibliographic, full-text, numeric, and images.

RELATIONAL DATA

A database structure that is in relationship with other database objects. These links are what we use to do our SQL Joins, so they are important to understand. The name for these links in database terminology is "foreign keys." A foreign key is the way you link one table to another.

This join can be of any type like one-to-one, one-to-many, many-to-many etc.

TABLE

Databases store their data in a system of tables. As we do stored data in excel or access same we do in a tables. Tables is a collection of columns and rows where you store data. Typically, each row represents an additional thing that you care about, and each column represents an attribute that the thing can have. Table can have multiple type of columns like numeric, string, text, image, video etc.

We have employee table which is collection of columns 

(IdNum, Lname,Fname,JobCode,Salary,Phone).

TYPES OF SQL JOINS

SQL join is in a database is combining data from more than one table. There are different kinds of joins, which have different rules for joining.

INNER JOIN

An inner join produces a result set that is limited to the rows where there is a match in both table.  

LEFT OUTER JOIN

A left outer join, or left join, results in a set where all of the rows from the first, or left hand side, table are preserved. The rows from the second, or right hand side table only show up if they have a match with the rows from the first table.  Where there are values from the left table but not from the right, the table will read null, which means that the value has not been set. 

RIGHT OUTER JOIN

A right outer join, or right join, is the same as a left join, except the roles are reversed.  All of the rows from the right hand side table show up in the result, but the rows from the table on the left are only there if they match the table on the right.  Empty spaces are null, just like with the the left join.  

FULL OUTER JOIN

All rows from both tables are returned in a full outer join. Similarly to the left and right joins, we call the empty spaces null.   

CROSS JOIN

The cross join returns a table with a potentially very large number of rows.  The row count of the result is equal to the number of rows in the first table times the number of rows in the second table. Each row is a combination of the rows of the first and second table.  

SELF JOIN

You can join a single table to itself.  We can use same table twice.

You can be perfect from diagram.