SQL Server Management Studio (BackEnd)
Database is a collection of interrelated data.
To create table in database , go to tables ,
new table,enter all the fields required along with their data types .
Create
database
Q.How to create database using Query
(DBSchool is NAME
FOR DATABASE)
Q. How to create a table using Query.
Syntax
create table TBLStudent (id int , name
varchar(50) , class int , mark int , fees int)
Table name (row name
datatypes, “name” varchar(50))
INSERT
QUERY
Q.How to enter values into Table
Syntax
insert into TBL_Student values ( 110, 'THESH'
, 'IT' , 2 , 13000, 70)
Insert into TableName values (100,’name’)
SELECT
QUERY
- select *
from TBL_Student
- select *
from TBL_Student where name = 'Nithesh'
- select *
from TBL_Student where mark >70
- select *
from TBL_Student where department = 'It'
- select *
from TBL_Student where mark > 50 and mark < 80
- select *
from TBL_Student where mark > 50 or
- select *
from TBL_Student where mark between 50 and 90
- select *
from TBL_Student where department in ('cs', 'it')
- select *
from TBL_Student where department not in ('cs', 'it')
- select *
from TBL_Student where name like 'nit%'
- select *
from TBL_Student where name like '%en'
- select *
from TBL_Student where name like '%i
- select *
from TBL_Student order by name
- select *
from TBL_Student order by name desc
- select *
from TBL_Student order by fees asc
- select *
from TBL_Student order by fees desc
- select name
, mark from TBL_Student
- select name
, mark , fees , department from TBL_Student where fees >50000
- select
distinct department from TBL_Student
- select department from TBL_Student group by
department
- select max
(mark) from TBL_Student
- select min
(mark) from TBL_Student
- select sum
(fees) from TBL_Student
- select avg
(fees) from TBL_Student
- select count
(*) from TBL_Student
- select name,
id , department, salary from TBLOffice where salary = (select max(salary)
from TBLOffice )
select name, id , department, salary from
TBLOffice where salary = (select max(salary) from TBLOffice )
UPDATE
QUERY
UPDATE TBL_Student set fees =30000 where name
='nithin'
UPDATE TBL_Student set fees =30000 where name
='nithin'
update TBL_Student set name = 'Kishor' ,fees =
27000 where student_id = 106
update TBL_Student set name = 'Joy' ,
department = ' civil' , mark = 99 where student_id =102
update TBL_Student set name = 'Rakesh' , mark
= 100 , fees = 7000 , year = 2 where student_id = 104
update TBL_Student set name =' James ' , mark
= 83 , fees = 40000, year = 4 , department ='Bio' where student_id = 108
DELETE
QUERY
delete from TBL_Student where name = 'joy'
delete from TBL_Student where mark > 98
delete from TBL_Student where department ='cs'
delete from TBL_Student where fees > 50000
or mark < 20
delete from TBL_Student where student_id = 106
delete from TBL_Student
ALTER
QUERY
alter Table TBLOffice add designation
varchar(50)
alter table TBLOffice add city varchar(50)
DROP
COLUMN
alter Table TBLOffice add designation
varchar(50)
alter table TBLOffice add city varchar(50)
alter table TBLOffice drop column designation
alter table TBLOffice drop column city
DROP
TABLE
##Drop
deletes the table and contents of the table
But delete only removes the contents in the
table##
DROP
DATABASE
DROP DATABASE DbCollege
A JOIN clause is used to combine rows from two
or more tables, based on a related column between them.
Different Types of SQL JOINs
Here are the different types of the JOINs in
SQL:
(INNER) JOIN: Returns records that have
matching values in both tables
LEFT (OUTER) JOIN: Returns all records from
the left table, and the matched records from the right table
RIGHT (OUTER) JOIN: Returns all records from
the right table, and the matched records from the left table
FULL (OUTER) JOINl l CROSS JOIN: Returns all records when there is a match in either left or right table
JOINS
Inner
Join
select TBLCustomer.customerid , TBLCustomer.customername , TBLOrder.orderid from TBLCustomer inner join TBLOrder on TBLCustomer.customerid = TBLOrder.customerid
Left Join
select TBLCustomer.customerid ,
TBLCustomer.customername , TBLOrder.orderid from TBLCustomer left join TBLOrder
on TBLCustomer.customerid = TBLOrder.customerid
Right
Join
select TBLCustomer.customerid , TBLCustomer.customername , TBLOrder.orderid from TBLCustomer right join TBLOrder on TBLCustomer.customerid = TBLOrder.customerid
select * from TBLCustomer cross join TBLOrder
SQL constraints are used to specify rules for the data in a table.
Constraints are used to limit the type of data
that can go into a table. This ensures the accuracy and reliability of the data
in the table. If there is any violation between the constraint and the data
action, the action is aborted.
Constraints can be column level or table
level. Column level constraints apply to a column, and table level constraints
apply to the whole table.
The following constraints are commonly used in
SQL:
NOT NULL - Ensures that a column cannot have a
NULL value
UNIQUE - Ensures that all values in a column
are different
PRIMARY KEY - A combination of a NOT NULL and
UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY - Uniquely identifies a row/record
in another table
CHECK - Ensures that all values in a column
satisfies a specific condition
DEFAULT - Sets a default value for a column
when no value is specified
NOT NULL
-create table TBLConstraints (id int NOT NULL
, name varchar(50) , city varchar(50) )
insert into TBLConstraints (name,city)values (
'nithin','kochi')
UNIQUE
create table TBLUnique (id int unique , name
varchar(50) , place varchar(50))
insert into TBLUnique values (1,
'nithhin','kochi')
PRIMARY KEY
create table TBLPrimaryK (id int primary key ,
state varchar(50))
FOREIGN KEY
create table TBLForeignKey (customerid int
primary key, customername varchar(50) , city varchar(50) ,personid int foreign
key references TBLPrimaryK (id))
ONLY INTEGERS
create table TBLCheck (id int , name
varchar(50), age int check (age>=18))
DEFAULT
create table TBLDefault (id int , name
varchar(50) , city varchar(50) default 'tvm')
insert into TBLDefault (id , name )values (1,
'nith' )
==================================
SQL BASICS
create table
CREATE TABLE `test`.`tablenew` (
`id` INT NOT NULL AUTO_INCREMENT,
`Name` VARCHAR(45) NULL,
`age` INT(30) NULL,
`city` VARCHAR(45) NULL,
`fees` INT(30) NULL,
`course` VARCHAR(450) NULL, PRIMARY KEY (`id`));
insert
insert into tablenew(`id`,`name`,`age`,`fees`,`course`) values ('1','Anzi','30','50000','Mern
stack');
update
update tablenew set city='Anchal' where id=1;
select table
SELECT * FROM test.tablenew;
select with where
condition
SELECT * FROM test.tablenew where fees>40000;
Like
SELECT * FROM test.tablenew where name like 'k%'; //strating with k SELECT * FROM
test.tablenew where name like '%i'; //ending with i SELECT * FROM test.tablenew
where name like '%i%'; //including i
between
SELECT * FROM test.tablenew where age between
30 and 40; orderd by
SELECT * FROM test.tablenew order by fees DESC;// descending order SELECT * FROM test.tablenew order by name;
SELECT
* FROM test.tablenew order by name Desc; Distinct data // if table have 3 tvm city then retrieve only one
select distinct(city) from tablenew;
Alter table
alter table tablenew
Add Dob date;
Drop coloum
alter table
tablenew drop column
Dob; delete row
Delete from tablenew
where name='parvathy' and city='Tvm';
Drop
table delete table
drop Table test.ss;
SQL JOIN
Inner join
:Rturns record that have matching values in both
Left(outer) join :Returns
all records from the left table and the matched records from the right table
Right (Outer join) :Returns all record from the right table , and the matched records from the left table
Full (outer ) join Returns
all records when there is a match
either left or right table
Table customer
# customerid, name, city '3',
'Anju', 'tvm'
'4', 'Siva', 'kollam'
'5', 'Anu', 'pune'
Table order
# orderid,
customerid, city '101', '1',
'tvm'
'102', '2', 'kollam'
'103', '3', 'kochi'
Inner join
SELECT
tblcustomer.customerid, tblcustomer.name, tblorders.orderid FROM
tblorders INNER JOIN
tblcustomer ON tblorders.customerid = tblcustomer.customerid;
# customerid, name, orderid '3',
'Anju', '103'
Left join
SELECT
tblcustomer.customerid,
tblcustomer.name, tblorders.orderid FROM
tblorders left JOIN
tblcustomer ON tblorders.customerid = tblcustomer.customerid;
# customerid, name, orderid '3',
'Anju', '103'
NULL, NULL, '101'
NULL, NULL, '102'
RIGHT JOIN
SELECT
tblcustomer.customerid,
tblcustomer.name, tblorders.orderid FROM
tblorders right JOIN
tblcustomer ON tblorders.customerid = tblcustomer.customerid;
# customerid, name, orderid '3',
'Anju', '103'
'4', 'Siva', NULL
'5', 'Anu', NULL
FULL JOIN
select * from tblorders cross
join tblcustomer;
# orderid,
customerid, city, customerid, name, city '101', '1',
'tvm', '3', 'Anju', 'tvm'
'102', '2', 'kollam',
'3', 'Anju', 'tvm'
'103', '3', 'kochi',
'3', 'Anju', 'tvm'
'101', '1', 'tvm',
'4', 'Siva', 'kollam'
'102', '2', 'kollam',
'4', 'Siva', 'kollam'
'103', '3', 'kochi',
'4', 'Siva', 'kollam'
'101', '1', 'tvm',
'5', 'Anu', 'pune'
'102', '2', 'kollam',
'5', 'Anu', 'pune'
'103', '3', 'kochi',
'5', 'Anu', 'pune'
CONSTRAINTS
Not null
create table student(studentid int not null,name varchar(45),batch varchar(50));
Not null used to the column has value all time insert into student(name,batch) values ('anzi','cs'); UNIQUE
create table studentdetails(studentid int unique,name
varchar(45),batch varchar(50));
The unique used to unique value only enter the particular column insert into studentdetails(studentid,name,batch) values (1,'anzi','cs');
insert into studentdetails(studentid,name,batch) values (1,'anzi','cs');
PRIMARY KEY
create table studentdetailsprimery(studentid int primary key,name varchar(45),batch varchar(50));
Primary key not null and also a unique key, primary key is the other table foreign key
insert into studentdetailsprimery(studentid,name,batch) values (1,'anzi','cs');
insert into studentdetailsprimery(studentid,name,batch) values (1,'anzi','cs');
FORIIGN KEY
create table studentmarks(markid int primary key,name
varchar(45),batch varchar(50),mark int,studentid int ,foreign key (studentid) references
studentdetailsprimery (studentid) );
insert into studentmarks(markid,name,batch,mark,studentid) values (1,'anzi','cs',60,1);
insert into studentmarks(markid,name,batch,mark,studentid) values (2,'anzi','cs',60,2);
CHECK CONSTRINTS
create table studentdetailscheck5(studentid int primary key,name varchar(45),batch
varchar(50),age int,check(age>=18));
insert into studentdetailscheck7(studentid,name,batch,age) values (1,'anzi','cs',10);
insert into studentdetailscheck(studentid,name,batch,age) values (2,'anu','cs',20);
CREATE TABLE studentdetailscheck7 ( studentid INT PRIMARY KEY,
name VARCHAR(45), batch VARCHAR(50),
age INT,
CONSTRAINT
chk_age check(age >= 18)
);
Not worked in mysql new version, its used the enter data only age >=
18 other wise show the error message
DEFAULT
create table
student1(studentid int not null,name varchar(45),batch varchar(50) default
'IT');
insert into student1(studentid,name)
values (1,'anzi');
Default used if we dont inser the default column entry automatically fill the data here we dont insert
th batch then column insert the data it;
SELECT
select * from tablenew;
# id, Name, age, city, fees, course
'1', 'Anzi',
'30', 'Anchal', '50000', 'Mern stack'
'3', 'Saritha',
'28', 'kollam', '44000', 'python'
'4', 'karthika',
'28', 'Tvm', '40000', 'Testing'
'5', 'keerhty',
'36', 'pathanam thitta', '50000', 'Mern stack'
'6', 'vishal',
'33', 'Tvm', '40000', 'Testing'
●
select Name,age from tablenew; ## Name, age
'Anzi', '30'
'Saritha', '28'
'karthika', '28'
'keerhty', '36'
'vishal', '33'
WHERE
select Name,age
from tablenew where fees>=50000;
# Name, age
'Anzi', '30'
'keerhty', '36'
LIKE
select Name,age
from tablenew where Name like 'a%';
# Name, age
'Anzi', '30'
The name start
with a;
select Name,age from tablenew where Name
like 'anzi';
# Name,
age 'Anzi', '30'
Here we didnt use % so full name in
single quote;
If we use % in front of the letter
example %a then show the details end with a;
BETWEEN
select * from tablenew where age between
20 and 30;
IN
select * from tablenew
where course in ('python');
# id, Name, age, city, fees, course
'3', 'Saritha', '28', 'kollam', '44000', 'python'
select * from tablenew where
course in ('python','Mern stack'); # id, Name, age, city, fees, course
'1', 'Anzi', '30', 'Anchal', '50000',
'Mern stack'
'3', 'Saritha',
'28', 'kollam', '44000', 'python'
'5', 'keerhty',
'36', 'pathanam thitta', '50000', 'Mern stack'
NOT IN
select * from tablenew where
course not in ('python');
# id, Name, age, city, fees, course
'1', 'Anzi', '30', 'Anchal', '50000',
'Mern stack'
'4', 'karthika',
'28', 'Tvm', '40000', 'Testing'
'5', 'keerhty',
'36', 'pathanam thitta', '50000', 'Mern stack'
'6', 'vishal',
'33', 'Tvm', '40000', 'Testing'
AND
select name,age from tablenew where
fees>=45000 and course='Testing';
# name, age 'karthika', '28'
Used when both conditions must be true. OR
select name,age,course,fees from tablenew where fees>=50000 or
course='Testing';
At least
one condition will be true # name, age, course, fees
'Anzi', '30', 'Mern stack', '50000'
'karthika', '28', 'Testing', '45000'
'keerhty', '36',
'Mern stack', '50000'
'vishal', '33', 'Testing', '40000'
GROUP BY
select name,max(age),course,fees from tablenew group by course; Group by used to grouping the
data, here grouping base on course and retrieve the max age details
# name, max(age),
course, fees 'Anzi', '36',
'Mern stack', '50000'
'Saritha', '28', 'python', '44000'
'karthika', '33', 'Testing', '45000'
ORDER BY
select
name,age,course,fees from tablenew order by course;
Ordering the data
#
name, age, course, fees 'Anzi', '30', 'Mern stack', '50000'
'keerhty', '36', 'Mern stack', '50000'
'Saritha', '28',
'python', '44000'
'karthika', '28', 'Testing', '45000'
'vishal', '33', 'Testing', '40000'
HAVING
select name,age,course,fees from tablenew group by course
having fees >45000;
# name, age, course, fees 'Anzi',
'30', 'Mern stack',
'50000'
HAVING → filters rows after grouping/aggregation.
AGGRIGATE FUNCTIONS
1.
MAX() → returns the largest value
2. MIN() → returns the
smallest value
3. AVG() → returns the
average value
4. COUNT() → returns the
number of rows
MAX
select max(fees)
as maxfee from tablenew;
# maxfee
'50000'
MIN
select min(fees)
as minfee from tablenew;
# minfee
'40000'
AVG
select avg(fees)
as avgfee from tablenew;
# avgfee
'45800.0000'
SUM
select sum(fees) as sumfee,course from tablenew group
by course; # sumfee, course
'100000', 'Mern stack'
'44000', 'python'
'85000', 'Testing'
COUNT
select count(course),course from tablenew group by course; # count(course), course
'2', 'Mern stack'
'1', 'python'
'2', 'Testing'
DISTINCT
select
distinct(course) from
tablenew ;
Get unique
values # course
'Mern stack' 'python' 'Testing'
DISTINCT is used to remove duplicate rows from the result set. It ensures that only unique values
are returned.
SUB QUERY
A subquery is
a query inside another query.
It is usually
written inside parentheses () and used to provide
values for the main query.
Scalar Subquery
→ returns a single value
Row Subquery → returns a single row (multiple columns) Table Subquery → returns multiple
rows (can be used with
IN,
EXISTS, or as a derived table)
1. Scalar Subquery
Get students who
have the maximum age:
SELECT *
FROM studentdetailscheck5
WHERE age = (SELECT MAX(age) FROM
studentdetailscheck5);
Get student
details of the one who has the minimum age: SELECT *
FROM studentdetailscheck5
WHERE (studentid, age) = ( SELECT studentid, age
FROM studentdetailscheck5
ORDER BY age ASC
LIMIT 1
);
Get students
who belong to batches where the average age > 20:
SELECT *
FROM studentdetailscheck5
WHERE batch IN (
SELECT batch
FROM studentdetailscheck5
GROUP BY batch
HAVING AVG(age) > 20
);
4. Using Subquery in FROM
(Derived Table)
SELECT t.batch, t.avg_age
FROM (
SELECT batch, AVG(age)
AS avg_age FROM
studentdetailscheck5
GROUP BY batch
) t
WHERE t.avg_age
> 20;
select * from tablenew where course=(select course
from tablenew where fees
>45000 group by course) ;
# id, Name, age, city, fees, course
'1', 'Anzi', '30', 'Anchal', '50000', 'Mern stack'
'5', 'keerhty', '36', 'pathanam thitta', '50000', 'Mern stack'
STORED PROCEDURE
A stored procedure is SQL server is a set of SQL statement thet can
be executed as a single unit.stored procedure
are used to encapsulate
code foe reuse,ensure,consistancy and improve performance.
A Stored Procedure is a group of SQL statements saved in the database, which you can execute
whenever needed.
Reuse queries (no need to rewrite). Improve performance (precompiled).
Enhance security (restrict direct table access).
Maintainability (centralized logic). syntax
CREATE PROCEDURE procedure_name() BEGIN
-- SQL statements END //
select USE `test`;
DROP procedure
IF EXISTS `new_procedure`;
DELIMITER $$
USE `test`$$
CREATE PROCEDURE `new_procedure` () BEGIN
select * from tablenew;
END
$$
DELIMITER ;
Insert
USE `test`;
DROP procedure
IF EXISTS `new_procedure`;
DELIMITER $$
USE `test`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE
`new_procedure`() BEGIN
insert into tablenew
(id,name,age) values (13,'anu',13); END$$
DELIMITER ;
No comments:
Post a Comment