Introduction To SQL

Basic Concepts - Data, Table, Databases.

What is data?

Data are pieces of information that are gathered and used for some specific purpose.

In simple words, any piece of information that we store or use is called data. Your name is a data, your date of birth is a data, your age is a data, everything is a data unit it has some meaningful use.

There is a lot of data present around us. But in order to make use of this data, it must be in useable form. It must be in a particular structure (organized way) so that we can take out the data of our concern with ease and in less time. This can be done in many ways, and one of the most useful, popular and efficient structure of organizing data is tabular structure.

We make use of tables for this purpose.

What are Tables?

A table is an organized arrangement of related data in the tabular form containing rows and columns, making it easier to understand and compare data.

We specify a set of rules/features/attributes/category in which we distribute the data we have. This helps us find any data easily (if required in future) if we know what category it belongs to.

The category is called Columns.

Columns are vertical

The data (of a particular object) are filled in these columns as something called Rows.

Rows are horizontal.

Suppose we have a table containing information about students, then each row contains all details of a particular student like student's ID, student's name, student's age and student's course.

For instance, the 2nd row has all the details of a single student, like, ID is 2, Name is C.V Raman, Age is 26, and Course is Maths.

Whereas, each column, i.e, ID, Name, Age and Course respectively contain a specific information about all students, like ID contains all IDs of all students, Name contains name of all students and so on. This is how the information is stored in a table.

* Rows are also called records.

* Columns are also called fields.

What are Databases?

A database is a collection of multiple tables in a single container.

A database is an organized collection of structured data or information, which is specifically stored on a computer system and controlled by a Database Management System (DBMS).

So, simply a database is a collection of multiple tables. It acts as a container for multiple tables. If you want some tables to be kept separately from others, you can simply create a database and store them in it together, and other tables in another database.

Relational Database:

Any database which contains tables which are related/connected to each other in any means, then that database is called a relational database.

We are going to start with the next topic, that is, DBMS, RDBMS and SQL in this article.

What is Database Management System (DBMS)?

A DBMS or Database Management System is a software package designed to define, manipulate, retrieve and manage data in a database. It helps the users to store and retrieve data.

To do anything with a table or a database, such as creating a table or database, editing it, modifying it or deleting it, we require a tool or some sort of a utility. This tool or utility is called DBMS.

Like, for writing, we have Notepad and MS-Word in our computer; for drawing, we have Paint, similarly, for doing anything related to tables and databases, we have a DBMS or a DataBase Management System.

Its basically a software that is used to create/edit/delete a database or a table. Any operation related to tables or databases can be done using a DBMS.

* In a DBMS when we need to do a task on a database, we give commands and these commands are knows as SQL or structured query language. So SQL is nothing but it is simply a programming language that is a set of syntax and rules which helps us to give instructions to the DBMS software to work with databases. Indirectly SQL help us to communicate with a database through a database management system. Like python, c programming languages etc. SQL is also a programming language there are many database management system software's which could be used to manage databases but they all use a common language that is structured query language or SQL.

(SQL) Structured Query Language is a computer language for management of databases and data manipulation. SQL is used to query, insert, update and modify data in a database. It contains a lot of commands which a user can execute to perform operations on a database.

> SQL Commands/Components of SQL

1. DDL : (Data Definition Language) Used to define the structure of database objects such as tables, views, functions etc. Using DDL commands, we can create, modify and drop any database objects. The commands include. * Create

Create a new database objects such as tables, views or functions etc.

Syntax to create a new table is:

CREATE TABLEIF NOT EXISTS STUDENTS

