Learn SQL programming for Free-Beginner’s

SQL is the language behind an Application to perform operations like storing and retrieving data. It is a standard language for the relational database management system.

Do check our Programming Blog if you don’t covered it.

Here’s the Index 🙂

1. What is SQL?
2. Relational Database
3. Database
4. Relationships in SQL
5. Conclusion
6. FAQ

What is SQL?

What is a Relational Database?

A relational database is a database that stores as well as retrieves data.

Basically the data is stored in systems is in the form of a table with n numbers of rows and columns. All relational database management systems like MySQL, MS Access, Oracle, PostgreSQL, etc use SQL as the standard database language.

SQL 

   SQL Commands

DDL

DML

DCL

TCL

DCL

CREATE	INSERT	GRANT	ROLLBACK	SELECT
ALTER	UPDATE	REVOKE	COMMIT
TRUNCATE	DELETE		SAVEPOINT
DROP
DESC
RENAME

What are DDL commands?(SQL)

DDL stands for Data Definition Language, used for definition and creation of objects in a table.

1. CREATE 

Used to create tables, databases, etc.

2. ALTER 

Used to modify the table.

3. DROP

Used to remove a table.

4. RENAME

Used to rename a table.

What are DML commands?(SQL)

DML stands for Data Manipulation Language, used for updates, retrieving data from a database.

1. INSERT

Used to Insert data in a table.

2. SELECT

Used to select a table’s data.

3. UPDATE 

Used to update the table’s data.

4. DELETE

Used to remove records from the table.

Database in SQL

How to create a Database?

The CREATE DATABASE  statement is used to create a new database.

Syntax

CREATE DATABASE Database_name;

CREATE DATABASE is the keyword and the Database_Name is the name of the database that I am going to create.

Ex.

CREATE DATABASE MyRecords;

Note: All database names should be unique.

How to delete Databases?

To delete Databases we should use the drop keyword. 

Syntax

DROP DATABASE Database_Name;

Ex.

DROP DATABASE MyRecords;

Tables in SQL

Creating a Table

Customers

Field	Type	Null	Key
ID	int	NO	PRI
NAME	varchar(20)	NO
AGE	int	NO
ADDRESS	char(25)	YES
SALARY	float	YES

The above table is the blueprint of the Customer table that we are going to create.

Let’s understand this.

In the field column, the following are the column’s head.

Type contains the data type of the columns.

Null is that column which shows that the section must contain a value or not.

Key, there are several types of keys, but we used the Primary key.

Primary Keys are the keys that are unique and also used to display details.

Let’s create a Table of Customers:

To create a table we have to use CREATE keyword

Syntax

CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype ….. columnN datatype, PRIMARY KEY (one or more column’s name));

→

CREATE TABLE Customers(ID int PRIMARY KEY, NAME varchar(20), AGE int, ADDRESS char(25), SALARY float));

In the above line, we have created a Customers table.

To create should have columns names and their datatypes. As for the ID, it is an integer and also it is unique so we set it as our table’s primary key.

The primary key can’t be repeated.

We used varchar(20) and char(25) for Name and Address respectively because the Name and the address are Strings and for that, we used varchar and char with sizes.

The varchar is of variable length and the char is of fixed length.

Then we used salary, of datatype float which shows that the salary that is going to be entered is in the decimal value.

Rename a table

To rename, RENAME command is used.

Syntax

RENAME TABLE old_table_name to new_table_name;

Ex.

RENAME TABLE Customers to Customer;

Inserting Data into a table

To insert data we have to use the INSERT INTO keyword. Also, the values should be as per the data types.

Syntax

INSERT INTO Customer(ID, NAME,....);

Ex.

INSERT INTO Customer(1,’Ayush’,21, Panaji, Goa’,’2000.00’);

The String should be in single quotes( ‘ ‘ ) and there’s no need for integers.

If you want to skip a column then you just have to leave a space.

For ex.

If it’s an integer then you have to

 INSERT INTO Customer(1,’Ayush’, , Panaji, Goa’,’2000.00’);

You can see I skip the age. 

Not the ID because it’s a primary key and it should be filled and also be unique.

Displaying Table using SQL

To display a table we use the SELECT keyword. 

Syntax

SELECT * FROM TABLE_NAME;

Ex. 

SELECT * FROM Customer;

Here, the above query is going to return the Customer table with entries.

ID	NAME	AGE	ADDRESS	SALARY
1	AYUSH	18	Panaji, Goa	20000.00

This table is the result of the query.

Where * means to select all.

We can also display a single column

Ex. 

SELECT NAME FROM Customer;

NAME

AYUSH

This table is the result of the query.

The above code is going to display only the Name column with its entries.

Also, we can search the table by

