ms sql database basic
TRANSCRIPT
DATABASE BASICMD.WALIUZZAMAN
1.DATABASE CREATE:
CREATE DATABASE mydb
mydb
2.DATABASE RENAME:ALTER DATABASE mydb MODIFY NAME= mynewdb
mynewdb
Method:1
Method:2Sp_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 TABLEINSERT 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 TABLEUPDATE mytable SET name =‘batash’ ,age=25WHERE id=2;
7.DELETE DATA IN TABLEDELETE mytable WHERE id=1;
Delete select value:
Delete all value:DELETE FROM mytableDelete 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,NOTBETWEENLIKEINALL,ANY,SOMEEXISTS
11.SELECT STATEMENTSELECT * FROM mytable WHERE age = 25SELECT * FROM mytable WHERE age != 25SELECT * 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-1SHOW 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-2SHOW SELECTED COLUMN VALUES:
SELECT name FROM mytable ORDER BY age ASC
ASC=ascending;DESC=descending
Note:
14.SELECT STATEMENTCreating Groups of data
15.SELECT STATEMENT
AGGREGATE MEANSSUM(numeric)AVG(numeric)MIN/MAX(int,datetime,varchar,..)COUNT(*)-return number of rows
AGGREGATE (USING GROUP BY Clause)
16.SELECT STATEMENTAGGREGATE (USING GROUP BY Clause)
SELECT id ,SUM(salary) FROM mytable GROUP BY id
17.SELECT STATEMENT
SELECT id ,SUM(salary) as totalsalary FROM mytable GROUP BY id
AGGREGATE (USING GROUP BY Clause)
18.SELECT STATEMENT
SELECT id ,SUM(salary) as totalsalary, AVG(salary) as aversalary FROM mytable GROUP BY id
AGGREGATE (USING GROUP BY Clause)
19.SELECT STATEMENT
SELECT COUNT(*) FROM mytable
AGGREGATE (USING GROUP BY Clause)
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 TABLEINNER 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.mothernameFROM student s INNER JOIN guardian gOn s.studentid=g.studentid
s=student tableg=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 JOINLEFT JOIN
27.SELECT STATEMENTadvance command:
(USING JOIN Clause)
SHOW selected column VALUES FROM BOTH TABLE
OUTER JOIN(LEFT JOIN)
28.SELECT STATEMENTOUTER JOIN –(LEFT JOIN) (USING JOIN Clause)
SELECT s.studentid as ‘id student table’, g.studentid as ‘id
guardian table’, s.name,g.fathername,g.mothernameFROM student s LEFT JOIN guardian bOn s.studentid=g.studentid
s=student tableg=guardian table
29.SELECT STATEMENT (USING JOIN Clause)
Before joining
After LEFT joining
30.SELECT STATEMENTadvance command:
(USING JOIN Clause)
SHOW selected column VALUES FROM BOTH TABLE
OUTER JOIN(RIGHT JOIN)
31.SELECT STATEMENTOUTER JOIN –(RIGHT JOIN) (USING JOIN Clause)
SELECT s.studentid as ‘id student table’, g.studentid as ‘id
guardian table’, s.name,g.fathername,g.mothernameFROM student s RIGHT JOIN guardian bOn s.studentid=g.studentid
s=student tableg=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)
SELECT s.studentid as ‘id student table’, g.studentid as ‘id
guardian table’, s.name,g.fathername,g.mothernameFROM student s FULL JOIN guardian bOn s.studentid=g.studentid
s=student tableg=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 JOINWali
Akash
Reza
Foyel
Murad
Haider
diit
horizone
33.SELECT STATEMENT (USING JOIN Clause)
CROSS JOINdiit_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_tableBEFORE JOINING
After CROSS JOINING