Database (DB):
Organized collection of data stored in an electronic format.
Database Management System (DBMS):
DBMS is a system software for creating and managing database.
Types of Database Architecture:
File-Server --> Files are located on your Local system
Client-Server --> Files are located on server system which will be accessed by multiple client who requests
Structured Query Language (SQL):
SQL stands for Structured Query Language which is a standard language for accessing and manipulating database.In simple terms SQL used to communicate with database.
Categories of SQL Commands:
Data Query/Retrieval Language (DRL) --> SELECT
Data Definition Language (DDL) --> CREATE, ALTER, TRUNCATE, DROP, RENAME
Data Manipulation Language (DML) --> INSERT, UPDATE, DELETE
Data Control Language (DCL) --> GRANT, REVOKE
Transaction Control Language (TCL) --> COMMIT, ROLLBACK, SAVEPOINT
Table :
A Table is a database object which comprises(consists/contains) of rows and columns.
Records:
The rows known as Records. Each individual entry of a table is called the record.
Fields:
The columns known as Fields(attributes). A Field provides specific information about data in the table.
Create A Database:
CREATE DATABASE databasename; Ex: create database scott;
Use A Database:
USE databasename; Ex: use scott;
Drop A Database:
DROP DATABASE databasename; Ex: drop database scott;
Data Types:
Data Types define what type of data a column can hold
Numeric Data Types --> bigint, int, smallint, tinyint, decimal(s,d)
Character Data Types --> char(s) - max length 255 chars,, varchar(s) - max length 255 chars, text - max length 65,535 chars
Date & Time Data Types --> date, time, year (YYYY-MM-DD, HH:MI:SS, YYYY)
Constraints:
Constraints are used to specify rules for data in table
NOT NULL --> ensures that the column cannot have a NULL value
DEFAULT --> sets a default value for a column when no value is specified
UNIQUE --> ensures that all values in a column are different
PRIMARY KEY --> uniquely identifies each record in a table (i.e Not Null+Unique)
Create a Table:
CREATE TABLE table_name
(
column1 datatype,
column2 datatype,
column3 datatype,
------
-------
columnN datatype,
PRIMARY KEY(column_X)
);
Note: Table cannot have more than one PRIMARY KEY.
CREATE TABLE EMP
(
EMPNO INT NOT NULL,
ENAME VARCHAR (10),
JOB VARCHAR (9),
MGR INT,
HIREDATE DATE,
SAL DECIMAL (7, 2),
COMM DECIMAL (7, 2),
DEPTNO INTEGER
);
CREATE TABLE DEPT
(
DEPTNO INTEGER,
DNAME VARCHAR (14),
LOC VARCHAR (13)
);
Insert values into a Table:
INSERT INTO table_name(column1,column2,....,columnN) VALUES(value1,value2,.........valueN);
INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902,'17-DEC-1980', 800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698,'20-FEB-1981' , 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, '22-FEB-1981' , 1250, 500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, '2-APR-1981' , 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-1981' , 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1-MAY-1981' , 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, '9-JUN-1981' , 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982' , 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, '17-NOV-1981' , 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, '8-SEP-1981' , 1500, 0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, '12-JAN-1983' , 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, '3-DEC-1981' , 950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, '3-DEC-1981' , 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, '23-JAN-1982' , 1300, NULL, 10);
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
Select values from Table:
SELECT * FROM table_name;
SELECT column1,column2,....columnN FROM table_name;
SELECT DISTINCT column1 FROM table_name;
SELECT GETDATE() AS TODAY_DATE;
SELECT * FROM EMP;
SELECT EMPNO,ENAME,JOB,SAL FROM EMP;
SELECT DISTINCT DEPTNO FROM EMP;
WHERE clause is used to extract records which satisfy a condition.
SELECT column1,column2,....columnN FROM table_name WHERE [condition];
SELECT * FROM EMP WHERE DEPTNO=30;
AND --> Operator displays records if all conditions seperated by AND are TRUE
SELECT column1,column2,....columnN FROM table_name WHERE [condition1] AND [condition2] AND [conditionN];
OR --> Operator displays records if any of the conditions seperated by OR are TRUE
SELECT column1,column2,....columnN FROM table_name WHERE [condition1] OR [condition2] OR [conditionN];
NOT --> Operator displays a record if the condition is NOT TRUE.
SELECT column1,column2,....columnN FROM table_name WHERE NOT [condition1];
LIKE --> Operator used to extract records where a particular pattern is present. (% Represents zero,one or multiple characters , _ Represents Single character)
SELECT column1,column2,....columnN FROM table_name WHERE column_N like '_XXXX%';
BETWEEN --> Operator used to select values within a given range
SELECT column1,column2,....columnN FROM table_name WHERE column_N BETWEEN val1 and val2;
SELECT * FROM EMP WHERE DEPTNO=30 AND JOB='CLERK';
SELECT * FROM EMP WHERE DEPTNO=30 OR JOB='CLERK';
SELECT * FROM EMP WHERE NOT DEPTNO=30 ;
SELECT * FROM EMP WHERE DEPTNO<>30;
SELECT * FROM EMP WHERE DEPTNO!=30 ;
SELECT * FROM EMP WHERE ENAME LIKE 'S%';
SELECT * FROM EMP WHERE ENAME LIKE '_M%';
SELECT * FROM EMP WHERE ENAME LIKE '%K';
SELECT * FROM EMP WHERE DEPTNO BETWEEN 10 AND 20;
Aggregate Functions:
MIN() function gives you the smallest value SELECT MIN(column) FROM table_name;
MAX() function gives you the largest value SELECT MAX(column) FROM table_name;
COUNT() function returns the number of rows that match a specific criteria. SELECT COUNT(*) FROM table_name;
SUM() function gives you the total sum of a numeric column SELECT SUM(column) FROM table_name;
AVG() function gives you the average value of a numeric column SELECT AVG(column) FROM table_name;
SELECT MAX(SAL) FROM EMP;
SELECT MIN(SAL) FROM EMP;
SELECT SUM(SAL) FROM EMP;
SELECT AVG(SAL) FROM EMP;
SELECT COUNT(*) FROM EMP;
String Functions:
LTRIM() Removes blanks on the left side of the character expression
LOWER() Converts all characters to lower case letters
UPPER() Converts all characters to upper case letters
REVERSE() Reverse all characters in the string
SUBSTRING() Gives a substring from the original string
SELECT LTRIM(' RAJASEKHAR'),RTRIM('RAJASEKHAR '),TRIM(' RAJA ');
SELECT UPPER(ENAME) FROM EMP;
SELECT LOWER('RAJASEKHAR');
SELECT REVERSE('I LOVE SQL');
SELECT SUBSTRING('RAJASEKHAR',5,6);
GROUP BY clause is used to aggregate result with respect to a group
SELECT column_list FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s) ;
HAVING BY clause is used in combination with GROUP BY to impose conditions on groups.
SELECT column_list FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
ORDER BY clause is used to sort the data in ascending or descending order
SELECT column1,column2,....columnN FROM table_name ORDER BY col1,col2,..ASC|DESC;
TOP clause is used to fetch TOP N records.
SELECT TOP x column_list FROM table_name;
SELECT * FROM EMP ORDER BY DEPTNO ;
SELECT * FROM EMP ORDER BY DEPTNO ASC ;
SELECT * FROM EMP ORDER BY DEPTNO DESC;
SELECT * FROM EMP WHERE DEPTNO IN (10,20) ORDER BY DEPTNO ;
SELECT DEPTNO,SUM(SAL) AS TOTAL FROM EMP GROUP BY DEPTNO ;
SELECT DEPTNO,SUM(SAL) AS TOTAL FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO;
SELECT DEPTNO,SUM(SAL) AS TOTAL FROM EMP GROUP BY DEPTNO HAVING SUM(SAL)>10000;
SELECT DEPTNO,SUM(SAL) AS TOTAL FROM EMP WHERE DEPTNO IN (10,20,30) GROUP BY DEPTNO HAVING SUM(SAL)>9000 ORDER BY DEPTNO ;
UPDATE:
Update is used to modify existing records in a table,
UPDATE table_name SET col1=val1col2=val2,.... [WHERE condition];
UPDATE EMP SET SAL=SAL+1000 WHERE DEPTNO=30;
DELETE:
Delete statement is used to delete existing records in a table..
DELETE FROM table_name [WHERE condition];
DELETE FROM EMP WHERE DEPTNO=20;
TRUNCATE:
Truncate statement is used to remove all existing records in a table but structure of the table still exists..
TRUNCATE TABLE table_name;
TRUNCATE TABLE EMP;
Inner Join:
Returns records that have matching values in both the tables. It is also known as simple join.
SELECT columns FROM table1 INNER JOIN table2 ON table1.column_x=table2.column_Y;
SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,D.DNAME,D.LOC FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
Left Join;
Returns all records from the left table, and the matched records from the right table.
SELECT columns FROM table1 LEFT JOIN table2 ON table1.column_x=table2.column_Y;
SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,D.DNAME,D.LOC FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
Right Join;
Returns all records from the right table, and the matched records from the left table.
SELECT columns FROM table1 LEFT JOIN table2 ON table1.column_x=table2.column_Y;
SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,D.DNAME,D.LOC FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
Full Join;
Returns all records from the LEFT table and the RIGHT table with null values in place where join condition is not met.
SELECT columns FROM table1 FULL JOIN table2 ON table1.column_x=table2.column_Y;
SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,D.DNAME,D.LOC FROM EMP E FULL JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
Update using Join;
UPDATE table1 SET column11=value1 FROM table1 JOIN table2 ON table1.column_x=table2.column_Y [WHERE condition];
UPDATE EMP SET SAL=SAL+100
FROM EMP E JOIN DEPT D ON E.DEPTNO=D.DEPTNO WHERE D.DNAME='RESEARCH';
Delete using Join;
DELETE table1 FROM table1 JOIN table2 ON table1.column_x=table2.column_Y [WHERE condition];
DELETE EMP
FROM EMP E JOIN DEPT D ON E.DEPTNO=D.DEPTNO WHERE D.DNAME='RESEARCH';
Set Operators:
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 1;
SELECT 1
UNION
SELECT 2
UNION
SELECT 1;
(SELECT 1
UNION
SELECT 2)
EXCEPT
SELECT 1;
(SELECT 1
UNION
SELECT 2)
INTERSECT
SELECT 1;
TOP:
SELECT TOP 2 * FROM DEPT ORDER BY DEPTNO DESC;
Views:
View is a Virtual Table is based on the result of SQL statment.
CREATE VIEW EMP_V AS SELECT EMPNO,ENAME,SAL,JOB,DEPTNO FROM EMP;
SELECT * FROM EMP_V;
DROP VIEW EMP_V;
ALTER Statement:
ALTER TABLE EMP ADD TEST INT;
SELECT * FROM EMP;
ALTER TABLE EMP DROP COLUMN TEST;
CREATE TABLE EMP_S(EMPNO INT,ENAME VARCHAR(200));
INSERT INTO EMP_S VALUES(7499,'ALLEN');
SELECT * FROM EMP;
MERGE Statement:
MERGE is the combination of INSERT, DLEETE and UPDATE statements.
MERGE EMP AS T
USING EMP_S AS S ON T.EMPNO=S.EMPNO
WHEN MATCHED THEN
UPDATE SET T.ENAME=S.ENAME
WHEN NOT MATCHED BY TARGET THEN
INSERT(EMPNO,ENAME)VALUES(S.EMPNO,S.ENAME)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
Scalar Valued Function:
Scalar Valued Function always returns a scalar value.
CREATE FUNCTION add_five(@num INT)
RETURNS INT
AS
BEGIN
RETURN (@num+5);
END;
SELECT dbo.add_five(50);
DROP FUNCTION add_five;
Table Valued Function:
Table Valued Function returns a table.
CREATE FUNCTION display_emp(@num INT)
RETURNS table
AS
RETURN(SELECT * FROM EMP WHERE DEPTNO=@num);
SELECT * FROM dbo.display_emp(30);
Temporary Table:
Temporary Tables are created in tempDB and deleted as soon as the session is terminated.
CREATE TABLE #student(sid int,sname varchar(100));
INSERT INTO #student VALUES(1,'raj');
SELECT * FROM #student;
Case Statement:
Case statement helps in multi way decision making
select
case
when 10>20 then '10 is greater than 20'
when 10<20 then '10 is less than 20'
else '10 is equal to 20'
end;
IIF() Function:
IIF() function is an alternative for the case statement.
select iif(10>20,'10 is greater than 20','10 is less than 20');
Stored Procedure in sql:
Strored Procedure is a prepared sql code which can be saved and reused.
CREATE PROCEDURE display_ename
AS
--select statement
SELECT ENAME FROM EMP
GO;
EXEC display_ename;
DROP PROCEDURE display_ename;
CREATE PROCEDURE display_empno @i_deptno int
AS
SELECT * FROM EMP WHERE DEPTNO=@i_deptno
GO
EXEC display_empno @i_deptno=10;
DROP PROCEDURE display_empno;
Exception Handling:
SQL Provides try/catch blocks for exception handling
declare @val1 int
declare @val2 int
begin try
set @val1=9;
set @val2=@val1/0;
end try
begin catch
print 'catching exception'
print error_message()
end catch
go
Transactions in SQL:
Transaction is group of commands that change data stored in a database
begin transaction
update emp set sal=sal+201 where deptno=20;
commit transaction;
rollback transaction;
begin try
begin transaction
update emp set sal=0 where deptno=20
update emp set sal=1/0 ;
commit transaction
print 'commit transaction'
end try
begin catch
rollback transaction
print 'rollback transaction'
print error_message()
end catch
go
select * from emp where deptno=20;
Database Administrator:
A Database Administrator (DBA) directs or performs all activities related to maintaining a successful database environment.
Software Installation and Maintenance
Taking care of Database backup & Recovery
Maintaing Security of the database
Take care of Access control for different users
Monitor databases for performance issues
Types of DBA:
Production DBA
Application DBA
Development DBA
UAT DBA
Data warehouse DBA
Intro to MS SQL Server:
SQL Server is Microsoft's relational database management system (RDBMS). It is a full featured database primarly designed to compete
against competitors Oracle Database and MYSQL.It is also known as ORDBMS.It is both GUI and command based software.
Evolution of SQL Server:
SQL Server 2000,2005,2008,2008 R2,2012,2014,2016,2017,.....
Editions of SQL Server:
Enterprise,Standard,Web,Developer,Express
https://www.youtube.com/watch?v=JTDK6r1GuUU&list=LLJ2jlYQyxwtWSG_5RzfBBjQ&index=2&t=555s
Instances of SQL Server:
An instance is collection of SQL Server Databases run by single server service
Default and Named Instances
If you plan to install a single instance of SQL Server on database server, go with default instance
Use named instance for situations where you planned to have multiple instances on the same server computer
A computer can host only one default instance; all other instances must be named
SQL Server System Databases:
System databases ae defined by Microsoft and are needed for SQL Server to operate. These databases includes
Master --> The master database records all the system level information for a SQL Server system. This includes instance wide metadata such as
logon accounts,endpoints,liked servers and system configuration settings
Model --> The model database is used as a template whenever a new user database is created.You can change most database properties,create users,
stored procedures,tables,views,etc-whatever you do will be applied to any new databases.
MSDB --> The msdb databse is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Managment Studio,
Service Broker and Database Mail.
TempDB --> TempDB is the workhorse of the system database.It is the workspace that SQL Server uses to store the intermediate results of
query processing and sorting.
Organized collection of data stored in an electronic format.
Database Management System (DBMS):
DBMS is a system software for creating and managing database.
Types of Database Architecture:
File-Server --> Files are located on your Local system
Client-Server --> Files are located on server system which will be accessed by multiple client who requests
Structured Query Language (SQL):
SQL stands for Structured Query Language which is a standard language for accessing and manipulating database.In simple terms SQL used to communicate with database.
Categories of SQL Commands:
Data Query/Retrieval Language (DRL) --> SELECT
Data Definition Language (DDL) --> CREATE, ALTER, TRUNCATE, DROP, RENAME
Data Manipulation Language (DML) --> INSERT, UPDATE, DELETE
Data Control Language (DCL) --> GRANT, REVOKE
Transaction Control Language (TCL) --> COMMIT, ROLLBACK, SAVEPOINT
Table :
A Table is a database object which comprises(consists/contains) of rows and columns.
Records:
The rows known as Records. Each individual entry of a table is called the record.
Fields:
The columns known as Fields(attributes). A Field provides specific information about data in the table.
Create A Database:
CREATE DATABASE databasename; Ex: create database scott;
Use A Database:
USE databasename; Ex: use scott;
Drop A Database:
DROP DATABASE databasename; Ex: drop database scott;
Data Types:
Data Types define what type of data a column can hold
Numeric Data Types --> bigint, int, smallint, tinyint, decimal(s,d)
Character Data Types --> char(s) - max length 255 chars,, varchar(s) - max length 255 chars, text - max length 65,535 chars
Date & Time Data Types --> date, time, year (YYYY-MM-DD, HH:MI:SS, YYYY)
Constraints:
Constraints are used to specify rules for data in table
NOT NULL --> ensures that the column cannot have a NULL value
DEFAULT --> sets a default value for a column when no value is specified
UNIQUE --> ensures that all values in a column are different
PRIMARY KEY --> uniquely identifies each record in a table (i.e Not Null+Unique)
Create a Table:
CREATE TABLE table_name
(
column1 datatype,
column2 datatype,
column3 datatype,
------
-------
columnN datatype,
PRIMARY KEY(column_X)
);
Note: Table cannot have more than one PRIMARY KEY.
CREATE TABLE EMP
(
EMPNO INT NOT NULL,
ENAME VARCHAR (10),
JOB VARCHAR (9),
MGR INT,
HIREDATE DATE,
SAL DECIMAL (7, 2),
COMM DECIMAL (7, 2),
DEPTNO INTEGER
);
CREATE TABLE DEPT
(
DEPTNO INTEGER,
DNAME VARCHAR (14),
LOC VARCHAR (13)
);
Insert values into a Table:
INSERT INTO table_name(column1,column2,....,columnN) VALUES(value1,value2,.........valueN);
INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902,'17-DEC-1980', 800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698,'20-FEB-1981' , 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, '22-FEB-1981' , 1250, 500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, '2-APR-1981' , 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-1981' , 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1-MAY-1981' , 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, '9-JUN-1981' , 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982' , 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, '17-NOV-1981' , 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, '8-SEP-1981' , 1500, 0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, '12-JAN-1983' , 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, '3-DEC-1981' , 950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, '3-DEC-1981' , 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, '23-JAN-1982' , 1300, NULL, 10);
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
Select values from Table:
SELECT * FROM table_name;
SELECT column1,column2,....columnN FROM table_name;
SELECT DISTINCT column1 FROM table_name;
SELECT GETDATE() AS TODAY_DATE;
SELECT * FROM EMP;
SELECT EMPNO,ENAME,JOB,SAL FROM EMP;
SELECT DISTINCT DEPTNO FROM EMP;
WHERE clause is used to extract records which satisfy a condition.
SELECT column1,column2,....columnN FROM table_name WHERE [condition];
SELECT * FROM EMP WHERE DEPTNO=30;
AND --> Operator displays records if all conditions seperated by AND are TRUE
SELECT column1,column2,....columnN FROM table_name WHERE [condition1] AND [condition2] AND [conditionN];
OR --> Operator displays records if any of the conditions seperated by OR are TRUE
SELECT column1,column2,....columnN FROM table_name WHERE [condition1] OR [condition2] OR [conditionN];
NOT --> Operator displays a record if the condition is NOT TRUE.
SELECT column1,column2,....columnN FROM table_name WHERE NOT [condition1];
LIKE --> Operator used to extract records where a particular pattern is present. (% Represents zero,one or multiple characters , _ Represents Single character)
SELECT column1,column2,....columnN FROM table_name WHERE column_N like '_XXXX%';
BETWEEN --> Operator used to select values within a given range
SELECT column1,column2,....columnN FROM table_name WHERE column_N BETWEEN val1 and val2;
SELECT * FROM EMP WHERE DEPTNO=30 AND JOB='CLERK';
SELECT * FROM EMP WHERE DEPTNO=30 OR JOB='CLERK';
SELECT * FROM EMP WHERE NOT DEPTNO=30 ;
SELECT * FROM EMP WHERE DEPTNO<>30;
SELECT * FROM EMP WHERE DEPTNO!=30 ;
SELECT * FROM EMP WHERE ENAME LIKE 'S%';
SELECT * FROM EMP WHERE ENAME LIKE '_M%';
SELECT * FROM EMP WHERE ENAME LIKE '%K';
SELECT * FROM EMP WHERE DEPTNO BETWEEN 10 AND 20;
Aggregate Functions:
MIN() function gives you the smallest value SELECT MIN(column) FROM table_name;
MAX() function gives you the largest value SELECT MAX(column) FROM table_name;
COUNT() function returns the number of rows that match a specific criteria. SELECT COUNT(*) FROM table_name;
SUM() function gives you the total sum of a numeric column SELECT SUM(column) FROM table_name;
AVG() function gives you the average value of a numeric column SELECT AVG(column) FROM table_name;
SELECT MAX(SAL) FROM EMP;
SELECT MIN(SAL) FROM EMP;
SELECT SUM(SAL) FROM EMP;
SELECT AVG(SAL) FROM EMP;
SELECT COUNT(*) FROM EMP;
String Functions:
LTRIM() Removes blanks on the left side of the character expression
LOWER() Converts all characters to lower case letters
UPPER() Converts all characters to upper case letters
REVERSE() Reverse all characters in the string
SUBSTRING() Gives a substring from the original string
SELECT LTRIM(' RAJASEKHAR'),RTRIM('RAJASEKHAR '),TRIM(' RAJA ');
SELECT UPPER(ENAME) FROM EMP;
SELECT LOWER('RAJASEKHAR');
SELECT REVERSE('I LOVE SQL');
SELECT SUBSTRING('RAJASEKHAR',5,6);
GROUP BY clause is used to aggregate result with respect to a group
SELECT column_list FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s) ;
HAVING BY clause is used in combination with GROUP BY to impose conditions on groups.
SELECT column_list FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
ORDER BY clause is used to sort the data in ascending or descending order
SELECT column1,column2,....columnN FROM table_name ORDER BY col1,col2,..ASC|DESC;
TOP clause is used to fetch TOP N records.
SELECT TOP x column_list FROM table_name;
SELECT * FROM EMP ORDER BY DEPTNO ;
SELECT * FROM EMP ORDER BY DEPTNO ASC ;
SELECT * FROM EMP ORDER BY DEPTNO DESC;
SELECT * FROM EMP WHERE DEPTNO IN (10,20) ORDER BY DEPTNO ;
SELECT DEPTNO,SUM(SAL) AS TOTAL FROM EMP GROUP BY DEPTNO ;
SELECT DEPTNO,SUM(SAL) AS TOTAL FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO;
SELECT DEPTNO,SUM(SAL) AS TOTAL FROM EMP GROUP BY DEPTNO HAVING SUM(SAL)>10000;
SELECT DEPTNO,SUM(SAL) AS TOTAL FROM EMP WHERE DEPTNO IN (10,20,30) GROUP BY DEPTNO HAVING SUM(SAL)>9000 ORDER BY DEPTNO ;
UPDATE:
Update is used to modify existing records in a table,
UPDATE table_name SET col1=val1col2=val2,.... [WHERE condition];
UPDATE EMP SET SAL=SAL+1000 WHERE DEPTNO=30;
DELETE:
Delete statement is used to delete existing records in a table..
DELETE FROM table_name [WHERE condition];
DELETE FROM EMP WHERE DEPTNO=20;
TRUNCATE:
Truncate statement is used to remove all existing records in a table but structure of the table still exists..
TRUNCATE TABLE table_name;
TRUNCATE TABLE EMP;
Inner Join:
Returns records that have matching values in both the tables. It is also known as simple join.
SELECT columns FROM table1 INNER JOIN table2 ON table1.column_x=table2.column_Y;
SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,D.DNAME,D.LOC FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
Left Join;
Returns all records from the left table, and the matched records from the right table.
SELECT columns FROM table1 LEFT JOIN table2 ON table1.column_x=table2.column_Y;
SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,D.DNAME,D.LOC FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
Right Join;
Returns all records from the right table, and the matched records from the left table.
SELECT columns FROM table1 LEFT JOIN table2 ON table1.column_x=table2.column_Y;
SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,D.DNAME,D.LOC FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
Full Join;
Returns all records from the LEFT table and the RIGHT table with null values in place where join condition is not met.
SELECT columns FROM table1 FULL JOIN table2 ON table1.column_x=table2.column_Y;
SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,D.DNAME,D.LOC FROM EMP E FULL JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
Update using Join;
UPDATE table1 SET column11=value1 FROM table1 JOIN table2 ON table1.column_x=table2.column_Y [WHERE condition];
UPDATE EMP SET SAL=SAL+100
FROM EMP E JOIN DEPT D ON E.DEPTNO=D.DEPTNO WHERE D.DNAME='RESEARCH';
Delete using Join;
DELETE table1 FROM table1 JOIN table2 ON table1.column_x=table2.column_Y [WHERE condition];
DELETE EMP
FROM EMP E JOIN DEPT D ON E.DEPTNO=D.DEPTNO WHERE D.DNAME='RESEARCH';
Set Operators:
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 1;
SELECT 1
UNION
SELECT 2
UNION
SELECT 1;
(SELECT 1
UNION
SELECT 2)
EXCEPT
SELECT 1;
(SELECT 1
UNION
SELECT 2)
INTERSECT
SELECT 1;
TOP:
SELECT TOP 2 * FROM DEPT ORDER BY DEPTNO DESC;
Views:
View is a Virtual Table is based on the result of SQL statment.
CREATE VIEW EMP_V AS SELECT EMPNO,ENAME,SAL,JOB,DEPTNO FROM EMP;
SELECT * FROM EMP_V;
DROP VIEW EMP_V;
ALTER Statement:
ALTER TABLE EMP ADD TEST INT;
SELECT * FROM EMP;
ALTER TABLE EMP DROP COLUMN TEST;
CREATE TABLE EMP_S(EMPNO INT,ENAME VARCHAR(200));
INSERT INTO EMP_S VALUES(7499,'ALLEN');
SELECT * FROM EMP;
MERGE Statement:
MERGE is the combination of INSERT, DLEETE and UPDATE statements.
MERGE EMP AS T
USING EMP_S AS S ON T.EMPNO=S.EMPNO
WHEN MATCHED THEN
UPDATE SET T.ENAME=S.ENAME
WHEN NOT MATCHED BY TARGET THEN
INSERT(EMPNO,ENAME)VALUES(S.EMPNO,S.ENAME)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
Scalar Valued Function:
Scalar Valued Function always returns a scalar value.
CREATE FUNCTION add_five(@num INT)
RETURNS INT
AS
BEGIN
RETURN (@num+5);
END;
SELECT dbo.add_five(50);
DROP FUNCTION add_five;
Table Valued Function:
Table Valued Function returns a table.
CREATE FUNCTION display_emp(@num INT)
RETURNS table
AS
RETURN(SELECT * FROM EMP WHERE DEPTNO=@num);
SELECT * FROM dbo.display_emp(30);
Temporary Table:
Temporary Tables are created in tempDB and deleted as soon as the session is terminated.
CREATE TABLE #student(sid int,sname varchar(100));
INSERT INTO #student VALUES(1,'raj');
SELECT * FROM #student;
Case Statement:
Case statement helps in multi way decision making
select
case
when 10>20 then '10 is greater than 20'
when 10<20 then '10 is less than 20'
else '10 is equal to 20'
end;
IIF() Function:
IIF() function is an alternative for the case statement.
select iif(10>20,'10 is greater than 20','10 is less than 20');
Stored Procedure in sql:
Strored Procedure is a prepared sql code which can be saved and reused.
CREATE PROCEDURE display_ename
AS
--select statement
SELECT ENAME FROM EMP
GO;
EXEC display_ename;
DROP PROCEDURE display_ename;
CREATE PROCEDURE display_empno @i_deptno int
AS
SELECT * FROM EMP WHERE DEPTNO=@i_deptno
GO
EXEC display_empno @i_deptno=10;
DROP PROCEDURE display_empno;
Exception Handling:
SQL Provides try/catch blocks for exception handling
declare @val1 int
declare @val2 int
begin try
set @val1=9;
set @val2=@val1/0;
end try
begin catch
print 'catching exception'
print error_message()
end catch
go
Transactions in SQL:
Transaction is group of commands that change data stored in a database
begin transaction
update emp set sal=sal+201 where deptno=20;
commit transaction;
rollback transaction;
begin try
begin transaction
update emp set sal=0 where deptno=20
update emp set sal=1/0 ;
commit transaction
print 'commit transaction'
end try
begin catch
rollback transaction
print 'rollback transaction'
print error_message()
end catch
go
select * from emp where deptno=20;
Database Administrator:
A Database Administrator (DBA) directs or performs all activities related to maintaining a successful database environment.
Software Installation and Maintenance
Taking care of Database backup & Recovery
Maintaing Security of the database
Take care of Access control for different users
Monitor databases for performance issues
Types of DBA:
Production DBA
Application DBA
Development DBA
UAT DBA
Data warehouse DBA
Intro to MS SQL Server:
SQL Server is Microsoft's relational database management system (RDBMS). It is a full featured database primarly designed to compete
against competitors Oracle Database and MYSQL.It is also known as ORDBMS.It is both GUI and command based software.
Evolution of SQL Server:
SQL Server 2000,2005,2008,2008 R2,2012,2014,2016,2017,.....
Editions of SQL Server:
Enterprise,Standard,Web,Developer,Express
https://www.youtube.com/watch?v=JTDK6r1GuUU&list=LLJ2jlYQyxwtWSG_5RzfBBjQ&index=2&t=555s
Instances of SQL Server:
An instance is collection of SQL Server Databases run by single server service
Default and Named Instances
If you plan to install a single instance of SQL Server on database server, go with default instance
Use named instance for situations where you planned to have multiple instances on the same server computer
A computer can host only one default instance; all other instances must be named
SQL Server System Databases:
System databases ae defined by Microsoft and are needed for SQL Server to operate. These databases includes
Master --> The master database records all the system level information for a SQL Server system. This includes instance wide metadata such as
logon accounts,endpoints,liked servers and system configuration settings
Model --> The model database is used as a template whenever a new user database is created.You can change most database properties,create users,
stored procedures,tables,views,etc-whatever you do will be applied to any new databases.
MSDB --> The msdb databse is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Managment Studio,
Service Broker and Database Mail.
TempDB --> TempDB is the workhorse of the system database.It is the workspace that SQL Server uses to store the intermediate results of
query processing and sorting.
No comments:
Post a Comment