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 TypeFromTo
int-2,147,483,6482,147,483,647
bigint-9,223,372,036,854,775,8089,223,372,036,854,775,807
smallint-32,76832,767
decimal-10^38 +110^38 -1
float1.79E + 3081.79E + 308
real-3.40E + 383.40E + 38

Text Data Types

Data TypeCharacter Limit
char8000
varchar8000
varchar(max)2E+31
text2,147,483,647

Date/Time Datatype

Data TypeRange
datetimeJan 1, 1753-Dec 31, 9999
smalldatetimeJan 1, 1900-Jun 6, 2079
dateStores a date like June 30, 1991
timeStores a time of day like 12:30 P.M

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

Binary Data Types

Data TypeMaximum Length(bytes)
binary8000
varbinary8000
varbinary(max)2E + 31 bytes
image2,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;

Until next time, adios!