MS SQL Database basic

1.DATABASE CREATE:
CREATE DATABASE mydb
mydb

2.DATABASE RENAME:
ALTER DATABASE mydb MODIFY NAME= mynewdb
mynewdb
Method:1
Method:2
Sp_renameDB ‘mynewdb’ , ‘mydb’
mydb

3.DROP DATABASE
DROP DATABSAE mydb
mydb

4.CREATE TABLE
CREATE TABLE mytable
(
id int primary key ,
name nvarchar(50) not null,
age int(20)
)
NEED: TABLE NAME, COLOMN NAME ,DATA TYPE+SIZE.
OPTIONAL:CONSTRAINTS(such as primary key, check constraints)
mytable

VIEW TABLE PROPERTY
EXEC sp_help mytable

5.INSERT DATA IN TABLE
INSERT INTO mytable
VALUES ( 1,`wali`,25)
Method:1(single row insert)
Method:2(multirow insert)
INSERT INTO mytable
(id,name,age )
VALUES ( 1,`wali`,25), ( 2,`akash`,26);

6.UPDATE DATA IN TABLE
UPDATE mytable
SET name =‘batash’ ,age=25
WHERE id=2;

7.DELETE DATA IN TABLE
DELETE mytable WHERE id=1;
Delete select value:
Delete all value:
DELETE FROM mytable
Delete full table:
DROP TABLE mytable

8.BASIC SELECT STATETMENT
SELECT * FROM mytable;
Show all values in table:
Show selected one column values:
SELECT age FROM mytable;
Show selected multiple column values:
SELECT age,name FROM mytable;

9.SELECT STATEMENT
SELECT DISTINCT
name FROM mytable
(USING DISTINCT)

10.SELECT STATEMENT
For using WHERE Clause
at first we need to know about
WHERE Clause operators
(USING WHERE Clause)

WHERE Clause OPERATORS
=, >, <, >=, <=, <>, !=, !>, !<
AND,OR,NOT
BETWEEN
LIKE
IN
ALL,ANY,SOME
EXISTS

11.SELECT STATEMENT
SELECT * FROM mytable WHERE age = 25
SELECT * FROM mytable WHERE age != 25
SELECT * FROM mytable WHERE age = 25 AND name = ‘wali’
SELECT * FROM mytable WHERE age = 25 OR name = ‘wali’
SELECT * FROM mytable WHERE name LIKE ‘w%’
SELECT * FROM mytable WHERE name LIKE ‘%wa%’
SELECT * FROM mytable WHERE name NOT LIKE ‘%wa%’
SELECT * FROM mytable WHERE name = ‘aksh’ OR name = ‘wali’
SELECT * FROM mytable WHERE name IN (‘aksh’,‘wali’)
SELECT * FROM mytable WHERE EXISTS (SELECT id FROM
mytable2 WHERE fee>500)
(USING WHERE Clause)

12.SELECT STATEMENT
SELECT * FROM mytable ORDER BY
age DESC
(USING ORDER BY Clause)part-1
SHOW ALL VALUES:
SELECT * FROM mytable ORDER BY
age ASC
ASC=ascending;
DESC=descending
Note:

13.SELECT STATEMENT
SELECT name FROM mytable ORDER
BY name DESC
(USING ORDER BY Clause)part-2
SHOW SELECTED COLUMN VALUES:
SELECT name FROM mytable ORDER
BY age ASC
ASC=ascending;
DESC=descending
Note:

14.SELECT STATEMENT
Creating Groups of data

15.SELECT STATEMENT
AGGREGATE MEANS
SUM(numeric)
AVG(numeric)
MIN/MAX(int,datetime,varchar,..)
COUNT(*)-return number of rows
AGGREGATE (USING GROUP BY Clause)

16.SELECT STATEMENT
SELECT id ,SUM(salary) FROM mytable
GROUP BY id

17.SELECT STATEMENT
SELECT id ,SUM(salary) as totalsalary
FROM mytable GROUP BY id

18.SELECT STATEMENT
SELECT id ,SUM(salary) as totalsalary,
AVG(salary) as aversalary FROM mytable
GROUP BY id