SELECT * FROM Customer WHERE NAME= AYUSH;
SELECT * FROM Customer WHERE AGE= 18;
SELECT * FROM Customer WHERE ID= 1;

In the first line of the above code, we request the entries of Ayush and * means all. So, the data I will get is the full row of all Ayush in the Name column.

ID	NAME	AGE	ADDRESS	SALARY
1	AYUSH	18	Panaji, Goa	20000.00

This table is the result of the query.

In the second query, I will get nothing because the Customer table doesn’t have any entry with age 18.

ID	NAME	AGE	ADDRESS	SALARY

This table is the result of the query.

In the third line, I will again get the full row of the 1 in ID.

ID	NAME	AGE	ADDRESS	SALARY
1	AYUSH	18	Panaji, Goa	20000.00

This table is the result of the query.

Updating 

The UPDATE keyword is used to modify the existing rows in a table.

Syntax

UPDATE TABLE_NAME SET column1 = value1, column2 = value2, … … … valueN WHERE [condition];

Ex.

UPDATE Customer SET ADDRESS = ‘PUNE’ WHERE ID =1;

ID	NAME	AGE	ADDRESS	SALARY
1	AYUSH	18	PUNE	20000.00

This table is the result of the query.

DELETE

To remove an entry from the table we can use the DELETE keyword.

Syntax

DELETE FROM table_name WHERE[condition];

Ex.

DELETE FROM Customer WHERE ID=1;

ID	NAME	AGE	ADDRESS	SALARY

Empty. This table is the result of the query.

Relationships in SQL

Relationships in DBMS can be stated as a relation between two entities like College-Department, Student-Course, etc.

There are 3 types of Relationship.

1 : 1 ( One to One) Relationship.

The one to one relationship is the relationship in which the instance of the tables is associated with each other.

Ex. Let’s take an example of a student table and standard table.

Student Table

Student_ID	Student Name
1	Ayush
2	Ramesh

Course Tabe

Course_ID	Course_Name
1	Science
2	Commerce

If I want to attach both tables then I should make a foreign key on a table which is the primary key of another

Ex.

CREATE TABLE Student(Student_ID int PRIMARY KEY, Student_Name varchar(20), Course_ID int REFERENCES COURSE(Course_ID));

We used the Course_ID as a foreign key in the Student table. The references is a keyword to associate the table, also we have to mention table_name(table_id_primary_key)

Student table

Student_ID	Student_Name	Course_ID
1	Ayush	1
2	Ramesh	2

In 1:1, we should use any table.

You can see that I made a new column of Course_ID which is the primary key of course.

If you run the query

SELECT * FROM Student WHERE Course_name=Science.

You will get this result

Student_ID	Student_Name	Course_ID
1	Ayush	1

1 : M or M : 1 ( One to Many or Many to One ) Relationship.

One to Many or the Many to One relationship used for that table whose columns are more,or that which depends on another table.

Let’s take two tables Student and Marks.

As you know, the Marks table is going to depend on the Student table. So, we are going to move the primary key of Marks to the Student table as a foreign key.

CREATE TABLE Student(Student_ID int PRIMARY KEY, Student_Name varchar(20), Marks_ID int REFERENCES Marks(Marks_ID));

Student table

Student_ID	Student_Name	Marks_ID
1	Ayush	1
2	Ramesh	2

Marks Table

Marks_ID	Subject	Marks
1	Bio	85
2	Maths	60

If you run the query

SELECT * FROM Student WHERE Marks=60

You will get this result

Student_ID	Student_Name	Marks_ID	Subject	Marks
2	Ramesh	2	Maths	60

M : M ( Many to Many ) Relationship.  

When there are two tables and both depend on each other, you can make a third table to join these two.

 Ex. If two tables Student and teachers have columns like name, id, subject etc, then we will have to create a third table with both tables’s primary key as foreign keys.

Student

Student_ID	Student_Name	Subject	Marks
1	Ayush	Bio	85
2	Ramesh	Maths	60

Teacher

Teacher_ID	Teacher_Name	Subject
1	Miss Neha	Maths
2	Mr. Pandey	Bio

To connect these tables we have to create another table.

CREATE TABLE Stud_Teach(Student_ID int REFERENCES Students(Student_ID) ,Teacher_ID int REFERENCES Teacher(Teacher_ID));

Student_ID	Teacher_ID
1	2
2	1

Conclusion

The SQL is easy. But the relationship is a bit hard to learn and to understand where to apply.

So, read books and tutorials and be perfect, because it is easy and if you master any development programming language, then you will need the Database to store its files.

So, there we use SQL queries.

FAQ

Check our Related Blogs:-

• Learn to make a website.
• What are Networking Architecture and its Types?
• Create Video and GIF for your Business with Android