(

ID VARCHAR(20) PRIMARY KEY,

FIRST_NAME VARCHAR(100) NOT NULL,

LAST_NAME VARHCAR(100) NOT NULL,

GENDER VARCHAR(10) CHECK(GENDER IN ('M','F','MALE',FEMALE')),

AGE INT,

DOB DATE,

GRADE FLOAT,

IS_ACTIVE BOOLEAN,

CONSTRAINT CH_STUDENTS_AGE CHECK(AGE > 0)

);

Here STUDENTS is the name of the table whereas it has 6 columns namely ID, FIRST_NAME, LAST_NAME, GENDER, AGE and DOB. These columns belong to different data type.

There are also few constraints like PRIMARY KEY, NOT NULL, CHECK used in this table.

IF NOT EXISTS before the table name is an optional clause which can be included while creating table which tells the RDBMS to check if the table already exist. If table exists then RDBMS will skip executing this create statement else the statement will be executed and table will be create.

In order to completely understand how to properly create a table, we need to first understand what is Data Type and what are Constraints:

CONSTRAINTS

Constraints refers to limitation or restriction applied to a column in a table. Constraints are very important to maintain data integrity among tables. If you want to make sure that wrong data is not inserted into your table then these kind of sanity checks can be applied by using CONSTRAINTS. Let’s look some of the most widely used constraints in RDBMS:

• CHECK: CHECK constraint allows you to control the values that can be inserted into a column. Let’s say if you have a column “AGE” and you want to make sure that only positive values are being is inserted into this column then you can use a CHECK constraint on this column to apply this rule. So if anyone tries to insert a negative value into this column then RDBMS will throw an error.

• NOT NULL: By applying NOT NULL constraint on a column, you make sure that this column will never have NULL or empty values.

• UNIQUE: Unique constraints are used to make sure that values inserted into a column across all the rows have unique or distinct values. It can help you to eliminate any duplicate data in a column. Remember NULL values are allowed in a UNIQUE constraint column. And two NULL values are not same hence multiple rows with NULL values are allowed.

• PRIMARY KEY: Primary key constraints is basically a combination of UNIQUE + NOT NULL constraint. It will ensure that all the values in the column are unique and there are no NULL values. A table can only have one primary key constraint. Primary key constraint can either be applied to a single column or to a combination of multiple columns in the table.

• FOREIGN KEY: FOREIGN KEY constraint can be used to form relationship between tables. It basically helps to create a parent child relationship between 2 tables. Such that the child table references a column value from the parent table. So that only the values present in parent table can be inserted into the child table.

Other Words - The FOREIGN KEY constraint is a key used to link two tables together.

A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

Ex: On technical level.

A table of customer orders might have a "User" Column with a foreign key attribute that links it to the "user_id" column in a users table. This way, each row in attribute that links it to the user_id column in a user table. This way, each row in the orders table can be associated with a specific user from the users table. And no orders can enter system without a valid user being associated with them.

* Alter

Alter is used to modify the structure of an existing table. Alter can be used to rename a table or rename a column. Alter can also be used to add new column or change the data type of an existing column. Using Alter we can also add new constraints or remove a constraint from a table.

Syntax to alter table is :

ALTER TABLE STUDENTS DROP COLUMN GRADE; -- Drop a column. ALTER TABLE STUDENTS ADD COLUMN REGISTER_NO VARCHAR(100); -- Add new column. ALTER TABLE STUDENTS ALTER COLUMN IS_ACTIVE TYPE VARCHAR(1); -- Change data type of a column. ALTER TABLE STUDENTS RENAME COLUMN IS_ACTIVE TO ACTIVE; -- Rename a column. ALTER TABLE STUDENTS ADD CONSTRAINT UNQ_STD UNIQUE (REGISTER_NO); -- Add new constraint ALTER TABLE STUDENTS DROP CONSTRAINT UNQ_STD; -- Drop a constraint. ALTER TABLE STUDENTS RENAME TO STUDENTS123; -- Rename a table.

* Drop

Drop as the name suggest, is used to remove a database object such as a table, view, functions etc from the database.

Syntax to drop table is :

DROP TABLE STUDENTS;

* TRUNCATE :

Truncate is used to remove all the data from a table at once.

Syntax to truncate table is:

TRUNCATE TABLE STUDENTS;

DML (Data Manipulation Language)

DML commands are used to load, modify and remove data from the database. The commands include:

* INSERT

Insert command can be used to load data into the table. The syntax is: (considering the original table structure we created above in CREATE command) INSERT INTO STUDENTS (ID, FIRST_NAME, LAST_NAME, GENDER, AGE, DOB, GRADE, IS_ACTIVE) VALUES ('STD10251','Minnaminnie','Cleft','Female',8,TO_DATE('2012-02-23', 'YYYY-MM-DD'), 3, TRUE); -- Mention the column names. INSERT INTO STUDENTS VALUES ('STD10252','Effie','Emlyn','Female',8,TO_DATE('2012-03-28', 'YYYY-MM-DD'), 3, TRUE); -- Do not mention column names. INSERT INTO STUDENTS VALUES ('STD10253','Kerry','Aysik','Female',8,TO_DATE('2012-01-09', 'YYYY-MM-DD'), 3, TRUE), ('STD10254','Jo','Mansfield','Male',8,TO_DATE('2012-03-26', 'YYYY-MM-DD'), 3, TRUE), ('STD10255','Elianore','Macon','Female',8,TO_DATE('2012-04-01', 'YYYY-MM-DD'), 3, FALSE); -- Insert multiple records.

* UPDATE

Update commands is used to modify the data in the table.

The syntax is:

UPDATE STUDENTS SET

FIRST_NAME = 'James' WHERE ID = 'STD10253'; -- Update single column.

UPDATE STUDENTS SET

FIRST_NAME = 'Rohan', GRADE = 4 WHERE ID = 'STD10251'; -- Update multiple columns at once.

Data Type

Tables in RDBMS consist of rows and columns. Each column has a data type associated to it. Data type is like a data rule applicable to that particular column. Meaning that only the data or values satisfying this data rule can be inserted into this column. There are several types of data types.

However, In this blog we will go through the most basic and most commonly used data type across RDBMS.

Please note : Data types might have different names in different database. And even if the same, the size and other details may be different! Always we need to check the documentation.

Data types.

Type of data/value an object can hold or process is known as data type.

Size of data type.

Size of a data type is the maximum value which an object can hold.

* Amount of data an object can hold.

* SQL supports lots of data types.

(Some data types are not supported by all DBMS software)

Lets note the standard data types, which are supported by all the DBMS software.

Different SQL data types

1. Exact Numeric Data Type.

Used to store integer values, that is numbers without a decimal point. such as 23,-56,+1034 etc.

A. BIT : It is used to store binary digits, that is, 0 or 1.

B. TINYINT : Can store value between 0 and 255.

C. SMALLINT : Can store values between (-32768) and 32767 (or + 32767)

D. INT : (-2147483648) to (+2147483647)

E. BIGINT : (-9223372036854775808) to (+9223372036854775807)

F. DECIMAL : (-10^32)+1 to (+10^32)-1

2. Approximate Numeric Data Type.

Used to store real number values, that is numbers with a decimal point. Such as 23.04, -56.6541214651, +1034.00 etc.

i) FLOAT - (1.7*10^-308) TO (1.7*10^+308)

ii) REAL - (3.4*10^-38) TO (3.4*10^+38)

