- A database is a collection of data
- In the database, we collect data in an organized way.
- Database that store data in tables known as Relational DataBase.
- SQL stands for Structured Query Language.
- SQL is used to create, update, retrieve and delete a database.
Some popular SQL Programs
There are many popular SQL programs and each one has their advantages and disadvantages.
In SQL we use 3 types of Commands:
- Data Definition Language(DDL): DDL commands used to define the frame of a database and its major commands are CREATE, ALTER, and DROP.
- Data Manipulation Language(DML): DML commands used to modify or edit the existing database and its major commands are INSERT, UPDATE and DELETE.
- Data Control Language(DCL): DCL commands deal with the permission of the database and its major commands are GRANT and REVOKE.
- Data Query Language(DQL)
- Data Transfer Language(DTL)
Data types are the most important factor, we should know data types before we store data in that format.
|int||To store integer values|
|decimal||To store numeric value with decimal points.|
|numeric||To store a numeric value|
|float||To store numeric value or real numbers|
|DateTime||To store data and time in a specific format|
|char(n)||To store characters with n length|
|varchar(n)||To store character data|
|text||To store characters or string|
|bit||0 or 1 integer|
|image||To store images|
|real||To store real numbers|
|binary||To store binary values|
|timestamp||Timestamp (time in seconds)|
|table||To store a temporary table|
To create a new table in the database we use CREATE TABLE command.
CREATE TABLE STUDENT ( roll_no int, name varchar(30), phone varchar(11) );
In SQL there are some rules that we need to follow to store data in an organized way and SQL commands can work on it. We use constrains when we create a table and pass it along with the data types.
Here are the SQL Constraints we use while creating a table.
- Primary key: It makes sure that all the data of primary key constraint have unique values and No null Value.
- Not Null: There would be no Null value in that column
- Check: It makes sure that all the values of the column satisfy the condition
- Foreign Key: Uniquely Identifies a record in another table
- Default: It gives a default value to the column if the user does not give a value.
- Unique: It makes sure that each value of the column is different.
Syntax to Write a constraint:
CREATE TABLE table_name ( column_name datatype constraint, column2_name datatype constraint, column3_name datatype constraint, );
Insert Data into the table
Using the INSERT and VALUES command we can enter data into a table.
INSERT INTO Student VALUES (1,'Ash’,’9999991119’);
Multiple Insertion of data using single INSERT command:
INSERT INTO Student VALUES (2,’Kakashi’,’2020202020’), (03,‘Naruto’,’3030303030’), (4,‘sam,’4040404040’), (5,‘Goku’,’5050505050’), (6,‘Gohan’,’6060606060’), (7,‘sam,’1010101010’);
Copy using Insert Command:
INSERT INTO Student SELECT * FROM Student_backup_Table WHERE roll_no < 30;
SELECT command is used to retrieve the attributes (columns with their values), of the Table.
SELECT roll_no, name From Student;
From tell, from which table select will retrieve the attributes and values, there could be more than one table associated with FROM statement.
SELECT roll_no, name From Student , Student_backup_Table;
DISTINCT is used along with SELECT command, and it is used to eliminate the duplicate values.
SELECT DISTINCT name FROM Student;
With ALTER command we can perform various operations such as adding new columns, modify data type and add or remove constraints.
To add a new column in the table
ALTER TABLE Student, ADD rank;
To delete a column from the table
ALTER TABLE Student DROP COLUMN rank;
To change the data type of table:
ALTER TABLE Student ALTER COLUMN Name varchar(100);
Rename the Table
With the help of RENAME command, we can rename the table
RENAME TABLE Student TO Students;
DROP TABLE command used to delete the complete table.
DROP TABLE Students;
Truncate table command is used to delete the table content, if we use the truncate table command it will not delete the table, it just deletes the content or data of the table.
TRUNCATE TABLE Students;
Update Data of the Table
With the help of SET command, we can update the data we have saved previously. We can also put come conditions to SET update for specific data.
SELECT Student SET phone = ‘7070707070’ WHERE roll_no = 7;
With the help of DELETE command, we can delete rows of the table.
DELETE FROM Student WHERE roll_no = 7;
In SQL we have a special function which we can apply on our string data to customize it.
(‘SAMO TARLI’, 5)
|lower||SELECT lower (‘HELLO’)|
|reverse||SELECT reverse (‘esrever’)|
|right||SELECT right (‘RIGHT’ ,4)|
|space||SELECT ‘Well’ + space(2) + ‘Done’|
|str||SELECT str (2389,6,2)|
|substring||SELECT substring (‘Hello’ ,2,2)|
|upper||SELECT upper (‘hello’)|
Aggregate functions used to summarize the attribute in one value.
|avg||Provide the average of all values|
|count||Count the total number of values present in the attribute|
|min||Give the minimum value present in an attribute|
|max||Give the maximum value present in the attribute|
|sum||Sum all the values of the attribute.|
In SQL we have some standard comparison operators which we use along with WHERE statement to retrieve or update some specific row or data.
|>=||Greater than equal to|
|<=||Less than equal to|
|!=||Not Equal to|
SELECT * FROM Student WHERE roll_no = 4; SELECT * FROM Student WHERE roll_no > 10; SELECT * FROM Student WHERE roll_no != 4; SELECT * FROM Student WHERE roll_no >= 5;
Logical Operators in SQL
In SQL we have 3 Logical operators, we use logical operators between two conditional expressions.
SELECT * FROM Student WHERE roll_no= 40 OR roll_no=56;
SELECT * FROM Student WHERE roll_no > 10 AND roll_no <56;
SELECT * FROM Student WHERE NOT name = “Naruto”;
With the help of BETWEEN command, we can obtain or retrieve a set of range.
SELECT * FROM Student WHERE roll_no BETWEEN 5 AND 10;
With LIKE command we can match string patterns, we use % symbol along with LIKE command to specify which pattern we want to search.
SELECT * FROM Student WHERE name LIKE ‘s%’ ;
It will show all those records where the name starts from s.
SELECT * FROM Student WHERE name LIKE ‘%s’ ;
It will show all those records where the name ends with s.
SQL Sequence ORDER BY
With ORDERED BY command we can retrieve data in Ascending or Descending order. By default, SQL retrieves data in Ascending order.
SELECT * FROM Student ORDER BY roll_no DESC;
JOINs command is used to join two tables together and provide a single table as a result.
- INNER JOIN
- OUTER JOIN
- CROSS JOIN
INNER JOIN is similar to a simple JOIN, it returns rows when there is a match between the tables.
SELECT * FROM s1.Student INNER JOIN s2.Student_backup_table ON s1.Student = s2.Student_backup_table;
OUTER ROW shows all the rows of the first table and the matching rows of another table.
SELECT * FROM s1.Student OUTER JOIN s2.Student_backup_table ON s1.Student = s2.Student_backup_table;
It generates a table by the cartesian product of one table with another table. The total number of rows after the cartesian product would be rows of first table * columns of 2nd table.
SELECT * FROM Students CROSS JOIN Students_backup_table;
With SQL we can query inside another query statement, with this we can perform multiple queries statements at once.
With IN command we can check whether the data is present in the table or not.
SELECT * FROM Students WHERE name IN (SELECT name FROM Students WHERE name = ”Sam” );
The EXISTS command is used to check whether the record exists or not.
SELECT * FROM Students WHERE EXISTS (SELECT name FROM Students WHERE name = ”Sam” );
The GROUP BY statement groups rows that have the same values into summary rows.
SELECT * FROM Customers GROUP BY name;
The having is similar to WHERE statement, HAVING used along GROUP BY statement.
SELECT * FROM Customers GROUP BY name HAVING COUNT(name) > 5;
You might be interested in: