SQL SERVER

 

SQL Server Management Studio  (BackEnd)

 

Microsoft SQL Server

 

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

 

create database DBSchool

                           (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

 

 

 

1. select * from TBL_Student
2. select * from TBL_Student where name = 'Nithesh'
3. select * from TBL_Student where mark >70
4. select * from TBL_Student where department = 'It'

 

5. select * from TBL_Student where mark > 50 and mark < 80
6. select * from TBL_Student where mark > 50 or
7. select * from TBL_Student where mark between 50 and 90
8. select * from TBL_Student where department in ('cs', 'it')
9. select * from TBL_Student where department not in ('cs', 'it')
10. select * from TBL_Student where name like 'nit%'
11. select * from TBL_Student where name like '%en'
12. select * from TBL_Student where name like '%i
13. select * from TBL_Student order by name
14. select * from TBL_Student order by name desc
15. select * from TBL_Student order by fees asc
16. select * from TBL_Student order by fees desc
17. select name , mark from TBL_Student
18. select name , mark , fees , department from TBL_Student where fees >50000
19. select distinct department from TBL_Student
20. select  department from TBL_Student group by department
21. select max (mark) from TBL_Student
22. select min (mark) from TBL_Student
23. select sum (fees) from TBL_Student
24. select avg (fees) from TBL_Student
25. select count (*) from TBL_Student

 

26. select name, id , department, salary from TBLOffice where salary = (select max(salary) from TBLOffice )

 

 

Inner query/sub query

 

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

 

JOINS

 

SQL JOIN

 

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

 

CROSS JOIN/ FULL OUTER JOIN

select * from TBLCustomer cross join TBLOrder

Constraints

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

 

 CHECK

 

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