3. Data & Time Data type

Used to store date and time related data

i) DATE : Used to store the date in DD/MM/YYYY format

ii) TIME : Used to store time in hh:mm:ss format.

iii) DATETIME : Used to store both date and time.

4. String Data Types.

Used to store string values, that is text, such as a sentence or a word or any other related data.

1. Char

2. VARCHAR

---> CHAR : Used to store up to 8000. Its length is fixed and we cannot change it. In CHAR, If the length of a string is less than set or fixed length then it is padded with extra memory space.

----> VARCHAR(SIZE) : Used to store up to 8000. Its length is variable and we can change it. In VARCHAR, If the length of string is less than set or fixed length then it will store as it is without padded with extra memory spaces.

---------------------------------------------------------------------------------------------------------------------------------------- Introduction to SQL Queries.

We understand how data is organized in databases, we can begin drawing insights using SQL queries.

What is SQL used for?

SQL is used to answer questions both within and across relational database tables. In an HR database, we could query salaries for employees in Marketing and Accounting to determine whether pay across departments is comparable.

SQL is best for large datasets analysis.

In many organizations, SQL is used as a complement to other tools such as spreadsheet applications. If the data we're interested in can fit in a spreadsheet and does not have many relationships to other data of interest, we can analyze it in a spreadsheet. But for sprawling and diverse data such as the data related to a retail platform, organizing the data in a database is best. Then, we use SQL queries to uncover trends in website traffic, customer reviews, and product sales.

* Which products had the highest sales last week?

* Which products get the worst review scores from customers?

* How did website traffic change when a feature was introduced?

(SQL shines when an organization has lots of data with complex relationships).

Let's write our first SQL code! To do that, we will need to learn a few keywords. Keywords are reserved words used to indicate what operation we'd like our code to perform.

The two most common keywords are SELECT and FROM.

SELECT name FROM students;

The SELECT keyword indicates which fields should be selected, in this case name field. And the FROM keyword indicates the table in which these fields are located. i.e students table. It is best practice to end the query with semicolon(;) and capitalize keywords while keeping table and fields all lowercase.

SELECT * FROM students;

If we would like to select all the fields from the students table by using asterisk (*)