19.SELECT STATEMENT
SELECT COUNT(*) FROM mytable

20.SELECT STATEMENT
(USING JOIN Clause)
JOIN
INNER JOIN OUTER JOIN FULL JOIN CROSS JOIN
LEFT JOIN RIGHT JOIN

21.SELECT STATEMENT
At first we need two table
(USING JOIN Clause)

22.SELECT STATEMENT
Simple basic command:
(USING JOIN Clause)
SHOW ALL VALUES FROM BOTH TABLE
INNER JOIN
SELECT * FROM student INNER JOIN
guardian
On student.studentid=guardian. guardianid

23.SELECT STATEMENT
Here is output:
(USING JOIN Clause)
Before joining
After joining

24.SELECT STATEMENT
advance command:
(USING JOIN Clause)
SHOW selected column VALUES FROM BOTH TABLE
INNER JOIN

25.SELECT STATEMENT
INNER JOIN
(USING JOIN Clause)
SELECT s.studentid as ‘id student table’,
g.studentid as ‘id guardian table’,
s.name,g.fathername,g.mothername
FROM student s INNER JOIN guardian g
On s.studentid=g.studentid
s=student table
g=guardian table

26.SELECT STATEMENT
(USING JOIN Clause)
Before joining
After INNER joining

27.SELECT STATEMENT
BASIC
(USING JOIN Clause)
OUTER JOIN
LEFT RIGHT
COLUMN COLUMN
RIGHT
JOIN
LEFT
JOIN

27.SELECT STATEMENT
advance command:
(USING JOIN Clause)
OUTER JOIN
(LEFT JOIN)

28.SELECT STATEMENT
OUTER JOIN –(LEFT JOIN)
(USING JOIN Clause)
FROM student s LEFT JOIN guardian b
s=student table
g=guardian table

29.SELECT STATEMENT
(USING JOIN Clause)
Before joining
After LEFT joining

30.SELECT STATEMENT
advance command:
(USING JOIN Clause)
OUTER JOIN
(RIGHT JOIN)

31.SELECT STATEMENT
OUTER JOIN –(RIGHT JOIN)
(USING JOIN Clause)
FROM student s RIGHT JOIN guardian b
s=student table
g=guardian table

32.SELECT STATEMENT
(USING JOIN Clause)
Before joining
After RIGHT joining

33.SELECT STATEMENT
(USING JOIN Clause)
FULL JOIN

34.SELECT STATEMENT
FULL JOIN
(USING JOIN Clause)
FROM student s FULL JOIN guardian b
s=student table
g=guardian table

35.SELECT STATEMENT
(USING JOIN Clause)
Before joining
After FULL joining

33.SELECT STATEMENT
(USING JOIN Clause)
CROSS JOIN
To be continue..............

FLASH BACK
1.DATABASE— create,rename,delete
2.TABLE— CREATE, data INSERT+UPDATE ,
data+table DELETE

FLASH BACK
a.basic select statement
b. select statement—(using distinct)
c.(using where clause)+where clause operators
d.order by clause(asc,desc)
e.group by clause(aggregate—sum,min/max,avg,count)
f.join—inner,outer(left+right),full join,
3.SELECT STATEMENT:

33.SELECT STATEMENT
(USING JOIN Clause)
CROSS JOIN

33.SELECT STATEMENT
(USING JOIN Clause)
CROSS JOIN
Wali
Akash
Reza
Foyel
Murad
Haider
diit
horizone

33.SELECT STATEMENT
(USING JOIN Clause)
CROSS JOIN
diit_table horizone_table

33.SELECT STATEMENT
(USING JOIN Clause)
CROSS JOIN
SELECT
diit_student_name,
horizon_student_name
FROM diit_table
CROSS JOIN
horizon_table

33.SELECT STATEMENT
(USING JOIN Clause) CROSS JOIN
diit_table horizone_table
BEFORE
JOINING
After CROSS JOINING

MS SQL Database basic

More Related Content

What's hot

Viewers also liked

Similar to MS SQL Database basic

Recently uploaded

MS SQL Database basic