Getting Started With SQL Fundamentals

Part-1

Data Types

Before you probably create your first SQL programme, it is incredibly important to review about some of the data types available to us at SQL.

Numeric Data Types

Data Type	From	To
int	-2,147,483,648	2,147,483,647
bigint	-9,223,372,036,854,775,808	9,223,372,036,854,775,807
smallint	-32,768	32,767
decimal	-10^38 +1	10^38 -1
float	1.79E + 308	1.79E + 308
real	-3.40E + 38	3.40E + 38

Text Data Types

Data Type	Character Limit
char	8000
varchar	8000
varchar(max)	2E+31
text	2,147,483,647

Date/Time Datatype

Data Type	Range
datetime	Jan 1, 1753-Dec 31, 9999
smalldatetime	Jan 1, 1900-Jun 6, 2079
date	Stores a date like June 30, 1991
time	Stores a time of day like 12:30 P.M

We are now ready to create our first simple SQL Programme!

Binary Data Types

Data Type	Maximum Length(bytes)
binary	8000
varbinary	8000
varbinary(max)	2E + 31 bytes
image	2,147,483,647

Let’s Get Started With Our First Programme!

The code snippet below simply demonstrates a simple programme where we create a database CodevitaLive, with a table Courses encapsulating details about some of our courses. The select* from tablename simply displays the contents of our table .

/*Our programme starts below. This is the syntax to display 
a multi line comment /*
create database CodevitaLive;
create table Courses(id integer, title varchar(100),price integer);
insert into Courses(id, title,price) values(1, "HYP101",6000);
insert into Courses(id, title,price) values(2, "DSF",4000);
insert into Courses(id, title,price) values(3, "Quantum Leap",10000);
insert into Courses(id, title,price) values(4, "Bon Voyage",450);
select * from Courses;--displays the data in the table ,the syntax to display a single line comment

We now take this opportunity to introduce queries. Queries help us display only certain sections of our data that satisfy specific conditions. We just need to insert one more line in our previous programme to display only the courses whose prices are above 4000:

create database CodevitaLive;
create table Courses(id integer, title varchar(100),price integer);
insert into Courses(id, title,price) values(1, "HYP101",6000);
insert into Courses(id, title,price) values(2, "DSF",4000);
insert into Courses(id, title,price) values(3, "Quantum Leap",10000);
insert into Courses(id, title,price) values(4, "Bon Voyage",450);
select * from Courses where price>2000;

We have to increase the scope of our programme a bit to showcase the use of AND and OR functions.

create database CodevitaLive;
create table Courses(id integer, title varchar(100),price integer,cohortsize integer);
insert into Courses(id, title,price,cohortsize) values(1, "HYP101",6000,100);
insert into Courses(id, title,price,cohortsize) values(2, "DSF",4000,50);
insert into Courses(id, title,price,cohortsize) values(3, "Quantum Leap",10000,10);
insert into Courses(id, title,price,cohortsize) values(4, "Bon Voyage",450,10000);
select * from Courses where price<2000 and cohortsize>100;
select * from Courses where price>5000 or cohortsize<15;