SQL Stands for “Structured Query Language”. This is a popular programming language to manage Data. SQL consists of DDL (Data Definition Language), DML (Data Manipulation Language) & DCL (Data Control Language). SQL was first appeared in 1974. That time It was developed by Donald D. Chamberlin & Raymond F. Boyce in IBM. In 1986 the SQL was initially released. SQL is approved by both ANSI & ISO. SQL is derived from the word “SEQUEL”. In this session for absolute beginners let us share the list of frequently used SQL Queries.
To get Started with various SQL Queries first we required to Create a Database and Table with Sample Data.
Create & Drop Database Queries
To Create a Database in SQL we use Create Database Query. Create Database query Syntax is as below.
Syntax: CREATE DATABASE Database_Name;
Assume here we are going to Create a Database with name “temp_db”. Then you required to execute the following query.
CREATE DATABASE temp_db;
Like Create Database to Drop a Database you required to run the following query.
Syntax: DROP DATABASE Database_Name;
Create & Drop Table SQL Queries
Once you successfully Created the Database next you required to Create Tables to Store records. In the below example I creating a SQL Table to store manager details. The Table name is ManagerDetails. In this table Manager_ID is the primary key.
CREATE TABLE IF NOT EXISTS ManagerDetails ( Manager_ID varchar(10) NOT NULL DEFAULT '', Manager_Name varchar(200) NOT NULL DEFAULT '', Manager_Designation varchar(200) NOT NULL DEFAULT '', Manager_Salary varchar(20) NOT NULL DEFAULT '0', PRIMARY KEY (Manager_ID) )
Like Create Table to Drop a Table from your Database you required to run the following query.
Syntax: DROP TABLE Table_Name;
INSERT Statement SQL Queries
To execute various SELECT Statements on over a SQL Table at minimum we required some Sample records. Here in my ManagerDetails table I am inserting 12 sample records using the following insert queries.
INSERT INTO ManagerDetails (Manager_ID, Manager_Name, Manager_Designation, Manager_Salary) VALUES (1, 'Sujata Mohapatra', 'Sr. Manager', '28000'), (2, 'Ravi Ranjan Dash', 'Program Manager', '32000'), (3, 'Bijaylakshmi Dash', 'Business Manager', '48000'), (4, 'Rosalin Roy', 'Sr. Program Manager', '62000'), (5, 'Nibedita Mahapatra', 'Jr. Manager', '18000'), (6, 'Meghana Roy', 'Sr. Manager', '35000'), (7, 'Manamohan Mohanty', 'Sr. Team Lead', '23000'), (8, 'Rupak Maharana', 'Business Head', '36000'), (9, 'Ramkrishna Dalei', 'QA Manager', '18000'), (10, 'Puspashree Mishra', 'Team Leader', '15000'), (11, 'Jayshree Moharana', 'Sr. Project Manager', '55000'), (12, 'Ravi Prakash Dash', 'Business Head', '42000');
SELECT Queries
In SQL we use SELECT Statement to fetch records from a Table. Looking into the complexity and filter there are various clauses introduced with SQL Select Statement. Let us explore those clauses 1 by 1. First let us discuss the Select Statement using which we can Fetch all the records from a Table.
SELECT * FROM ManagerDetails;
OR
SELECT Column_Name_A, Column_Name_B,..., Column_Name_N FROM ManagerDetails;
DISTINCT Clause
In a SQL Table it may happen a column may contain more than 1 records with duplicate values. In this scenario to list only the different values we use DISTINCT Clause with SELECT Statement look at the Syntax below.
Syntax: SELECT DISTINCT Column_Name, Column_Name, Column_Name FROM Table_Name;
Example:
SELECT DISTINCT Manager_Salary FROM managerdetails;
Here you can notice in my ManagerDetails table Manager_Salary column contain 18,000 Salary for more than 1 manager. Using DISTINCT Clause the result will show me 18,000 Salary only once.
WHERE Clause
This is a Conditional Clause. For an example in the above ManagerDetails table if you want to Filter those managers who are having more then 40,000 Salary per month this clause is helpful. Look at the example below.
Example:
SELECT * FROM ManagerDetails WHERE Manager_Salary > 40000;
Logical Clause (AND/OR)
Logical Clause can be used with various SELECT Statement Clauses. To show you an example here I am using Logical Clause with Where Condition. What I want is from the above ManagerDetails table I want those manager records who are having salary more than 40,000 and less than 60,000. Basically there are 2 types of logical clauses AND & OR. In below example let me show you how to use AND logical clause.
Example:
SELECT * FROM ManagerDetails WHERE Manager_Salary > 40000 AND Manager_Salary < 60000;
IN Clause
This Clause is useful to fetch specific records with Specific ID’s or Values. For an example using below SQL Query I am looking all those manager records who are having Salary 28000 and 18000.
Example:
SELECT * FROM ManagerDetails WHERE Manager_Salary IN (28000, 18000);
BETWEEN Clause
Generally we use Between Clause with SELECT Statement when we required to extract those records which are in between a specific value. For an example here in ManagerDetails table I want to fetch all those records who are having salary in between 30,000 t0 50,000. Look at the SQL Queries below.
Example:
SELECT * FROM ManagerDetails WHERE Manager_Salary BETWEEN 30000 AND 40000;
LIKE Clause
We use Like Clause in a SQL SELECT Statement to Search. For an example in ManagerDetails table I want to fetch all those record who are with the first name “Ravi”.
Example:
SELECT * FROM ManagerDetails WHERE Manager_Name LIKE 'Ravi%';
The meaning of % at the end of Ravi is what are the names started with these characters. In case you want to search those records where the middle name is Ravi you can go for the expression “%Ravi%”.
ORDER BY Clause
We use Order By clause with Select statement for sorting. Using Order By clause there are two types of sorting can possible Ascending and Descending. For Ascending order we use ASC and for Descending order we use DESC keywords respectively. Look at the example below how I am fetching Manager Details depending upon there Salary.
SELECT * FROM ManagerDetails ORDER BY Manager_Salary ASC;
OR
SELECT * FROM ManagerDetails ORDER BY Manager_Salary DESC;
GROUP BY Function
We use GROUP BY function in conjunction with aggregate functions to group the set of results by one or more columns. Syntax for Group By function is as below.
Syntax: SELECT Column_Name, aggregate_function(Column_Name)
FROM Table_Name
WHERE Column_Name operator value
GROUP BY Column_Name;
COUNT Function
To count number of records against column(s) we use Count Function. For an example if you want to count the number of rows in ManagerDetails table, you can execute the following query.
SELECT COUNT(*) FROM ManagerDetails;
HAVING Function
Having function work similar like where Clause but the only difference is where clause can’t be used with aggregate functions where Having can.
Syntax: SELECT Column_Name, aggregate_function(Column_Name)
FROM Table_Name
WHERE Column_Name operator value
GROUP BY Column_Name
HAVING aggregate_function(Column_Name) operator value;
CREATE & DROP INDEX
Using index in a SQL Table we fetch the record in less time. User can’t view the index but in behind it works to speed of search queries.
CREATE INDEX myIndex ON ManagerDetails (Manager_ID);
Not only for a Single Column index can be set for multiple Columns.
CREATE INDEX myIndex ON ManagerDetails (Manager_ID, Manager_Name);
Also we can Create unique index on a table. Unique index not allow duplicate values.
CREATE UNIQUE INDEX myIndex ON ManagerDetails (Manager_ID);
To drop index from a SQL Table you can use the following Queries for various databases.
Drop index Syntax for MS Access:
Syntax: DROP INDEX index_name ON table_name;
Drop index Syntax for MS SQL Server:
Syntax: DROP INDEX table_name.index_name;
Drop Index Syntax for MySQL:
Syntax: ALTER TABLE table_name DROP INDEX index_name;
ALTER & UPDATE Queries
Generally we use Alter SQL Statement to add or modify Columns of a Table. The syntax of ALETER Statement is as below.
Syntax: ALTER TABLE Table_Name ADD Column_Name Datatype;
For an example if I want to add one more column to my ManagerDetails table I can execute the following query.
ALTER TABLE ManagerDetails ADD Emp_Age date;
Like ALTER UPDATE query helps to update specific record in a table. Assume that in my ManagerDetails table I want to updated “Rosalin Roy” salary to “65000”. Then the following query I need to execute.
UPDATE ManagerDetails SET Manager_Salary='65000' WHERE Manager_ID=4;
Syntax for UPDATE Statement is as below.
Syntax: UPDATE Table_Name SET Column_Name1=Value1, Column_Name2=Value2, … WHERE Some_Column=Some_Value;
TURNCATE & DELETE TABLE SQL Queries
Both the command TURNCATE & DELETE we use to delete records from a SQL Table. But the only difference is using delete we only delete records but the identity id did not get deleted. For an example if you have 5 records in a table and you used delete statement to clear the records. When after delete all records you will insert a new record it will show you id 6. But using TURNCATE identity filed get reset. Look at the example of TURNCATE & DELETE queries.
Delete statement can be 2 types Conditional or All. To remove all records from a table we use:
DELETE * FROM ManagerDetails;
To have a conditional DELETE Statement we use the following Syntax.
Syntax: DELETE FROM table_name WHERE some_column=some_value;
Similar to DELETE Statement the following Syntax we use for TURNCATE a table.
Syntax: TRUNCATE TABLE table_name;
