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.
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)
It is used to create a new table.
Syntax : CREATE TABLE TABLE_NAME (attribute1 type(size), attribute2 type(size),………..);
Create table Student(RollNo number (5), Name varchar(10), Age number (5));
It is used to modify an existing table.
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);
It is used to delete an entire table.
Syntax: DROP table table_name;
DROP table Student;
Data Manipulation Language (DML)
It is used to retrieve certain records from one or more tables.
Select * from Student;Select Name from Student;Select Name,RollNo from Student where age=20;
It is used to insert values into the table.
Insert into Student values(1,'Adhi',20);Insert into Student(RollNo) values (1);
It is used to modify existing records.
Update Student set Name='Don', age=50 where RollNo=1;
It is used to delete records.
Delete from Student where RollNo=1;
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 is the description of a database that is specified during the database design (definition of the database).
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.
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.
It ensures that a column cannot have a NULL value.
Create table Student(RollNo number(20), NotNULL, name varchar(20));
It ensures that all the values in a column are different.
It provides a default value for a column when none is specified.
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
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.
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.
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.
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).
This function returns the smallest value in a given column.
This function returns the greatest value in a given column.
This function returns the sum of numeric values in a given column.
This function returns the average numeric value in a given column.
This function returns the total number of values in a given column.
This function returns the number of rows in a table.
Select avg(salary) from Employee;Select count(*) from Employee;
A nested query is a query within another SQL query and embedded within the WHERE clause.
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.
A correlated subquery is a subquery that uses values from the outer query.
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 clause is used to combine columns from one or more tables in a relational database.
Select * from Student, Dept;
Select * from Student Join Dept;
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.
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.
Most of us have used or have come across the necessity of using the Python programming language. Python is one of the most popular programming languages around the world. Due to many factors,...
Welcome to the future..! In this article, we will be dealing with how to learn Machine Learning. We know that humans can learn a lot from their past experiences and that machines follow...