Understanding Sql basics queries
In this blog, I am trying to incorporate all the SQL queries with the instance that typically you may not get simply along and that I will attempt to make a case for terribly deep regarding the SQL question. As we know, a database is needed for each computer code application thus, SQL will the duty of linking software application to the database. SQL plays a really important role in the database.
An SQL stands for the structured query language it is the standard language for accessing and manipulating the database.
A table could be an assortment of connected data entries and it consists of column and rows.
Types Of SQL Keys
The various type of keys in SQL which are as follows.
Primary Key: A combination of not null and unique is though as a primary key. It implies that it unambiguously identifies every record in an exceedingly table and follow cluster indexing.
Unique Key: A unique is one in which it unambiguously identifies every record in an exceedingly table, however, settle for one null value.
Composite Key: A combination of two or more over two columns to create a primary key is referred to as a composite key.
Candidate Key: A candidate key is those that are sensible to be a primary key.
Foreign Key: A key that makes the parent-child relationship between 2 tables is thought as a foreign key. One column that incorporates a primary key of parent table creates the foreign key within the kid table to form reference of the parent table.
Systematic collection of data is known as a database.
DML:- Data Manipulation Language
Uses to retrieve modify data and includes.
Select Update Delete Insert
DDL:- Data Definition Language
It is used to modify the structure of a database and includes.
Create Alter Drop Truncate
DCL:- Data Control Language
It is used to permissions and referential integrity and includes.
TCL:-Transactional Control Language
It is using to handle different transactions and includes
Commit Rollback Save Transaction
First We create Database With Query:
Syntax: Create database name of database
Query:- Create database practice
Create a table Of Student Name with primary key And Auto Incremented Id:
Query:- create table student(Id int identity(1,1) primary key,Roll_Number int, Name Varchar(50),Course varchar(50))
Query For Insert Values into a table student:
Insert into student values(1,'Raja','B.tech'); Insert into student values(2,'Priya','MCA'); Insert into student values(3,'Dev','BCA'); Insert into student values(4,'Vish','MCA');
Note: we can also insert from this way
Insert into student(Roll_Number,name,course) values(1,'Raja','B.tech'); Insert into student(Roll_Number,name,course) values(2,'Priya','MCA'); Insert into student(Roll_Number,name,course) values(3,'Dev','BCA'); Insert into student(Roll_Number,name,course) values(4,'Aman','MCA');
Query For Select All Records:
Select * from Student
Alias: select records from table student with the alias name.
Query:- select Roll_Number, name as student_name, course as class from student;
Update and delete records from table student:
If we update a value in a column without using where clause then updates value throughout the entire column of a table.
Query -> update student set name ='vons'
If the value updates with where clause then we can specifically update the records in a table.
Query ->Update student set name='vons' where id='1';
If we want to delete a record in a table then we can use delete command.
Query -> delete from student where name='priya'
Note: If we execute delete command without where clause then all records will be deleted.
If we would like to do any modification in a very table like add column drop column etc then we will use alter statement.
Example -> we would like to feature an additional column of name age within the existing table student then we have a tendency to use the statement.
Query -> Alter table student add age int
Query -> alter table employee alter column salary int
Use Of AND, BETWEEN, IN Keywords
We discuss all the three keywords ‘AND’, ‘BETWEEN’ and ‘IN’ in the DML operation.
Query -> Select * from student where course='Mca' and age='18'
Query -> Update student set Name='gymmy' where course='Mca' and age='25'
Query -> Delete from student where course=’Mca’ and age=’25’
‘Between’ With Select
Query -> select * from student where age between '20' and '30' Result:
‘Between’ With Update:-
We have added one more column with name Mobile in the student table.
Query -> update student set mobile='Allow' where age between 20 and 30
‘IN’ With Select:-
Query -> select * from student where id in(1,3)
‘IN’ With Update:-
Query -> update student set mobile='Not Allow' where roll_number in (1,3)
‘IN’ With Delete:-
Query -> delete from student where age in(20,18)
Using Like Keyword
We are going to use ‘like’ keyword with a table of name Employee.
‘Like’ With Select:-
Name like ‘A%’ Start with A
Emp_Name like ‘%D’ End with D
Department like ‘%A%’ Anywhere A exist
Query -> select * from Employee where Emp_name like 'A%' select * from Employee where Emp_name like '%d' select * from Employee where Emp_name like '%d%'
The all aggregate function is using in SQL queries we will discuss one by one.
Query -> select min(salary) from employee select min(age) from employee
Query -> select max(salary) from employee select max(age) from salary
Query -> select count(*) from employee select count(salary) from employee
Query -> select avg(salary) from employee select avg(salary) from employee where Emp_name like '%d%'
Query -> select sum(salary) from employee
select sum(salary) from employee where id in(1,2,3)
Order By Keyword:-
The order by keyword is employed to rearrange data in associate degree ascending or degressive order with relation to a particular column. By default order by is ascending.
Query -> select * from employee order by id //by default ascending select name, emp_code, age from employee order by age desc
Group By Keyword:-
Group by keyword are used to group selected column with an aggregate function. We want to count a particular column with respect to a selected column then use group by keyword.
Query -> select count(department),department from employee group by department select count(salary),salary from employee group by salary
If we would like to quick retrieval of data from an enormous data information then we tend to use a top keyword. we have got a table employee that has thousands of records then we would like to retrieve quick ten records.
Query -> select top (10) * from employee
Use Of Substring
If we would like to pick a column per would like then we are able to use substring function. Substring contains three arguments column name, star, and length.
Syntax: substring(column_name, star_index, length)
Example:- we want to select last 4 digits of mobile column for table employee
Query -> select top (5) emp_name,department,substring(mobile,7,4)as mobile from employee select top (5) emp_name,substring(department,1,1) from employee
Use Of Stuff
The stuff function is employed to feature some values during a column specified begin from a particular index and finish with a particular length and replace from a particular string then we will use stuff. Stuff needed four argument column name, start, length and commutation string.
Syntax: Stuff(column_name, start, length, add_string)
Example:- If we want to add ‘+91’ as prefix from every mobile number in a mobile column then we can retrieve it by the use of stuff.
Query -> select Emp_name,stuff(mobile,1,0,'+91')as mobile from employee
Use Of Distinct Keyword
A distinct keyword use to find distinct values in a particular column.
Query -> select distinct department from employee select count(distinct Emp_name) from employee select count(Emp_name) from employee
Note: If count emp_name from employee table without distinct keyword then result is 14 but if count Emp_name with a distinct keyword then result is 12 because common values repeating in a column.
Having clause is just like where clause. where clause is not used with a group by but having clause is used after the group by statement.
If we have a table of name ‘Items’ and column name ‘Id’ and ‘Item’ now we retrieve data with the use of having a clause.
Query -> select id, sum(item) as total_Item from items group by id having sum(item)>50
It is wont to count the length of a given string. len() function doesn’t embrace space at the tip of string however it includes space at the beginning of a string.
Query -> select len('Rashid') select len(deoartment) from employee How to count the occurrence of a character in a string:
Example: We have a table of name employee and we want to count how many times of ‘a’ occurs in a column emp_name.
Query -> SELECT len(emp_name) - len(REPLACE(emp_name, 'a' ,''))as Occurrence,emp_name from employee
We have two table one is Table_customer and another is Table_order such that columns have customer id, customer name, order count and order id, customer id, order item respectively. Now we use inner join from these two tables.
Note: By default, a join is an inner join.
Query ->select aa.customerid,aa.customername, aa.ordercount from Table_customer as aa inner join Table_order as bb on aa.customerid=bb.customerid
All records from a left table(table1) and matching records from a right table(table2) and null when no match in the right table.
Query -> select aa.customerid,aa.customername, aa.ordercount,bb.orderitem from Table_customer as aa left join Table_order as bb on aa.customerid=bb.customerid
All records from a right table(table2) and matching records from left(table1) and null in the left when no match.
Query -> select aa.customerid,aa.customername, aa.ordercount,bb.orderitem from Table_customer as aa right join Table_order as bb on aa.customerid=bb.customerid
Full Outer Join:
All records from both the table table1 and table2.
Query -> select aa.customerid,aa.customername, aa.ordercount,bb.orderitem from Table_customer as aa full outer join Table_order as bb on aa.customerid=bb.customerid
SQL SELECT INTO
How to create a table from existing table(backup table):
syntax: select * into Backup_table_name from Existing_table_name
Query -> select * into employee1 from employee
If we want to create new table from existing table but only structure, not data then
Query -> select * into employee1 from employee where 1=0
To combine the two or more than two select statement.
- Each select statement with the union must have a same number of column.
- It must have a same data type of column.
- The column in each select statement must also be in the same order.
We have two table customer, product and their column is id, name, and PID, product name respectively.
Query -> select id, name from customer union select pid, productname from product
The union all operator select duplicate values but union operator select only distinct values.
Query -> select id, name from customer union all select pid,productname from product
View In SQL
A view is a virtual table based on our need for a result set. If we have a table of multiple columns and we want to show only specific column to the user then we can create for that specific column.
- If the view creates from a single table than all operation select delete and update can be performed and also reflect the original table.
- If the view creates from two or more than two table than only select operation can perform.
Syntax: Create view view_name as select column1,column2,… from table_name where condition
Query -> create view myview as select id,emp_name,department from employee where age>20 and age<28 select * from myview
Delete, Truncate and Drop
In delete statement, we can use a condition like where clause.
Query -> delete from employee where id='1'
To delete all records in a table we use a truncate command. We can not use any condition like where clause with truncate command.
Query -> Truncate table employee
To eliminate table, records as well as structure we use drop command
Query -> drop table employee
In this blog, I have covered all the major SQL questions that are often in SQL database and wish for each SQL learner still as a computer code developer. I hope this blog proves extremely helpful for you!