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.
Jump to Section
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.
SelectUpdateDeleteInsert
DDL:- Data Definition Language
It is used to modify the structure of a database and includes.
CreateAlterDropTruncate
DCL:- Data Control Language
It is used to permissions and referential integrity and includes.
RevokeGrant
TCL:-Transactional Control Language
It is using to handle different transactions and includes
CommitRollbackSave 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
Min
Max
Count
Avg
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.
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.
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.
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!
I am a database administrator who has the responsibility of maintaining the integrity and performance of company databases. I guarantee that the data is stored securely and ensure all the users are informed of any changes in the same. Apart from managing all this bulk of technological binaries, I love to enjoy little things in my life. I am an avid foodie who loves to explore the abundance of specialties our cultures offer. I am also into sports, especially cricket. I make sure that I never miss my weekend morning cricket matches alongside my long-time friends. I cherish both my professional and personal lives to the fullest.
