How to understand Sql basics query
Print Friendly, PDF & Email

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.

SQL

An SQL stands for the structured query language it is the standard language for accessing and manipulating the database.

Table

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.

Database

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.

Revoke
Grant

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))

Result: 

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

Result: 

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' 

Result:

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';

Result:

Delete

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.

Alter keyword

Add Column:

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

Change Datatype:

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.

Select:-

Query -> Select * from student where course='Mca' and age='18'

Update:-

Query -> Update student set Name='gymmy' where course='Mca' and age='25'

Delete:-

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

Result:

‘IN’ With Select:-

Query -> select * from student where id in(1,3)

Result:

‘IN’ With Update:-

Query -> update student set mobile='Not Allow' where roll_number in (1,3)

Result:

‘IN’ With Delete:-

Query -> delete from student where age in(20,18)

Result:

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%'

Aggregate Function

  1. Min
  2. Max
  3. Count
  4. Avg
  5. Sum

The all aggregate function is using in SQL queries we will discuss one by one.

Min:

Query -> select min(salary) from employee
select min(age) from employee

Max:

Query -> select max(salary) from employee
select max(age) from salary

Count:

Query -> select count(*) from employee
select count(salary) from employee

Avg:

Query -> select avg(salary) from employee
select avg(salary) from employee where Emp_name like '%d%'

Sum:

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

Result:

     

Top Keyword:

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 

Result:

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

Result:

     

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

Result:

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

Result:

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

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

Result:

Len Function:

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

Result:

 

SQL JOIN:-

Inner Join:

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

Result:

Left Join:

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

Result:

Right Join:

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

Result:

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

Result:

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

Union Operator

To combine the two or more than two select statement.

Note:

  • 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

Result:

Union All:-

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

Result:

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.

Note:

  • 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

Result:

Delete, Truncate and Drop

Delete:

In delete statement, we can use a condition like where clause.

Query -> delete from employee where id='1'

Truncate:

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

Drop:

To eliminate table, records as well as structure we use drop command

Query -> drop table employee

Conclusion

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!

  •  
  •  
  •  
  •  
  •  

LEAVE A REPLY

Please enter your comment!
Please enter your name here