SQL Basics For Database Management

SQL (structured query language) is an important language that every web developer should know.

MySQL is one of the easiest database technologies that can be used for creating databases and managing them based on SQL.

In this tutorial, let’s learn some of the important database management techniques using SQL.

What is SQL?

SQL (Structured Query Language) is a database computer language designed for the insertion, retrieval, and management of data in a relational database.  

A relational database means a database in tabular form. There are also technologies that use non-relational databases.  

Is SQL Case Sensitive?

The SQL Keywords are case-insensitive but are often written in all caps. However, in some setups table and column names are case-sensitive.

MySQL has a configuration option to enable/disable it.  

SQL Commands

The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE, ALTER, and DROP.  

These commands are classified into data definition language (DDL) and data manipulation language (DML).  

Data Definition Language (DDL)

1. Create  

It is used to create a new table.  

Syntax : CREATE TABLE TABLE_NAME (attribute1 type(size), attribute2 type(size),………..);  

Example:   Create table Student(RollNo number (5), Name varchar(10), Age number (5));  

2. Alter  

It is used to modify an existing table.  

Examples:   Alter table Student rename to Stud;Alter table Student add age number(5);

Alter table Student drop age;Alter table Student modify age varchar(5);  

3. Drop  

It is used to delete an entire table.  

Syntax: DROP table table_name;  

Example:   DROP table Student;

Data Manipulation Language (DML)  

1. Select  

It is used to retrieve certain records from one or more tables.  

Examples:   Select * from Student;Select Name from Student;Select Name,RollNo from Student where age=20;  

2. Insert  

It is used to insert values into the table.  

Examples:   Insert into Student values(1,'Adhi',20);Insert into Student(RollNo) values (1);  

3. Update  

It is used to modify existing records.  

Example:   Update Student set Name='Don', age=50 where RollNo=1;

4. Delete  

It is used to delete records.  

Example:   Delete from Student where RollNo=1;

Database Domain

A database domain is the data type used by a column in a database. Each attribute has a domain that defines allowable values.  

This could include its data type, length, values, and other details.  

The database domains are bolded in the example given below.  

Create table Student(RollNo numbers(5), Name varchar(10), age number(5));

Schema

Schema is the description of a database that is specified during the database design (definition of the database).  

Constraints

Constraints are rules you create at design time that protects your data from becoming corrupt. Constraints can be either column level or table level.  

Some of the most commonly used constraints available in SQL are Primary Key, Foreign Key, NotNULL, Unique, Default, etc.  

Primary Key

It uniquely identifies each row or record in a database table.  

Create table Student(RollNo number(10), Name varchar(20), primary key(RollNo));  

In the above example, RollNo is taken as the primary key.  

Foreign Key It is used to uniquely identify a row or record in any other database table.  

An example is given below.  

Create table Student(RollNo number(10), name varchar(10),Dept_id(10),primary key (RollNo), foreign key(Dept_id));  

Here, Dept_id is taken as the foreign key.  

NotNULL

It ensures that a column cannot have a NULL value.  

For example:   Create table Student(RollNo number(20), NotNULL, name varchar(20));

Unique

It ensures that all the values in a column are different.  

Default

It provides a default value for a column when none is specified.  

Constraint Violation

Constraint violation occurs when an insert, update or delete statement violates a primary key, foreign key, or any other constraints defined during database design.  

Some other basic queries in SQL

Like  

Let’s have a look at some examples.  

Select * from Student where name like 'A%';  

The above query is used to select a student whose name starts with A.  

Select * from Student where name like '%A';  

This query selects a student whose name ends with A.  

Select * from Student where name like '_A%';  

It selects a student who has the second letter of the name as A.  

Order by  

Some examples of using order by are given below.  

Select * from Student order by name;  

This query selects students in the order of their names (alphabetic order).  

Select * from Student order by name DESC;  

It selects students in the descending order of their names.  

Group by  

An example of using group by is given below.  

Select * from Employee group by name;  

It is used to group rows with the same names.  

Having  

An example of using this is given below.  

Select * from Employee where age=30 having salary>70000;  

The having clause specifies that select data from the table only if some conditions are met (age=30 and salary >70000).  

Aggregate Functions

min

This function returns the smallest value in a given column.  

max

This function returns the greatest value in a given column.  

sum

This function returns the sum of numeric values in a given column.  

avg

This function returns the average numeric value in a given column.  

count

This function returns the total number of values in a given column.  

count(*)

This function returns the number of rows in a table.

Examples:   Select avg(salary) from Employee;Select count(*) from Employee;

Nested Queries

A nested query is a query within another SQL query and embedded within the WHERE clause.  

Example:   Select dep_name from DEPT where depid in (select depid from Student where name= 'Adhi');  

It is used to find the department name of a student when the name is known.  

Correlated Subquery

A correlated subquery is a subquery that uses values from the outer query.  

Examples:  

Select * from Student, Dept where Student.depid = Dept.depid;Select Dept.dept_name from Dept,Student where Student.deptid = Dept.deptid and name = 'Adhi';

Join

Join clause is used to combine columns from one or more tables in a relational database.  

Implicit:   Select * from Student, Dept;  

Explicit:   Select * from Student Join Dept;

Views

In SQL, a view is a virtual table based on the result set of an SQL statement. It contains rows and columns just like a real table.  

It can be used to restrict unwanted access to the exact database.  

Example:   Create view Toppers as Select * from Student where marks>90;    

That’s it with the basics of SQL. There are lots of more queries and techniques that can be used for database management.  

We just discussed the basic concepts and that will be enough for you to create and manage amazing apps and databases.  

If you have any doubts or queries regarding this article, feel free to mention them in the comments section.  

If this article was helpful, do share it with your friends.

Ashwin Joy

I'm the face behind Pythonista Planet. I learned my first programming language back in 2015. Ever since then, I've been learning programming and immersing myself in technology. On this site, I share everything that I've learned about computer programming.

Leave a Reply

Your email address will not be published. Required fields are marked *

Recent Content